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);
}
private object GetValue()
{
CellProxy c = this.Worksheet.GetCell(this.Row, this.Column);
if (c != null)
{
if (c.DataType != null)
{
CellValues cellDataType = (CellValues)c.DataType;
if (cellDataType == CellValues.Number)
{
return(c.Value);
}
if (cellDataType == CellValues.InlineString)
{
return(c.Value);
}
else if (cellDataType == CellValues.SharedString)
{
if (c.Value != null)
{
return(this.Worksheet.Document.SharedStrings.Get(Convert.ToUInt32(c.Value)));
}
}
}
if (c.StyleIndex != null)
{
CellFormat cf = this.Worksheet.Document.Styles.GetCellFormat(c.StyleIndex.Value);
if (this.Worksheet.Document.Styles.IsDateFormat(cf))
{
if (c.Value != null)
{
return(DateTime.FromOADate((double)c.Value));
}
}
else
{
if (c.Value != null)
{
return(c.Value);
}
}
}
else
{
if (c.Value != null)
{
return(c.Value);
}
}
}
return(null);
}
private void AddCellToCache(CellProxy c, uint rowIdx, uint colIdx)
{
this.Modified = true;
SortedList <uint, CellProxy> rowCache;
if (!_cachedCells.TryGetValue(rowIdx, out rowCache))
{
rowCache = new SortedList <uint, CellProxy>();
_cachedCells[rowIdx] = rowCache;
}
rowCache[colIdx] = c;
}
private CellProxy RemoveCellFromCache(uint rowIdx, uint colIdx)
{
this.Modified = true;
CellProxy c = null;
SortedList <uint, CellProxy> rowCache;
if (_cachedCells.TryGetValue(rowIdx, out rowCache))
{
if (rowCache.TryGetValue(colIdx, out c))
{
rowCache.Remove(colIdx);
return(c);
}
}
return(null);
}
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 CellProxy EnsureCell(uint row, uint col)
{
CellProxy c = null;
SortedList <uint, CellProxy> rowCache;
if (!_cachedCells.TryGetValue(row, out rowCache))
{
c = new CellProxy(this);
AddCellToCache(c, row, col);
}
else
{
if (!rowCache.TryGetValue(col, out c))
{
c = new CellProxy(this);
if (rowCache != null)
{
AddCellToCache(c, rowCache, col);
}
}
}
return(c);
}
public void InsertOrDeleteColumns(uint colStart, int colDelta, bool copyPreviousStyle)
{
if (colDelta == 0)
{
return;
}
this.Modified = true;
Action <uint, uint> shiftCells = (rowIdx, colIdx) =>
{
if (colIdx >= colStart)
{
CellProxy c = RemoveCellFromCache(rowIdx, colIdx);
int newColIdx = (int)colIdx + colDelta;
// If delta is negative, cells will be not put back, i.e. deleted
if (newColIdx >= colStart && newColIdx >= 1)
{
if (colIdx >= ExcelConstraints.MaxColumns)
{
throw new InvalidOperationException("Max number of columns exceeded");
}
AddCellToCache(c, rowIdx, (uint)newColIdx);
}
}
};
if (colDelta > 0)
{
LoopCellsReverse(shiftCells);
}
else
{
LoopCells(shiftCells);
}
// Adjust cached <col> elements
Columns cols = this.GetFirstElement <Columns>();
if (cols != null)
{
List <Column> colsToRemove = new List <Column>();
foreach (Column col in cols)
{
if (col.Min >= colStart)
{
col.Min = (uint)Math.Max(0, col.Min + colDelta);
}
if (col.Max >= colStart)
{
col.Max = (uint)Math.Max(0, col.Max + colDelta);
}
if (col.Min <= 0 || col.Max < col.Min)
{
colsToRemove.Add(col);
}
}
foreach (Column col in colsToRemove)
{
col.Remove();
}
Column colPrev = (from col in cols.Elements <Column>()
where col.Max == colStart - 1
select col).FirstOrDefault();
if (colPrev != null)
{
colPrev.Max = (uint)(colPrev.Max + colDelta);
}
if (cols.ChildElements.Count == 0)
{
_cachedElements.Remove(cols);
}
}
if (colDelta > 0)
{
if (copyPreviousStyle)
{
CreateColumnCopies(colStart - 1, colDelta,
cOld =>
{
if (cOld.StyleIndex != null)
{
var cNew = new CellProxy(this);
cNew.StyleIndex = cOld.StyleIndex;
return(cNew);
}
return(null);
}
);
}
else
{
// Ensure this column does not have a column defintion
// so we don't copy previous column style
// TODO: make this more efficient
for (uint ctr = colStart; ctr < colStart + colDelta; ctr++)
{
EnsureSingleSpanColumn(ctr);
DeleteSingleSpanColumn(ctr);
}
}
}
}
public void InsertOrDeleteRows(uint rowStart, int rowDelta, bool copyPreviousStyle)
{
if (rowDelta == 0)
{
return;
}
this.Modified = true;
IList <uint> rowIndexes;
if (rowDelta > 0)
{
rowIndexes = _cachedCells.Keys.Reverse().ToList();
}
else
{
rowIndexes = _cachedCells.Keys.ToList();
}
var newCellProxies = new SortedList <uint, SortedList <uint, CellProxy> >();
foreach (uint rowIdx in rowIndexes)
{
uint newRowIdx;
if (rowIdx >= rowStart)
{
newRowIdx = (uint)(rowIdx + rowDelta);
}
else
{
newRowIdx = rowIdx;
}
newCellProxies[newRowIdx] = _cachedCells[rowIdx];
}
_cachedCells = newCellProxies;
// Adjust cached <row> elements
IEnumerable <uint> affectedCachedRowIndexes;
if (rowDelta > 0)
{
affectedCachedRowIndexes = _cachedRows.Keys.Where(k => k >= rowStart).Reverse().ToList();
}
else
{
affectedCachedRowIndexes = _cachedRows.Keys.Where(k => k >= rowStart).ToList();
}
foreach (var rowIdx in affectedCachedRowIndexes)
{
Row r = _cachedRows[rowIdx];
int newRowIdx = (int)r.RowIndex.Value + rowDelta;
_cachedRows.Remove(rowIdx);
// If delta is negative, rows will be not put back, i.e. deleted
if (newRowIdx >= rowStart && newRowIdx >= 1)
{
r.RowIndex = (uint)(newRowIdx);
_cachedRows[r.RowIndex] = r;
}
}
if (rowDelta > 0 && copyPreviousStyle)
{
CreateRowCopies(rowStart - 1, rowDelta,
cOld =>
{
if (cOld.StyleIndex != null)
{
var cNew = new CellProxy(this);
cNew.StyleIndex = cOld.StyleIndex;
return(cNew);
}
return(null);
}
);
}
}
/// <summary>
/// For optimization -- enables use of rowCache if we already have it
/// </summary>
/// <param name="c"></param>
/// <param name="rowIdx"></param>
/// <param name="colIdx"></param>
private void AddCellToCache(CellProxy c, SortedList <uint, CellProxy> rowCache, uint colIdx)
{
this.Modified = true;
rowCache[colIdx] = c;
}
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;
}
private void WriteSheetData(OpenXmlWriter writer)
{
writer.WriteStartElement(new SheetData());
foreach (var rowItem in _cachedCells)
{
uint rowIdx = rowItem.Key;
var cells = rowItem.Value;
var rowAttrs = EnumRowAttributes(rowIdx, cells).ToList();
if (rowAttrs.Count > 0 || cells.Count > 0)
{
writer.WriteStartElement(new Row(), rowAttrs);
foreach (var cellItem in cells)
{
uint colIdx = cellItem.Key;
CellProxy cellProxy = cellItem.Value;
writer.WriteStartElement(new Cell(), EnumCellProxyAttributes(rowIdx, colIdx, cellProxy));
if (cellProxy.Formula != null)
{
CellFormula cf = new CellFormula(cellProxy.Formula.Text);
if (cellProxy.Formula.R1 != null)
{
cf.R1 = cellProxy.Formula.R1;
}
if (cellProxy.Formula.R2 != null)
{
cf.R2 = cellProxy.Formula.R2;
}
if (cellProxy.Formula.Reference != null)
{
cf.Reference = cellProxy.Formula.Reference;
}
if (cellProxy.Formula.AlwaysCalculateArray != null)
{
cf.AlwaysCalculateArray = cellProxy.Formula.AlwaysCalculateArray;
}
if (cellProxy.Formula.Bx != null)
{
cf.Bx = cellProxy.Formula.Bx;
}
if (cellProxy.Formula.CalculateCell != null)
{
cf.CalculateCell = cellProxy.Formula.CalculateCell;
}
if (cellProxy.Formula.DataTable2D != null)
{
cf.DataTable2D = cellProxy.Formula.DataTable2D;
}
if (cellProxy.Formula.DataTableRow != null)
{
cf.DataTableRow = cellProxy.Formula.DataTableRow;
}
if (cellProxy.Formula.FormulaType != null)
{
cf.FormulaType = cellProxy.Formula.FormulaType;
}
if (cellProxy.Formula.Input1Deleted != null)
{
cf.Input1Deleted = cellProxy.Formula.Input1Deleted;
}
if (cellProxy.Formula.Input2Deleted != null)
{
cf.Input2Deleted = cellProxy.Formula.Input2Deleted;
}
if (cellProxy.Formula.SharedIndex != null)
{
cf.SharedIndex = cellProxy.Formula.SharedIndex;
}
writer.WriteElement(cf);
}
if (cellProxy.Value != null)
{
writer.WriteElement(new CellValue(cellProxy.SerializedValue));
}
writer.WriteEndElement(); // c
}
writer.WriteEndElement(); // row
}
}
writer.WriteEndElement(); // sheetData
}
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 SetValue(object value)
{
CellProxy c = this.Worksheet.EnsureCell(this.Row, this.Column);
bool hasDateFormat = false;
if (c.StyleIndex != null)
{
CellFormat cfCurrent = this.Worksheet.Document.Styles.GetCellFormat(c.StyleIndex.Value);
if (this.Worksheet.Document.Styles.IsDateFormat(cfCurrent))
{
hasDateFormat = true;
}
}
if (value == null)
{
c.DataType = null;
c.Value = null;
}
else
{
Type valueType = value.GetType();
if (valueType == typeof(DateTime))
{
c.DataType = null;
if (!hasDateFormat)
{
// 14 = generic date format
CellFormat cfDate = new CellFormat()
{
ApplyNumberFormat = true, NumberFormatId = 14
};
uint cfIdxDate = this.Worksheet.Document.Styles.MergeAndRegisterCellFormat(cfDate, c.StyleIndex, false);
c.StyleIndex = cfIdxDate;
}
DateTime dtValue = (DateTime)value;
c.Value = dtValue.ToOADate();
}
else if (ValueChecker.IsNumeric(valueType))
{
if (hasDateFormat)
{
CellFormat cfGeneric = new CellFormat()
{
NumberFormatId = 0
};
uint cfIfxGeneric = this.Worksheet.Document.Styles.MergeAndRegisterCellFormat(cfGeneric, c.StyleIndex, false);
c.StyleIndex = cfIfxGeneric;
}
c.DataType = CellValues.Number;
c.Value = value;
}
else
{
if (hasDateFormat)
{
CellFormat cfGeneric = new CellFormat()
{
NumberFormatId = 0
};
uint cfIfxGeneric = this.Worksheet.Document.Styles.MergeAndRegisterCellFormat(cfGeneric, c.StyleIndex, false);
c.StyleIndex = cfIfxGeneric;
}
string valueStr = value.ToString();
int sharedStrIdx = this.Worksheet.Document.SharedStrings.Put(valueStr);
c.DataType = CellValues.SharedString;
c.Value = (uint)sharedStrIdx;
}
}
}
