# How to export excel file with template

Sometimes we may need to export a document in the form of a spreadsheet (MS Excel).

But often the problem of the automatic display by MS Excel.

Before export the data. we can create MS Excel templates and upload to SAP then run template later.

1) Prepare the MS Excel template

2) Upload to SAP with transaction OAOR

Class Name -> SOFFICEINTEGRATION

Class Type -> OT

3) Click tab Create then expand all and double click to Table template

4) Chose MS Excel Template

5) Fill Description (Use in ABAP Program)

6) Check MS Excel template

7) ABAP Program

perform genarate_excel_with_template tables lt_excel

using ‘ExcelTest’

‘C:\text.xls’.

*&———————————————————————*

*&      Form  genarate_excel_with_template

*&———————————————————————*

form genarate_excel_with_template tables pt_excel    type standard table

using pv_template type bds_propva

pv_file     type localfile.

data: l_iref_template    type ref to cl_bds_document_set,

l_oref_container   type ref to cl_gui_custom_container,

l_iref_control     type ref to i_oi_container_control,

l_iref_error       type ref to i_oi_error,

l_iref_document    type ref to i_oi_document_proxy,

l_retcode          type soi_ret_string.

data: lt_signature       type sbdst_signature,

lw_signature       type bapisignat,

lt_uri             type sbdst_uri,

lw_uri             type bapiuri,

lt_sheet           type soi_sheets_table,

lw_sheet           type soi_sheets.

data: lt_fields          type standard table of rfc_fields,

lv_last_row        type i,

lv_last_col        type i.

call method c_oi_container_control_creator=>get_container_control

importing

control = l_iref_control

retcode = l_retcode.

check l_retcode = c_oi_errors=>ret_ok.

call method l_iref_control->init_control

exporting

r3_application_name      = pv_template

inplace_enabled          = ‘X’

inplace_scroll_documents = ‘X’

parent                   = l_oref_container

importing

retcode                  = l_retcode.

check l_retcode = c_oi_errors=>ret_ok.

create object l_iref_template.

lw_signatureprop_name  = ‘DESCRIPTION’.

lw_signatureprop_value = pv_template.

append lw_signature to lt_signature.

refresh lt_uri.

call method l_iref_template->get_with_url

exporting

classname                = ‘SOFFICEINTEGRATION’

classtype                = ‘OT’

object_key               = ‘SOFFICEINTEGRATION’

changing

uris                     = lt_uri

signature                = lt_signature

exceptions

nothing_found            = 1

error_kpro               = 2

internal_error           = 3

parameter_error          = 4

not_authorized           = 5

not_allowed              = 6.

clear lw_uri.

read table lt_uri into lw_uri index 1.

check sysubrc = 0.

call method l_iref_control->get_document_proxy

exporting

document_type            = ‘Excel.Sheet’

importing

document_proxy           = l_iref_document

retcode                  = l_retcode.

check l_retcode = c_oi_errors=>ret_ok.

call method l_iref_document->open_document

exporting

document_url = lw_uriuri

open_inplace = ‘X’

importing

retcode      = l_retcode.

check l_retcode = c_oi_errors=>ret_ok.

free l_iref_error.

importing

error           = l_iref_error

importing

sheets          = lt_sheet

error           = l_iref_error.

check l_iref_error->error_code = c_oi_errors=>ret_ok.

clear lw_sheet.

read table lt_sheet into lw_sheet index 1.

check sysubrc = 0.

exporting

name            = lw_sheetsheet_name

importing

error           = l_iref_error.

check l_iref_error->error_code = c_oi_errors=>ret_ok.

refresh lt_fields.

call function ‘DP_GET_FIELDS_FROM_TABLE’

tables

data   = pt_excel

fields = lt_fields.

lv_last_row = lines( pt_excel ).

lv_last_col = lines( lt_fields ).

exporting

left    = 1

top     = 2

rows    = lv_last_row

columns = lv_last_col.

exporting

columns = lv_last_col

rows    = lv_last_row

name    = pv_template.

exporting

data_table   = pt_excel[]

fields_table = lt_fields

rangename    = pv_template.

call method l_iref_document->save_as

exporting

file_name    = pv_file.

call method l_iref_document->release_document

importing

retcode = l_retcode.

l_iref_document.

call method l_iref_control->release_all_documents.

call method l_iref_control->destroy_control.

message ‘Generate Excel file’ type ‘S’.

endform.                    ” genarate_excel_with_template

You must be Logged on to comment or reply to a post.
• Very Much Helpful Document ℹ , Thanks For Sharing With Screen Shots 🙂 🙂 .

Reagrds,
Seshu.P

• Hi  Budtawan Vu

This article looks interesting, I’m not an SAP ABAP developer, just a SAP user that specifies new functions and tools for our SAP .
I have one question that seems like it may be related to this somehow.

Our development team has exported an excel with many lines for the user, the idea is that the user will modify some of the values in that excel then upload that excel back into SAP to update those new values in SAP. that works OK.

my question is,  Is it possible to export an excel that already includes working formulas in the excel.

e.g. if the exported excel contains a column with Quantity and user inputs a Price in each cell in excel,  then if such formualrs are actively working in the excel then it could immediately calculate the total amount = QTY x Price  and show this value  in the excel before the user uploads the new price data to SAP.

The idea is,  that this would allow the user to do some basic and easy sanity checking on the data before uploading

Of course the user could add in the formulas himself after the download but this is more tricky, because the real formulas used are quite complicated and the users are, lets say, very busy with other tasks and not skilled with writing excel formulas

… any ideas that I could pass on to our development team, so far they haven’t found a way to add in working formulas ?
Thanks

• Nice….

• cool !!!

• Excellent !!!

• Great Job !, Thanks for share.

Regards,

Danai P

• Nice article….
Thanks for sharing…… 😎

Regards

Jesse

• Hello Budtawan,

would that example work in background, if not what would you suggest to read a excel template fill it and export in background mode?

Thanks

Marcos

• SOFFICEINTEGRATION works only in FG mode, so you need ABAP2XLSX to use template in BG mode. You’ll find many examples on the web.

• Hello Lukasz,

Thank you for your reply. I found one of your example to generate excel in background it really worked 🙂 .

ABAP2XLSX uses OLE which is GUI dependent. I dont think we can use it for background.

• ABAP2XLSX does not use OLE but xml files are written or read directly so you can use it in backgroud easily, especially if you want to use some templates, then this would be good choise to use it.

• As Łukasz Pęgiel already mentioned, abap2xlsx doesn’t use OLE2 approach but it creates pure XML file accordingly to the OOXML standard used by most of the suites (open office, excel, etc).

It can run in background, foreground, in parallel processing and moreover is open sourced.

Have a try http://www.abap2xlsx.org

• Very useful thanks Tomas.

• Fantastic article! Many Thanks!

• Hi,

many thanks for this coding!

I have implemented it into our system but while processing

exporting

data_table   = pt_excel[]

fields_table = lt_fields

rangename    = pv_template.

a short dump with system_failure is provided.

This is raised whith the message: CPIC-CALL: ‘ThSAPOCMINIT’ : cmRc=17 thRc=223

Do you have any idea why this dosn’t work?

• Thanks so much for sharing.
But i occur error