Problem upload .xls multiples tables

Former Member — Mon, 24 Sep 2012 19:00:42 GMT

Hello all!!

I'm trying to upload a file. Xls with multiple tables and am facing a problem only when the table has or has filters, calculations, macros, someone would have any tips on how to solve the problem?

The example problem is that the data from the second table to the data coming from the first table or the third table contains the data of all the other tables.

I'm developing based on the example code on the website below:

http://wiki.sdn.sap.com/wiki/display/Snippets/Read+multiple+sheets+of+an+Excel+file+into+SAP+through+ABAP

Or also if anyone has any other tips on how to load a. Xls with multiple tables.

Tks!

Re: Problem upload .xls multiples tables

Former Member — Mon, 24 Sep 2012 20:03:50 GMT

Example my code

REPORT ztrnr00101_ca.

DATA:

oref_container TYPE REF TO cl_gui_custom_container,

iref_control TYPE REF TO i_oi_container_control,

iref_document TYPE REF TO i_oi_document_proxy,

iref_spreadsheet TYPE REF TO i_oi_spreadsheet,

iref_error TYPE REF TO i_oi_error.

DATA:

v_document_url TYPE c LENGTH 256,

i_sheets TYPE soi_sheets_table,

wa_sheets TYPE soi_sheets,

i_data TYPE soi_generic_table,

wa_data TYPE soi_generic_item,

i_ranges TYPE soi_range_list,

gt_ranges TYPE soi_full_range_table,

gs_ranges LIKE LINE OF i_ranges.

TYPES: BEGIN OF st_plan1,

col1(30),

col2(3),

END OF st_plan1.

TYPES: BEGIN OF st_plan2,

col3(60),

col4(5),

END OF st_plan2.

DATA:

gt_plan1 TYPE TABLE OF st_plan1,

gs_plan1 LIKE LINE OF gt_plan1,

gt_plan2 TYPE TABLE OF st_plan2,

gs_plan2 LIKE LINE OF gt_plan2.

SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-001.

SELECTION-SCREEN BEGIN OF BLOCK b02 WITH FRAME TITLE text-002.

PARAMETERS: p_file TYPE localfile OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b02.

SELECTION-SCREEN BEGIN OF BLOCK b07 WITH FRAME TITLE text-007.

SELECTION-SCREEN BEGIN OF BLOCK b03 WITH FRAME TITLE text-003.

PARAMETERS: p_rows1 TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

p_cols1 TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

SELECTION-SCREEN END OF BLOCK b03.

SELECTION-SCREEN BEGIN OF BLOCK b04 WITH FRAME TITLE text-004.

PARAMETERS: p_rows2 TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

p_cols2 TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

SELECTION-SCREEN END OF BLOCK b04.

SELECTION-SCREEN BEGIN OF BLOCK b05 WITH FRAME TITLE text-005.

PARAMETERS: p_rows3 TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

p_cols3 TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

SELECTION-SCREEN END OF BLOCK b05.

SELECTION-SCREEN BEGIN OF BLOCK b06 WITH FRAME TITLE text-006.

PARAMETERS: p_rows4 TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

p_cols4 TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

SELECTION-SCREEN END OF BLOCK b06.

SELECTION-SCREEN END OF BLOCK b07.

SELECTION-SCREEN END OF BLOCK b01.

INITIALIZATION.

"...+code

START-OF-SELECTION.

"....+code

LOOP AT i_sheets INTO wa_sheets.

PERFORM: seleciona_aba.

IF wa_sheets-sheet_name EQ 'codigo'.

PERFORM: le_aba_codigo.

ELSEIF wa_sheets-sheet_name EQ 'artigo'.

PERFORM: le_aba_artigo.

ELSEIF wa_sheets-sheet_name EQ 'consulta Web'.

PERFORM: le_aba_consultaWeb.

ELSE.

PERFORM: le_aba_STIF.

ENDIF.

"Limpa linhas em branco do excel

DELETE i_data WHERE value IS INITIAL OR value = space.

"Limpa variaveis

CLEAR: i_ranges,

i_data.

ENDLOOP.

"Fecha arquivo xls

PERFORM: fecha_xls.

form SELECIONA_ABA .

CALL METHOD iref_spreadsheet->select_sheet

EXPORTING

name = wa_sheets-sheet_name

* no_flush = ' '

IMPORTING

error = iref_error

* retcode =

IF iref_error->has_failed = 'X'.

EXIT.

* call method iref_error->raise_message

* exporting

* type = 'E'.

ENDIF.

endform. " SELECIONA_ABA

form LE_ABA_CODIGO .

CALL METHOD iref_spreadsheet->set_selection

EXPORTING

top = 1

left = 1

rows = p_rows1

columns = p_cols1.

CALL METHOD iref_spreadsheet->insert_range

EXPORTING

name = 'Test'

rows = p_rows1

columns = p_cols1

no_flush = ''

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

EXIT.

ENDIF.

REFRESH i_data.

gs_ranges-NAME = 'codigo'.

gs_ranges-ROWS = p_rows1.

gs_ranges-COLUMNS = p_cols1.

APPEND gs_ranges TO i_ranges.

CALL METHOD iref_spreadsheet->get_ranges_data

EXPORTING

all = 'X'

IMPORTING

contents = i_data

error = iref_error

CHANGING

ranges = i_ranges.

endform. " LE_ABA_CODIGO

"......+code

form FECHA_XLS .

CALL METHOD iref_document->close_document

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

CALL METHOD iref_document->release_document

* EXPORTING

* no_flush = ' '

IMPORTING

error = iref_error

* retcode =

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

endform. " FECHA_XLS

topic Problem upload .xls multiples tables in Application Development and Automation Discussions

Problem upload .xls multiples tables

Re: Problem upload .xls multiples tables