/// <summary>
/// Removes the comment
/// </summary>
/// <param name="comment">The comment to remove</param>
public void Remove(ExcelComment comment)
{
int i = -1;
ExcelComment c = null;
if (Worksheet._commentsStore.Exists(comment.Range._fromRow, comment.Range._fromCol, ref i))
{
c = _list[i];
}
if (comment == c)
{
comment.TopNode.ParentNode.RemoveChild(comment.TopNode); //Remove VML
comment._commentHelper.TopNode.ParentNode.RemoveChild(comment._commentHelper.TopNode); //Remove Comment
Worksheet.VmlDrawingsComments._drawings.Delete(comment.Range._fromRow, comment.Range._fromCol, 1, 1, false);
_list.RemoveAt(i);
Worksheet._commentsStore.Delete(comment.Range._fromRow, comment.Range._fromCol, 1, 1, false); //Issue 15549, Comments should not be shifted
var ci = new CellStoreEnumerator <int>(Worksheet._commentsStore);
while (ci.Next())
{
if (ci.Value > i)
{
ci.Value -= 1;
}
}
}
else
{
throw (new ArgumentException("Comment does not exist in the worksheet"));
}
}
private void CreateCache(string address, XmlNode node)
{
var ws = _chart.WorkSheet;
var range = ws.Cells[address];
var startRow = range._fromRow;
var items = 0;
var cse = new CellStoreEnumerator <ExcelValue>(ws._values);
while (cse.Next())
{
var v = cse.Value._value;
if (v != null)
{
var d = Utils.ConvertUtil.GetValueDouble(v);
var ptNode = node.OwnerDocument.CreateElement("c", "pt", ExcelPackage.schemaChart);
node.AppendChild(ptNode);
ptNode.SetAttribute("idx", (cse.Row - startRow).ToString(CultureInfo.InvariantCulture));
ptNode.InnerXml = $"<c:v>{Utils.ConvertUtil.GetValueForXml(d, ws.Workbook.Date1904)}</c:v>";
items++;
}
}
var countNode = node.SelectSingleNode("c:ptCount", NameSpaceManager) as XmlElement;
if (countNode != null)
{
countNode.SetAttribute("val", items.ToString(CultureInfo.InvariantCulture));
}
}
private static void CopyFromStyleRow(ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow)
{
if (copyStylesFromRow >= rowFrom)
{
copyStylesFromRow += rows;
}
//Copy style from style row
using (var cseS = new CellStoreEnumerator <ExcelValue>(ws._values, copyStylesFromRow, 0, copyStylesFromRow, ExcelPackage.MaxColumns))
{
while (cseS.Next())
{
if (cseS.Value._styleId == 0)
{
continue;
}
for (var r = 0; r < rows; r++)
{
ws.SetStyleInner(rowFrom + r, cseS.Column, cseS.Value._styleId);
}
}
}
//Copy outline
var styleRowOutlineLevel = ws.Row(copyStylesFromRow).OutlineLevel;
for (var r = rowFrom; r < rowFrom + rows; r++)
{
ws.Row(r).OutlineLevel = styleRowOutlineLevel;
}
}
private static void InsertColumnFormulas(ExcelWorksheet ws, int columnFrom, int columns)
{
foreach (var f in ws._sharedFormulas.Values)
{
if (f.StartCol >= columnFrom)
{
f.StartCol += columns;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromCol >= columnFrom)
{
a._fromCol += columns;
a._toCol += columns;
}
else if (a._toCol >= columnFrom)
{
a._toCol += columns;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, 0, columns, 0, columnFrom, ws.Name, ws.Name);
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), 0, columns, 0, columnFrom, ws.Name, ws.Name);
}
}
}
public void Reset()
{
if (_enum != null)
{
_enum.Dispose();
}
_enum = new CellStoreEnumerator <ExcelVmlDrawingComment>(_drawings, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
}
private void SetAddress(ExcelWorksheet ws, ExcelAddressBase address)
{
_ws = ws;
_fromRow = address._fromRow;
_fromCol = address._fromCol;
_toRow = address._toRow;
_toCol = address._toCol;
_address = address;
_values = new CellStoreEnumerator <ExcelValue>(_ws._values, _fromRow, _fromCol, _toRow, _toCol);
_cell = new CellInfo(_ws, _values);
}
private static void FixFormulasInsertRow(ExcelWorksheet ws, int rowFrom, int rows, int columnFrom = 0, int columnTo = ExcelPackage.MaxColumns)
{
//Adjust formulas
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
foreach (var f in wsToUpdate._sharedFormulas.Values)
{
if (ws.Name == wsToUpdate.Name)
{
if (f.StartCol >= columnFrom)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromRow >= rowFrom)
{
a._fromRow += rows;
a._toRow += rows;
}
else if (a._toRow >= rowFrom)
{
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0, wsToUpdate.Name, ws.Name);
}
}
else if (f.Formula.Contains(ws.Name))
{
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, columnFrom, wsToUpdate.Name, ws.Name);
}
}
var cse = new CellStoreEnumerator <object>(wsToUpdate._formulas);
while (cse.Next())
{
if (cse.Value is string v)
{
if (ws.Name == wsToUpdate.Name)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, rows, 0, rowFrom, 0, wsToUpdate.Name, ws.Name);
}
else if (v.Contains(ws.Name))
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, rows, 0, rowFrom, 0, wsToUpdate.Name, ws.Name);
}
}
}
}
}
public void EnumCellstore()
{
var ws = _pck.Workbook.Worksheets.Add("enum");
LoadData(ws, 5000);
var o = new CellStoreEnumerator <ExcelValue>(ws._values, 2, 1, 5, 3);
for (var i = 1; i < 5000; i++)
{
Assert.AreEqual($"{i-1},0", ws.Cells[i, 1].Value);
}
}
private static void AdjustColumnMinMaxDelete(ExcelWorksheet ws, int columnFrom, int columns)
{
ExcelColumn col = ws.GetValueInner(0, columnFrom) as ExcelColumn;
if (col == null)
{
var r = 0;
var c = columnFrom;
if (ws._values.PrevCell(ref r, ref c))
{
col = ws.GetValueInner(0, c) as ExcelColumn;
if (col._columnMax >= columnFrom)
{
col.ColumnMax = columnFrom - 1;
}
}
}
var toCol = columnFrom + columns - 1;
var moveValue = new ExcelValue {
_styleId = int.MaxValue
};
var cse = new CellStoreEnumerator <ExcelValue>(ws._values, 0, columnFrom, 0, ExcelPackage.MaxColumns);
while (cse.MoveNext())
{
var column = cse.Value._value as ExcelColumn;
if (column != null && column._columnMax > toCol)
{
if (column.ColumnMin > toCol)
{
column._columnMin -= columns;
column._columnMax -= columns;
}
else if (column.ColumnMax > toCol)
{
column._columnMax -= columns;
if (column._columnMin > columnFrom)
{
column._columnMin = columnFrom;
}
moveValue = cse.Value;
}
}
}
if (moveValue._styleId != int.MaxValue)
{
ws._values.SetValue(0, toCol + 1, moveValue);
}
}
private static void CopyStylesFromColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn)
{
//Copy style from another column?
if (copyStylesFromColumn > 0)
{
if (copyStylesFromColumn >= columnFrom)
{
copyStylesFromColumn += columns;
}
//Get styles to a cached list,
var l = new List <int[]>();
var sce = new CellStoreEnumerator <ExcelValue>(ws._values, 0, copyStylesFromColumn, ExcelPackage.MaxRows, copyStylesFromColumn);
lock (sce)
{
while (sce.Next())
{
if (sce.Value._styleId == 0)
{
continue;
}
l.Add(new int[] { sce.Row, sce.Value._styleId });
}
}
//Set the style id's from the list.
foreach (var sc in l)
{
for (var c = 0; c < columns; c++)
{
if (sc[0] == 0)
{
var col = ws.Column(columnFrom + c); //Create the column
col.StyleID = sc[1];
}
else
{
ws.SetStyleInner(sc[0], columnFrom + c, sc[1]);
}
}
}
var newOutlineLevel = ws.Column(copyStylesFromColumn).OutlineLevel;
for (var c = 0; c < columns; c++)
{
ws.Column(columnFrom + c).OutlineLevel = newOutlineLevel;
}
}
}
internal static void FixFormulasDeleteColumn(ExcelWorksheet ws, int columnFrom, int columns, string workSheetName)
{
var delSF = new List <int>();
foreach (var sf in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
var a = new ExcelAddress(sf.Address).DeleteColumn(columnFrom, columns);
if (a == null)
{
delSF.Add(sf.Index);
}
else
{
sf.Address = a.Address;
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -columns, 0, columnFrom, ws.Name, workSheetName);
if (sf.StartCol > columnFrom)
{
var c = Math.Max(columnFrom, sf.StartCol - columns);
sf.StartCol -= c;
}
}
}
else if (sf.Formula.Contains(workSheetName))
{
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -columns, 0, columnFrom, ws.Name, workSheetName);
}
}
foreach (var ix in delSF)
{
ws._sharedFormulas.Remove(ix);
}
var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name || v.IndexOf(workSheetName, StringComparison.CurrentCultureIgnoreCase) >= 0)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, 0, -columns, 0, columnFrom, ws.Name, workSheetName);
}
}
}
}
private static void FixFormulasInsertRow(ExcelWorksheet ws, int rowFrom, int rows, string workSheetName)
{
foreach (var f in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromRow >= rowFrom)
{
a._fromRow += rows;
a._toRow += rows;
}
else if (a._toRow >= rowFrom)
{
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
else if (f.Formula.Contains(workSheetName))
{
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
else if (v.Contains(workSheetName))
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
}
}
}
private static void AdjustColumns(ExcelWorksheet ws, int columnFrom, int columns)
{
var csec = new CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
var lst = new List <ExcelColumn>();
foreach (var val in csec)
{
var col = val._value;
if (col is ExcelColumn)
{
lst.Add((ExcelColumn)col);
}
}
for (int i = lst.Count - 1; i >= 0; i--)
{
var c = lst[i];
if (c._columnMin >= columnFrom)
{
if (c._columnMin + columns <= ExcelPackage.MaxColumns)
{
c._columnMin += columns;
}
else
{
c._columnMin = ExcelPackage.MaxColumns;
}
if (c._columnMax + columns <= ExcelPackage.MaxColumns)
{
c._columnMax += columns;
}
else
{
c._columnMax = ExcelPackage.MaxColumns;
}
}
else if (c._columnMax >= columnFrom)
{
var cc = c._columnMax - columnFrom;
c._columnMax = columnFrom - 1;
ws.CopyColumn(c, columnFrom + columns, columnFrom + columns + cc);
}
}
}
private static void AdjustColumnMinMax(ExcelWorksheet ws, int columnFrom, int columns)
{
var csec = new CellStoreEnumerator <ExcelValue>(ws._values, 0, columnFrom, 0, columnFrom + columns - 1);
foreach (var val in csec)
{
var column = val._value;
if (column is ExcelColumn)
{
var c = (ExcelColumn)column;
if (c._columnMin >= columnFrom)
{
c._columnMin += columns;
c._columnMax += columns;
}
}
}
}
internal static void AdjustFormulasColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
var delSF = new List <int>();
foreach (var sf in ws._sharedFormulas.Values)
{
var a = new ExcelAddress(sf.Address).DeleteColumn(columnFrom, columns);
if (a == null)
{
delSF.Add(sf.Index);
}
else
{
sf.Address = a.Address;
if (sf.StartCol > columnFrom)
{
var c = Math.Min(sf.StartCol - columnFrom, columns);
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -c, 0, 1, ws.Name, ws.Name);
sf.StartCol -= c;
}
}
}
foreach (var ix in delSF)
{
ws._sharedFormulas.Remove(ix);
}
delSF = null;
var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), 0, -columns, 0, columnFrom, ws.Name, ws.Name);
}
}
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
{
var ws = Range.Worksheet;
var fs = new CellStoreEnumerator <object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);
while (fs.Next())
{
if (fs.Value == null || fs.Value.ToString().Trim() == "")
{
continue;
}
var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell()
{
SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column
};
if (fs.Value is int)
{
f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
}
else
{
f.Formula = fs.Value.ToString();
}
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
depChain.Add(f);
FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
}
}
}
}
private static Dictionary <int, ExcelAddress> GetCopiedMergedCells(ExcelRangeBase sourceRange, ExcelRangeBase Destination)
{
var toRow = sourceRange._toRow;
var toCol = sourceRange._toCol;
var fromRow = sourceRange._fromRow;
var fromCol = sourceRange._fromCol;
var worksheet = sourceRange._worksheet;
var copiedMergedCells = new Dictionary <int, ExcelAddress>();
//Merged cells
var csem = new CellStoreEnumerator <int>(worksheet.MergedCells._cells, fromRow, fromCol, toRow, toCol);
while (csem.Next())
{
if (!copiedMergedCells.ContainsKey(csem.Value))
{
var adr = new ExcelAddress(worksheet.Name, worksheet.MergedCells._list[csem.Value]);
var collideResult = sourceRange.Collide(adr);
if (collideResult == eAddressCollition.Inside || collideResult == eAddressCollition.Equal)
{
copiedMergedCells.Add(csem.Value, new ExcelAddress(
Destination._fromRow + (adr.Start.Row - fromRow),
Destination._fromCol + (adr.Start.Column - fromCol),
Destination._fromRow + (adr.End.Row - fromRow),
Destination._fromCol + (adr.End.Column - fromCol)));
}
else
{
//Partial merge of the address ignore.
copiedMergedCells.Add(csem.Value, null);
}
}
}
return(copiedMergedCells);
}
private void SetStyleCells(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
{
var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
var colCache = new Dictionary <int, ExcelValue>(address.End.Column - address.Start.Column + 1);
var cellEnum = new CellStoreEnumerator <ExcelValue>(ws._values, address.Start.Row, address.Start.Column, address.End.Row, address.End.Column);
var hasEnumValue = cellEnum.Next();
for (int row = address._fromRow; row <= address._toRow; row++)
{
for (int col = address._fromCol; col <= address._toCol; col++)
{
ExcelValue value;
if (hasEnumValue && row == cellEnum.Row && col == cellEnum.Column)
{
value = cellEnum.Value;
hasEnumValue = cellEnum.Next();
}
else
{
value = new ExcelValue {
_styleId = 0
};
}
var s = value._styleId;
if (s == 0)
{
// get row styleId with cache
if (rowCache.ContainsKey(row))
{
s = rowCache[row];
}
else
{
s = ws._values.GetValue(row, 0)._styleId;
rowCache.Add(row, s);
}
if (s == 0)
{
// get column styleId with cache
if (colCache.ContainsKey(col))
{
s = colCache[col]._styleId;
}
else
{
var v = ws._values.GetValue(0, col);
colCache.Add(col, v);
s = v._styleId;
}
if (s == 0)
{
int r = 0, c = col;
if (ws._values.PrevCell(ref r, ref c))
{
if (!colCache.ContainsKey(c))
{
colCache.Add(c, ws._values.GetValue(0, c));
}
var val = colCache[c];
var colObj = (ExcelColumn)(val._value);
if (colObj != null && colObj.ColumnMax >= col) //Fixes issue 15174
{
s = val._styleId;
}
}
}
}
}
if (styleCashe.ContainsKey(s))
{
ws._values.SetValue(row, col, new ExcelValue {
_value = value._value, _styleId = styleCashe[s]
});
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws._values.SetValue(row, col, new ExcelValue {
_value = value._value, _styleId = newId
});
}
}
}
}
void IDisposable.Dispose()
{
_enum.Dispose();
_enum = null;
}
private static void CloneCellsAndStyles(ExcelWorksheet Copy, ExcelWorksheet added)
{
bool sameWorkbook = (Copy.Workbook == added.Workbook);
bool doAdjust = added._package.DoAdjustDrawings;
added._package.DoAdjustDrawings = false;
//Merged cells
foreach (var r in Copy.MergedCells) //Issue #94
{
added.MergedCells.Add(new ExcelAddress(r), false);
}
//Shared Formulas
foreach (int key in Copy._sharedFormulas.Keys)
{
added._sharedFormulas.Add(key, Copy._sharedFormulas[key].Clone());
}
Dictionary <int, int> styleCashe = new Dictionary <int, int>();
//Cells
int row, col;
var val = new CellStoreEnumerator <ExcelValue>(Copy._values);
while (val.Next())
{
row = val.Row;
col = val.Column;
int styleID = 0;
if (row == 0) //Column
{
var c = Copy.GetValueInner(row, col) as ExcelColumn;
if (c != null)
{
var clone = c.Clone(added, c.ColumnMin);
clone.StyleID = c.StyleID;
added.SetValueInner(row, col, clone);
styleID = c.StyleID;
}
}
else if (col == 0) //Row
{
var r = Copy.Row(row);
if (r != null)
{
r.Clone(added);
styleID = r.StyleID;
}
}
else
{
styleID = CopyValues(Copy, added, row, col);
}
if (!sameWorkbook)
{
if (styleCashe.ContainsKey(styleID))
{
added.SetStyleInner(row, col, styleCashe[styleID]);
}
else
{
var s = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, styleID);
styleCashe.Add(styleID, s);
added.SetStyleInner(row, col, s);
}
}
}
//Copy dfx styles used in conditional formatting.
if (!sameWorkbook)
{
CopyDxfStylesConditionalFormatting(Copy, added);
}
added._package.DoAdjustDrawings = doAdjust;
}
internal CellInfo(ExcelWorksheet ws, CellStoreEnumerator <ExcelValue> values)
{
_ws = ws;
_values = values;
}
private static void AdjustFormulasInsert(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
{
//Adjust formulas
foreach (var ws in range._workbook.Worksheets)
{
var workSheetName = range.Worksheet.Name;
var rowFrom = range._fromRow;
var columnFrom = range._fromCol;
var rows = range.Rows;
foreach (var f in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
var a = new ExcelAddressBase(f.Address);
var c = effectedAddress.Collide(a);
if (c == ExcelAddressBase.eAddressCollition.Partly)
{
throw new Exception("Invalid shared formula"); //This should never happend!
}
if (f.StartCol >= columnFrom && c != ExcelAddressBase.eAddressCollition.No)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
if (a._fromRow >= rowFrom)
{
a._fromRow += rows;
a._toRow += rows;
}
else if (a._toRow >= rowFrom)
{
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
else if (f.Formula.Contains(workSheetName))
{
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
}
else if (v.Contains(workSheetName))
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
}
}
}
private static void FixFormulasDelete(ExcelRangeBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift)
{
foreach (var ws in range.Worksheet.Workbook.Worksheets)
{
var workSheetName = range.WorkSheetName;
var rowFrom = range._fromRow;
var rows = range.Rows;
var delSF = new List <int>();
foreach (var sf in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
if (effectedRange.Collide(new ExcelAddressBase(sf.Address)) != ExcelAddressBase.eAddressCollition.No)
{
ExcelAddressBase a;
if (shift == eShiftTypeDelete.Up)
{
a = new ExcelAddress(sf.Address).DeleteRow(range._fromRow, rows);
}
else
{
a = new ExcelAddress(sf.Address).DeleteColumn(range._fromRow, rows);
}
if (a == null)
{
delSF.Add(sf.Index);
}
else
{
sf.Address = a.Address;
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, range, effectedRange, shift, ws.Name, workSheetName);
if (sf.StartRow >= rowFrom)
{
var r = Math.Max(rowFrom, sf.StartRow - rows);
sf.StartRow = r;
}
}
}
}
else if (sf.Formula.Contains(workSheetName))
{
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
}
foreach (var ix in delSF)
{
ws._sharedFormulas.Remove(ix);
}
var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name || v.IndexOf(workSheetName, StringComparison.CurrentCultureIgnoreCase) >= 0)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedRange, shift, ws.Name, workSheetName);
}
}
}
}
}
private static List <CopiedCell> GetCopiedValues(ExcelRangeBase sourceRange, ExcelRangeBase Destination, ExcelRangeCopyOptionFlags?excelRangeCopyOptionFlags)
{
var worksheet = sourceRange._worksheet;
var toRow = sourceRange._toRow;
var toCol = sourceRange._toCol;
var fromRow = sourceRange._fromRow;
var fromCol = sourceRange._fromCol;
int i = 0;
object o = null;
byte flag = 0;
Uri hl = null;
var excludeFormulas = (excelRangeCopyOptionFlags ?? 0 & ExcelRangeCopyOptionFlags.ExcludeFormulas) == ExcelRangeCopyOptionFlags.ExcludeFormulas;
var copiedValue = new List <CopiedCell>();
ExcelStyles sourceStyles = worksheet.Workbook.Styles, styles = Destination._worksheet.Workbook.Styles;
Dictionary <int, int> styleCashe = new Dictionary <int, int>();
bool sameWorkbook = Destination._worksheet.Workbook == sourceRange._worksheet.Workbook;
var cse = new CellStoreEnumerator <ExcelValue>(worksheet._values, fromRow, fromCol, toRow, toCol);
while (cse.Next())
{
var row = cse.Row;
var col = cse.Column; //Issue 15070
var cell = new CopiedCell
{
Row = Destination._fromRow + (row - fromRow),
Column = Destination._fromCol + (col - fromCol),
Value = cse.Value._value
};
if (!excludeFormulas && worksheet._formulas.Exists(row, col, ref o))
{
if (o is int)
{
cell.Formula = worksheet.GetFormula(cse.Row, cse.Column);
if (worksheet._flags.GetFlagValue(cse.Row, cse.Column, CellFlags.ArrayFormula))
{
Destination._worksheet._flags.SetFlagValue(cse.Row, cse.Column, true, CellFlags.ArrayFormula);
}
}
else
{
cell.Formula = o;
}
}
if (worksheet.ExistsStyleInner(row, col, ref i))
{
if (sameWorkbook)
{
cell.StyleID = i;
}
else
{
if (styleCashe.ContainsKey(i))
{
i = styleCashe[i];
}
else
{
var oldStyleID = i;
i = styles.CloneStyle(sourceStyles, i);
styleCashe.Add(oldStyleID, i);
}
cell.StyleID = i;
}
}
if (worksheet._hyperLinks.Exists(row, col, ref hl))
{
cell.HyperLink = hl;
}
// Will just be null if no comment exists.
cell.Comment = worksheet.Cells[cse.Row, cse.Column].Comment;
if (worksheet._flags.Exists(row, col, ref flag))
{
cell.Flag = flag;
}
copiedValue.Add(cell);
}
//Copy styles with no cell value
var cses = new CellStoreEnumerator <ExcelValue>(worksheet._values, fromRow, fromCol, toRow, toCol);
while (cses.Next())
{
if (!worksheet.ExistsValueInner(cses.Row, cses.Column))
{
var row = Destination._fromRow + (cses.Row - fromRow);
var col = Destination._fromCol + (cses.Column - fromCol);
var cell = new CopiedCell
{
Row = row,
Column = col,
Value = null
};
i = cses.Value._styleId;
if (sameWorkbook)
{
cell.StyleID = i;
}
else
{
if (styleCashe.ContainsKey(i))
{
i = styleCashe[i];
}
else
{
var oldStyleID = i;
i = styles.CloneStyle(sourceStyles, i);
styleCashe.Add(oldStyleID, i);
}
cell.StyleID = i;
}
copiedValue.Add(cell);
}
}
return(copiedValue);
}
private void SetStyleFullRow(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws.GetStyleInner(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value._styleId;
if (s == 0)
{
continue;
}
var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws.ExistsStyleInner(rowNum, col))
{
ws.SetStyleInner(rowNum, col, s);
}
}
}
}
ws.SetStyleInner(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellStoreEnumerator <ExcelValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value._styleId;
if (s == 0)
{
continue;
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(cse2.Row, cse2.Column, newId);
}
}
//Update cells with styled rows
cse2 = new CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, address._toCol);
while (cse2.Next())
{
if (cse2.Value._styleId == 0)
{
continue;
}
for (int r = address._fromRow; r <= address._toRow; r++)
{
if (!ws.ExistsStyleInner(r, cse2.Column))
{
var s = cse2.Value._styleId;
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(r, cse2.Column, newId);
}
}
}
}
}
private void RemoveUnusedStyles()
{
CellXfs[0].useCnt = 1; //First item is allways used.
foreach (ExcelWorksheet sheet in _wb.Worksheets)
{
if (sheet is ExcelChartsheet)
{
continue;
}
var cse = new CellStoreEnumerator <ExcelValue>(sheet._values);
while (cse.Next())
{
var v = cse.Value._styleId;
if (v >= 0)
{
CellXfs[v].useCnt++;
}
}
}
foreach (ExcelNamedStyleXml ns in NamedStyles)
{
CellStyleXfs[ns.StyleXfId].useCnt++;
}
foreach (ExcelXfs xf in CellXfs)
{
if (xf.useCnt > 0)
{
if (xf.FontId >= 0)
{
Fonts[xf.FontId].useCnt++;
}
if (xf.FillId >= 0)
{
Fills[xf.FillId].useCnt++;
}
if (xf.BorderId >= 0)
{
Borders[xf.BorderId].useCnt++;
}
}
}
foreach (ExcelXfs xf in CellStyleXfs)
{
if (xf.useCnt > 0)
{
if (xf.FontId >= 0)
{
Fonts[xf.FontId].useCnt++;
}
if (xf.FillId >= 0)
{
Fills[xf.FillId].useCnt++;
}
if (xf.BorderId >= 0)
{
Borders[xf.BorderId].useCnt++;
}
}
}
}
private void SetStyleFullColumn(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
column = (ExcelColumn)o;
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumMax + 1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
column = newColumn;
}
break;
}
else
{
column = (ws.GetValueInner(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellStoreEnumerator <ExcelValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column &&
cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellStoreEnumerator <ExcelValue>(ws._values, 1, 0, address._toRow, 0);
while (cse.Next())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (int c = address._fromCol; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn)
{
ValidateInsertColumn(ws, columnFrom, columns);
lock (ws)
{
InsertCellStores(ws, 0, columnFrom, 0, columns);
InsertColumnFormulas(ws, columnFrom, columns);
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false);
var csec = new CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
var lst = new List <ExcelColumn>();
foreach (var val in csec)
{
var col = val._value;
if (col is ExcelColumn)
{
lst.Add((ExcelColumn)col);
}
}
for (int i = lst.Count - 1; i >= 0; i--)
{
var c = lst[i];
if (c._columnMin >= columnFrom)
{
if (c._columnMin + columns <= ExcelPackage.MaxColumns)
{
c._columnMin += columns;
}
else
{
c._columnMin = ExcelPackage.MaxColumns;
}
if (c._columnMax + columns <= ExcelPackage.MaxColumns)
{
c._columnMax += columns;
}
else
{
c._columnMax = ExcelPackage.MaxColumns;
}
}
else if (c._columnMax >= columnFrom)
{
var cc = c._columnMax - columnFrom;
c._columnMax = columnFrom - 1;
ws.CopyColumn(c, columnFrom + columns, columnFrom + columns + cc);
}
}
//Copy style from another column?
if (copyStylesFromColumn > 0)
{
if (copyStylesFromColumn >= columnFrom)
{
copyStylesFromColumn += columns;
}
//Get styles to a cached list,
var l = new List <int[]>();
var sce = new CellStoreEnumerator <ExcelValue>(ws._values, 0, copyStylesFromColumn, ExcelPackage.MaxRows, copyStylesFromColumn);
lock (sce)
{
while (sce.Next())
{
if (sce.Value._styleId == 0)
{
continue;
}
l.Add(new int[] { sce.Row, sce.Value._styleId });
}
}
//Set the style id's from the list.
foreach (var sc in l)
{
for (var c = 0; c < columns; c++)
{
if (sc[0] == 0)
{
var col = ws.Column(columnFrom + c); //Create the column
col.StyleID = sc[1];
}
else
{
ws.SetStyleInner(sc[0], columnFrom + c, sc[1]);
}
}
}
var newOutlineLevel = ws.Column(copyStylesFromColumn).OutlineLevel;
for (var c = 0; c < columns; c++)
{
ws.Column(columnFrom + c).OutlineLevel = newOutlineLevel;
}
}
//Adjust tables
foreach (var tbl in ws.Tables)
{
if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
InsertTableColumns(columnFrom, columns, tbl);
}
tbl.Address = tbl.Address.AddColumn(columnFrom, columns);
}
foreach (var ptbl in ws.PivotTables)
{
if (columnFrom <= ptbl.Address.End.Column)
{
ptbl.Address = ptbl.Address.AddColumn(columnFrom, columns);
}
if (columnFrom <= ptbl.CacheDefinition.SourceRange.End.Column)
{
if (ptbl.CacheDefinition.CacheSource == eSourceType.Worksheet)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddColumn(columnFrom, columns).Address;
}
}
}
//Adjust DataValidation
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
var newAddr = addr.AddColumn(columnFrom, columns).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
// Update cross-sheet references.
foreach (var sheet in ws.Workbook.Worksheets.Where(otherSheet => otherSheet != ws))
{
sheet.UpdateSheetNameInFormulas(ws.Name, 0, 0, columnFrom, columns);
}
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns);
}
}
internal static void InsertRow(ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow)
{
ValidateInsertRow(ws, rowFrom, rows);
lock (ws)
{
InsertCellStores(ws, rowFrom, 0, rows, 0);
foreach (var f in ws._sharedFormulas.Values)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromRow >= rowFrom)
{
a._fromRow += rows;
a._toRow += rows;
}
else if (a._toRow >= rowFrom)
{
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0, ws.Name, ws.Name);
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), rows, 0, rowFrom, 0, ws.Name, ws.Name);
}
}
WorksheetRangeHelper.FixMergedCellsRow(ws, rowFrom, rows, false);
if (copyStylesFromRow > 0)
{
CopyFromStyleRow(ws, rowFrom, rows, copyStylesFromRow);
}
foreach (var tbl in ws.Tables)
{
tbl.Address = tbl.Address.AddRow(rowFrom, rows);
}
foreach (var ptbl in ws.PivotTables)
{
ptbl.Address = ptbl.Address.AddRow(rowFrom, rows);
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddRow(rowFrom, rows).Address;
}
//Update data validation references
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
var newAddr = addr.AddRow(rowFrom, rows).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
// Update sheetname in cross reference formulas
foreach (var sheet in ws.Workbook.Worksheets.Where(otherWs => otherWs != ws))
{
sheet.UpdateSheetNameInFormulas(ws.Name, rowFrom, rows, 0, 0);
}
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, rows);
}
}