/**
* 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;
}