Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Ole excel ChartType not working on some type

Go to solution
xiswanto
Active Participant

‎2023 Jul 20 11:52 AM

0 Kudos
169

Hello all,

In my program, i could generate ChartType = 4 ( line chart ), but when i tried generating other chartType ( such as 52 ), it failed somehow without any explanation, I'm not exactly sure which part caused the error, since it could generate the type 4, but not other type. The difference is that my code will generate 2 sheet in 1 file of excel ( where each sheet have 1 chart and 1 set of data ), while the sample code in link below has 1 sheet with 2 sets of data and 2 chart, but I doubt the issue lies in the number of sheet generated.

Sample program I tried from this link.

Here is part of my program to generate the ole excel.

"EXCEL
DATA: gs_excel          TYPE ole2_object,
      gs_wbooklist      TYPE ole2_object,
      gs_application    TYPE ole2_object,
      gs_wbook          TYPE ole2_object,
      gs_activesheet    TYPE ole2_object,
      gs_sheets         TYPE ole2_object,
      gs_newsheet       TYPE ole2_object,
      gs_cell1          TYPE ole2_object,
      gs_cell2          TYPE ole2_object,
      gs_cells          TYPE ole2_object,
      gs_range          TYPE ole2_object,
      gs_font           TYPE ole2_object,
      gs_interior       TYPE ole2_object,
      gs_columns        TYPE ole2_object,
      gs_charts         TYPE ole2_object,
      gs_chart          TYPE ole2_object,
      gs_charttitle     TYPE ole2_object,
      gs_charttitlechar TYPE ole2_object,
      gs_chartobjects   TYPE ole2_object,

      gv_sheet_name(20) TYPE c,
      gv_row            TYPE i , "line number
      gv_col            TYPE i , "column number
      gv_row_c_s        TYPE i,
      gv_col_c_s        TYPE i,
      gv_row_c_e        TYPE i,
      gv_col_c_e        TYPE i,

      z_chart_no        TYPE C LENGTH 5 VALUE '52'.<br>FORM generate_excel .
  DATA: z_vis TYPE i.
  z_vis = 1.
  gv_sheet_name = 'COGM Value'.

  CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .
  SET PROPERTY OF gs_excel 'Visible' = z_vis.
  GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .

  GET PROPERTY OF gs_wbooklist 'Application' = gs_application .
  SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF gs_wbooklist 'Add' = gs_wbook .

  GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet .
  SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.


  DO z_cols + 1 TIMES.
    CALL METHOD OF gs_excel 'COLUMNS' = gs_columns
      EXPORTING
        #1 = sy-index.  "the column number
    CASE sy-index.
      WHEN 1.
        SET PROPERTY OF gs_columns 'ColumnWidth' = 20.
      WHEN OTHERS.
        SET PROPERTY OF gs_columns 'ColumnWidth' = 15.
    ENDCASE.
  ENDDO.


  "VALUE DATA
  "HEADER
  gv_row = 1. "ROW
  gv_col = 1. "COLUMN

  gv_row_c_s = gv_row.
  gv_col_c_s = gv_col.
  DO z_cols TIMES.
    ADD 1 TO gv_col.
    PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
    CASE gv_col.
      WHEN 2.
        PERFORM excel_cell_value USING gs_cell1 'COGM Budget Rate'.
      WHEN 3.
        PERFORM excel_cell_value USING gs_cell1 'COGM - Standard BOM'.
      WHEN 4.
        PERFORM excel_cell_value USING gs_cell1 'RMPM Price Var'.
      WHEN 5.
        PERFORM excel_cell_value USING gs_cell1 'RMPM Loss'.
      WHEN 6.
        PERFORM excel_cell_value USING gs_cell1 'Labor'.
      WHEN 7.
        PERFORM excel_cell_value USING gs_cell1 'Utility'.
      WHEN 8.
        PERFORM excel_cell_value USING gs_cell1 'FOH'.
      WHEN 9.
        PERFORM excel_cell_value USING gs_cell1 'Depreciation'.
      WHEN 10.
        PERFORM excel_cell_value USING gs_cell1 'Repair'.
      WHEN 11.
        PERFORM excel_cell_value USING gs_cell1 'Actual COGM'.
    ENDCASE.
  ENDDO.

  "DATA
  DO 2 TIMES.

  ADD 1 TO gv_row.
  gv_col = 1.
