Smart Need to down load in Excel

[Former Member]

Hello Guys,

I have created Smart form for invoice and Packaging List. As per Requirement I have to give option to Down load Both of them in Same forMat as in printout into the Excel.

They are easily download in PDF, but not In Excel with Display List option.

Can Some One Please Provide me the Correct way or Source code for the same.

Thanks in Advance

Swati Namdeo

[Former Member]

Hello Guys,

As per the requirement I have to Download a smart forms out put in Excel. I have discuss and ask with many people but not having the proper Answer.

The Solution I have done to close the ticket is that , I have use OLE Concept . In this I have paste my Formated Excel File in FTP Server.

From ABAP Program I am opening the excel file and filling data on the basis of selection from ABAP program .

Here is a sample program for your Help. Hope It help you people..

REPORT ZSD_RE_FREIGHTMIS.

TABLES: LIPS, T247 .

TYPE-POOLS: ole2.

CONSTANTS: ciLineConst TYPE i VALUE 256, ccLineRange(5) TYPE c VALUE

'B7:I7', ccDistChnlRange(5) TYPE c VALUE 'B6:I6'.

DATA: oExcel TYPE OLE2_OBJECT, oWorkbook TYPE OLE2_OBJECT, oSheet TYPE OLE2_OBJECT, oCell TYPE OLE2_OBJECT,

oFile TYPE OLE2_OBJECT, oFont TYPE OLE2_OBJECT, oBorder TYPE OLE2_OBJECT,

fQty TYPE f, fCKM TYPE f, fFreight TYPE f, iCounter TYPE i, cCuCode TYPE LIKP-KdGrp, cChnlCode TYPE LIPS-VtWeg,

iChnlLine TYPE i, iLineIdx TYPE i, sLineIdx(10) TYPE c, cFormula(100) TYPE c, iCellRef TYPE i, iFirstRec TYPE i,

cSrcFile(50) TYPE c VALUE 'C:\FR_MIS.xls', w_datef like sy-datum, w_disp_date(15) type c,

w_cDestFile like RCGFILETR-FTFRONT VALUE 'C:\FR_MIS.xls',

w_cSrcFile like RCGFILETR-FTAPPL VALUE '/home/sotlftp/FR_MIS.xls'.

• w_LocalFile like RLGRAP-FILENAME value 'C:\FR_MIS1.xls'.

• w_ServerFile like RLGRAP-FILENAME value '/home/sotlftp/FR_MIS1.xls', g_mode(255) TYPE c.

DATA: cCkmFkm LIKE MARM-Meinh, cCkmText(50) TYPE c.

*&-- AVL Parameters.

TYPE-POOLS: slis.

DATA: fcSummary TYPE slis_t_fieldcat_alv WITH HEADER LINE,

fcDetail TYPE slis_t_fieldcat_alv WITH HEADER LINE,

gd_layout TYPE slis_layout_alv,

gd_repid LIKE sy-repid.

*&-- Event Parameters.

DATA : tl_Event TYPE slis_t_event,

wa_Event TYPE slis_alv_event.

*&----

---

*&----

---

*& CKM Values and Codes.

DATA: BEGIN OF itLIPS OCCURS 0,

tKdGrp LIKE LIKP-KdGrp,

tVtWeg LIKE LIPS-VtWeg,

tVbEln LIKE LIPS-VbEln,

tErDat LIKE LIPS-ErDat,

tQty LIKE LIPS-KcMeng,

tPair LIKE MARM-Umren,

tMatNr LIKE LIPS-MatNr,

tArKtx LIKE LIPS-ArKtx,

tPoSnr LIKE LIPS-PoSnr,

tUnit LIKE LIPS-VrKme,

tBoxLen LIKE LIPS-UmVkz,

tKmLen LIKE LIPS-UmVkn,

tFrAmt LIKE KONV-KweRt,

END OF itLIPS.

*&----

---

*Following 2 - tables not requrd when getting the freight from VFKN

*Table.

*&----

---

**& Freight Codes.

*DATA: BEGIN OF itVBRP OCCURS 0,

• tVgBel LIKE VBRP-VgBel,

• tKnuMv LIKE VBRK-KnuMv,

*END OF itVBRP.

*

**& Freight Values and Codes.

*DATA: BEGIN OF itKONV OCCURS 0,

• tKnuMv LIKE VBRK-KnuMv,

• tFrAmt LIKE KONV-KweRt,

*END OF itKONV.

*&----

---

*& Freight Values and Codes.

DATA: BEGIN OF itVFKN OCCURS 0,

tReBel LIKE VFKN-ReBel,

tRePos LIKE VFKN-RePos,

tNetWr LIKE VFKN-NetWr,

END OF itVFKN.

*& Output Summary Table / Data.

DATA: BEGIN OF itFreightMIS OCCURS 0,

cCuName(50) TYPE c,

cChannel(50) TYPE c,

fFreight TYPE p DECIMALS 3,

fCKM TYPE p DECIMALS 3,

fQty TYPE p DECIMALS 3,

