Data in excel sheet to be used for programing

[Former Member]

Hi,

I have to make a z report for which the data i have to use is in a excel sheet.

How can I use this data for making different reports.

Reg,

Archana

[Former Member]

Hi,

Upload the data into internal table from excel sheet and then make the report accordingly....

Use TEXT_CONVERT_XLS_TO_SAP function module to READ data from excel sheet.

REWARD IF USEFUL

[venkat_o]

Hi Archana, We can use the Function module TEXT_CONVERT_XLS_TO_SAP to read the Excel file into the internal table. From this internal table you can fill the target internal table.

  report  zvenkat-upload-xl  no standard page heading.
"----------------------------------------------------------------------
"Declarations.
"----------------------------------------------------------------------
"types
types:
      begin of t_bank_det,
        pernr(8)  type c,
        bnksa(4)  type c,
        zlsch(1)  type c,
        bkplz(10) type c,
        bkort(25) type c,
        bankn(18) type c,
      end of t_bank_det.
"work areas
data:
      w_bank_det type t_bank_det.
"internal tables
data:
      i_bank_det type table of t_bank_det.
"---------------------------------------------------------------------
" selection-screen
"----------------------------------------------------------------------
selection-screen begin of block b1 with frame title text_001.
parameters p_file type localfile.
selection-screen end of block b1.
*---------------------------------------------------------------------
"At selection-screen on value-request for p_file.
*---------------------------------------------------------------------
at selection-screen on value-request for p_file.
  perform f4_help.
*---------------------------------------------------------------------
  "Start-of-selection.
*---------------------------------------------------------------------
start-of-selection.
  perform upload_data.
*---------------------------------------------------------------------
  "End-of-selection.
*---------------------------------------------------------------------
end-of-selection.
  perform display_data.
*&---------------------------------------------------------------------*
  "Form  f4_help
*&---------------------------------------------------------------------*
form f4_help .
  data:
        l_file_name like  ibipparms-path  .

  call function 'F4_FILENAME'
    exporting
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = 'P_FILE'
    importing
      file_name     = l_file_name.

  p_file = l_file_name.

endform.                                                    " f4_help
*---------------------------------------------------------------------*
"Form  upload_data
*---------------------------------------------------------------------*
form upload_data .
  type-pools:truxs.
  data:li_tab_raw_data type  truxs_t_text_data.
  data:l_filename      like  rlgrap-filename.

  l_filename = p_file.
  call function 'TEXT_CONVERT_XLS_TO_SAP'
    exporting
      i_tab_raw_data       = li_tab_raw_data
      i_filename           = l_filename
    tables
      i_tab_converted_data = i_bank_det
    exceptions
      conversion_failed    = 1
      others               = 2.
  if sy-subrc <> 0.
    message id sy-msgid type sy-msgty number sy-msgno
            with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  endif.

endform.                    " upload_data
*---------------------------------------------------------------------*
" Form  display_data
*---------------------------------------------------------------------*
form display_data .
  data: char100 type char100.
  loop at i_bank_det into w_bank_det .
    if sy-tabix = 1.
      write w_bank_det.
      write / '------------------------------------------------------------'.
    else.
      write / w_bank_det.
    endif.
  endloop.

endform.                    " display_data 

I hope that it helps u . Regards, Venkat.O

[Former Member]

Hi,

Thank you very much for the code. But as u know an excel sheet itself can have many sheets in it.....

Suppose my excel sheet name is XYZ.Xls and it consists of a sheet 'abcd' . how can use the data in this particular sheet only as i will be defining my internal table according to the 'abcd' sheet?

I hope my question is clear???

Regards,

Archana

[Former Member]

Hi,

Try the following code for multiple sheets.

data : $i_intern type kcde_cells occurs 0 with header line.

data : $v_index type i.

data : $v_start_col type i value '1',

$v_start_row type i value '1',

$v_end_col type i value '256',

$v_end_row type i value '7500'.

data: excel_tab type kcde_sender.

data: separator type c.

field-symbols: <field>.

data: application type ole2_object,

workbook type ole2_object,

range type ole2_object,

worksheet type ole2_object,

worksheets type ole2_object,

sheets type ole2_object.

data: h_cell type ole2_object.

data: h_cell1 type ole2_object.

data: l_sheet type c length 40.

data: l_active_sheet type i.

define m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others. raise upload_ole.

endcase.

end-of-definition.

• Create Excel OLE2 object and open XLS file

if application-header = space or application-handle = -1.

create object application 'Excel.Application'.

m_message.

endif.

call method of application 'Workbooks' = workbook.

m_message.

call method of workbook 'Open' exporting #1 = pa_file.

m_message.

• Show/don't show XLS

• set property of application 'Visible' = 1.

• m_message.

• Determine number of sheets

call method of application 'Sheets' = sheets.

m_message.

call method of sheets 'Count' = sheetno.

m_message.

l_active_sheet = 0.

do sheetno times.

clear: $i_intern[], excel_tab[].

clear: it_vals[], it_chars[].

l_active_sheet = l_active_sheet + 1.

• Activate sheet number L_ACTIVE_SHEET

call method of application 'Worksheets' = worksheets exporting #1 = l_active_sheet.

m_message.

call method of worksheets 'Activate'.

m_message.

• Get active sheet

get property of application 'ACTIVESHEET' = worksheet.

m_message.

• Find start

call method of worksheet 'Cells' = h_cell

exporting #1 = $v_start_row #2 = $v_start_col.

m_message.

• Find end

call method of worksheet 'Cells' = h_cell1

exporting #1 = $v_end_row #2 = $v_end_col.

m_message.

• Create range

call method of worksheet 'RANGE' = range

exporting #1 = h_cell #2 = h_cell1.

m_message.

• Select range

call method of range 'SELECT'.

m_message.

• copy to Clipboard

call method of range 'COPY'.

m_message.

call function 'CONTROL_FLUSH'

exceptions

others = 3.

• Import clipboard

call function 'CLPB_IMPORT'

tables

data_tab = excel_tab

exceptions

clpb_error = 1

others = 2.

if sy-subrc <> 0. message x001(kx). endif.

separator = cl_abap_char_utilities=>horizontal_tab.

perform separated_to_intern_convert(saplkcde) tables excel_tab $i_intern

using separator.

set property of application 'CutCopyMode' = 0.

m_message.

enddo.

call method of application 'QUIT'.

m_message.

free object : application,

workbook,

worksheet,

sheets,

range.

m_message.

Hope it works.

Please reward accordingly.

Thanks,

Priyanka