You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2017/06/07 12:39:41 UTC
svn commit: r1797914 - in /poi/trunk:
src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
test-data/spreadsheet/TablesWithDifferentHeaders.xlsx
Author: nick
Date: Wed Jun 7 12:39:41 2017
New Revision: 1797914
URL: http://svn.apache.org/viewvc?rev=1797914&view=rev
Log:
XSSFTable should format numeric/date cells when used as Column Header names as Excel does, see https://stackoverflow.com/questions/44407111/apache-poi-cant-format-filled-cells-as-numeric
Added:
poi/trunk/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx (with props)
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java?rev=1797914&r1=1797913&r2=1797914&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java Wed Jun 7 12:39:41 2017
@@ -31,8 +31,10 @@ import java.util.Locale;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Table;
import org.apache.poi.ss.usermodel.TableStyleInfo;
+import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Internal;
import org.apache.poi.util.StringUtil;
@@ -311,7 +313,20 @@ public class XSSFTable extends POIXMLDoc
return getNumberOfMappedColumns();
}
-
+ /**
+ * @return The reference for the cells of the table
+ * (see Open Office XML Part 4: chapter 3.5.1.2, attribute ref)
+ *
+ * Does not track updates to underlying changes to CTTable
+ * To synchronize with changes to the underlying CTTable,
+ * call {@link #updateReferences()}.
+ */
+ public AreaReference getReferences() {
+ return new AreaReference(
+ getStartCellReference(),
+ getEndCellReference()
+ );
+ }
/**
* @return The reference for the cell in the top-left part of the table
@@ -401,6 +416,10 @@ public class XSSFTable extends POIXMLDoc
* If calling both {@link #updateReferences()} and
* {@link #updateHeaders()}, {@link #updateReferences()}
* should be called first.
+ *
+ * Note that a Table <em>must</em> have a header. To reproduce
+ * the equivalent of inserting a table in Excel without Headers,
+ * manually add cells with values of "Column1", "Column2" etc first.
*/
public void updateHeaders() {
XSSFSheet sheet = (XSSFSheet)getParent();
@@ -410,13 +429,14 @@ public class XSSFTable extends POIXMLDoc
int headerRow = ref.getRow();
int firstHeaderColumn = ref.getCol();
XSSFRow row = sheet.getRow(headerRow);
+ DataFormatter formatter = new DataFormatter();
if (row != null && row.getCTRow().validate()) {
int cellnum = firstHeaderColumn;
for (CTTableColumn col : getCTTable().getTableColumns().getTableColumnArray()) {
XSSFCell cell = row.getCell(cellnum);
if (cell != null) {
- col.setName(cell.getStringCellValue());
+ col.setName(formatter.formatCellValue(cell));
}
cellnum++;
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java?rev=1797914&r1=1797913&r2=1797914&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java Wed Jun 7 12:39:41 2017
@@ -38,6 +38,7 @@ import org.apache.poi.xssf.streaming.SXS
import org.junit.Test;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
public final class TestXSSFTable {
@@ -287,4 +288,81 @@ public final class TestXSSFTable {
table.updateReferences();
assertEquals(11, table.getRowCount());
}
+
+ @Test
+ public void testDifferentHeaderTypes() throws IOException {
+ XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("TablesWithDifferentHeaders.xlsx");
+ assertEquals(3, wb.getNumberOfSheets());
+ XSSFSheet s;
+ XSSFTable t;
+
+ // TODO Nicer column fetching
+
+ s = wb.getSheet("IntHeaders");
+ assertEquals(1, s.getTables().size());
+ t = s.getTables().get(0);
+ assertEquals("A1:B2", t.getReferences().formatAsString());
+ assertEquals("12", t.getCTTable().getTableColumns().getTableColumnArray(0).getName());
+ assertEquals("34", t.getCTTable().getTableColumns().getTableColumnArray(1).getName());
+
+ s = wb.getSheet("FloatHeaders");
+ assertEquals(1, s.getTables().size());
+ t = s.getTables().get(0);
+ assertEquals("A1:B2", t.getReferences().formatAsString());
+ assertEquals("12.34", t.getCTTable().getTableColumns().getTableColumnArray(0).getName());
+ assertEquals("34.56", t.getCTTable().getTableColumns().getTableColumnArray(1).getName());
+
+ s = wb.getSheet("NoExplicitHeaders");
+ assertEquals(1, s.getTables().size());
+ t = s.getTables().get(0);
+ assertEquals("A1:B3", t.getReferences().formatAsString());
+ assertEquals("Column1", t.getCTTable().getTableColumns().getTableColumnArray(0).getName());
+ assertEquals("Column2", t.getCTTable().getTableColumns().getTableColumnArray(1).getName());
+ }
+
+ /**
+ * See https://stackoverflow.com/questions/44407111/apache-poi-cant-format-filled-cells-as-numeric
+ */
+ @Test
+ public void testNumericCellsInTable() throws IOException {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ XSSFSheet s = wb.createSheet();
+
+ //Setting up the CTTable
+ XSSFTable t = s.createTable();
+ CTTable ctt = t.getCTTable();
+ ctt.setId(1);
+ ctt.setName("CT Table Test");
+ ctt.setRef("A1:B2");
+ CTTableColumns cttcs = ctt.addNewTableColumns();
+ CTTableColumn cttc1 = cttcs.addNewTableColumn();
+ cttc1.setId(1);
+ CTTableColumn cttc2 = cttcs.addNewTableColumn();
+ cttc2.setId(2);
+
+ //Creating the cells
+ Cell c1 = s.createRow(0).createCell(0);
+ XSSFCell c2 = s.getRow(0).createCell(1);
+ XSSFCell c3 = s.createRow(1).createCell(0);
+ XSSFCell c4 = s.getRow(1).createCell(1);
+
+ // Inserting values; some numeric strings, some alphabetical strings
+ c1.setCellValue(12);
+ c2.setCellValue(34);
+ c3.setCellValue("AB");
+ c4.setCellValue("CD");
+
+ // Save and re-load
+ wb = XSSFTestDataSamples.writeOutAndReadBack(wb);
+ s = wb.getSheetAt(0);
+
+ // Check
+ assertEquals(1, s.getTables().size());
+ t = s.getTables().get(0);
+ assertEquals("A1", t.getStartCellReference().formatAsString());
+ assertEquals("B2", t.getEndCellReference().formatAsString());
+
+ // Done
+ wb.close();
+ }
}
Added: poi/trunk/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx?rev=1797914&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Wed Jun 7 12:39:41 2017
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org