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.)
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); }
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:
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.
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".
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.
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.
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.
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.