I need to change date system to 1904 calendar using JXL (rather than 1900 calendar).
Everytime I write something in Excel using JXL API, date system is set to default and I need to change date system.
When I write negative time values (ex. -0:55) I get ################ instead of -0:55.
After I change in date system to 1904 in Excel ################ changes to -0:55.
Is there any way to change that automatically in JXL?
The actual version of jxl can't do that. I have the same problem, and I just did some debugging of jxl. Jxl reads the record for the 1904 date format correctly, but it doesn't write it. It always writes the 1900 date format as a constant.
I see the following solutions:
1) Change the jxl library so it can write the 1904 date format. That should be not so difficult. In the method WritableWorkbookImpl.write() you have to change in the line
NineteenFourRecord nf = new NineteenFourRecord(false);
the parameter value false to true for the 1904 date format.
In the private method DateRecord.calculateValue(boolean) you have to change the line
value = utcDays + utcOffsetDays;
in a way replacing utcOffsetDays by a different constant with the number of days for four years less for the 1904 date format.
There might be more places to change (especially when doing a pretty solution with the 1904 format as a property of a workbook), but this is what's relevant for my program. Also pay attention for the obligations of the LGPL when selling your program.
2) Easier solution: Put always positive time values into the cells and store the sign in the cell format. Use for example the format "[h]:mm:ss" for positive values and "-[h]:mm:ss" for negative values.
Disadvantage of solution 2): Thought looking perfect, there is a positive value in the cell which is meant negative, and when doing calculations the results can be wrong.