Apache POI无法将填充单元格设置为数值格式

Question

public static void main(String[] args) throws Exception
{
    //Setting up the workbook and the sheet
    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
    XSSFCell 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*/"12"); //Numbers have to be inputted as a string
    c2.setCellValue(/*34*/"34"); //for the code to work 
    c3.setCellValue("AB");
    c4.setCellValue("CD");

    //With those lines, the code would also crash
    //c1.setCellType(CellType.NUMERIC);
    //c2.setCellType(CellType.NUMERIC);

    //On write() it produces a "java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell"
    FileOutputStream fos = new FileOutputStream("test.xlsx");
    wb.write(fos);
    fos.flush();
    fos.close();
    wb.close();
}

而且，当不为c1和c2设置任何CellValue时，实际上可以将它们的CellType设置为Numeric，突然之间代码又可以工作了。它在没有CTTable的情况下也可以工作。

有什么想法或变通办法吗？还是POI的一个bug（因为它尝试从任何单元格中获取字符串值，而不管它的单元格类型如何）？

Answer 1

您需要使用Apache POI 3.17 beta 1或更高版本（或20170607之后的夜间构建）

如果这样做，您还可以使您的代码更加简单和干净。如TestNumericCellsIntable()单元测试所示，您的代码可以简化为以下内容：

    Workbook wb = new XSSFWorkbook();
    Sheet s = wb.createSheet();

    // Create some cells, some numeric, some not
    Cell c1 = s.createRow(0).createCell(0);
    Cell c2 = s.getRow(0).createCell(1);
    Cell c3 = s.getRow(0).createCell(2);
    Cell c4 = s.createRow(1).createCell(0);
    Cell c5 = s.getRow(1).createCell(1);
    Cell c6 = s.getRow(1).createCell(2);
    c1.setCellValue(12);
    c2.setCellValue(34.56);
    c3.setCellValue("ABCD");
    c4.setCellValue("AB");
    c5.setCellValue("CD");
    c6.setCellValue("EF");

    // Setting up the CTTable
    Table t = s.createTable();
    t.setName("TableTest");
    t.setDisplayName("CT_Table_Test");
    t.addColumn();
    t.addColumn();
    t.addColumn();
    t.setCellReferences(new AreaReference(
            new CellReference(c1), new CellReference(c6)
    ));

（与您的问题代码不同，这为表头混合了整数、浮点数和字符串，以显示各种选项）