javascript 从 Excel 导出 XML 并保持日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7918347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting XML from Excel and Keeping Date Format
提问by Nicholas Flees
Many StackOverflow posts and other online forums have questions similar to this, but none of them really resolve the issue I'm having.
许多 StackOverflow 帖子和其他在线论坛都有与此类似的问题，但没有一个能真正解决我遇到的问题。
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.
我正在从 Excel 导出 XML 文件。Excel 文件包含日期。然而，在 XML 文件中，这些日期显示为整数——我相信日期是自 1/0/1900 以来的第 n 天。
I'm using JavaScript to extract the data from the XML file to populate a table in a web page.
我正在使用 JavaScript 从 XML 文件中提取数据以填充网页中的表格。
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.
我怀疑解决方案可以有两种不同的形式： 1. 一种在 JavaScript 中将整数转换为它所代表的日期的方法。2. 一种强制 XML 文件从 Excel 文件中获取格式化日期的方法。
I'm clearly a novice with XML and JavaScript, so your assistance would be sincerely appreciated. Thanks.
我显然是 XML 和 JavaScript 的新手，因此真诚地感谢您的帮助。谢谢。
回答by Andreas J
The format Excel uses for the xml-export depends on the added XML-schema. This can be explicitlycreated and added or -- as I suspect in your case -- has been implicitly/ automatically generatedby Excel.
Excel 用于 xml 导出的格式取决于添加的 XML 架构。这可以显式创建和添加，或者 - 正如我怀疑你的情况 - 已经由 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.
在我的示例中，我有两个列，第一个通过 XML-Schema 格式化日期时间，第二个是普通文本，仅用于在 Excel（列格式）中表示日期。
When I export this as XML I get two seemingly completely different results:
当我将其导出为 XML 时，我得到两个看似完全不同的结果：
<?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):
当我检查 XML 架构（例如使用“Debug.Print ActiveWorkbook.XmlMaps(1).Schemas(1).XML”）时，我看到了不同之处（只有片段，而不是完整的文件）：
<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)
第一列采用格式日期时间的模式，第二列是 varchar。同样，您甚至可能不知道 Excel 如何处理您的 XML 数据，因为此架构是自动生成的（如果我在 Excel 2003 中没有记错的话，有一条消息表明已经为您生成了一个架构）
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).
现在如何实现这一点——尤其是在现有文件中：我会尝试提取 XML 架构（见上文），将其保存为 something.xsd，根据您的需要调整架构并导入这个新架构。糟糕的是你必须再次设置映射（或试试这个：Excel 2007 XML Source Maps - Refreshing Schemas）。我经常让 MS Access 生成我的 XML 架构，即生成所需的表，插入一些值并将表导出为 XML（勾选添加架构的选项）。
I hope this general outline gives you an idea on how to proceed.
我希望这个大纲能让你对如何进行有一个想法。
Regards
问候
Andreas
安德烈亚斯
回答by Brian Vanderbusch
It's actually pretty simple... if you're creating your own "schema" for example. Create an xml file, and say you have google analytics data to populate your full xml file with each row having the following three data points:
它实际上非常简单……例如，如果您要创建自己的“模式”。创建一个 xml 文件，并假设您有谷歌分析数据来填充您的完整 xml 文件，每行具有以下三个数据点：
<date>1/1/2012</date>
<page>mywebsite.com</page>
<visits>100000</visits>
When you import this xml file as a data map schema for excel before exporting, you'll wind up the the integer you describe above. However, if you define this schema instead as:
当您在导出之前将此 xml 文件导入为 excel 的数据映射模式时，您将得到上面描述的整数。但是，如果您将此架构定义为：
<date>2012-01-01</date>
<page>mywebsite.com</page>
<visits>100000</visits>
Excel will show an additional grouping for the date element when you organize your xml on the sheet. Use the value (not the format()) as the column header, and your export will be the formatted date instead of the integer. You don't need to do any special .xsd extraction from excel or access!!
当您在工作表上组织 xml 时，Excel 将显示日期元素的附加分组。使用值（而不是 format()）作为列标题，您的导出将是格式化日期而不是整数。您不需要从 excel 或 access 中进行任何特殊的 .xsd 提取！！
For an example of how to build your own schema, I found this video to be spot on: http://www.youtube.com/watch?v=9bat12gH3Qs
有关如何构建自己的架构的示例，我发现此视频位于：http: //www.youtube.com/watch?v=9bat12gH3Qs
