/// <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;
}
}
}
}
}