public void TestCTTableStyleInfo()
{
XSSFWorkbook outputWorkbook = new XSSFWorkbook();
XSSFSheet sheet = outputWorkbook.CreateSheet() as XSSFSheet;
//Create
XSSFTable outputTable = sheet.CreateTable();
outputTable.DisplayName = ("Test");
CT_Table outputCTTable = outputTable.GetCTTable();
//Style configurations
CT_TableStyleInfo outputStyleInfo = outputCTTable.AddNewTableStyleInfo();
outputStyleInfo.name = ("TableStyleLight1");
outputStyleInfo.showColumnStripes = (false);
outputStyleInfo.showRowStripes = (true);
XSSFWorkbook inputWorkbook = XSSFTestDataSamples.WriteOutAndReadBack(outputWorkbook) as XSSFWorkbook;
List <XSSFTable> tables = (inputWorkbook.GetSheetAt(0) as XSSFSheet).GetTables();
Assert.AreEqual(1, tables.Count, "Tables number");
XSSFTable inputTable = tables[0];
Assert.AreEqual(outputTable.DisplayName, inputTable.DisplayName, "Table display name");
CT_TableStyleInfo inputStyleInfo = inputTable.GetCTTable().tableStyleInfo;
Assert.AreEqual(outputStyleInfo.name, inputStyleInfo.name, "Style name");
Assert.AreEqual(outputStyleInfo.showColumnStripes, inputStyleInfo.showColumnStripes, "Show column stripes");
Assert.AreEqual(outputStyleInfo.showRowStripes, inputStyleInfo.showRowStripes, "Show row stripes");
}
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();
}
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
XSSFTable table = sheet1.CreateTable();
table.Name = "Tabella1";
table.DisplayName = "Tabella1";
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void GetNumberOfMappedColumns()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual(3, table.NumberOfMappedColumns);
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 GetStartRowIndex()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.GetTable("\\_Prime.1");
Assert.AreEqual(0, table.StartRowIndex);
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 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 Test56170()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56170.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
IWorkbook wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
ICell cell;
// add some contents to table so that the table will need expansion
IRow row = sheet.GetRow(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo1");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(1);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo2");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(2);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
row = sheet.GetRow(1);
cell = row.CreateCell(0);
cell.SetCellValue("demo1");
cell = row.CreateCell(1);
cell.SetCellValue("demo2");
cell = row.CreateCell(2);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
// expand table
XSSFTable table = sheet.GetTables()[0];
CellReference startRef = table.StartCellReference;
CellReference endRef = table.EndCellReference;
table.GetCTTable().@ref = (new CellRangeAddress(startRef.Row, 1, startRef.Col, endRef.Col).FormatAsString());
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbRead);
/*FileOutputStream stream = new FileOutputStream("c:\\temp\\output.xlsx");
* workbook.Write(stream);
* stream.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 GetRowCount()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = wb.CreateSheet() as XSSFSheet;
XSSFTable table = sh.CreateTable() as XSSFTable;
CT_Table ctTable = table.GetCTTable();
Assert.AreEqual(0, table.RowCount);
ctTable.@ref = "B2:B2";
// update cell references to clear the cache
table.UpdateReferences();
Assert.AreEqual(1, table.RowCount);
ctTable.@ref = "B2:B12";
// update cell references to clear the cache
table.UpdateReferences();
Assert.AreEqual(11, table.RowCount);
}
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();
}
public void GetCellReferences()
{
// make sure that cached start and end cell references
// can be synchronized with the underlying CTTable
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = wb.CreateSheet() as XSSFSheet;
XSSFTable table = sh.CreateTable() as XSSFTable;
CT_Table ctTable = table.GetCTTable();
ctTable.@ref = "B2:E8";
Assert.AreEqual(new CellReference("B2"), table.StartCellReference);
Assert.AreEqual(new CellReference("E8"), table.EndCellReference);
// At this point start and end cell reference are cached
// and may not follow changes to the underlying CTTable
ctTable.@ref = "C1:M3";
Assert.AreEqual(new CellReference("B2"), table.StartCellReference);
Assert.AreEqual(new CellReference("E8"), table.EndCellReference);
// Force a synchronization between CTTable and XSSFTable
// start and end cell references
table.UpdateReferences();
Assert.AreEqual(new CellReference("C1"), table.StartCellReference);
Assert.AreEqual(new CellReference("M3"), table.EndCellReference);
}
public XSSFXmlColumnPr(XSSFTable table, CT_TableColumn ctTableColum, CT_XmlColumnPr CT_XmlColumnPr)
{
this.table = table;
this.ctTableColumn = ctTableColum;
this.ctXmlColumnPr = CT_XmlColumnPr;
}
public XSSFXmlColumnPr(XSSFTable table, CT_TableColumn ctTableColum, CT_XmlColumnPr CT_XmlColumnPr)
{
this.table = table;
this.tableColumn = table.GetColumns()[table.FindColumnIndex(ctTableColum.name)];
this.ctXmlColumnPr = CT_XmlColumnPr;
}
internal XSSFXmlColumnPr(XSSFTableColumn tableColumn, CT_XmlColumnPr ctXmlColumnPr)
{
this.table = tableColumn.GetTable();
this.tableColumn = tableColumn;
this.ctXmlColumnPr = ctXmlColumnPr;
}
public void ExportToXML(Stream os, string encoding, bool validate)
{
List <XSSFSingleXmlCell> relatedSingleXmlCell = this.map.GetRelatedSingleXMLCell();
List <XSSFTable> relatedTables = this.map.GetRelatedTables();
string rootElement = this.map.GetCTMap().RootElement;
XmlDocument emptyDocument = this.GetEmptyDocument();
XmlElement xmlElement = !this.IsNamespaceDeclared() ? emptyDocument.CreateElement(rootElement) : emptyDocument.CreateElement(rootElement, this.GetNamespace());
emptyDocument.AppendChild((XmlNode)xmlElement);
List <string> stringList = new List <string>();
Dictionary <string, XSSFSingleXmlCell> dictionary1 = new Dictionary <string, XSSFSingleXmlCell>();
Dictionary <string, XSSFTable> dictionary2 = new Dictionary <string, XSSFTable>();
foreach (XSSFSingleXmlCell xssfSingleXmlCell in relatedSingleXmlCell)
{
stringList.Add(xssfSingleXmlCell.GetXpath());
dictionary1[xssfSingleXmlCell.GetXpath()] = xssfSingleXmlCell;
}
foreach (XSSFTable xssfTable in relatedTables)
{
string commonXpath = xssfTable.GetCommonXpath();
stringList.Add(commonXpath);
dictionary2[commonXpath] = xssfTable;
}
stringList.Sort();
foreach (string index in stringList)
{
XSSFSingleXmlCell xssfSingleXmlCell = !dictionary1.ContainsKey(index) ? (XSSFSingleXmlCell)null : dictionary1[index];
XSSFTable xssfTable = !dictionary2.ContainsKey(index) ? (XSSFTable)null : dictionary2[index];
if (!Regex.IsMatch(index, ".*\\[.*"))
{
if (xssfSingleXmlCell != null)
{
XSSFCell referencedCell = (XSSFCell)xssfSingleXmlCell.GetReferencedCell();
if (referencedCell != null)
{
XmlNode nodeByXpath = this.GetNodeByXPath(index, emptyDocument.FirstChild, emptyDocument, false);
ST_XmlDataType xmlDataType = xssfSingleXmlCell.GetXmlDataType();
this.mapCellOnNode(referencedCell, nodeByXpath, xmlDataType);
}
}
if (xssfTable != null)
{
List <XSSFXmlColumnPr> xmlColumnPrs = xssfTable.GetXmlColumnPrs();
XSSFSheet xssfSheet = xssfTable.GetXSSFSheet();
int num = xssfTable.GetStartCellReference().Row + 1;
int row1 = xssfTable.GetEndCellReference().Row;
for (int rownum = num; rownum <= row1; ++rownum)
{
XSSFRow row2 = (XSSFRow)xssfSheet.GetRow(rownum);
XmlNode nodeByXpath1 = this.GetNodeByXPath(xssfTable.GetCommonXpath(), emptyDocument.FirstChild, emptyDocument, true);
short col = xssfTable.GetStartCellReference().Col;
for (int cellnum = (int)col; cellnum <= (int)xssfTable.GetEndCellReference().Col; ++cellnum)
{
XSSFCell cell = (XSSFCell)row2.GetCell(cellnum);
if (cell != null)
{
XSSFXmlColumnPr xssfXmlColumnPr = xmlColumnPrs[cellnum - (int)col];
XmlNode nodeByXpath2 = this.GetNodeByXPath(xssfXmlColumnPr.GetLocalXPath(), nodeByXpath1, emptyDocument, false);
ST_XmlDataType xmlDataType = xssfXmlColumnPr.GetXmlDataType();
this.mapCellOnNode(cell, nodeByXpath2, xmlDataType);
}
}
}
}
}
}
bool flag = true;
if (validate)
{
flag = this.IsValid(emptyDocument);
}
if (!flag)
{
return;
}
using (XmlWriter w = XmlWriter.Create(os, new XmlWriterSettings()
{
Indent = true, Encoding = Encoding.GetEncoding(encoding)
}))
emptyDocument.WriteTo(w);
}
public static void WriteDMToTable(this ISheet sheet, List <DefineSelectItem> lst, string tableName, int numCol)
{
if (lst == null || lst.Count == 0)
{
return;
}
XSSFSheet s = (XSSFSheet)sheet;
XSSFTable tbl = s.GetTables().Where(a => a.Name == tableName).FirstOrDefault();
if (tbl == null)
{
return;
}
CT_Table ctTBl = tbl.GetCTTable();
if (numCol < 2 || ctTBl.tableColumns.count < numCol)
{
return;
}
CellReference cellRefStart = tbl.GetStartCellReference();
int rowStart = cellRefStart.Row + 1;
short colStart = cellRefStart.Col;
CellReference cellRefEnd = tbl.GetEndCellReference();
AreaReference reference = new AreaReference(cellRefStart, new CellReference(cellRefStart.Row + lst.Count, cellRefEnd.Col));
ctTBl.insertRow = true;
ctTBl.insertRowShift = true;
ctTBl.@ref = reference.FormatAsString();
IRow row = GetCreateRow(sheet, rowStart);
switch (numCol)
{
case 3:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Text);
}
break;
case 4:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Value3);
row.GetCreateCell(colStart + 3).SetCellValue(item.Text);
}
break;
case 2:
default:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Text);
}
break;
}
}