fFrCkm TYPE p DECIMALS 3,

tVtWeg LIKE TVTWT-VtWeg,

fBP TYPE p DECIMALS 3,

cFrPaid(20) TYPE c,

END OF itFreightMIS.

*& Customer Codes & Names.

DATA: BEGIN OF itCustomers OCCURS 0,

tKdGrp LIKE T151T-KdGrp,

tKText LIKE T151T-KText,

END OF itCustomers.

*& Distr. Channels Codes & Names.

DATA: BEGIN OF itDistChannel OCCURS 0,

tVtWeg LIKE TVTWT-VtWeg,

tVText LIKE TVTWT-VText,

END OF itDistChannel.

*& Output Detailed Table / Data.

DATA: BEGIN OF itDetailMIS OCCURS 0,

cCuName(50) TYPE c,

tVouNum LIKE LIPS-VbEln,

tErDat LIKE LIPS-ErDat,

fQty TYPE p DECIMALS 3,

fCKM TYPE p DECIMALS 3,

fFreight TYPE p DECIMALS 3,

fFrCkm TYPE p DECIMALS 3,

tMatNr LIKE LIPS-MatNr,

tArKtx LIKE LIPS-ArKtx,

END OF itDetailMIS.

*DATA: BEGIN OF itAllCustomers OCCURS 0,

• tKdGrp LIKE LIKP-KdGrp,

*END OF itAllCustomers.

*&----

---

*&----

---

*& Input Screen.

SELECTION-SCREEN: BEGIN OF BLOCK B001 WITH FRAME TITLE T001.

• SELECT-OPTIONS Dates FOR LIPS-ErDat NO-EXTENSION OBLIGATORY.

SELECT-OPTIONS Dates FOR LIPS-ErDat OBLIGATORY.

PARAMETERS RM_Code LIKE LIPS-LGort OBLIGATORY.

PARAMETERS Plant_Cd LIKE LIPS-Werks OBLIGATORY.

PARAMETERS Bus_Area LIKE LIPS-GsBer OBLIGATORY.

PARAMETERS Sale_Org LIKE LIKP-VkOrg OBLIGATORY.

SELECTION-SCREEN: SKIP 1.

PARAMETERS: chkExcel as CHECKBOX .

SELECTION-SCREEN: END OF BLOCK B001.

SELECTION-SCREEN: BEGIN OF BLOCK B002 WITH FRAME TITLE T002.

PARAMETERS: Bp_Rel(30) TYPE c,

Bp_Tata(30) TYPE c,

Bp_Bhart(30) TYPE c,

Bp_Bsnl(30) TYPE c,

Bp_Vsnl(30) TYPE c,

Bp_Rdso(30) TYPE c,

Bp_Catv(30) TYPE c,

Bp_Exp(30) TYPE c,

Bp_Other(30) TYPE c,

Bp_Ot1(30) TYPE c,

Bp_Ot2(30) TYPE c.

• Fr_Rel TYPE p DECIMALS 3.

SELECTION-SCREEN: END OF BLOCK B002.

INITIALIZATION.

T001 = 'Selection Parameters'.

T002 = 'Business Plan Data'.

Dates-low = '20070401'.

Dates-high = sy-datum.

Dates-sign = 'I'.

Dates-option = 'BT'.

APPEND Dates.

RM_Code = 'FG01'.

• Plant_Cd = 1400.

• Bus_Area = 1430.

Sale_Org = 1000.

*AT USER-COMMAND.

START-OF-SELECTION.

IF Plant_Cd = 1300.

cCkmFkm = 'FKM'.

cCkmText = 'Qty (FKM)'.

ELSEIF Plant_Cd = 1400.

cCkmFkm = 'CKM'.

cCkmText = 'Qty (CKM)'.

ELSEIF Plant_Cd = 1800 or Plant_Cd = 2100.

cCkmFkm = 'BOX'.

cCkmText = 'Qty (Box)'.

ELSE.

cCkmFkm = 'KM'.

cCkmText = 'Qty (KM)'.

ENDIF.

clear: w_datef .

concatenate dates-high+0(6) '01' into w_datef .

*&----

---

*&----

---

*PERFORM UserScreen.

PERFORM GetCkmFreightData.

IF chkExcel = 'X' OR chkExcel = 'x'.

" Get Excel Object.

PERFORM GetMasterFile .

CREATE OBJECT oExcel 'Excel.Application'.

• SET PROPERTY OF oExcel 'Visible' = 1.

CALL METHOD OF oExcel 'Workbooks' = oWorkbook.

CALL METHOD OF oWorkbook 'Open' = oFile EXPORTING #1 = w_cDestFile.

PERFORM PrepareSummary USING Dates-high Dates-high.

PERFORM ExportReportToExcel USING 'DAY' Dates-high.

PERFORM PrepareSummary USING w_datef Dates-high.

PERFORM ExportReportToExcel USING 'MONTH' Dates-high.

PERFORM PrepareSummary USING Dates-low Dates-high.

PERFORM ExportReportToExcel USING 'YEAR' Dates-high.

