private void CreateColumnCopies(uint colFromIdx, int numOfColumns, Func <CellProxy, CellProxy> fnCreate)
{
Action <uint, uint> actionCopyColumn = (rowIdx, colIdx) =>
{
RowColumn rcOld = new RowColumn()
{
Row = rowIdx, Column = colIdx
};
if (rcOld.Column == colFromIdx)
{
foreach (var newColIdx in Enumerable.Range((int)colFromIdx + 1, numOfColumns))
{
RowColumn rcNew = new RowColumn()
{
Row = rcOld.Row, Column = (uint)newColIdx
};
CellProxy cOld = GetCell(rcOld.Row, rcOld.Column);
if (cOld != null)
{
CellProxy cNew = fnCreate(cOld);
if (cNew != null)
{
AddCellToCache(cNew, rcNew.Row, rcNew.Column);
}
}
}
}
};
LoopCells(actionCopyColumn);
}
public ExcelCell this[string address]
{
get
{
RowColumn rc = ExcelAddress.ToRowColumn(address);
return(this[rc.Row, rc.Column]);
}
}
public static RowColumn CacheIndexToRowCol(ulong cacheIdx)
{
RowColumn rc = new RowColumn();
// Note: 0-indexed --> 1-indexed
rc.Row = (uint)(cacheIdx / (ulong)ExcelConstraints.MaxColumns) + 1;
rc.Column = (uint)(cacheIdx % (ulong)ExcelConstraints.MaxColumns) + 1;
return(rc);
}
private IEnumerable <OpenXmlAttribute> EnumCellProxyAttributes(uint row, uint col, CellProxy cellProxy)
{
yield return(new OpenXmlAttribute(null, "r", null, RowColumn.ToAddress(row, col)));
if (cellProxy.DataType != null)
{
yield return(new OpenXmlAttribute(null, "t", null, STCellType((CellValues)cellProxy.DataType)));
}
if (cellProxy.StyleIndex != null)
{
yield return(new OpenXmlAttribute(null, "s", null, cellProxy.StyleIndex.Value.ToString()));
}
}
private void CreateRowCopies(uint rowFromIdx, int numOfRows, Func <CellProxy, CellProxy> fnCreate)
{
SortedList <uint, CellProxy> rowCache;
if (_cachedCells.TryGetValue(rowFromIdx, out rowCache))
{
Row cachedRow;
_cachedRows.TryGetValue(rowFromIdx, out cachedRow);
foreach (var newRowIdx in Enumerable.Range((int)rowFromIdx + 1, numOfRows))
{
if (cachedRow != null)
{
Row cachedRowCopy = (Row)cachedRow.CloneNode(false);
cachedRowCopy.RowIndex = (uint)newRowIdx;
_cachedRows.Add(cachedRowCopy.RowIndex, cachedRowCopy);
}
foreach (var colIdx in rowCache.Keys)
{
RowColumn rcOld = new RowColumn()
{
Row = rowFromIdx, Column = colIdx
};
RowColumn rcNew = new RowColumn()
{
Row = (uint)newRowIdx, Column = colIdx
};
CellProxy cOld = GetCell(rcOld.Row, rcOld.Column);
CellProxy cNew = fnCreate(cOld);
if (cNew != null)
{
AddCellToCache(cNew, rcNew.Row, rcNew.Column);
}
}
}
}
}
public void Load()
{
WorksheetPart wp = _wsheet.GetOWorksheetPart();
if (wp == null)
{
return;
}
Action <Cell> readCell = (cell) =>
{
RowColumn rc = ExcelAddress.ToRowColumn(cell.CellReference);
CellProxy cellProxy = this.EnsureCell(rc.Row, rc.Column);
if (cell.DataType != null)
{
cellProxy.DataType = cell.DataType.Value;
if (cell.DataType.Value == CellValues.InlineString)
{
cellProxy.Value = cell.InlineString.Text.Text;
}
else
{
if (cell.CellValue != null)
{
cellProxy.Value = cell.CellValue.Text;
}
else
{
cellProxy.Value = string.Empty;
}
}
}
else
{
if (cell.CellValue != null)
{
cellProxy.Value = cell.CellValue.Text;
}
}
if (cell.StyleIndex != null)
{
cellProxy.StyleIndex = cell.StyleIndex;
}
if (cell.ShowPhonetic != null)
{
cellProxy.ShowPhonetic = cell.ShowPhonetic;
}
if (cell.ValueMetaIndex != null)
{
cellProxy.ValueMetaIndex = cell.ValueMetaIndex;
}
if (cell.CellFormula != null)
{
cellProxy.CreateFormula();
cellProxy.Formula.Text = cell.CellFormula.Text;
cellProxy.Formula.R1 = cell.CellFormula.R1;
cellProxy.Formula.R2 = cell.CellFormula.R2;
cellProxy.Formula.Reference = cell.CellFormula.Reference;
if (cell.CellFormula.AlwaysCalculateArray != null)
{
cellProxy.Formula.AlwaysCalculateArray = cell.CellFormula.AlwaysCalculateArray;
}
if (cell.CellFormula.Bx != null)
{
cellProxy.Formula.Bx = cell.CellFormula.Bx;
}
if (cell.CellFormula.CalculateCell != null)
{
cellProxy.Formula.CalculateCell = cell.CellFormula.CalculateCell;
}
if (cell.CellFormula.DataTable2D != null)
{
cellProxy.Formula.DataTable2D = cell.CellFormula.DataTable2D;
}
if (cell.CellFormula.DataTableRow != null)
{
cellProxy.Formula.DataTableRow = cell.CellFormula.DataTableRow;
}
if (cell.CellFormula.FormulaType != null)
{
cellProxy.Formula.FormulaType = cell.CellFormula.FormulaType;
}
if (cell.CellFormula.Input1Deleted != null)
{
cellProxy.Formula.Input1Deleted = cell.CellFormula.Input1Deleted;
}
if (cell.CellFormula.Input2Deleted != null)
{
cellProxy.Formula.Input2Deleted = cell.CellFormula.Input2Deleted;
}
if (cell.CellFormula.SharedIndex != null)
{
cellProxy.Formula.SharedIndex = cell.CellFormula.SharedIndex;
}
cellProxy.Value = null; // Don't cache/store values for formulas
}
};
OpenXmlReader reader = OpenXmlReader.Create(_wsheet.GetOWorksheetPart());
bool inWorksheet = false;
bool inSheetData = false;
while (reader.Read())
{
if (inWorksheet && reader.IsStartElement)
{
if (reader.ElementType == typeof(Row) && reader.IsStartElement)
{
// ----------------------------------------
// Scan row if anything other than RowIndex and Spans has been set,
// if so then cache
Row r = (Row)reader.LoadCurrentElement();
var needToCacheRow = (from a in r.GetAttributes()
let ln = a.LocalName
where ln != "r" && ln != "spans"
select a).Any();
if (needToCacheRow)
{
_cachedRows.Add(r.RowIndex, (Row)r.CloneNode(false));
}
foreach (Cell cell in r.Elements <Cell>())
{
readCell(cell);
}
// ----------------------------------------
}
else if (reader.ElementType == typeof(SheetData))
{
inSheetData = reader.IsStartElement;
}
else if (reader.IsStartElement)
{
var e = reader.LoadCurrentElement();
_cachedElements.Add(e);
}
}
else if (reader.ElementType == typeof(Worksheet))
{
inWorksheet = reader.IsStartElement;
}
}
// Reset modified to false (loading sets it to true due to CellProxy loading)
this.Modified = false;
}
public void WriteWorksheetPart(OpenXmlWriter writer)
{
// TODO: final cleanup
// - merge redundant columns
// - remove unused shared strings
// Remove rows without cells
foreach (var rowItem in _cachedCells.ToList())
{
if (rowItem.Value.Count == 0)
{
_cachedCells.Remove(rowItem.Key);
}
}
// Simulate rows for cached rows
foreach (var rowIdx in _cachedRows.Keys)
{
if (!_cachedCells.ContainsKey(rowIdx))
{
_cachedCells[rowIdx] = new SortedList <uint, CellProxy>();
}
}
// Get first and last addresses
uint minRow = uint.MaxValue;
uint minCol = uint.MaxValue;
uint maxRow = 0;
uint maxCol = 0;
foreach (var rowItem in _cachedCells)
{
uint rowIdx = rowItem.Key;
var cells = rowItem.Value;
if (minRow == uint.MaxValue)
{
minRow = rowIdx;
}
maxRow = rowIdx;
if (cells.Count > 0)
{
minCol = Math.Min(minCol, cells.Keys.First());
maxCol = Math.Max(maxCol, cells.Keys.Last());
}
}
string firstAddress = null, lastAddress = null;
if (minRow < uint.MaxValue && minCol < uint.MaxValue)
{
firstAddress = RowColumn.ToAddress(minRow, minCol);
if (minRow != maxRow || minCol != maxCol)
{
lastAddress = RowColumn.ToAddress(maxRow, maxCol);
}
}
else
{
firstAddress = "A1";
}
writer.WriteStartDocument();
writer.WriteStartElement(new Worksheet());
foreach (string childTagName in SchemaInfo.WorksheetChildSequence)
{
if (childTagName == "sheetData")
{
WriteSheetData(writer);
}
else if (childTagName == "dimension")
{
string dimensionRef = firstAddress + (lastAddress != null ? ":" + lastAddress : "");
writer.WriteElement(new SheetDimension()
{
Reference = dimensionRef
});
}
else if (childTagName == "sheetViews")
{
SheetViews svs = GetFirstElement <SheetViews>();
if (svs != null)
{
foreach (SheetView sv in svs.Elements <SheetView>())
{
foreach (Selection sel in sv.Elements <Selection>())
{
if (minRow < uint.MaxValue)
{
sel.ActiveCell = firstAddress;
sel.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(firstAddress) });
}
else
{
sel.Remove();
}
}
}
writer.WriteElement(svs);
}
}
else
{
foreach (var e in GetElementsByTagName(childTagName))
{
writer.WriteElement(e);
}
}
}
writer.WriteEndElement(); // worksheet
}
private static string TranslateInternal(RangeComponents er, uint rowStart, uint colStart, int rowDelta, int colDelta, bool followAbsoluteRefs)
{
string newCellRef1 = null;
string newCellRef2 = null;
bool errRef1 = false, errRef2 = false;
if (true)
{
if (er.Cell1Error != "")
{
newCellRef1 = er.Cell1Error;
}
else
{
RowColumn rc1 = er.Cell1RowColumn;
newCellRef1 = TranslateInternal(
er.Cell1RowDollar, rc1.Row,
er.Cell1ColDollar, rc1.Column,
rowStart, colStart,
rowDelta, colDelta,
followAbsoluteRefs,
out errRef1);
}
}
if (er.Cell2 != "")
{
if (er.Cell2Error != "")
{
newCellRef2 = er.Cell2Error;
}
else
{
RowColumn rc2 = er.Cell2RowColumn;
newCellRef2 = TranslateInternal(
er.Cell2RowDollar, rc2.Row,
er.Cell2ColDollar, rc2.Column,
rowStart, colStart,
rowDelta, colDelta,
followAbsoluteRefs,
out errRef2);
}
}
string newRange = "";
if (er.SheetName != "")
{
newRange += er.EscapedSheetName + "!";
}
if (errRef1 && (errRef2 || newCellRef2 == null))
{
newRange += "#REF!";
}
else
{
newRange += newCellRef1;
if (newCellRef2 != null)
{
newRange += ":" + newCellRef2;
}
}
return(newRange);
}