java Apache POI无法将填充的单元格格式化为数字

Question

我试图更改ApachePOI中已填充的单元格的单元格类型，但我一直收到一个非法状态异常。应该可以使用POI（-ooxml）3.16重现该问题

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

您需要使用ApachePOI3.17Beta1或更高版本才能工作（或从20170607以后开始的夜间构建）

如果你这样做了，你也可以让你的代码变得更加简单和干净。如图in the ^{} unit test所示，您的代码可以简化为：

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

（与您的问题代码不同，它混合使用整数、浮点数和字符串作为表格标题，以显示各种选项）