JXL and Timezone writing an Excel

Question

I try to create an Excel sheet with jxl. One of my field is a Date, and I live in GMT+1 TimeZone

I use something like this to do it:

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 writing in the correct format but with a -1 hour value (in GMT) I try to find a solution and i found this http://www.andykhan.com/jexcelapi/tutorial.html#dates But I can't pass a SimpleDateFormat to a DateCell. There is a way to do it? Now I using java.util.Calendar to add an hour, but is an horrible solution. Thank for help!

Answer 1

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

By default, it will use your system TimeZone to modify the date. You can pass to the constructor a 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

Yesterday I had a same issue. I live in CET time zone (Central European Time) and simple creation of the DateTime cell moved the time about one hour.

At first I tried to set the timezone on GMT as is suggested in the official tutorial.

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

It appears not to be working. The time modification was still same. So I tried to set up correct timezone to match the timezone in a 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 the things not to be too easy, there is except CET timezone also CEST (Central European Summer Time) which moves the time about another hour. When I tried to use dates in CEST time, it didn't worked again because there was one hour addition to the expected base. I guess that it would be solution to set up "CEST" timezone instead of "CET" for them but I didn't figure out how to get the proper timezone from the Calendar, it always returned CET.

Anyway finally I used a not nice, but reliably working solution.

• I have a factory method for date cell to have a configuration on one single place
• in that method, I convert the given Date to be in GMT timezone at first
• set up the timezone format to GMT
• disable timezone modification on DateTime cell.

These steps are not absolutely clean but it works for CET as well as 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 );
}