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));
}
}