*  PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
*  PERFORM excel_cell_value USING gs_cell1 'COGM Value: Budget; BOM to Actual'.

  DO z_cols TIMES.
    ADD 1 TO gv_col.
    PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
    CASE gv_col.
      WHEN 2.
        PERFORM excel_cell_value USING gs_cell1 wa_values-cogm_bdgt_rate.
      WHEN 3.
        PERFORM excel_cell_value USING gs_cell1 wa_values-cogm_std_bom.
      WHEN 4.
        PERFORM excel_cell_value USING gs_cell1 wa_values-rmpm_prc_var.
      WHEN 5.
        PERFORM excel_cell_value USING gs_cell1 wa_values-rmpm_loss.
      WHEN 6.
        PERFORM excel_cell_value USING gs_cell1 wa_values-labour.
      WHEN 7.
        PERFORM excel_cell_value USING gs_cell1 wa_values-utility.
      WHEN 8.
        PERFORM excel_cell_value USING gs_cell1 wa_values-foh.
      WHEN 9.
        PERFORM excel_cell_value USING gs_cell1 wa_values-depr.
      WHEN 10.
        PERFORM excel_cell_value USING gs_cell1 wa_values-repair.
      WHEN 11.
        PERFORM excel_cell_value USING gs_cell1 wa_values-act_cogm.
    ENDCASE.
    PERFORM excel_cell_ftype USING gs_cell1 0.
  ENDDO.

  ENDDO.

  gv_row_c_e = gv_row.
  gv_col_c_e = gv_col.

  CALL METHOD OF gs_excel 'Cells' = gs_cell1
  EXPORTING
    #1 = 1
*    #1 = gv_row_c_s
    #2 = 2.
*    #2 = gv_col_c_s.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
  EXPORTING
    #1 = 3
    #2 = 11.

  CALL METHOD OF gs_excel 'Range' = gs_cells
  EXPORTING
    #1 = gs_cell1
    #2 = gs_cell2.

  CALL METHOD OF gs_cells 'Select' .

  GET PROPERTY OF gs_application 'Charts' = gs_charts .
  CALL METHOD OF gs_charts 'Add' = gs_chart .
  CALL METHOD OF gs_chart 'Activate'.
  SET PROPERTY OF gs_chart 'ChartType' = z_chart_no .
  CALL METHOD OF gs_chart 'SetSourceData'
    EXPORTING
      #1 = gs_cells
      #2 = 1.
  SET PROPERTY OF gs_chart 'HasTitle' = 0.
  SET PROPERTY OF gs_chart 'HasLegend' = 0.
  CALL METHOD OF gs_chart 'SetElement' EXPORTING #1 = 208.

*----------------------------------------------------------------------*
*--put chart in current sheet

  CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet
    EXPORTING #1 = gv_sheet_name.
  CALL METHOD OF gs_activesheet 'Activate' .
  CALL METHOD OF gs_chart 'Location'
    EXPORTING
      #1 = 2
      #2 = gv_sheet_name.

**  CALL METHOD OF GS_ACTIVESHEET 'ChartObjects' = GS_CHARTOBJECTS .
**  CALL METHOD OF GS_CHARTOBJECTS 'Select' .
**  CALL METHOD OF GS_CHARTOBJECTS 'Cut' .
**
**  CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
**    EXPORTING
**    #1 = 8
**    #2 = 1.
**  CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2
**    EXPORTING
**    #1 = 8
**    #2 = 1.
**  CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS
**    EXPORTING
**    #1 = GS_CELL1.
**  CALL METHOD OF GS_CELLS 'Select' .
**  CALL METHOD OF GS_ACTIVESHEET 'Paste' .


************  "% DATA in new sheet
  "FOR NEW SHEET METHOD
