public void GetNumberOfMappedColumns()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual(3, table.NumberOfMappedColumns);
wb.Close();
}
public void GetStartRowIndex()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual(0, table.StartRowIndex);
wb.Close();
}
public void GetEndCellReference()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual(new CellReference("C7"), table.EndCellReference);
wb.Close();
}
public void IsHasTotalsRow()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.IsFalse(table.IsHasTotalsRow);
wb.Close();
}
public void GetSheetName()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual("Table", table.SheetName);
wb.Close();
}
public void GetTable()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTable.xlsx");
XSSFTable table1 = wb.GetTable("Tabella1");
Assert.IsNotNull(table1, "Tabella1 was not found in workbook");
Assert.AreEqual("Tabella1", table1.Name, "Table name");
Assert.AreEqual("Foglio1", table1.SheetName, "Sheet name");
// Table lookup should be case-insensitive
Assert.AreSame(table1, wb.GetTable("TABELLA1"), "Case insensitive table name lookup");
// If workbook does not contain any data tables matching the provided name, getTable should return null
Assert.IsNull(wb.GetTable(null), "Null table name should not throw NPE");
Assert.IsNull(wb.GetTable("Foglio1"), "Should not be able to find non-existent table");
// If a table is added after getTable is called it should still be reachable by XSSFWorkbook.getTable
// This test makes sure that if any caching is done that getTable never uses a stale cache
XSSFTable table2 = (wb.GetSheet("Foglio2") as XSSFSheet).CreateTable();
table2.Name = "Table2";
Assert.AreSame(table2, wb.GetTable("Table2"), "Did not find Table2");
// If table name is modified after getTable is called, the table can only be found by its new name
// This test makes sure that if any caching is done that getTable never uses a stale cache
table1.Name = "Table1";
Assert.AreSame(table1, wb.GetTable("TABLE1"), "Did not find Tabella1 renamed to Table1");
wb.Close();
}
public void FindColumnIndexIsRelativeToTableNotSheet()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("DataTableCities.xlsx");
XSSFTable table = wb.GetTable("SmallCity");
// Make sure that XSSFTable.findColumnIndex returns the column index relative to the first
// column in the table, not the column number in the sheet
Assert.AreEqual(0, table.FindColumnIndex("City")); // column I in worksheet but 0th column in table
Assert.AreEqual(1, table.FindColumnIndex("Latitude"));
Assert.AreEqual(2, table.FindColumnIndex("Longitude"));
Assert.AreEqual(3, table.FindColumnIndex("Population"));
wb.Close();
}
public void GetAndSetDisplayName()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual("\\_Prime.1", table.DisplayName);
table.DisplayName = null;
Assert.IsNull(table.DisplayName);
Assert.AreEqual("\\_Prime.1", table.Name); // name and display name are different
table.DisplayName = "Display name";
Assert.AreEqual("Display name", table.DisplayName);
Assert.AreEqual("\\_Prime.1", table.Name); // name and display name are different
wb.Close();
}
public void TestTableFormulas()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
try
{
IFormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
XSSFSheet tableSheet = wb.GetSheet("Table") as XSSFSheet;
XSSFSheet formulaSheet = wb.GetSheet("Formulas") as XSSFSheet;
Confirm(eval, tableSheet.GetRow(5).GetCell(0), 49);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209);
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "one");
// test changing a table value, to see if the caches are properly Cleared
// Issue 59814
// this test passes before the fix for 59814
tableSheet.GetRow(1).GetCell(1).SetCellValue("ONEA");
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "ONEA");
// test Adding a row to a table, issue 59814
IRow newRow = tableSheet.GetRow(7);
if (newRow == null)
{
newRow = tableSheet.CreateRow(7);
}
newRow.CreateCell(0, CellType.Formula).CellFormula = (/*setter*/ "\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
newRow.CreateCell(1, CellType.String).SetCellValue("thirteen");
newRow.CreateCell(2, CellType.Numeric).SetCellValue(13);
// update Table
XSSFTable table = wb.GetTable("\\_Prime.1");
AreaReference newArea = new AreaReference(table.StartCellReference, new CellReference(table.EndRowIndex + 1, table.EndColIndex));
String newAreaStr = newArea.FormatAsString();
table.GetCTTable().@ref = (/*setter*/ newAreaStr);
table.GetCTTable().autoFilter.@ref = (/*setter*/ newAreaStr);
table.UpdateHeaders();
//table.UpdateReferences();
// these fail before the fix for 59814
Confirm(eval, tableSheet.GetRow(7).GetCell(0), 13 * 13);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209 + 13 * 13);
}
finally
{
wb.Close();
}
}
public void FindColumnIndex()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
// FIXME: use a worksheet where upper left cell of table is not A1 so that we test
// that XSSFTable.findColumnIndex returns the column index relative to the first
// column in the table, not the column number in the sheet
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.IsNotNull(table);
Assert.AreEqual(0, table.FindColumnIndex("calc='#*'#"),
"column header has special escaped characters");
Assert.AreEqual(1, table.FindColumnIndex("Name"));
Assert.AreEqual(2, table.FindColumnIndex("Number"));
Assert.AreEqual(2, table.FindColumnIndex("NuMbEr"), "case insensitive");
// findColumnIndex should return -1 if no column header name matches
Assert.AreEqual(-1, table.FindColumnIndex(null));
Assert.AreEqual(-1, table.FindColumnIndex(""));
Assert.AreEqual(-1, table.FindColumnIndex("one"));
wb.Close();
}
