/**
* 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 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("A2", cell.CellFormula);
//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("A2", cell.CellFormula);
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual("t='str'", cell.StringCellValue);
//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("t='str'", cell.StringCellValue);
//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);
}
/**
* 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();
}
int style = _sheet.GetColDefaultStyle(columnIndex);
if (style >= 0)
{
ctCell.s = (uint)style;
}
XSSFCell xcell = new XSSFCell(this, ctCell);
xcell.SetCellNum(columnIndex);
if (type != CellType.Blank)
{
xcell.SetCellType(type);
}
_cells[columnIndex] = xcell;
return(xcell);
}
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);
}
}
}
}
/**
* 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 ICell CreateCell(int columnIndex, CellType type)
{
XSSFCell xssfCell1 = this._cells.ContainsKey(columnIndex) ? (XSSFCell)this._cells[columnIndex] : (XSSFCell)null;
CT_Cell cell;
if (xssfCell1 != null)
{
cell = xssfCell1.GetCTCell();
cell.Set(new CT_Cell());
}
else
{
cell = this._row.AddNewC();
}
XSSFCell xssfCell2 = new XSSFCell(this, cell);
xssfCell2.SetCellNum(columnIndex);
if (type != CellType.BLANK)
{
xssfCell2.SetCellType(type);
}
this._cells[columnIndex] = (ICell)xssfCell2;
return((ICell)xssfCell2);
}
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));
}
}
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));
}
}