AutoCreate Quotesheet with excel format
来源：互联网 发布：linux应用开发前景 编辑：程序博客网 时间：2024/06/05 00:34
从外贸ERP生成全球500强公司的报偿单(包含walmark,kmark,dollar tree...)
/*1.Write to excel button clicked event code
blog:http://blog.csdn.net/chinayaosir
qq:44633197
PB代码功能:把客人报价单数据转到excel的模板文件中
case 'dollar'
xlApp.Application.Workbooks.Open("c:/pbssystem/document/DOLLAR TREE.xls")
xlApp.Application.Visible = true
dollar()
case "other cusotmer...."
*/
/*2.dollar() function code */
/*2.1 define variable */
int r,numrows
string mcountry,pictname
double jpgsize
string cust,fobport,item,itemno
datetime qdate
int catrow,n
long mt,flg
numrows=dw_3.rowcount()
/*2.2choose excel work book and worksheet*/
xls=xlapp.application.activeworkbook
xlsub=xlapp.application.activeworkbook.worksheets[1]
itemno=string(dw_3.object.q_qsheetitem_itemnumber[numrows])
mt=pos(itemno,"/")
if mt>0 then
itemno=replace(itemno,mt,1,"-")
end if
xlsub.name=itemno
//2.3copy excel sheet and rename it with itemnumber
for r=2 to numrows
xlsub.Copy(xls.Sheets(xls.Sheets.Count))
itemno=string(dw_3.object.q_qsheetitem_itemnumber[r - 1])
mt=pos(itemno,"/")
if mt>0 then
itemno=replace(itemno,mt,1,"-")
end if
xls.ActiveSheet.name=itemno
next
//2.4for each wrint each itemnumber information into each sheets of excel
for r=1 to numrows
/*2.4.1 在excel的不同的worksheet切换*/
xlapp.application.activeworkbook.worksheets[r].activate
xlsub=xlapp.application.activeworkbook.worksheets[r]
/*2.4.2写数据到excel*/
xlsub.Cells[3,2]=dw_3.object.q_qsheetitem_qsheetdate[r]
xlsub.Cells[9,2]=dw_3.object.q_quotesheet_quotbuyer[r]
xlsub.Cells[17,3]=dw_3.object.q_qsheetitem_briefdesc[r]
if mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1) >='0' and mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1)<='9' then
xlsub.Cells[17,9]=mid(dw_3.object.q_qsheetitem_itemnumber[r],2)
else
xlsub.Cells[17,9]=mid(dw_3.object.q_qsheetitem_itemnumber[r],3)
end if
xlsub.Cells[17,10]=dw_3.object.q_qsheetitem_upc[r]
xlsub.Cells[21,10]=dw_3.object.q_qsheetitem_master_ctn[r]
xlsub.Cells[21,11]=dw_3.object.q_qsheetitem_inner_box[r]
xlsub.Cells[22,10]=round(dw_3.object.q_qsheetitem_dimension[r],2)
xlsub.Cells[23,4]=round(dw_3.object.q_qsheetitem_price_fob[r],3)
xlsub.Cells[23,10]=round(dw_3.object.q_qsheetitem_grosweight[r]*2.2,2)
xlsub.Cells[24,2]=round(dw_3.object.q_quotesheet_freight_prc[r],3)
xlsub.Cells[25,2]=round(dw_3.object.q_quotesheet_quotemisc[r]/100,4)
xlsub.Cells[26,2]=round(dw_3.object.q_qsheetitem_tariffrate[r],2)
xlsub.Cells[30,10]=dw_3.object.q_qsheetitem_fobport[r]
xlsub.Cells[31,4]=round(dw_3.object.q_qsheetitem_retailofprc[r],3)
xlsub.Cells[31,10]=dw_3.object.q_qsheetitem_tariffofno[r]
mcountry=dw_3.object.q_quotesheet_m_country[r]
if mcountry='H' then
xlsub.Cells[33,10]='HONG KONG'
elseif mcountry='C' then
xlsub.Cells[33,10]='CN'
end if
xlsub.Cells[38,2]=dw_3.object.q_qsheetitem_typepackag[r]
xlsub.Cells[55,2]=dw_3.object.q_qsheetitem_detaildesc[r]
xlsub.Cells[57,2]=dw_3.object.q_qsheetitem_packing[r]
//2.4.3 write product photo into excel
pictname=dw_3.object.i_photoitem_photoname[r]
xlsub.Range("A76").Select
if pictname<>'' and not(isnull(pictname)) then
if flg = 0 then flg = photoexist(pictname)
if FILEEXISTS(pictname) = true then
xlapp.Application.ActiveSheet.Pictures.Insert(string(pictname)).select
xlapp.Application.Selection.ShapeRange.height=160
end if
end if
next
/*write data into excel with walgreen.xls*/
/*
int r,numrows
string mcountry,pictname,ls_anti,itemno
long mt,flg
double jpgsize
string cust,fobport,item
datetime qdate
int catrow,n
numrows=dw_3.rowcount()
xls=xlapp.application.activeworkbook
xlsub=xlapp.application.activeworkbook.worksheets[1]
itemno=string(dw_3.object.q_qsheetitem_itemnumber[numrows])
mt=pos(itemno,"/")
if mt>0 then
itemno=replace(itemno,mt,1,"-")
end if
xlsub.name=itemno
for r=2 to numrows
xlsub.Copy(xls.Sheets(xls.Sheets.Count))
itemno=string(dw_3.object.q_qsheetitem_itemnumber[r - 1])
mt=pos(itemno,"/")
if mt >0 then
itemno=replace(itemno,mt,1,"-")
end if
xls.ActiveSheet.name=itemno
next
for r=1 to numrows
xlapp.application.activeworkbook.worksheets[r].activate
xlsub=xlapp.application.activeworkbook.worksheets[r]
if dw_3.object.q_qsheetitem_is_revise[r]='Y' then
xlsub.Cells[4,35]='REVISED'
xlsub.Cells[4,49]='x'
end if
xlsub.Cells[6,40]=dw_3.object.q_quotesheet_quotbuyer[r]
xlsub.Cells[6,52]=dw_3.object.q_qsheetitem_qsheetdate[r]
pictname=dw_3.object.i_photoitem_photoname[r]
xlsub.Range("AD9").Select
if pictname<>'' and not(isnull(pictname)) then
if flg = 0 then flg = photoexist(pictname)
if FILEEXISTS(pictname) = true then
xlapp.Application.ActiveSheet.Pictures.Insert(string(pictname)).select
xlapp.Application.Selection.ShapeRange.height=150
end if
end if
if mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1) >='0' and mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1)<='9' then
xlsub.Cells[28,8]=mid(dw_3.object.q_qsheetitem_itemnumber[r],2)
xlsub.Cells[50,37]=mid(dw_3.object.q_qsheetitem_itemnumber[r],2)
else
xlsub.Cells[28,8]=mid(dw_3.object.q_qsheetitem_itemnumber[r],3)
xlsub.Cells[50,37]=mid(dw_3.object.q_qsheetitem_itemnumber[r],3)
end if
xlsub.Cells[31,8]=dw_3.object.q_qsheetitem_briefdesc[r]
xlsub.Cells[32,8]=dw_3.object.q_qsheetitem_detaildesc[r]
xlsub.Cells[35,8]=dw_3.object.q_qsheetitem_packing[r]
xlsub.Cells[34,43]=round(dw_3.object.q_qsheetitem_price_fob[r],2)
xlsub.Cells[35,36]=round(dw_3.object.q_quotesheet_freight_prc[r],2)
xlsub.Cells[42,31]=dw_3.object.q_qsheetitem_master_ctn[r]
xlsub.Cells[42,35]=dw_3.object.q_qsheetitem_inner_box[r]
xlsub.Cells[42,39]=round(dw_3.object.compute_2[r],2)
xlsub.Cells[42,45]=round(dw_3.object.q_qsheetitem_dimens_l[r],2)
xlsub.Cells[42,48]=round(dw_3.object.q_qsheetitem_dimens_w[r],2)
xlsub.Cells[42,51]=round(dw_3.object.q_qsheetitem_dimens_h[r],2)
xlsub.Cells[42,54]=round(dw_3.object.q_qsheetitem_dimension[r],2)
xlsub.Cells[46,1]=dw_3.object.q_qsheetitem_typepackag[r]
xlsub.Cells[46,10]=dw_3.object.q_qsheetitem_fobport[r]
mcountry=dw_3.object.q_quotesheet_m_country[r]
if mcountry='C' then
xlsub.Cells[46,19]='CHINA'
elseif mcountry='H' then
xlsub.Cells[46,19]='HONG KONG'
end if
xlsub.Cells[46,28]=dw_3.object.q_qsheetitem_qs_minimu[r]
xlsub.Cells[50,47]=round(dw_3.object.q_qsheetitem_price_fob[r],2)
xlsub.Cells[46,37]=dw_3.object.q_quotesheet_leadtime[r]
xlsub.Cells[50,1]=dw_3.object.q_qsheetitem_briefdesc[r]
xlsub.Cells[51,8]=dw_3.object.q_qsheetitem_cartonupc[r]
xlsub.Cells[57,38]=dw_3.object.q_qsheetitem_material[r]
if dw_3.object.q_qsheetitem_tariffofno[r]='' then
cust=dw_3.object.q_qsheetitem_customerid[r]
qdate=dw_3.object.q_qsheetitem_qsheetdate[r]
fobport=dw_3.object.q_qsheetitem_fobport[r]
item=dw_3.object.q_qsheetitem_itemnumber[r]
select count(*) into :catrow from q_qsmtariff
where customerid=:cust and qsheetdate=:qdate
and fobport=:fobport and itemnumber=:item ;
if catrow>0 then
for n=1 to catrow
xlsub.Cells[57+(n - 1),1]=dw_3.object.r_detail[r].object.briefdesc[n]
xlsub.Cells[57+(n - 1),14]=dw_3.object.r_detail[r].object.tariffnumber[n]
xlsub.Cells[57+(n - 1),32]=round(dw_3.object.r_detail[r].object. tariffrate[n]/100,4)
xlsub.Cells[57+(n - 1),24]=round(dw_3.object.r_detail[r].object.itemvalue[n]/dw_3.object.q_qsheetitem_price_fob[r],4)
next
xlsub.Cells[36,36]=round(dw_3.object.q_qsheetitem_tariffrate[r]/100,4)
end if
else
xlsub.Cells[57,14]=dw_3.object.q_qsheetitem_tariffofno[r]
xlsub.Cells[57,24]='1'
xlsub.Cells[57,32]=round(dw_3.object.q_qsheetitem_tariffrate[r]/100,4)
xlsub.Cells[36,36]=round(dw_3.object.q_qsheetitem_tariffrate[r]/100,4)
end if
if dw_3.object.q_qsheetitem_upc[r]="" then
cust=dw_3.object.q_qsheetitem_customerid[r]
qdate=dw_3.object.q_qsheetitem_qsheetdate[r]
fobport=dw_3.object.q_qsheetitem_fobport[r]
item=dw_3.object.q_qsheetitem_itemnumber[r]
select count(*) into :catrow from q_qsupc
where customerid=:cust and qsheetdate=:qdate
and fobport=:fobport and itemnumber=:item ;
if catrow>0 then
for n=1 to catrow
xlsub.Cells[86+(n - 1),1]=dw_3.object.r_retail[r].object.description[n]
xlsub.Cells[86+(n - 1),20]=dw_3.object.r_retail[r].object.upc[n]
xlsub.Cells[86+(n - 1),35]=dw_3.object.r_retail[r].object.itemno[n]
xlsub.Cells[86+(n - 1),43]=dw_3.object.r_retail[r].object.quantity[n]
next
end if
else
xlsub.Cells[50,20]=dw_3.object.q_qsheetitem_upc[r]
end if
ls_anti=dw_3.object.q_qsheetitem_costmeth[r]
if ls_anti='Y' then
xlsub.Cells[64,45]='X'
elseif ls_anti="N" then
xlsub.Cells[64,51]='X'
end if
next
*/
/*write data into excel with winn dixie */
int r,numrows
string pictname,countrycode,itemno
long mt,flg
double jpgsize
int li_inner
string cust,fobport,item,port,a
datetime qdate
int catrow,n
numrows=dw_3.rowcount()
xls=xlapp.application.activeworkbook
xlsub=xlapp.application.activeworkbook.worksheets[1]
itemno=string(dw_3.object.q_qsheetitem_itemnumber[numrows])
mt=pos(itemno,"/")
if mt>0 then
itemno=replace(itemno,mt,1,"-")
end if
xlsub.name=itemno
for r=2 to numrows
xlsub.Copy(xls.Sheets(xls.Sheets.Count))
itemno=string(dw_3.object.q_qsheetitem_itemnumber[r - 1])
mt=pos(itemno,"/")
if mt >0 then
itemno=replace(itemno,mt,1,"-")
end if
xls.ActiveSheet.name=itemno
next
for r=1 to numrows
xlapp.application.activeworkbook.worksheets[r].activate
xlsub=xlapp.application.activeworkbook.worksheets[r]
if dw_3.object.q_qsheetitem_is_revise[r]='Y' then
xlsub.Cells[4,7]='REVISED'
xlsub.Cells[4,8]=dw_3.object.q_qsheetitem_revisedate[r]
end if
xlsub.Cells[33,10]=dw_3.object.q_qsheetitem_costmeth[r]
if mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1) >='0' and mid(dw_3.object.q_qsheetitem_itemnumber[r],3,1)<='9' then
xlsub.Cells[35,4]=mid(dw_3.object.q_qsheetitem_itemnumber[r],2)
else
xlsub.Cells[35,4]=mid(dw_3.object.q_qsheetitem_itemnumber[r],3)
end if
xlsub.Cells[37,4]=dw_3.object.q_qsheetitem_briefdesc[r]
xlsub.Cells[77,5]=round(dw_3.object.q_qsheetitem_retailofprc[r],2)
countrycode=dw_3.object.q_quotesheet_m_country[r]
if countrycode='H' then
xlsub.Cells[39,10]='HONG KONG'
elseif countrycode='C' then
xlsub.Cells[39,10]='CHINA'
end if
xlsub.Cells[35,10]=dw_3.object.q_qsheetitem_qsheetdate[r]
// xlsub.Cells[24,8]=dw_3.object.q_qsheetitem_briefdesc[r]
xlsub.Cells[61,5]=round(dw_3.object.q_qsheetitem_price_fob[r],4)
xlsub.Cells[53,4]=round(dw_3.object.q_qsheetitem_dimens_l[r],2)
xlsub.Cells[55,4]=round(dw_3.object.q_qsheetitem_dimens_w[r],2)
xlsub.Cells[51,6]=round(dw_3.object.q_qsheetitem_dimens_h[r],2)
// xlsub.Cells[59,5]=dw_3.object.q_qsheetitem_fobport[r]
port=dw_3.object.q_qsheetitem_fobport[r]
if port='YANTIAN' then
xlsub.Cells[59,5]='Yantian '
elseif port='HONG KONG' then
xlsub.Cells[59,5]='Hong Kong '
elseif port='SHENZHEN' then
xlsub.Cells[59,5]='Shenzhen '
elseif port='XIAMEN' then
xlsub.Cells[59,5]='Xiamen '
elseif port='SHANGHAI' then
xlsub.Cells[59,5]='Shanghai '
elseif port='NINGBO' then
xlsub.Cells[59,5]='Ningbo'
elseif port='FUZHOU' then
xlsub.Cells[59,5]='Fuzhou'
elseif port='QINGDAO' then
xlsub.Cells[59,5]='Qingdao'
elseif port='GUANGZHOU' then
xlsub.Cells[59,5]='Guangzhou'
end if
xlsub.Cells[41,10]=dw_3.object.q_qsheetitem_typepackag[r]
xlsub.Cells[45,4]=dw_3.object.q_qsheetitem_type_pkg[r]
xlsub.Cells[51,4]=dw_3.object.q_qsheetitem_master_ctn[r]
xlsub.Cells[55,6]=round(dw_3.object.q_qsheetitem_dimension[r],2)
xlsub.Cells[53,6]=dw_3.object.grosweight[r]
xlsub.Cells[49,4]=dw_3.object.q_qsheetitem_upc[r]
if dw_3.object.q_qsheetitem_tariffofno[r]='' then
cust=dw_3.object.q_qsheetitem_customerid[r]
qdate=dw_3.object.q_qsheetitem_qsheetdate[r]
fobport=dw_3.object.q_qsheetitem_fobport[r]
item=dw_3.object.q_qsheetitem_itemnumber[r]
select count(*) into :catrow from q_qsmtariff
where customerid=:cust and qsheetdate=:qdate
and fobport=:fobport and itemnumber=:item ;
if catrow>0 then
// xlsub.Cells[29,19]=round(dw_3.object.r_detail[r].object.compute_2[1],2)
// xlsub.Cells[29,4]='SEE ATTACHED'
xlsub.Cells[91,2]='Brief Desc'
xlsub.Cells[91,5]='Tariff Number'
xlsub.Cells[91,7]='Rate'
xlsub.Cells[91,8]='Value'
xlsub.Cells[91,9]='Duty'
for n=1 to catrow
xlsub.Cells[92+(n - 1),2]=dw_3.object.r_detail[r].object.briefdesc[n]
xlsub.Cells[92+(n - 1),5]=dw_3.object.r_detail[r].object.tariffnumber[n]
xlsub.Cells[92+(n - 1),7]=round(dw_3.object.r_detail[r].object.tariffrate[n],4)/100
xlsub.Cells[92+(n - 1),8]=round(dw_3.object.r_detail[r].object.itemvalue[n],4)
xlsub.Cells[92+(n - 1),9]=round(dw_3.object.r_detail[r].object.dutyeach[n],4)
next
xlsub.Cells[92+n - 1,2]='TOTAL'
xlsub.Cells[92+n - 1,9]=round(dw_3.object.r_detail[r].object.compute_3[1],4)
xlsub.Cells[63,5]=round(dw_3.object.q_qsheetitem_tariffrate[r]/100,4)
end if
else
xlsub.Cells[63,5]=round(dw_3.object.q_qsheetitem_tariffrate[r]/100,4)
xlsub.Cells[79,4]=dw_3.object.q_qsheetitem_tariffofno[r]
end if
// xlsub.Cells[63,5]=dw_3.object.q_qsheetitem_tariffrate[r]
// xlsub.Cells[79,4]=dw_3.object.q_qsheetitem_tariffofno[r]
xlsub.Cells[81,4]=dw_3.object.q_qsheetitem_qs_minimu[r]
// xlsub.Cells[87,2]=dw_3.object.q_qsheetitem_detaildesc[r]
a=dw_3.object.q_qsheetitem_detaildesc[r] + '~n' + dw_3.object.q_qsheetitem_packing[r]
xlsub.Cells[87,2]=a
pictname=dw_3.object.i_photoitem_photoname[r]
xlsub.Range("H57").Select
if pictname<>'' and not(isnull(pictname)) then
if flg = 0 then flg = photoexist(pictname)
if FILEEXISTS(pictname) = true then
xlapp.Application.ActiveSheet.Pictures.Insert(string(pictname)).select
xlapp.Application.Selection.ShapeRange.height=240
end if
end if
next
- AutoCreate Quotesheet with excel format
- database autocreate winrar file with window plan-task
- Excel Format
- Download a report to excel with format (border, color cell, etc)
- Read and write excel file with format by pandas and xlwt libraries of python
- MICROSOFT EXCEL FILE FORMAT
- EXCEL FILE FORMAT 1
- Excel User define format
- Android string format with html
- Format the array with hashes
- Read date format from Excel
- Excel文件格式（MICROSOFT EXCEL FILE FORMAT）
- TimesTen的AutoCreate的一个注意点
- Change Video Signal Format with DirectX library
- Validator FIX message Format With Regular Expression
- I Can Do That With PROC FORMAT
- conversion to dalvik format failed with ...
- conversion to dalvik format failed with ...
- SSH常见错误拾遗
- 用Java输出分辨率300dpi以上的TIFF图像
- Grove——.NET中的ORM实现
- 一个有关perl hash结构的how to
- 关于IncludeAction的思考
- AutoCreate Quotesheet with excel format
- 使用URLDownloadToFile http 下载文件
- 2年后的第一个web project--struts 2.0
- Group By 和 Having, Where ,Order by语句的执行顺序
- 青蛙过河
- 转一篇EXTJS很好的文章
- 理解Qt多线程类
- 计算阶乘
- [下载]40个轻量级JavaScript 库（下）