*  gv_sheet_name = 'COGM ratio'.
*  CALL METHOD OF gs_excel 'WORKSHEETS' = gs_sheets.
*  CALL METHOD OF gs_sheets 'ADD'.
*  FREE OBJECT gs_sheets.
*
*  CALL METHOD OF gs_excel 'WORKSHEETS' = gs_sheets EXPORTING #1 = 1.
*  CALL METHOD OF gs_sheets 'ACTIVATE'.
*  SET PROPERTY OF gs_sheets 'NAME' = gv_sheet_name.
*
*  DO z_cols + 1 TIMES.
*    CALL METHOD OF gs_excel 'COLUMNS' = gs_columns
*      EXPORTING
*        #1 = sy-index.  "the column number
*    CASE sy-index.
*      WHEN 1.
*        SET PROPERTY OF gs_columns 'ColumnWidth' = 20.
*      WHEN OTHERS.
*        SET PROPERTY OF gs_columns 'ColumnWidth' = 15.
*    ENDCASE.
*  ENDDO.

  "HEADER
  "NEW SHEET METHOD
*  gv_row = 1.
*  gv_col = 1.
*
*  gv_row_c_s = gv_row.
*  gv_col_c_s = gv_col.
*  DO z_cols TIMES.
*    ADD 1 TO gv_col.
*    PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
*    CASE gv_col.
*      WHEN 2.
*        PERFORM excel_cell_value USING gs_cell1 'COGM Budget rate'.
*      WHEN 3.
*        PERFORM excel_cell_value USING gs_cell1 'COGM - Standard BOM'.
*      WHEN 4.
*        PERFORM excel_cell_value USING gs_cell1 'RMPM Price Var'.
*      WHEN 5.
*        PERFORM excel_cell_value USING gs_cell1 'RMPM Loss'.
*      WHEN 6.
*        PERFORM excel_cell_value USING gs_cell1 'Labor'.
*      WHEN 7.
*        PERFORM excel_cell_value USING gs_cell1 'Utility'.
*      WHEN 8.
*        PERFORM excel_cell_value USING gs_cell1 'FOH'.
*      WHEN 9.
*        PERFORM excel_cell_value USING gs_cell1 'Depreciation'.
*      WHEN 10.
*        PERFORM excel_cell_value USING gs_cell1 'Repair'.
*      WHEN 11.
*        PERFORM excel_cell_value USING gs_cell1 'Actual COGM'.
*    ENDCASE.
*  ENDDO.
*
*  "DATA
*
*  ADD 1 TO gv_row.
*  gv_col = 1.
*  PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
*  PERFORM excel_cell_value USING gs_cell1 'COGM Ratio: Budget; BOM to Actual'.
*
*  DO z_cols TIMES.
*    ADD 1 TO gv_col.
*    PERFORM excel_cell_pos USING gs_cell1 gv_row gv_col.
*    CASE gv_col.
*      WHEN 2.
*        z_word = wa_values-cogm_bdgt_rate_p && '%'.
*      WHEN 3.
*        z_word = wa_values-cogm_std_bom_p && '%'.
*      WHEN 4.
*        z_word = wa_values-rmpm_prc_var_p && '%'.
*      WHEN 5.
*        z_word = wa_values-rmpm_loss_p && '%'.
*      WHEN 6.
*        z_word = wa_values-labour_p && '%'.
*      WHEN 7.
*        z_word = wa_values-utility_p && '%'.
*      WHEN 8.
*        z_word = wa_values-foh_p && '%'.
*      WHEN 9.
*        z_word = wa_values-depr_p && '%'.
*      WHEN 10.
*        z_word = wa_values-repair_p && '%'.
*      WHEN 11.
*        z_word = wa_values-act_cogm_p && '%'.
*    ENDCASE.
*    CONDENSE z_word.
*    PERFORM excel_cell_value USING gs_cell1 z_word.
*    PERFORM excel_cell_ftype USING gs_cell1 'p'.
*  ENDDO.
*  gv_row_c_e = gv_row.
*  gv_col_c_e = gv_col.
*
*  CALL METHOD OF gs_excel 'Cells' = gs_cell1
*  EXPORTING
*    #1 = gv_row_c_s
*    #2 = gv_col_c_s.
*  CALL METHOD OF gs_excel 'Cells' = gs_cell2
*  EXPORTING
*    #1 = gv_row_c_e
*    #2 = gv_col_c_e.
*
*  CALL METHOD OF gs_excel 'Range' = gs_cells
*  EXPORTING
*    #1 = gs_cell1
*    #2 = gs_cell2.
*
*  CALL METHOD OF gs_cells 'Select' .
*
*  GET PROPERTY OF gs_application 'Charts' = gs_charts .
*  CALL METHOD OF gs_charts 'Add' = gs_chart .
*  CALL METHOD OF gs_chart 'Activate' .
*  SET PROPERTY OF gs_chart 'ChartType' = z_chart_no.
*  CALL METHOD OF gs_chart 'SetSourceData'
*    EXPORTING
*      #1 = gs_cells
*      #2 = 1.
*  SET PROPERTY OF gs_chart 'HasTitle' = 0.
*  SET PROPERTY OF gs_chart 'HasLegend' = 0.
*  CALL METHOD OF gs_chart 'SetElement' EXPORTING #1 = 208.
*
*
**----------------------------------------------------------------------*
**--put chart in current sheet
*
*
*  CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet
*    EXPORTING #1 = gv_sheet_name.
*  CALL METHOD OF gs_activesheet 'Activate' .
*  CALL METHOD OF gs_chart 'Location'
*    EXPORTING
*      #1 = 2
*      #2 = gv_sheet_name.

  "SAVE AND CLOSE BG EXCEL

