private static void InsertSparkLinesAddress(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress) { foreach (var slg in range.Worksheet.SparklineGroups) { if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside) { string address; if (shift == eShiftTypeInsert.Down) { address = slg.DateAxisRange.AddRow(range._fromRow, range.Rows).Address; } else { address = slg.DateAxisRange.AddColumn(range._fromCol, range.Columns).Address; } slg.DateAxisRange = range.Worksheet.Cells[address]; } foreach (var sl in slg.Sparklines) { if (shift == eShiftTypeInsert.Down) { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow)) { sl.RangeAddress = sl.RangeAddress.AddRow(range._fromRow, range.Rows); } if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol) { sl.Cell = new ExcelCellAddress(sl.Cell.Row + range.Rows, sl.Cell.Column); } } else { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol)) { sl.RangeAddress = sl.RangeAddress.AddColumn(range._fromCol, range.Columns); } if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow) { sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column + range.Columns); } } } } }
internal static void ValidateIfInsertDeleteIsPossible(ExcelRangeBase range, ExcelAddressBase effectedAddress, ExcelAddressBase effectedAddressTable, bool insert) { //Validate autofilter if (range.Worksheet.AutoFilterAddress != null && effectedAddress.Collide(range.Worksheet.AutoFilterAddress) == ExcelAddressBase.eAddressCollition.Partly && range.Worksheet.AutoFilterAddress.CollideFullRowOrColumn(range) == false) { throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with the worksheets autofilter."); } //Validate merged Cells foreach (var a in range.Worksheet.MergedCells) { var mc = new ExcelAddressBase(a); if (effectedAddress.Collide(mc) == ExcelAddressBase.eAddressCollition.Partly) { throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with merged range {a}"); } } //Validate pivot tables Cells foreach (var pt in range.Worksheet.PivotTables) { if (effectedAddress.Collide(pt.Address) == ExcelAddressBase.eAddressCollition.Partly) { throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with pivot table {pt.Name}"); } } //Validate tables Cells foreach (var t in range.Worksheet.Tables) { if (effectedAddressTable.Collide(t.Address) == ExcelAddressBase.eAddressCollition.Partly && t.Address.CollideFullRowOrColumn(range) == false) { throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with table {t.Name}"); } } }
private static ExcelAddressBase DeleteSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift) { if (address.CollideFullRowOrColumn(range)) { if (range.CollideFullColumn(address._fromCol, address._toCol)) { return(address.DeleteColumn(range._fromCol, range.Columns)); } else { return(address.DeleteRow(range._fromRow, range.Rows)); } } else { var collide = effectedAddress.Collide(address); if (collide == ExcelAddressBase.eAddressCollition.Partly) { var addressToShift = effectedAddress.Intersect(address); var shiftedAddress = ShiftAddress(addressToShift, range, shift); var newAddress = ""; if (address._fromRow < addressToShift._fromRow) { newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ","; } if (address._fromCol < addressToShift._fromCol) { var fromRow = Math.Max(address._fromRow, addressToShift._fromRow); newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ","; } if (shiftedAddress != null) { newAddress += $"{shiftedAddress.Address},"; } if (address._toRow > addressToShift._toRow) { newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ","; } if (address._toCol > addressToShift._toCol) { newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ","; } return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } else if (collide != ExcelAddressBase.eAddressCollition.No) { return(ShiftAddress(address, range, shift)); } } return(address); }
private static void InsertFilterAddress(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift) { var ws = range.Worksheet; if (ws.AutoFilterAddress != null && effectedAddress.Collide(ws.AutoFilterAddress) != ExcelAddressBase.eAddressCollition.No) { if (shift == eShiftTypeInsert.Down) { ws.AutoFilterAddress = ws.AutoFilterAddress.AddRow(range._fromRow, range.Rows); } else { ws.AutoFilterAddress = ws.AutoFilterAddress.AddColumn(range._fromCol, range.Columns); } } }
private static void DeleteFilterAddress(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift) { var ws = range.Worksheet; if (ws.AutoFilterAddress != null && effectedAddress.Collide(ws.AutoFilterAddress) != ExcelAddressBase.eAddressCollition.No) { var firstRow = new ExcelAddress(ws.AutoFilterAddress._fromRow, ws.AutoFilterAddress._fromCol, ws.AutoFilterAddress._fromRow, ws.AutoFilterAddress._toCol); if (range.Collide(firstRow, true) >= ExcelAddressBase.eAddressCollition.Inside) { ws.AutoFilterAddress = null; } else if (shift == eShiftTypeDelete.Up) { ws.AutoFilterAddress = ws.AutoFilterAddress.DeleteRow(range._fromRow, range.Rows); } else { ws.AutoFilterAddress = ws.AutoFilterAddress.DeleteColumn(range._fromCol, range.Columns); } } }
private static bool ConvertEffectedSharedFormulaIfReferenceWithinRange(ExcelWorksheet ws, ExcelAddressBase delRange, ExcelWorksheet.Formulas sf, string wsName) { bool doConvertSF = false; var sfAddress = new ExcelAddressBase(sf.Address); sf.SetTokens(ws.Name); foreach (var token in sf.Tokens) { if (token.TokenTypeIsSet(TokenType.ExcelAddress)) { //Check if the address for the entire shared formula collides with the deleted address. var tokenAddress = new ExcelAddressBase(token.Value); if ((ws.Name.Equals(wsName, StringComparison.CurrentCultureIgnoreCase) && string.IsNullOrEmpty(tokenAddress.WorkSheetName)) || (!string.IsNullOrEmpty(tokenAddress.WorkSheetName) && tokenAddress.WorkSheetName.Equals(wsName, StringComparison.CurrentCultureIgnoreCase))) { if (tokenAddress._toRowFixed == false) { tokenAddress._toRow += (sfAddress.Rows - 1); } if (tokenAddress._toColFixed == false) { tokenAddress._toCol += (sfAddress.Columns - 1); } if (tokenAddress.Collide(delRange, true) != ExcelAddressBase.eAddressCollition.No) //Shared Formula address is effected. { doConvertSF = true; break; } } } } if (doConvertSF) { ConvertSharedFormulaToCellFormula(ws, sf, sfAddress); } return(doConvertSF); }
private static ExcelAddressBase InsertSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift) { var collide = effectedAddress.Collide(address); if (collide == ExcelAddressBase.eAddressCollition.Partly) { var addressToShift = effectedAddress.Intersect(address); var shiftedAddress = ShiftAddress(addressToShift, range, shift); var newAddress = ""; if (address._fromRow < addressToShift._fromRow) { newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ","; } if (address._fromCol < addressToShift._fromCol) { var fromRow = Math.Max(address._fromRow, addressToShift._fromRow); newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ","; } newAddress += $"{shiftedAddress},"; if (address._toRow > addressToShift._toRow) { newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ","; } if (address._toCol > addressToShift._toCol) { newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ","; } return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } else if (collide != ExcelAddressBase.eAddressCollition.No) { return(ShiftAddress(address, range, shift)); } return(address); }
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 void DeleteSparkLinesAddress(ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress) { var delSparklineGroups = new List <ExcelSparklineGroup>(); foreach (var slg in range.Worksheet.SparklineGroups) { if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside) { string address; if (shift == eShiftTypeDelete.Up) { address = slg.DateAxisRange.DeleteRow(range._fromRow, range.Rows).Address; } else { address = slg.DateAxisRange.DeleteColumn(range._fromCol, range.Columns).Address; } slg.DateAxisRange = address == null? null : range.Worksheet.Cells[address]; } var delSparklines = new List <ExcelSparkline>(); foreach (var sl in slg.Sparklines) { if (range.Collide(sl.Cell.Row, sl.Cell.Column) >= ExcelAddressBase.eAddressCollition.Inside) { delSparklines.Add(sl); } else if (shift == eShiftTypeDelete.Up) { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow)) { sl.RangeAddress = sl.RangeAddress.DeleteRow(range._fromRow, range.Rows); } if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol) { sl.Cell = new ExcelCellAddress(sl.Cell.Row - range.Rows, sl.Cell.Column); } } else { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol)) { sl.RangeAddress = sl.RangeAddress.DeleteColumn(range._fromCol, range.Columns); } if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow) { sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column - range.Columns); } } } delSparklines.ForEach(x => slg.Sparklines.Remove(x)); if (slg.Sparklines.Count == 0) { delSparklineGroups.Add(slg); } } delSparklineGroups.ForEach(x => range.Worksheet.SparklineGroups.Remove(x)); }