DateTime Issue

Aspose.Cells Product Family
1

I have excel sheet and using java code.

In one of the col containing Date time type,

Date can be in any format like dd mm yyyy or dd mm yyyy hh mm.

case CellValueType.DATETIME:if( cellValue != null && GregorianCalendar.class.isInstance( cellValue ) )

{

Date date = ( (GregorianCalendar) cellValue).getTime();

value = formatter.format( date );

( (GregorianCalendar) cellValue).toString();

}

break;

This is how I get the date string. But what I want is to get the exact value of date present in the excel cell without any formatting. I don't want to use a formatter. Is it possible to get the date as it was present in the cell taking into consideration that cell type is date in excel?

Expect a reply asap.

Thanks!

This message was posted using Aspose.Live 2 Forum
2

Hi,


If you want to get the actual value saved in any cell. You can simply output it in string format regardless of the Column Type.
Attached is a sample xlsx file. I have manually saved different Date values in Cells A2, A3 and A4. Through my below code I output the values in Console.
JAVA
Workbook wb = new Workbook();
wb.open(“C:\test.xlsx”);
Worksheet ws = wb.getWorksheets().getSheet(0);
Cells cells = ws.getCells();
for(int i = 1; i < cells.getMaxRow() ; i++)
{
String s = cells.getCell(i, 0).getStringValue();
System.out.println(s);
}
Hope your question is answered. Please feel free to write back again
3

But, in my code written same works for your attached file, but same pattern I mean the lst one mm/dd/yyyy hh:mm shows result as dd/mm/yyyy hh:mm when I do cell.getStringValue();

If in file, input is 12/23/2011 15:34, cell.getStringValue() reads it as 23/12/2011 15:34 which is wrong for mm/dd/yyyy hh:mm format.

Due to security issues, I cant share the sheet but it is html based spreadsheet and before opening workbook, I am setting UTF-16 encoding in open options and then while opening I pass FileFormatType.HTML as argument. Could you please help?

4

Hi,

Thanks for your question.

It is because of your computer date format. Please change it accordingly. See the screenshot.

5

It shouldn't be machine dependent. I don't know, on which machine my code will run. Also, with dd/mm/yyyy time on my machine, the file attached by you is getting read properly but not my file which is html based spreadsheet.

Ajmad Sahi has worked on the html based spreadsheet before. Please check with him if you need more inputs and help us in fixing this. Thanks.

6

Hi Vineet,


If the problem is keep coming with your file then it would be of great help that you share your input file with us. We can make this thread “Private”, so that no one except the Aspose Team will be able to see it’s contents.