CODEWITHSUNDEEP
javaexcelapachecsv
user2883071
user2883071

Reputation: 980

Excel cannot understand the format of data written by java

I am having 2 issues using the apache POI to write data from a csv into an excel file. The data consists of dates, and numbers The issues are:

1) The numbers are written as strings.
2) Excel cannot read the date format (this messes the graphs up)

The code (that I received help with previously):

    String name = "test";
    Sheet sheet = wb.getSheet(name);
    if (sheet == null) {
        sheet = wb.createSheet(name);
    }

    int rowCount = 0;
    Scanner scanner = new Scanner(new File("/tmp/" + name + ".csv"));
    while (scanner.hasNextLine()) {
        String[] rowData = scanner.nextLine().split(",");
        for (int col = 0; col < rowData.length; col++) {
            Row row = sheet.getRow(rowCount);
            if (row == null)
                row = sheet.createRow(rowCount);
            Cell cell = row.getCell(col);
            if (cell == null) {
                cell = row.createCell(col);
            }
            cell.setCellValue(rowData[col]);
        }
        rowCount++;
    }

    wb.write(new FileOutputStream(excel));
}

1) I tried using Double.parseDouble(rowData[col]) when entering the data into the excel file. but this gives an empty string error. I even set the cell format with style.setDataFormat(format.getFormat("#,##0.0000")); but it still does not work

2) I tried using the date format cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yyyy hh:mm:ss")); but still the excel graphs can't read this format. (when I manually copy and paste from the csv file it works).

So basically, when copying data using the apache poi, none of the other data that relies on the copied cells is updated. for example if a cell has a value of the average of 100 cells, and I manually copy data into those cells, it updates automatically. But when it copies through java, the cells do not update.

Upvotes: 0

Views: 709

Answers (1)

Joop Eggen
Joop Eggen

Reputation: 109532

The following should do something more.

try {
    double value = Double.parseDouble(rowData[col]);
    cell.setCellValue(value);
} catch (NumberFormatException | NullPointerException e) {
    String value = rowData[col];
    cell.setCellValue(value);
}

(However you might not use Apache POI and straight copy the CSV file to a .xls, if it is just a need for double-click reading by Excel.)

Upvotes: 1

Related Questions