/** * 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 TestMultisheetFormulaEval() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet; XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet; XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet; // sheet1 A1 XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet2 A1 cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet2 B1 cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet3 A1 cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet1 A2 formulae cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Formula); cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)"); // sheet1 A3 formulae cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Formula); cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)"); wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); cell = sheet1.GetRow(1).GetCell(0) as XSSFCell; Assert.AreEqual(3.0, cell.NumericCellValue); cell = sheet1.GetRow(2).GetCell(0) as XSSFCell; Assert.AreEqual(4.0, cell.NumericCellValue); } finally { wb.Close(); } }
/** * Construct a XSSFRow. * * @param row the xml bean Containing all cell defInitions for this row. * @param sheet the parent sheet. */ public XSSFRow(CT_Row row, XSSFSheet sheet) { _row = row; _sheet = sheet; _cells = new SortedDictionary <int, ICell>(); if (0 < row.SizeOfCArray()) { foreach (CT_Cell c in row.c) { XSSFCell cell = new XSSFCell(this, c); _cells.Add(cell.ColumnIndex, cell); sheet.OnReadCell(cell); } } }
/** * If cell Contains formula, it Evaluates the formula, and * Puts the formula result back into the cell, in place * of the old formula. * Else if cell does not contain formula, this method leaves * the cell unChanged. * Note that the same instance of HSSFCell is returned to * allow chained calls like: * <pre> * int EvaluatedCellType = Evaluator.EvaluateInCell(cell).CellType; * </pre> * Be aware that your cell value will be Changed to hold the * result of the formula. If you simply want the formula * value computed for you, use {@link #EvaluateFormulaCell(Npoi.Core.ss.usermodel.Cell)} } * @param cell */ public ICell EvaluateInCell(ICell cell) { if (cell == null) { return(null); } XSSFCell result = (XSSFCell)cell; if (cell.CellType == CellType.Formula) { CellValue cv = EvaluateFormulaCellValue(cell); SetCellType(cell, cv); // cell will no longer be a formula cell SetCellValue(cell, cv); } return(result); }
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); } } } }
/** * Remove the Cell from this row. * * @param cell the cell to remove */ public void RemoveCell(ICell cell) { if (cell.Row != this) { throw new ArgumentException("Specified cell does not belong to this row"); } XSSFCell xcell = (XSSFCell)cell; if (xcell.IsPartOfArrayFormulaGroup) { xcell.NotifyArrayFormulaChanging(); } if (cell.CellType == CellType.Formula) { ((XSSFWorkbook)_sheet.Workbook).OnDeleteFormula(xcell); } _cells.Remove(cell.ColumnIndex); }
public void Bug51158() { // create a workbook XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.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 readBack = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; Assert.IsNotNull(readBack); Assert.AreEqual("test1", readBack.GetSheetAt(0).GetRow(2).GetCell(3).StringCellValue); Assert.IsNull(readBack.GetSheetAt(0).GetRow(2).GetCell(4)); // add a new cell to the sheet cell = row.CreateCell(4) as XSSFCell; cell.SetCellValue("test2"); // write the second excel file readBack = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; Assert.IsNotNull(readBack); Assert.AreEqual("test1", readBack.GetSheetAt(0).GetRow(2).GetCell(3).StringCellValue); Assert.AreEqual("test2", readBack.GetSheetAt(0).GetRow(2).GetCell(4).StringCellValue); }
public void TestXSSFSetArrayFormula_SingleCell() { ICellRange <ICell> cells; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); // 1. Single-cell array formula String formula1 = "123"; CellRangeAddress range = CellRangeAddress.ValueOf("C3:C3"); cells = sheet.SetArrayFormula(formula1, range); Assert.AreEqual(1, cells.Size); // check GetFirstCell... XSSFCell firstCell = (XSSFCell)cells.TopLeftCell; Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell)); //retrieve the range and check it is the same Assert.AreEqual(range.FormatAsString(), firstCell.ArrayFormulaRange.FormatAsString()); ConfirmArrayFormulaCell(firstCell, "C3", formula1, "C3"); }
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"); }
/** * 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); }
public void TestBug55843f() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFRow row2 = sheet.CreateRow(1) as XSSFRow; XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell; XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell; cellB1.SetCellValue(10); XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; cellA2.SetCellFormula("IF(B1=0,\"\",IF(B1=10,3,4))"); CellValue Evaluate = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(Evaluate); Assert.AreEqual("3", Evaluate.FormatAsString()); } finally { wb.Close(); } }
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 TestCreate() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper; String[] urls = { "http://apache.org/", "www.apache.org", "/temp", "file:///c:/temp", "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*" }; for (int i = 0; i < urls.Length; i++) { String s = urls[i]; XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink; link.Address = (s); XSSFCell cell = row.CreateCell(i) as XSSFCell; cell.Hyperlink = (link); } workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } }
public XSSFEvaluationCell(ICell cell, XSSFEvaluationSheet EvaluationSheet) { _cell = (XSSFCell)cell; _evalSheet = EvaluationSheet; }