/// <summary> /// Shift the Hyperlink anchors(not the hyperlink text, even if the hyperlink is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks do not track the content they point to. /// </summary> /// <param name="sheet"></param> /// <param name="shifter"></param> public static void UpdateHyperlinks(ISheet sheet, FormulaShifter shifter) { XSSFSheet xsheet = (XSSFSheet)sheet; int sheetIndex = xsheet.GetWorkbook().GetSheetIndex(sheet); List <IHyperlink> hyperlinkList = sheet.GetHyperlinkList(); foreach (IHyperlink hyperlink1 in hyperlinkList) { XSSFHyperlink hyperlink = hyperlink1 as XSSFHyperlink; String cellRef = hyperlink.CellRef; CellRangeAddress cra = CellRangeAddress.ValueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.ShiftRange(shifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten hyperlink.SetCellReference(shiftedRange.FormatAsString()); } } }
public static void UpdateConditionalFormatting(ISheet sheet, FormulaShifter Shifter) { XSSFSheet xsheet = (XSSFSheet)sheet; XSSFWorkbook wb = xsheet.Workbook as XSSFWorkbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); CT_Worksheet ctWorksheet = xsheet.GetCTWorksheet(); List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting; // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--) { CT_ConditionalFormatting cf = conditionalFormattingArray[j]; List <CellRangeAddress> cellRanges = new List <CellRangeAddress>(); String[] regions = cf.sqref.ToString().Split(new char[] { ' ' }); for (int i = 0; i < regions.Length; i++) { cellRanges.Add(CellRangeAddress.ValueOf(regions[i])); } bool Changed = false; List <CellRangeAddress> temp = new List <CellRangeAddress>(); for (int i = 0; i < cellRanges.Count; i++) { CellRangeAddress craOld = cellRanges[i]; CellRangeAddress craNew = BaseRowColShifter.ShiftRange(Shifter, craOld, sheetIndex); if (craNew == null) { Changed = true; continue; } temp.Add(craNew); if (craNew != craOld) { Changed = true; } } if (Changed) { int nRanges = temp.Count; if (nRanges == 0) { conditionalFormattingArray.RemoveAt(j); continue; } string refs = string.Empty; foreach (CellRangeAddress a in temp) { if (refs.Length == 0) { refs = a.FormatAsString(); } else { refs += " " + a.FormatAsString(); } } cf.sqref = refs; } foreach (CT_CfRule cfRule in cf.cfRule) { List <String> formulas = cfRule.formula; for (int i = 0; i < formulas.Count; i++) { String formula = formulas[i]; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, -1); if (Shifter.AdjustFormula(ptgs, sheetIndex)) { String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); formulas[i] = ShiftedFmla; } } } } }