Create XML with Excel, date field formatting problem

Question

I have an Excel spreadsheet, generated by SAP with a date field. I need to convert this spreadsheet to XML.

I do the XML mapping by Excel, but I cannot make it export the date field as a date, always appears the equivalent number as below, where the date is 06/06/2014, and in XML appears 41796.

I’ve tried some combinations of formatting in Excel, but none helped. Even has a post right here in stackoverflow, but solved my case.

Note: When I convert range to xml, msg appears: The data you are trying to map contains an incomparable formatting with the format specified in the spreadsheet.

I am using the XML add-in tools from Excel itself.

I wonder if there’s some configuration missing. Is there any way to specify the data type in the XML map?

     <Row>
        <Documento_SD>3208510</Documento_SD>
        <Denominacao>DISJUNTOR DWA800N-630-3</Denominacao>
        <Nome_1>A B RODRIGUES</Nome_1>
        <Data_de_remessa>41796</Data_de_remessa>
     </Row>

Thanks for your help.

Answer 1

I found the solution, only one question left to solve, where the date is coming out in English pattern and with "-" instead of "/" yyaa-mm-dd, if someone knows how to manipulate this in Delphi, without having to read the whole string, replace, etc. Or even in Excel xml map, that would be the right thing to do.

To resolve this issue:

I created an xml as first post, but with the date in the right dd/mm/yyyy format, imported the xml in Excel, and generated an xml schema (below), I only had to manually insert that the field type was date ( type="xsd:date"). Then just take the spreadsheet with the data and apply this scheme. When exporting, the field goes with date, only the question of field formatting, which has the fields spaced with - and the English standard.

<?xml version='1.0' encoding='UTF-16'?>
<!-- Created from XmlMap.Name: Root_Mapa -->
<!-- XmlMap.DataBinding.SourceUrl:  -->
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="">
<xsd:element nillable="true" name="Root">
    <xsd:complexType>
        <xsd:sequence minOccurs="0">
            <xsd:element minOccurs="0" maxOccurs="unbounded" 
 nillable="true" name="Row" form="unqualified">
                <xsd:complexType>
                    <xsd:sequence minOccurs="0">
                        <xsd:element minOccurs="0" nillable="true" 
 type="xsd:integer" name="Documento_SD" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" 
 type="xsd:string" name="Denomina__o" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" type="xsd:string" 
 name="Nome_1" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" type="xsd:date" 
 name="Data_de_remessa" form="unqualified"/>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:sequence>
    </xsd:complexType>
 </xsd:element>
 </xsd:schema>

Output from XML:

 <Row>
    <Documento_SD>3249301</Documento_SD>
    <Denomina__o>FENIX INDUSTRIA E PRESTACAO DE</Denomina__o>
    <Nome_1>CHAVE PDWCA08-5V40</Nome_1>
    <Data_de_remessa>2014-07-04</Data_de_remessa>
</Row>