internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn)
{
ValidateInsertColumn(ws, columnFrom, columns);
lock (ws)
{
InsertCellStores(ws, 0, columnFrom, 0, columns);
FixFormulasInsertColumn(ws, columnFrom, columns);
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false);
AdjustColumns(ws, columnFrom, columns);
CopyStylesFromColumn(ws, columnFrom, columns, copyStylesFromColumn);
//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;
}
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeInsert.Right);
InsertFilterAddress(range, effectedAddress, eShiftTypeInsert.Right);
InsertSparkLinesAddress(range, eShiftTypeInsert.Right, effectedAddress);
//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);
}
}
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns);
}
}
internal static void Insert(ExcelRangeBase range, eShiftTypeInsert shift, bool styleCopy)
{
ValidateInsert(range, shift);
var effectedAddress = GetEffectedRange(range, shift);
WorksheetRangeHelper.ValidateIfInsertDeleteIsPossible(range, effectedAddress, GetEffectedRange(range, shift, 1), true);
var ws = range.Worksheet;
lock (ws)
{
var styleList = GetStylesForRange(range, shift);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
InsertCellStores(range._worksheet, range._fromRow, range._fromCol, range.Rows, range.Columns, range._toCol);
}
else
{
InsertCellStoreShiftRight(range._worksheet, range);
}
AdjustFormulasInsert(range, effectedAddress, shift);
InsertFilterAddress(range, effectedAddress, shift);
WorksheetRangeHelper.FixMergedCells(ws, range, shift);
if (styleCopy)
{
SetStylesForRange(range, shift, styleList);
}
InsertTableAddress(ws, range, shift, effectedAddress);
InsertPivottableAddress(ws, range, shift, effectedAddress);
//Update data validation references
foreach (var dv in ws.DataValidations)
{
((ExcelDataValidation)dv).SetAddress(InsertSplitAddress(dv.Address, range, effectedAddress, shift).Address);
}
//Update Conditional formatting references
foreach (var cf in ws.ConditionalFormatting)
{
((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(InsertSplitAddress(cf.Address, range, effectedAddress, shift).Address);
}
InsertSparkLinesAddress(range, shift, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
WorksheetRangeHelper.AdjustDrawingsRow(ws, range._fromRow, range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(ws, range._fromCol, range.Columns, range._fromRow, range._toRow);
}
}
}
internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn)
{
ValidateInsertColumn(ws, columnFrom, columns);
lock (ws)
{
InsertCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasInsertColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false);
AdjustColumns(ws, columnFrom, columns);
CopyStylesFromColumn(ws, columnFrom, columns, copyStylesFromColumn);
//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);
}
}
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns);
}
}
private static void AdjustDrawings(ExcelRangeBase range, eShiftTypeDelete shift)
{
if (shift == eShiftTypeDelete.Up)
{
WorksheetRangeHelper.AdjustDrawingsRow(range.Worksheet, range._fromRow, -range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(range.Worksheet, range._fromCol, -range.Columns, range._fromRow, range._toRow);
}
}
internal static void Insert(ExcelRangeBase range, eShiftTypeInsert shift, bool styleCopy)
{
ValidateInsert(range, shift);
var effectedAddress = GetEffectedRange(range, shift);
WorksheetRangeHelper.ValidateIfInsertDeleteIsPossible(range, effectedAddress, GetEffectedRange(range, shift, 1), true);
var ws = range.Worksheet;
lock (ws)
{
var styleList = GetStylesForRange(range, shift);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
InsertCellStores(range._worksheet, range._fromRow, range._fromCol, range.Rows, range.Columns, range._toCol);
}
else
{
InsertCellStoreShiftRight(range._worksheet, range);
}
AdjustFormulasInsert(range, effectedAddress, shift);
InsertFilterAddress(range, effectedAddress, shift);
WorksheetRangeHelper.FixMergedCells(ws, range, shift);
if (styleCopy)
{
SetStylesForRange(range, shift, styleList);
}
InsertTableAddress(ws, range, shift, effectedAddress);
InsertPivottableAddress(ws, range, shift, effectedAddress);
InsertDataValidation(range, shift, effectedAddress, ws);
InsertConditionalFormatting(range, shift, effectedAddress, ws);
InsertSparkLinesAddress(range, shift, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
WorksheetRangeHelper.AdjustDrawingsRow(ws, range._fromRow, range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(ws, range._fromCol, range.Columns, range._fromRow, range._toRow);
}
}
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
//Set previous column Max to Row before if it spans the deleted column range.
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;
}
}
}
DeleteCellStores(ws, 0, columnFrom, 0, columns, true);
AdjustFormulasColumn(ws, columnFrom, columns);
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column > columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
}
//Adjust DataValidation
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
if (addr.Start.Column > columnFrom + columns)
{
var newAddr = addr.DeleteColumn(columnFrom, columns).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
}
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
AdjustColumnMinMaxDelete(ws, columnFrom, columns);
var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
}
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column >= columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
DeleteDataValidations(range, eShiftTypeDelete.Left, ws, effectedAddress);
DeleteConditionalFormatting(range, eShiftTypeDelete.Left, ws, effectedAddress);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
//Set previous column Max to Row before if it spans the deleted column range.
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 delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column > columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
}
//Adjust DataValidation
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
if (addr.Start.Column > columnFrom + columns)
{
var newAddr = addr.DeleteColumn(columnFrom, columns).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}
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);
}
}