如何在OpenDocument中保存.NET DateTime

Question

我正在使用C#编写一个OpenDocument电子表格。编写双精度值是没有问题的，就像这样：

    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);
    }

但是，当尝试保存DateTime值时，我无法正确显示它们。这就是我到目前为止所做的，它在单元格中显示"2012“，而不是指定的日期格式：

    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);
    }

我花了相当多的时间尝试我找到的各种代码片段，但都没有成功。在偶然的情况下，有没有慷慨的灵魂可以帮助我？

非常感谢！

Answer 1

好了，我有答案了。最终的代码如下所示：

    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);
    }

这是"ce2“的定义，这是关键。这是在解压缩后的*.ods文件的styles.xml文件中完成的：

    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);
    }

然后，在解压缩的*.ods文件的content.xml的自动样式中引用此日期样式的N19，如下所示：

    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);
    }

事实上，Jon的建议对于代码的完美工作也是必要的。再次感谢乔恩。无论如何，一种黑色，黑色的艺术确实... :)

Answer 2

有可能你只需要修复你的格式，因为它目前是坏的：

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

更改：

• 使用M表示月，而不是m(表示分钟)
• 使用HH表示小时，而不是hh (12小时制)

我也建议指定CultureInfo.InvariantCulture，以表明您不希望应用任何文化设置。