*  CALL METHOD OF gs_wbook 'SAVEAS' EXPORTING #1 = p_file.
*  CALL METHOD OF gs_wbook 'CLOSE'.
*  CALL METHOD OF gs_excel 'QUIT'.
  IF sy-subrc EQ 0.
    MESSAGE 'excel file exported' TYPE 'S'.
  ELSE.
    z_subrc = 2.
    z_err = 'failed to export excel file to location'.
  ENDIF.


  FREE: gs_excel, gs_wbooklist, gs_application, gs_wbook,
  gs_activesheet,gs_sheets, gs_newsheet, gs_cell1,
  gs_cell2, gs_cells, gs_range, gs_font, gs_interior,
  gs_columns,
  gs_chart, gs_charts, gs_charttitle, gs_charttitlechar, gs_chartobjects.
ENDFORM.<br>FORM excel_cell_pos  USING p_cell p_row p_col.
  CALL METHOD OF gs_excel 'Cells' = p_cell
    EXPORTING
    #1 = p_row
    #2 = p_col.
ENDFORM.
FORM excel_cell_value  USING p_cell p_value.
  SET PROPERTY OF p_cell 'Value' = p_value.
ENDFORM.

FORM excel_cell_ftype  USING p_cell p_dec.
  CASE p_dec.
    WHEN '0'.
      SET PROPERTY OF p_cell 'NumberFormat' = '_(* #,##0_);_(* \(#,##0\);_(* "-"??_);_(@_)'.
    WHEN '1'.
      SET PROPERTY OF p_cell 'NumberFormat' = '_(* #,##0.0_);_(* \(#,##0\);_(* "-"??_);_(@_)'.
    WHEN '2'.
      SET PROPERTY OF p_cell 'NumberFormat' = '_(* #,##0.00_);_(* \(#,##0\);_(* "-"??_);_(@_)'.
    WHEN '5'.
      SET PROPERTY OF p_cell 'NumberFormat' = '_(* #,##0.00000_);_(* \(#,##0\);_(* "-"??_);_(@_)'.
    WHEN 'p'.
      SET PROPERTY OF p_cell 'NumberFormat' = '_(* #,##0.0%_);_(* \(#,##0.00%\);_(* "-"??_);_(@_)'.
  ENDCASE.

ENDFORM.<br>

Never have I been so confused since I did my own test to remark some part, and I've made the syntax pretty similar with the sample program and yet it still did not work.

Kindly point out the mistake I missed.

1 ACCEPTED SOLUTION

Go to solution
xiswanto
Active Participant

‎2023 Jul 21 3:18 AM

0 Kudos
137

found the issue, the line of chart 'set element' cause the error.

1 REPLY 1

Go to solution
xiswanto
Active Participant

‎2023 Jul 21 3:18 AM

0 Kudos
138

found the issue, the line of chart 'set element' cause the error.