CODEWITHSUNDEEP
javaexcelapacheapache-poi
Ihavenoclue
Ihavenoclue

Reputation: 60

Apache POI is not formating cells properly

I am currently facing some problems working with Apache POI.

My current project is to read data out of a xml-file and write it into a excel file. The excel-writing part is done with Apache POI, but I am stuck with a problem. When I try to write some duration with the data format ("mm:ss,sss;@") this means I write a time in minutes:seconds, milliseconds.

The problem is, that this data is not formatted correctly and you are not able to work with it in excel. You can see that in excel by the fact that it is written left justified to the cell. But if you click on the cell in excel and press enter, it gets right justified and you can work with it.

The following code is used by me to create the sheet and workbook.

Workbook workbook = new HSSFWorkbook();
    sheet1 = workbook.createSheet("Daten");
    style = workbook.createCellStyle();
    CreationHelper creationHelper = workbook.getCreationHelper();
    style.setDataFormat(creationHelper.createDataFormat().getFormat("mm:ss,sss;@"));

The following method is used to write the data to the cell. "minutes", "seconds" and "millisecods" are in the original programm methods that gather the needed information. "cellcount" is the position in the row, the data should be written to.

public static void createCellTime(Row row, int cellcount, xmlInterface interface, Main main){
        Cell cell = row.createCell(cellcount);
        try {
            String cellTimeStringTime = minutes + ":" + seconds + "," + milliseconds;
            cell.setCellValue(cellTimeStringEchtzeit);
        } catch (NullPointerException e) {
            cell.setCellValue("-----");
        }
        cell.setCellStyle(main.style);
    }

The main.style Cellstyle is a style defined in the main method as seen in the first code block.

If you need any further information, feel free to ask for it.

I hope there is a nice solution for this, as my current workaround - a tool that clicks every cell and presses enter - is an awful way :)

Upvotes: 2

Views: 725

Answers (1)

Axel Richter
Axel Richter

Reputation: 61945

The s in a format code is the code for seconds and not for milliseconds. Fractions of a second: h:mm:ss.00.

Also the stored format strings are in en_US always. The dot (.) ist the decimal delimiter, independent of your locale. So your format code should be .getFormat("mm:ss.000").

Also don't put a string into the cell. Use DateUtil.convertTime to get the time value from the string and put that double value into the cell.

...
String cellTimeStringTime = "00:" + minutes + ":" + seconds;
double timeValue = DateUtil.convertTime(cellTimeStringTime);
double millisecValue = Double.parseDouble(""+milliseconds);
millisecValue = millisecValue / 24 / 60 / 60 / 1000;
timeValue += millisecValue;
cell.setCellValue(timeValue);
...

Upvotes: 2

Related Questions