XML Excel cell format issue

[ilya_eremetov]

Hi everyone, i faced with format of cell issue in excel file, created through XML transformation.

I have this Style:

         <Style ss:ID="tabDate">

           <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>

           <Borders>

             <Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>

             <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>

             <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>

             <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>

           </Borders>

           <Font ss:Bold="0" ss:Size="10" x:Family="Swiss"/>

           <NumberFormat ss:Format="Short Date"/>

           <Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>

         </Style>

And this Cell with style id attached to it:

          <tt:loop ref=".ITAB">

             <Row>

              <Cell ss:StyleID="tabDate">

                 <Data ss:Type="String">

                   <tt:value ref="ASDATE"/>

                 </Data>

               </Cell>

              </Row>

What i want to see in excel is date in format like 'DD.MM.YYYY' but i see date like 'YYYY-MM-DD'. Interesting thing that when i doubleclick to the cell and push Enter button this date is transforming to 'DD.MM.YYYY'.

The question is how to make Excel to work properly with XML?

[Sandra_Rossi]

As always, to know what you have to do, you must first manually mimic the behavior using Excel (format like 'DD.MM.YYYY'), save it as XML 2003, and open the file in notepad or equivalent to see how Microsoft built the format elements and attributes, and then mimic it in your program.

[ilya_eremetov]

Sandra, thank you for your reply.

I made it that way and in txt file i have this notes:

...

  <Style ss:ID="s62">

   <NumberFormat ss:Format="Short Date"/>

  </Style>

...

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">2016-01-21T00:00:00.000</Data></Cell>

   </Row>

As you see the only difference with my xml document is in this part 'ss:Type="DateTime"' but even when i changed it - it didn't help excel document to change format of data in the cell. It still 'YYYY-MM-DD'

[Sandra_Rossi]

That's because, in Excel, you didn't select the right format. You have selected "Short Date". Instead, if you select the format dd.mm.yyyy;@, you'll get:

  <Style ss:ID="s62">

   <NumberFormat ss:Format="dd\.mm\.yyyy;@"/>

  </Style>

[ilya_eremetov]

Where did you find this mask? But no, even i set  <NumberFormat ss:Format="dd\.mm\.yyyy;@"/> in Excel it looks like 'YYYY-MM-DD' only if i double click to the cell then click Enter, it changes to 'DD.MM.YYYY'.

[Sandra_Rossi]

In Excel, you may choose the formatting you want (there are "standard", "short date", "text", etc. and at the bottom you have a link "Advanced formatting" in which you see more options). So, try again, it will work with Excel for sure, and so you'll just have to mimic the same XML.

[ilya_eremetov]

Hi Sandra! Thank you for your time! But it is not working for me, Excel don't want to change YYYY-MM-DD to DD.MM.YYYY in the cell but the format of the cell IS DD.MM.YYYY. Could you please do the same thing? I mean create a simple transformation program with only one incoming paramater(Sy-datum) and save xmlstring on local machine as *.XLS file. I can give you the code of xslt program and abap.

[Sandra_Rossi]

I'm afraid I don't understand where your issue is. You should be able to achieve format DD.MM.YYYY with Excel and save it as XML. If you change the date in the XML manually, and open the file, you'll see that the file contains the manually entered date, in the format DD.MM.YYYY. Then all you need is to mimic that in ABAP. If you can't achieve the format manually, then no need to try ABAP.

[ilya_eremetov]

My situation is:

Call abap program that calls transformation program and then the file saves in app server with extension *.XLS in the background. then after opening file Excel doesn't convert the date from 2016-09-23 to 23.09.2016 however in xml structure of this file i have            <NumberFormat ss:Format="Short Date"/>.

That's the issue. I can do convert Date format in ABAP to DD.MM.YYYY and then send it to the XML in that way but i want to know why does this not working properly?

[Sandra_Rossi]

I told you that it's not Short Date, and I have given you the format you expect (but you could have found it yourself, and it works for anything, the way I told you previously).

If you can't make it work by modifying manually the XML, why do you persist doing it in ABAP: first do it manually in the XML, and make sure it works as expected, then do it in ABAP.

[ilya_eremetov]

It doesn't matter what i put to the <NumberFormat ss:Format=""/> the point is that it is not working no matter if this dd\.mm\.yyyy;@ (as i told) or Short Date or anything else. I am asking how to fix that if it possible. Thank you for your time but it is not solution of my problem.

[Sandra_Rossi]

Let's say we have this Excel file (XMLSS 2003):

<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
  <Style ss:ID="ddmmyyyy">
   <NumberFormat ss:Format="dd\.mm\.yyyy;@"/>
  </Style> </Styles> <Worksheet ss:Name="Sheet1">
  <Table>
   <Row>
    <Cell ss:StyleID="ddmmyyyy"><Data ss:Type="DateTime">2013-10-15T00:00:00.000</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

When you open it, you'll see that it's what you expect:

So, if your abap simply writes the cells with ss:StyleID="ddmmyyyy" (you may choose another name if you want) and date in internal excel format (yyyy-mm-ddT00:00:00.000), then it should be okay.

[ilya_eremetov]

Absolutely right. But to make it work i need to send date to XML in mask 'YYYY-MM-DD' from ABAP, but i send it in the standard type of dates in abap 'YYYYMMDD' (type sy-datum). If i need to send it in YYYY-MM-DD then i prefer to send it in dd.mm.yyyy (char10) format already. What is the point of using construction 'YYYY-MM-DD' in this situation? I don't see it.

[Sandra_Rossi]

This is all about the standards. If you want to benefit of a standard, you must comply to the rules. Here, you want an Excel, hopefully there is XMLSS (2003), so it's easy to generate an Excel sheet (can you imagine what was the nightware for developers before this format was available!), but you have to make an effort to pass the date in internal format (anyway, it's ISO 8601 format, so it's widely used in all XML formats). It's technically an error to send in another format (dd.mm.yyyy in your case). What would happen if we want to create calculation formulas over these dates... That won't immediately work, it will require extra formulas to convert them to the right format before using these dates.