public void TestConvertSharedFormulasOperandClasses_bug45123()
{
ILittleEndianInput in1 = TestcaseRecordInputStream.CreateLittleEndian(SHARED_FORMULA_WITH_REF_ARRAYS_DATA);
int encodedLen = in1.ReadUShort();
Ptg[] sharedFormula = Ptg.ReadTokens(encodedLen, in1);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
Ptg[] ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 100, 200);
RefPtg refPtg = (RefPtg)ConvertedFormula[1];
Assert.AreEqual("$C101", refPtg.ToFormulaString());
if (refPtg.PtgClass == Ptg.CLASS_REF)
{
throw new AssertionException("Identified bug 45123");
}
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
}
/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private String ConvertSharedFormula(int si)
{
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);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(sheet.Workbook);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return FormulaRenderer.ToFormulaString(fpb, fmla);
}
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);
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);
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)");
}
/**
* @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
*/
public Ptg[] GetFormulaTokens(FormulaRecord formula)
{
int formulaRow = formula.Row;
int formulaColumn = formula.Column;
//Sanity checks
if (!IsInRange(formulaRow, formulaColumn))
{
throw new Exception("Shared Formula Conversion: Coding Error");
}
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
return sf.ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn);
//return ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn);
}
