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

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1797914 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 100 insertions, 2 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
index ad9e09befc..ef8ace666b 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFTable.java
@@ -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 POIXMLDocumentPart implements Table {
         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 POIXMLDocumentPart implements Table {
      * 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 POIXMLDocumentPart implements Table {
         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++;
             }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
index 69b412a51f..e9e7f3a885 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFTable.java
@@ -38,6 +38,7 @@ import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 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();
+    }
 }
diff --git a/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx b/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx
new file mode 100644
index 0000000000..c9bb110adc
--- /dev/null
+++ b/test-data/spreadsheet/TablesWithDifferentHeaders.xlsx