Date value formatting using Worksheet.Cells and CSV

Aspose.Cells Product Family
1

I am using Aspose.Cells to read a CSV file and populate a DataTable.

wkbook = new Workbook();
wkbook.Open(filePath, FileFormatType.CSV);
dataTable = cells.ExportDataTable(cells.MinRow, cells.MinColumn, (cells.MaxDataRow + 1), (cells.MaxDataColumn + 1), true);

Dates are not formatted by default as they are in EXCEL. I must loop the collection and format them. If I dont format them the dates appear as a float (eg. 51213.21444858743). This obviously causes processing problems.

These are dates that if imported into Excel, Excel formats them fine and Aspose.Cells can read the XLS and I can then process. Its only when I ask Aspose.Cells to read them straight from a CSV that I encounter a problem.

Here is a sample of the CSV that I am loading.

466_7065535,PreMapping,DOCDATE,8/16/2006 0:19:45
466_7075146,PreMapping,DOCDATE,8/22/2005 2:46:09
466_7075144,PreMapping,FIRST,Paqo
466_7094226,PreMapping,LAST,Gomez
466_2414979,PreMapping,DOCDATE,1/28/2008 13:12:35

Is there any way around this? Or is this by design?
2

Hi,

Thank you for considering Aspose.

Well, you can set the display format of the column with dates before exporting the data to the datatable. Please see the following sample code in this regard:

Workbook workbook = new Workbook();

workbook.Open("C:\\DateValue.csv",FileFormatType.CSV);

Cells cells = workbook.Worksheets[0].Cells;

Aspose.Cells.Style stl2 = workbook.Styles[workbook.Styles.Add()];

stl2.Number = 14;

//Set the style flag struct.

StyleFlag flag = new StyleFlag();

flag.NumberFormat = true;

//Get the first column in the first worksheet.

Column col = workbook.Worksheets[0].Cells.Columns[3];

//Apply the style to it.

col.ApplyStyle(stl2, flag);

DataTable dataTable = cells.ExportDataTable(cells.MinRow, cells.MinColumn, (cells.MaxDataRow + 1), (cells.MaxDataColumn + 1));

Also, please try the latest version of Aspose.Cells as we have supported some new date formats when using ExportData methods.

Thank You & Best Regards,

3

This works well, thank you.


It does not solve all the problem however.

If I have a CSV (or a spreadsheet for that matter), that has a date as the first value, but a string value below it (as in my example), i get this error when I run ExportDataTable:

The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.Couldn’t store in Column4 Column. Expected type is DateTime.

This appears to be a C# error not an Aspose error, but something is telling it that it needs a “DateTime” column so that only DateTime values can be put in it.

Can this be remedied?
4

Hi,

Thank you for considering Aspose.

Well, you may try ExportDataTableAsString instead of ExportDataTable. I checked and it works fine. Please see the following sample code in this regard:

Sample Code:

Workbook workbook = new Workbook();

workbook.Open("C:\\DateValue.csv",FileFormatType.CSV);

Cells cells = workbook.Worksheets[0].Cells;

Aspose.Cells.Style stl2 = workbook.Styles[workbook.Styles.Add()];

stl2.Number = 14;

//Set the style flag struct.

StyleFlag flag = new StyleFlag();

flag.NumberFormat = true;

//Get the first column in the first worksheet.

Column col = workbook.Worksheets[0].Cells.Columns[3];

//Apply the style to it.

col.ApplyStyle(stl2, flag);

DataTable dataTable = cells. ExportDataTableAsString(cells.MinRow, cells.MinColumn, (cells.MaxDataRow + 1), (cells.MaxDataColumn + 1));

Thank You & Best Regards,

5

Excellent. Thank you very much.