Timezone issue when exporting to excel format

Hello everyone,
 
 
Actually I am facing an issue with timezone whenI am export my report to xlsx format. We have integrated BIRT in our application and used ReportEngine API to generate report. So in RenderTask ,i have set timezone
using
 
renderTask.setTimeZone(<User set timezone">);
 
Using this,we could generate report in all other format like html,pdf .All are changing the data according to timezone except excel format.
 
Please help if any other configuration is needed or not.

Find more posts tagged with

Comments

mwilliams

What is your BIRT version?

shalu30aug

We are using 4.3.1 version of BIRT

mwilliams

Any way you could post the code you're using so I can test on your exact code? Thanks.

shalu30aug

This is the code snippet I have used to create a RenderTask and then generate the report in excel format.
 
I have set the timezone which we extract based on user's timezone at runtime with the help of userid.
 
 
 
IRenderTask
renderTask=engine.createRenderTask(doc, design); // doc is the rptdocument
 
/*----setting the timezone ----*/
 
if(timeZone!=null){
TimeZone userTimeZone=TimeZone.getTimeZone(timeZone);//timeZone is the string e.g "Asia/Kolkata"
renderTask.setTimeZone(userTimeZone);
}
else{
renderTask.setTimeZone(TimeZone.getDefault());
}
 
This part of the code is the generation of excel:
 
HTMLRenderOption options = new HTMLRenderOption();
response.setContentType(mimeType);
response.setHeader("Content-Disposition", "attachment; filename=\"" + reportName ".xlsx");
 
options.setBaseURL(baseUrl);
options.setImageHandler(new HTMLServerImageHandler());
options.setMasterPageContent(true);
options.setSupportedImageFormats("PNG;GIF;JPG;BMP;SWF");
options.setOutputStream(response.getOutputStream());
options.setOutputFormat("xlsx");
options.setEmitterID("uk.co.spudsoft.birt.emitters.excel.XlsxEmitter");
options.setOption("ExcelEmitter.StructuredHeader", true); 
options.setOutputStream(response.getOutputStream()); 
 
task.setRenderOption(options);
 
//run the report 
task.render();
task.close();
 
 
 
 
I have tried with
 
options.setOption("timeZone" ,timeZone)
 
I have also tried with EXCELRenderOption class.
 
But still its not working.
 
Excel is generated with different elements behaving in different ways.
 
e.g Footer is generated with timestamp taking timezone in consideration but the data in the grid ,table is not showing data w.r.t timeZone.
 
Please help.Its very critical.

mwilliams

This works for me (executed from within Eclipse as a Java project): <pre class="_prettyXprint _lang-auto _linenums:0">
EXCELRenderOption options = new EXCELRenderOption();
options.setOutputFileName("myReport.xlsx");
options.setOutputFormat("xlsx");

renderTask.setRenderOption(options);
TimeZone tz = TimeZone.getTimeZone("Asia/Kolkata");
renderTask.setTimeZone(tz);

renderTask.render();
</pre>

shalu30aug

I have tried this code in my application....but it behaves very differently..In the footer the generated time of the report is coming according to the timezone selected but the in the table,that data fetched from database are not converter according to timezone.It is shown in the way its was saved.
 
Likewise if i have used a dynamic text and generated the current time...it again is taking timezone in consideration.
 
But the data in table are not taking timezone in consideration.

mwilliams

Ah. My mistake. I was only looking at the footer time. I'll take a look at the database data issue in the morning.

mwilliams

This appears to be a bug with the XLS emitter. You can log the bug in the bugzilla in the community area of eclipse.org/birt. Post the bug info in here for future reference if you post one. Thanks!

shalu30aug

I have logged the bug and the link to access - <a data-ipb='nomediaparse' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=452599'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=452599</a>

mwilliams

Thanks for the update!