I’m using Aspose Cells v4.1. When using ImportDataTable(), I want to convert numeric Strings to Numbers. To do this, I set the “convertStringToNumber” parameter to true. I thought it was working, until I discovered that it will turn a date (stored in a String column in the DataTable) to a number.
For instance, the string “01/01/2001” becomes the number 36892 with “convertStringToNumber” set to true.
Is this a bug, or functioned as designed? If it’s a bug, is it fixed in newer versions?
Maybe there’s an alternative way to do want I want? I’d like to find a way to convert an entire column from Sttring to Number, based on the contents of the column (column headers excluded). If the culumn isn’t numeric, like “01/01/2001”, it shouldn’t be touched.
Hi,
Thank you for considering Aspose.
Well, you can try the following ImportDataTable overloaded method as per your rewuirement by using the latest version of Aspose.Cells attached. In this overloaded method you can specify the dateFormatString for your DateTime Objects.
public int ImportDataTable( DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int rowNumber, int columnNumber, bool insertRows, string dateFormatString, bool convertStringToNumber );
or Alternately you can also try to Format a Range of Cells in your column to change there format (without header) after importing Data Table. Please see the following sample code to apply date format to 2nd Columns in the Excel Sheet,
Sample Code:
//Create a range of cells after Importing the data.
Range range = WS.Cells.CreateRange(0, 1, WS.Cells.MaxRow, 1);
//Name the range.
range.Name = "MyRange";
//Declare a style object.
Style stl;
//Create the style object with respect to the style of the first row.
stl = WS.Cells.Rows[0].Style;
//Specify Date Number Format d-mmm-yy
stl.Number = 15;
//Apply the style to the range.
range.Style = stl;
//Save the excel file.
workbook.Save("c:\\rangestyles.xls");
Please see the following documentation link for setting different Display Formats,
Thank You & Best Regards,