/**
* Updates named ranges due to moving of cells
*/
public void UpdateNamesAfterCellShift(FormulaShifter shifter)
{
for (int i = 0; i < NumNames; ++i)
{
NameRecord nr = GetNameRecord(i);
Ptg[] ptgs = nr.NameDefinition;
if (shifter.AdjustFormula(ptgs, nr.SheetNumber))
{
nr.NameDefinition = ptgs;
}
}
}
public void UpdateFormulasAfterRowShift(FormulaShifter shifter, int currentExternSheetIndex)
{
for (int i = 0; i < records.Length; i++)
{
CellValueRecordInterface[] rowCells = records[i];
if (rowCells == null)
{
continue;
}
for (int j = 0; j < rowCells.Length; j++)
{
CellValueRecordInterface cell = rowCells[j];
if (cell is FormulaRecordAggregate)
{
FormulaRecord fr = ((FormulaRecordAggregate)cell).FormulaRecord;
Ptg[] ptgs = fr.ParsedExpression; // needs clone() inside this getter?
if (shifter.AdjustFormula(ptgs, currentExternSheetIndex))
{
fr.ParsedExpression = (ptgs);
}
}
}
}
}
/**
* Updates formulas in cells and conditional formats due to moving of cells
* @param externSheetIndex the externSheet index of this sheet
*/
public void UpdateFormulasAfterCellShift(FormulaShifter shifter, int externSheetIndex)
{
RowsAggregate.UpdateFormulasAfterRowShift(shifter, externSheetIndex);
if (condFormatting != null)
{
ConditionalFormattingTable.UpdateFormulasAfterCellShift(shifter, externSheetIndex);
}
// TODO - adjust data validations
}
public void UpdateFormulasAfterRowShift(FormulaShifter formulaShifter, int currentExternSheetIndex)
{
_valuesAgg.UpdateFormulasAfterRowShift(formulaShifter, currentExternSheetIndex);
}
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;
}
}
}
}
}
private static CellRangeAddress ShiftRange(FormulaShifter Shifter, CellRangeAddress cra, int currentExternSheetIx)
{
// FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
AreaPtg aptg = new AreaPtg(cra.FirstRow, cra.LastRow, cra.FirstColumn, cra.LastColumn, false, false, false, false);
Ptg[] ptgs = { aptg, };
if (!Shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
return cra;
}
Ptg ptg0 = ptgs[0];
if (ptg0 is AreaPtg)
{
AreaPtg bptg = (AreaPtg)ptg0;
return new CellRangeAddress(bptg.FirstRow, bptg.LastRow, bptg.FirstColumn, bptg.LastColumn);
}
if (ptg0 is AreaErrPtg)
{
return null;
}
throw new InvalidOperationException("Unexpected Shifted ptg class (" + ptg0.GetType().Name + ")");
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
if (f.t == ST_CellFormulaType.shared)
{
int si = (int)f.si;
CT_CellFormula sf = ((XSSFSheet)row.Sheet).GetSharedFormula(si);
sf.Value = (ShiftedFormula);
}
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null) f.@ref = ShiftedRef;
}
}
}
}
/**
* 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;
}
}
/**
* Update formulas.
*/
public void UpdateFormulas(FormulaShifter shifter)
{
//update formulas on the parent sheet
UpdateSheetFormulas(sheet, shifter);
//update formulas on other sheets
IWorkbook wb = sheet.Workbook;
foreach (XSSFSheet sh in wb)
{
if (sheet == sh) continue;
UpdateSheetFormulas(sh, shifter);
}
}
private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
updateRowFormulas(row, Shifter);
}
}
/**
* 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;
}
}
}
/**
* @return <c>false</c> if this whole {@link CFHeaderRecord} / {@link CFRuleRecord}s should be deleted
*/
public bool UpdateFormulasAfterCellShift(FormulaShifter shifter, int currentExternSheetIx)
{
CellRangeAddress[] cellRanges = header.CellRanges;
bool changed = false;
List<CellRangeAddress> temp = new List<CellRangeAddress>();
for (int i = 0; i < cellRanges.Length; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(shifter, craOld, currentExternSheetIx);
if (craNew == null)
{
changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
changed = true;
}
}
if (changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
return false;
}
CellRangeAddress[] newRanges = new CellRangeAddress[nRanges];
newRanges = temp.ToArray();
header.CellRanges = (newRanges);
}
for (int i = 0; i < rules.Count; i++)
{
CFRuleRecord rule = rules[i];
Ptg[] ptgs;
ptgs = rule.ParsedExpression1;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule.ParsedExpression1 = (ptgs);
}
ptgs = rule.ParsedExpression2;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule.ParsedExpression2 = (ptgs);
}
}
return true;
}
/**
* 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;
}
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 UpdateFormulasAfterCellShift(FormulaShifter shifter, int externSheetIndex)
{
for (int i = 0; i < _cfHeaders.Count; i++)
{
CFRecordsAggregate subAgg = (CFRecordsAggregate)_cfHeaders[i];
bool shouldKeep = subAgg.UpdateFormulasAfterCellShift(shifter, externSheetIndex);
if (!shouldKeep)
{
_cfHeaders.RemoveAt(i);
i--;
}
}
}
