topic Re: Convert Excel File into Google Sheet Compatible Format in Application Development and Automation Discussions

Convert Excel File into Google Sheet Compatible Format

vivekprabhu13 — Fri, 13 Dec 2019 11:23:48 GMT

Hi Experts,

We just moved from using Excel to Google Sheets. So now we require all our files to open up in Google Sheets. I have gone through blogs on Google Sheet and SAP Integration using OAuth and it needs upgraded system and looks like a standard solution. All I would need is the file atleast opens up in Google Sheets when I upload it ( not directly save from SAP to Google ).

Though most files work, we have one program which downloads an ALV report using XML color formatting as per requirement.

The code snippet looks like this :

.......

* Creating a ixml Factory
l_ixml = cl_ixml=>create( ).

* Creating the DOM Object Model
l_document = l_ixml->create_document( ).

* Create Root Node 'Workbook'
l_element_root = l_document->create_simple_element_ns( name = 'Workbook' parent = l_document ).
l_element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

ns_attribute = l_document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
l_element_root->set_attribute_node( ns_attribute ).

ns_attribute = l_document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
l_element_root->set_attribute_node( ns_attribute ).

........

.........

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = xtext
IMPORTING
output_length = lv_bin_size
TABLES
binary_tab = lt_data_tab.

CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
bin_filesize = lv_bin_size
filename = lv_fullpath
filetype = 'BIN'
CHANGING
data_tab = lt_data_tab.

......

This file however does not open up in Google Sheets. I can convert this file using online converter https://cloudconvert.com/xls-to-csv to XLSX format which opens in Google Sheets along with color formatting.

Is there any way this conversion can be done in program ?

Thanks and Regards

Vivek

Re: Convert Excel File into Google Sheet Compatible Format

Sandra_Rossi — Fri, 13 Dec 2019 13:58:55 GMT

So, your code currently generates an Excel file in the old XML 2003 format (AKA XMLSS).

And the online converter you mention converts it in a special CSV format which keeps the colors...

You should search a Google Sheets documentation which explains what input format is expected, and you code the converter in ABAP.

Re: Convert Excel File into Google Sheet Compatible Format

Nawanandana — Fri, 13 Dec 2019 15:59:49 GMT

Google Drive File Stream will enable you to automatically synchronize Excel files with Google Sheets. you need to find add on to connect your files. there are few addon available to do it, just search. I cant not mention name.

Re: Convert Excel File into Google Sheet Compatible Format

ThangaPrakash — Sat, 14 Dec 2019 02:39:51 GMT

Hi Vivek Prabhu,

I could see same cloud convert has REST API API Console xls to csv

Have you tried consuming this API in ABAP and convert them?

You can use class CL_REST_HTTP_CLIENT to achieve it.

Re: Convert Excel File into Google Sheet Compatible Format

vivekprabhu13 — Mon, 16 Dec 2019 06:04:07 GMT

The cloud converter converts it into xlsx format - I am assuming this is the new format of excel. I was wondering if we can convert to xlsx format in ABAP itself. I cannot convert to CSV as CSV does not retain color formatting.

Thanks and Regards

Vivek

Re: Convert Excel File into Google Sheet Compatible Format

Tomas_Buryanek — Mon, 16 Dec 2019 08:27:10 GMT

vivekprabhu1 XLSX (Office Open XML) format is not really new. It is 13 years old. And yes you can create XLSX files with the ABAP, for example with ABAP2XLSX or also standard ALV has build in export to XLSX format. Check for example "SALV to xlsx" keywords.

Re: Convert Excel File into Google Sheet Compatible Format

Former Member — Fri, 05 Feb 2021 14:40:12 GMT

You can convert excel to google sheets However, in some cases, you would lose some functionalities when you convert an Excel to Google Sheets

Re: Convert Excel File into Google Sheet Compatible Format

Former Member — Fri, 05 Feb 2021 14:41:46 GMT

Hi there,

Have a question and need help by the community? Or looking to help others in the community by answering questions? Instead of posting into an old question thread, it is more helpful, if you create your own question or respond to a recent question. Here is how to get started:

1. Learn about asking and answering questions in SAP Community with this tutorial: https://developers.sap.com/tutorials/community-qa.html

2. To ask questions visit: https://answers.sap.com/questions/ask.html

3. Wait for a response.

That's it. Thank you!

Best regards,

Your SAP Community moderator