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