/**
* update cell references when Shifting rows
*
* @param n the number of rows to move
*/
internal void Shift(int n)
{
int rownum = RowNum + n;
CalculationChain calcChain = ((XSSFWorkbook)_sheet.Workbook).GetCalculationChain();
int sheetId = (int)_sheet.sheet.sheetId;
String msg = "Row[rownum=" + RowNum + "] contains cell(s) included in a multi-cell array formula. " +
"You cannot change part of an array.";
foreach (ICell c in this)
{
XSSFCell cell = (XSSFCell)c;
if (cell.IsPartOfArrayFormulaGroup)
{
cell.NotifyArrayFormulaChanging(msg);
}
//remove the reference in the calculation chain
if (calcChain != null)
{
calcChain.RemoveItem(sheetId, cell.GetReference());
}
CT_Cell CT_Cell = cell.GetCTCell();
String r = new CellReference(rownum, cell.ColumnIndex).FormatAsString();
CT_Cell.r = r;
}
RowNum = rownum;
}
private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
internal void OnDeleteFormula(XSSFCell cell)
{
if (this.calcChain == null)
{
return;
}
this.calcChain.RemoveItem((int)((XSSFSheet)cell.Sheet).sheet.sheetId, cell.GetReference());
}
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);
}
}
}
}
/**
* Fired when a formula is deleted from this workbook,
* for example when calling cell.SetCellFormula(null)
*
* @see XSSFCell#setCellFormula(String)
*/
internal void OnDeleteFormula(XSSFCell cell)
{
if (calcChain != null)
{
int sheetId = (int)((XSSFSheet)cell.Sheet).sheet.sheetId;
calcChain.RemoveItem(sheetId, cell.GetReference());
}
}