Formatting Strings before writing into excel sheet in POI

Question

I have a String array with values as below:

 String[] myArray = new String[4];
 myArray[0] = "one";
 myArray[1] = "2012-02-25";
 myArray[2] = "12345.58";
 myArray[3] = "1245";

I want to write this array in to a excel sheet using POI where each value will go in a new column as below:

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("mySheet");
Row myRow = sheet.createRow(1);
Cell myCell;

for(int i=0;i < my.length;i++){
   myCell = myRow.createCell(i);
   myCell.setCellValue(myArray[i]);
   sheet.autoSizeColumn(i);
}

FileOutputStream out;

try {
        out = new FileOutputStream("myOutputFile");
        wb.write(out);
        out.close();
} catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

However, as obvious, everything will be dumped in the excel sheet as text. And I get a green colored notification on top left of every cell indicating "number stored as text", etc.

Assuming, that I will be unable to change the way I get the input because of source system limitations i.e. I will continue getting input only as Strings, how can I process the format of actual value inside the String and dump a date as date in excel, a number as number, a text as text and a double value as a properly decimal formatted double value ?

Please note that I do not have the information about the type of value I am getting in the String. I have to decide it at runtime whether the value is a text, number, float, date, etc and then put in appropriately in the excel sheet.

Please do not flag the question before reading properly.

Thanks for reading!

possible duplicate of [using poi , How to set the Cell type as number](http://stackoverflow.com/questions/15248284/using-poi-how-to-set-the-cell-type-as-number) — epoch, Sep 23 '13 at 09:52
but also read the answer here http://stackoverflow.com/questions/14618081/apache-poi-excel-cell-formatting-is-not-working-beyond-32748-cell-in-a-sheet?rq=1 - number of definable styles is limited! — fvu, Sep 23 '13 at 09:53
@epoch: Sorry... But its not a duplicate... As in the question you have referenced me to has inherent information that the String in question is already a number or a float or a date.. Which is not the case here.. I do not have the information about the value inside the String beforehand... I don't know if the value is a date or a number or a float.. What to do in that case ? — Vicky, Sep 23 '13 at 09:56
@All: Its not a duplicate question... please read the question carefully before flagging... — Vicky, Sep 23 '13 at 09:59

score 1 · Accepted Answer · answered Sep 23 '13 at 10:24

You can use following code..

 if(myArray[i].matches(".*\\d.*") && ! myArray[i].contains("-"))    //For date assuming '-' would be a separator always
         {
            //Its a number(int or float).. Excel treats both as numeric

            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
            mycell.setCellStyle(style);
            mycell.setCellValue(Float.parseFloat(myArray[i]));
         }
         else if(myArray[i].contains("-"))
         {
            // Its a date
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            mycell.setCellStyle(cellStyle);
            mycell.setCellValue(myArray[i]);
         }
         else
         {
            // Its a string/Text..
            mycell.setCellValue(myArray[i]);
         }

score 0 · Answer 2 · answered Sep 23 '13 at 14:03

0

You can try first parsing the string as an integer. If error as a float. If error as a date. If error, well should be a string.

answered Sep 23 '13 at 14:03

polypiel

2,321
1
19
27

Formatting Strings before writing into excel sheet in POI

2 Answers2