Melukis di Excell (Visual.Foxpro)

Digunakan ketika kita di haruskan membuat report excell dari database, berikut contoh script nya :

( Melukis di Excell PDF Format )

LOCAL _bln,_thn, qTemp, pathdef,lnROW,lnCOL,_supplier,_headerx

LOCAL hit1,hit2,hit3,hit4,x,_rhitung
LOCAL sb1,sb2,sb3,sb4,sb5,sb6,sb7,sb8,sb9,sb10
LOCAL sb11,sb12,sb13,sb14,sb15,sb16,sb17,sb18,sb19,sb20
LOCAL sb21,sb22,sb23,sb24,sb25,sb26,sb27,sb28,sb29,sb30

LOCAL st1,st2,st3,st4,st5,st6,st7,st8,st9,st10
LOCAL st11,st12,st13,st14,st15,st16,st17,st18,st19,st20
LOCAL st21,st22,st23,st24,st25,st26,st27,st28,st29,st30

LOCAL sg1,sg2,sg3,sg4,sg5,sg6,sg7,sg8,sg9,sg10
LOCAL sg11,sg12,sg13,sg14,sg15,sg16,sg17,sg18,sg19,sg20
LOCAL sg21,sgb22,sg23,sg24,sg25,sg26,sg27,sg28,sg29,sg30

STORE 0 TO sb1,sb2,sb3,sb4,sb5,sb6,sb7,sb8,sb9,sb10, ;
sb11,sb12,sb13,sb14,sb15,sb16,sb17,sb18,sb19,sb20, ;
sb21,sb22,sb23,sb24,sb25,sb26,sb27,sb28,sb29,sb30

STORE 0 TO st1,st2,st3,st4,st5,st6,st7,st8,st9,st10, ;
st11,st12,st13,st14,st15,st16,st17,st18,st19,st20, ;
st21,stb22,st23,st24,st25,st26,st27,st28,st29,st30

STORE 0 TO sg1,sg2,sg3,sg4,sg5,sg6,sg7,sg8,sg9,sg10, ;
sg11,sg12,sg13,sg14,sg15,sg16,sg17,sg18,sg19,sg20, ;
sg21,sgb22,sg23,sg24,sg25,sg26,sg27,sg28,sg29,sg30

STORE 0 TO hit1,hit2,hit3,hit4

CLOSE DATABASES
CLOSE TABLES

pathdef = GETDIR()
IF EMPTY(pathdef)
pathdef = SYS(5) + SYS(2003)
ENDIF
* SET DEFAULT TO &pathdef

store “q”+padl(allt(str(rand()*1000000,7)),7,”0″)+”.dbf” to qTemp1

SELECT 0
USE gDatax+’\rkrekap.dbf’ SHARE
SET ORDER TO KEY1

thisform.Label1.ForeColor = 255
thisform.Label1.Caption = ‘Perhitungan Rekap……’

SELECT rkrekap
GO top
SCAN
REPLACE salesafret WITH gros_sales + sales_ret
REPLACE donitotal WITH cdoni + pdoni
REPLACE hrgfaktur WITH salesafret – sel_harga
REPLACE revenue1 WITH hrgfaktur – donitotal
REPLACE dofitotal WITH cdofi + pdofi
REPLACE revenue2 WITH revenue1 – dofitotal
REPLACE gp1 WITH revenue2 – hpp_total
flush
ENDSCAN

SELECT rkrekap
INDEX ON xkodedept+xkodesup+xklasprod+division+supplier+productgrp TO gDTemp+’\’+qTemp1
SET ORDER TO 1

_thn = rkrekap.ptahun
_bln = rkrekap.pbulan
SELECT DIST xkodesup, SUPPLIER FROM rkrekap INTO CURSOR CUR_SUPPLIER READWRITE ORDER BY 1

thisform.Label1.Caption = ‘Buat Header Excell……’

nmfl1 = ‘RPT’+ALLTRIM(_THN)+ALLTRIM(_BLN)
*new_excel_file = pathdef +’\EXCEL\’+ nmfl1
new_excel_file = pathdef + nmfl1

IF !DIRECTORY(pathdef)
MKDIR pathdef
ENDIF

