/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <param name="fpb"></param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb)
{
XSSFSheet sheet = (XSSFSheet)Sheet;
CT_CellFormula f = sheet.GetSharedFormula(si);
if (f == null)
{
throw new InvalidOperationException(
"Master cell of a shared formula with sid=" + si + " was not found");
}
String sharedFormula = f.Value;
//Range of cells which the shared formula applies to
String sharedFormulaRange = f.@ref;
CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange);
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return(FormulaRenderer.ToFormulaString(fpb, fmla));
}
public void TestArrayFormulas()
{
int rownum = 4;
int colnum = 4;
FormulaRecord fr = new FormulaRecord();
fr.Row = (rownum);
fr.Column = ((short)colnum);
FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty());
Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) };
agg.SetParsedExpression(ptgsForCell);
String formula = "SUM(A1:A3*B1:B3)";
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.Array, 0);
agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs);
Assert.IsTrue(agg.IsPartOfArrayFormula);
Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString());
Ptg[] ptg = agg.FormulaTokens;
String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg);
Assert.AreEqual(formula, fmlaSer);
agg.RemoveArrayFormula(rownum, colnum);
Assert.IsFalse(agg.IsPartOfArrayFormula);
}
/// <summary>
/// Shift a formula using the supplied FormulaShifter
/// </summary>
/// <param name="row">the row of the cell this formula belongs to. Used to get a reference to the parent workbook.</param>
/// <param name="formula">the formula to shift</param>
/// <param name="formulaShifter">the FormulaShifter object that operates on the parsed formula tokens</param>
/// <returns>the shifted formula if the formula was changed, null if the formula wasn't modified</returns>
public static String ShiftFormula(IRow row, String formula, FormulaShifter formulaShifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
int rowIndex = row.RowNum;
HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb);
try
{
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, rowIndex);
String shiftedFmla;
if (formulaShifter.AdjustFormula(ptgs, sheetIndex))
{
shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
else
{
shiftedFmla = formula;
}
return(shiftedFmla);
}
catch (FormulaParseException fpe)
{
// Log, but don't change, rather than breaking
log.Log(POILogger.ERROR, "Error shifting formula on row " + row.RowNum.ToString(), fpe);
return(formula);
}
}
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f == null)
{
return;
}
string formula = f.Value;
if (formula == null || formula.Length <= 0)
{
return;
}
int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (formula.Equals(formulaString))
{
return;
}
f.Value = formulaString;
}
public void TestRead()
{
String hex =
"21 02 25 00 01 00 01 00 01 01 00 00 00 00 00 00 " +
"17 00 65 00 00 01 00 02 C0 02 C0 65 00 00 01 00 " +
"03 C0 03 C0 04 62 01 07 00";
byte[] data = HexRead.ReadFromString(hex);
RecordInputStream in1 = TestcaseRecordInputStream.Create(data);
ArrayRecord r1 = new ArrayRecord(in1);
CellRangeAddress8Bit range = r1.Range;
Assert.AreEqual(1, range.FirstColumn);
Assert.AreEqual(1, range.LastColumn);
Assert.AreEqual(1, range.FirstRow);
Assert.AreEqual(1, range.LastRow);
Ptg[] ptg = r1.FormulaTokens;
Assert.AreEqual(FormulaRenderer.ToFormulaString(null, ptg), "MAX(C1:C2-D1:D2)");
//construct a new ArrayRecord with the same contents as r1
Ptg[] fmlaPtg = FormulaParser.Parse("MAX(C1:C2-D1:D2)", null, FormulaType.Array, 0);
ArrayRecord r2 = new ArrayRecord(Formula.Create(fmlaPtg), new CellRangeAddress8Bit(1, 1, 1, 1));
byte[] ser = r2.Serialize();
//serialize and check that the data is the same as in r1
Assert.AreEqual(HexDump.ToHex(data), HexDump.ToHex(ser));
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
name.RefersToFormula = (updatedFormula);
}
}
}
private static string CopyFormula(string formula, ISheet sheet, int targetIndex)
{
var workbookWrapper = XSSFEvaluationWorkbook.Create((XSSFWorkbook)sheet.Workbook);
var ptgs = FormulaParser.Parse(formula, workbookWrapper, FormulaType.Cell, sheet.Workbook.GetSheetIndex(sheet));
foreach (var ptg in ptgs)
{
if (ptg is RefPtgBase refs && refs.IsRowRelative)
{
refs.Row = targetIndex;
}
}
return(FormulaRenderer.ToFormulaString(workbookWrapper, ptgs));
}
private static string ShiftFormula(XSSFRow row, string formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook workbook = sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex(sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
string str = (string)null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
str = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
}
return(str);
}
/**
* Shift a formula using the supplied FormulaShifter
*
* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
* @param formula the formula to shift
* @param Shifter the FormulaShifter object that operates on the Parsed formula tokens
* @return the Shifted formula if the formula was Changed,
* <code>null</code> if the formula wasn't modified
*/
private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
String ShiftedFmla = null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
return(ShiftedFmla);
}
private string ConvertSharedFormula(int si)
{
XSSFSheet sheet = (XSSFSheet)this.Sheet;
CT_CellFormula sharedFormula = sheet.GetSharedFormula(si);
if (sharedFormula == null)
{
throw new InvalidOperationException("Master cell of a shared formula with sid=" + (object)si + " was not found");
}
string formula = sharedFormula.Value;
CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(sharedFormula.@ref);
int sheetIndex = sheet.Workbook.GetSheetIndex((ISheet)sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(sheet.Workbook);
Ptg[] ptgs = new SharedFormula(SpreadsheetVersion.EXCEL2007).ConvertSharedFormulas(FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex), this.RowIndex - cellRangeAddress.FirstRow, this.ColumnIndex - cellRangeAddress.FirstColumn);
return(FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs));
}
public void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook workbook = this.sheet.Workbook;
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
for (int nameIndex = 0; nameIndex < workbook.NumberOfNames; ++nameIndex)
{
IName nameAt = workbook.GetNameAt(nameIndex);
string refersToFormula = nameAt.RefersToFormula;
int sheetIndex = nameAt.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.NAMEDRANGE, sheetIndex);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
nameAt.RefersToFormula = formulaString;
}
}
}
private static FormulaValuePair toFormulaString(Ptg[] ptgs, IFormulaRenderingWorkbook book)
{
FormulaValuePair pair = new FormulaValuePair();
if (ptgs != null && ptgs.Length > 0)
{
String aString = FormulaRenderer.ToFormulaString(book, ptgs);
if (ptgs.Length == 1 && ptgs[0].GetType() == typeof(NumberPtg))
{
pair._value = aString;
}
else
{
pair._formula = aString;
}
}
return(pair);
}
/**
* Updated named ranges
*/
public override void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook wb = sheet.Workbook;
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
foreach (IName name in wb.GetAllNames())
{
String formula = name.RefersToFormula;
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex, -1);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
name.RefersToFormula = shiftedFmla;
}
}
}
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
string refersToFormula = name.RefersToFormula;
if (refersToFormula == null)
{
return;
}
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.NAMEDRANGE, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (refersToFormula.Equals(formulaString))
{
return;
}
name.RefersToFormula = formulaString;
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/**
* Updated named ranges
*/
public void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook wb = sheet.Workbook;
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
for (int i = 0; i < wb.NumberOfNames; i++)
{
IName name = wb.GetNameAt(i);
String formula = name.RefersToFormula;
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
name.RefersToFormula = shiftedFmla;
}
}
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, String oldName, String newName)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex, -1);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula))
{
name.RefersToFormula = (updatedFormula);
}
}
}
public void TestConvertSharedFormulas()
{
IWorkbook wb = new HSSFWorkbook();
HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create(wb);
Ptg[] sharedFormula, ConvertedFormula;
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
sharedFormula = FormulaParser.Parse("A2", fpb, FormulaType.Cell, -1, -1);
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//conversion relative to [0,0] should return the original formula
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A2");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//one row down
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A3");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//one row down and one cell right
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "B3");
sharedFormula = FormulaParser.Parse("SUM(A1:C1)", fpb, FormulaType.Cell, -1, -1);
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A1:C1)");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A2:C2)");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(B2:D2)");
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, String oldName, String newName)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/**
* Shift a formula using the supplied FormulaShifter
*
* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
* @param formula the formula to shift
* @param Shifter the FormulaShifter object that operates on the Parsed formula tokens
* @return the Shifted formula if the formula was Changed,
* <code>null</code> if the formula wasn't modified
*/
private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
try
{
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
String ShiftedFmla = null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
return(ShiftedFmla);
}
catch (FormulaParseException fpe)
{
// Log, but don't change, rather than breaking
Console.WriteLine("Error shifting formula on row {0}, {1}", row.RowNum, fpe);
return(formula);
}
}
/**
* Static method to convert an array of {@link Ptg}s in RPN order
* to a human readable string format in infix mode.
* @param book used for defined names and 3D references
* @param ptgs must not be <c>null</c>
* @return a human readable String
*/
public static String ToFormulaString(HSSFWorkbook book, Ptg[] ptgs)
{
return(FormulaRenderer.ToFormulaString(HSSFEvaluationWorkbook.Create(book), ptgs));
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
List <CT_ConditionalFormatting> cfList = sheet.GetCTWorksheet().conditionalFormatting;
for (int j = 0; cfList != null && j < cfList.Count; j++)
{
CT_ConditionalFormatting cf = cfList[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
foreach (Object stRef in cf.sqref)
{
String[] regions = stRef.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 = 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)
{
cfList.RemoveAt(j);
continue;
}
List <String> refs = new List <String>();
foreach (CellRangeAddress a in temp)
{
refs.Add(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);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook workbook = this.sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex((ISheet)this.sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
List <CT_ConditionalFormatting> conditionalFormatting1 = this.sheet.GetCTWorksheet().conditionalFormatting;
for (int index1 = 0; conditionalFormatting1 != null && index1 < conditionalFormatting1.Count; ++index1)
{
CT_ConditionalFormatting conditionalFormatting2 = conditionalFormatting1[index1];
List <CellRangeAddress> cellRangeAddressList1 = new List <CellRangeAddress>();
foreach (object obj in conditionalFormatting2.sqref)
{
string str = obj.ToString();
char[] chArray = new char[1] {
' '
};
foreach (string reference in str.Split(chArray))
{
cellRangeAddressList1.Add(CellRangeAddress.ValueOf(reference));
}
}
bool flag = false;
List <CellRangeAddress> cellRangeAddressList2 = new List <CellRangeAddress>();
for (int index2 = 0; index2 < cellRangeAddressList1.Count; ++index2)
{
CellRangeAddress cra = cellRangeAddressList1[index2];
CellRangeAddress cellRangeAddress = XSSFRowShifter.ShiftRange(Shifter, cra, sheetIndex);
if (cellRangeAddress == null)
{
flag = true;
}
else
{
cellRangeAddressList2.Add(cellRangeAddress);
if (cellRangeAddress != cra)
{
flag = true;
}
}
}
if (flag)
{
if (cellRangeAddressList2.Count == 0)
{
conditionalFormatting1.RemoveAt(index1);
continue;
}
List <string> stringList = new List <string>();
foreach (CellRangeAddress cellRangeAddress in cellRangeAddressList2)
{
stringList.Add(cellRangeAddress.FormatAsString());
}
conditionalFormatting2.sqref = stringList;
}
foreach (CT_CfRule ctCfRule in conditionalFormatting2.cfRule)
{
List <string> formula = ctCfRule.formula;
for (int index2 = 0; index2 < formula.Count; ++index2)
{
Ptg[] ptgs = FormulaParser.Parse(formula[index2], (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
formula[index2] = formulaString;
}
}
}
}
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
CT_Worksheet ctWorksheet = sheet.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 = 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);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
