/*****************************************************
function:datawindow write into excel with powerbuilder
author::chinayaosir QQ:44633197
tool:pb7.0
date:05/05/2005
//code list
//1.total row check
//2.use excel object check
//3.open execl file
//4.auto add row in excel
//5.read data into array var list
//6.array var data into excel
//6.1 write head data
//6.2write each item detail
//7. destroy excel object
*******************************************************/
pointer oldpointer
oldpointer = setpointer(HourGlass!)
//1.total row check
if t_1.invoi.dw_4.rowcount()<=0 then
return
end if
//2.use excel object check
OLEObject xlapp,xlsub,xls
xlApp=create oleobject
int ret
ret = xlapp.ConnectToNewObject( "Excel.Application" )
if ret < 0 then
MessageBox("Warning!","Connect to Excel Failed")
return
end if
//3.open execl file
string customer
customer=trim(t_1.print.dw_1.object.customerid[1])
string ls_excel_name
ls_excel_name="C:/BAB_pbs/excel/invoice.xls"
xlApp.Application.Workbooks.Open(ls_excel_name)
xlApp.Application.Visible = true
integer i,j
real li_total_1,li_total_2,li_total_3
li_total_1=0
li_total_2=0
if ii_dw_4 > 0 then
i = 1
else
return 0
end if
string cpayment,ls_str_10
if (customer="B.A.B.") or (customer="B.A.B./CA") or (customer="B.A.B./UK") then
cpayment="Payment term: 30 days from shipment"
else
cpayment=""
end if
//choose invoice
if cbx_1.checked=true then
xlapp.application.activeworkbook.worksheets[1].activate
xlsub=xlapp.application.activeworkbook.worksheets[1]
xlsub.cells(7,3).value = t_1.invoi.dw_4.object.v_printvoice_oceanvess[1]
xlsub.cells(8,3).value =cpayment
xlsub.cells(9,3).value = t_1.invoi.dw_4.object.b_customerid_busi_name[1]
xlsub.cells(10,3).value = t_1.invoi.dw_4.object.b_customerid_customaddr[1]
xlsub.cells(2,7).value = t_1.invoi.dw_4.object.invnumber[1]
xlsub.cells(3,7).value = t_1.invoi.dw_4.object.page_1[1]
xlsub.cells(4,7).value = date( t_1.invoi.dw_4.object.date_1[1])
xlsub.cells(7,7).value = t_1.invoi.dw_4.object.compute_7[1]
j=13 // excel begin rows
string oc1,po1,item1,upc1,hts1,desc1,qty1,price1,amt1,fty1
for i=1 to ii_dw_4
oc1 =trim(t_1.invoi.dw_4.object.oc_number[i])
po1 =trim(t_1.invoi.dw_4.object.o_itemallrow_customecpo[i])
item1 =t_1.invoi.dw_4.object.compute_1[i]
upc1 =trim(string(t_1.invoi.dw_4.object.i_itembasic_itemofupc[i]) )
hts1 =trim(string(t_1.invoi.dw_4.object.o_bab_itemofoc_other3[i]) )
desc1 =string(right((t_1.invoi.dw_4.object.detaildesc[i]),55))
desc1 =trim(desc1)
qty1 =string( t_1.invoi.dw_4.object.expression[i]) + string(t_1.invoi.dw_4.object.v_viewinvoic_itempcunit[i]," ")
price1=string( t_1.invoi.dw_4.object.priceoffob[i],"#,##0.0000") + "/" + trim(t_1.invoi.dw_4.object.v_viewinvoic_itempcunit[i])
amt1 =string(t_1.invoi.dw_4.object.compute_2[i])
if isnull(oc1) then oc1=""
if isnull(po1) then po1=""
if isnull(item1) then item1=""
if isnull(upc1) then upc1=""
if isnull(hts1) then hts1=""
if isnull(desc1) then desc1=""
if isnull(qty1) then qty1=""
if isnull(price1) then price1=""
if isnull(amt1) then amt1=""
xlsub.cells(j,1).value = oc1
xlsub.cells(j,2).value = po1
xlsub.cells(j,3).value = item1
xlsub.cells(j,4).value = upc1
xlsub.cells(j+1,4).value =desc1
xlsub.cells(j,5).value = qty1
xlsub.cells(j,6).value = price1
xlsub.cells(j,7).value = amt1
//add with yao 5/5/2009
if customer="B.A.B./UK" then
xlsub.cells(j,4).value = upc1 +" HTS#"+hts1
end if
//add with yao 04/2/2009
if customer="B.A.B./JP" then
string jpupc
jpupc =string(t_1.invoi.dw_4.object.i_itembasic_specialupc[i])
if isnull(jpupc) then jpupc=""
xlsub.cells(j,4).value = jpupc+" / "+right(upc1,5)
end if
if customer="B.A.B./RU" then
//fty1 =string(t_1.invoi.dw_4.object.v_viewinvoic_factory_id[i])
//if isnull(fty1) then fty1=""
//xlsub.cells(j+1,1).value = fty1
xlsub.cells(j,4).value = upc1 +" HTS#"+hts1
end if
li_total_1 = li_total_1+real(t_1.invoi.dw_4.object.expression[i])
li_total_2 = li_total_2 + real(t_1.invoi.dw_4.object.compute_2[i])
j=j+2
oc1="";po1="";item1="";upc1="";hts1="";desc1="";qty1="";price1="";amt1=""
next
// format a line with borlad
ls_str_10 = "A"+string(j - 1 )+":"+"G"+string( j - 1 )
xlapp.application.range(ls_str_10).select
xlapp.application.selection.Borders(9).weight=2
xlsub.cells(j,1).value = "TOTAL : "
xlsub.cells(j,5).value = string(li_total_1)
xlsub.cells(j,7).value = string(li_total_2,"##,###0.0000")
xlsub.cells(j+1,1).value = "TOTAL UNITED STATES DOLLARS:"
xlsub.cells(j+2,1).value = t_1.invoi.dw_4.object.t_22.text
xlsub.cells(j+3,1).value = "***COUNTRY OF ORIGIN:CHINA"
if customer="B.A.B./RU" then
xlsub.cells(j+4,1).value ="***SUPPLY CONTRACT NO.22 JENE,2006"
end if
end if
//choose packing list
if cbx_2.checked=true then
xlapp.application.activeworkbook.worksheets[2].activate
xlsub=xlapp.application.activeworkbook.worksheets[2]
//write head data
xlsub.cells(7,3+1).value = t_1.invoi.dw_4.object.v_printvoice_oceanvess[1]
xlsub.cells(8,3+1).value =cpayment
xlsub.cells(9,3+1).value = t_1.invoi.dw_4.object.b_customerid_busi_name[1]
xlsub.cells(10,3+1).value = t_1.invoi.dw_4.object.b_customerid_customaddr[1]
xlsub.cells(2,7+1).value = t_1.invoi.dw_4.object.invnumber[1]
xlsub.cells(3,7+1).value = t_1.packi.dw_pac.object.page_1[1]
xlsub.cells(4,7+1).value = date( t_1.invoi.dw_4.object.date_1[1])
xlsub.cells(13,1).value = "CONTAINER : "
xlsub.cells(13,2).value =string(t_1.packi.dw_pac.object.v_bab_invoice_container[1])
xlsub.cells(13,3+1).value = "SEAL:"
xlsub.cells(13,4+1).value =string(t_1.packi.dw_pac.object.v_bab_invoice_seal_no[1])
int jj=14,xx=0,max_i
max_i=t_1.packi.dw_pac.rowcount()
string fty,ctns,allctns,po,itemno,upc2,hts2,desc2,CARTON,qty,nw,gw,cbm,cbm_v
for xx=1 to max_i
ctns =string(t_1.packi.dw_pac.object.compute_2[xx])
allctns=string(t_1.packi.dw_pac.object.compute_1[xx])
po=string(t_1.packi.dw_pac.object.o_itemallrow_customeupc[xx])
itemno=string(t_1.packi.dw_pac.object.itemnumber[xx])
upc2 =string(t_1.packi.dw_pac.object.i_itembasic_itemofupc[xx])
if customer="B.A.B./RU" then
hts2 =" HTS#"+string(t_1.packi.dw_pac.object.o_bab_itemofoc_other3[xx])
cbm_v =string(t_1.packi.dw_pac.object.compute_cbm[xx],"#.####")
end if
desc2 =string(right((t_1.packi.dw_pac.object.detaildesc[xx]),55))
desc2 =trim(desc2)
CARTON=string(t_1.packi.dw_pac.object.v_viewinvoic_tt_mastctn[xx])
qty =string(t_1.packi.dw_pac.object.expression[xx])+space(1)+string(t_1.packi.dw_pac.object.v_viewinvoic_itempcunit[xx])
nw =string(t_1.packi.dw_pac.object.compute_5[xx],"#,###.##")
gw =string(t_1.packi.dw_pac.object.compute_6[xx],"#,###.##")
cbm =string(t_1.packi.dw_pac.object.dimensi_l[xx])+' X '+string(t_1.packi.dw_pac.object.dimensi_w[xx])+' X '+string(t_1.packi.dw_pac.object.dimensi_h[xx])
if isnull(fty) then fty=""
if isnull(ctns) then ctns=""
if isnull(allctns) then allctns=""
if isnull(po) then po=""
if isnull(itemno) then itemno=""
if isnull(upc2) then upc2=""
if isnull(hts2) then hts2=""
if isnull(desc2) then desc2=""
if isnull(CARTON) then CARTON=""
if isnull(qty) then qty=""
if isnull(nw) then nw=""
if isnull(gw) then gw=""
if isnull(cbm_v) then cbm_v=""
xlsub.cells(jj,1).value =ctns
xlsub.cells(jj,1+1).value =allctns
xlsub.cells(jj,2+1).value =po
xlsub.cells(jj,3+1).value =itemno
xlsub.cells(jj,4+1).value =upc2+hts2
xlsub.cells(jj+1,4+1).value =desc2
//add with yao 04/2/2009
if customer="B.A.B./JP" then
string jpupc2
jpupc2 =string(t_1.invoi.dw_4.object.i_itembasic_specialupc[xx])
if isnull(jpupc2) then jpupc2=""
xlsub.cells(jj,4+1).value = jpupc2+" / "+right(upc2,5)
end if
xlsub.cells(jj,5+1).value =CARTON
xlsub.cells(jj,6+1).value =qty
xlsub.cells(jj,7+1).value =nw
xlsub.cells(jj,8+1).value =gw
xlsub.cells(jj,9+1).value =cbm
xlsub.cells(jj+1,9+1).value =cbm_v
jj=jj+2
fty="";ctns="";allctns="";po="";itemno="";upc2="";desc2="";CARTON="";qty="";nw="";gw="";cbm="";cbm_v=""
next
ls_str_10 = "A"+string(j + 1 )+":"+"J"+string( j + 1 )
xlapp.application.range(ls_str_10).select
xlapp.application.selection.Borders(9).weight=2
xlsub.cells(jj+1,1).value ="SUB-TOTAL:"
xlsub.cells(jj+1,2).value =string(t_1.packi.dw_pac.object.compute_10[1],"#,###")+" CTNS"
xlsub.cells(jj+1,6+1).value =string(t_1.packi.dw_pac.object.compute_11[1],"#,###.00")
xlsub.cells(jj+1,7+1).value =string(t_1.packi.dw_pac.object.compute_12[1],"#,###.00")
xlsub.cells(jj+1,8+1).value =string(t_1.packi.dw_pac.object.compute_13[1],"#,###.00")
xlsub.cells(jj+2,1).value ="TOTAL:"
xlsub.cells(jj+2,2).value =string(t_1.packi.dw_pac.object.compute_10[1],"#,###")+" CTNS"
xlsub.cells(jj+2,6+1).value =string(t_1.packi.dw_pac.object.compute_11[1],"#,###.00")
xlsub.cells(jj+2,7+1).value =string(t_1.packi.dw_pac.object.compute_12[1],"#,###.00")
xlsub.cells(jj+2,8+1).value =string(t_1.packi.dw_pac.object.compute_13[1],"#,###.00")
xlsub.cells(jj+3,1).value ="***COUNTRY OF ORIGIN: CHINA"
if customer="B.A.B./RU" then
xlsub.cells(jj+1,9+1).value=string(t_1.packi.dw_pac.object.compute_cbm_total[1],"#,###.##")
xlsub.cells(jj+2,9+1).value=string(t_1.packi.dw_pac.object.compute_cbm_total[1],"#,###.##")
xlsub.cells(jj+4,1).value ="***SUPPLY CONTRACT NO.22 JENE,2006"
end if
end if
DESTROY xlapp
setpointer(oldpointer)