Atributos de la clase:
- FILENAME: Nombre del fichero, con su ruta. Si existe se abre, si no existe se crea
- OPENED: Verdadero si se abre el fichero, falso si no es así (se crea porque no existe y no se puede abrir)
- FILELENGTH: Peso del fichero (si se abre)
- FILEHEADER: Cabecera del documento
- XLS_OBJECT: Objeto
- T_RECORDS: Registros
- T_SHEETS: Hojas del fichero. El typo TYT_SHEETS se crea pulsando en "Entrada de tipo directa". Es una tabla standard de strings.
types:
tyt_sheets TYPE STANDARD TABLE OF string .Métodos
CONSTRUCTOR
Constructor de la clase. Recibe el nombre del fichero (Parámetro import PI_FILENAME) y modifica sus atributos
METHOD constructor.
filename = pi_filename.
CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file = filename
RECEIVING
result = opened
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
wrong_parameter = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc EQ 0 AND opened EQ abap_true.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = filename
filetype = 'BIN'
* HAS_FIELD_SEPARATOR = ' '
* HEADER_LENGTH = 0
* READ_BY_LINE = 'X'
* DAT_MODE = ' '
* CODEPAGE = ' '
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* CHECK_BOM = ' '
* VIRUS_SCAN_PROFILE =
* NO_AUTH_CHECK = ' '
IMPORTING
filelength = filelength
header = fileheader
TABLES
data_tab = t_records
* CHANGING
* ISSCANPERFORMED = ' '
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc <> 0.
* Implement suitable error handling here
"raise
ENDIF.
* Convierte datos binarios a XString
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = filelength
IMPORTING
buffer = fileheader
TABLES
binary_tab = t_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc EQ 0.
* Obtiene objeto Excel del XString
TRY .
xls_object = NEW cl_fdt_xl_spreadsheet(
document_name = filename
xdocument = fileheader ) .
CATCH cx_fdt_excel_core.
ENDTRY .
xls_object->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = t_sheets ).
ELSE.
"Raise exception
ENDIF.
ELSE. "Fichero no existe, se crea
ENDIF.
ENDMETHOD.
GET_EXCEL_SHEETS
Obtiene las hojas del fichero.
METHOD get_excel_sheets.
me->xls_object->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = t_sheets ).
ENDMETHOD.
GET_SHEET_CONTENT_BY_IDX
Obtiene las celdas de una hoja pasada como índice (1, 2...)
METHOD get_sheet_content_by_idx.
DATA:
lv_rows TYPE i,
lv_cols TYPE i.
DATA:
ls_sheet TYPE string.
CHECK t_sheets[] IS NOT INITIAL.
TRY.
READ TABLE t_sheets INTO ls_sheet INDEX pi_sheet_index.
ENDTRY.
IF ls_sheet IS NOT INITIAL.
* DATA(lo_data_ref) =
me->xls_object->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
EXPORTING
worksheet_name = ls_sheet
RECEIVING
itab = pt_content ).
ENDIF.
"Núm filas y columnas
IF pt_content IS NOT INITIAL.
me->get_rows_and_cols(
EXPORTING
pt_content = pt_content
IMPORTING
pe_columns = pe_columns
pe_rows = pe_rows
).
ENDIF.
ENDMETHOD.
El contenido de la hoja se puede obtener en un report de varias formas, por ejemplo nos declaramos un tipo de fichero con tantos strings como columnas vayamos a leer; la variable GO_EXCEL es de tipo referenciado a la clase
TYPES: BEGIN OF ty_file,
a TYPE string, "ebeln
b TYPE string, "ebelp
c TYPE string, "knttp
d TYPE string, "Elemento de imputación
e TYPE string, "srvpos
END OF ty_file,
tyt_file TYPE STANDARD TABLE OF ty_file.
DATA:
gv_columns TYPE i,
gt_file TYPE tyt_file,
gs_file TYPE ty_file,
gt_content TYPE REF TO data.
FIELD-SYMBOLS:
<itab> TYPE STANDARD TABLE.
CREATE OBJECT go_excel
EXPORTING
pi_filename = gv_filename.
go_excel->get_sheet_content_by_idx(
EXPORTING
pi_sheet_index = 1
IMPORTING
pe_columns = gv_columns
RECEIVING
pt_content = gt_content
).
"El núm. de campos debe ser 5
IF gv_columns EQ 5
AND sy-subrc EQ 0.
ASSIGN gt_content->* TO <itab>.
IF <itab> IS ASSIGNED.
gt_file[] = <itab>[].
"Si hay línea de cabecera, la borramos
IF gt_file[] IS NOT INITIAL.
DELETE gt_file INDEX 1.
ENDIF.
ENDIF.
GET_ROWS_AND_COLS
Obtiene el número de filas y columnas de una hoja
METHOD get_rows_and_cols.
FIELD-SYMBOLS:
<itab> TYPE STANDARD TABLE,
<wa> TYPE any,
<field> TYPE any.
IF pt_content IS NOT INITIAL.
ASSIGN pt_content->* TO <itab>.
DESCRIBE TABLE <itab> LINES pe_rows.
ENDIF.
IF <itab> IS ASSIGNED.
* ASSIGN <itab> TO <wa>.
READ TABLE <itab> ASSIGNING <wa> INDEX 1.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <wa> TO <field>.
IF sy-subrc EQ 0.
ADD 1 TO pe_columns.
ELSE.
EXIT.
ENDIF.
ENDDO.
ENDIF.
ENDMETHOD.