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 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 Ptg[] GetFormulaTokens(IEvaluationCell EvalCell) { XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell(); XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook); return(FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet))); }
/** * 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) { foreach (CT_TableColumn col in GetCTTable().tableColumns.tableColumn) { int colIdx = (int)col.id - 1 + firstHeaderColumn; XSSFCell cell = row.GetCell(colIdx) as XSSFCell; if (cell != null) { col.name = (cell.StringCellValue); } } } }
/** * Returns the cell at the given (0 based) index, with the specified {@link NPOI.SS.usermodel.Row.MissingCellPolicy} * * @return the cell at the given (0 based) index * @throws ArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid * @see Row#RETURN_NULL_AND_BLANK * @see Row#RETURN_BLANK_AS_NULL * @see Row#CREATE_NULL_AS_BLANK */ public ICell GetCell(int cellnum, MissingCellPolicy policy) { if (cellnum < 0) { throw new ArgumentException("Cell index must be >= 0"); } XSSFCell cell = (XSSFCell)RetrieveCell(cellnum); if (policy == MissingCellPolicy.RETURN_NULL_AND_BLANK) { return(cell); } if (policy == MissingCellPolicy.RETURN_BLANK_AS_NULL) { if (cell == null) { return(cell); } if (cell.CellType == CellType.Blank) { return(null); } return(cell); } if (policy == MissingCellPolicy.CREATE_NULL_AS_BLANK) { if (cell == null) { return(CreateCell(cellnum, CellType.Blank)); } return(cell); } throw new ArgumentException("Illegal policy " + policy + " (" + policy.id + ")"); }
/** * 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 TestXSSFSetArrayFormula_multiCell() { ICellRange <ICell> cells; String formula2 = "456"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6"); cells = sheet.SetArrayFormula(formula2, range); Assert.AreEqual(3, cells.Size); // sheet.SetArrayFormula Creates rows and cells for the designated range /* * From the spec: * For a multi-cell formula, the c elements for all cells except the top-left * cell in that range shall not have an f element; */ // Check that each cell exists and that the formula text is Set correctly on the first cell XSSFCell firstCell = (XSSFCell)cells.TopLeftCell; ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6"); ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5"); ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6"); Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell)); }
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"); }
/** * 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 (!row.IsSetR()) { // Certain file format writers skip the row number // Assume no gaps, and give this the next row number int nextRowNum = sheet.LastRowNum + 2; if (nextRowNum == 2 && sheet.PhysicalNumberOfRows == 0) { nextRowNum = 1; } row.r = (uint)nextRowNum; } }
/** * Returns the cell at the given (0 based) index, with the specified {@link NPOI.SS.usermodel.Row.MissingCellPolicy} * * @return the cell at the given (0 based) index * @throws ArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid * @see Row#RETURN_NULL_AND_BLANK * @see Row#RETURN_BLANK_AS_NULL * @see Row#CREATE_NULL_AS_BLANK */ public ICell GetCell(int cellnum, MissingCellPolicy policy) { if (cellnum < 0) { throw new ArgumentException("Cell index must be >= 0"); } XSSFCell cell = (XSSFCell)RetrieveCell(cellnum); switch (policy) { case MissingCellPolicy.RETURN_NULL_AND_BLANK: return(cell); case MissingCellPolicy.RETURN_BLANK_AS_NULL: bool isBlank = (cell != null && cell.CellType == CellType.Blank); return((isBlank) ? null : cell); case MissingCellPolicy.CREATE_NULL_AS_BLANK: return((cell == null) ? CreateCell(cellnum, CellType.Blank) : cell); default: throw new ArgumentException("Illegal policy " + policy + " (" + policy + ")"); } }
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; } } }
internal void OnDeleteFormula(XSSFCell cell) { if (this.calcChain == null) { return; } this.calcChain.RemoveItem((int)((XSSFSheet)cell.Sheet).sheet.sheetId, cell.GetReference()); }
public override Ptg[] GetFormulaTokens(IEvaluationCell evalCell) { XSSFCell cell = ((XSSFEvaluationCell)evalCell).GetXSSFCell(); int sheetIndex = _uBook.GetSheetIndex(cell.Sheet); int rowIndex = cell.RowIndex; return(FormulaParser.Parse(cell.GetCellFormula(this), this, FormulaType.Cell, sheetIndex, rowIndex)); }
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(); } }
public CellRangeAddress GetArrayFormulaRange() { XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this); if (cell == null) { throw new InvalidOperationException("Cell " + _cell.r + " is not part of an array formula."); } String formulaRef = cell._cell.f.@ref; return(CellRangeAddress.ValueOf(formulaRef)); }
public void EvaluateInCellReturnsSameDataType() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet().CreateRow(0).CreateCell(0); XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; XSSFCell cell = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell; XSSFCell same = evaluator.EvaluateInCell(cell) as XSSFCell; //assertSame(cell, same); Assert.AreSame(cell, same); wb.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 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(); }
/** * 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.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 ICell EvaluateInCell(ICell cell) { if (cell == null) { return((ICell)null); } XSSFCell xssfCell = (XSSFCell)cell; if (cell.CellType == CellType.FORMULA) { CellValue formulaCellValue = this.EvaluateFormulaCellValue(cell); XSSFFormulaEvaluator.SetCellType(cell, formulaCellValue); XSSFFormulaEvaluator.SetCellValue(cell, formulaCellValue); } return((ICell)xssfCell); }
public XSSFRow(CT_Row row, XSSFSheet sheet) { this._row = row; this._sheet = sheet; this._cells = new SortedDictionary <int, ICell>(); if (0 >= row.SizeOfCArray()) { return; } foreach (CT_Cell cell1 in row.c) { XSSFCell cell2 = new XSSFCell(this, cell1); this._cells.Add(cell2.ColumnIndex, (ICell)cell2); sheet.OnReadCell(cell2); } }
public void RemoveCell(ICell cell) { if (cell.Row != this) { throw new ArgumentException("Specified cell does not belong to this row"); } XSSFCell cell1 = (XSSFCell)cell; if (cell1.IsPartOfArrayFormulaGroup) { cell1.NotifyArrayFormulaChanging(); } if (cell.CellType == CellType.FORMULA) { ((XSSFWorkbook)this._sheet.Workbook).OnDeleteFormula(cell1); } this._cells.Remove(cell.ColumnIndex); }
private void setUp_testCopyCellFrom_CellCopyPolicy() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFRow row = wb.CreateSheet().CreateRow(0) as XSSFRow; srcCell = row.CreateCell(0) as XSSFCell; destCell = row.CreateCell(1) as XSSFCell; srcCell.CellFormula = ("2+3"); ICellStyle style = wb.CreateCellStyle(); style.BorderTop = BorderStyle.Thick; style.FillBackgroundColor = ((short)5); srcCell.CellStyle = (style); destCell.SetCellValue(true); }
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); } } } }
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.GetArrayFormulaRange().FormatAsString()); ConfirmArrayFormulaCell(firstCell, "C3", formula1, "C3"); }
/** * package/hierarchy use only - reuse an existing evaluation workbook if available for caching * * @param fpb evaluation workbook for reuse, if available, or null to create a new one as needed * @return a formula for the cell * @throws InvalidOperationException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA */ protected internal String GetCellFormula(XSSFEvaluationWorkbook fpb) { CellType cellType = CellType; if (cellType != CellType.Formula) { throw TypeMismatch(CellType.Formula, cellType, false); } CT_CellFormula f = _cell.f; if (IsPartOfArrayFormulaGroup && f == null) { XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this); return(cell.GetCellFormula(fpb)); } if (f.t == ST_CellFormulaType.shared) { //return ConvertSharedFormula((int)f.si); return(ConvertSharedFormula((int)f.si, fpb == null ? XSSFEvaluationWorkbook.Create(Sheet.Workbook) : fpb)); } return(f.Value); }
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 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); }
/** * 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); }
internal void OnReadCell(XSSFCell cell) { //collect cells holding shared formulas CT_Cell ct = cell.GetCTCell(); CT_CellFormula f = ct.f; if (f != null && f.t == ST_CellFormulaType.shared && f.isSetRef() && f.Value != null) { // save a detached copy to avoid XmlValueDisconnectedException, // this may happen when the master cell of a shared formula is Changed sharedFormulas[(int)f.si] = (CT_CellFormula)f.Copy(); } if (f != null && f.t == ST_CellFormulaType.array && f.@ref != null) { arrayFormulas.Add(CellRangeAddress.ValueOf(f.@ref)); } }
public XSSFEvaluationCell(ICell cell, XSSFEvaluationSheet EvaluationSheet) { _cell = (XSSFCell)cell; _evalSheet = EvaluationSheet; }
/** * 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()); } }
internal void OnReadCell(XSSFCell cell) { //collect cells holding shared formulas CT_Cell ct = cell.GetCTCell(); CT_CellFormula f = ct.f; if (f != null && f.t == ST_CellFormulaType.shared && f.isSetRef() && f.Value != null) { // save a detached copy to avoid XmlValueDisconnectedException, // this may happen when the master cell of a shared formula is Changed CT_CellFormula sf = (CT_CellFormula)f.Copy(); CellRangeAddress sfRef = CellRangeAddress.ValueOf(sf.@ref); CellReference cellRef = new CellReference(cell); // If the shared formula range preceeds the master cell then the preceding part is discarded, e.g. // if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85 // see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710 if (cellRef.Col > sfRef.FirstColumn || cellRef.Row > sfRef.FirstRow) { String effectiveRef = new CellRangeAddress( Math.Max(cellRef.Row, sfRef.FirstRow), sfRef.LastRow, Math.Max(cellRef.Col, sfRef.FirstColumn), sfRef.LastColumn).FormatAsString(); sf.@ref = (effectiveRef); } sharedFormulas[(int)f.si] = sf; } if (f != null && f.t == ST_CellFormulaType.array && f.@ref != null) { arrayFormulas.Add(CellRangeAddress.ValueOf(f.@ref)); } }