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?
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.ValueTypeset toCellValueType.DateTime. In other words, theExcelCell.Valuecontains theDateTimeobject.Also, if you check the
ExcelCell.Style.NumberFormatyou'll notice it defines some time format like "h:mm:ss".Anyway, try using this
SetValueextension method:Also, here is an example showing how to use it: