*&---------------------------------------------------------------------*
*& Report YN_XLSX_TEST
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT YN_XLSX_TEST1.
SELECTION-SCREEN BEGIN OF BLOCK B01 WITH FRAME TITLE TEXT-B01.
PARAMETERS P_TABLE TYPE DD02L-TABNAME MODIF ID GP1 OBLIGATORY MEMORY ID HT.
PARAMETERS P_FILE TYPE LOCALFILE MODIF ID GP2 OBLIGATORY MEMORY ID HF.
PARAMETERS P_SQL TYPE STRING MODIF ID GP3.
SELECTION-SCREEN END OF BLOCK B01.
*&---------------------------------------------------------------------*
*& Include
*&---------------------------------------------------------------------*
CLASS LCX_CONFIGURATION DEFINITION
INHERITING FROM CX_STATIC_CHECK.
PUBLIC SECTION.
DATA LOCAL_TEXT TYPE STRING.
METHODS CONSTRUCTOR IMPORTING TEXT TYPE STRING.
ENDCLASS.
CLASS LCX_CONFIGURATION IMPLEMENTATION.
METHOD CONSTRUCTOR.
SUPER->CONSTRUCTOR( ).
LOCAL_TEXT = TEXT.
ENDMETHOD.
ENDCLASS.
*&---------------------------------------------------------------------*
*& Class lcl_configuration
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
CLASS LCL_CONFIGURATION DEFINITION.
PUBLIC SECTION.
DATA FILEFULLPATH TYPE STRING.
DATA TABLENAME TYPE STRING.
DATA SQLSCRIPT TYPE STRING.
DATA TABLEINFO TYPE TADIR.
DATA TABLESTRUCTURE TYPE REF TO CL_ABAP_STRUCTDESCR.
DATA TABLETYPE TYPE REF TO CL_ABAP_TABLEDESCR.
DATA TABLEDATA TYPE REF TO DATA.
CLASS-METHODS VALIDATE_SQL_SCRIPT
CHANGING SQLSCRIPT TYPE STRING.
CLASS-METHODS VALIDATE_TABLE
CHANGING CHECKEDTABLENAME TYPE STRING
RAISING LCX_CONFIGURATION.
METHODS CONSTRUCTOR "Constructore method
IMPORTING FILEFULLPATH TYPE STRING
TABLENAME TYPE STRING
SQLSCRIPT TYPE STRING
RAISING LCX_CONFIGURATION.
METHODS EXPORT RAISING LCX_CONFIGURATION.
PROTECTED SECTION.
METHODS GET_FILECONTENT
RETURNING VALUE(FILECONTENT) TYPE XSTRING
RAISING LCX_CONFIGURATION.
METHODS EXTRACT_DATA_FROM_TABLE
RAISING LCX_CONFIGURATION.
METHODS CHECK_FILE
RAISING LCX_CONFIGURATION.
METHODS EXTRACT_DATA_FROM_EXCEL
RAISING LCX_CONFIGURATION.
METHODS GET_TABLECONTENT
EXPORTING TABLECONTENT TYPE ANY TABLE
RAISING LCX_CONFIGURATION.
PRIVATE SECTION.
ENDCLASS.
CLASS LCL_CONFIGURATION IMPLEMENTATION.
METHOD CONSTRUCTOR.
IF FILEFULLPATH IS INITIAL OR TABLENAME IS INITIAL.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |File Name { FILEFULLPATH } and Table Name { TABLENAME } should be provided|.
ENDIF.
ME->FILEFULLPATH = FILEFULLPATH.
ME->TABLENAME = TABLENAME.
ME->SQLSCRIPT = SQLSCRIPT.
LCL_CONFIGURATION=>VALIDATE_TABLE( CHANGING CHECKEDTABLENAME = ME->TABLENAME ).
ME->TABLESTRUCTURE ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_NAME( ME->TABLENAME ).
IF NOT ME->TABLESTRUCTURE IS BOUND.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Exception occurs when parsing Table Structure for { TABLENAME } |.
ENDIF.
TRY.
ME->TABLETYPE = CL_ABAP_TABLEDESCR=>CREATE( P_LINE_TYPE = ME->TABLESTRUCTURE ).
CATCH CX_SY_TABLE_CREATION INTO DATA(TABLETYPEEXCEPTION).
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Exception occurs when parsing Table Type for { TABLENAME } |.
ENDTRY.
CREATE DATA TABLEDATA TYPE HANDLE ME->TABLETYPE.
ENDMETHOD.
METHOD EXPORT.
"Create client PC excel file from DDIC table
DATA(FILECONTENT) = ME->GET_FILECONTENT( ).
CL_SCP_CHANGE_DB=>XSTR_TO_XTAB( EXPORTING IM_XSTRING = FILECONTENT
IMPORTING EX_XTAB = DATA(FILECONTENTTAB) ).
CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD(
EXPORTING
BIN_FILESIZE = XSTRLEN( FILECONTENT )
FILENAME = |{ ME->FILEFULLPATH }|
FILETYPE = 'BIN'
CONFIRM_OVERWRITE = ABAP_TRUE
IMPORTING
FILELENGTH = DATA(BYTESTRANSFERRED)
CHANGING
DATA_TAB = FILECONTENTTAB
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
NOT_SUPPORTED_BY_GUI = 22
ERROR_NO_GUI = 23
OTHERS = 24
).
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
MESSAGE S001(00) WITH BYTESTRANSFERRED ' bytes transferred'.
ENDIF.
ENDMETHOD.
METHOD GET_FILECONTENT.
ME->EXTRACT_DATA_FROM_TABLE( ).
IF ME->TABLEDATA IS INITIAL.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Table { TABLENAME } has no entry.|.
ENDIF.
"Get file content from table
CLEAR FILECONTENT.
TRY.
DATA(XLSX_HANDLING) = CL_EHFND_XLSX=>GET_INSTANCE( ).
DATA(XLSX_DOCUMENT) = XLSX_HANDLING->CREATE_DOC( ).
DATA(XLSX_SHEETS) = XLSX_DOCUMENT->GET_SHEETS( ).
DATA(FIRST_XLSX_SHEET) = XLSX_DOCUMENT->GET_SHEET_BY_ID( XLSX_SHEETS[ 1 ]-SHEET_ID ).
FIRST_XLSX_SHEET->CHANGE_SHEET_NAME( 'Data' ).
DATA(LV_COLUMN) = 1.
LOOP AT ME->TABLESTRUCTURE->COMPONENTS REFERENCE INTO DATA(COMPONENT).
FIRST_XLSX_SHEET->SET_CELL_CONTENT( IV_ROW = 1 IV_COLUMN = LV_COLUMN IV_VALUE = COMPONENT->NAME ).
LV_COLUMN = LV_COLUMN + 1.
ENDLOOP.
DATA(LV_ROW) = 2.
FIELD-SYMBOLS <TABLEDATA> TYPE STANDARD TABLE.
ASSIGN ME->TABLEDATA->* TO <TABLEDATA>.
LOOP AT <TABLEDATA> ASSIGNING FIELD-SYMBOL(<CURRENTTABLEDATA>).
LV_COLUMN = 1.
LOOP AT ME->TABLESTRUCTURE->COMPONENTS REFERENCE INTO COMPONENT.
ASSIGN COMPONENT COMPONENT->NAME OF STRUCTURE <CURRENTTABLEDATA> TO FIELD-SYMBOL(<COLUMNVALUE>).
FIRST_XLSX_SHEET->SET_CELL_CONTENT( IV_ROW = LV_ROW IV_COLUMN = LV_COLUMN IV_VALUE = <COLUMNVALUE> ).
LV_COLUMN = LV_COLUMN + 1.
ENDLOOP.
LV_ROW = LV_ROW + 1.
ENDLOOP.
FILECONTENT = XLSX_DOCUMENT->SAVE( ).
CATCH CX_OPENXML_FORMAT INTO DATA(OPENXML_FORMAT_EXCEPTION).
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when constructing excel file instance. cx_openxml_format|.
CATCH CX_OPENXML_NOT_FOUND INTO DATA(OPENXML_NOT_FOUND_EXCEPTION).
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when constructing excel file instance. CX_OPENXML_NOT_FOUND |.
CATCH CX_OPENXML_NOT_ALLOWED INTO DATA(OPENXML_NOT_ALLOWED_EXCEPTION).
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when constructing excel file instance. CX_OPENXML_NOT_ALLOWED |.
ENDTRY.
ENDMETHOD.
METHOD GET_TABLECONTENT.
"Get table content from file
ENDMETHOD.
METHOD VALIDATE_TABLE.
"raise exception if table does not exist
SELECT SINGLE * FROM TADIR INTO @DATA(TABLEINFO) WHERE OBJ_NAME = @CHECKEDTABLENAME AND OBJECT = 'TABL'. "#EC CI_GENBUFF.
IF SY-SUBRC <> 0.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Table { CHECKEDTABLENAME } does not exist.|.
ENDIF.
TRY.
CHECKEDTABLENAME =
CL_ABAP_DYN_PRG=>CHECK_TABLE_OR_VIEW_NAME_STR(
VAL = CHECKEDTABLENAME
PACKAGES = CONV #( TABLEINFO-DEVCLASS )
INCL_SUB_PACKAGES = ABAP_TRUE
).
CATCH CX_ABAP_NOT_A_TABLE
CX_ABAP_NOT_IN_PACKAGE.
RETURN.
ENDTRY.
ENDMETHOD.
METHOD EXTRACT_DATA_FROM_TABLE.
DATA SQL_SCRIPT TYPE STRING.
DATA CHECKEDTABLENAME TYPE STRING.
SQL_SCRIPT = ME->SQLSCRIPT.
CHECKEDTABLENAME = ME->TABLENAME.
LCL_CONFIGURATION=>VALIDATE_SQL_SCRIPT( CHANGING SQLSCRIPT = SQL_SCRIPT ).
LCL_CONFIGURATION=>VALIDATE_TABLE( CHANGING CHECKEDTABLENAME = CHECKEDTABLENAME ).
FIELD-SYMBOLS <TABLEDATA> TYPE STANDARD TABLE.
ASSIGN TABLEDATA->* TO <TABLEDATA>.
IF ME->SQLSCRIPT IS INITIAL.
SELECT * FROM (CHECKEDTABLENAME) INTO TABLE <TABLEDATA>.
ELSE.
SELECT * FROM (CHECKEDTABLENAME) INTO TABLE <TABLEDATA> WHERE (SQL_SCRIPT).
ENDIF.
ENDMETHOD.
METHOD VALIDATE_SQL_SCRIPT.
IF SQLSCRIPT IS INITIAL.
RETURN.
ENDIF.
SQLSCRIPT = REPLACE( VAL = SQLSCRIPT
SUB = `'`
WITH = `''`
OCC = 0 ).
CONCATENATE `'` SQLSCRIPT `'` INTO SQLSCRIPT SEPARATED BY SPACE.
TRY.
SQLSCRIPT =
CL_ABAP_DYN_PRG=>CHECK_CHAR_LITERAL( SQLSCRIPT ).
DATA(LV_LEN) = STRLEN( SQLSCRIPT ) - 2.
SQLSCRIPT = SQLSCRIPT+1(LV_LEN).
SQLSCRIPT = REPLACE( VAL = SQLSCRIPT
SUB = `''`
WITH = `'`
OCC = 0 ).
CATCH CX_ABAP_INVALID_VALUE INTO DATA(LO_EXCEPTION).
CLEAR SQLSCRIPT.
ENDTRY.
ENDMETHOD.
METHOD CHECK_FILE.
ENDMETHOD.
METHOD EXTRACT_DATA_FROM_EXCEL.
FIELD-SYMBOLS <EXCELDATA> TYPE STANDARD TABLE.
ASSIGN ME->TABLEDATA->* TO <EXCELDATA>.
DATA(XLSXHANDLER) = CL_EHFND_XLSX=>GET_INSTANCE( ).
CHECK NOT XLSXHANDLER IS INITIAL.
TRY.
DATA(XSTRING_EXCEL) = CL_OPENXML_HELPER=>LOAD_LOCAL_FILE( ME->FILEFULLPATH ).
CATCH CX_OPENXML_NOT_FOUND INTO DATA(OPENXML_NOT_FOUND).
RETURN.
ENDTRY.
TRY.
DATA(XLSXDOCUMENT) = XLSXHANDLER->LOAD_DOC( IV_FILE_DATA = XSTRING_EXCEL ).
CATCH CX_OPENXML_FORMAT INTO DATA(OPENXML_FORMAT).
RETURN.
CATCH CX_OPENXML_NOT_ALLOWED INTO DATA(OPENXML_NOT_ALLOWED).
RETURN.
CATCH CX_DYNAMIC_CHECK INTO DATA(DYNAMIC_CHECK).
RETURN.
ENDTRY.
"extract data from first sheet
TRY.
DATA(FIRSTSHEET) = XLSXDOCUMENT->GET_SHEET_BY_ID( IV_SHEET_ID = 1 ).
CATCH CX_OPENXML_FORMAT INTO OPENXML_FORMAT.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when extract data from first sheet: CX_OPENXML_FORMAT |.
CATCH CX_OPENXML_NOT_FOUND INTO OPENXML_NOT_FOUND.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when extract data from first sheet: OPENXML_NOT_FOUND |.
CATCH CX_DYNAMIC_CHECK INTO DYNAMIC_CHECK.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Error occurs when extract data from first sheet: CX_DYNAMIC_CHECK |.
ENDTRY.
"return if no sheet in xlsx file
CHECK NOT FIRSTSHEET IS INITIAL.
"check file structure, first line of excel file
DATA(COLUMNCOUNT) = FIRSTSHEET->GET_LAST_COLUMN_NUMBER_IN_ROW( 1 ).
DATA COLUMN TYPE I VALUE 1.
"data tablecomponents type cl_abap_structdescr=>component_table.
DATA(TABLECOMPONENTS) = ME->TABLESTRUCTURE->GET_COMPONENTS( ).
DATA INVALIDCOLUMN TYPE STRING.
TYPES: BEGIN OF COLUMNINFO,
COLUMN TYPE I,
COLUMNNAME TYPE STRING,
END OF COLUMNINFO.
TYPES COLUMNSINFO TYPE STANDARD TABLE OF COLUMNINFO WITH EMPTY KEY.
DATA COLUMNFROMFILE TYPE COLUMNSINFO.
DO COLUMNCOUNT TIMES.
DATA(CELLVALUE) = FIRSTSHEET->GET_CELL_CONTENT(
EXPORTING
IV_ROW = 1
IV_COLUMN = COLUMN ).
APPEND INITIAL LINE TO COLUMNFROMFILE ASSIGNING FIELD-SYMBOL(<COLUMNFROMFILE>).
<COLUMNFROMFILE>-COLUMN = COLUMN.
<COLUMNFROMFILE>-COLUMNNAME = CELLVALUE.
IF LINE_EXISTS( TABLECOMPONENTS[ NAME = CELLVALUE ] ).
DELETE TABLECOMPONENTS WHERE NAME = CELLVALUE.
ELSE.
INVALIDCOLUMN = INVALIDCOLUMN && |,{ CELLVALUE }|.
ENDIF.
COLUMN = COLUMN + 1.
ENDDO.
DATA MISSINGCOLUMNS TYPE STRING.
LOOP AT TABLECOMPONENTS REFERENCE INTO DATA(CURRENTCOMPONENT).
MISSINGCOLUMNS = MISSINGCOLUMNS && |, { CURRENTCOMPONENT->*-NAME }|.
ENDLOOP.
IF NOT INVALIDCOLUMN IS INITIAL.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Find invalid columns: { INVALIDCOLUMN } |.
ENDIF.
IF NOT MISSINGCOLUMNS IS INITIAL.
RAISE EXCEPTION TYPE LCX_CONFIGURATION
EXPORTING
TEXT = |Columns do not exist in excel file: { MISSINGCOLUMNS } |.
ENDIF.
TABLECOMPONENTS = ME->TABLESTRUCTURE->GET_COMPONENTS( ).
DATA(ROWCOUNT) = FIRSTSHEET->GET_LAST_ROW_NUMBER( ).
DATA CURRENTROW TYPE I VALUE 2.
WHILE CURRENTROW <= ROWCOUNT.
APPEND INITIAL LINE TO <EXCELDATA> ASSIGNING FIELD-SYMBOL(<CURRENTROW>).
LOOP AT COLUMNFROMFILE REFERENCE INTO DATA(CURRENTCOLUMN).
CELLVALUE = FIRSTSHEET->GET_CELL_CONTENT(
EXPORTING
IV_ROW = CURRENTROW
IV_COLUMN = CURRENTCOLUMN->*-COLUMN ).
ASSIGN COMPONENT CURRENTCOLUMN->*-COLUMNNAME OF STRUCTURE <CURRENTROW> TO FIELD-SYMBOL(<CELLVALUE>).
<CELLVALUE> = CELLVALUE.
ENDLOOP.
CURRENTROW = CURRENTROW + 1.
ENDWHILE.
ENDMETHOD.
ENDCLASS.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
DATA(TITLE) = |Select Excel File, e.g. *.xlsx|.
DATA(DEFAULTEXTENSION) = |.xlsx|.
DATA(FILEFILTER) = `Excel Files (*.xlsx)|*.xlsx`.
DATA IT_TAB TYPE FILETABLE.
DATA RETURNCODE TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE = TITLE
DEFAULT_EXTENSION = DEFAULTEXTENSION
CHANGING
FILE_TABLE = IT_TAB
RC = RETURNCODE
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
READ TABLE IT_TAB ASSIGNING FIELD-SYMBOL(<SELECTEDFILENAME>) INDEX 1.
IF SY-SUBRC = 0.
P_FILE = <SELECTEDFILENAME>-FILENAME.
ENDIF.
START-OF-SELECTION.
TRY.
DATA(CONFIGURATIONHANDLER) = NEW LCL_CONFIGURATION( FILEFULLPATH = CONV #( P_FILE )
TABLENAME = CONV #( P_TABLE )
SQLSCRIPT = P_SQL ).
CONFIGURATIONHANDLER->EXPORT( ).
CATCH LCX_CONFIGURATION INTO DATA(CONFIGURATIONEXCEPTION).
WRITE: / CONFIGURATIONEXCEPTION->LOCAL_TEXT.
ENDTRY.
ABAP xlsx格式EXCEL文件导出(非OLE或DOI方式)
最新推荐文章于 2024-05-19 09:34:38 发布