Excel does not recognize jxl dateformat

Question

I have this code to write to an excel file.

DateFormat customDateFormat = new DateFormat("yyyy/MM/dd HH:mm:ss");
WritableCellFormat dateFormat = new WritableCellFormat(new WritableFont(WritableFont.ARIAL), customDateFormat);

I can successfully write to the excel file but there's one problem which is that Excel does not recognize my date format("yyyy/MM/dd HH:mm:ss").

But when I select the cell and hit enter. The text formatted into dateformat that i specify.

When I select the Format Cell option,it's already pre-selected at the "yyyy/MM/dd HH:mm:ss") but yet not in that format.

Is it concerning with excel or jxl? I have been searching for the whole day. Thanks!

Have you checked this question? http://stackoverflow.com/questions/10872112/jexcel-formula-calculation-error. It seems that jxl does not compute formulas, which will probably also apply for formatted dates. — Mare Geldenhuys, Sep 10 '15 at 09:09
@MareGeldenhuys Oh,I see. Is there any alternative ways , APIs , Libraries that I could use? Thanks! (But the excel recognize the format as date but just not in the specified format. And jxl doc has a tutorial on formatting dates. "http://www.andykhan.com/jexcelapi/tutorial.html#formatting dates)" — sonny, Sep 10 '15 at 09:26
Yes, it does handle the date value correctly as shown in your example, yet it does not do the necessary evaluation of the formula. From the same question link above it seems Apache POI has a FormulatEvaluator for this purpose. More info here : https://poi.apache.org/spreadsheet/eval.html — Mare Geldenhuys, Sep 10 '15 at 10:00

sonny · Accepted Answer · 2015-09-23T07:08:52.133

1 ) Convert String to Date and Date to String (For my case)

I receive Date from my database as String.
Use SimpleDateFormat to parse and format the String.
Use a String variable to return the result.

public static String convertDateToStringDMY(String input) throws ParseException{
      SimpleDateFormat df1 = new SimpleDateFormat("dd/MM/yyyy");
      SimpleDateFormat df2 = new SimpleDateFormat("dd-MM-yyyy");
      String temp,result;    
      if(input!=null){
      Date date = df2.parse(input);
      temp = df1.format(date);
      result = temp;
      }
      else{
          result = "";
      }
      return result;
    }

2) @Mare Geldenhuys is correct.
jxl does not compute formulas which means the date will be inserted correctly into the excel file and Excel will recognize them as date. But you can't perform formula on them(E.g you cannot +1 hour to all dates,you have to manually +1 hour to every cell. Thankfully there are tricks for that.).

UPDATE

I found a solution for the excel to pre-select the cell as Date.
First,make sure the data is in date type (not string,if it's string,excel will pre-select as Custom Type).
And set the CellFormat to be by using a DateFormat Method.

WritableCellFormat dateformat1 = new WritableCellFormat(new DateFormat("d/m/yyyy"));

Excel does not recognize jxl dateformat

1 Answers1