OLE excel -> subtotals
hello everybody,
i am trying to format an already existing excel-file with ole-technique.
this works fine so far, but (of course) there is one very important step i can't handle.
i need to make subtotals and after 2 days of trying and searching the net i have no more ideas how it could work.
to make it easier to understand i have made a simple excerpt which everyone should be able to use as local report by copy/paste or you can download the coding as txt-file from here:
txt-filehttp://bluemer.antville.org/static/bluemer/files/abapcoding_ole.txt">txt-file>
***************************************
*----
*
Report ZVEXCELOLE_TMP
Author: Matthias Leitner
*----
*
REPORT zvexcelole_tmp NO STANDARD PAGE HEADING LINE-SIZE 132.
------------------------------------------------------------------- *
datendeklaration *
------------------------------------------------------------------- *
DATA: it_filetable TYPE filetable.
DATA: wa_filetable TYPE file_table.
DATA: wa_filename TYPE string,
wa_upload TYPE string,
wa_download TYPE string,
wa_file(255) TYPE c,
wa_rc TYPE i.
OLE-Definitionen
INCLUDE ole2incl.
DATA: wa_loesch type i,
o_excel TYPE ole2_object,
o_workbook TYPE ole2_object,
o_columns TYPE ole2_object,
o_autofit TYPE ole2_object,
o_blatt TYPE ole2_object.
------------------------------------------------------------------- *
selection-screen *
------------------------------------------------------------------- *
SELECTION-SCREEN BEGIN OF BLOCK z3 WITH FRAME.
PARAMETERS: pa_dir(255) TYPE c LOWER CASE .
SELECTION-SCREEN END OF BLOCK z3.
------------------------------------------------------------------- *
initialization *
------------------------------------------------------------------- *
INITIALIZATION.
standardmässiges download-verzeichnis holen
CALL METHOD cl_gui_frontend_services=>get_upload_download_path
CHANGING
upload_path = wa_upload
download_path = wa_download
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
gui_upload_download_path = 4
upload_download_path_failed = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
MOVE wa_download TO pa_dir.
ENDIF.
------------------------------------------------------------------- *
at selection-screen *
------------------------------------------------------------------- *
AT SELECTION-SCREEN.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_dir.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
WINDOW_TITLE =
DEFAULT_EXTENSION =
DEFAULT_FILENAME =
FILE_FILTER =
INITIAL_DIRECTORY =
MULTISELECTION =
WITH_ENCODING =
CHANGING
file_table = it_filetable
rc = wa_rc
USER_ACTION =
FILE_ENCODING =
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
READ TABLE it_filetable INTO wa_filetable INDEX 1.
MOVE wa_filetable-filename TO pa_dir.
ENDIF.
------------------------------------------------------------------- *
start-of-selection *
------------------------------------------------------------------- *
START-OF-SELECTION.
PERFORM excel_format.
MOVE pa_dir TO wa_file.
FORMAT HOTSPOT ON.
WRITE: wa_file+0(132).
FORMAT HOTSPOT OFF.
HIDE wa_file.
CLEAR wa_file.
------------------------------------------------------------------- *
at line-selection *
------------------------------------------------------------------- *
AT LINE-SELECTION.
CLEAR wa_file.
READ CURRENT LINE.
IF NOT wa_file IS INITIAL.
MOVE wa_file TO wa_filename.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
document = wa_filename
application =
PARAMETER =
DEFAULT_DIRECTORY =
MAXIMIZED =
MINIMIZED =
SYNCHRONOUS =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
OTHERS = 8
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDIF.
------------------------------------------------------------------- *
form excel_format *
------------------------------------------------------------------- *
FORM excel_format.
start excel
CREATE OBJECT o_excel 'EXCEL.APPLICATION'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. EXCEL.APPLICATION'.
ENDIF.
excel -> not visible
SET PROPERTY OF o_excel 'Visible' = 0. "visible = 1
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Visible'.
ENDIF.
Arbeitsblatt auswählen
CALL METHOD OF o_excel 'Workbooks' = o_workbook.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Workbooks'.
ENDIF.
open existing file
CALL METHOD OF o_workbook 'OPEN'
EXPORTING
#1 = pa_dir.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. OPEN'.
ENDIF.
SUBTOTALS!!!
bei allen spalten auf optimale breite setzen
CALL METHOD OF o_excel 'Columns' = o_columns.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Columns'.
ENDIF.
CALL METHOD OF o_columns 'Autofit' = o_autofit.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Autofit'.
ENDIF.
save and close excel
CALL METHOD OF o_excel 'Worksheets' = o_blatt
EXPORTING
#1 = 1.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Worksheets'.
ENDIF.
CALL METHOD OF o_blatt 'SaveAs'
EXPORTING
#1 = pa_dir
#2 = 1. "fileFormat
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. SaveAs'.
ELSE.
ENDIF.
CALL METHOD OF o_workbook 'CLOSE'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. CLOSE'.
ENDIF.
CALL METHOD OF o_excel 'QUIT'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. QUIT'.
ENDIF.
FREE o_excel.
ENDFORM. "FORM excel_format
***************************************
the excel-file should be grouped by column 6 and sum column 4.
you can download an example of the excel-sheet (already including the subtotals as i need them):
xls-filehttp://bluemer.antville.org/static/bluemer/files/faktbeilage06_beispiel.xls">xls-file>
i hope my question is clear. if i have forgotten anything important - please ask.
our system:
SAP_BASIS 620
excel 2002 - service pack 3
thanks in advance!
matthias
Message was edited by: Matthias Leitner