Startseite Beiträge getaggt mit "xco_cp_xlsx"

Schlagwort: xco_cp_xlsx

[ABAP] Excel-Daten mit XCO API (xco_cp_xlsx) lesen 

TYPES: BEGIN OF ty_xl_sheet,
         col1 TYPE string,
         col2 TYPE string,
         col3 TYPE string,
       END OF ty_xl_sheet.

TYPES: ty_it_users TYPE STANDARD TABLE OF ty_xl_sheet WITH DEFAULT KEY.

TRY.

    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                CHANGING  file_table  = it_files
                                                          rc          = lv_rc
                                                          user_action = lv_action ).

    IF lv_action = cl_gui_frontend_services=>action_ok.
* wenn Datei ausgewählt wurde
      IF lines( it_files ) > 0.
* ersten Tabelleneintrag lesen
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Excel-Datei auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING filename   = |{ it_files[ 1 ]-filename }|
                                                        filetype   = 'BIN'
                                              IMPORTING filelength = lv_filesize
                                              CHANGING  data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_xsts_xlsx) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

* Excel-Dokument
        DATA(o_xl) = xco_cp_xlsx=>document->for_file_content( lv_xsts_xlsx )->read_access( ).

* Sheet
        DATA(o_sheet) = o_xl->get_workbook( )->worksheet->at_position( 1 ).

        IF abap_true = o_sheet->exists( ).

* komplette Sheet auswählen
          DATA(o_sel_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to( )->get_pattern( ).

          DATA: it_sheet_data TYPE ty_it_users.

* Daten aus Sheet als Strings in interne Tabelle schreiben
          DATA(o_result) = o_sheet->select( o_sel_pattern
                                          )->row_stream(
                                          )->operation->write_to( REF #( it_sheet_data )
                                          )->set_value_transformation( xco_cp_xlsx_read_access=>value_transformation->string_value
                                          )->execute( ).

          IF abap_true = o_result->succeeded.
* Datenausgabe
            cl_demo_output=>write_data( it_sheet_data ).

*             HTML-Code vom Demo-Output holen
            DATA(lv_html) = cl_demo_output=>get( ).

*             Daten im Inline-Browser im SAP-Fenster anzeigen
            cl_abap_browser=>show_html( EXPORTING title       = 'Excel'
                                                  html_string = lv_html
                                                  container   = cl_gui_container=>default_screen ).

*             cl_gui_container=>default_screen erzwingen
            WRITE: space.
          ENDIF.

        ELSE.

* Fehlerbehandlung
          LOOP AT o_result->messages INTO DATA(o_msg).
            WRITE: / o_msg->get_text( ).
          ENDLOOP.

        ENDIF.
      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

Links

[ABAP] Daten einer internen Tabelle als Excel-Datei speichern (xco_cp_xlsx) 

TYPES: BEGIN OF ty_xl_sheet,
         col1 TYPE string,
         col2 TYPE string,
         col3 TYPE string,
       END OF ty_xl_sheet.

TYPES: ty_it_users TYPE STANDARD TABLE OF ty_xl_sheet WITH DEFAULT KEY.

TRY.
* leeres Excel-Dokument
    DATA(o_xl) = xco_cp_xlsx=>document->empty( )->write_access( ).

* 1. Sheet holen
    DATA(o_sheet) = o_xl->get_workbook( )->worksheet->at_position( 1 ).

    IF abap_true = o_sheet->exists( ).

* neuen Namen für Sheet setzen
      o_sheet->set_name( 'Test1' ).

* Daten
      DATA(it_sheet_data) = VALUE ty_it_users( ( col1 = 'Name' col2 = 'Adr.' col3 = 'Age' )
                                               ( col1 = 'Ulf'  col2 = 'Str. 1' col3 = '22' )
                                               ( col1 = 'Ida'  col2 = 'Str. 2' col3 = '21' ) ).

* komplette Sheet auswählen
      DATA(o_sel_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to( )->get_pattern( ).

* Daten einfügen
      DATA(o_result) = o_sheet->select( o_sel_pattern
                                      )->row_stream(
                                      )->operation->write_from( REF #( it_sheet_data )
                                      )->execute( ).

      IF abap_true = o_result->succeeded.
* Daten als XSTRING (Excel-Datei holen)
        DATA(lv_xl_xtsr) = o_xl->get_file_content( ).

* xstring -> itab (bytes)
        DATA: lv_size TYPE i.
        DATA: it_raw_data TYPE xml_rawdata.

* xstring (Bytes) -> RAW (iTab)
        cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = lv_xl_xtsr
                                        IMPORTING ex_size    = lv_size
                                                  ex_xtab    = it_raw_data ).

        IF lines( it_raw_data ) > 0.
          DATA: lv_action TYPE i.
          DATA: lv_filename TYPE string.
          DATA: lv_fullpath TYPE string.
          DATA: lv_path TYPE string.

* SaveDialog aufrufen
          cl_gui_frontend_services=>file_save_dialog( EXPORTING default_extension   = 'xlsx'
                                                                default_file_name   = 'export.xlsx'
                                                                file_filter         = |Excel-Datei (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                                prompt_on_overwrite = abap_true
                                                      CHANGING  filename            = lv_filename
                                                                path                = lv_path
                                                                fullpath            = lv_fullpath
                                                                user_action         = lv_action ).

          IF lv_action EQ cl_gui_frontend_services=>action_ok.
* iTab (bytes) -> lokale Datei
            cl_gui_frontend_services=>gui_download( EXPORTING filename     = lv_fullpath
                                                              filetype     = 'BIN'
                                                              bin_filesize = lv_size " Size ist wichtig für das korrekte Schreiben der Excel-Datei
                                                    CHANGING  data_tab     = it_raw_data ).
* lokale Datei im Excel aufrufen
* parameter muss wegen möglicher Leerzeichen im Pfad mit "" quotiert werden
            cl_gui_frontend_services=>execute( application = 'excel.exe'
                                               parameter   = |"{ lv_fullpath }"| ).
          ENDIF.

        ELSE.

* Fehlerbehandlung
          LOOP AT o_result->messages INTO DATA(o_msg).
            WRITE: / o_msg->get_text( ).
          ENDLOOP.

        ENDIF.
      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

Links