0

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?

Alex
  • 689
  • 1
  • 8
  • 22
  • Rather than shove a timespan directly into Excel, why not a starting time and a ending time? That way you can [let Excel find the difference and format it however you like](http://stackoverflow.com/a/7929360/2596334). – Scott Solmer Dec 03 '14 at 20:37
  • The List data comes from a separate database generated by a separate project. They represent average times to do certain tasks, so no start/finish date. The purpose of this excel/word document is to report the averages in a graphical way. – Alex Dec 03 '14 at 20:39
  • You could always make up an arbitrary start time, then add the times from your object to get an "end time". – Scott Solmer Dec 03 '14 at 20:41
  • how about showing how you are formatting the Date in regards to why you are seeing `AM/PM` that sounds like a `string.Format()` issue – MethodMan Dec 03 '14 at 20:42
  • @Okuma.Cott That is true. But the real question I have is, how to convert my TimeSpan (or start/finish times) into the small number, e.g. how the Open XML SDK Productivity Tool did with turning 01:04:04 to "4.449074074074074E-2" – Alex Dec 03 '14 at 20:44
  • To convert the `TimeSpan` to the number you need to create a `DateTime` and call `ToOADate()` on it. E.g. `TimeSpan t = new TimeSpan(1, 4, 4); DateTime d = new DateTime(t.Ticks); Console.WriteLine(d.ToOADate().ToString("E15"));` – petelids Dec 04 '14 at 12:44

1 Answers1

1

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

Scott Solmer
  • 3,871
  • 6
  • 44
  • 72
  • Thanks... I'm not sure if the bar graph will show up correctly with this formula. Also, the real question is, how to convert a c# timespan, e.g. 1:04:04 into that number 0.0444907407407407, because I'm generating the document with c# code. – Alex Dec 03 '14 at 21:11
  • I see what you mean from the image. Thanks. – Alex Dec 03 '14 at 21:16