Technical Articles
Formatted excel using XML
In every business, it is necessary to convey data within or outside of SAP. When we think about exporting data from SAP to a non-SAP system, the very first option that comes in mind is excel. Because, it is a fast and easy way but the only drawback is, it gives plain text without color, alignment, and style. So in this blog post, you are going to learn, how to generate a formatted excel file.
Steps 1:
Create your own excel file in Microsoft-Excel and write content as per your requirement. Like, heading, columns, row data, footer and apply alignment, color, formula and so on.
Note: I have used all dummy data based on materials and plants. so you can understand how it works and you can refer or enhance it as per your requirement. This example does not follow any business process.
(Screenshot – 1)
Note: Keep less row-data so after converting this file to .XML, it will be easy to use in the report.
Steps 2:
Save an Excel file with “XML Spreadsheet 2003”. ( Save As )
(Screenshot – 2)
Steps 3:
Create a report in SAP using SE38 as bellow.
*&---------------------------------------------------------------------*
*& Report ZTEST1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ztest1.
INCLUDE ztest1_top.
INCLUDE ztest1_ss.
INCLUDE ztest1_cl_def.
INCLUDE ztest1_cl_impl.
START-OF-SELECTION.
CLEAR : _obj.
CREATE OBJECT _obj. " OBJECT OF ZCL_EXCEL CLASS.
_obj->_header( ). " HEADER, METHOD TO ADD TITLE AND DYNAMIC COLUMNS IN EXCEL.
_obj->_body( ). " BODY, METHOD TO ADD DYNAMIC ROW DATA IN EXCEL.
_obj->_footer( ). " FOOTER, METHOD TO ADD TOTAL OF ROW DATA IN EXCEL.
_obj->_download( ). " DOWNLOAD, METHOD THAT ASK USER TO SELECT LOCATION TO DOWNLOAD AND OPEN EXCEL.Steps 4:
Double click on ztest1_top and write code as bellow,
*&---------------------------------------------------------------------*
*& Include ZTEST1_TOP
*&---------------------------------------------------------------------*
TYPE-POOLS: slis.
TABLES : mara,t001w,sscrfields.
CLASS zcl_excel DEFINITION DEFERRED. " DECLARE CLASS AS DEFERRED.
TYPES : BEGIN OF plant,
werks TYPE werks_d,
END OF plant,
BEGIN OF mat,
matnr TYPE matnr,
END OF mat.
DATA : iplant TYPE TABLE OF plant,
wplant TYPE plant.
DATA : imat TYPE TABLE OF mat,
wmat TYPE mat.
DATA : _obj TYPE REF TO zcl_excel. " DECLARE zcl_excel OBJECT
DATA : th TYPE string VALUE ''. " STRING VARIABLE TO STORE 'N' COLUMNS.
DATA : td TYPE string VALUE ''. " STRING VARIABLE TO STORE 'N' ROWS
DATA : randn TYPE i. " INTEGER TO STORE RANDOM NUMBER (DUMMY NETWR)
DATA : col_n(5), " TOTAL NO OF COLUMNS
row_n(5). " TOTAL NO OF ROWS.
DATA: ld_filename TYPE string, " EXCEL FILENAME
ld_path TYPE string, " PATH
ld_fullpath TYPE string, " FULL PATH WHERE EXCEL STORED IN DRIVE
ld_result TYPE i.
DATA : oref TYPE REF TO cx_root.
DATA xml_tab TYPE STANDARD TABLE OF string. " INTERNAL TABLE THAT USED TO ASSIGN XML STRING TO FMSteps 5:
Double click on ztest1_ss and write code as bellow,
*&---------------------------------------------------------------------*
*& Include ZTEST1_SS
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS : s_matnr FOR mara-matnr , " N ROWS
s_plant FOR t001w-werks. " M COLS
SELECTION-SCREEN END OF BLOCK b1.Steps 6:
Double click on ztest1_cl_def and write code as bellow,
*&---------------------------------------------------------------------*
*& Include ZTEST1_CL_DEF
*&---------------------------------------------------------------------*
CLASS zcl_excel DEFINITION.
PUBLIC SECTION.
DATA : _excel TYPE string. " STRING VARIABLE TO STORE ENTIRE XML DATA
METHODS : constructor, " DECLARE EXCEL DOCUMENT,WORKSHEET,STYLES.....
_header, " HEADER, METHOD TO ADD TITLE AND DYNAMIC COLUMNS IN EXCEL.
_body, " BODY, METHOD TO ADD DYNAMIC ROW DATA IN EXCEL.
_footer, " FOOTER, METHOD TO ADD TOTAL OF ROW DATA IN EXCEL.
_download. " DOWNLOAD, METHOD THAT ASK USER TO SELECT LOCATION TO DOWNLOAD AND OPEN EXCEL.
ENDCLASS.Steps 6:
Double click on ztest1_cl_impl and write code as bellow,
*&---------------------------------------------------------------------*
*& Include ZTEST1_CL_IMPL
*&---------------------------------------------------------------------*
CLASS zcl_excel IMPLEMENTATION.
METHOD constructor. " OPEN DEMO.XML IN NOTEPAD AND COPY TILL HEADING TAG (<XML><WORKBOOK><STYLES></STYLES><WORKFHEET><TABLE><ROW>HEADING</ROW>)
***XML STARTING TAGS + STYLES
CONCATENATE
'<?xml version="1.0" encoding="UTF-16"?>'
' <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" '
' xmlns:o="urn:schemas-microsoft-com:office:office" '
' xmlns:x="urn:schemas-microsoft-com:office:excel" '
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" '
' xmlns:html="http://www.w3.org/TR/REC-html40"> '
' <Styles> '
' <Style ss:ID="Default" ss:Name="Normal">'
' <Alignment ss:Vertical="Bottom"/>'
' <Borders/>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>'
' <Interior/>'
' <NumberFormat/>'
' <Protection/>'
' </Style>'
' <Style ss:ID="m2145582194624">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="28" ss:Color="#000000"/>'
' <Interior ss:Color="#808080" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="m2145582194644">'
' <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"'
' ss:Bold="1"/>'
' <Interior ss:Color="#808080" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s16">'
' <Alignment ss:Vertical="Center"/>'
' </Style>'
' <Style ss:ID="s17">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#FFD966" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s18">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s19">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#A5C480" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s20">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#ACB9CA" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s21">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#88B157" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s22">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"'
' ss:Bold="1"/>'
' <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s23">'
' <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#FFE9A3" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s24">'
' <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#FFE9A3" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s25">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s26">'
' <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#A5C480" ss:Pattern="Solid"/>'
' </Style>'
' <Style ss:ID="s27">'
' <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>'
' <Borders>'
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'
' </Borders>'
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
' <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
' </Style>'
' </Styles>'
' <Worksheet ss:Name="DEMO">'
' <Table ss:ExpandedColumnCount="@cols" ss:ExpandedRowCount="@rows" x:FullColumns="1"'
' x:FullRows="1" ss:DefaultRowHeight="14.4">'
' <Column ss:Width="22.200000000000003"/>'
' <Column ss:AutoFitWidth="0" ss:Width="76.8"/>'
' <Column ss:AutoFitWidth="0" ss:Width="145.79999999999998"/>'
' <Column ss:Width="35.4" ss:Span="4"/>'
' <Row ss:AutoFitHeight="0" ss:Height="27">'
' <Cell ss:MergeAcross="2" ss:StyleID="m2145582194624"><Data ss:Type="String">Heading</Data></Cell>'
' </Row>'
INTO _excel.
***XML STARTING TAGS + STYLES
ENDMETHOD.
METHOD _header. " COPY FIRST 3 COLUMN BECAUSE ITS STATIC
th = |<Row ss:StyleID="s16">|.
th = |{ th }<Cell ss:StyleID="s20"><Data ss:Type="String">Sr.#</Data></Cell>|.
th = |{ th }<Cell ss:StyleID="s21"><Data ss:Type="String">Material #</Data></Cell>|.
th = |{ th }<Cell ss:StyleID="s20"><Data ss:Type="String">Material Description</Data></Cell>|.
***********************************************************************************************************
" IF THERE IS ONLY ONE PLANT OR NO PLANT IN YOUR SYSTE, IN THAT CASE YOU CAN COMMENT THIS SELECT QUERY
" AND PASS MULTIPLE DUMMY PLANTS IN SELECT-OPTIONS LIKE P001,P002,P003 UPTO N SO THAT YOU CAN UNDERSTAND THE PROCESS.
" MAKE SURE IF YOU ARE COMMENTING QUERY, REPLACE "wplant-werks" TO "wplant-low".
***********************************************************************************************************
SELECT werks FROM t001w INTO TABLE iplant WHERE werks IN s_plant.
LOOP AT iplant INTO wplant.
th = |{ th }<Cell ss:StyleID="s17"><Data ss:Type="String">{ wplant-werks }</Data></Cell>|.
CLEAR wplant.
ENDLOOP.
th = |{ th }</Row>|.
_excel = |{ _excel } { th }|.
ENDMETHOD.
METHOD _body.
***********************************************************************************************************
" IF THERE IS NO MATERIALS IN YOUR SYSTE, IN THAT CASE YOU CAN COMMENT THIS SELECT QUERY
" AND PASS MULTIPLE DUMMY MATERIALS IN SELECT-OPTIONS LIKE 1,2,3,4 UPTO N SO THAT YOU CAN UNDERSTAND THE PROCESS.
" MAKE SURE IF YOU ARE COMMENTING QUERY, REPLACE "wmat-matnr" TO "wmat-low".
***********************************************************************************************************
SELECT matnr FROM mara INTO TABLE imat WHERE matnr IN s_matnr.
LOOP AT imat INTO wmat.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = wmat-matnr
IMPORTING
output = wmat-matnr.
td = |{ td }<Row>|.
td = |{ td }<Cell ss:StyleID="s18"><Data ss:Type="String">{ sy-tabix }</Data></Cell>|.
td = |{ td }<Cell ss:StyleID="s19"><Data ss:Type="String">{ wmat-matnr }</Data></Cell>|.
td = |{ td }<Cell ss:StyleID="s27"><Data ss:Type="String">Description { sy-tabix }</Data></Cell>|.
LOOP AT iplant INTO wplant.
CALL FUNCTION 'QF05_RANDOM_INTEGER' " USED THIS FM TO GENERATE RANDOM NO BETWEEN 10 TO 100 FOR DUMMY NETWR.
EXPORTING
ran_int_max = 100
ran_int_min = 10
IMPORTING
ran_int = randn
EXCEPTIONS
invalid_input = 1
OTHERS = 2.
td = |{ td }<Cell ss:StyleID="s23"><Data ss:Type="Number">{ randn }</Data></Cell>|.
CLEAR wplant.
ENDLOOP.
td = |{ td }</Row>|.
CLEAR wmat.
ENDLOOP.
_excel = |{ _excel } { td }|.
ENDMETHOD.
METHOD _footer.
DESCRIBE TABLE imat LINES row_n. " GET TOTAL NO OF ROWS BASED ON LIST OF MATERIALS
DESCRIBE TABLE iplant LINES col_n. " GET TOTAL NO OF COLS BASED ON LIST OF PLANTS
CONDENSE : row_n,col_n. " REMOVE BLANK SPACES
" TOTAL BY USING R1C1 FORMULA
_excel = |{ _excel } <Row>|.
_excel = |{ _excel } <Cell ss:MergeAcross="2" ss:StyleID="m2145582194644"><Data ss:Type="String">Total</Data></Cell>|.
LOOP AT iplant INTO wplant.
_excel = |{ _excel }<Cell ss:StyleID="s22" ss:Formula="=SUM(R[-{ row_n }]C:R[-1]C)"><Data ss:Type="Number"></Data></Cell>|.
ENDLOOP.
_excel = |{ _excel } </Row>|.
" FINALLY CLOSING ALL TAGS
CONCATENATE _excel
' </Table> '
' <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> '
' <PageSetup> '
' <Header x:Margin="0.3"/> '
' <Footer x:Margin="0.3"/> '
' <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> '
' </PageSetup> '
' <Print> '
' <ValidPrinterInfo/> '
' <HorizontalResolution>300</HorizontalResolution> '
' <VerticalResolution>300</VerticalResolution> '
' </Print> '
' <Selected/> '
' <Panes> '
' <Pane> '
' <Number>3</Number> '
' <ActiveRow>2</ActiveRow> '
' <ActiveCol>1</ActiveCol> '
' </Pane> '
' </Panes> '
' <ProtectObjects>False</ProtectObjects> '
' <ProtectScenarios>False</ProtectScenarios> '
' </WorksheetOptions> '
' </Worksheet> '
' </Workbook> '
INTO _excel.
ENDMETHOD.
METHOD _download.
" THIS FM ALLOW USER TO SELECT PATH WHERE TO STORE EXCEL FILE
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
* window_title = ' '
default_extension = 'xls'
default_file_name = 'Test_Report'
initial_directory = 'D:\'
CHANGING
filename = ld_filename
path = ld_path
fullpath = ld_fullpath
user_action = ld_result.
*<Table ss:ExpandedColumnCount="@cols" ss:ExpandedRowCount="@rows" x:FullColumns="1"' x:FullRows="1" ss:DefaultRowHeight="14.4">
row_n = row_n + 3. " + 3 MEANS, ADDING HEADING LINE, COLUMN AND TOTAL LINE SO expandedRowCount will be row_n + 3
col_n = col_n + 3. " + 3 MEANS, ADDING Sr #, Material # AND Description BECAUSE THESE ARE STATIC COLUMNS SO expandedColumnCount will be row_n + 3
" IT IS NOT NECESSARY TO + 3 EVERY TIME, IT IS COMPLETELY BASED ON YOUR STATIC AND DYANAMIC DATA COUNT.
CONDENSE : row_n,col_n.
REPLACE REGEX '@cols' IN _excel WITH col_n. " SEE <TABLE> TAG IN CONSTRUCTOR
REPLACE REGEX '@rows' IN _excel WITH row_n. " SEE <TABLE> TAG IN CONSTRUCTOR
APPEND _excel TO xml_tab. " ADD FINAL XML TO XML_TABLE.
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = ld_fullpath
filetype = 'ASC'
write_field_separator = 'X'
CHANGING
data_tab = xml_tab
EXCEPTIONS
access_denied = 15.
IF sy-subrc EQ 15.
MESSAGE 'Access denied' TYPE 'I' DISPLAY LIKE 'E'.
ELSE.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
application = 'EXCEL'
parameter = ld_fullpath.
ENDIF.
ENDMETHOD.
ENDCLASS.This is how I had enhanced.
(Screenshot – 3 )
Useful links.
- What is an XML?
- String builder to put XML into a single quote. https://codebeautify.org/string-builder
- In case, you are getting some error after downloading an excel file, you can validate dynamically generated XML code here. https://jsonformatter.org/xml-validator
I hope this example helps you to fulfill your requirements.
Do connect me if there is any issue or if I missed some points.
Thanks & Regards,
Rajesh Kalyanji Rajgor
Extremely useful, Thank you so much for sharing very useful information to generate formatted excel report.
Very useful blog Mr. Rajesh.
Have you heard of ABAP2XLSX?
https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/
https://github.com/sapmentors/abap2xlsx
This has been out for TEN YEARS now and is all about how to generate formatted spreadsheets from ABAP.
Every month during those ten years someone who has not heard of it works out a way to do something similar and posts a blog on SCN.
Hi,
You are right, Paul. I have never heard ABAP2XLSX before. Please consider this blog post as a different approach for the same. I will try to learn ABAP2XLSX and use it in the future.
Paul Hardy I request you to educate the people who are unaware through a series of blog posts. Many people find it daunting though the code is powerful. Thanks in advance for your tutorials on the same and guiding the next gen of developers
Kindest regards,
PSB
You can use an XSLT transformation instead of using hardcoding XML strings, to just pass internal table and retrieve formated XML
Hi Rajesh. Thanks for sharing. Did you try your solution with a big amount of data?
Hi Michael,
I tried with big amount of data,
The file size was 326MB. There may be an issue regarding size of string(_excel), for that we can append it to xml_tab(internal Table) like a string array.
I asked because I worked with an Excel-XML-solution back in 2007. My problem was that the amount of XML-tags in comparison to the amount of business data was quite big. So files were very big and not to handle. While looking for a solution, I discovered that modern Excel-files (xlsx) are zipped archives.
Ohh ok. so i think this approach is for a small amount of data. By the way, i am trying ABAP2XLSX library, its working fine and it also generates.XLSX file.
Can you share that program for send mail logic