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"); }
public void Bug51158() { // create a workbook XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet; XSSFRow row = sheet.CreateRow(2) as XSSFRow; XSSFCell cell = row.CreateCell(3) as XSSFCell; cell.SetCellValue("test1"); //XSSFCreationHelper helper = workbook.GetCreationHelper(); //cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0)); XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment; Assert.IsNotNull(comment); comment.SetString("some comment"); // ICellStyle cs = workbook.CreateCellStyle(); // cs.ShrinkToFit=(/*setter*/false); // row.CreateCell(0).CellStyle=(/*setter*/cs); // write the first excel file XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook; Assert.IsNotNull(wb2); sheet = wb2.GetSheetAt(0) as XSSFSheet; row = sheet.GetRow(2) as XSSFRow; Assert.AreEqual("test1", row.GetCell(3).StringCellValue); Assert.IsNull(row.GetCell(4)); // add a new cell to the sheet cell = row.CreateCell(4) as XSSFCell; cell.SetCellValue("test2"); // write the second excel file XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook; Assert.IsNotNull(wb3); sheet = wb3.GetSheetAt(0) as XSSFSheet; row = sheet.GetRow(2) as XSSFRow; Assert.AreEqual("test1", row.GetCell(3).StringCellValue); Assert.AreEqual("test2", row.GetCell(4).StringCellValue); wb3.Close(); wb2.Close(); wb1.Close(); }
public void TestCopyRowFrom() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet("test") as XSSFSheet; XSSFRow srcRow = sheet.CreateRow(0) as XSSFRow; srcRow.CreateCell(0).SetCellValue("Hello"); XSSFRow destRow = sheet.CreateRow(1) as XSSFRow; destRow.CopyRowFrom(srcRow, new CellCopyPolicy()); Assert.IsNotNull(destRow.GetCell(0)); Assert.AreEqual("Hello", destRow.GetCell(0).StringCellValue); workbook.Close(); }
/** * Synchronize table headers with cell values in the parent sheet. * Headers <em>must</em> be in sync, otherwise Excel will display a * "Found unreadable content" message on startup. */ public void UpdateHeaders() { XSSFSheet sheet = (XSSFSheet)GetParent(); CellReference ref1 = GetStartCellReference() as CellReference; if (ref1 == null) { return; } int headerRow = ref1.Row; int firstHeaderColumn = ref1.Col; XSSFRow row = sheet.GetRow(headerRow) as XSSFRow; if (row != null && row.GetCTRow() != null) { int cellnum = firstHeaderColumn; foreach (CT_TableColumn col in GetCTTable().tableColumns.tableColumn) { XSSFCell cell = row.GetCell(cellnum) as XSSFCell; if (cell != null) { col.name = (cell.StringCellValue); } cellnum++; } } }
public void UpdateHeaders() { XSSFSheet parent = (XSSFSheet)this.GetParent(); CellReference startCellReference = this.GetStartCellReference(); if (startCellReference == null) { return; } int row1 = startCellReference.Row; int col = (int)startCellReference.Col; XSSFRow row2 = parent.GetRow(row1) as XSSFRow; if (row2 == null) { return; } foreach (CT_TableColumn ctTableColumn in this.GetCTTable().tableColumns.tableColumn) { int cellnum = (int)ctTableColumn.id - 1 + col; XSSFCell cell = row2.GetCell(cellnum) as XSSFCell; if (cell != null) { ctTableColumn.name = cell.StringCellValue; } } }
public void TestCopyRowOverwritesExistingRow() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet1 = workbook.CreateSheet("Sheet1") as XSSFSheet; ISheet sheet2 = workbook.CreateSheet("Sheet2"); IRow srcRow = sheet1.CreateRow(0); XSSFRow destRow = sheet1.CreateRow(1) as XSSFRow; IRow observerRow = sheet1.CreateRow(2); IRow externObserverRow = sheet2.CreateRow(0); srcRow.CreateCell(0).SetCellValue("hello"); srcRow.CreateCell(1).SetCellValue("world"); destRow.CreateCell(0).SetCellValue(5.0); //A2 -> 5.0 destRow.CreateCell(1).CellFormula = ("A1"); // B2 -> A1 -> "hello" observerRow.CreateCell(0).CellFormula = ("A2"); // A3 -> A2 -> 5.0 observerRow.CreateCell(1).CellFormula = ("B2"); // B3 -> B2 -> A1 -> "hello" externObserverRow.CreateCell(0).CellFormula = ("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0 // overwrite existing destRow with row-copy of srcRow destRow.CopyRowFrom(srcRow, new CellCopyPolicy()); // copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer // to the new row (and allow the old row to be garbage collected) // this is mostly so existing references to rows that are overwritten are updated // rather than allowing users to continue updating rows that are no longer part of the sheet Assert.AreSame(srcRow, sheet1.GetRow(0), "existing references to srcRow are still valid"); Assert.AreSame(destRow, sheet1.GetRow(1), "existing references to destRow are still valid"); Assert.AreSame(observerRow, sheet1.GetRow(2), "existing references to observerRow are still valid"); Assert.AreSame(externObserverRow, sheet2.GetRow(0), "existing references to externObserverRow are still valid"); // Make sure copyRowFrom actually copied row (this is tested elsewhere) Assert.AreEqual(CellType.String, destRow.GetCell(0).CellType); Assert.AreEqual("hello", destRow.GetCell(0).StringCellValue); // We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region Assert.AreEqual("A2", observerRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified"); Assert.AreEqual("B2", observerRow.GetCell(1).CellFormula, "references to overwritten cells are unmodified"); Assert.AreEqual("Sheet1!A2", externObserverRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified"); workbook.Close(); }
public IEvaluationCell GetCell(int rowIndex, int columnIndex) { // cache for performance: ~30% speedup due to caching if (_cellCache == null) { _cellCache = new Dictionary <CellKey, IEvaluationCell>(_xs.LastRowNum * 3); foreach (IRow row in _xs) { int rowNum = row.RowNum; foreach (ICell cell in row) { // cast is safe, the iterator is just defined using the interface CellKey key1 = new CellKey(rowNum, cell.ColumnIndex); IEvaluationCell evalcell1 = new XSSFEvaluationCell((XSSFCell)cell, this); _cellCache.Add(key1, evalcell1); } } } CellKey key = new CellKey(rowIndex, columnIndex); IEvaluationCell evalcell = null; if (_cellCache.ContainsKey(key)) { evalcell = _cellCache[key]; } // If cache is stale, update cache with this one cell // This is a compromise between rebuilding the entire cache // (which would quickly defeat the benefit of the cache) // and not caching at all. // See bug 59958: Add cells on the fly to the evaluation sheet cache on cache miss if (evalcell == null) { XSSFRow row = _xs.GetRow(rowIndex) as XSSFRow; if (row == null) { return(null); } XSSFCell cell = row.GetCell(columnIndex) as XSSFCell; if (cell == null) { return(null); } evalcell = new XSSFEvaluationCell(cell, this); _cellCache[key] = evalcell; } return(evalcell); }
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("A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA", cell_0.StringCellValue); XSSFCell cell_1 = (XSSFCell)row.GetCell(1); Assert.AreEqual(ST_CellType.inlineStr, cell_1.GetCTCell().t); Assert.IsTrue(cell_1.GetCTCell().IsSetIs()); Assert.AreEqual("foo", cell_1.StringCellValue); XSSFCell cell_2 = (XSSFCell)row.GetCell(2); Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t); Assert.IsTrue(cell_2.GetCTCell().IsSetIs()); Assert.AreEqual("bar", row.GetCell(2).StringCellValue); }
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); } } } }
/** * Construct a XSSFCell. * * @param row the parent row. * @param cell the xml bean Containing information about the cell. */ public XSSFCell(XSSFRow row, CT_Cell cell) { _cell = cell; _row = row; if (cell.r != null) { _cellNum = new CellReference(cell.r).Col; } else { int prevNum = row.LastCellNum; if (prevNum != -1) { _cellNum = row.GetCell(prevNum - 1).ColumnIndex + 1; } } _sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource(); _stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource(); }
private static DataTable GetDataTableByXlsx(string excelFilePath, DataTable dt) { XSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int r = 0; while (rows.MoveNext()) { r++; if (r == 1) { continue; } NPOI.XSSF.UserModel.XSSFRow row = (NPOI.XSSF.UserModel.XSSFRow)rows.Current; DataRow rw = dt.NewRow(); for (int i = 1; i <= row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell == null) { continue; } else { rw[i - 1] = cell.ToString(); } } dt.Rows.Add(rw); } return(dt); } }
public void TestBug56511() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56511.xlsx"); foreach (XSSFSheet sheet in wb) { int lastRow = sheet.LastRowNum; for (int rowIdx = sheet.FirstRowNum; rowIdx <= lastRow; rowIdx++) { XSSFRow row = sheet.GetRow(rowIdx) as XSSFRow; if (row != null) { int lastCell = row.LastCellNum; for (int cellIdx = row.FirstCellNum; cellIdx <= lastCell; cellIdx++) { XSSFCell cell = row.GetCell(cellIdx) as XSSFCell; if (cell != null) { //System.out.Println("row " + rowIdx + " column " + cellIdx + ": " + cell.CellType + ": " + cell.ToString()); XSSFRichTextString richText = cell.RichStringCellValue as XSSFRichTextString; int anzFormattingRuns = richText.NumFormattingRuns; for (int run = 0; run < anzFormattingRuns; run++) { /*XSSFFont font =*/ richText.GetFontOfFormattingRun(run); //System.out.Println(" run " + run // + " font " + (font == null ? "<null>" : font.FontName)); } } } } } } }
public void TestCopyRowFromExternalSheet() { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet srcSheet = workbook.CreateSheet("src"); XSSFSheet destSheet = workbook.CreateSheet("dest") as XSSFSheet; workbook.CreateSheet("other"); IRow srcRow = srcSheet.CreateRow(0); int col = 0; //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) srcRow.CreateCell(col++).CellFormula = ("B5"); srcRow.CreateCell(col++).CellFormula = ("src!B5"); srcRow.CreateCell(col++).CellFormula = ("dest!B5"); srcRow.CreateCell(col++).CellFormula = ("other!B5"); //Test 2D and 3D Ref Ptgs with absolute row srcRow.CreateCell(col++).CellFormula = ("B$5"); srcRow.CreateCell(col++).CellFormula = ("src!B$5"); srcRow.CreateCell(col++).CellFormula = ("dest!B$5"); srcRow.CreateCell(col++).CellFormula = ("other!B$5"); //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) srcRow.CreateCell(col++).CellFormula = ("SUM(B5:D$5)"); srcRow.CreateCell(col++).CellFormula = ("SUM(src!B5:D$5)"); srcRow.CreateCell(col++).CellFormula = ("SUM(dest!B5:D$5)"); srcRow.CreateCell(col++).CellFormula = ("SUM(other!B5:D$5)"); ////////////////// XSSFRow destRow = destSheet.CreateRow(1) as XSSFRow; destRow.CopyRowFrom(srcRow, new CellCopyPolicy()); ////////////////// //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) col = 0; ICell cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("B6", cell.CellFormula, "RefPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("src!B6", cell.CellFormula, "Ref3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("dest!B6", cell.CellFormula, "Ref3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("other!B6", cell.CellFormula, "Ref3DPtg"); ///////////////////////////////////////////// //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change) cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("B$5", cell.CellFormula, "RefPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("src!B$5", cell.CellFormula, "Ref3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("dest!B$5", cell.CellFormula, "Ref3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("other!B$5", cell.CellFormula, "Ref3DPtg"); ////////////////////////////////////////// //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) // Note: absolute row changes from last cell to first cell in order // to maintain topLeft:bottomRight order cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("SUM(B$5:D6)", cell.CellFormula, "Area2DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(cell); Assert.AreEqual("SUM(src!B$5:D6)", cell.CellFormula, "Area3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(destRow.GetCell(6)); Assert.AreEqual("SUM(dest!B$5:D6)", cell.CellFormula, "Area3DPtg"); cell = destRow.GetCell(col++); Assert.IsNotNull(destRow.GetCell(7)); Assert.AreEqual("SUM(other!B$5:D6)", cell.CellFormula, "Area3DPtg"); workbook.Close(); }