从Excel导出XML并保留日期格式 [英] Exporting XML from Excel and Keeping Date Format

问题描述

很多StackOverflow的帖子和其他在线论坛都有类似的问题，但是他们都没有真正解决我遇到的问题。

我正在导出一个XML文件从Excel。 Excel文件包含日期。然而，在XML文件中，这些日期显示为整数 - 日期自1/0/1900以来是第n天。

I我使用JavaScript从XML文件中提取数据以填充网页中的表。

我怀疑一种解决方案有两种不同的形式：
1.一种在JavaScript中将整数转换为代表日期的方式。
2.强制XML文件从Excel文件格式化日期的一种方式。

我显然是XML和JavaScript的新手，所以您的协助将得到真诚的赞赏。谢谢。

解决方案

Excel用于xml-export的格式取决于添加的XML模式。可以通过Excel创建和添加明确的

在我的例子中，我有两个列，第一个是通过XML-Schema格式化日期时间，第二个是普通文本，只有Excel中的表示（列格式）一个日期。 >

当我将其导出为XML时，我得到两个看似完全不同的结果：

 <？xml version =1.0encoding =UTF-8standalone =yes？> 
< dataroot> 
< Tabelle11> 
< Title_1> 2009-10-12T00：00：00.000< / Title_1> 
< Title_2> 40098< / Title_2> 
< / Tabelle11> 
< Tabelle11> 
< Title_1> 2000-01-01T00：00：00.000< / Title_1> 
< Title_2> 36526< / Title_2> 
< / Tabelle11> 
< / dataroot> 
  

当我检查XML模式（例如使用Debug.Print ActiveWorkbook.XmlMaps（1））。模式（1）.XML）我看到差异（只是片段，而不是完整的文件）：

 < xsd： element name =Title_1minOccurs =0od：jetType =datetimeod：sqlSType =datetimetype =xsd：dateTime> 
< xsd：annotation> 
< xsd：appinfo> 
< od：fieldProperty name =Formattype =10value =dd.mm.yyyy/> 
< / xsd：appinfo> 
< / xsd：annotation> 
< / xsd：element> 
< xsd：element name =Title_2minOccurs =0od：jetType =textod：sqlSType =nvarchar> 
< xsd：annotation> 
< xsd：appinfo> 
< od：fieldProperty name =Formattype =10value =@/> 
< / xsd：appinfo> 
< / xsd：annotation> 
< xsd：simpleType> 
< xsd：restriction base =xsd：string> 
< xsd：maxLength value =255/> 
< / xsd：restriction> 
< / xsd：simpleType> 
< / xsd：element> 
  

第一列是格式为datetime的模式，第二列为varchar。再次，您可能甚至不知道Excel如何处理您的XML数据，因为此模式会自动生成（如果我在Excel 2003中正确记录了一条消息，指示模式已为您生成了benn）

现在如何解决这个问题 - 特别是在现有的文件中：我会尝试提取XML-Schema （见上文），将其另存为something.xsd ，根据您的需要调整模式并导入此新模式。 Bummer你必须重新设置映射（或者尝试这样做： Excel 2007 XML源地图 - 刷新模式）。通常我让MS Access生成我的XML模式，即生成所需的表，插入一些值，并将表格作为XML表示（勾选添加模式的选项）。

我希望这个大纲给你一个如何进行的想法。

Regards

Andreas

Many StackOverflow posts and other online forums have questions similar to this, but none of them really resolve the issue I'm having.

I'm exporting an XML file from Excel. The Excel file contains dates. In the XML file, however, those dates show up as integers--the date is made to be the nth day since 1/0/1900, I believe.

I'm using JavaScript to extract the data from the XML file to populate a table in a web page.

I suspect that a solution could come in two different forms: 1. A way to convert, in JavaScript, the integer to the date it represents. 2. A way to force the XML file to take a formatted date from the Excel file.

I'm clearly a novice with XML and JavaScript, so your assistance would be sincerely appreciated. Thanks.

解决方案

The format Excel uses for the xml-export depends on the added XML-schema. This can be explicitly created and added or -- as I suspect in your case -- has been implicitly/ automatically generated by Excel.

In my example I have two colums, the first one has via XML-Schema to format date-time, the second is normal text and only for the representation in Excel (column format) a date.

When I export this as XML I get two seemingly completely different results:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataroot>
    <Tabelle11>
        <Title_1>2009-10-12T00:00:00.000</Title_1>
        <Title_2>40098</Title_2>
    </Tabelle11>
    <Tabelle11>
        <Title_1>2000-01-01T00:00:00.000</Title_1>
        <Title_2>36526</Title_2>
    </Tabelle11>
</dataroot>

When I check the XML-Schema (e.g. with "Debug.Print ActiveWorkbook.XmlMaps(1).Schemas(1).XML") I see the difference (only snippet, not the complete file):

<xsd:element name="Title_1" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime">
    <xsd:annotation>
        <xsd:appinfo>
            <od:fieldProperty name="Format" type="10" value="dd.mm.yyyy"/>
        </xsd:appinfo>
    </xsd:annotation>
</xsd:element>
<xsd:element name="Title_2" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
    <xsd:annotation>
        <xsd:appinfo>
            <od:fieldProperty name="Format" type="10" value="@"/>
        </xsd:appinfo>
    </xsd:annotation>
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="255"/>
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

The first column is in the schema of format datetime, the second is varchar. Again, you might not even be aware, how Excel handles your XML-data, since this schema is automatically generated (if I recall correctly in Excel 2003 there was a message indicating that an schema had benn generated for you)

Now how to acomplish this -- especially in an existing file: I would try to extract the XML-Schema (see above), save it as something.xsd, adapt the schema according to your needs and import this new Schema. Bummer is you have to set the mapping again (or try this: Excel 2007 XML Source Maps - Refreshing Schemas). Often I let MS Access generate my XML-Schema, i.e. generate the required table, insert some values and exprt the table as XML (ticking the option to add the schema).

I hope this general outline gives you an idea on how to proceed.

Regards

Andreas

这篇关于从Excel导出XML并保留日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！