Value of the Double Type Attribute is getting converted to DateandTime Format

Posted by: krishna.vajja on 15 October 2020, 6:21 am EST

  • Posted 15 October 2020, 6:21 am EST

    We have values like length and Temperature which are of datatype Double.
    When we try to open the spreadsheet to load those values into our application few of them are getting converted to DateandTime Format.
    We are using the below method to open spread and read the data.
    OpenExcel(FileName, FarPoint.Excel.ExcelOpenFlags.DocumentCaching | FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad);

    Please provide us assistance in resolving this error.

  • Marked as Answer

    Replied 16 October 2020, 2:28 am EST


    Can you please share the Excel file loading which you are facing the problem? Trying to reproduce the same using the OpenExcel method with same arguments, we do not face the problem.

    Also, please ensure that you are using the latest Spread version.

  • Replied 16 November 2020, 11:56 pm EST

    Hi Ruchir,
    Providing the both excels.
    Excel 1 (BaseExcel) - Here all the data is proper.
    Excel 2 (ExcelafterReload) - But on re-loading data of some random attributes changes to Date-time

  • Replied 17 November 2020, 11:10 pm EST

    Thank you for the sample files. Though, I could see the values in length column turning to date time values, in the sample excel files you shared, I could not replicate the same in Spread as it opens the BaseExcel.xlsx file fine, please see the attached screen-shot.

    I tested this with both, Spread v13 and latest Spread v14 and both show the correct values. So, if you are using any older version, you are suggested to test the issue using the latest version.
    Download Spread v14:

  • Replied 18 November 2020, 5:39 am EST

    Hi Ruchir,
    Thanks for the update.
    For opening the Excel we are using the following method:
    OpenExcel(FileName, FarPoint.Excel.ExcelOpenFlags.DocumentCaching | FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad);
    Want to confirm if you have used the same method with same parameters.

  • Replied 18 November 2020, 4:14 pm EST

    Hi Krishna,

    Yes, I used the same code:
    fpSpread1.OpenExcel("BaseExcel.xlsx", FarPoint.Excel.ExcelOpenFlags.DocumentCaching | FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad);

  • Replied 18 November 2020, 8:29 pm EST

    Hello Ruchir,
    This issue does not appear to us every time.
    We can see this at some random time.
    When I tried to change the parameters of OpenExcel method, I couldn't see this particular issue where as we found problems with merged cell, so we have to revert back that parameter.
    Method with New parameter: OpenExcel(FileName, FarPoint.Excel.ExcelOpenFlags.DocumentCaching | FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad | FarPoint.Excel.ExcelOpenFlags.DateandFormulasOnly);

    When this problem got solved with some other parameter, we are suspicious that this behavior is because of OpenExcel method.
    Can you please try to reopen the excel multiple times to cross check the issue.

  • Replied 21 November 2020, 11:58 pm EST

    Hello Krishna,

    I tested the behavior 15+ times and in no iteration I could see the problem. So, can you please answer the following questions, which would help us narrow down the source of the problem:

    1. Can you try to replicate the behavior in a new application? Just create a new WinForms application, drop FpSpread from Toolbox, add BaseExcel.xlsx file to project and invoke the OpenExcel method with the discussed parameters.
    This would narrow down whether the behavior is specific to application or machine.
    2. Can you please confirm whether the issue is occurring with other excel files too? Or is it specific to the BaseExcel.xlsx file?
    This would narrow down whether the problem is related to the BaseExcel.xlsx file or not.
    3. Share the Spread version you are using.

  • Replied 10 December 2020, 7:26 pm EST

    We will be sharing a sample application to you shortly.
    and we are using v12 of spread

  • Replied 11 May 2021, 10:58 pm EST

    Hi Ruchir,
    We have encountered the same issue in some other excel files also. When opened this in spread designer we were able to reproduce this there to. I have attached the video showing the same. The cell when double clicked is changing the value to date format, but on our application while opening itself this is happening. Could you please look into this. I have attached the excel file and the video along with this.

    The excel file have customer data along with it It would be great if you could trash it afterwards.

  • Replied 12 May 2021, 4:33 pm EST

    Hi Krishna,

    In your Excel file, the cells F7, I7, L7, all have Date formats.
    If you use the following code, you can see the cell format of the cell you click:
    private void FpSpread1_EnterCell(object sender, FarPoint.Win.Spread.EnterCellEventArgs e)
    var worksheet = fpSpread1.AsWorkbook().ActiveSheet;
    NumberFormat numberFormat = (worksheet as Worksheet).GetNumberFormat(e.Row, e.Column);
    Console.WriteLine($"Cell[{e.Row}, {e.Column}]: FormatId = {numberFormat.BuiltInID}, Format = {numberFormat.FormatCode}");

    The reason the values are formatted differently in Excel is because the Format Codes in your Excel file have been changed. Please check following image for the difference:

    Since Excel does not allow users to change format codes for built-in number formats, can please specify how you have created this file?

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels