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

Question

我正在尝试更改 Apache POI 中已填充单元格的单元格类型，但我不断收到 IllegalStateException。使用 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 的一个错误(因为它尝试从任何 Cell 获取字符串值，无论其 CellType 如何)？

Answer 1

您需要使用 Apache POI 3.17 beta 1 或更高版本才能正常工作(或 20170607 之后的夜间版本)

如果这样做，您还可以使您的代码变得更加简单和清晰。如图in the testNumericCellsInTable() 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)
    ));

(与您的问题代码不同，它混合了表格标题的整数、 float 和字符串，以显示各种选项)