apache poi excel hours format set cell value java

2
votes

I am working on Excel generation and also did all functionality in Excel, but one problem occurs. I want to set a cell value like HH:MM only not date. I have a string like 12:45,36:30,102:50. For each cell but, I want to detect that those cells are time format greater than 24 hours in Excel and if I select the cells, to show the sum of hours on Excel. I don't know how to do that. I found a similar post on stack ( HH:MM:SS xls reading using Apache POI), but this post only shows how to read those cells. I want to set a cell value like greater than 24 hours format only hours and minutes like 25:00, 205:00, 152:30.

Can you help me? I really appreciate your effect!

2
javaexcelapache-poi
What happens if you tell Apache POI to set the same format string that you'd set in Excel for those numbers? – Gagravarr
numbers are not calculate like 60 mins as 1 hour something calculation? – Stack Overflow User
Excel uses days since 1900/1904, with fractions for hours, so 0.5 is 12:00, and 1.5 is either 36:00 or 1900-01-02T12:00:00 depending on if you format it as a date or as hours! – Gagravarr

2 Answers

3
votes

Promoting a comment to an answer....

Excel stores dates as integer days since 1900 or 1904, depending on a setting, ish... (There's some leap year issues in there). Excel stores times as fractions of a day. So, on a 1900 window'd file, depending on the format string used

0.1 = 02:24:00
0.5 = 12:00:00
1.5 = 1900-01-02 12:00:00
1.5 = 36:00:00

So, if you want to store and display 36 hours, work out what fraction of a day that is (1.5), then apply a formatting string to it which supports >36 hours. Format as HH:MM and you'll get 12:00. Format as [HH]:MM and you'll get 36:00

Use Excel to work out what format string you need - POI just writes the format string you give it to the file, and Excel renders it just as if you'd written it in Excel!

2
votes

finally i got solution from @Gagravrr answer

below code for convert 12:00 to 0.5 or something

                            Date inserted_date = time_format.parse((String) obj);
                            CellStyle cellStyle = workbook.createCellStyle();
                            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("[HH]:MM"));
                            Calendar calendar = Calendar.getInstance();
                            calendar.setTime(inserted_date);
                            int hours = calendar.get(Calendar.HOUR_OF_DAY);
                            int minutes = calendar.get(Calendar.MINUTE);

                            //System.out.println("\nHOUR :"+hours+"\n");
                            //System.out.println("\nMINS :"+minutes+"\n");
                            //System.out.println("\nCALC :"+ hours/24d+(minutes/60d)/24d +"\n");
                            cell.setCellValue(hours / 24d + (minutes / 60d) / 24d);
                            cell.setCellStyle(cellStyle);

this code insert time only and sum selected cell automatically on excel sheet.