**** CREATE HEADER
oleApp=Createobject(“Excel.Application”)
oleApp.Visible=.F.
oleApp.Workbooks.add
nameole = right(oleApp.caption,len(oleApp.caption)-at(‘Book’,oleApp.caption)+1)
oleApp.windows(nameole).activate
oleApp.sheets(“sheet1″).select

oleApp.Cells.Select
oleApp.Cells.Font.size = 12
oleApp.Cells.Name=”ARIAL”

oleapp.ActiveWindow.Zoom=80
oleApp.Selection.NumberFormat = “General”
oleApp.Cells.VerticalAlignment = 2
oleApp.Cells(1,1).Select

oleApp.sheets(“sheet1″).name=’REKAP’
*****
_headerx = ‘Laporan Penjualan dan Gross Profit, Periode Bulan ‘+_BLN+’ Tahun ‘+_THN
*****
oleApp.Sheets(‘REKAP’).Cells(1,1).Value=’PT.MENSA BINA SUKSES’
oleApp.Sheets(‘REKAP’).Cells(1,1).Font.size = 12
oleApp.Sheets(‘REKAP’).Cells(1,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(1,1).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(2,1).Value=’LAPORAN PENJUALAN DAN GROSS PROFIT’
oleApp.Sheets(‘REKAP’).Cells(2,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(2,1).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(3,1).Value=’PERIODE LAPORAN BULAN ‘+_BLN+’ TAHUN ‘+_THN
oleApp.Sheets(‘REKAP’).Cells(3,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(3,1).Font.ColorIndex = 5

** Header **
oleApp.Sheets(‘REKAP’).Cells(6,1).Value=’GROUP’
oleApp.Sheets(‘REKAP’).Cells(6,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,1).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,2).Value=’GROSS’
oleApp.Sheets(‘REKAP’).Cells(6,2).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,2).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,3).Value=’SALES’
oleApp.Sheets(‘REKAP’).Cells(6,3).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,3).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,4).Value=’SALES’
oleApp.Sheets(‘REKAP’).Cells(6,4).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,4).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,5).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,5).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,5).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,6).Value=’SEL.HARGA’
oleApp.Sheets(‘REKAP’).Cells(6,6).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,6).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,7).Value=’HARGA’
oleApp.Sheets(‘REKAP’).Cells(6,7).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,7).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,8).Value=’DONI’
oleApp.Sheets(‘REKAP’).Cells(6,8).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,8).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,9).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,9).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,9).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,10).Value=’DONI’
oleApp.Sheets(‘REKAP’).Cells(6,10).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,10).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,11).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,11).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,11).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,12).Value=’TOTAL’
oleApp.Sheets(‘REKAP’).Cells(6,12).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,12).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,13).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,13).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,13).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,14).Value=’REVENUE 1′
oleApp.Sheets(‘REKAP’).Cells(6,14).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,14).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,15).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,15).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,15).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,16).Value=’DOFI’
oleApp.Sheets(‘REKAP’).Cells(6,16).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,16).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,17).Value=”
oleApp.Sheets(‘REKAP’).Cells(6,17).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,17).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,18).Value=’DOFI’
oleApp.Sheets(‘REKAP’).Cells(6,18).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,18).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,19).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,19).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,19).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,20).Value=’DOFI’
oleApp.Sheets(‘REKAP’).Cells(6,20).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,20).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,21).Value=’REVENUE 2′
oleApp.Sheets(‘REKAP’).Cells(6,21).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,21).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,22).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,22).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,22).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,23).Value=’COGS’
oleApp.Sheets(‘REKAP’).Cells(6,23).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,23).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,24).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,24).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,24).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,25).Value=’GP 1′
oleApp.Sheets(‘REKAP’).Cells(6,25).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,25).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,26).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,26).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,26).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,27).Value=’COGS’
oleApp.Sheets(‘REKAP’).Cells(6,27).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,27).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,28).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,28).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,28).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,29).Value=’GP 2′
oleApp.Sheets(‘REKAP’).Cells(6,29).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,29).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(6,30).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(6,30).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(6,30).Font.ColorIndex = 5

