Skip to Content

Downloading data into Excel with Format Options (from SAP Web Applications)

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.

/
image
21 Comments
You must be Logged on to comment or reply to a post.
  • 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

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

    • 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”…

        • 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
  • 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

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

    • 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

      • 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.”