It appears a breaking change was introduced since 23.5, most likely by this change in 23.9:
CELLSNET-53962 Support data type detection when exporting data to DataTable
The problem with the change is that the column data types in the result DataTable are different than what they were prior to the change. We’ve been using Aspose for probably over 15 years, and all existing code that expects a certain column type is now seeing a different type. I have looked in vain for an option to revert the behavior back.
Attached is a zip file with test code. When the code is run with version 23.5 there are no errors. When run with version 24.1 there are errors.
There should be an option to keep this long-standing behavior as it was.
ExportDataTableTests.zip (14.1 KB)
@jcapson
Thanks for your details. We will evaluate your issue further and get back to you soon.
@jcapson
Sorry for the inconvenience caused by the changed logic. In previous versions, when the first data was empty, the column data type was set to string. However, some customers think that this was not reasonable because it is common for columns with a determined type to have some empty data. We believe this requirement is reasonable, so we have accordingly modified this part of the logic to ignore empty cells when determining the column data type.
To get the same behavior with old versions, we are afraid currently there is no simple way. Preprocessing the range that needs to be exported is a possible workaround. You may traverse cells in the range to detect data type for every column by yourself and define the DataTable before exporting, or you may traverse cells in the range to replace blank cells with empty string value(“”).
If you do need one simple api to get the same result with old versions, we may consider to provide new API(s) later, but currently we are afraid you have to manipulate it by youself, such as trying the workaround we mentioned.
Thank you for the reply.
It is both unfortunate and surprising that the decision was made to alter such long-standing non-bug behavior without a flag to either enable or reverse the new behavior. While I do agree that the new behavior is better, I disagree that your clients should be forced to accommodate the new behavior. We were upgrading from 23.5 to 24.1 to fix a critical bug that was affecting a couple clients, and now before we can deploy the fix we have to figure out how to prevent bugs from an unnecessary breaking change that should have been provided a flag to govern the behavior. For example, our clients put together import and input spreadsheets in 100s of different formats and now there is the likelihood that when read into data tables the data types will be different from expected. Our common code for converting the Excel sheet to a data table doesn’t know what types the myriad different calling code expects, and now the calling code is likely to get unexpected values.
I urgently reiterate my call for a flag to govern the new behavior.
@jcapson,
Thanks for your thoughts and providing further details.
We will check if we could consider a flag or relevant API to get your older results with newer versions. We will get back to you soon.
1 Like
@jcapson
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55029
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@jcapson,
We are pleased to inform you that your issue has been resolved. The enhancement will be included in an upcoming release (Aspose.Cells v24.2) that we plan to release in the first half of February 2024. You will be notified when the next version is released. We have also added AllowDBNull Boolean attribute under ExportTableOptions to support your needs.
1 Like
The issues you have found earlier (filed as CELLSNET-55029) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
1 Like
Thank you! I will download the latest and test ASAP.
@jcapson,
Sure, please take your time and try the latest version. Hopefully new version with new API(s) will work for your needs.
@jcapson
Please set options as the following :
var opts = new ExportTableOptions
{
CheckMixedValueType = true,
ExportColumnName = true,
AllowDBNull = false
};
Hello,
We finally decided to upgrade Aspose.Cells from 23.5 to 24.5. We didn’t upgrade sooner because we found a workaround for the issue that prompted us to try to upgrade that led to us finding the breaking change that led to CELLSNET-55029.
Unfortunately, when I tested the AllowDBNull flag I discovered that it does not truly provide full backwards compatibility. In a nutshell, the pre-CELLSNET-53962 behavior would look at the Excel data type when creating the datatable column type, even if there was no data in the cell. The fix implemented for CELLSNET-55029 was supposed to provide backwards compatibility when AllowDBNull is set to true, but does not look at the Excel data type when deciding the datatable column type.
I will attach a zip with a simple Excel file and tests to run against the different versions. The versions I tested against were 23.5 and 24.5.
AsposeBug.zip (8.4 KB)
@jcapson
By testing with sample files and code on the latest version v25.3, we can reproduce the issue. Data type error when exporting data using Cells.ExportDataTable method.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58119
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@jcapson
As a temporary solution, you can create a DataTable object and set the type of columns. Then use Cells.ExportDataTable method to export the data. Please refer to the following example code.
Workbook excel = new Workbook(filePath + "ExportDataTableTestData.xlsx");
Worksheet sheet = excel.Worksheets[0];
int maxRow = sheet.Cells.MaxDataRow + 1;
int maxCol = sheet.Cells.MaxDataColumn + 1;
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(double));
dt.Columns.Add("value date", typeof(DateTime));
var opts = new ExportTableOptions
{
CheckMixedValueType = true,
ExportColumnName = true,
AllowDBNull = true,
DataTable = dt
};
DataTable table = sheet.Cells.ExportDataTable(0, 0, maxRow, maxCol, opts);
Assert.AreEqual(2, table.Columns.Count);
Assert.AreEqual(5, table.Rows.Count);
Assert.IsTrue(table.Columns.Contains("id"));
Assert.AreEqual("System.Double", table.Columns["id"].DataType.FullName);
Assert.IsTrue(table.Columns.Contains("value date"));
Assert.AreEqual("System.DateTime", table.Columns["value date"].DataType.FullName);
Hope helps a bit.
@jcapson,
We are pleased to inform you that your issue (Ticket ID: “CELLSNET-55029”) has been resolved. The fix will be included in an upcoming release (Aspose.Cells v25.4) that we plan to release in the first half of April 2025. You will be notified when the next version is released. In the meantime, you may try the temporary solution provided by @John.He.
1 Like
The issues you have found earlier (filed as CELLSNET-58119) have been fixed in this update. This message was posted using Bugs notification tool by leoluo