** // +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
oleApp.Sheets(‘REKAP’).Cells(7,1).Value=’PRODUK’
oleApp.Sheets(‘REKAP’).Cells(7,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,1).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,2).Value=’SALES’
oleApp.Sheets(‘REKAP’).Cells(7,2).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,2).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,3).Value=’RETURN’
oleApp.Sheets(‘REKAP’).Cells(7,3).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,3).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,4).Value=’AFTER RETURN’
oleApp.Sheets(‘REKAP’).Cells(7,4).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,4).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,5).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,5).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,5).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,6).Value=’HNA’
oleApp.Sheets(‘REKAP’).Cells(7,6).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,6).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,7).Value=’FAKTUR’
oleApp.Sheets(‘REKAP’).Cells(7,7).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,7).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,8).Value=’CABANG’
oleApp.Sheets(‘REKAP’).Cells(7,8).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,8).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,9).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,9).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,9).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,10).Value=’PRINCIPAL’
oleApp.Sheets(‘REKAP’).Cells(7,10).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,10).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,11).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,11).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,11).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,12).Value=’DONI’
oleApp.Sheets(‘REKAP’).Cells(7,12).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,12).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,13).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,13).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,13).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,14).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,14).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,14).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,15).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,15).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,15).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,16).Value=’CABANG’
oleApp.Sheets(‘REKAP’).Cells(7,16).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,16).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,17).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,17).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,17).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,18).Value=’PRINCIPAL’
oleApp.Sheets(‘REKAP’).Cells(7,18).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,18).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,19).Value=’%’
oleApp.Sheets(‘REKAP’).Cells(7,19).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,19).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,20).Value=’TOTAL’
oleApp.Sheets(‘REKAP’).Cells(7,20).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,20).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,21).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,21).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,21).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,22).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,22).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,22).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,23).Value=’ACTUAL’
oleApp.Sheets(‘REKAP’).Cells(7,23).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,23).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,24).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,24).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,24).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,25).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,25).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,25).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,26).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,26).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,26).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,27).Value=’ADJ.STDR’
oleApp.Sheets(‘REKAP’).Cells(7,27).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,27).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,28).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,28).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,28).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,29).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,29).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,29).Font.ColorIndex = 5

oleApp.Sheets(‘REKAP’).Cells(7,30).Value=”
oleApp.Sheets(‘REKAP’).Cells(7,30).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(7,30).Font.ColorIndex = 5

lnRow = 9
SELECT RKREKAP
SET ORDER TO 1
GO top
_xkodesup = rkrekap.xkodesup
_supplier = rkrekap.supplier
_fist = .T.
DO WHILE !EOF()
IF _fist
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Value=_supplier
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.Size = 12
_fist = .F.
_xkodesup = rkrekap.xkodesup
_supplier = rkrekap.supplier
lnRow = lnRow + 1
_rhitung = 0
ELSE
_xkodesup = rkrekap.xkodesup
_supplier = rkrekap.supplier
endif

oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Value=” “+rkrekap.productgrp
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.Bold = .T.
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.ColorIndex = 10
oleApp.Sheets(‘REKAP’).Cells(lnRow,1).Font.Size = 10

