三、导数据到EXCEL的方法:
1> 先COPY行,在写数据到行的单元格
做一个EXCEL模板 启用巨集 巨集代码如下:
Sub copy()
Range("a4:r4").Select //复制行
Selection.copy
End Sub
1> 先COPY行,在写数据到行的单元格
做一个EXCEL模板 启用巨集 巨集代码如下:
Sub copy()
Range("a4:r4").Select //复制行
Selection.copy
End Sub
Sub paste(xrows As String)
Range(xrows).Select
ActiveSheet.paste
End Sub
在PB各式里写一个按钮的click事件(也可以写一个转EXCEL的函数)
long ret,i, rows
OLEObject xlapp
pointer oldpointer
oldpointer=setpointer(hourglass!)
xlApp = Create OLEObject
ret = xlApp.ConnectToNewObject( "Excel.Application" )
if ret < 0 then
MessageBox("Connect to Excel Failed !",string(ret))
setpointer(oldpointer)
return
end if
xlApp.Application.Workbooks.Open("C:/excel/test.xls")
xlApp.Application.Visible = true
xlapp.application.activeworkbook.sheets("test").select //调用模板
Range(xrows).Select
ActiveSheet.paste
End Sub
在PB各式里写一个按钮的click事件(也可以写一个转EXCEL的函数)
long ret,i, rows
OLEObject xlapp
pointer oldpointer
oldpointer=setpointer(hourglass!)
xlApp = Create OLEObject
ret = xlApp.ConnectToNewObject( "Excel.Application" )
if ret < 0 then
MessageBox("Connect to Excel Failed !",string(ret))
setpointer(oldpointer)
return
end if
xlApp.Application.Workbooks.Open("C:/excel/test.xls")
xlApp.Application.Visible = true
xlapp.application.activeworkbook.sheets("test").select //调用模板
xlApp.Application.run("copy") //COPY行
rows=dw_2.rowcount()
if rows<=0 then return 1
for i=2 to rows
if i<=dw_2.rowcount() then xlApp.Application.run("paste","a"+string(i+1))
xlApp.application.activeworkbook.worksheets[1].cells[i+1,4]=string(dw_2.object.c_date[i],"mm/dd/yyyy")//写日期数据到EXCEL单元格里
rows=dw_2.rowcount()
if rows<=0 then return 1
for i=2 to rows
if i<=dw_2.rowcount() then xlApp.Application.run("paste","a"+string(i+1))
xlApp.application.activeworkbook.worksheets[1].cells[i+1,4]=string(dw_2.object.c_date[i],"mm/dd/yyyy")//写日期数据到EXCEL单元格里
next
//save EXCEL
integer value
string docname, named
value = GetFileSaveName("Select File", docname, named, "xls", " Excel Files (*.xls), *.xls")
// 导出图片
pictname=dw_4.object.i_photoname[r]
xlsub1.Range("N9").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=180 //图片的大小
end if
end if
integer value
string docname, named
value = GetFileSaveName("Select File", docname, named, "xls", " Excel Files (*.xls), *.xls")
// 导出图片
pictname=dw_4.object.i_photoname[r]
xlsub1.Range("N9").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=180 //图片的大小
end if
end if
xlApp.DisConnectObject()
Destroy xlapp
setpointer(oldpointer)
Destroy xlapp
setpointer(oldpointer)
2> COPY多行,在写数据到行的单元格
不同的ITEM在一个页面放的数量>3个以上
转数据到EXCEL时,要对模板多行COPY格式
代码如下
//定义变量
double jpgsize
string pictname,aa,ff,ctn
long i=1,k,m,b,numrows,f,flg
numrows=dw_2.rowcount()// 取总行数
不同的ITEM在一个页面放的数量>3个以上
转数据到EXCEL时,要对模板多行COPY格式
代码如下
//定义变量
double jpgsize
string pictname,aa,ff,ctn
long i=1,k,m,b,numrows,f,flg
numrows=dw_2.rowcount()// 取总行数
K=CEILING(numrows/5) //每页总行数除去5个ITEM
xls=xlapp.application.activeworkbook
xlsub=xlapp.application.activeworkbook.worksheets[1]
xls=xlapp.application.activeworkbook
xlsub=xlapp.application.activeworkbook.worksheets[1]
for i=1 to k - 1
b=50*i+1
aa='A'+string(B)
xlapp.Application.range("A1:N50").Select
xlapp.Application.Selection.Copy
xlapp.Application.range(AA).Select
xlapp.Application.Selection.Insert()
next
xlapp.Application.cutcopymode=false
//每次10 COPY
for i=1 to numrows
xlsub.Cells[10*(i - 1)+9,12]=string(round(dw_2.object.q_l[i],2),"#,##0.00")+" x "+string(round(dw_2.object.q_w,2),"#,##0.00")+" x "+string(round(dw_2.object.q_h[i],2),"#,##0.00")// 长*宽*高
b=50*i+1
aa='A'+string(B)
xlapp.Application.range("A1:N50").Select
xlapp.Application.Selection.Copy
xlapp.Application.range(AA).Select
xlapp.Application.Selection.Insert()
next
xlapp.Application.cutcopymode=false
//每次10 COPY
for i=1 to numrows
xlsub.Cells[10*(i - 1)+9,12]=string(round(dw_2.object.q_l[i],2),"#,##0.00")+" x "+string(round(dw_2.object.q_w,2),"#,##0.00")+" x "+string(round(dw_2.object.q_h[i],2),"#,##0.00")// 长*宽*高
------------------------------------------------------------------------------------------------------------------------------------------
//转图片
(注意:定義函數 global external functions)
內容如下
function double GetJpgSize(string filename) library 'jpg.dll'
Function ulong GetModuleFileNameA(ulong hModule,ref string lpFileName,ulong nSize) LIBRARY 'kernel32.dll'
Function double ShowME() LIBRARY 'Pwebbrowser.dll'
Function ulong GetModuleFileNameA(ulong hModule,ref string lpFileName,ulong nSize) LIBRARY 'kernel32.dll'
Function double ShowME() LIBRARY 'Pwebbrowser.dll'
--------------------------------------------------------------------------------------------------------------------------------------------
pictname=dw_2.object.i_photoname[i]
f=10*(i - 1)+2
ff="N"+string(f)
xlapp.Application.ActiveWorkbook.Worksheets[1].Range(ff).Select
if pictname<>'' and not(isnull(pictname)) then
if GetJpgSize(pictname)<>0 then
xlapp.Application.ActiveSheet.Pictures.Insert(string(pictname)).select
if GetJpgSize(pictname)<1.29 then
xlapp.Application.Selection.ShapeRange.height = 215
else
xlapp.Application.Selection.ShapeRange.Width = 255
end if
end if
end if
next
-------------------------------------------------------------------------------------------------