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;
}
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;
}
}
}
}
}
public void TestMissingRAttribute()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
XSSFCell a1 = (XSSFCell)row.CreateCell(0);
a1.SetCellValue("A1");
XSSFCell a2 = (XSSFCell)row.CreateCell(1);
a2.SetCellValue("B1");
XSSFCell a4 = (XSSFCell)row.CreateCell(4);
a4.SetCellValue("E1");
XSSFCell a6 = (XSSFCell)row.CreateCell(5);
a6.SetCellValue("F1");
assertCellsWithMissingR(row);
a2.GetCTCell().unsetR();
a6.GetCTCell().unsetR();
assertCellsWithMissingR(row);
wb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
assertCellsWithMissingR(row);
}
public void TestPreserveSpaces()
{
String[] samplesWithSpaces =
{
" POI",
"POI ",
" POI ",
"\nPOI",
"\n\nPOI \n",
};
foreach (String str in samplesWithSpaces)
{
IWorkbook swb = _testDataProvider.CreateWorkbook();
ICell sCell = swb.CreateSheet().CreateRow(0).CreateCell(0);
sCell.SetCellValue(str);
Assert.AreEqual(sCell.StringCellValue, str);
// read back as XSSF and check that xml:spaces="preserve" is Set
XSSFWorkbook xwb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(swb);
XSSFCell xCell = xwb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
CT_Rst is1 = xCell.GetCTCell().@is;
//XmlCursor c = is1.NewCursor();
//c.ToNextToken();
//String t = c.GetAttributeText(new QName("http://www.w3.org/XML/1998/namespace", "space"));
//c.Dispose();
//write is1 to xml stream writer ,get the xml text and parse it and get space attr.
//Assert.AreEqual("preserve", t, "expected xml:spaces=\"preserve\" \"" + str + "\"");
xwb.Close();
swb.Close();
}
}
public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
}
public void TestInlineString()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("xlsx-jdbc.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(1);
XSSFCell cell_0 = (XSSFCell)row.GetCell(0);
Assert.AreEqual(ST_CellType.inlineStr, cell_0.GetCTCell().t);
Assert.IsTrue(cell_0.GetCTCell().IsSetIs());
Assert.AreEqual(cell_0.StringCellValue, "A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA");
XSSFCell cell_1 = (XSSFCell)row.GetCell(1);
Assert.AreEqual(ST_CellType.inlineStr, cell_1.GetCTCell().t);
Assert.IsTrue(cell_1.GetCTCell().IsSetIs());
Assert.AreEqual(cell_1.StringCellValue, "foo");
XSSFCell cell_2 = (XSSFCell)row.GetCell(2);
Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
Assert.AreEqual(row.GetCell(2).StringCellValue, "bar");
}
/**
* 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);
}
}
}
}
public void TestMissingRAttributeBug54288()
{
// workbook with cells missing the R attribute
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx");
// same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0);
Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows);
// Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
// For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
// and assert that POI reads them equally:
DataFormatter formater = new DataFormatter();
foreach (IRow r in sheetRef)
{
XSSFRow rowRef = (XSSFRow)r;
XSSFRow row = (XSSFRow)sheet.GetRow(rowRef.RowNum);
Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]");
foreach (ICell c in rowRef.Cells)
{
XSSFCell cellRef = (XSSFCell)c;
XSSFCell cell = (XSSFCell)row.GetCell(cellRef.ColumnIndex);
Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex);
Assert.AreEqual(cellRef.GetReference(), cell.GetReference());
if (!cell.GetCTCell().IsSetR())
{
Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef");
String valRef = formater.FormatCellValue(cellRef);
String val = formater.FormatCellValue(cell);
Assert.AreEqual(valRef, val);
}
}
}
}
/// <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;
}
}
}
}
}
/**
* 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);
}
}
}
}
/**
* 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);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
}
}
}
/**
* 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);
}
}
}
public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
// check behavior with setCellFormulaValidation
String invalidFormula = "A", validFormula = "A2";
FormulaParseException fpe = null;
// check that default is true
Assert.IsTrue(wb.CellFormulaValidation);
// check that valid formula does not throw exception
try
{
cell.SetCellFormula(validFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
// check that invalid formula does throw exception
try
{
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNotNull(fpe);
fpe = null;
// set cell formula validation to false
wb.CellFormulaValidation = false;
Assert.IsFalse(wb.CellFormulaValidation);
// check that neither valid nor invalid formula throw an exception
try
{
cell.SetCellFormula(validFormula);
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
}
