how to populate excel without error warning using JSONArray and POI

Question

Am generating excel using POI and am looping an JSON Array to fill values in cells My JSON Array structure looks like this

[[1,"A","0","6","0","6"],[2,"B","3","3","0","6"],[3,"C","3","0","0","3"],[4,"D","4","0","0","4"],[5,"E","3","6","0","9"],[6,"F","3","6","0","9"],[7,"G","3","3","0","6"],[8,"H","3","0","0","3"],[9,"I","3","3","0","6"],[10,"J","2","0","0","2"],[11,"L","3","0","0","3"],[12,"M","3","0","0","3"],[13,"N","3","0","0","3"],[14,"O","3","0","0","3"],[15,"P","3","0","0","3"],[16,"R","3","0","0","3"]]

This Array am looping like this

for(int i=0;i<jsonArray.length();i++){
            Row row = sheet.createRow(rowCount);
            JSONArray array = jsonArray.getJSONArray(i);
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            for(int j=0;j<array.length();j++) {
                Cell  cell = row.createCell(j);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(array.getString(j));
            }

}

Here the data in the excel is dynamic and it may contain numbers or anything but am printing it to excel like this cell.setCellValue(array.getString(j)) which is causing an warning like thing in excel (i.e.the number in this cell is preceded by an apostrophe )the excel that gets generated may contain numbers float,String or even doubles.How can I resolve this issue kindly help me in resolving this issue.

Test if your value is a number, and write it as a number rather than a string if so? — Gagravarr
– Gagravarr, Commented Nov 6, 2015 at 11:55

centic · Accepted Answer · 2015-11-07 08:11:21Z

1

The method Cell.setCellValue() comes in a number of flavors, i.e. depending on the type of parameter that you pass in, it will create a text-cell, number-cell, boolean or any of the other support types.

As you are passing in a string, POI creates a text-cell in the Excel workbook. If you convert the string to a number (POI uses double to allow floating values as well) before populating the cell, it will be written as number-cell which should get rid of the warnings in Excel.

See the javadoc for details

answered Nov 7, 2015 at 8:11

centic

16k10 gold badges74 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to populate excel without error warning using JSONArray and POI

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related