CODEWITHSUNDEEP
c#excel.net-4.0openxmlopenxml-sdk
Alex
Alex

Reputation: 699

C# TimeSpan to Excel in Open XML

I'm working on a project to create a bar graph showing times in a MS Word 2010 document. The main program stores these times in a List<TimeSpan>

I created an example word document using the Insert Chart feature in Word, and made a bar chart with various times (around 1 hour each, formatted as, for example, 01:04:04. (using this code as a template).

I looked in the Open XML SDK 2.0 Productivity Tool and noticed that it was generated in the reflected code as (for the example above):

numericValue9.Text = "4.449074074074074E-2"; //01:04:04

If I want to automatically generate this using my List<TimeSpan>, how do I convert my TimeSpans to this small numerical value? Is that TimeSpan.TotalDays?

Also, looking at the Word Document (right clicking the chart and selecting Edit Data), I noticed that "AM" is appended to the end of my TimeSpan. Does excel convert my timespan into a datetime?

Upvotes: 0

Views: 1262

Answers (1)

Scott Solmer
Scott Solmer

Reputation: 3897

The reason this is happening, as DJ KRAZE mentioned, is due to the cell's format.
Open XML assumes DataType = CellValues.String but it will return something that doesn't look right (your "small number") if it isn't actually type string.

The way to correct this issue is simply to format the cell as text. You can do this by using a formula like so: 

// A1 being what ever cell your original data is in
=TEXT(A1, "h:mm:ss")

The result:

result

Upvotes: 1

Related Questions