Ir al contenido principal

ABAP - Crear un archivo Excel usando OLE 

TYPE-POOLS ole2.
DATA: wf_cell_from  TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to    TYPE ole2_object,
      wf_cell_to1   TYPE ole2_object,
      wf_excel      TYPE ole2_object,   " Excel object
      wf_mapl       TYPE ole2_object,   " list of workbooks
      wf_map        TYPE ole2_object,   " workbook
      wf_worksheet  TYPE ole2_object,   " Worksheet
      wf_cell       TYPE ole2_object,   " Cell Range
      wf_cell1      TYPE ole2_object,
      wf_range      TYPE ole2_object,   " Range of cells to be formatted
      wf_range2     TYPE ole2_object,
      wf_column1    TYPE ole2_object.   " Column to be Autofit


DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.

DATA: wf_deli(1) TYPE c,            "delimiter
      wf_action TYPE i,
      wf_file TYPE string,
      wf_path TYPE string,
      wf_fullpath TYPE string.



TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table

*All the data was prepared as line type internal tables for faster
*download

DATA: int_matl  TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl   TYPE t_data1.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.

DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.

FIELD-SYMBOLS: <fs> .

DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
      gd_subrcemp TYPE i.

CONSTANTS wl_c09(2) TYPE n VALUE 09.

CLEAR wc_sheets.


DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  REFRESH: it_tabemp.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = 'Select File'
*      default_extension    = 'xls'
      default_file_name    = 'Material Details'
*      with_encoding        =
      file_filter          = '*.xls'
      initial_directory    = 'C:\'
      prompt_on_overwrite  = ' '
    CHANGING
      filename             = wf_file
      path                 = wf_path
      fullpath             = wf_fullpath
      user_action          = wf_action
*      file_encoding        =
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4
          .
  IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

  IF wf_action EQ 9.
    MESSAGE 'No File have been Selected' TYPE 'S'.
  ELSE.
    p_file = wf_fullpath.
    PERFORM create_excel.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  create_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM create_excel.
  LOOP AT it_tabemp INTO p_file.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.

* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.

* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.

  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.

  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.


*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.

  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.


  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.

*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING  1.

  PERFORM f_material_details
  TABLES int_matl
  USING  2.


  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.

  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.

    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_file.
  ENDIF.

  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.

  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.

ENDFORM.                    "create_excel
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.

  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.

ENDFORM.                    "ERR_HDL

*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_no TYPE i.

  DATA: lv_lines TYPE i,
        lv_sheet_name(50) TYPE c.

  wc_sheets = l_sheet_no.
  CASE l_sheet_no.
    WHEN 1.
      lv_sheet_name = 'Material_sheet1'.
    WHEN 2.
      lv_sheet_name = 'Material_sheet2'.
  ENDCASE.


*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.



  SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first

                                              " column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.

  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
  "in Material number


  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = lint_matl[]
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 4.
  CALL METHOD OF wf_worksheet 'Paste'.

  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.

ENDFORM.                    " f_material_details
If you only want to transfer the data to Excel like when you transfer the data from 
ALV to Excel simply use the Function Modules: 

XXL_SIMPLE_API 

If you want more modifications when you transfer it to Excel use: 

XXL_FULL_API 


Fuente: https://answers.sap.com/questions/5609371/create-excel-sheet-using-ole.html
https://www.erpgreat.com/abap/download-to-excel-with-format-border-color-cell-etc.htm
Etiquetas:

Comentarios

Publicar un comentario

Entradas más populares de este blog

A to Z of OLE Excel in ABAP 7.4

  SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics.   The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring.  There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc. Updated 16th Aug 2019  –  If you are working in non-ABAP 7.4 (below), there is complete reference program for you too. Go to the end of this article.  Thank you  Legxis  ( LeonievonK ) for the share. I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but  providing multiple tabs in the spreadsheet and formatting is quite tricky with non  OLE  method. OLE = Object Linking and Embedding The high level agenda of this article is to be the  G.O.A.T.  (please google it if you do not know the full form)  of OLE Excel Gu
Publicar un comentario
Leer más
How to add custom field in Additional B Tab for SAP Sales Order In this article we want to explain step by step how to add custom field in SAP Sales Order transaction VA01/VA02/VA03. This time Mr ABAPGurus will give the tutorial of enhancement to add custom field in SAP Sales order. In this sample we will add new custom field for comments and customer satisfaction, this data will save into separate table from SAP Standard table ( VBAK / VBAP ). SAP provided us with ADDITIONAL TAB in the sales order transaction ( VA01/VA02/VA03 ) which allow customer to add custom fields.  T he different between ADDITIONAL A and ADDITIONAL B is the ADDITIONAL A for field which already predefine in Sales Order Header ( VBAK ) fields and ADDITIONAL B is for field that freely define. This sample we will use ADDITIONAL B because we will store data from customer satisfaction into Z database table. 1. Create one Z table using SE11 SAP Transaction code. 2.Using SE38 Transaction code and ope
Publicar un comentario
Leer más

VOFM

Creating VOFM custom routine, functionality same as SAP standard routine 103 with Additional Functionality 1.    Introduction 1.1   Document Purpose 1.2    This is the document helps you to create custom routine for sales order for below specific requirement: Functional description: In case of sale order with the SD document category ‘K’ or ‘L’ or ‘H’ related to an invoice, we need to fill the fixed value date (VBKD_VALDT) with the billing reference date (VBRK_FKDAT). For example: Invoice 90001111, the billing date is 22.07.2013 If we create a sale order related to this invoice, we want the fixed value date equal to the billing reference date. As per the requirement: Sap standard routine ‘103’ triggers whenever sales order is being created with reference. Sap standard routine is configured in VOFM transaction as: Sap standard routine ‘103’ has the functionality of copying the billing document header data and billing document line item data into the
Publicar un comentario
Leer más