/// <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);
}
}
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)
{
FormulaRecordAggregate fra = (FormulaRecordAggregate)cell;
Ptg[] ptgs = fra.FormulaTokens; // needs clone() inside this getter?
Ptg[] ptgs2 = ((FormulaRecordAggregate)cell).FormulaRecord.ParsedExpression; // needs clone() inside this getter?
if (shifter.AdjustFormula(ptgs, currentExternSheetIndex))
{
fra.SetParsedExpression(ptgs);
}
}
}
}
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (XSSFCell xssfCell in row)
{
CT_Cell ctCell = xssfCell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
string formula1 = f.Value;
if (formula1.Length > 0)
{
string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
if (str != null)
{
f.Value = str;
}
}
if (f.isSetRef())
{
string formula2 = f.@ref;
string str = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
if (str != null)
{
f.@ref = str;
}
}
}
}
}
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.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;
}
}
}
}
}
private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
{
foreach (XSSFRow row in sh)
{
this.updateRowFormulas(row, Shifter);
}
}
public static void UpdateSheetFormulas(ISheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
UpdateRowFormulas(row, Shifter);
}
}
private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
updateRowFormulas(row, Shifter);
}
}
/**
* @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>();
foreach (CellRangeAddress craOld in cellRanges)
{
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);
}
foreach (CFRuleBase rule in rules)
{
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);
}
if (rule is CFRule12Record)
{
CFRule12Record rule12 = (CFRule12Record)rule;
ptgs = rule12.ParsedExpressionScale;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule12.ParsedExpressionScale = (ptgs);
}
}
}
return(true);
}
public void UpdateFormulas(FormulaShifter shifter)
{
this.UpdateSheetFormulas(this.sheet, shifter);
foreach (XSSFSheet sh in this.sheet.Workbook)
{
if (this.sheet != sh)
{
this.UpdateSheetFormulas(sh, shifter);
}
}
}
public void TestToString()
{
FormulaShifter shifter = FormulaShifter.CreateForRowShift(0, "sheet", 123, 456, 789,
SpreadsheetVersion.EXCEL2007);
Assert.IsNotNull(shifter);
Assert.IsNotNull(shifter.ToString());
Assert.IsTrue(shifter.ToString().Contains("123"));
Assert.IsTrue(shifter.ToString().Contains("456"));
Assert.IsTrue(shifter.ToString().Contains("789"));
}
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--;
}
}
}
/**
* @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;
ArrayList temp = new ArrayList();
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 = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress));
header.CellRanges = (newRanges);
}
for (int i = 0; i < rules.Count; i++)
{
CFRuleRecord rule = (CFRuleRecord)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);
}
/// <summary>
/// Update the formulas in specified row using the formula shifting policy specified by shifter
/// </summary>
/// <param name="row">the row to update the formulas on</param>
/// <param name="formulaShifter">the formula shifting policy</param>
public static void UpdateRowFormulas(IRow row, FormulaShifter formulaShifter)
{
ISheet sheet = row.Sheet;
foreach (ICell c in row)
{
HSSFCell cell = (HSSFCell)c;
String formula = cell.CellFormula;
if (formula.Length > 0)
{
String shiftedFormula = ShiftFormula(row, formula, formulaShifter);
cell.SetCellFormula(shiftedFormula);
}
}
}
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);
}
/**
* 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);
}
}
public void TestShiftFormulasAddCondFormat_bug46547()
{
// Create a sheet with data validity (similar to bugzilla attachment id=23131).
Sheet sheet = Sheet.CreateSheet();
IList sheetRecs = sheet.Records;
Assert.AreEqual(23, sheetRecs.Count);
FormulaShifter shifter = FormulaShifter.CreateForRowShift(0, 0, 0, 1);
sheet.UpdateFormulasAfterCellShift(shifter, 0);
if (sheetRecs.Count == 24 && sheetRecs[22] is ConditionalFormattingTable)
{
throw new AssertFailedException("Identified bug 46547a");
}
Assert.AreEqual(23, sheetRecs.Count);
}
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;
}
}
}
/**
* 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;
}
}
}
/**
* 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;
}
}
}
public void TestShiftFormulasAddCondFormat_bug46547()
{
// Create a sheet with data validity (similar to bugzilla attachment id=23131).
InternalSheet sheet = InternalSheet.CreateSheet();
IList sheetRecs = sheet.Records;
//Assert.AreEqual(23, sheetRecs.Count);
Assert.AreEqual(24, sheetRecs.Count); //for SheetExtRecord
FormulaShifter shifter = FormulaShifter.CreateForRowShift(0, "", 0, 0, 1, SpreadsheetVersion.EXCEL97);
sheet.UpdateFormulasAfterCellShift(shifter, 0);
if (sheetRecs.Count == 25 && sheetRecs[22] is ConditionalFormattingTable)
{
throw new AssertionException("Identified bug 46547a");
}
//Assert.AreEqual(23, sheetRecs.Count);
Assert.AreEqual(24, sheetRecs.Count); //for SheetExtRecord
}
public void TestInvalidArgument()
{
try
{
FormulaShifter.CreateForRowShift(1, "name", 1, 2, 0, SpreadsheetVersion.EXCEL97);
Assert.Fail("Should catch exception here");
}
catch (ArgumentException e)
{
// expected here
}
try
{
FormulaShifter.CreateForRowShift(1, "name", 2, 1, 2, SpreadsheetVersion.EXCEL97);
Assert.Fail("Should catch exception here");
}
catch (ArgumentException e)
{
// expected here
}
}
/**
* 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.
*
* @param shifter
*/
public override void UpdateHyperlinks(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 = 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());
}
}
}
/// <summary>
/// Update the formulas in specified row using the formula shifting policy specified by shifter
/// </summary>
/// <param name="row">the row to update the formulas on</param>
/// <param name="Shifter">the formula shifting policy</param>
public override void UpdateRowFormulas(IRow row, FormulaShifter Shifter)
{
XSSFSheet sheet = (XSSFSheet)row.Sheet;
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 = 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;
}
}
}
}
}
public void TestShiftSheet2()
{
// 4 sheets, move a sheet from pos 1 to pos 2, i.e. current 2 becomes 1, current 1 becomes pos 2
FormulaShifter shifter = FormulaShifter.CreateForSheetShift(1, 2);
Ptg[] ptgs = new Ptg[] {
new Ref3DPtg(new CellReference("first", 0, 0, true, true), 0),
new Ref3DPtg(new CellReference("second", 0, 0, true, true), 1),
new Ref3DPtg(new CellReference("third", 0, 0, true, true), 2),
new Ref3DPtg(new CellReference("fourth", 0, 0, true, true), 3),
};
shifter.AdjustFormula(ptgs, -1);
Assert.AreEqual(0, ((Ref3DPtg)ptgs[0]).ExternSheetIndex,
"formula previously pointing to sheet 0 should be unchanged");
Assert.AreEqual(2, ((Ref3DPtg)ptgs[1]).ExternSheetIndex,
"formula previously pointing to sheet 1 should now point to sheet 2");
Assert.AreEqual(1, ((Ref3DPtg)ptgs[2]).ExternSheetIndex,
"formula previously pointing to sheet 2 should now point to sheet 1");
Assert.AreEqual(3, ((Ref3DPtg)ptgs[3]).ExternSheetIndex,
"formula previously pointing to sheet 3 should be unchanged");
}
private static void ConfirmAreaShift(AreaPtg aptg,
int firstRowMoved, int lastRowMoved, int numberRowsMoved,
int expectedAreaFirstRow, int expectedAreaLastRow)
{
FormulaShifter fs = FormulaShifter.CreateForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved, SpreadsheetVersion.EXCEL2007);
bool expectedChanged = aptg.FirstRow != expectedAreaFirstRow || aptg.LastRow != expectedAreaLastRow;
AreaPtg copyPtg = (AreaPtg)aptg.Copy(); // clone so we can re-use aptg in calling method
Ptg[] ptgs = { copyPtg, };
bool actualChanged = fs.AdjustFormula(ptgs, 0);
if (expectedAreaFirstRow < 0)
{
Assert.AreEqual(typeof(AreaErrPtg), ptgs[0].GetType());
return;
}
Assert.AreEqual(expectedChanged, actualChanged);
Assert.AreEqual(copyPtg, ptgs[0]); // expected to change in place (although this is not a strict requirement)
Assert.AreEqual(expectedAreaFirstRow, copyPtg.FirstRow);
Assert.AreEqual(expectedAreaLastRow, copyPtg.LastRow);
}
private static CellRangeAddress ShiftRange(FormulaShifter Shifter, CellRangeAddress cra, int currentExternSheetIx)
{
Ptg[] ptgs = new Ptg[1] {
(Ptg) new AreaPtg(cra.FirstRow, cra.LastRow, cra.FirstColumn, cra.LastColumn, false, false, false, false)
};
if (!Shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
return(cra);
}
Ptg ptg = ptgs[0];
if (ptg is AreaPtg)
{
AreaPtg areaPtg = (AreaPtg)ptg;
return(new CellRangeAddress(areaPtg.FirstRow, areaPtg.LastRow, areaPtg.FirstColumn, areaPtg.LastColumn));
}
if (ptg is AreaErrPtg)
{
return((CellRangeAddress)null);
}
throw new InvalidOperationException("Unexpected Shifted ptg class (" + ptg.GetType().Name + ")");
}
private static void ConfirmAreaCopy(AreaPtg aptg,
int firstRowCopied, int lastRowCopied, int rowOffset,
int expectedFirstRow, int expectedLastRow, bool expectedChanged)
{
AreaPtg copyPtg = (AreaPtg)aptg.Copy(); // clone so we can re-use aptg in calling method
Ptg[] ptgs = { copyPtg, };
FormulaShifter fs = FormulaShifter.CreateForRowCopy(0, null, firstRowCopied, lastRowCopied, rowOffset, SpreadsheetVersion.EXCEL2007);
bool actualChanged = fs.AdjustFormula(ptgs, 0);
// DeletedAreaRef
if (expectedFirstRow < 0 || expectedLastRow < 0)
{
Assert.AreEqual(typeof(AreaErrPtg), ptgs[0].GetType(),
"Reference should have shifted off worksheet, producing #REF! error: " + ptgs[0]);
return;
}
Assert.AreEqual(expectedChanged, actualChanged, "Should this AreaPtg change due to row copy?");
Assert.AreEqual(copyPtg, ptgs[0], "AreaPtgs should be modified in-place when a row containing the AreaPtg is copied"); // expected to change in place (although this is not a strict requirement)
Assert.AreEqual(expectedFirstRow, copyPtg.FirstRow, "AreaPtg first row");
Assert.AreEqual(expectedLastRow, copyPtg.LastRow, "AreaPtg last row");
}
/**
* 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);
}
}
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 InvalidCastException("Unexpected shifted ptg class (" + ptg0.GetType().Name + ")");
}
/**
* 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;
}
}
}
/**
* 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
}
