YA KLU MO SAMA PERSIS YA HARUS DIATUR SECARA MANUAL.. KLU DW LANGSUNG DI EXPORT
PASTI NGK AKN SESUAI
CONTOH :
OLEObject ole_excel
string ls_place[33] =
{'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG'}
integer i,a,li_result,ex,li_no
decimal{0} ld_jams[12],ld_upah[12]
decimal ld_totaljams,ld_totalupah
string
ls_asc,ls_yy,ls_ym,ls_select,ls_pnl_no,ls_KPJ_NO,ls_PNL_NM,ls_BIRTH_D,ls_HIRE_D,ls_UP_D
dw_1.accepttext()
// dw_2.accepttext()
ls_yy = dw_1.getitemstring(dw_1.getrow(),'as_yyyy')
ls_select = dw_1.getitemstring(dw_1.getrow(),'as_select')
ole_excel = CREATE OLEObject
li_result = ole_excel.ConnectToNewObject("excel.application")
ole_excel.Workbooks.Add
ole_excel.Application.Visible = True
ole_excel.ActiveWindow.DisplayGridlines = True
ole_excel.ActiveSheet.PageSetup.LeftMargin =
ole_excel.Application.InchesToPoints(0.196850393700787)
ole_excel.ActiveSheet.PageSetup.RightMargin =
ole_excel.Application.InchesToPoints(0.196850393700787)
ole_excel.ActiveSheet.PageSetup.TopMargin =
ole_excel.Application.InchesToPoints(0.393700787401575)
ole_excel.ActiveSheet.PageSetup.BottomMargin =
ole_excel.Application.InchesToPoints(0.393700787401575)
ole_excel.Cells.Select
ole_excel.Selection.Font.Name = 'Times New Roman'
ole_excel.Cells.Font.size = 8
ole_excel.Selection.columnWidth = 10
ls_asc = ls_place[1] + string(1)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'IURAN DUMTK 2% PT ...... - ' +
trim(gs_factno)
ls_asc = ls_place[1] + string(1) + ':' + ls_place[33] + string(1)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ole_excel.Range(ls_asc).Font.Size = 14
ole_excel.Selection.Font.Bold = True
ole_excel.Selection.HorizontalAlignment = 3 //Center
ls_asc = ls_place[1] + string(2)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'TAHUN - ' + ls_yy
ls_asc = ls_place[1] + string(2) + ':' + ls_place[33] + string(2)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ole_excel.Range(ls_asc).Font.Size = 14
ole_excel.Selection.Font.Bold = True
ole_excel.Selection.HorizontalAlignment = 3 //Center
ls_asc = ls_place[1] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'NO'
ls_asc = ls_place[2] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'KPJ'
ls_asc = ls_place[3] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'NIK'
ls_asc = ls_place[4] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'NAMA'
ls_asc = ls_place[5] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'LAHIR'
ls_asc = ls_place[6] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'JANUARI'
ls_asc = ls_place[6] + string(4) + ':' + ls_place[7] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[8] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'PEBRUARI'
ls_asc = ls_place[8] + string(4) + ':' + ls_place[9] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[10] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'MARET'
ls_asc = ls_place[10] + string(4) + ':' + ls_place[11] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[12] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'APRIL'
ls_asc = ls_place[12] + string(4) + ':' + ls_place[13] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[14] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'MEI'
ls_asc = ls_place[14] + string(4) + ':' + ls_place[15] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[16] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'JUNI'
ls_asc = ls_place[16] + string(4) + ':' + ls_place[17] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[18] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'JULI'
ls_asc = ls_place[18] + string(4) + ':' + ls_place[19] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[20] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'AGUSTUS'
ls_asc = ls_place[20] + string(4) + ':' + ls_place[21] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[22] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'SEPTEMBER'
ls_asc = ls_place[22] + string(4) + ':' + ls_place[23] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[24] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'OKTOBER'
ls_asc = ls_place[24] + string(4) + ':' + ls_place[25] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[26] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'NOVEMBER'
ls_asc = ls_place[26] + string(4) + ':' + ls_place[27] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[28] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'DESEMBER'
ls_asc = ls_place[28] + string(4) + ':' + ls_place[29] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[30] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'TOTAL'
ls_asc = ls_place[30] + string(4) + ':' + ls_place[31] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.selection.merge
ls_asc = ls_place[32] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'IN'
ls_asc = ls_place[33] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = 'OUT'
ls_asc = ls_place[1] + string(4) + ':' + ls_place[33] + string(4)
ole_excel.Range(ls_asc).Select
ole_excel.Selection.Font.Bold = True
ole_excel.Selection.HorizontalAlignment = 3 //Center
DECLARE CUR_PNL_NO CURSOR FOR
SELECT DISTINCT
"PCI_ADMIN"."EM_PNL"."PNL_NO",
"PCI_ADMIN"."EM_PNL"."KPJ_NO",
"PCI_ADMIN"."EM_PNL"."PNL_NM",
"PCI_ADMIN"."EM_PNL"."BIRTH_D",
"PCI_ADMIN"."EM_PNL"."HIRE_D",
"PCI_ADMIN"."EM_PNL"."UP_D"
FROM "PCI_ADMIN"."EM_PNL",
(
( SELECT "PCI_ADMIN"."EM_MONPAY"."COMP_NO" "COMPANY_CODE",
"PCI_ADMIN"."EM_MONPAY"."FACT_NO" "FACTORY_CODE",
"PCI_ADMIN"."EM_MONPAY"."PNL_NO" "EMPLOYEE_ID",
"PCI_ADMIN"."EM_MONPAY"."CARD_YM" "PAY_YM",
"PCI_ADMIN"."EM_MONPAY"."LABOR" "PAY_LABOR"
FROM "PCI_ADMIN"."EM_MONPAY"
WHERE "PCI_ADMIN"."EM_MONPAY"."FACT_NO" = :gs_factno and
SUBSTR("PCI_ADMIN"."EM_MONPAY"."CARD_YM",1,4) =
:ls_yy and
"PCI_ADMIN"."EM_MONPAY"."LABOR" > 0
)
UNION
( SELECT "PCI_ADMIN"."EM_PHK"."COMP_NO" "COMPANY_CODE",
"PCI_ADMIN"."EM_PHK"."FACT_NO" "FACTORY_CODE",
"PCI_ADMIN"."EM_PHK"."PNL_NO" "EMPLOYEE_ID",
SUBSTR("PCI_ADMIN"."EM_PHK"."CARD_YM",1,6)
"PAY_YM",
"PCI_ADMIN"."EM_PHK"."LABOR" "PAY_LABOR"
FROM "PCI_ADMIN"."EM_PHK","PCI_ADMIN"."EM_PNL"
WHERE "PCI_ADMIN"."EM_PHK"."COMP_NO" =
"PCI_ADMIN"."EM_PNL"."COMP_NO" AND
"PCI_ADMIN"."EM_PHK"."FACT_NO" =
"PCI_ADMIN"."EM_PNL"."FACT_NO" AND
"PCI_ADMIN"."EM_PHK"."PNL_NO" =
"PCI_ADMIN"."EM_PNL"."PNL_NO" AND
"PCI_ADMIN"."EM_PHK"."FACT_NO" = :gs_factno and
SUBSTR("PCI_ADMIN"."EM_PHK"."CARD_YM",1,4) = :ls_yy
and
"PCI_ADMIN"."EM_PHK"."REMARKS" = '9' and
"PCI_ADMIN"."EM_PHK"."LABOR" > 0
)
)X1
WHERE ( "PCI_ADMIN"."EM_PNL"."COMP_NO" = X1.COMPANY_CODE ) and
( "PCI_ADMIN"."EM_PNL"."COMP_NO" = X1.COMPANY_CODE ) and
( "PCI_ADMIN"."EM_PNL"."FACT_NO" = X1.FACTORY_CODE ) and
( "PCI_ADMIN"."EM_PNL"."PNL_NO" = X1.EMPLOYEE_ID ) and
(
( :ls_select = '0' )
or
( :ls_select = '1' and "PCI_ADMIN"."EM_PNL"."UP_D" IS
NULL )
or
( :ls_select = '2' and "PCI_ADMIN"."EM_PNL"."UP_D" IS
NOT NULL)
);
OPEN CUR_PNL_NO;
FETCH CUR_PNL_NO INTO
:ls_pnl_no,:ls_KPJ_NO,:ls_PNL_NM,:ls_BIRTH_D,:ls_HIRE_D,:ls_UP_D ;
i = 5
li_no = 1
DO WHILE SQLCA.SQLCode = 0
ls_asc = ls_place[1] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = li_no
ls_asc = ls_place[2] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_KPJ_NO
ls_asc = ls_place[3] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_pnl_no
ls_asc = ls_place[4] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_PNL_NM
ls_asc = ls_place[5] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_BIRTH_D
FOR a =1 TO 12
ld_jams[a] = 0
ld_upah[a] = 0
NEXT
ld_totaljams = 0
ld_totalupah = 0
ex = 6
FOR a =1 TO 12
ls_ym = ls_yy + string(a,'00')
SELECT DISTINCT
NVL(X1.PAY_LABOR,0),
NVL(X1.PAY_LABOR * 50,0) UPAH
INTO
:ld_jams[a],
:ld_upah[a]
FROM
(
( SELECT "PCI_ADMIN"."EM_MONPAY"."COMP_NO" "COMPANY_CODE",
"PCI_ADMIN"."EM_MONPAY"."FACT_NO"
"FACTORY_CODE",
"PCI_ADMIN"."EM_MONPAY"."PNL_NO"
"EMPLOYEE_ID",
"PCI_ADMIN"."EM_MONPAY"."CARD_YM"
"PAY_YM",
"PCI_ADMIN"."EM_MONPAY"."LABOR"
"PAY_LABOR"
FROM "PCI_ADMIN"."EM_MONPAY"
WHERE "PCI_ADMIN"."EM_MONPAY"."FACT_NO" = :gs_factno
and
SUBSTR("PCI_ADMIN"."EM_MONPAY"."CARD_YM",1,6)
= :ls_ym and
"PCI_ADMIN"."EM_MONPAY"."LABOR" > 0 )
UNION
( SELECT "PCI_ADMIN"."EM_PHK"."COMP_NO" "COMPANY_CODE",
"PCI_ADMIN"."EM_PHK"."FACT_NO"
"FACTORY_CODE",
"PCI_ADMIN"."EM_PHK"."PNL_NO" "EMPLOYEE_ID",
SUBSTR("PCI_ADMIN"."EM_PHK"."CARD_YM",1,6)
"PAY_YM",
"PCI_ADMIN"."EM_PHK"."LABOR" "PAY_LABOR"
FROM "PCI_ADMIN"."EM_PHK","PCI_ADMIN"."EM_PNL"
WHERE "PCI_ADMIN"."EM_PHK"."COMP_NO" =
"PCI_ADMIN"."EM_PNL"."COMP_NO" AND
"PCI_ADMIN"."EM_PHK"."FACT_NO" =
"PCI_ADMIN"."EM_PNL"."FACT_NO" AND
"PCI_ADMIN"."EM_PHK"."PNL_NO" =
"PCI_ADMIN"."EM_PNL"."PNL_NO" AND
"PCI_ADMIN"."EM_PHK"."FACT_NO" = :gs_factno
and
SUBSTR("PCI_ADMIN"."EM_PHK"."CARD_YM",1,6) =
:ls_ym and
"PCI_ADMIN"."EM_PHK"."REMARKS" = '9' and
"PCI_ADMIN"."EM_PHK"."LABOR" > 0 )
)X1
WHERE
X1.EMPLOYEE_ID = :ls_pnl_no;
ls_asc = ls_place[ex] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ld_jams[a]
ld_totaljams = ld_totaljams + ld_jams[a]
ex++
ls_asc = ls_place[ex] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ld_upah[a]
ld_totalupah = ld_totalupah + ld_upah[a]
ex++
NEXT
ls_asc = ls_place[30] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ld_totaljams
ls_asc = ls_place[31] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ld_totalupah
ls_asc = ls_place[32] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_HIRE_D
ls_asc = ls_place[33] + string(i)
ole_excel.Range(ls_asc).Select
ole_excel.ActiveCell.FormulaR1C1 = ls_UP_D
FETCH CUR_PNL_NO INTO
:ls_pnl_no,:ls_KPJ_NO,:ls_PNL_NM,:ls_BIRTH_D,:ls_HIRE_D,:ls_UP_D ;
i++
li_no++
LOOP
ls_asc = ls_place[6] + string(4) + ':' + ls_place[31] + string(i - 1)
ole_excel.Range(ls_asc).Select
ole_excel.Selection.NumberFormat = '#,##0'
ls_asc = ls_place[1] + string(4) + ':' + ls_place[33] + string(i - 1)
ole_excel.Range(ls_asc).Select
ole_excel.Range(ls_asc).EntireColumn.AutoFit
ole_excel.Selection.Borders(1).LineStyle = 1
ole_excel.Selection.Borders(2).LineStyle = 1
ole_excel.Selection.Borders(3).LineStyle = 1
ole_excel.Selection.Borders(4).LineStyle = 1
CLOSE CUR_PNL_NO;
BY
HENDRY
--- On Tue, 7/15/08, b154 474 <[EMAIL PROTECTED]> wrote:
From: b154 474 <[EMAIL PROTECTED]>
Subject: [indopb] HELP...Ada yang sudah pernah melakukan export laporan dw ke
excel di powerbuilder versi 5??
To: [email protected]
Date: Tuesday, July 15, 2008, 10:30 PM
dear PBers,
saya ada kesulitan untuk mengexport laporan2 aplikasi saya yg di powerbuilder 5
ke excel dan kebutuhannya adalah hasil export harus sama persis seperti
tampilan report...
saya coba pake dw_xx.saveas( blabla) berhasil tp tampilannya tidak bagus, kira2
ada yang bisa membantu memberikan solusi utk kasus ini?
terimakasih,
Asep P
