java to excel date formatting

Question

The problem is when I'm running my application and have a grid (with strings and date columns) and save it as an excel file.

When I save it for the first time everything is correctly formatted, but when I try to save the same exact grid again a second time, the date formatting is gone (it's just a float value that when i right click and format to a dateTime object works). When I restart my app it will work again for the first time, then lose formatting again

the code looks like this:

Calendar calendar = Calendar.getInstance();

calendar.setTime((Date)data);
            Date gmtDate = new Date(((Date) data).getTime() + (calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET)));

writableCell = new jxl.write.DateTime(sheetColumn, sheetRow, gmtDate, jxl.write.DateTime.GMT);

cellFormat = new jxl.write.WritableCellFormat (new jxl.write.DateFormat("m/d/yyyy h:mm");

writableCell.setCellFormat(cellFormat);

sheet.addCell(writableCell);

I kept break-pointing and everything is as it should be (it always knew it was a dateTime type before going in to the sheet), so I don't think it's from the code.

Has anyone else run into this issue?

You show the creation of the format, can you show how you're saving the file? — Devon_C_Miller, Jun 16 '12 at 02:15
Sorry about the late response (i had left for the day when it came in). so what i do is use a JFileChooser to save it as a .xls, the code before i play with the formatting i make the workbook jxl.write.WriteableWorkbook workbook = jxl.Workbook.createWorkbook(filename); sheet = workbook.createSheet("Report",0); then afteri do the formatting i say: workbook.write; workbook.close; — samligo, Jun 18 '12 at 18:03
I tried, but could not reproduce what you're seeing. Can you provide a SSCCE? — Devon_C_Miller, Jun 19 '12 at 02:36
i dug in a little deeper into the code (i didn't initially write it) and it seemed like it was reusing the format cell (which jxl doesn't allow), but when i took out the code that reuses the format cell i get an exception because the excel file is really big and and too many format cells are being created it seems — samligo, Jun 19 '12 at 23:55
Take a look through the code for any disabling of "rationalization". Either by setting a system property of "jxl.norat" or a call to "setRationalization(false)" (on a WorkbookSettings object). When it's enabled, jxl will try to eliminate duplicate cell formats. — Devon_C_Miller, Jun 20 '12 at 09:39

Mr. Polywhirl · Answer 1 · 2015-05-28T10:50:46.627

Try to define a static WritableCellFormat which takes care of the date formatting.

// Required classes.
import java.util.TimeZone;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.WritableCellFormat;

// Defined once.
public static final WritableCellFormat DATE_CELL_FRMT;
static {
    DateFormat df = new DateFormat("MM/dd/yyyy hh:mm");
    df.getDateFormat().setTimeZone(TimeZone.getTimeZone("GMT"))
    DATE_CELL_FRMT = new WritableCellFormat(df);
}

// Usage
writableCell = new DateTime(sheetColumn, sheetRow, gmtDate, DATE_CELL_FRMT);

score 2 · Answer 2 · answered Jun 16 '12 at 01:11

2

Try this:

cellFormat = new jxl.write.WritableCellFormat (new jxl.write.DateFormat("MM/dd/yyyy hh:mm");

answered Jun 16 '12 at 01:11

duffymo

305,152
44
369
561

I've tried that too unfortunately that gives me the same results – samligo Jun 16 '12 at 01:19

score 0 · Answer 3 · edited May 23 '17 at 12:31

It seems that your question is similar to "jExcelApi - cell date format reusing doesn't work". Probably, answer for that question help you with your problem.

Retelling of answer: According to FAQ (question: "I'm getting the error message 'too many different cell formats'") formats cannot be reused in different sheets because they are not designed to be reused this way

In your case, code may be like this:

WritableCellFormat format = new jxl.write.WritableCellFormat(new jxl.write.DateFormat("m/d/yyyy h:mm"));
for(java.util.Date date : someDateList){
  WritableCell cell = new jxl.write.DateTime(someColumn, someRow, date, format);
  sheet.addCell(cell);
}

java to excel date formatting

3 Answers3