1

I am working in a project where I have data in HH:MM:SS.sss format, I have to generate report in Excel/Spreadsheet. I am using POI to generate the excel file, but I am facing the following problems:

  1. If I set data in string format i.e cell.setCellValue("02:45:6.7"); it is impossible to use Excel formulas such as average, sum, etc. Even if I format the cell in [h]:mm:ss.000;@ i.e. cs.setDataFormat(df.getFormat("[h]:mm:ss.000;@")) is does not works, as it is string.

    HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook.createDataFormat(); cs.setDataFormat(df.getFormat("[h]:mm:ss.000;@"));

  2. Excel does not have any function(as per my knowledge) which takes data in hh:mm:ss.sss format, I tried using 'TIME(HH,MM,SS)' and formatting the cell to [h]:mm:ss.000;@ it does not shows value after decimal point replaces it with 000 i.e TIME(02,45,6.7) is displayed as 02:45:6.000

  3. I tried making changes in POI source code, I tried the steps as per in this question https://stackoverflow.com/a/10306350/2513084 Still it does not work. The generated file says - some formulae or name of this file contains built-in functions which Kingsoft Spreadsheet does not support, and recalculation of these formulas or name may cause incorrect results. Microsoft Office was not able to open the file it gave the error File Error: data may have been lost.

Community
  • 1
  • 1
Priyank
  • 56
  • 6
  • Did you try reading the [Apache POI Documentation on creating date cells](http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells)? – Gagravarr Oct 15 '14 at 09:03
  • @Gagravarr Thanks for answering are you referring to this one ? "cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("m/d/yy h:mm"));" – Priyank Oct 15 '14 at 10:07
  • Put in whatever format you actually want (as copied from Excel), then set a suitable java date object containing your time, as per that documentation, and you should be done – Gagravarr Oct 15 '14 at 10:31

1 Answers1

0

I was not working with POI for a long time, but as far as I recall ... the dates are nothing else as numbers (milliseconds). Only the cell type is defined as a date/numeric cell.

The actual visual representation of the cell is then defined elsewhere.

Some old code of mine (probably not the latest POI version has this statement):

HSSFCell cell = newRow.createCell(column);

Date date = new Date(some_date_value); 
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(date);

You can set the cell style (display format) as:

cell.setCellStyle(style);

So I would dig into the style part ... and how to set it (Sorry have no working example of this part). Maybe it will work with your code ... as you are not setting the value as numeric but as a string.

Drejc
  • 14,196
  • 16
  • 71
  • 106
  • Thanks for answering, I guess this won't work. How can I parse Date in "HH:MM:SS.sss" using 'DateTimeFormatUtils.parseDate(value);' There is no such method for this specific format. – Priyank Oct 15 '14 at 08:49
  • This is a method of mine, just ignore it. You can parse String into Date with SimpleDateFormat (just Google it up). – Drejc Oct 15 '14 at 10:25
  • If I parse string to Date using SimpleDateFormat it will include date part also e.g. DateFormat ff =newSimpleDateFormat("HH:mm:ss.SSS") – Priyank Oct 15 '14 at 11:19
  • The result is a Date object ... format = SimpleDateFormat(yourFormat); Date date = format.parse(stringDate); – Drejc Oct 15 '14 at 11:23
  • If I parse string to Date using SimpleDateFormat it will include date part also e.g. DateFormat ff =newSimpleDateFormat("HH:mm:ss.SSS"); SOP(ff.parse("08:6:2.011")); it takes time from 1 Jan 1970 I only want the "time" part but it includes the "Date part also" Is there any possible way to include only timming part.. cell.setCellValue(..) take 3 type of values-- String,Rich Text and Date. – Priyank Oct 15 '14 at 11:26