How to save .NET DateTime in OpenDocument

Question

I'm using C# to write out an OpenDocument spreadsheet in XML. No problem with writing double values, which work fine just like this:

    private void SaveFloatCell(XmlNode rowNode, XmlDocument ownerDocument, double number)
    {
        XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table"));

        XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office"));
        valueType.Value = "float";
        cellNode.Attributes.Append(valueType);

        XmlAttribute value = ownerDocument.CreateAttribute("office:value", this.GetNamespaceUri("office"));
        value.Value = number.ToString(CultureInfo.InvariantCulture);
        cellNode.Attributes.Append(value);

        rowNode.AppendChild(cellNode);
    }

However, when trying to save DateTime values I can't get them to display properly. This is what I have so far, which displays "2012" in the cell instead of the specified Date format:

    private void SaveDateTimeCell(XmlNode rowNode, XmlDocument ownerDocument, double number, string format)
    {
        XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table"));

        XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office"));
        valueType.Value = "date";
        cellNode.Attributes.Append(valueType);

        XmlAttribute value = ownerDocument.CreateAttribute("office:value", this.GetNamespaceUri("office"));
        value.Value = Utils.DateTime(number).ToString("yyyy-mm-ddThh:mm:ss");
        cellNode.Attributes.Append(value);

        rowNode.AppendChild(cellNode);
    }

I've spent quite a while playing with various code snippets I've found, but with no success. On the offchance, is there any generous soul out there that can help me with this?

Many thanks!

score 1 · Answer 1 · answered Apr 24 '12 at 14:32

1

It's possible that you just need to fix your format, given that it's currently broken:

value.Value = Utils.DateTime(number).ToString("yyyy-MM-ddTHH:mm:ss");

Changes:

Use M for month, not m (which means minutes)
Use HH for hours, not hh (which is 12-hour clock)

I would suggest specifyinh CultureInfo.InvariantCulture as well, to show that you don't want any cultural settings applied.

answered Apr 24 '12 at 14:32

Jon Skeet

1,421,763
867
9,128
9,194

Thanks Jon - unfortunately CultureInfo.InvariantCulture doesn't work and your suggested changes to the format don't either. I've got a feeling that the answer has something to do with the black art of OpenDocument XML attributes. Many thanks anyhow for your help! – Shunyata Kharg Apr 24 '12 at 14:39

Shunyata Kharg · Accepted Answer · 2012-04-25T13:10:45.463

Ok, I have the answer. The final code looks like this:

    private void SaveDateTimeCell(XmlNode rowNode, XmlDocument ownerDocument, double number, string format)
    {
        XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table"));

        XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office"));
        valueType.Value = "date";
        cellNode.Attributes.Append(valueType);

        XmlAttribute value = ownerDocument.CreateAttribute("office:date-value", this.GetNamespaceUri("office"));
        value.Value = Utils.DateTime(number).ToString("yyyy-MM-ddTHH:mm:ss");
        cellNode.Attributes.Append(value);

        XmlAttribute tableStyleName = ownerDocument.CreateAttribute("table:style-name", this.GetNamespaceUri("table"));
        tableStyleName.Value = "ce2";
        cellNode.Attributes.Append(tableStyleName);

        rowNode.AppendChild(cellNode);
    }

It's the definition of "ce2" which is key. This is done in the styles.xml file of the unzipped *.ods file:

    private void SaveStyleSheet(XmlNode sheetsRootNode)
    {
        XmlDocument ownerDocument = sheetsRootNode.OwnerDocument;

        XmlNode sheetNode = ownerDocument.CreateElement("number:date-style", this.GetNamespaceUri("number"));

        XmlAttribute styleName = ownerDocument.CreateAttribute("style:name", this.GetNamespaceUri("style"));
        styleName.Value = "N19";
        sheetNode.Attributes.Append(styleName);

        XmlElement numberDay = ownerDocument.CreateElement("number:day", this.GetNamespaceUri("number"));
        XmlAttribute numberStyle = ownerDocument.CreateAttribute("number:style", this.GetNamespaceUri("number"));
        numberStyle.Value = "long";
        numberDay.Attributes.Append(numberStyle);
        sheetNode.AppendChild(numberDay);

        XmlElement numberText = ownerDocument.CreateElement("number:text", this.GetNamespaceUri("number"));
        numberText.InnerText = "/";
        sheetNode.AppendChild(numberText);

        XmlElement numberMonth = ownerDocument.CreateElement("number:month", this.GetNamespaceUri("number"));
        XmlAttribute numberStyle2 = ownerDocument.CreateAttribute("number:style", this.GetNamespaceUri("number"));
        numberStyle2.Value = "long";
        numberMonth.Attributes.Append(numberStyle2);
        sheetNode.AppendChild(numberMonth);

        XmlElement numberText2 = ownerDocument.CreateElement("number:text", this.GetNamespaceUri("number"));
        numberText2.InnerText = "/";
        sheetNode.AppendChild(numberText2);

        XmlElement numberYear = ownerDocument.CreateElement("number:year", this.GetNamespaceUri("number"));
        XmlAttribute numberStyle3 = ownerDocument.CreateAttribute("number:style", this.GetNamespaceUri("number"));
        numberStyle3.Value = "long";
        numberYear.Attributes.Append(numberStyle3);
        sheetNode.AppendChild(numberYear);

        sheetsRootNode.AppendChild(sheetNode);
    }

This date-style N19 is then referred to in the automatic-styles of the content.xml of the unzipped *.ods file thus:

    private void SaveAutomaticStyleSheet(XmlNode sheetsRootNode)
    {
        XmlDocument ownerDocument = sheetsRootNode.OwnerDocument;

        XmlNode sheetNode = ownerDocument.CreateElement("style:style", this.GetNamespaceUri("style"));

        XmlAttribute styleName = ownerDocument.CreateAttribute("style:name", this.GetNamespaceUri("style"));
        styleName.Value = "ce2";
        sheetNode.Attributes.Append(styleName);

        XmlAttribute styleFamily = ownerDocument.CreateAttribute("style:family", this.GetNamespaceUri("style"));
        styleFamily.Value = "table-cell";
        sheetNode.Attributes.Append(styleFamily);

        XmlAttribute styleParentStyleName = ownerDocument.CreateAttribute("style:parent-style-name", this.GetNamespaceUri("style"));
        styleParentStyleName.Value = "Default";
        sheetNode.Attributes.Append(styleParentStyleName);

        XmlAttribute styleDataStyleName = ownerDocument.CreateAttribute("style:data-style-name", this.GetNamespaceUri("style"));
        styleDataStyleName.Value = "N19";
        sheetNode.Attributes.Append(styleDataStyleName);

        sheetsRootNode.AppendChild(sheetNode);
    }

In fact, Jon's suggestions were necessary as well for the code to work perfectly. So thanks again Jon. Anyhow, a black, black art indeed ... :)

How to save .NET DateTime in OpenDocument

2 Answers2