CODEWITHSUNDEEP
javaexcelexceptionapache-poi
Impulse The Fox
Impulse The Fox

Reputation: 2771

Apache POI can't format filled cells as numeric

I am trying to change CellTypes of already filled Cells in Apache POI, but I keep getting an IllegalStateException. The problem should be reproduceable using 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();
}

Also, when not setting any CellValue for c1 and c2, you can actually set their CellType to NUMERIC and suddenly the code works again. It also works without the CTTable.

Any ideas or workarounds? Or is it a bug of POI (since it tries fetching a string value from any Cell regardless of its CellType)?

Upvotes: 1

Views: 742

Answers (1)

Gagravarr
Gagravarr

Reputation: 48326

You need to use Apache POI 3.17 beta 1 or later for this to work (or a nightly build from after 20170607)

If you do, you can also make your code significantly simpler and cleaner too. As shown in the testNumericCellsInTable() unit test, your code could simplify to something like:

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

(Unlike your problem code, this does a mixture of integers, floating point numbers and strings for the table headers, to show the various options)

Upvotes: 2

Related Questions