Exportar tablas internas a Excel mediante OLE2
INCLUDE ole2incl.
TYPES: ty_data(1500) TYPE c.
DATA: lt_data TYPE ty_data OCCURS 0 WITH HEADER LINE,
lv_cont type i.
DATA: ls_cabecera TYPE ywtst_10004_01,
ls_detalle TYPE ywtst_10004_02,
ls_pallet TYPE ywtst_10004_03,
ls_calibre TYPE ywtst_10004_04.
DATA: archivo TYPE string .
DATA: path TYPE string,
fullpath TYPE string,
uact TYPE i.
* VARIABLES PARA CREAR EL ARCHIVO EXCEL
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cells TYPE ole2_object,
int TYPE ole2_object,
font TYPE ole2_object,
we TYPE ole2_object,
lo_borders TYPE ole2_object.
CONSTANTS:
c_col_black TYPE i VALUE 0,
c_col_white TYPE i VALUE 2,
c_col_red TYPE i VALUE 3,
c_col_light_green TYPE i VALUE 4,
c_col_dark_blue TYPE i VALUE 5,
c_col_yellow TYPE i VALUE 6,
c_col_pink TYPE i VALUE 7,
c_col_light_blue TYPE i VALUE 8,
c_col_brown TYPE i VALUE 9.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_column TYPE ole2_object,
lo_range TYPE ole2_object,
lo_font TYPE ole2_object,
lo_interior TYPE ole2_object.
* Theme Colours:
* Use the subrutine set_soft_colour.
CONSTANTS:
c_theme_col_white TYPE i VALUE 1,
c_theme_col_black TYPE i VALUE 2,
c_theme_col_yellow TYPE i VALUE 3,
c_theme_col_dark_blue TYPE i VALUE 4,
c_theme_col_light_blue TYPE i VALUE 5,
c_theme_col_red TYPE i VALUE 6,
c_theme_col_green TYPE i VALUE 7,
c_theme_col_violet TYPE i VALUE 8,
c_theme_col_pal_blue TYPE i VALUE 9,
c_theme_col_orange TYPE i VALUE 10.
data a type c LENGTH 2 VALUE ''''.
CREATE OBJECT application 'excel.application'.
* HACE VISIBLE LA APLICACION
SET PROPERTY OF application 'visible' = 0.
* LLAMA AL METODO WORKSBOOKS PARA CREAR EL LIBRO
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add'.
* CREA LA PRIMER HOJA
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING
#1 = 1.
* SETEA NOMBRE DE LA HOJA
SET PROPERTY OF sheet 'Name' = 'Packing'.
CALL METHOD OF sheet 'Activate'.
FIELD-SYMBOLS: <field> TYPE ANY.
READ TABLE lt_cabecera INTO ls_cabecera WITH KEY TEXTOCOL2 = 'PRODUCT'.
IF SY-SUBRC = 0.
CONCATENATE 'PACKING LIST ' ls_cabecera-VALORCOL2 INTO lt_data SEPARATED BY SPACE.
ENDIF.
APPEND lt_data. CLEAR lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_cabecera INTO ls_cabecera.
IF ls_cabecera-TEXTOCOL2 = 'TOTAL BOXES'.
REPLACE ALL OCCURRENCES OF '.' IN ls_cabecera-VALORCOL2 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_cabecera-VALORCOL2 WITH ''.
ENDIF.
CONCATENATE ls_cabecera-TEXTOCOL1
ls_cabecera-VALORCOL1
''
''
ls_cabecera-TEXTOCOL2
ls_cabecera-VALORCOL2
''
''
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
data: lv_cajas TYPE char30,
lv_total TYPE char30.
CONCATENATE 'PALLET NUMBER'
'VARIETY'
'WEIGHT/BOX'
'DESCRIPTION'
''
'LABEL'
'SIZE'
'BOXES'
'TOTAL BOXES'
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_detalle INTO ls_detalle.
WRITE ls_detalle-MIXED TO lv_cajas DECIMALS 0.
WRITE ls_detalle-MIXED TO lv_total DECIMALS 0.
CONCATENATE a+00(1) ls_detalle-EXIDV INTO ls_detalle-EXIDV.
CONCATENATE ls_detalle-EXIDV
ls_detalle-VARIETY
ls_detalle-WEIGHT
ls_detalle-DESCRIPTION
''
ls_detalle-LABEL
ls_detalle-CALIBER
lv_cajas
lv_total
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
MOVE 'PALLET DISTRIBUTION' TO lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_pallet INTO ls_pallet.
CONCATENATE a+00(1) ls_pallet-TEXTOCOL1 INTO ls_pallet-TEXTOCOL1.
CONCATENATE a+00(1) ls_pallet-TEXTOCOL2 INTO ls_pallet-TEXTOCOL2.
CONCATENATE ls_pallet-TEXTOCOL1
ls_pallet-TEXTOCOL2
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
MOVE 'QUALITY SUMMARY' TO lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_calibre INTO ls_calibre.
IF sy-tabix = 1.
CONCATENATE 'VARIETY'
'BOXES'
ls_calibre-CALIBER1
ls_calibre-CALIBER2
ls_calibre-CALIBER3
ls_calibre-CALIBER4
ls_calibre-CALIBER5
ls_calibre-CALIBER6
"ls_calibre-CALIBER7
"ls_calibre-CALIBER8
"ls_calibre-CALIBER9
"ls_calibre-CALIBER10
'TOTAL BOXES'
INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
else.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER1 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER1 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER2 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER2 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER3 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER3 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER4 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER4 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER5 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER5 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER6 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER6 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER7 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER7 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER8 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER8 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER9 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER9 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER10 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER10 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-TOTAL WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-TOTAL WITH ''.
CONCATENATE ls_calibre-VARIETY
ls_calibre-BOXES
ls_calibre-CALIBER1
ls_calibre-CALIBER2
ls_calibre-CALIBER3
ls_calibre-CALIBER4
ls_calibre-CALIBER5
ls_calibre-CALIBER6
* ls_calibre-CALIBER7
* ls_calibre-CALIBER8
* ls_calibre-CALIBER9
* ls_calibre-CALIBER10
ls_calibre-TOTAL
INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDIF.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = 3.
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab = lt_data
EXCEPTIONS
clpb_error = 1
OTHERS = 2.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 3 "Row
#2 = 2. "Column
CALL METHOD OF cells 'SELECT'.
CALL METHOD OF sheet 'PASTE'.
"Formatear cabecera de excel
DATA: lv_row TYPE i,
lv_col TYPE i,
lv_colfin TYPE i.
lv_row = 5.
lv_col = 3.
lv_colfin = lv_col + 2.
DO 30 TIMES.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_row "Fila
#2 = lv_col. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_row "Fila
#2 = lv_colfin. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = lv_row "Row
#2 = lv_col. "Column
SET PROPERTY OF cells 'HorizontalAlignment' = 1.
lv_row = lv_row + 1.
IF lv_row = 20.
lv_row = 5.
lv_col = 7.
lv_colfin = lv_col + 3.
ENDIF.
ENDDO.
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 7 "Row
* #2 = 3. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 13 "Row
* #2 = 3. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 12 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 13 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 14 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 12 "Row
#2 = 7. "Column
set property of cells 'NumberFormat' = '#,### '.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 13 "Row
#2 = 7. "Column
set property of cells 'NumberFormat' = '#,##0.00 '.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
data: lv_filas TYPE i,
lv_acumulado TYPE i.
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_filas = lv_filas + 21.
"Formatear detalle de excel
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 21 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
"Alinear centrado
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_row = 21.
lv_col = 5.
lv_colfin = 6.
lv_filas = lv_filas + 1.
DO lv_filas TIMES.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_row "Fila
#2 = lv_col. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_row "Fila
#2 = lv_colfin. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
lv_row = lv_row + 1.
ENDDO.
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_filas = lv_filas + 21.
lv_acumulado = lv_filas + 2.
"Formater listado de pallets
DESCRIBE TABLE lt_pallet LINES lv_filas.
lv_filas = lv_filas + lv_acumulado.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_acumulado "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
lv_acumulado = lv_filas + 2.
"Formater listado de calibres
DESCRIBE TABLE lt_calibre LINES lv_filas.
lv_filas = lv_filas + lv_acumulado.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_acumulado "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
set property of lo_range 'NumberFormat' = '#,### '.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 2.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 3.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 4.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 5.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 6.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 7.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 8.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 9.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 10.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 11.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 12.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 13.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 14.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 3 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 3 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '15'.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
"Subtitulos:
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 6. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 6. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 21 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 21 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'SIZE' = 11.
SET PROPERTY OF font 'BOLD' = 2.
lv_row = 5.
lv_col = 3.
DO 30 TIMES.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = lv_row "Row
#2 = lv_col. "Column
SET PROPERTY OF cells 'HorizontalAlignment' = 2. "3 = Cnetrado; 2 = Izquierda ; 1 = Derecha
lv_row = lv_row + 1.
IF lv_row = 20.
lv_row = 5.
lv_col = 7.
ENDIF.
ENDDO.
Call Method of sheet 'Columns' = LO_Column.
CALL METHOD OF LO_COLUMN 'Autofit' NO FLUSH.
Free Object LO_Column.
* GUARDA EN EL EXCEL
CONCATENATE p_vbeln '.xls' INTO archivo.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'XLS'
default_file_name = archivo
CHANGING
filename = archivo
path = path
fullpath = fullpath
user_action = uact.
SET PROPERTY OF application 'DisplayAlerts' = 'FALSE' .
CALL METHOD OF sheet 'SaveAs'
EXPORTING
#1 = fullpath.
SET PROPERTY OF application 'DisplayAlerts' = 'TRUE' .
call function 'FLUSH'
exceptions others = 0.
IF sy-subrc EQ 0.
MESSAGE 'Archivo descargado satisfactoriamente' TYPE 'S'.
ELSE.
MESSAGE 'Error descargando archivo' TYPE 'E'.
ENDIF.
SET PROPERTY OF application 'visible' = 0.
CALL METHOD OF application 'QUIT'.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT application.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
application = 'EXCEL.EXE'
parameter = archivo
default_directory = path
EXCEPTIONS
OTHERS = 5.
TYPES: ty_data(1500) TYPE c.
DATA: lt_data TYPE ty_data OCCURS 0 WITH HEADER LINE,
lv_cont type i.
DATA: ls_cabecera TYPE ywtst_10004_01,
ls_detalle TYPE ywtst_10004_02,
ls_pallet TYPE ywtst_10004_03,
ls_calibre TYPE ywtst_10004_04.
DATA: archivo TYPE string .
DATA: path TYPE string,
fullpath TYPE string,
uact TYPE i.
* VARIABLES PARA CREAR EL ARCHIVO EXCEL
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cells TYPE ole2_object,
int TYPE ole2_object,
font TYPE ole2_object,
we TYPE ole2_object,
lo_borders TYPE ole2_object.
CONSTANTS:
c_col_black TYPE i VALUE 0,
c_col_white TYPE i VALUE 2,
c_col_red TYPE i VALUE 3,
c_col_light_green TYPE i VALUE 4,
c_col_dark_blue TYPE i VALUE 5,
c_col_yellow TYPE i VALUE 6,
c_col_pink TYPE i VALUE 7,
c_col_light_blue TYPE i VALUE 8,
c_col_brown TYPE i VALUE 9.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_column TYPE ole2_object,
lo_range TYPE ole2_object,
lo_font TYPE ole2_object,
lo_interior TYPE ole2_object.
* Theme Colours:
* Use the subrutine set_soft_colour.
CONSTANTS:
c_theme_col_white TYPE i VALUE 1,
c_theme_col_black TYPE i VALUE 2,
c_theme_col_yellow TYPE i VALUE 3,
c_theme_col_dark_blue TYPE i VALUE 4,
c_theme_col_light_blue TYPE i VALUE 5,
c_theme_col_red TYPE i VALUE 6,
c_theme_col_green TYPE i VALUE 7,
c_theme_col_violet TYPE i VALUE 8,
c_theme_col_pal_blue TYPE i VALUE 9,
c_theme_col_orange TYPE i VALUE 10.
data a type c LENGTH 2 VALUE ''''.
CREATE OBJECT application 'excel.application'.
* HACE VISIBLE LA APLICACION
SET PROPERTY OF application 'visible' = 0.
* LLAMA AL METODO WORKSBOOKS PARA CREAR EL LIBRO
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add'.
* CREA LA PRIMER HOJA
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING
#1 = 1.
* SETEA NOMBRE DE LA HOJA
SET PROPERTY OF sheet 'Name' = 'Packing'.
CALL METHOD OF sheet 'Activate'.
FIELD-SYMBOLS: <field> TYPE ANY.
READ TABLE lt_cabecera INTO ls_cabecera WITH KEY TEXTOCOL2 = 'PRODUCT'.
IF SY-SUBRC = 0.
CONCATENATE 'PACKING LIST ' ls_cabecera-VALORCOL2 INTO lt_data SEPARATED BY SPACE.
ENDIF.
APPEND lt_data. CLEAR lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_cabecera INTO ls_cabecera.
IF ls_cabecera-TEXTOCOL2 = 'TOTAL BOXES'.
REPLACE ALL OCCURRENCES OF '.' IN ls_cabecera-VALORCOL2 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_cabecera-VALORCOL2 WITH ''.
ENDIF.
CONCATENATE ls_cabecera-TEXTOCOL1
ls_cabecera-VALORCOL1
''
''
ls_cabecera-TEXTOCOL2
ls_cabecera-VALORCOL2
''
''
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
data: lv_cajas TYPE char30,
lv_total TYPE char30.
CONCATENATE 'PALLET NUMBER'
'VARIETY'
'WEIGHT/BOX'
'DESCRIPTION'
''
'LABEL'
'SIZE'
'BOXES'
'TOTAL BOXES'
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_detalle INTO ls_detalle.
WRITE ls_detalle-MIXED TO lv_cajas DECIMALS 0.
WRITE ls_detalle-MIXED TO lv_total DECIMALS 0.
CONCATENATE a+00(1) ls_detalle-EXIDV INTO ls_detalle-EXIDV.
CONCATENATE ls_detalle-EXIDV
ls_detalle-VARIETY
ls_detalle-WEIGHT
ls_detalle-DESCRIPTION
''
ls_detalle-LABEL
ls_detalle-CALIBER
lv_cajas
lv_total
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
MOVE 'PALLET DISTRIBUTION' TO lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_pallet INTO ls_pallet.
CONCATENATE a+00(1) ls_pallet-TEXTOCOL1 INTO ls_pallet-TEXTOCOL1.
CONCATENATE a+00(1) ls_pallet-TEXTOCOL2 INTO ls_pallet-TEXTOCOL2.
CONCATENATE ls_pallet-TEXTOCOL1
ls_pallet-TEXTOCOL2
INTO lt_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
APPEND lt_data. CLEAR lt_data.
MOVE 'QUALITY SUMMARY' TO lt_data.
APPEND lt_data. CLEAR lt_data.
LOOP AT lt_calibre INTO ls_calibre.
IF sy-tabix = 1.
CONCATENATE 'VARIETY'
'BOXES'
ls_calibre-CALIBER1
ls_calibre-CALIBER2
ls_calibre-CALIBER3
ls_calibre-CALIBER4
ls_calibre-CALIBER5
ls_calibre-CALIBER6
"ls_calibre-CALIBER7
"ls_calibre-CALIBER8
"ls_calibre-CALIBER9
"ls_calibre-CALIBER10
'TOTAL BOXES'
INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
else.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER1 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER1 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER2 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER2 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER3 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER3 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER4 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER4 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER5 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER5 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER6 WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER6 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER7 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER7 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER8 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER8 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER9 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER9 WITH ''.
* REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-CALIBER10 WITH ''.
* REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-CALIBER10 WITH ''.
REPLACE ALL OCCURRENCES OF '.' IN ls_calibre-TOTAL WITH ''.
REPLACE ALL OCCURRENCES OF ',' IN ls_calibre-TOTAL WITH ''.
CONCATENATE ls_calibre-VARIETY
ls_calibre-BOXES
ls_calibre-CALIBER1
ls_calibre-CALIBER2
ls_calibre-CALIBER3
ls_calibre-CALIBER4
ls_calibre-CALIBER5
ls_calibre-CALIBER6
* ls_calibre-CALIBER7
* ls_calibre-CALIBER8
* ls_calibre-CALIBER9
* ls_calibre-CALIBER10
ls_calibre-TOTAL
INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDIF.
APPEND lt_data. CLEAR lt_data.
ENDLOOP.
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = 3.
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab = lt_data
EXCEPTIONS
clpb_error = 1
OTHERS = 2.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 3 "Row
#2 = 2. "Column
CALL METHOD OF cells 'SELECT'.
CALL METHOD OF sheet 'PASTE'.
"Formatear cabecera de excel
DATA: lv_row TYPE i,
lv_col TYPE i,
lv_colfin TYPE i.
lv_row = 5.
lv_col = 3.
lv_colfin = lv_col + 2.
DO 30 TIMES.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_row "Fila
#2 = lv_col. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_row "Fila
#2 = lv_colfin. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = lv_row "Row
#2 = lv_col. "Column
SET PROPERTY OF cells 'HorizontalAlignment' = 1.
lv_row = lv_row + 1.
IF lv_row = 20.
lv_row = 5.
lv_col = 7.
lv_colfin = lv_col + 3.
ENDIF.
ENDDO.
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 7 "Row
* #2 = 3. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 13 "Row
* #2 = 3. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 12 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 13 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
*
* CALL METHOD OF sheet 'Cells' = cells
* EXPORTING
* #1 = 14 "Row
* #2 = 7. "Column
* SET PROPERTY OF cells 'HorizontalAlignment' = 1.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 12 "Row
#2 = 7. "Column
set property of cells 'NumberFormat' = '#,### '.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = 13 "Row
#2 = 7. "Column
set property of cells 'NumberFormat' = '#,##0.00 '.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
data: lv_filas TYPE i,
lv_acumulado TYPE i.
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_filas = lv_filas + 21.
"Formatear detalle de excel
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 21 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
"Alinear centrado
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_row = 21.
lv_col = 5.
lv_colfin = 6.
lv_filas = lv_filas + 1.
DO lv_filas TIMES.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_row "Fila
#2 = lv_col. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_row "Fila
#2 = lv_colfin. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
lv_row = lv_row + 1.
ENDDO.
DESCRIBE TABLE lt_detalle LINES lv_filas.
lv_filas = lv_filas + 21.
lv_acumulado = lv_filas + 2.
"Formater listado de pallets
DESCRIBE TABLE lt_pallet LINES lv_filas.
lv_filas = lv_filas + lv_acumulado.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_acumulado "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
lv_acumulado = lv_filas + 2.
"Formater listado de calibres
DESCRIBE TABLE lt_calibre LINES lv_filas.
lv_filas = lv_filas + lv_acumulado.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_acumulado "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 3. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = lv_acumulado "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = lv_filas "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
set property of lo_range 'NumberFormat' = '#,### '.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 2.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 3.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 4.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 5.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 6.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '26'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 7.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 8.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 9.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 10.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 11.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 12.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 13.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
*
* CALL METHOD OF sheet 'Columns' = lo_column
* EXPORTING
* #1 = 14.
* CALL METHOD OF lo_column 'select'.
* CALL METHOD OF application 'selection' = lo_selection.
* SET PROPERTY OF lo_column 'ColumnWidth' = '12'.
"Titulo
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 3 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 3 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '15'.
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.
"Subtitulos:
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 5 "Fila
#2 = 6. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 19 "Fila
#2 = 6. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'BOLD' = 2.
SET PROPERTY OF font 'SIZE' = '11'.
CALL METHOD OF sheet 'Cells' = lo_cellstart
EXPORTING
#1 = 21 "Fila
#2 = 2. "Columna
CALL METHOD OF sheet 'Cells' = lo_cellend
EXPORTING
#1 = 21 "Fila
#2 = 10. "Columna
CALL METHOD OF sheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'FONT' = font.
SET PROPERTY OF font 'SIZE' = 11.
SET PROPERTY OF font 'BOLD' = 2.
lv_row = 5.
lv_col = 3.
DO 30 TIMES.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING
#1 = lv_row "Row
#2 = lv_col. "Column
SET PROPERTY OF cells 'HorizontalAlignment' = 2. "3 = Cnetrado; 2 = Izquierda ; 1 = Derecha
lv_row = lv_row + 1.
IF lv_row = 20.
lv_row = 5.
lv_col = 7.
ENDIF.
ENDDO.
Call Method of sheet 'Columns' = LO_Column.
CALL METHOD OF LO_COLUMN 'Autofit' NO FLUSH.
Free Object LO_Column.
* GUARDA EN EL EXCEL
CONCATENATE p_vbeln '.xls' INTO archivo.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'XLS'
default_file_name = archivo
CHANGING
filename = archivo
path = path
fullpath = fullpath
user_action = uact.
SET PROPERTY OF application 'DisplayAlerts' = 'FALSE' .
CALL METHOD OF sheet 'SaveAs'
EXPORTING
#1 = fullpath.
SET PROPERTY OF application 'DisplayAlerts' = 'TRUE' .
call function 'FLUSH'
exceptions others = 0.
IF sy-subrc EQ 0.
MESSAGE 'Archivo descargado satisfactoriamente' TYPE 'S'.
ELSE.
MESSAGE 'Error descargando archivo' TYPE 'E'.
ENDIF.
SET PROPERTY OF application 'visible' = 0.
CALL METHOD OF application 'QUIT'.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT application.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
application = 'EXCEL.EXE'
parameter = archivo
default_directory = path
EXCEPTIONS
OTHERS = 5.
Comentarios
Publicar un comentario