Set Excel cell to TimeSpan value

Question

I need to create an Excel file with a column containing time values (TimeSpan objects).

I'm using GemBox.Spreadsheet and I can create a column with date values (DateTime objects) by using either ExcelCell.Value property or ExcelCell.SetValue method:

var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");

worksheet.Cells["A1"].Value = "Start Dates";

// Set cell value to DateTime with Value property.
worksheet.Cells["A2"].Value = DateTime.Today;

// Set cell value to DateTime with SetValue method.
worksheet.Cells["A3"].SetValue(DateTime.Today);

workbook.Save("output.xlsx");

But neither of those work for TimeSpan.
When using the properly, I get NotSupportedException, and when using the method, I get a compiler error.

What's confusing to me is that I can load an existing Excel file with cells containing time values and read them.
So, there must be a way how I can write them as well, right?

What is the exptected output of cells with `TimeSpan` value? How do you want them to appear in Excel. — tozlu, Nov 27 '20 at 07:56
Like time values, the same that I get when manually writing from Microsoft Excel values "12:30:00" or "999:59:59". — Morgan Rose, Nov 27 '20 at 08:25
There is no direct TimeSpan equivalent in Excel. You should set the value as number and use excel formatting to show. Example here; https://stackoverflow.com/questions/43896427/write-a-timespan-on-an-excel-file . Also check `TimeValue` function, it can help. — tozlu, Nov 27 '20 at 08:54

score 1 · Accepted Answer · answered Nov 27 '20 at 10:45

Excel files don't have separate types for dates and for times.

When reading those cells with time values, you may notice that they have ExcelCell.ValueType set to CellValueType.DateTime. In other words, the ExcelCell.Value contains the DateTime object.

Also, if you check the ExcelCell.Style.NumberFormat you'll notice it defines some time format like "h:mm:ss".

Anyway, try using this SetValue extension method:

public static class GemBoxHelper
{
    public static void SetValue(this ExcelCell cell, TimeSpan span, string format = "[h]:mm:ss")
    {
        DateTime start = cell.Worksheet.Parent.Use1904DateSystem ?
            new DateTime(1904, 1, 1) :
            new DateTime(1899, 12, 30);

        DateTime date = start.Add(span);
        DateTime leapYearBug = new DateTime(1900, 3, 1);

        if (date < leapYearBug)
            date = date.AddDays(1);

        cell.SetValue(date);
        cell.Style.NumberFormat = format;
    }
}

Also, here is an example showing how to use it:

var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");

worksheet.Cells["A1"].SetValue(new TimeSpan(12, 30, 00));
worksheet.Cells["A2"].SetValue(new TimeSpan(999, 59, 59));

workbook.Save("output.xlsx");

Set Excel cell to TimeSpan value

1 Answers1