Skip to Content
Author's profile photo Sergio Ferrari

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.

Assigned Tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      ... that you talk of MS Excel only but show an OpenOffice screenshot :-))

      anton

      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog Post Author
      You read really with a lot of attention...
      I'm going to post a new blog about spreadsheet compatibility...
      Author's profile photo Former Member
      Former Member
      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
      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog 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

      Author's profile photo Gregor Wolf
      Gregor Wolf
      Hello Robert,

      or if you have a PHP Server available you can try my sulution described in the Blog: Excel to CSV conversion with a PHP Web Service.

      Regards
      Gregor

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Guillaume GARCIA
      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

      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog 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.
      Author's profile photo Guillaume GARCIA
      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.

      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog 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”…

      Author's profile photo Former Member
      Former Member
      Is it possible to download the excel formatted file created using HTML Approach in background to a presentation server?
      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog 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
      Author's profile photo Former Member
      Former Member
      I don't suppose you know of a way of forcing Excel to treat a number with leading zeroes as a text field?
      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog Post Author
      And you?
      It is possibile to use the syntax:

      '0012345'

      It is not perfect and it does not support numeric formulas (eg. SUM).

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog Post Author
      Have a look to the blog of a collegue of mine (Sergio Locatelli): https://www.sdn.sap.com/irj/sdn/wiki?path=/display/snippets/read+any+sap+table+with+microsoft+excel&
      You should get the idea to write your on code...
      Sergio
      Author's profile photo Former Member
      Former Member
      I struggled a lot...but finally i got....from your blog....
      Author's profile photo Former Member
      Former Member
      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.

      Author's profile photo Former Member
      Former Member
      pardon me if i am too silly to ask this,but it would be really appreciated if u can tell me where to include the format of this table into the bsp code when i have used the code as given in the below link page.
      http://www.****************/Tutorials/BSP/Excel/Index.htm

      I want to format the text written in the downloaded excel sheet.

      Author's profile photo Sergio Ferrari
      Sergio Ferrari
      Blog 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

      Author's profile photo Former Member
      Former Member
      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."