XLS Date Export Error in Java

Aspose.Cells Product Family
1

Hello,

We are using the following version of aspose cells:

com.aspose aspose-cells 7.1.0

While trying to export a date with year 20019 to XLS, we encounter the following exception:

Exception in thread “main” java.lang.IndexOutOfBoundsException: ArgumentOutOfRange_DateTimeBadTicks
at com.aspose.cells.DateTime.(Unknown Source)
at com.aspose.cells.DateTime.(Unknown Source)
at com.aspose.cells.Cell.putValue(Unknown Source)
at com.aspose.cells.Cell.setValue(Unknown Source)

Here is the sample program executed:

   public static void main(String... args) {
	Workbook workbook = new Workbook();
	Worksheet sheet = workbook.getWorksheets().get(0);
	sheet.setName("Test Date");
					
	Calendar cal = Calendar.getInstance();
	cal.set(20019, Calendar.FEBRUARY, 1, 0, 0, 0);
	cal.set(Calendar.MILLISECOND, 0);
	
	sheet.getCells().get(0, 0).setValue(cal.getTime());
	
	try (OutputStream out = new FileOutputStream(new File("c:\\Test.xls"))) {
		workbook.save(out, SaveFormat.XLSX);
	} catch(Exception e) {
		throw new RuntimeException(e);
	}		
}

Could you please advise on how such cases should be handled?

Regards,
Horatiu Dan

2

@Asentinel,

Thanks for the code segment and details.

Please try to change the line:
i.e.,

sheet.getCells().get(0, 0).setValue(cal.getTime());

to:

sheet.getCells().get(0, 0).setValue(cal.getTime().toString());

Also, your code is wrong at the bottom, so you may correct it accordingly.
e.g
you may change the code segment:
i.e.,

try (OutputStream out = new FileOutputStream(new File("c:\\Test.xls"))) {
		workbook.save(out, SaveFormat.XLSX);
	} catch(Exception e) {
		throw new RuntimeException(e);
	}

to:

try (OutputStream out = new FileOutputStream(new File("c:\\Test.xlsx"))) {
		workbook.save(out, SaveFormat.XLSX);
	} catch(Exception e) {
		throw new RuntimeException(e);
	}

Hope, this helps a bit.

3

Thank you for your reply, although it looks more like a hack.

But, what should we do in case we want to use styles. Please see below:

public static void main(String… args) {
Workbook wb = new Workbook();
Worksheet sheet = wb.getWorksheets().get(0);

	Calendar cal = Calendar.getInstance();
	cal.set(20019, Calendar.FEBRUARY, 1, 0, 0, 0);
	cal.set(Calendar.MILLISECOND, 0);
	Date value = cal.getTime();
	
	Style styleDate = wb.createStyle();
	
	SimpleDateFormat dateFormat = (SimpleDateFormat) SimpleDateFormat.getDateInstance(DateFormat.MEDIUM, Locale.getDefault());		
	styleDate.setCustom(dateFormat.toPattern());
	
	Cell cell = sheet.getCells().get(0, 0);
	cell.setStyle(styleDate);
	cell.setValue(value);
			
	try (OutputStream out = new FileOutputStream(new File("c:\\Test.xlsx"))) {
		wb.save(out, SaveFormat.XLSX);
	} catch(Exception e) {
		throw new RuntimeException(e);
	}
}
4

@Asentinel,

Could you change the line of code if it works for your needs:
i.e.,

cell.setValue(value);

to:

cell.putValue(value.toString(), true );

Hope, this helps a bit.

5

I tried what you recommended, it does not make any difference - the value written is Fri Feb 01 00:00:00 EET 20019 and the cell is formatted as string, while I would expect it to be 2/1/20019 and formatted as custsom (mmm, d, yyyy).

6

@Asentinel,

I tested it manually in MS Excel but to no avail. I open a new workbook and then enter the value e.g “Fri Feb 01 00:00:00 PKT 20019”. Now right click on the cell and click “Format Cells…”, select custom in Number tab and enter “d mmm, yyyy” and click ok button. I do not see any change in the cell. In short, MS Excel cannot format this value in accordance with your desired pattern. If something cannot be done in MS Excel manually, the same cannot be done via Aspose.Cells.

If you still think this can be achieved, kindly do provide your expected file which contains the formatted value as per your needs, we will check on how to do it via Aspose.Cells.

7

I was able to put 2/1/20019 in XLS and format it as a date. See the picture.Capture.png (24.7 KB)

8

@Asentinel,

This is not what I requested. We need a template Excel file having the original value “Fri Feb 01 00:00:00 EET 20019” in a cell but when custom formatted (“mmm, d, yyyy”), it should display as “2/1/20019”. I tried in the MS Excel but it does not work, it means your custom formatting cannot work upon the value “Fri Feb 01 00:00:00 EET 20019”, the value should be a valid Date/Time value which MS Excel should accept. I also suspect MS Excel does not accept such farthest date.