CODEWITHSUNDEEP
javaexcelapache-poi
Rodin10
Rodin10

Reputation: 337

Apache POI - Allow calculation with time strings

I have an excel sheet which contains some time values. These values are Durations formatted to Strings. When I try to do a sum on those values I get a 0 as result since Excel can't do sums on Strings. When I hit the enter button in the formula bar it does become a time so I the sum works. How do I change the value of the cell from a String to a time value? I already have the date format set up as [hh:mm] with a DateFormat

I start with an amount of time converted into seconds which I convert into a Duration

Duration clockedDuration = Duration.ofSeconds(clockedSeconds)

Then I format the Duration to a String using DurationFormatUtils

DurationFormatUtils.formatDuration(duration.toMillis(), "HH:mm", true)

Then I set the cell value to the String that was just made 

c.setCellValue(clockedDuration)

I then set the CellStyle to one that has a DataFormat that's set up as [hh]:mm

Lastly I make a sum of all the values in that particular column (B in this instance)

c = r.createCell(i++)
c.setCellFormula("SUM(B2:B" + lastRow +")")
c.setCellType(Cell.CELL_TYPE_FORMULA)

This is the sheet before I hit the enter button on the first value in the row (09:52)

enter image description here

This is the sheet after I've hit the enter button in the formula bar

enter image description here

Upvotes: 2

Views: 578

Answers (2)

Rodin10
Rodin10

Reputation: 337

I fixed it by dividing the seconds by 86400 to get a decimal value and then use [hh]:mm as the data format. 

c.setCellValue(clockedSeconds / 86400)  // 24(hours) * 60(minutes) * 60(seconds) = 86400

Upvotes: 1

Nikolas
Nikolas

Reputation: 2420

Just a hunch: you mentioned to actually use formulas, and if you are hitting enter on the sheet "the sum works". Applying formulas is a two-liner, in addition to set the "value", you also need to format the cell type as being a formula:

XSSFRow row = worksheet.getRow(rowIndex);
XSSFCell cell = row.getCell(cellIndex);
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("sum(start:end)");

Edit: You also will want to make sure to use the XSSF-Classes

Upvotes: 0

Related Questions