Search code examples
c#exceldatetimegembox-spreadsheet

Set Excel cell to TimeSpan value


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?


Solution

  • 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");