*sb1
oleApp.Sheets(‘REKAP’).Cells(lnRow,2).Value=rkrekap.gros_sales
*oleApp.Sheets(‘REKAP’).Cells(lnRow,2).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,2).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,2).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,2).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb2
oleApp.Sheets(‘REKAP’).Cells(lnRow,3).Value=rkrekap.sales_ret
*oleApp.Sheets(‘REKAP’).Cells(lnRow,3).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,3).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,3).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,3).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb3
oleApp.Sheets(‘REKAP’).Cells(lnRow,4).Value=rkrekap.salesafret
*oleApp.Sheets(‘REKAP’).Cells(lnRow,4).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,4).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,4).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,4).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb4
*oleApp.Sheets(‘REKAP’).Cells(lnRow,5).Value=IIF(rkrekap.salesafret=0,0,(rkrekap.salesafret/rkrekap.salesafret)*100)
*oleApp.Sheets(‘REKAP’).Cells(lnRow,5).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,5).Font.ColorIndex = 5
*oleApp.Sheets(‘REKAP’).Cells(lnRow,5).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnRow,5).FormulaR1C1 = “=if(RC[-1]=0,0,(RC[-1]/RC[-1])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,5).Font.Size = 10
*sb5
oleApp.Sheets(‘REKAP’).Cells(lnRow,6).Value=rkrekap.sel_harga
*oleApp.Sheets(‘REKAP’).Cells(lnRow,6).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,6).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,6).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,6).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb6
oleApp.Sheets(‘REKAP’).Cells(lnRow,7).Value=rkrekap.hrgfaktur
*oleApp.Sheets(‘REKAP’).Cells(lnRow,7).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,7).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,7).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,7).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb7
oleApp.Sheets(‘REKAP’).Cells(lnRow,8).Value=rkrekap.cdoni*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,8).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,8).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,8).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,8).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb8
*oleApp.Sheets(‘REKAP’).Cells(lnRow,9).Value=IIF(rkrekap.salesafret=0,0,((rkrekap.cdoni*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,9).FormulaR1C1 = “=IF(RC[-5]=0,0,(RC[-1]/RC[-5])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,9).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,9).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,9).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,9).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb9
oleApp.Sheets(‘REKAP’).Cells(lnRow,10).Value=rkrekap.pdoni*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,10).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,10).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,10).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,10).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb10
*oleApp.Sheets(‘REKAP’).Cells(lnRow,11).Value=IIF(rkrekap.salesafret=0,0,((rkrekap.pdoni*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,11).FormulaR1C1 = “=IF(RC[-7]=0,0,(RC[-1]/RC[-7])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,11).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,11).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,11).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,11).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb11
oleApp.Sheets(‘REKAP’).Cells(lnRow,12).Value=(rkrekap.cdoni+rkrekap.pdoni)*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,12).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,12).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,12).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,12).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb12
*oleApp.Sheets(‘REKAP’).Cells(lnRow,13).Value=IIF(rkrekap.salesafret=0,0,(((rkrekap.pdoni+rkrekap.cdoni)*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,13).FormulaR1C1 = “=IF(RC[-9]=0,0,(RC[-1]/RC[-9])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,13).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,13).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,13).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,13).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb13
oleApp.Sheets(‘REKAP’).Cells(lnRow,14).Value=rkrekap.hrgfaktur+((rkrekap.cdoni+rkrekap.pdoni)*-1)
*oleApp.Sheets(‘REKAP’).Cells(lnRow,14).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,14).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,14).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,14).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

** hitungan revenue 1 **
hit1 = rkrekap.hrgfaktur+((rkrekap.cdoni+rkrekap.pdoni)*-1)
*sb14
*oleApp.Sheets(‘REKAP’).Cells(lnRow,15).Value=IIF(rkrekap.salesafret=0,0,(hit1/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,15).FormulaR1C1 = “=IF(RC[-11]=0,0,(RC[-1]/RC[-11])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,15).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,15).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,15).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,15).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb15
oleApp.Sheets(‘REKAP’).Cells(lnRow,16).Value=cdofi*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,16).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,16).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,16).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,16).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb16
*oleApp.Sheets(‘REKAP’).Cells(lnRow,17).Value=IIF(rkrekap.salesafret=0,0,((rkrekap.cdofi*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,17).FormulaR1C1 = “=IF(RC[-13]=0,0,(RC[-1]/RC[-13])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,17).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,17).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,17).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,17).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb17
oleApp.Sheets(‘REKAP’).Cells(lnRow,18).Value=pdofi*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,18).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,18).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,18).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,18).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb18
*oleApp.Sheets(‘REKAP’).Cells(lnRow,19).Value=IIF(rkrekap.salesafret=0,0,((rkrekap.pdofi*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,19).FormulaR1C1 = “=IF(RC[-15]=0,0,(RC[-1]/RC[-15])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,19).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,19).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,19).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,19).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb19
oleApp.Sheets(‘REKAP’).Cells(lnRow,20).Value=(rkrekap.cdofi+rkrekap.pdofi)*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,20).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,20).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,20).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,20).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

