Spreadsheet Cell Values for DateTime returning Number values

11 posts, 0 answers
  1. Ed James
    Ed James avatar
    9 posts
    Member since:
    Sep 2008

    Posted 02 Dec 2013 Link to this post

    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?
  2. Svetoslav
    Admin
    Svetoslav avatar
    55 posts

    Posted 04 Dec 2013 Link to this post

    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
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WPF.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  3. Jiri
    Jiri avatar
    43 posts
    Member since:
    Oct 2013

    Posted 26 Sep 2014 in reply to Svetoslav Link to this post

    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
  4. Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    127 posts

    Posted 29 Sep 2014 Link to this post

    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
     
    Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
     
  5. Jiri
    Jiri avatar
    43 posts
    Member since:
    Oct 2013

    Posted 29 Sep 2014 in reply to Nikolay Demirev Link to this post

    Hi Nikolay,

    Thank you, great, this works nicely!

    Cheers,
    Jiri

  6. Benoit
    Benoit avatar
    1 posts
    Member since:
    Feb 2017

    Posted 08 Feb in reply to Nikolay Demirev Link to this post

    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.

  7. Deyan
    Admin
    Deyan avatar
    155 posts

    Posted 13 Feb Link to this post

    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
    Want to extend the target reach of your WPF applications, leveraging iOS, Android, and UWP? Try UI for Xamarin, a suite of polished and feature-rich components for the Xamarin framework, which allow you to write beautiful native mobile apps using a single shared C# codebase.
  8. ManishB
    ManishB avatar
    2 posts
    Member since:
    Jul 2015

    Posted 28 Mar Link to this post

    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.

  9. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 31 Mar Link to this post

    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
    Want to extend the target reach of your WPF applications, leveraging iOS, Android, and UWP? Try UI for Xamarin, a suite of polished and feature-rich components for the Xamarin framework, which allow you to write beautiful native mobile apps using a single shared C# codebase.
  10. ManishB
    ManishB avatar
    2 posts
    Member since:
    Jul 2015

    Posted 31 Mar in reply to Veselin Tsvetanov Link to this post

    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.

  11. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 05 Apr Link to this post

    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
    Want to extend the target reach of your WPF applications, leveraging iOS, Android, and UWP? Try UI for Xamarin, a suite of polished and feature-rich components for the Xamarin framework, which allow you to write beautiful native mobile apps using a single shared C# codebase.
Back to Top