SAP Download Excel file with MULTIPLE SHEETS through OData

[former_member216437]

Hi Experts,

I want to download the Excel file with Multiple Sheets and formatting option in OData.

Can any expert give me a solution ?

I request you to please do and test before answer here.

Best regards,

Sathish Mani

[Tomas_Buryanek]

I request you to send your budget for this task.

Best regards.

[matt]

Just to clarify Tomas' remark for you. There is a difference between asking for assistance, and demanding people do work for you. Your question as phrased can easily be interpreted as the latter.

[former_member216437]

Hi Gregor Wolf/Matthew Billingham/Tomas Buryanek,

As per your guidance, I have downloaded Excel with multiple worksheet through odata-get_Stream Method using ABAP2XLSX.

DATA: lv_file TYPE xstring.

lv_file = lo_excel_writer->write_file( lo_excel ).

ls_stream-value = lv_file.

copy_data_to_ref( EXPORTING is_data = ls_stream CHANGING cr_data = er_stream ).

Thank you all for your support.

Best Regards,

Sathish Mani

[gregorw · Accepted solution]

Don't know of a solution for a direct OData Service. But you could try abap2xlsx.

[former_member216437 · Accepted solution]

Hi Gregor Wolf,

Thanks for response.

Actually the requirement is download multiple sheets in a excel file through OData..

As mentioned in abap2xslx , one sheet for in a excel file . Please check attached screenshot from abap2xslx.screenshot-2018-08-13-22-52-05-620-comandroidchrom.png.

Even I gone through most of the blogs ..

For more information about issues please check this link

https://answers.sap.com/questions/355466/excel-in-sapui5.html

Best Regards,

Sathish Mani

[gregorw · Accepted solution]

What's about packing them into a ZIP file? Or use one Excel file with multiple Worksheets? There is no download multiple files at once functionality in browsers.

[former_member216437 · Accepted solution]

Hi Gregor Wolf,

I want to download only one Excel file with multiple worksheets. So zip file not required.

How to download one Excel file with multiple worksheets in OData with formatting?...

I have tried some thing like OLE, XML but I doesn't work..

Could you provide me a possible idea to do that?

Thanks in advance.

Best Regards,

Sathish Mani

[matt · Accepted solution]

So the limit of one worksheet for one Excel file no longer exists?

[gregorw · Accepted solution]

Then abap2xlsx is the right solution for you. You create the Excel with multiple worksheets on the server and and get it to the client using a $value OData request.

[gregorw · Accepted solution]

You mean in abap2xlsx?

[matt · Accepted solution]

Yes

........

[gregorw · Accepted solution]

I think that wasn't even a limitation in the initial release. Perhaps ivan.femia3 knows.

[Tomas_Buryanek · Accepted solution]

Did you tried these ABAP2XLSX demo programs:

zdemo_excel23 - Multiple sheets with and w/o grid lines, print options

zdemo_excel24 - Multiple sheets with different default date formats

[former_member216437 · Accepted solution]

do you have any examble abap2xslx code snippet for get stream ?

Also why they give limitations in abab2xslx if possible multiple worksheets in a excel file .

[gregorw · Accepted solution]

The blog post abap2xlsx – Generate your professional Excel spreadsheet from ABAP is from 2010. A lot has changed since then. Simply try out the demo report zdemo_excel3 which uses lo_excel->add_new_worksheet( ).

[former_member216437 · Accepted solution]

Hi Gregor,

How to convert lo_excel as xstring to pass get stream ?

Here we need to pass the value as xstring.

lo_excel is an object.

How to convert lo_excel to xstring.

ls_stream-value = ?

copy_data_to_ref( EXPORTING is_data = ls_stream CHANGING cr_data = er_stream ).

[matt · Accepted solution]

The OP's screenshot came from this blog https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/ where it is written

What are the limits again?

• Only one sheet for each workbook
• No graphs
• No conditional formatting
• HTML extension is not associated by default with MS Excel
• No excel advanced features

However, the OP has failed to notice that this limitation list refers to Sergio Ferarri's solution, not abap2xlsx!.

[matt · Accepted solution]

The limitations refer to this solution

https://blogs.sap.com/2006/06/11/downloading-data-into-excel-with-format-options-from-sap-web-applic...

NOT abap2xlsx. Try reading the entire blog https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/ Ivan Femia goes on to say that Sergio Ferrari's solution having the limitations you listed was the driver for him initiating abap2xlsx where there would be no such limitations.

[former_member216437 · Accepted solution]

Hi Mathew,

ya i have read ... But still i am having some doubts steaming

How to convert lo_excel as xstring to pass get stream ?

Here we need to pass the value as xstring.

lo_excel is an object.

How to convert lo_excel to xstring.

ls_stream-value = ?

copy_data_to_ref( EXPORTING is_data = ls_stream CHANGING cr_data = er_stream ).

Best Regards,

Sathish Mani

[matt · Accepted solution]

Look at the example programs. Hint: you need an instance of a writer class.

[gregorw · Accepted solution]

I think SAPCRMa2xExport shows a nice example how to get the excel as xstring.