xlbook.close
xls.quit
end if
set xlbook = nothing
set xls = nothing
exit sub
'exlerror:
' msgbox err.description, vbokonly + vbcritical, "警告"
end sub
option explicit
public sub toexcelzgcl(byref xlbook, byref xls) '输出总工程量
dim con as new adodb.connection
dim rst_gcl as new adodb.recordset
dim rst_qm as new adodb.recordset
'**************************连接数据库****************************************
con.cursorlocation = aduseclient
con.connectionstring = "provider=microsoft.jet.oledb.4.0;data source=" & strconnection & ";persist security info=false"
con.open
rst_gcl.open "zonggcl", con, adopenkeyset, adlockoptimistic, adcmdtable '打开工程量汇总表
if not (rst_gcl.bof and rst_gcl.eof) then
rst_gcl.movefirst
end if
rst_qm.open "qianming", con, adopenkeyset, adlockoptimistic, adcmdtable '打开签名表
rst_qm.movefirst
'****************************工作表初使化***********************************
dim xlsheet as excel.worksheet
set xlsheet = xlbook.sheets.add '添加一张工作表
xlsheet.name = "工程量汇总"
xls.activesheet.pagesetup.orientation = xllandscape '纸张设置为横向
xlsheet.columns("a:j").font.size = 10
xlsheet.columns("a:j").verticalalignment = xlvaligncenter '垂直居中
xlsheet.columns(1).horizontalalignment = xlhaligncenter '1列水平居中对齐
xlsheet.columns(1).columnwidth = 8
xlsheet.columns(2).horizontalalignment = xlhalignleft
xlsheet.columns(2).columnwidth = 26
xlsheet.columns("c:j").horizontalalignment = xlhalignright
xlsheet.columns("c:j").columnwidth = 10
xlsheet.columns("c:j").numberformatlocal = "0.00_ " '3到10列保留两位小数
'***************************写入标头*************************************
xlsheet.rows(1).rowheight = 40
xlsheet.range(xlsheet.cells(1, 1), xlsheet.cells(1, 10)).mergecells = true
xlsheet.cells(1, 1).value = "工程量汇总"
xlsheet.cells(1, 1).font.size = 14
xlsheet.cells(1, 1).font.bold = true
xlsheet.rows(2).rowheight = 18
xlsheet.rows(2).horizontalalignment = xlhaligncenter
xlsheet.cells(2, 1).value = "序号"
xlsheet.cells(2, 2).value = "工程项目及名称"
xlsheet.cells(2, 3).value = "土方开挖(m3)"
xlsheet.cells(2, 4).value = "石方开挖(m3)"
xlsheet.cells(2, 5).value = "土方回填(m3)"
xlsheet.cells(2, 6).value = "洞挖石方(m3)"
xlsheet.cells(2, 7).value = "砼浇筑(m3)"
xlsheet.cells(2, 8).value = "钢筋制安(t)"
xlsheet.cells(2, 9).value = "砌石工程(m3)"
xlsheet.cells(2, 10).value = "灌浆工程(m)"
xls.activesheet.pagesetup.printtitlerows = "$1:$2" '固定表头
'***************************写入内容*************************
dim i as integer
i = 3 'i控制行
dim j as integer 'j控制列
dim countpage as integer
countpage = 0 '控制页
do while not rst_gcl.eof
xlsheet.rows(i).rowheight = 18 '控制行高
for j = 1 to 10
xlsheet.cells(i, j) = rst_gcl.fields(j) '将工程理库中的一条记录的第一个字段写入工作表中
next
'每18行为一页，如果数据超出一页时进行特殊处理
if i > 18 then
xls.activewindow.smallscroll down:=1 '活动窗口内容向下滚动1行
end if
if i mod 18 = 0 then
if countpage = 0 then
xlsheet.range(xlsheet.cells(2, 1), xlsheet.cells(i, 10)).borders.linestyle = xlcontinuous '首页加边框
else
