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) |
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.
21 Comments
