CODEWITHSUNDEEP
c#excelopenxml
meitsrik
meitsrik

Reputation: 61

Writing a TimeSpan to a cell using OpenXml without using CellValues.Date DataType

I'm creating a graph in Excel using OpenXml. The graph shows a TimeSpan of elapsed time between two DateTimes.

I'm writing my elapsed time to a cell in the following way

new Cell {DataType = new EnumValue<CellValues>(CellValues.Date), StyleIndex = 4, CellValue = new CellValue(ElapsedTime.ToString())}

where StyleIndex = 4 corresponds to 

cellFormats.AppendChild(new CellFormat {NumberFormatId = 21, ApplyNumberFormat = true, FontId = 2, ApplyFont = true});

however this is causing some problems with Excel 2007 as it doesn't support date time.

The best solution I've found for this so far is to convert it to the double equivalent of the DateTime and write that to the cell but that seems ugly and I'd rather maintain my hours/minutes format.

Upvotes: 4

Views: 1049

Answers (2)

Wilhelm
Wilhelm

Reputation: 306

I managed to solve this with the following code (the cellStringValue should be like 14:45):

    TimeSpan TsValue = new TimeSpan();
    TimeSpan.TryParse(cellStringValue, out TsValue);
    double ms = TsValue.TotalSeconds;
    double ms1 = (ms / 86400);
    myCell.CellValue = new CellValue(ms1.ToString(CultureInfo.InvariantCulture));

Excel saves time in seconds divided by maximum seconds of a day. Using no datatype or datatype CellValues.Number - the result is the same. The cell should have a StyleIndex pointing to a CellFormat with for instance 20 what is "hh:mm" and where StyleIndex is the position in CellFormats listing. My beginner problem was assuming, that a newly created workbook was having all the cellformats built in. That was wrong. You need to add them manually. At least one empty, one for date and one for time.

Upvotes: 2

meitsrik
meitsrik

Reputation: 61

I managed to work around this by writing the cell as a number cell as the number cell type can be used to write dates to. Although it didn't seem to support TimeSpans, I worked around this by representing my TimeSpan as a DateTime and adding my TimeSpan to the MinValue of DateTime:

new Cell {DataType = new EnumValue<CellValues>(CellValues.Number), StyleIndex = 4, CellValue = new CellValue(DateTime.MinValue.Add(platformOccupancyRow.ElapsedTime).ToOADate().ToString(CultureInfo.CurrentCulture))}

Upvotes: 0

Related Questions