Happy reporting with Excel III: There and back
I have started this series, because too many users around me wanted to use the Excel as their primary interface for the “SAP data access”. Briefly saying people prefer to see their data in Excel rather than in the ALV or any SAP (GUI) interface.
At the beginning I was concentrated on various ways how to output SAP data into Excel, but recently we (me and my colleagues, team credit!) were challenged to use formatted (!!) Excel files as both output and input channel.
The worries about the XML file upload
This was when the worries started. When you generate the Excel file through the XML transformation (as described in Happy reporting with Excel II.), you do that to get the formatted file and of course to have the job done in background/get the file to send it or save it as you wish.
But keep in mind it still is only a XML file with a header saying “this is the Excel file” (of course you can use XLS extension to help the Excel to understand it is “his” file), but not the correct XLS. The same approach as saving data into the HTM “format” and make somehow the Excel to understand it as a tabular data is applied here.
Then how could one upload the file back? It is not the Excel file (XLS/XLSX), it´s a XML file which mimics the Excel file. That could cause the error, couldn´t it?
But…luckily (or not, depends on your needs, read further) the scenario, where the user opens the Excel file (XML version) and have to save it as XLS before uploading it back (to “translate” the XML format to the correct XLS) was not necessary. If I were a user, I would not like it either.
Why it worked: because SAP does NOT process the file itself. The function modules and some “black magic” hidden deep there are calling Excel to read and return the data from the file. That means Excel can understand it is “his” file, convert it for internal purpose (you can debug that out yourself, that a TMP version is created during the processing) into the (probably) more comprehensive format and processes the file correctly.
Hooray, even this “forged” file can be uploaded without any problems.
To clarify the detail, where it is not so cool, that the file is being read by the Excel and not the SAP itself, is when you would like the user with no Excel installed to be able to upload the file. That, of course, is not possible because the upload function needs to call the Excel to read the file for it.
Read from the other sheets
To give you a complete set of information about uploading the data back into the SAP system, there is one last thing to say. Sometimes you need to read data from multiple sheets of the Excel file.
By default the sheet, which is active (the one you see when you open the file), is read by the function. If you want to get the data from other sheets, you have to tell the engine. How? It took me an hour to find out. I started inspecting this code snippet:
I found the wiki page from a, where the guy, who got ten points for solved questions, said: +”It’s a program to upload data and as you can see in this, it shows how to navigate between spreadsheets and cells.” +In my opinion it is not CLEAR how to navigate between the sheets and want to add my two cents here to help the future users/ readers to spare that hour.