C# Gembox Spreadsheet - Read cell value which references to another excel file

Question

I am working with GemBox Spreadsheet now to read Excel file using C#. In the Excel file, there is cell contains Date value which refers to another excel file.

In the C#, at first, I got zero value. Based on the GemBox Retrieving Calculated Values From a Spreadsheet or Flexcel post, I replaced the Load method into XlsxOptions.PreserveMakeCopy. After replacing, I got some random value, like 43257, instead of Date value. I am not sure what value it is.

Here is the code snippet:

public void Export()
{

    SpreadsheetInfo.SetLicense(FileExport.GemBoxLicenseKey);
    ExcelFile ef = new ExcelFile();
    FileExport file = new FileExport();
    ef.LoadXlsx(locationPath + "\\XX.xlsx", GemBox.Spreadsheet.XlsxOptions.PreserveMakeCopy);

    ExcelWorksheet ws = ef.Worksheets[0];

    try
    {
        int trackrowcount = 0;
        int columncounter = 1;
        DateTime date = DateTime.MinValue;

        foreach (ExcelRow row in ws.Rows)
        {
            trackrowcount += 1;

            if (trackrowcount < 4)
                continue;

            columncounter = 1;

            foreach (ExcelCell cell in row.AllocatedCells)
            {
                if (cell.Value != null)
                {
                    if (trackrowcount == 4 && columncounter == 2)
                    {
                        var cellFormula = cell.Formula;
                        var cellValue = cell.Value;

                        //Here is where I am trying to get the date value
                        date = Convert.ToDateTime(cellValue);
                    }
                }
                columncounter += 1;
            }
        }
        // and so on
    }
    catch (Exception ex)
    {
    }
}

Is there any setting that I missed?

Answer 1

You are getting date in OADate format in datatype double. You have to convert it back in DateTime Format.

double dateValue = 43257;
DateTime date = DateTime.FromOADate(dateValue);