Skip to Content
Introduction

Users love to download every kind of tabular data from portals to Microsoft Excel or their preferred spreadsheet application. Developing web applications since years, I can say that just the ABAP Web Dynpro technology released in NW2004s (when using ALV) is providing what every developer expects from the basic engine. If you think that it’s so easy to download data to excel, just run a search for “excel download” in sdn! I got 37 blogs and 284 posts in the Forums. Probably it is one of the most hot topic? Gurus like Brian McKeller and Thomas Jung already produced professional documentation, examples and also nice working BSP extensions to support us about this topic. Even if everything works fine I want to “recommend” NetWeaver developers to generate well formatted spreadsheets and not the nasty .CSV files.

Never more .CSV files

In the thread BSP and Excel the approach I prefer has been introduced.

How it works…

The actual release of Microsoft Excel (Microsoft Office Excel 2003) is able to read and understand files formatted in html. The idea is to download tabular data (e.g. invoice list) as simple html page declaring table, rows and columns via the HTML tags. The file name will be proposed with the “.xls” extension (e.g. invoiceList.xls) and the mime type will be set to “application/vnd.ms-excel”.

An example

Via your preferred technology (e.g. BSP, XSLT, JSP, Java Web Dynpro, …) here is a simple html that should be generated:    

a b c
=1000 =2000 =3000
=1,111 =2,222 =3,222
=1,111 =2,222 =3,222
=SUM(A2:A4) =SUM(B2:B4) =SUM(C2:C4)
a b c
28/05/2006 01/05/2006 21/05/2006
28/05/2006 01/05/2006 21/05/2006
=”From city” =”To city”
=”Milano” =”Roma”
Milano Roma
=COUNTA(A10:A12) =COUNTA(B10:B12)

Here is the result: image

Tips & tricks
  • html formatting options will be will understood by Microsoft Excel (e.g. embedded styles, style classes)
  • write texts without manipulation
  • write dates as expected by the local setting of Excel (e.g. 28/05/2006 for Italy)
  • precede numbers, amounts and quantities with the = (equal sign)
  • precede formulas with the = (equal sign). Pay attention to the local language of excel (eg. SUM works in English version of Excel but not in the Italian one)
Compatibility

OpenOffice.org Calc is able to import HTML files but not the new entry spreadsheet.google.com.

To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

      1. Robert Leung
        Hi,
        I found this Excel – XML is interesting.  I am looking at creating a spreadsheet with multi worksheets(e.g. 2 tables) using ABAP and want to run in background.  I have been told to do it with excel output in xml format.  Do you know of any sample code where I can follow and see how it works?  Thanks – R
        (0) 
        1. Sergio Ferrari Post author
          Hi Robert,
            I think you are speaking about the Excel proprietary XML implementation.
            Try in this way: create a new file, fill few values in sheet1 and then in sheet2. Now save the file selecting the file type (Spreadsheet XML format) in the Save file popup.
            Open the generate XML file with notepad and study the XML structure. It’s not so complicated…
            You’ll be able to generate something like that via ABAP with XSLT (CALL TRANSFORMATION…) or iXML classes..

            Some links:
          http://support.microsoft.com/kb/288130/en-us
          http://www.codeproject.com/csharp/excelxmlreport.asp

          Sergio

          (0) 
        2. Anton Wenzelhuemer
          you could also write an excel macro which directly downloads and presents data from your ABAP backend in realtime using webservices.
          see my respective comments in this Excel to SAP R/3 (it gives examples for word and excel)

          regards, anton

          (0) 
  1. Guillaume GARCIA
    Hi Sergio,

    So useful… !!
    I’ve always struggled to format those Excel files and it’s great to see it can be done using only HTML.

    I tried with my Excel 2002 SP 3 version ant it works too. The only difference is that it does not merge cells the way it does on your screenshot.

    Best regards,
    Guillaume

    (0) 
    1. Sergio Ferrari Post author
      Hi Guillaume,
        I did not see the merge and it is not defined in the HTML.
        We can say that it is a nice interpretetion of OpenOffice.org Calc; Microsoft Excel doesn’t do the same also on my Laptop.
      (0) 
  2. Guillaume GARCIA
    Hi Sergio,

    I tried your approach (really interesting, I was trying to push it to its limits !) with the standard SHOWCOLO program.

    Steps:
    1) Run the SHOWCOLO program
    2) Export the list as Local File (HTM)
    3) Opens it in Ms Excel

    The result is quite OK but the colors are not the same than in the Internet Explorer browser.

    I also tried this with a SmartForms of mine (SmartForms supports HTML output – cf. SF_WEBFORM_04) and the result is worse. The CSS seems to be totally ignored

    Do you know a resource somewhere where we can find information about compatibility of CSS for Excel ?

    Thanks in advance.

    (0) 
    1. Sergio Ferrari Post author
      Hi Guillaume, unfortunately I do not know a good resource.
      One idea is to see how excel save to html.
      I mean:
      1) Run the SHOWCOLO program
      2) Export the list as Local File (HTM)
      3) Opens it in Ms Excel
      4) change to yellow the background colour of a cell
      5) save the changed file from excel (as HTML)
      6) open in notepad and study the generated html

      Standard: “nice word but nobody care about it”…

      (0) 
        1. Sergio Ferrari Post author
          yes Mansi, it is possible.
          To be honest I don’t see the point, the issue normally is to build up the file not to download it. It can be downloaded in so many ways, it depends on the interaction model you adopt.
          Sergio
          (0) 
  3. Victor Figueroa
    Hi, good blog.

    You can help me in some idea for Write SAP ZTable with Microsoft Excel, i need define Excel Front-End and Macro VBA for call RFC and Insert datas in ZTABLE.

    Thank you

    (0) 
  4. Alvaro Tejada Galindo
    Sergio:

    I had read your blog before…But after my Excel OLE and ABAP one, I came to realize its contents…Really awesome approach…Very clean and can be implemented using any available language…Cool man…Very cool…

    Greetings,
    Blag.

    (0) 
    1. Sergio Ferrari Post author
      Hi Deepak,
      in this blog I just shared the idea to generate pure html tables from BSP. I didn’t presented the way to perform the generation.
      I’m sure that in SDN there are plenty of other materials already presenting how to generate any HTML data and here the idea is just to put the data flow in the HTTP response paying attention to the mimetype and to the file extension.
      The resource you found in internet is indeed presenting how to perform the most, I can suggest also the following blog by Mr.Jung http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/675. [original link is broken] [original link is broken]

      Finally I strongly suggest to adopt the new fantastic approach shared by Ivan (colleague of mine) here http://wiki.sdn.sap.com/wiki/display/ABAP/abap2xlsx.
      It is absolutely outstanding.

      Sergio

      (0) 
      1. actually the blog written in the given two links is quite a different operation.
        what i want is “when my excel file is downloaded from my bsp application,the captions(1st row line) in it should appear BOLD & COLORED to differentiate it from other records.”

        (0) 

Leave a Reply