** hitungan revenue 2 *
hit2 = hit1 + ((rkrekap.cdofi+rkrekap.pdofi)*-1)
*sb20
oleApp.Sheets(‘REKAP’).Cells(lnRow,21).Value=hit2
*oleApp.Sheets(‘REKAP’).Cells(lnRow,21).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,21).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,21).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,21).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb21
*oleApp.Sheets(‘REKAP’).Cells(lnRow,22).Value=IIF(rkrekap.salesafret=0,0,(hit2/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,22).FormulaR1C1 = “=IF(RC[-18]=0,0,(RC[-1]/RC[-18])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,22).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,22).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,22).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,22).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb22
oleApp.Sheets(‘REKAP’).Cells(lnRow,23).Value=rkrekap.hpp_total*-1
*oleApp.Sheets(‘REKAP’).Cells(lnRow,23).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,23).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,23).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,23).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb23
*oleApp.Sheets(‘REKAP’).Cells(lnRow,24).Value=IIF(rkrekap.salesafret=0,0,((rkrekap.hpp_total*-1)/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,24).FormulaR1C1 = “=IF(RC[-20]=0,0,(RC[-1]/RC[-20])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,24).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,24).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,24).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,24).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

** hitungan GP 1 **
hit3 = hit2+(rkrekap.hpp_total*-1)
*sb24
oleApp.Sheets(‘REKAP’).Cells(lnRow,25).Value=hit3
*oleApp.Sheets(‘REKAP’).Cells(lnRow,25).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,25).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,25).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,25).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb25
*oleApp.Sheets(‘REKAP’).Cells(lnRow,26).Value=IIF(rkrekap.salesafret=0,0,((hit2+(rkrekap.hpp_total*-1))/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,26).FormulaR1C1 = “=IF(RC[-22]=0,0,(RC[-1]/RC[-22])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,26).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,26).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,26).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,26).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

** hitungan GP 2 *
hit4 = hit3-(rkrekap.pdoni*-1)-((rkrekap.cdofi+rkrekap.pdofi)*-1)
*sb26
oleApp.Sheets(‘REKAP’).Cells(lnRow,29).Value=hit4
*oleApp.Sheets(‘REKAP’).Cells(lnRow,29).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,29).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,29).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,29).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
*sb27
*oleApp.Sheets(‘REKAP’).Cells(lnRow,30).Value=IIF(rkrekap.salesafret=0,0,(hit4/rkrekap.salesafret)*100)
oleApp.Sheets(‘REKAP’).Cells(lnRow,30).FormulaR1C1 = “=IF(RC[-26]=0,0,(RC[-1]/RC[-26])*100)”
*oleApp.Sheets(‘REKAP’).Cells(lnRow,30).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,30).Font.ColorIndex = 5
oleApp.Sheets(‘REKAP’).Cells(lnRow,30).Font.Size = 10
oleApp.Sheets(‘REKAP’).Cells(lnROW,30).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

* Hitung Sum Total *
sb1 = sb1 + rkrekap.gros_sales
sb2 = sb2 + rkrekap.sales_ret
sb3 = sb3 + rkrekap.salesafret
sb4 = ”
sb5 = sb5 + rkrekap.sel_harga
sb6 = sb6 + rkrekap.hrgfaktur
sb7 = sb7 + rkrekap.cdoni*-1
sb8 = IIF(sb3=0,0,(sb7/sb3)*100)
sb9 = sb9 + rkrekap.pdoni*-1
sb10 = IIF(sb3=0,0,(sb9/sb3)*100)
sb11 = sb11 + (rkrekap.cdoni+rkrekap.pdoni)*-1
sb12 = IIF(sb3=0,0,(sb11/sb3)*100)
sb13 = sb13 + rkrekap.hrgfaktur+((rkrekap.cdoni+rkrekap.pdoni)*-1)
sb14 = IIF(sb3=0,0,(sb13/sb3)*100)
sb15 = sb15 + rkrekap.cdofi*-1
sb16 = IIF(sb3=0,0,(sb15/sb3)*100)
sb17 = sb17 + rkrekap.pdofi*-1
sb18 = IIF(sb3=0,0,(sb17/sb3)*100)
sb19 = sb19 + (rkrekap.cdofi+rkrekap.pdofi)*-1
sb20 = sb20 + hit2
sb21 = IIF(sb3=0,0,(sb20/sb3)*100)
sb22 = sb22 + rkrekap.hpp_total*-1
sb23 = IIF(sb3=0,0,(sb22/sb3)*100)
sb24 = sb24 + hit3
sb25 = IIF(sb3=0,0,(sb24/sb3)*100)
sb26 = sb26 + hit4
sb27 = IIF(sb3=0,0,(sb26/sb3)*100)

