"Not a DateTime value" exception on call to Worksheet.AutoFitColumn


#1

I am hitting a difficult and critical bug that shows itself as Aspose.Cells throwing the following Exception on a call to Worksheet.AutoFitColumn. (This problem exists in 3.7.2.2 and 3.6.2.1.)

Not a DateTime value.
Aspose.Cells.Cell.get_DateTimeValue() +277
Aspose.Cells.ढ.म(Cell ו, Boolean प) +7881
Aspose.Cells.౾.ಂ(Worksheet ֭, Byte ٗ, Int32 ܠ, Int32 ౖ) +659
Aspose.Cells.Worksheet.AutoFitColumn(Int32 columnIndex) +137

I hope it's possible for you to look at this problem without a code sample, because it would be time consuming for me to try to create a test case, mostly because I am reading from a SQL Server database.

It appears to have something to do with using Cells.ImportFromDataReader when a (SQL Server) DateTime cell is NULL and the cell was styled to be a DateTime (22). There may also be a dependency on the data types of other columns in the "Import", since importing the offending column alone does not produce the exception.

In pseudo-code, here is what I am doing:

IDbCommand command = db.CreateCommand(sqlCommandText);
using (IDataReader reader = db.ExecuteReader(command))
{
cells.ImportArrayList(columnHeadings, 0, 0, false);
cells.ImportFromDataReader((SqlDataReader)reader, 1, 0 , true);
}
for (int i = 0; i < numberOfColumns; i++)
{
create a Validation and add a cell area to the Validation;
create a Style and Range, then apply the Style to the Range;
}
for (int i = 0; i < numberOfColumns; i++)
{
worksheet.AutoFitColumn(i);
}

Thanks for your help.


#2

I cannot reproduce this problem in my machine. Could you post your output file without calling autofitcolumn?


#3

Please try this attached fix. I added some debug information when throwing this exception.


#4

Here's an update: I am importing 1 column of 1715 DateTime values, some of which are NULL. With the DLL that you provided (3.7.2.7), and with the calls to AutoFitColumn active, I see this:

[Exception: Not a DateTime value.0Aspose.Cells.?|Object]
Aspose.Cells.Cell.get_DateTimeValue() +443
Aspose.Cells.?.?(Cell ?, Boolean ?) +7960
Aspose.Cells.?.?(Worksheet ?, Byte ?, Int32 ?, Int32 ?) +663
Aspose.Cells.Worksheet.AutoFitColumn(Int32 columnIndex) +137

When I remove the calls to AutoFitColumn, I get the same exception, but on a call to Excel.Save with a file type of Excel2003.:

[Exception: Not a DateTime value.2Aspose.Cells.?|Object]
Aspose.Cells.Cell.get_DoubleValue() +526
Aspose.Cells.Cells.?(? ?, FileFormatType ?) +8241
Aspose.Cells.Worksheet.?(? ?) +4301
Aspose.Cells.Worksheets.?(? ?) +467
Aspose.Cells.Worksheets.?(FileFormatType ?) +263
Aspose.Cells.Worksheets.?(Stream ?, FileFormatType ?) +63
Aspose.Cells.Excel.Save(Stream stream, FileFormatType fileFormatType) +338


Not sure how to proceed. Thanks for your prompt replies and help.


#5

What's NULL in your sql datareader? Is it "null" in C# or "Nothing" in VB? Or is it DBNull.Value?

Please try this fix. It will tell your which cell has this problem. Please debug into your program to see what's it. Please post a screenshot here. That will help me to figure out the problem. Thank you.


#6

Progress! (Aside: It would be extremely helpful if a future release of the Aspose DLL provided cell name, cell value, etc., details in exceptions thrown by the various "Import" methods. What you've provided me today was very helpful.)

I have narrowed-down to the problem to this:

Our SQL Server 2005 database has a field whose DateTime value is the SQL Server min datetime (1753-01-01 00:00:00.000). This is the equivalent of "new DateTime(1753, 1, 1)" in .NET. On import, the exception is thrown at this cell.

The Visual Studio debugger says that cell B344 IsDateTime and the exception message buried in the cells structure says "Not a DateTime value. 0 in CellB344".

Oddly, the exception that is thrown onto my screen says "Not a DateTime value. 2 in CellB344".

Screen snapshots are attached. Thanks.


#7

Laurence,
We are seeing a similar error from an Oracle data feed during the .Save call. Error message is as follows:

msg: Not a DateTime value. 2 in CellI2397 trace: at Aspose.Cells.Cell.get_DoubleValue()
at Aspose.Cells.Cells.֤(ۉ Ԏ, FileFormatType ֣)
at Aspose.Cells.Worksheet.֤(ۉ Ԏ)
at Aspose.Cells.Worksheets.֠(ۉ Ԏ)
at Aspose.Cells.Worksheets.֥(FileFormatType ֣)
at Aspose.Cells.Worksheets.֤(Stream Ԏ, FileFormatType ֣)
at Aspose.Cells.Excel.Save(Stream stream, FileFormatType fileFormatType)
at Aspose.Cells.Excel.Save(String fileName, FileFormatType fileFormatType)
at Aspose.Cells.Excel.Save(String fileName)
at AsposeCellsPerfTesting.Form1.CreateExcel() in C:\Projects\RFN20\VSStudio2005\AsposeCellsPerfTesting\AsposeCellsPerfTesting\Form1.vb:line 119

Cell I2397 appears to be

4/19/1881

I realize that a very old date but when I export the same data set to Toad it just left aligns that particular field data and treats it as a general data type. Please advise on what the solution may be. We've just purchased a license and are planning to roll this to our Quality dept today and Production environment the end of next week.

Thanks!

Chris


#8

Thank you for the information.

From your information I figure it out. This problem is caused by that your date time value is before 1900/01/01. In MS Excel, only date time value after 1900/01/01 is saved as numeric value, otherwise it's saved as string.

I fixed this bug in this attached fix. Please try it.


#9

This works! Thanks for the updated DLL.


#10

Laurence,
Your fix solved the problem. Thanks! One final question relating to this issue. The date is correctly formatted as a string. However, when the user edits the cell and the presses enter, the date which displays 04/06/81 immediately becomes 04/04/1981 rather than 04/04/1881. Being new to the Cells component I was wondering if there is a way to force the date to have the long year when it is formatted as a string? This is not a critical thing for us since the odds are very slim that our real data would be that old.

Thanks,
Chris


#11

When meeting date before 1900/01/01, Aspose.Cells will convert it to string according to the number format. To force the date as long year, please change the number format for that cell.