Apache POI - Change row's cell format from String to Other

Question

Hi I'm using Apache POI in Java to read from an input txt file and paste onto xls sheet.

I pass each line from the input txt file as string to paste onto the sheet, however, all of my numbers/date fields are pasted as strings and I am struggling to change to relevant number/date format.

Below is what I am using to read my input:

              LinkedList<String[]> text_lines = new LinkedList<>();
              try (BufferedReader br = new BufferedReader(new FileReader("C:/input.txt"))) {
                  String sCurrentLine;
                  while ((sCurrentLine = br.readLine()) != null) {
                      text_lines.add(sCurrentLine.split("\\t"));
                      }
                  } catch (IOException e) {
                  e.printStackTrace();
                  }

Below is what I am using to paste:

         for(String[] line : text_lines){
            Row row = worksheet.createRow(row_num++);
            int cell_num = 0;
            for(String value : line){
                 Cell cell = row.createCell(cell_num++);
                 cell.setCellValue(value);
                 }}

And the below is what I have tried to change a particular cell to number format instead of string but does not work and the format of the cell is still as a string when my xls is produced

        String formatStr = "#,##0";
        HSSFCellStyle commaNumber = output.createCellStyle();
        HSSFDataFormat format = output.createDataFormat();
        commaNumber.setDataFormat(format.getFormat(formatStr));

        c.setCellStyle(commaNumber);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
          }

Please can anyone help? Thanks!

Answer 1

This code should work if you can make sure that "value" contains a valid parseable String:

for(String value : line)
{
    Cell cell = row.createCell(cell_num++);
    cell.setCellValue(new Double(value));
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}

EDIT As you said your values are not always parsable, you can do this "trick".

Please note: Quick hack! You should not use Exceptions for flow control!

You can try to parse the double and if you not succeed => set the String value.

for(String value : line)
{
    Cell cell = row.createCell(cell_num++);
    try
    {
        cell.setCellValue(new Double(value));
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    }
    catch(Exception ex)
    {
        cell.setCellValue(value);
    }
}