thisform.Label1.Caption = ‘Create Data ‘+ALLTRIM(_supplier)+’ ‘+rkrekap.productgrp

lnRow = lnRow + 1
_rhitung = _rhitung + 1
SKIP

IF rkrekap.xkodesup <> _xkodesup
_fist = .T.
_xkodesup = rkrekap.xkodesup
_supplier = rkrekap.supplier
** Sum Total **
*sb1
x2 = 0
FOR x1 = 1 TO 29
*a123 = ‘sb’+ALLTRIM(STR(x1))
*x2 = x1
*IF x1 = 26 OR x1 = 27
* x2 = x2 + 2
*ENDIF
*oleApp.Sheets(‘REKAP’).Cells(lnRow,1+x2).Value=&a123
*oleApp.Sheets(‘REKAP’).Cells(lnRow,1+x2).Font.Bold = .T.
*oleApp.Sheets(‘REKAP’).Cells(lnRow,1+x2).Font.ColorIndex = 2
*oleApp.Sheets(‘REKAP’).Cells(lnRow,1+x2).Font.Size = 12
*oleApp.Sheets(‘REKAP’).Cells(lnROW,1+x2).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

oleApp.Sheets(‘REKAP’).Cells(lnRow,x1+1).FormulaR1C1 = “=SUM(R[-“+ALLTRIM(STR(_rhitung))+”]C:R[-1]C)”
oleApp.Sheets(‘REKAP’).Cells(lnROW,x1+1).NumberFormat = “#,##0.00_);[Red](#,##0.00)”

thisform.Label1.Caption = “Create Summary (“+ALLTRIM(STR(lnRow))+” , “+ALLTRIM(STR(x2))+”)”

* hitung prosentase *
oleApp.Sheets(‘REKAP’).Cells(lnRow,5).FormulaR1C1 = “=IF(RC[-1]=0,0,(RC[-1]/RC[-1])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,9).FormulaR1C1 = “=IF(RC[-5]=0,0,(RC[-1]/RC[-5])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,11).FormulaR1C1 = “=IF(RC[-7]=0,0,(RC[-1]/RC[-7])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,13).FormulaR1C1 = “=IF(RC[-9]=0,0,(RC[-1]/RC[-9])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,15).FormulaR1C1 = “=IF(RC[-11]=0,0,(RC[-1]/RC[-11])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,17).FormulaR1C1 = “=IF(RC[-13]=0,0,(RC[-1]/RC[-13])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,19).FormulaR1C1 = “=IF(RC[-15]=0,0,(RC[-1]/RC[-15])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,22).FormulaR1C1 = “=IF(RC[-18]=0,0,(RC[-1]/RC[-18])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,24).FormulaR1C1 = “=IF(RC[-20]=0,0,(RC[-1]/RC[-20])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,26).FormulaR1C1 = “=IF(RC[-22]=0,0,(RC[-1]/RC[-22])*100)”
oleApp.Sheets(‘REKAP’).Cells(lnRow,30).FormulaR1C1 = “=IF(RC[-26]=0,0,(RC[-1]/RC[-26])*100)”

NEXT

STORE 0 TO sb1,sb2,sb3,sb4,sb5,sb6,sb7,sb8,sb9,sb10, ;
sb11,sb12,sb13,sb14,sb15,sb16,sb17,sb18,sb19,sb20, ;
sb21,sb22,sb23,sb24,sb25,sb26,sb27,sb28,sb29,sb30
lnRow = lnRow + 2
ENDIF

ENDDO

oleApp.Cells(9,1).Columns.ColumnWidth = 40
OleApp.Cells(9,2).Select
oleapp.ActiveWindow.FreezePanes = .t.

IF FILE(new_excel_file+’.XLS’)
DELETE FILE (new_excel_file+’.XLS’)
ENDI
oleApp.ActiveWorkbook.SaveAs(new_excel_file)
oleApp.Quit
Release oleApp

This entry was posted in Programming. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s