I am reading the following value from a cell in an xls file “09/02/2011 00:00:00”.
When I try to get the DateTimeValue of this cell the following error is thrown. “Not a DateTime value in CellG2”
This is clearly a date time value as the .NET IsDate and CType(, DateTime) both confirm.
Hi Christopher,
We have looked into the issue. If you use following lines of code, these will give you the desired result. Furthermore, please send us the sample project and excel file so that we can investigate it more.
Code snippet:
string sCellVal = objWS.Cells["G2"].Value.ToString();
DateTime objDT = objWS.Cells["G2"].DateTimeValue;
Thanks,
This code does not give the desired result.
Here is the VB code we are using with the attached spreadsheet.
Dim dt As DateTime = worksheet.Cells(1, 6).DateTimeValue
We have also found another problem with the attached CSV file. The source cell contains the string "01". But both of the following calls return just 1 as an integer, not the original string which is required. Make sure you open the CSV as a text file if you want to see the original string value "01".
Dim s As String = worksheet.Cells(1, 1).Value.ToString()
Dim s As String = worksheet.Cells(1, 1).StringValue
FYI I already tried setting the workbook.Settings.ConvertNumericData = False to fix the leading “0” from being truncated. But now that you have depricated the Open method of the Workbook object it appears that there is no way for this to be applied before the file is loaded.
Hi Christopher,
We are able to reporduce the issues which you have mentioned. We have logged the issues into our Issue Tracking System with ID CELLSNET-19740. We will update you as soon as possible.
In the mean while you can use following lines of code as a work around. It will give you the same result as desired.
DateTime dt = Convert.ToDateTime( objWS.Cells[1, 6].Value.ToString());
DateTime dt1 = Convert.ToDateTime(objWS.Cells[1, 6].StringValue);
Thanks,
Hi Christopher,
After further investigation, we found following details:
1. For the DateTime issue:
The value of Cell G2 is a string value not a date time value in the template file. If you paste cell "G2" value to a new MS Excel file. MS Excel will convert the string value to a valid date time value. So, using Aspose.Cells you can find the Value, StringValue and convert it to date time.
2. For the issue of “01”:
We only support to convert a string value to a numberic value. We do not support to set the number format back. Aspose.Cells works in the same way as MS Excel do. You will see “1” in MS Excel, if you open the CSV file in MS Excel. If you save the CSV file, MS Excel will truncate the '0'.
Thanks,
Two points.
Your workaround for the first issue to open the file in Excel and paste the cell to convert the date is ridiculous. We have your control to automate the process of reading spreadsheet data that is uploaded from 3rd parties. If we have to manually open each file to copy and paste defeats the purpose of an automated solution. According to your marketing, your software is supposed to replace the need to use Excel.
Your point about the the truncation of leading zeros is also not completely correct. While it is true that excel will automatically convert the column if it is opened directly in Excel, it also allows you the option of specifying the format of a column before importing it. The fact that in a previous version you had a setting for preventing the automatic conversion to numbers (which no longer works because you depricated the Worksheet.Open method, demonstrates that you at one time understood this is a problem.
Unfortunately this is a serious bug that will prevent us or anyone else needing to read in CSV files from using your product.
Hi Christopher,
We are working on the issues mentioned by you. We will soon release an updated version of Aspose.Cells.dll which will contains the support of ConvertNumericData in the LoadOptions so that you can prevent the leading "0" from being truncated.
Thank you for your patience.
Hi,
Please try the attached version.
We have supported LoadOptions. ConvertNumericData attribute.
Thank you.
The issues you have found earlier (filed as 19740) have been fixed in this update.