topic Re: Create multiple worksheets in single workbook in application server in Application Development and Automation Discussions

Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 10:36:57 GMT

HI ,

How to create the excel file which is having multiple sheets in single workbook to transfer two internal table contents.

I've two internal tables and i need to tranfer this to applicationserver with the excel sheet (Each interanl table to each excel sheet). I can able to create SINGLE excel workbook in application server with one sheet and not able to create with two different sheets.

Help me....

Regards,

Krishna.

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 11:07:36 GMT

INCLUDE ole2incl.

DATA: h_excel TYPE ole2_object,        " Excel object
      h_mapl  TYPE ole2_object,        " list of workbooks
      h_sheet TYPE ole2_object,        " Sheet
      h_map   TYPE ole2_object,        " workbook
      h_zl    TYPE ole2_object,        " cell
      h_f     TYPE ole2_object,        " font
      int     TYPE ole2_object,        " color
      g_worksheet TYPE ole2_object,
      g_worksheets TYPE ole2_object.

DATA: g_first_ws VALUE 'Y',
      count TYPE i VALUE 1,
      h  TYPE  i   ,
      g_row TYPE i,
      g_col TYPE i,

PERFORM download_excel .

FORM download_excel .

  DATA : x TYPE i,
        gs_adrc LIKE adrc.

  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF h_excel  'Visible' = 1.
  CALL METHOD OF h_excel 'Workbooks' = h_mapl.
  CALL METHOD OF h_mapl 'Add' = h_map.
  x = 0.
  GET PROPERTY OF h_map 'Worksheets' = g_worksheets.
  h = 1.

*for first internal table
  PERFORM  add_worksheet.
  LOOP AT gt_outtab.
    h = h + 1.
    PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
    PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
  ENDLOOP.

*for second internal table
  PERFORM  add_worksheet.
  LOOP AT gt_outtab.
    h = h + 1.
    PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
    PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
  ENDLOOP.

ENDFORM.                    "download_excel


FORM fill_cell USING i j bold val.
  CALL METHOD OF h_excel 'Cells' = h_zl
    EXPORTING
    #1 = i
    #2 = j.
  SET PROPERTY OF h_zl 'Value' = val .

  GET PROPERTY OF h_zl 'Font' = h_f.

  SET PROPERTY OF h_f 'Bold' = bold .

ENDFORM.                    "FILL_CELL


FORM add_worksheet.

  IF g_first_ws ='N'.
    g_first_ws = 'N'.
    GET PROPERTY OF h_excel 'ACTIVESHEET' = g_worksheet.
  ELSE.
    CALL METHOD OF g_worksheets 'Add' = g_worksheet.
  ENDIF.
  SET PROPERTY OF g_worksheet 'NAME' = count.
  h = 1.
  g_col = 1.
  count = count + 1.
ENDFORM.                    "add_worksheet

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 11:14:24 GMT

Hi Thanks for your reply,

I need to create the excel sheets in APPLICATION SERVER ....and i need to transfer the internal table contents to excel sheets which is application server.

Pls help me.

Regards,

Krishnamoorthy V.

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 11:24:11 GMT

Hi ,

If you need to deal with application server then

make use of

Open Data Set

Transfer contents to and from file/application server/ internal table work area

Close Data Set

Hope this is helpful.

Regards,

Uma Dave

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 11:32:36 GMT

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 13:36:09 GMT

Hi ,

Thank for your reply,

I need to transfer two internal contents to two worksheets in single excel workbook ....

for eg:

materialdetails.xls is having two sheets as below and i need to transfer two internal table contents.

Open dataset is creating excelsheet as well but with only one sheet...

lt_matnr = sheet1_matnr.

lt_matnr2 = sheet2_matnr2.

regards,

Krishnamoorthy .

Re: Create multiple worksheets in single workbook in application server

Former Member — Mon, 21 Jun 2010 13:36:32 GMT

Hi ,

Thank for your reply,

I need to transfer two internal contents to two worksheets in single excel workbook ....

for eg:

materialdetails.xls is having two sheets as below and i need to transfer two internal table contents.

Open dataset is creating excelsheet as well but with only one sheet...

lt_matnr = sheet1_matnr.

lt_matnr2 = sheet2_matnr2.

regards,

Krishnamoorthy .

Re: Create multiple worksheets in single workbook in application server

ricky_shaw — Wed, 31 Mar 2021 14:21:25 GMT

Hello,

How can i make this excel generated in background and attach to an email later?

I can see this excel generated with 2 tabs in front end.