How to convert all date-formatted cells in IRange to DateTime using Spreadsheet Gear C#?

Question

I'm trying to read the used range of an Excel file with Spreadsheet Gear and I've currently got it set up to do this:

var workbook = Factory.GetWorkbookSet().Workbooks.OpenFromStream(fileStreamInput);
var sheet = workbook.Worksheets[0];
var values = sheet.Cells[sheet.UsedRange.Address].Value;

The Excel file will be uploaded by people using different column formatting so this code needs to type-independent... except for dates. This code pulls in the double-representation of dates that Excel stores them as and I don't want that. I'm aware that the following line will convert that double into a date:

workbook.NumberToDateTime(dateAsADouble);

However, that assumes that I know which cell is date-formatted beforehand and I never will. I'd like to be able to convert all of the cells stored in values that are date-doubles using this method, ideally without looping through every value individually but I will accept such an answer if there's nothing else. I thought I might be able to use Linq but it seems that SpreadsheetGear's IRange class doesn't support Linq.

Is there a way to do this? Perhaps a method that tests whether a cell is a date-double? I haven't found anything like this in SpreadsheetGear's API.

Answer 1

You will not be able to determine from IRange.Value alone whether a given double value is intended to be used for a serial date or just as a simple number value. A "date" or "datetime" or "time" cell is simply a cell with a numeric value that has a particular NumberFormat applied to it that formats this cell's value as a date.

I suppose you could look for cells that have numeric values in a particular range that might be a good candidate for a "valid" date (i.e. November 2, 2017 has a serial numeric value of 43041), but this is by no means a very reliable or definitive approach.

To determine when you need to use IWorkbook.NumberToDateTime(...) on a given cell's numeric value, you will also have to consider the cell's NumberFormat. You can do this for most cases by checking a cell's IRange.NumberFormatType property, which will return NumberFormatType.Date / DateTime / Time if the cell has a NumberFormat that matches a "date/time" type such as "m/d/yyyy", "m/d/yyyy h:mm", "h:mm:ss", etc. This approach will require looping through the range. Example:

// Get used range.
IRange usedRange = sheet.UsedRange;

// Setup 2D object array to copy cell values into.
object[,] values = new object[usedRange.RowCount, usedRange.ColumnCount];

// Loop through range in row groups.
for (int row = 0; row < usedRange.RowCount; row++)
{
    // Loop through each column in a single row.
    for (int col = 0; col < usedRange.ColumnCount; col++)
    {
        // Get current cell.
        IRange cell = usedRange[row, col];
        object cellVal;

        // Special Case: Number formatted as dates/dateTimes/times.
        if (cell.ValueType == SpreadsheetGear.ValueType.Number)
        {
            // Cells formatted as a Date, DateTime or Time
            if (cell.NumberFormatType == NumberFormatType.Date || cell.NumberFormatType == NumberFormatType.DateTime || cell.NumberFormatType == NumberFormatType.Time)
            {
                DateTime dateTime = workbook.NumberToDateTime((double)cell.Value);
                Console.WriteLine($"Found a 'date' or 'datetime' or 'time' cell - {cell.Address} - {dateTime.ToString()}");
                cellVal = dateTime;
            }
            // For any other numeric value, copy as-is.
            else
            {
                cellVal = cell.Value;
            }
        }
        // For all other ValueTypes (Text / Logical / Empty / Error), copy as-is.
        else
            cellVal = cell.Value;

        // Set object[,] value.
        values[row, col] = cellVal;
    }
}