I attached the excel file to make it easier, but here is a summary of what it contains, in case you don’t want to open the file.
| A | B | |
| 1 | col1 | col2 |
| 2 | 12/04/05 | 12/04/05 |
A2 has a custom format = "mm/dd/yy"
B2 has the formula: “=$A2” and also has a custom format = "mm/dd/yy"
The Problem:
When I Export to a DataTable using ExportDataTableAsString()
A2 is in the DataTable as “12/04/05” which is correct, however
B2 is in the DataTable as “MM/dd/yy” which is wrong.
If I change the format of column B to “Category:General” in the Excel file, and then Export to a DataTable, I at least get an integer representing the date.
If I change the format of column B to “Category: Date” and select “Type:*3/14/2001” and then Export to a DataTable, I get “12/04/2005”: the expected result.
However, If I change the format of column B to “Category: Date” and select “Type:03/14/01” and then Export to a DataTable, I get “MM/dd/yy” again.
I am using Aspose.Excel version 3.0.4.0
Here’s something else: Using an older version of Aspose.Excel (version 2.6), I get an integer representing the date, rather than the literal string: “MM/dd/yy”, so the older version at least worked somewhat better.