javascript - Exporting XML from Excel and Keeping Date Format - Stack Overflow

admin管理员组

文章数量:1295306

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 e 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.

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 e 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.

• javascript
• xml
• excel
• date

Share Follow asked Oct 27, 2011 at 15:29 Nicholas FleesNicholas Flees 1,95333 gold badges2222 silver badges3030 bronze badges 3

• How are you exporting the XML? – Tim Williams Commented Oct 27, 2011 at 15:59
• I'm using the export button on the developer tab. I'm using Excel 2007, by the way. – Nicholas Flees Commented Oct 27, 2011 at 16:05
• Do you have the ability to edit the Excel file or is there some reason it has to remain exactly as is before you export it? – Joel Spolsky Commented Oct 27, 2011 at 17:05

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

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 pletely 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 plete 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 aplish 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

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:

<date>1/1/2012</date>
<page>mywebsite.</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:

<date>2012-01-01</date>
<page>mywebsite.</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!!

For an example of how to build your own schema, I found this video to be spot on: http://www.youtube./watch?v=9bat12gH3Qs

本文标签： javascriptExporting XML from Excel and Keeping Date FormatStack Overflow