Export report to Excel with a cell-format [h]:mm:ss

Schmatte

Hello,

we have to do an export of a BIRT-report to Excel. There we must create a cell, which must fullfill the following requirements:

1.) match the style: "HOURS:MINUTES:SECONDS"
2.) must be a Date/Time-Field, because it has to be used in additional calculation
3.) value of HOURS could be bigger than 24 (example: "55:30:30").

Because of this three points we decided to use the pattern "[h]:mm:ss" to format the cell. This should fullfill the requirements in Excel.
In addtion we have to set a double-value in the report, because Excel interprets the value of 1 as 24 hours. That's why, if we want to set 1 second in the Excel-cell, we must put the value 1 / (24 * 60 * 60) to Excel. Now we have the problem, in BIRT we can't create a format of a parameter/date-field/Number-Field, which would fullfill this task. We tried the same scenario with Apache POI, without problems:

FileOutputStream outTwo = new FileOutputStream("dateFormat.xls");
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheetTwo = hssfworkbook.createSheet("new sheet");
HSSFCellStyle cs = hssfworkbook.createCellStyle();
HSSFDataFormat df = hssfworkbook.createDataFormat();
cs.setDataFormat(df.getFormat("[h]:mm:ss"));
HSSFRow rowTwo = sheetTwo.createRow((short) 0);
HSSFCell cellTwo = rowTwo.createCell((short) 0);
// 70 seconds
Double value = new Double(70 * 1.1574074074074074074074074074074e-5);
cellTwo.setCellValue(value);
cellTwo.setCellStyle(cs);
hssfworkbook.write(outTwo);

We've got the following questions:

1.) Does BIRT even provide such a funtionality to get the needed output in Excel?
2.) If the first question could be answered with "yes", what must be done for it?

Any hints to solve this problem are welcome.

Thanks in advance.

Regards.