技术放送!从数据库中生成电子表格文件完整程序,但还差表格样式设置,请各位高手指教
大放送!从数据库中生成电子表格文件完整程序,但还差表格样式设置,请各位高手指教
以下是一个完整的程序,就差表格样式设置了,不知道如何写,请有经验的同行指导一下。
<%
'定义数据库链接
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.MapPath("winpy.mdb")
set rs=server.createobject("adodb.recordset")
'删除曾经创建的文件
function delfile(filenm)
set objfso=server.createobject("scripting.filesystemobject")
if objfso.FileExists(filenm) then
objfso.deletefile filenm,false
end if
set objfso=nothing
end function
'关闭电子表格链接
function xls_close()
set xlbook=nothing
xlApp.Quit
set xlapp=nothing
end function
'操作宏使其对表格进行格式定义
'问题就在这里,这个宏是从电子表格中直接贴出来的,但不能直接用,必须做一定的修改。
Sub set_hong()
Range("A20:D45").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "黑体"
.FontStyle = "加粗"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
on error resume next
Set xlApp = CreateObject("Excel.Application") '创建对象
xlApp.Visible = true '隐藏电子表格
Set xlBook = xlApp.Workbooks.Open(server.mappath("dh_excel.xls")) '打开文件
Set xlSheet = xlBook.Worksheets(1)'打开第一个表
'设置格式,如果出错则马上关闭电子表格链接,不使滞留在内存中
'就是设置格式出错
call set_hong()
if err<>0 then
call xls_close()
end if
'打开数据库内容
sql="select * from dhhm order by id"
rs.open sql,cn,1,1
rsl=rs.recordcount
j=0
'该模板从第四行第1列写起,数据库中每行共有四列,行数不定
do while not rs.eof
for k=1 to 4
xlSheet.Cells(j+4,k) = rs(k-1)
next
j=j+1
rs.movenext
loop
rs.close
set rs=nothing
set cn=nothing
xlApp.DisplayAlerts=false
filename=server.mappath("./")&"\txt1.xls"
'删除存在的相同文件名的文件
call delfile(filename)
xlBook.Saveas filename '将此文件另存
xlbook.close'关闭连接
if err<>0 then
response.write "操作失败!"
err.clear
else
response.write "操作成功!"
end if
'关闭电子表格连接,一定要关闭,否则有问题
call xls_close()
%>
<a href="<%=filename%>" target="_blank">下载文件</a>