cancel
Showing results for 
Search instead for 
Did you mean: 

Convert xls_xml to plain xls (excel)

Former Member
0 Kudos

In BI a query is broadcasted to the portal in output form xml (Excel) and format xls_xml.

When you look at the file with notepad it starts with

MIME-Version: 1.0

X-Document-Type: Worksheet

Content-Type: multipart/related; boundary="NEXTMIME"

When opening the file in Excel it takes a long time since the XML has to be interpreded/converted? so it looks like a normal excel file.

My scenario is:

1) Retrieve xls_xml file from Portal

2) Convert this file to a normal xls file using SAP PI ( or other method if available).

3) Store file in a windows directory so the user can opening the excel fast from a windows directory. The user does not really require the file to be on the portal.

Steps 1 and 3 should be no problem.

But how can I convert the file to a normal excel file?

Any suggestion for conversion(SAP PI, Abap function modules) is welcome. Or maybe it is even possible to create a normal excel file in SAP BI already altoiugh it is not an option in the bex broadcaster?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Thomas,

please refer to my wiki on generation of Excel with SAP.

For your particular problem, you might consider the use of the Java Excel API which is described in this article.

http://wiki.sdn.sap.com/wiki/x/2wj8Cw

However, this solution is not too simple but you at least have the option to convert xls_xml into native xml directly in PI.

You could also forward the xls_xml to ABAP backend, but there you fill face the problem that most methods do not support background processing because they base on OLE.

Best regards,

Markus

Former Member
0 Kudos

Hi,

This could be a tough task, depending on the complexity of the transformation process (I dunno much about xml_xls) ... Maybe some APIs already exist for that ? Did you google that ?

Maybe Apache POI : http://www.javaworld.com/javaworld/jw-10-2006/jw-1019-xmlexcel.html

Rgds

Chris

Former Member
0 Kudos

Hi Chris,

thanks for your answer, but it will not help me (but can be handy in future for other projects)..

The description (as it is also in SAP BI) suggests XML is used. But in fact it is a hybrid.

It is HTML with a tag <XML> embedded with some info inside this tag like document property.

The real excel data follows after the <XML> tag and consists of normal HTML

<Table>, <TR> and <TD> tags.

An explanation of the format is written on http://support.microsoft.com/kb/271572

It says:

Excel can persist storage information in a format that is an HTML/XML hybrid. When you save an Excel 2000 workbook as a Web page, Excel creates a file that contains a combination of HTML tags and XML tags that have special meaning to Excel. A workbook saved in this format can go from Excel to the browser and back to Excel again (a "round trip") without losing workbook integrity.

When you open a workbook saved as a Web page in Internet Explorer, the HTML tags are used to render the document. When you open a workbook in this format in Excel, Excel uses the XML tags for settings that might pertain to the workbook, worksheets, rows and columns. For more information on using HTML and XML with Excel 2000, see "Microsoft Office HTML and XML Reference" at the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/Aa155477(office.10).aspx (http://msdn2.microsoft.com/en-us/library/Aa155477(office.10).aspx).

SInce the reports I have to convert are more or less stable and formatting is not really required I think I will just read the HTML file and extract the data in the Table tags.

Then write it to Excel using the jexcelapi (http://jexcelapi.sourceforge.net/)