Formatting a workbook cell to return date

Aspose.Cells Product Family
1

Hi,

I am trying to programmatically create an excel using aspose . In this i set a cell value

with a java.util.Date value. But when i try to read that vaue i get a Double.

What i am expecting is when i excute row.getCell(4).getValueType()

i should get 2( which is for date). Currently i am getting 5 which is for Double.

Regards

Abhijit

2

Hi Abhijit,

Thank you for considering Aspose.

Well, I tried your mentioned issue regarding the date with the attached latest version of Aspose.Cells and it works fine (gives me 2 for date value type). Please see the following code sample.

Sample Code:

System.out .println(CellsHelper.getReleaseVersion());

Workbook workbook = new Workbook();

Worksheet ws = workbook.getWorksheets().addSheet(“TESTSHEET”);

java.util.Date date = new java.util.Date();

ws.getCells().getCell(“E5”).setValue(date);

System.out .println(ws.getCells().getCell(“E5”).getValueType());

System.out .println(“DONE”);

If you still face any problem, please share your code and we will look into it soon.

Thank You & Best Regards,

3

Hi,

The following is the method i use to create an excel file

private static void createTestXlsFile() {

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.getWorksheets().addSheet();

worksheet.setName("PnL Output");

Worksheet worksheet1 = workbook.getWorksheets().getSheet("PnL Output");

worksheet1.getCells().getCell("A1").setValue("Deal ID");

worksheet1.getCells().getCell("B1").setValue("Facility ID");

worksheet1.getCells().getCell("C1").setValue("Sub BL");

worksheet1.getCells().getCell("D1").setValue("Booking Entity ID");

worksheet1.getCells().getCell("E1").setValue("Period End Date");

worksheet1.getCells().getCell("F1").setValue("Funding");

worksheet1.getCells().getCell("G1").setValue("Interest");

worksheet1.getCells().getCell("H1").setValue("Commission DEVL");

worksheet1.getCells().getCell("I1").setValue("Other Commission");

worksheet1.getCells().getCell("J1").setValue("Dividend");

worksheet1.getCells().getCell("K1").setValue("Financial Instr");

worksheet1.getCells().getCell("L1").setValue("Other");

worksheet1.getCells().getCell("M1").setValue("Gross Up");

worksheet1.getCells().getCell("N1").setValue("IAS");

worksheet1.getCells().getCell("O1").setValue("Margin");

worksheet1.getCells().getCell("P1").setValue("Pre Tax Equiv");

worksheet1.getCells().getCell("Q1").setValue("After IAS");

worksheet1.getCells().getCell("A2").setValue(94200);

worksheet1.getCells().getCell("B2").setValue(70650);

worksheet1.getCells().getCell("C2").setValue(213);

worksheet1.getCells().getCell("D2").setValue(13450);

worksheet1.getCells().getCell("E2").setValue(new Date());

worksheet1.getCells().getCell("F2").setValue(1000);

System.out.println("Value type is " +worksheet1.getCells().getCell("E2").getValueType());

worksheet1.getCells().getCell("G2").setValue("");

worksheet1.getCells().getCell("H2").setValue("");

worksheet1.getCells().getCell("I2").setValue("");

worksheet1.getCells().getCell("J2").setValue("");

worksheet1.getCells().getCell("K2").setValue("");

worksheet1.getCells().getCell("L2").setValue("");

worksheet1.getCells().getCell("M2").setValue("");

worksheet1.getCells().getCell("N2").setValue("");

worksheet1.getCells().getCell("O2").setValue("");

worksheet1.getCells().getCell("P2").setValue("");

worksheet1.getCells().getCell("Q2").setValue("");

try {

workbook.save("src/test/resources/TestFile.xls");

} catch (IOException e) {

e.printStackTrace();

}

}

The System.out ..

System.out.println("Value type is " +worksheet1.getCells().getCell("E2").getValueType());

gives value as 2

But after the excel is generated if i try to read again and check value type(getValueType())) i get the value as 5.

If i open the excel file and check i see the date value in the excel as

39948.42
which is a double value

Now if i manually change the cell format (Right click- format cell- and then choose Date) then the value changes to the correct one as desired

5/15/2009

How do i foemat the cell to have this value programmatically?

Regards

Abhijit

4

Hi Abhijit,

Well, when you use setValue(Date) to set a date to a cell and just save the workbook, the valuetype is changed to double. This is due to the fact that in excel both double values and datetime values are saved just as double. So, if you do not specify the datetime Style of the cell , we can not recognize it as a date. Please set your desired datetime style (you may use Style.setNumber() or Style.setCustom() methods) for the cell after you input the datetime value to cell and then save the file, it will work fine.

See the following your piece of code, I have added a few lines to it.

.................

worksheet1.getCells().getCell("E2").setValue(new Date());

Style style = worksheet1.getCells().getCell("E2").getStyle();
style.setCustom("m/d/yyyy");
worksheet1.getCells().getCell("E2").setStyle(style);

worksheet1.getCells().getCell("F2").setValue(1000);

..............................

Thank you.