Creare XLS con più spreadsheet

*&---------------------------------------------------------------------*
*& Report ZRIC
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZRIC.

include ole2incl.
data: application type ole2_object,
workbook type ole2_object,
sheet type ole2_object,
sheets type ole2_object,
cells type ole2_object.
constants: row_max type i value 256.
data index type i.

types: begin of t_data,
sheet_no type i,
col1(20) type c,
col2(20) type c,
end of t_data.
data: it_data type standard table of t_data,
wa_data type t_data.

data: g_sheet type i value 1,
g_row type i,
g_col type i.


***********************************************************************
*
*START-OF-SELECTION
start-of-selection.

perform populate_data.

create object application 'excel.application'.
set property of application 'visible' = 1.
call method of application 'Workbooks' = workbook.

call method of workbook 'Add' = workbook.
call method of workbook 'Sheets' = sheets.

loop at it_data into wa_data.

on change of wa_data-sheet_no.
if wa_data-sheet_no > 3.
call method of sheets 'Add' = sheet.
set property of sheets 'active' = 1.
set property of sheet 'Name' = wa_data-sheet_no.
endif.

call method of workbook 'worksheets' = sheet no flush
exporting #1 = g_sheet.
set property of sheet 'name' = wa_data-sheet_no.

g_sheet = g_sheet + 1.
g_row = 0.
endon.

g_row = g_row + 1. g_col = 1.
call method of sheet 'cells' = cells
exporting #1 = g_row
#2 = g_col.

set property of cells 'value' = wa_data-col1.
g_col = g_col + 1.

call method of sheet 'cells' = cells
exporting #1 = g_row
#2 = g_col.

set property of cells 'value' = wa_data-col2.
endloop.

* Save excel speadsheet to particular filename
call method of sheet 'SaveAs'
exporting #1 = 'c:\exceldoc1.xls' "filename
#2 = 1.
*ileFormat

call method of sheet 'Close'
exporting #1 = 'c:\exceldoc1.xls' "filename
#2 = 1.
if sy-subrc eq 0.
exit.
endif.

* Closes excel window, data is lost if not saved
set property of application 'visible' = 0.

*&---------------------------------------------------------------------*
*& Form populate_data
*&---------------------------------------------------------------------*
form populate_data .

data: l_sheet type n.


do 3 times.
l_sheet = l_sheet + 1.
move l_sheet to wa_data-sheet_no.

concatenate: l_sheet '-row1-col1' into wa_data-col1,
l_sheet '-row1-col2' into wa_data-col2.
append wa_data to it_data.

concatenate: l_sheet '-row2-col1' into wa_data-col1,
l_sheet '-row2-col2' into wa_data-col2.
append wa_data to it_data.

concatenate: l_sheet '-row3-col1' into wa_data-col1,
l_sheet '-row3-col2' into wa_data-col2.
append wa_data to it_data.

concatenate: l_sheet '-row4-col1' into wa_data-col1,
l_sheet '-row4-col2' into wa_data-col2.
append wa_data to it_data.

enddo.

endform. " populate_data

Importare un file xls in una tabella interna

Per porter importare un file excel in una tabella interna è possibile utilizzare la seguente funzione:

DATA: tb_excel TYPE alsmex_tabline OCCURS 0.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 2
i_begin_row = 3
i_end_col = 6
i_end_row = 10000
TABLES
intern = tb_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.

E' necessario dichiarare l'area in cui si trovano i dati.
Una volta eseguita la funzone verrà valorizzata la tabella tb_excel . Questa tabella ha una riga per ogni cella letta nel file xls.
La riga della tabella è strutturata con 3 valori: riga, colonna e valore.

Per porter splittare i dati nel modo corretto è possibile usare questo semplice loop:

FIELD-SYMBOLS (c) TYPE any.

LOOP AT tb_excel INTO wa_excel.
ASSIGN COMPONENT wa_excel-col OF STRUCTURE wa_xls TO (c) .

IF sy-subrc = 0.
MOVE wa_excel-value TO (c).
ENDIF.
AT END OF row.
APPEND wa_xls TO tb_xls.
CLEAR wa_xls.
ENDAT.
ENDLOOP.

L'oggetto tb_xls è la tabella che identifica correttamente una riga del nostro xls. Nel mio caso particolare la struttura era formata da :

DATA: BEGIN OF tb_xls OCCURS 0.
DATA: kunnr TYPE kunnr,
zkprot TYPE zkprot,
zkddef(10),
barcode TYPE bds_bar_in-barcode,
tipo .
DATA: END OF tb_xls.