SET PROPERTY OF oExcel 'Visible' = 1.

ELSE.

PERFORM PrepareSummary USING Dates-low Dates-high.

ENDIF.

"----

---

PERFORM fcSummary.

PERFORM CallEvent.

PERFORM RaiseEvent.

PERFORM DisplaySummary.

"----

---

" Clear the Memory.

FREE OBJECT: oFont, oCell, oSheet, oWorkbook, OExcel, OFile.

CLEAR: oFont-handle, oFont-header, oCell-handle, oCell-header, oSheet-handle, oSheet-header.

CLEAR: oWorkbook-handle, oWorkbook-header, oExcel-handle, oExcel-header, OFile-handle, OFile-header.

*&----

---

*&----

---

*&----

---

*&----

---

FORM GetCkmFreightData.

CLEAR itLIPS.

REFRESH itLIPS.

"----

---

" Get data for Dadra

IF Plant_Cd = 1800 or Plant_Cd = 2100.

SELECT LH_{KdGrp LD}VtWeg LD_{VbEln LD}ErDat LD_{KcMeng MA}UmRen LD_{MatNr LD}ArKtx LD_{PoSnr LD}VrKme LD_{UmVkz LD}UmVkn

FROM ( ( LIPS AS LD LEFT JOIN LIKP AS LH ON LD_{VbEln = LH}VbEln AND LH~VkOrg = Sale_Org )

LEFT JOIN MARM AS MA ON MA_{MatNr = LD}MatNr AND MA~Meinh = cCkmFkm )

INTO TABLE itLIPS

WHERE LD_{LGORT = RM_Code AND LD}ErDat IN Dates

