Convert Excel File into Google Sheet Compatible Format

[vivekprabhu13]

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

[vivekprabhu13]

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

[Sandra_Rossi]

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.

[Nawanandana]

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.

[ThangaPrakash]

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.

[vivekprabhu13]

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

[Tomas_Buryanek]

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.

[Former Member]

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

[Former Member]

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