Spreadsheet Cell Values for DateTime returning Number values

[Ed James]

I have a CSV file with Dates ("MM/DD/YYYY"). I wish to parse these values within the active worksheet as string values, but they're converted to number format (i.e. "22532"). I'm unable to get these values in the original string format.

Is it possible to ensure all cell values are in String format, representing exactly how they are in a CSV file?

[Svetoslav]

Hi Ed,

Thank you for contacting us!

Unfortunately, my attempts to replicate the issue you describe were not successful. Could you please try to import your file in our click-once examples and see if you reproduce the issue there?  Also, please verify Q3 2013 (2013.3 1016) is the version of RadControls for WPF you are using.

In order to analyze the roots of the problem we would need to replicate it on our side. For this reason if the issue persists please send us your project. Once we are able to reproduce the behavior locally, we will do our best to assist you further.

We are looking forward to hearing from you again!

Regards,
Svetoslav
Telerik

[Jiri]

Hi,

I have exactly the same problem as reported by Ed James, except in Silverlight.

var cell = ws.Cells[row, c]; // ws is Worksheet
var val = cell.GetValue();
var v = val.Value.RawValue; // v is now some strange number instead of for instance: 1.1.2014 - what is a value from excel's cell

Could you please do something with that?

Thank you,
Jiri

[Nikolay Demirev]

Hello Jiri,

The date values are stored in the Worksheet as number values with specific number format. This is the reason why when you get the value of the cell the result is number. If you want to get the formatted result, you could use the following code snippet:

CellSelection cellSelection = this.workbook.ActiveWorksheet.Cells[0, 0];
cellSelection.SetValue(22532);
cellSelection.SetFormat(new CellValueFormat("m/d/yyyy"));

ICellValue value = cellSelection.GetValue().Value;
CellValueFormat format = cellSelection.GetFormat().Value;
CellValueFormatResult formatResult = format.GetFormatResult(value);
string result = formatResult.InfosText;

Regards,
Nikolay Demirev
Telerik

 

 

[Jiri]

Hi Nikolay,

Thank you, great, this works nicely!

Cheers,
Jiri

[Ben]

Hi, im using XlsxFormatProvider and CsvFormatProvider to import file into a Workbook. My date format are good when i use an XLSX file (they keep the cell format). But when i import a CSV file, i would like to keep the original format.For example, if i have a CSV:

2017-01-31,info1

01/31/2017,info2

31/01/2017,info3

I would like that telerik import all the date without changing the format. Is it possible ? Because now, it all change them to 2017-01-31.

[Deyan]

Hello Benoit,

Saving spreadsheet data to CSV file ensures preserving only the cell values and all information from other cell formatting features is lost. If you require preserving the date format you should either use XLSX file format or implement custom logic that sets CellValueFormat to specific cells right after the CSV import as mentioned in previous answer by Nikolay Demirev.

Regards,
Deyan
Telerik by Progress

[ManishB]

Hello,

I have the same issue for Date cell in Telerik Web Workbook.

can you please suggest how can I do that for Web workbook cell.

[Veselin Tsvetanov]

Hello ManishB,

May I ask you to provide a bit more details about the scenario in which you use the Telerik.Web.Spreadsheet.dll? Do you have it in ASP.NET Web Forms application with RadSpreadsheet, or you use it in an ASP.NET MVC application? Also, please explain a bit more in detail the actual use case in which you observe this issue.

Regards,
Veselin Tsvetanov
Telerik by Progress

[ManishB]

Hello Veselin,

I am using Telerik.Web.Spreadsheet.dll with ASP.NET MVC application. I used it to import CSV or TXT file. I am transferring data from Imported file to DataTable, so while retrieving data through worksheet I got (22532) such value from a cell for Date Field and Now I want to convert it to DateFormat and set it to the DataTable.

[Veselin Tsvetanov]

Hello,

Thank you for the additional information provided.

In order to retrieve the correct date, you will need to convert the OLE Date format to DateTime object:

var cell = workbook.Sheets[0].Rows[0].Cells[0];
var value = Double.Parse(cell.Value.ToString());
var date = DateTime.FromOADate(value);

Regards,
Veselin Tsvetanov
Telerik by Progress

[Christopher]

It would be very nice if there was an option on CsvFormatProvider to read everything in as a string instead of the current behavior. Sure I can use what's suggested in this thread to convert the dates from numbers into the format I want, but that assumes I know which columns are dates. In my use case, I won't know that so in order to get my dates as strings I ended up not using Telerik.Windows.Documents.Spreadsheet and used  Microsoft.VisualBasic.FileIO.TextFieldParser instead.

[Nikolay Demirev]

Hi Christopher,

I have just tested importing the attached CSV file containing dates in the format "MM/DD/YYYY" while my OS has the same setting for date format, and the RadSpreadsheet shows the values as dates which are also formatted as dated. Could you try it on your end and share the result?