AND LD_{WERKS = Plant_Cd AND LD}GSBER = Bus_Area AND LD_{UeCha EQ 0 " LD}KcMeng GT 0

ORDER BY LH_{KdGrp LD}VbEln.

" Delete records other than Dadra.

LOOP AT itLIPS WHERE NOT ( tUnit = 'BOX' OR tUnit = 'KM' ).

DELETE itLIPS INDEX sy-tabix.

ENDLOOP.

"----

---

" Get data for CKM/FKM.

ELSE.

SELECT LH_{KdGrp LD}VtWeg LD_{VbEln LD}ErDat LD_{KcMeng MA}UmRen LD_{MatNr LD}ArKtx LD~PoSnr

FROM ( ( LIPS AS LD LEFT JOIN LIKP AS LH ON LD_{VbEln = LH}VbEln AND LH~VkOrg = Sale_Org )

LEFT JOIN MARM AS MA ON MA_{MatNr = LD}MatNr AND MA~Meinh = cCkmFkm )

INTO TABLE itLIPS

WHERE LD_{LGORT = RM_Code AND LD}ErDat IN Dates

AND LD_{WERKS = Plant_Cd AND LD}GSBER = Bus_Area AND LD_{UeCha EQ 0 " LD}KcMeng GT 0

ORDER BY LH_{KdGrp LD}VbEln.

ENDIF.

" Delete Scrap Customers & null values records.

LOOP AT itLIPS WHERE tKdGrp = 'SC' OR tKdGrp = ''.

DELETE itLIPS INDEX sy-tabix.

ENDLOOP.

*&----

---

• This code does not requrd when getting the freight from VFKN Table.

*&----

---

• " Get Selected Codes for the Freight.

• SELECT DISTINCT VD_{VgBel VH}KnuMv

• FROM VBRP AS VD LEFT JOIN VBRK AS VH ON VH_{VbEln = VD}VbEln

• INTO TABLE itVBRP.

*

• "------------------------

• " Get Selected Codes & Freight Amount.

• SELECT KnuMv KweRt

• FROM KONV

• INTO TABLE itKONV

• WHERE KSchl = 'ZBF2'.

*&----

---

SELECT ReBel RePos NetWr

FROM VFKN

INTO TABLE itVFKN

WHERE ReTyp = 'J'.

"----

---

SORT itLIPS BY tVbEln tPoSnr.

" Get Freight Amount.

LOOP AT itLIPS.

• READ TABLE itVBRP WITH KEY tVgBel = itLIPS-tVbEln.

• READ TABLE itKONV WITH KEY tKnuMv = itVBRP-tKnuMv.

• itLIPS-tFrAmt = itKONV-tFrAmt.

LOOP AT itVFKN WHERE tReBel = itLIPS-tVbEln AND tRePos =

itLIPS-tPoSnr.

itLIPS-tFrAmt = itVFKN-tNetWr.

MODIFY itLIPS.

ENDLOOP.

ENDLOOP.

"&----

---

" Get Customer Names.

SELECT KdGrp KText

FROM T151T

INTO TABLE itCustomers

ORDER BY KText.

" Get Channel Names.

SELECT VtWeg VText

FROM TVTWT

INTO TABLE itDistChannel

WHERE SpRas = 'E'

ORDER BY VText.

"&----

---

" Show All Customers.

• SELECT DISTINCT KdGrp

• FROM LIKP

• INTO itAllCustomers

• WHERE VtWeg IN '10, 20, 30'.

• SORT itLIPS BY tKdGrp.

• LOOP AT itLIPS.

• ON CHANGE OF itLIPS-tKdGrp.

• itAllCustomers-tKdGrp = itLIPS-tKdGrp.

• APPEND itAllCustomers.

• ENDON.

• ENDLOOP.

*

• itAllCustomers-tKdGrp = itLIPS-tKdGrp.

• APPEND itAllCustomers.

ENDFORM.

*&----

---

*&----

---

*&----

---

*&----

---

FORM PrepareSummary USING dtFrom dtTo.

"&----

---

" Prepare Summary Report.

SORT itLIPS BY tKdGrp.

iCounter = 1.

CLEAR: itFreightMIS, fQty, fCKM, fFreight.

REFRESH: itFreightMIS.

" Add dummy record to trigger the ON CHANGE OF event.

LOOP AT itLIPS WHERE tErDat BETWEEN dtFrom AND dtTo.

ON CHANGE OF itLIPS-tKdGrp.

IF iCounter GT 1.

READ TABLE itCustomers WITH KEY tKdGrp = cCuCode.

READ TABLE itDistChannel WITH KEY tVtWeg = cChnlCode.

itFreightMIS-cCuName = itCustomers-tKText.

itFreightMIS-cChannel = itDistChannel-tVText.

itFreightMIS-tVtWeg = cChnlCode.

itFreightMIS-fQty = fQty.

itFreightMIS-fCKM = fCKM.

itFreightMIS-fFreight = fFreight.

IF fCKM GT 0.

itFreightMIS-fFrCkm = fFreight / fCKM.

ENDIF.

PERFORM HardCodeBPAndFreight USING cCuCode cChnlCode.

APPEND itFreightMIS.

ENDIF.

CLEAR: fQty, fCKM, fFreight.

ENDON.

"----

---

" Get Group by sum.

• IF Plant_Cd = 1400.

• fCKM = fCKM + fQty * itLIPS-tPair.

• ELSEIF Plant_Cd = 1300.

• fCKM = fCKM + itLIPS-tPair.

• ELSE.

• fCKM = fCKM + itLIPS-tPair.

• ENDIF.

fQty = fQty + itLIPS-tQty.

fFreight = fFreight + itLIPS-tFrAmt.

" Update Qty for Dadra, 1840 division.

IF ( Plant_Cd = 1800 OR Plant_Cd = 2100 ) and ( Bus_Area = 1840 or Bus_Area = 2140 ).

IF itLIPS-tUnit <> 'KM'.

fCKM = fCKM + itLIPS-tQty * itLIPS-tKmLen / itLIPS-tBoxLen.

ELSEIF itLIPS-tBoxLen = 1 AND itLIPS-tKmLen = 1.

fCKM = fCKM + itLIPS-tQty * 1000 / 305.

ENDIF.

ELSE.

fCKM = fCKM + itLIPS-tQty * itLIPS-tPair.

ENDIF.

cCuCode = itLIPS-tKdGrp.

cChnlCode = itLIPS-tVtWeg.

iCounter = iCounter + 1.

ENDLOOP.

"----

---

" Add Last Record.

READ TABLE itCustomers WITH KEY tKdGrp = cCuCode.

READ TABLE itDistChannel WITH KEY tVtWeg = cChnlCode.

itFreightMIS-cCuName = itCustomers-tKText.

itFreightMIS-cChannel = itDistChannel-tVText.

itFreightMIS-tVtWeg = cChnlCode.

itFreightMIS-fFreight = fFreight.

itFreightMIS-fCKM = fCKM.

itFreightMIS-fQty = fQty.

IF fCKM GT 0.

itFreightMIS-fFrCkm = fFreight / fCKM.

ENDIF.

"----

---

" Show all customers.

• LOOP AT itAllCustomers.

• READ TABLE itFreightMIS WITH KEY

• ENDLOOP.

"----

---

PERFORM HardCodeBPAndFreight USING cCuCode cChnlCode.

APPEND itFreightMIS.

ENDFORM.

*&----

---

*&----

---

FORM HardCodeBPAndFreight USING cCustCode cChnlCode.

CASE cCustCode.

WHEN 'RE'.

itFreightMIS-fBP = 258.

itFreightMIS-cFrPaid = 'Extra at Actual'.

WHEN 'CA'.

itFreightMIS-fBP = 356.

itFreightMIS-cFrPaid = 'Inclusive'.

WHEN 'TA'.

itFreightMIS-fBP = 602.

itFreightMIS-cFrPaid = '600'.

WHEN 'BH'.

itFreightMIS-fBP = 615.

itFreightMIS-cFrPaid = '1500'.

WHEN 'OT'.

itFreightMIS-fBP = 638.

itFreightMIS-cFrPaid = '1031'.

WHEN 'BS'.

itFreightMIS-fBP = 712.

itFreightMIS-cFrPaid = 'Inclusive'.

WHEN 'RA'.

itFreightMIS-fBP = 750.

itFreightMIS-cFrPaid = '450'.

WHEN 'TP'.

itFreightMIS-fBP = 856.

itFreightMIS-cFrPaid = '500'.

WHEN OTHERS.

itFreightMIS-fBP = 0.

itFreightMIS-cFrPaid = ''.

ENDCASE.

IF cChnlCode = 30.

itFreightMIS-fBP = 1315.

itFreightMIS-cFrPaid = ''.

ENDIF.

ENDFORM.

*&----

---

*&----

---

FORM fcSummary.

fcSummary-fieldname = 'CCUNAME'.

fcSummary-seltext_m = 'Segment'.

fcSummary-col_pos = 0.

fcSummary-outputlen = 25.

fcSummary-emphasize = 'X'.

fcSummary-key = 'X'.

APPEND fcSummary TO fcSummary.

CLEAR fcSummary.

fcSummary-fieldname = 'FQTY'.

fcSummary-seltext_m = 'Qty (KM)'.

fcSummary-col_pos = 1.

fcSummary-outputlen = 12.

APPEND fcSummary TO fcSummary.

CLEAR fcSummary.

fcSummary-fieldname = 'FCKM'.

fcSummary-seltext_m = cCkmText.

fcSummary-col_pos = 2.

fcSummary-outputlen = 12.

APPEND fcSummary TO fcSummary.

CLEAR fcSummary.

fcSummary-fieldname = 'FFREIGHT'.

fcSummary-seltext_m = 'Freight'.

fcSummary-col_pos = 3.

fcSummary-outputlen = 12.

APPEND fcSummary TO fcSummary.

CLEAR fcSummary.

CONCATENATE 'Freight /' cCkmText INTO cFormula SEPARATED BY SPACE.

fcSummary-fieldname = 'FFRCKM'.

fcSummary-seltext_m = cFormula.

fcSummary-col_pos = 4.

fcSummary-outputlen = 12.

APPEND fcSummary TO fcSummary.

CLEAR fcSummary.

ENDFORM.

*&----

---

*&----

---

FORM DisplaySummary.

gd_repid = sy-repid.

call function 'REUSE_ALV_GRID_DISPLAY'

exporting

i_callback_program = gd_repid

i_callback_user_command = 'USER_COMMAND'

i_grid_title =

'SAP - Packing and Freight MIS Report Summary'

is_layout = gd_layout

it_fieldcat = fcSummary[]

IT_EVENTS = tl_event

i_save = 'X'

tables

t_outtab = itFreightMIS

exceptions

program_error = 1

others = 2.

ENDFORM.

*&----

---

*&----

---

*&----

---

*&----

---

FORM PrepareDetailedReport USING rsCustCode TYPE slis_selfield.

REFRESH itDetailMIS.

CLEAR itDetailMIS.

READ TABLE itCustomers WITH KEY tKText = rsCustCode-Value.

LOOP AT itLIPS WHERE tKdGrp = itCustomers-tKdGrp.

itDetailMIS-cCuName = itCustomers-tKText.

itDetailMIS-tVouNum = itLIPS-tVbEln.

itDetailMIS-tErDat = itLIPS-tErDat.

itDetailMIS-fQty = itLIPS-tQty.

" Update Qty for Dadra, 1840 division.

IF ( Plant_Cd = 1800 OR Plant_Cd = 2100 ) and ( Bus_Area = 1840 or bus_Area = 2140 ) .

IF itLIPS-tUnit <> 'KM'.

itDetailMIS-fCKM = itLIPS-tQty * itLIPS-tKmLen / itLIPS-tBoxLen.

ELSEIF itLIPS-tBoxLen = 1 AND itLIPS-tKmLen = 1.

itDetailMIS-fCKM = itLIPS-tQty * 1000 / 305.

ENDIF.

ELSE.

itDetailMIS-fCKM = itLIPS-tQty * itLIPS-tPair.

ENDIF.

itDetailMIS-fFreight = itLIPS-tFrAmt.

IF itDetailMIS-fCKM GT 0.

itDetailMIS-fFrCkm = itLIPS-tFrAmt / itDetailMIS-fCKM.

ENDIF.

itDetailMIS-tMatNr = itLIPS-tMatNr.

itDetailMIS-tArKtx = itLIPS-tArKtx.

APPEND itDetailMIS.

ENDLOOP.

SORT itDetailMIS BY cCuName tVouNum.

ENDFORM.

*&----

---

*&----

---

FORM fcDetailed.

REFRESH fcDetail.

fcDetail-fieldname = 'CCUNAME'.

fcDetail-seltext_m = 'Customer Name'.

fcDetail-col_pos = 0.

fcDetail-outputlen = 20.

fcDetail-emphasize = 'X'.

fcDetail-key = 'X'.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'TVOUNUM'.

fcDetail-seltext_m = 'DC No.'.

fcDetail-col_pos = 1.

fcDetail-outputlen = 12.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'TERDAT'.

fcDetail-seltext_m = 'DC Date'.

fcDetail-col_pos = 2.

fcDetail-outputlen = 10.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'TMATNR'.

fcDetail-seltext_m = 'Material'.

fcDetail-col_pos = 3.

fcDetail-outputlen = 20.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'TARKTX'.

fcDetail-seltext_m = 'Description'.

fcDetail-col_pos = 4.

fcDetail-outputlen = 20.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'FQTY'.

fcDetail-seltext_m = 'Qty (KM)'.

fcDetail-col_pos = 5.

fcDetail-outputlen = 10.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'FCKM'.

• fcDetail-seltext_m = 'CKM'.

fcDetail-seltext_m = cCkmText.

fcDetail-col_pos = 6.

fcDetail-outputlen = 10.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

fcDetail-fieldname = 'FFREIGHT'.

fcDetail-seltext_m = 'Freight'.

fcDetail-col_pos = 7.

fcDetail-outputlen = 12.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

CONCATENATE 'Freight /' cCkmText INTO cFormula SEPARATED BY SPACE.

fcDetail-fieldname = 'FFRCKM'.

fcDetail-seltext_m = cFormula.

fcDetail-col_pos = 8.

fcDetail-outputlen = 12.

APPEND fcDetail TO fcDetail.

CLEAR fcDetail.

ENDFORM.

*&----

---

*&----

---

FORM DisplayDetailedReport.

gd_repid = sy-repid.

call function 'REUSE_ALV_GRID_DISPLAY'

exporting

i_callback_program = gd_repid

i_grid_title =

'SAP - Packing and Freight MIS Detailed Report'

it_fieldcat = fcDetail[]

IT_EVENTS = tl_event

i_save = 'X'

tables

t_outtab = itDetailMIS

exceptions

program_error = 1

others = 2.

ENDFORM.

*&----

---

*&----

---

*&----

---

*&----

---

FORM CallEvent.

CALL FUNCTION 'REUSE_ALV_EVENTS_GET'

EXPORTING

i_list_type = 0

IMPORTING

et_events = tl_Event

EXCEPTIONS

list_type_wrong = 1

OTHERS = 2.

ENDFORM.

*&----

---

*&----

---

FORM RaiseEvent.

READ TABLE tl_Event INTO wa_Event WITH KEY NAME = 'USER_COMMAND'.

IF sy-subrc = 0.

wa_Event-form = 'USERCOMMAND'.

MODIFY TABLE tl_Event FROM wa_Event.

ELSE.

• MESSAGE e005 DISPLAY LIKE 'S'.

ENDIF.

ENDFORM.

*&----

---

*&----

---

FORM USER_COMMAND USING r_ucomm LIKE sy-ucomm rs_selfield TYPE

slis_selfield.

CASE r_ucomm.

WHEN '&IC1'.

IF rs_selfield-SEL_TAB_FIELD <> '1-CCUNAME' .

message e333(ZPP_MSG) .

ENDIF .

PERFORM PrepareDetailedReport USING rs_selfield.

PERFORM fcDetailed.

PERFORM DisplayDetailedReport.

ENDCASE.

ENDFORM.

*&----

---

*&----

---

FORM ExportReportToExcel USING sSheet dtDate.

DATA iValue TYPE i.

clear: w_disp_date .

"----

---

CALL METHOD OF oExcel 'Worksheets' = oSheet EXPORTING #1 = sSheet.

CALL METHOD OF oSheet 'Activate'.

iLineIdx = 5.

iChnlLine = 4.

iFirstRec = 1.

"----

---

CONCATENATE dtDate6(2) '.' dtDate4(2) '.' dtDate+0(4) INTO cFormula.

CASE sSheet.

WHEN 'DAY'.

CONCATENATE 'For the Day :' '' cFormula INTO cFormula.

WHEN 'MONTH'.

select single * from T247 where spras = 'EN' and MNR = dtDate+4(2).

if sy-subrc = 0 .

CONCATENATE 'For the Month :' '' T247-LTX ' -' '' dtDate+0(4) ' (Till -' cFormula ')' INTO cFormula SEPARATED BY SPACE.

endif .

WHEN 'YEAR'.

if dtDate+4(2) > '03' .

w_disp_date = dtDate+0(4) + 1 .

CONDENSE w_disp_date.

concatenate dtDate+0(4) ' -' '' w_disp_date into w_disp_date SEPARATED BY SPACE.

else .

w_disp_date = dtDate+0(4) - 1 .

CONDENSE w_disp_date.

concatenate w_disp_date ' -' '' dtDate+0(4) into w_disp_date SEPARATED BY SPACE.

endif .

CONCATENATE 'For the Year : ' w_disp_date ' (Till -' cFormula ')' INTO cFormula SEPARATED BY SPACE.

ENDCASE.

iCellRef = ciLineConst * 2 + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = cFormula.

IF Plant_Cd = 1300.

cFormula = 'OFC'.

ELSEIF Plant_Cd = 1400.

cFormula = 'JFTC'.

ELSEIF Plant_Cd = 1800 or Plant_Cd = 2100.

cFormula = 'Data Cables'.

ENDIF.

CONCATENATE 'FREIGHT MIS REPORT - Unit :' cFormula INTO cFormula SEPARATED BY SPACE.

iCellRef = iCellRef - ciLineConst.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = cFormula.

"----

---

" Write Output to Excel.

SORT itFreightMIS BY tVtWeg cCuName.

LOOP AT itFreightMIS.

"----

---

" Write Channel Name.

ON CHANGE OF itFreightMIS-tVtWeg.

IF iLineIdx GT 6.

" Add SUM Formula.

PERFORM WriteCustomerTotals.

iLineIdx = iLineIdx + 1.

"----

---

" SUM of Govt. & Pvt.

IF itFreightMIS-tVtWeg = 30.

PERFORM WriteChannelTotals USING 'DOMESTIC - TOTAL'.

ENDIF.

"----

---

" Copy & Paste Cell.

iLineIdx = iLineIdx + 1.

iCounter = iLineIdx + 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE 'B' sLineIdx INTO cFormula.

PERFORM CopyAndPasteCells USING ccDistChnlRange cFormula.

ENDIF.

" Write Distribution Channel's Name.

iChnlLine = iLineIdx.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-cChannel.

iLineIdx = iLineIdx + 1.

iFirstRec = 999.

ENDON.

"----

---

" This code is written to execute the ON CHANGE event's code.

if iFirstRec = 1.

" Write Distribution Channel's Name.

iChnlLine = iLineIdx.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-cChannel.

iLineIdx = iLineIdx + 1.

iFirstRec = 999.

ENDIF.

"----

---

" Copy & Paste Line.

IF iLineIdx GT 6.

iCounter = iLineIdx + 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE 'B' sLineIdx INTO cFormula.

PERFORM CopyAndPasteCells USING ccLineRange cFormula.

ENDIF.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-cCuName.

iCellRef = iCellRef + 1.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-fQty.

iCellRef = iCellRef + 1.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-fFreight.

iCellRef = iCellRef + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-fBP.

iCellRef = iCellRef + 3.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = itFreightMIS-cFrPaid.

iLineIdx = iLineIdx + 1.

ENDLOOP.

"----

---

PERFORM WriteCustomerTotals.

iLineIdx = iLineIdx + 1.

PERFORM WriteChannelTotals USING 'DOM + EXPORT Actual Total'.

"----

---

" Write Data '748'.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = 'DOM + EXPORT BP MIS Avg.'.

" 'Grand Total for MIS'.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'Bold' = 'True'.

iCellRef = iCellRef + 4.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = '748'.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'Bold' = 'True'.

" Set Formula.

iCounter = iLineIdx + 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE '=F' sLineIdx '*C' INTO cFormula.

iCounter = iLineIdx.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE cFormula sLineIdx INTO cFormula.

iCellRef = iCellRef + 1.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

TRANSLATE cFormula USING 'FG'.

TRANSLATE cFormula USING 'CD'.

TRANSLATE cFormula USING '*-'.

iCellRef = iCellRef + 1.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

" Set Border.

iCounter = iLineIdx + 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE 'B6:I' sLineIdx INTO cFormula.

PERFORM SetExcelRangeBorder USING cFormula 2.

iLineIdx = iLineIdx + 3.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = 'For any query regarding to this report, please run the ZFR_MIS1 transaction code in SAP.'.

"----

---

" For Data Cable Only.

IF Plant_Cd = 1800 or Plant_Cd = 2100.

iCellRef = 4 * 256 + 3.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = 'BOX'.

iCellRef = iCellRef + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = 'ACTUAL/BOX'.

iCellRef = iCellRef + 1.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = 'B.P./BOX'.

ENDIF.

"----

---

SET PROPERTY OF oExcel 'Visible' = 1.

iCellRef = 6 * 256 + 8.

DO iLineIdx TIMES.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

GET PROPERTY OF oCell 'Value' = cFormula.

MOVE cFormula TO iValue.

IF iValue < 0.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'ColorIndex' = 3.

ENDIF.

iCellRef = iCellRef + 256.

ENDDO.

ENDFORM.

*&----

---

*----

---

*&----

---

*----

---

FORM WriteCustomerTotals.

iCellRef = ciLineConst * iChnlLine + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

GET PROPERTY OF oCell 'Value' = cFormula.

CONCATENATE cFormula '-' 'TOTAL' INTO cFormula SEPARATED BY SPACE.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = cFormula.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'Bold' = 'True'.

"----

---

iCounter = iChnlLine + 2.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE '=SUM(C' sLineIdx ':C' INTO cFormula.

MOVE iLineIdx TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE cFormula sLineIdx ')' INTO cFormula.

"----

---

" CKM SUM.

iCellRef = iCellRef + 1.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

" Freight SUM.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'CD'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

" Fr./ BP SUM.

iCellRef = iCellRef + 3.

TRANSLATE cFormula USING 'DG'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

" Variance SUM.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'GH'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

"----

---

" Actual / CKM Formula.

iCellRef = iCellRef - 3.

iCounter = iLineIdx + 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE '=D' sLineIdx '/C' sLineIdx INTO cFormula.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

" B.P. / CKM Formula.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'DG'.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

ENDFORM.

*&----

---

*----

---

*&----

---

*----

---

FORM WriteChannelTotals USING cText.

iCounter = iChnlLine - 1.

MOVE iCounter TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE '=C' sLineIdx '+C' INTO cFormula.

MOVE iLineIdx TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE cFormula sLineIdx INTO cFormula.

iCellRef = ciLineConst * iLineIdx + 2.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = cText.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'Bold' = 'True'.

" CKM SUM.

iCellRef = iCellRef + 1.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

" Freight SUM.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'CD'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

" Fr./ BP SUM.

iCellRef = iCellRef + 3.

TRANSLATE cFormula USING 'DG'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

" Variance SUM.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'GH'.

PERFORM SetExcelFormula USING iCellRef cFormula '#,##0'.

iLineIdx = iLineIdx + 1.

"----

---

" Actual / CKM Formula.

iCellRef = iCellRef - 3.

• iCounter = iLineIdx + 1.

MOVE iLineIdx TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE '=D' sLineIdx '/C' sLineIdx INTO cFormula.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

" B.P. / CKM Formula.

iCellRef = iCellRef + 1.

TRANSLATE cFormula USING 'DG'.

PERFORM SetExcelFormula USING iCellRef cFormula '0'.

"----

---

" Draw Border.

MOVE iLineIdx TO sLineIdx.

CONDENSE sLineIdx.

CONCATENATE 'B' sLineIdx ':I' sLineIdx INTO cFormula.

PERFORM SetExcelRangeBorder USING cFormula 2.

ENDFORM.

*&----

---

*----

---

*&----

---

*----

---

*&----

---

*----

---

*&----

---

*----

---

FORM CopyAndPasteCells USING cSource cDestination.

CALL METHOD OF oExcel 'Range' = oCell EXPORTING #1 = cSource.

CALL METHOD OF oCell 'Select'.

CALL METHOD OF oCell 'Copy'.

CALL METHOD OF oExcel 'Range' = oCell EXPORTING #1 = cDestination.

CALL METHOD OF oCell 'Select'.

CALL METHOD OF oSheet 'Paste'.

ENDFORM.

*&----

---

*----

---

*&----

---

*----

---

*& Developed By :

*& Date Created :

*& Module Purpose : Function to draw the border in Excel File.

*&----

---

*----

---

*&----

---

*----

---

FORM SetExcelRangeBorder USING cRange iThickness.

DATA: iThickValue TYPE i, iBorderEdge TYPE i.

CASE iThickness.

WHEN 1.

iThickValue = 2.

WHEN 2.

iThickValue = -4138.

WHEN 3.

iThickValue = 4.

ENDCASE.

CALL METHOD OF oExcel 'Range' = oCell EXPORTING #1 = cRange.

iBorderEdge = 7.

DO 4 TIMES.

CALL METHOD OF oCell 'Borders' = oBorder EXPORTING #1 = iBorderEdge.

SET PROPERTY OF oBorder 'LineStyle' = 1.

SET PROPERTY OF oBorder 'Weight' = iThickValue.

iBorderEdge = iBorderEdge + 1.

ENDDO.

ENDFORM.

*&----

---

*----

---

*&----

---

*----

---

*& Developed By :

*& Date Created : Tuesday, June 19, 2007.

*& Module Purpose : Function to Set the Formula in Excel File.

*&----

---

*----

---

*&----

---

*----

---

FORM SetExcelFormula USING iCellRef cFormula cNumberFormat.

CALL METHOD OF oSheet 'Cells' = oCell EXPORTING #1 = iCellRef.

SET PROPERTY OF oCell 'Value' = cFormula.

SET PROPERTY OF oCell 'NumberFormat' = cNumberFormat.

CALL METHOD OF oCell 'Font' = oFont.

SET PROPERTY OF oFont 'Bold' = 'True'.

ENDFORM.

*&----

---

*&----

---

form GetMasterFile.

CALL FUNCTION 'C13Z_FILE_DOWNLOAD_BINARY'

EXPORTING

i_file_front_end = w_cDestFile

i_file_appl = w_cSrcFile

I_FILE_OVERWRITE = 'X'

• IMPORTING

• E_FLG_OPEN_ERROR =

• E_OS_MESSAGE =

EXCEPTIONS

FE_FILE_OPEN_ERROR = 1

FE_FILE_EXISTS = 2

FE_FILE_WRITE_ERROR = 3

AP_NO_AUTHORITY = 4

AP_FILE_OPEN_ERROR = 5

AP_FILE_EMPTY = 6

OTHERS = 7

.

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. " GetMasterFile

[Former Member]

Hi

I don't believe there's a tool to convert a sap print (smartform or sapscript) to excel format.

U should create the excel file directly without to produce the smartform, try to see the demo progrm XXLFTEST.

Max