JXL and Timezone, writing Excel

Question

I am trying to create an excel sheet with jxl. One of my fields is a date and I live in GMT + 1 TimeZone

I am using something like this:

WritableCellFormat EXCEL_DATE_FORMATTER = new WritableCellFormat(new DateFormat("dd/MM/yyyy hh:mm")); ... WritableCell cell = null; cell = new jxl.write.DateTime(col, row, date); cell.setCellFormat(EXCEL_DATE_FORMATTER); 

The date is written in the correct format, but with a -1 hour value (in GMT) I am trying to find a solution, and I found it http://www.andykhan.com/jexcelapi/tutorial.html#dates But I can not pass SimpleDateFormat to DateCell Is there a way to do this? Now I use java.util.Calendar to add an hour, but this is a terrible solution. Thanks for the help!

Answer 1

The jxl.write.DateTime class has several constructors (see the API ).

By default, it will use your TimeZone system to change the date. You can pass the constructor jxl.write.DateTime.GMTDate object to disable this. Here is the code you should use:

 WritableCell cell = null; cell = new jxl.write.DateTime(col, row, date, DateTime.GMT); 

Answer 2

I had the same issue yesterday. I live in the time zone of CET (Central European Time), and just creating a DateTime cell moved the time around an hour.

At first I tried to set the time zone to GMT , as suggested in the official textbook.

 final DateFormat valueFormatDate = new DateFormat( "dd.MM.yyyy HH:mm" ); valueFormatDate.getDateFormat().setTimeZone( TimeZone.getTimeZone( "GMT" ) ); 

It seems that it is not working. The temporary modification was still the same. So I tried to set the correct time zone to match the time zone in the Date object.

 final DateFormat valueFormatDate = new DateFormat( "dd.MM.yyyy HH:mm" ); valueFormatDate.getDateFormat().setTimeZone( TimeZone.getTimeZone( "CET" ) ); 

This worked perfectly as I expected. But things that are not too light are, besides the CET time zone, also CEST (Central European Daylight Saving Time), which moves the time by about one hour. When I tried to use dates in CEST, it did not work because an hour was added to the expected base. I believe that it would be a decision to set the CEST time zone, instead of them “CET” for them , but I did not understand how to get the correct time zone from Calendar , it always returned CET.

In the end, I used a bad, but reliable working solution.

• I have a factory method for a date cell to have the configuration in one place
• in this method, I convert the given Date to the first time zone
• set timezone format to GMT
• disable changing the time zone in the DateTime cell.

These steps are not entirely clean, but they work on CET as well as on CEST dates. The final code is here:

 public class DateUtils { // formatter to convert from current timezone private static final SimpleDateFormat DATE_FORMATTER_FROM_CURRENT = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // formatter to convert to GMT timezone private static final SimpleDateFormat DATE_FORMATTER_TO_GMT = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); static { // initialize the GMT formatter final Calendar cal = Calendar.getInstance( new SimpleTimeZone( 0, "GMT" ) ); DATE_FORMATTER_TO_GMT.setCalendar( cal ); } public static Date toGMT( final Date base ) { try { // convert to string and after that convert it back final String date = DATE_FORMATTER_FROM_CURRENT.format( base ); return DATE_FORMATTER_TO_GMT.parse( date ); } catch ( ParseException e ) { log.error( "Date parsing failed. Conversion to GMT wasn't performed.", e ); return base; } } } 

And there is a factory method

 /** builds date cell for header */ static WritableCell createDate( final int column, final int row, final Date value ) { final DateFormat valueFormatDate = new DateFormat( "dd.MM.yyyy HH:mm" ); valueFormatDate.getDateFormat().setTimeZone( TimeZone.getTimeZone( "GMT" ) ); final WritableCellFormat formatDate = new WritableCellFormat( valueFormatDate ); // create cell return new DateTime( column, row, toGMT( value ), formatDate, DateTime.GMT ); }