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 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, ""); }
/** * 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; }
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"); }
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); } } } }
/** * Use this to create new cells within the row and return it. * * @param columnIndex - the column number this cell represents * @param type - the cell's data type * @return XSSFCell a high level representation of the Created cell. * @throws ArgumentException if the specified cell type is invalid, columnIndex < 0 * or greater than 16384, the maximum number of columns supported by the SpreadsheetML format (.xlsx) * @see Cell#CELL_TYPE_BLANK * @see Cell#CELL_TYPE_BOOLEAN * @see Cell#CELL_TYPE_ERROR * @see Cell#CELL_TYPE_FORMULA * @see Cell#CELL_TYPE_NUMERIC * @see Cell#CELL_TYPE_STRING */ public ICell CreateCell(int columnIndex, CellType type) { CT_Cell ctCell; XSSFCell prev = _cells.ContainsKey(columnIndex) ? (XSSFCell)_cells[columnIndex] : null; if (prev != null) { ctCell = prev.GetCTCell(); ctCell.Set(new CT_Cell()); } else { ctCell = _row.AddNewC(); } XSSFCell xcell = new XSSFCell(this, ctCell); xcell.SetCellNum(columnIndex); if (type != CellType.Blank) { xcell.SetCellType(type); } _cells[columnIndex] = xcell; return(xcell); }