1
excelDriver
=
"
Driver={Microsoft Excel Driver (*.xls)}; DBQ=
"
&
filepath
2 Set excelConn = Server.CreateObject( " Adodb.Connection " )
3 excelConn.Open excelDriver
4 excelSql = " select * from [ " & sheetname & " $] "
5 Set rs = Server.CreateObject( " ADODB.Recordset " )
6 rs.Open excelSql,excelConn, 3 , 3
2 Set excelConn = Server.CreateObject( " Adodb.Connection " )
3 excelConn.Open excelDriver
4 excelSql = " select * from [ " & sheetname & " $] "
5 Set rs = Server.CreateObject( " ADODB.Recordset " )
6 rs.Open excelSql,excelConn, 3 , 3
这里的sheetname就是excel 中的工作表名,剩下的工作就是在adodb.recordset 中处理了,和access 操作一样。这里对excel 格式要求比较严格,第一行必须是标题行,也就是对应数据库里的列名。而且excel 里面不能有合并单元格,拆分单元格,不然读出的数据是乱的。
导出Excel,网上大部分都是说使用 COM组件 Excel.Application,一些常规的用法我也放在下面:
1
On
Error
Resume
Next
2 set objExcelApp = Server.CreateObject( " Excel.Application " )
3
4 objExcelApp.DisplayAlerts = false
5 objExcelApp.Application.Visible = true
6 objExcelApp.SheetsInNewWorkbook = 1 ' 指定excel中表的数量
7
8 set objExcelBook = objExcelApp.WorkBooks.Add
9 set objExcelSheet = objExcelBook.WorkSheets( 1 )
10
11 objExcelSheet.name = " sheet1 "
12
13 objExcelSheet.Range(objExcelSheet.Cells( 1 , 1 ), objExcelSheet.Cells( 1 , 13 )).MergeCells = True ' 合并列
14 objExcelSheet.Range( " A1 " ).value = " Title "
15 objExcelSheet.Range( " A1 " ).HorizontalAlignment = 3 ' 水平对齐
16
17 objExcelSheet.cells( 2 , 1 ).value = " 类别 "
18 objExcelSheet.cells( 2 , 2 ).value = " 名称 "
19 objExcelSheet.cells( 2 , 3 ).value = " 数量 "
20 objExcelSheet.cells( 2 , 4 ).value = " 单价 "
21 objExcelSheet.cells( 2 , 5 ).value = " 总价 "
22
23 set rs = server.CreateObject( " adodb.recordset " )
24 sql = " select * from jdb "
25 rs.open sql, conn, 1 , 1
26 set row = 3
27 do while not rs.eof
28 objExcelSheet.cells(row, 1 ).value = rs( " category " )
29 objExcelSheet.cells(row, 2 ).value = rs( " name " )
30 objExcelSheet.cells(row, 3 ).value = rs( " num " )
31 objExcelSheet.cells(row, 4 ).value = rs( " singleprice " )
32 objExcelSheet.cells(row, 5 ).value = rs( " totalprice " )
33 rs.movenext
34 row = cint (row) + 1
35 loop
36
37 tfile = Server.MapPath( "" & SitePath & ExcelPath & ExcelFileName & "" )
38 Set fs = CreateObject ( " Scripting.FileSystemObject " )
39 if fs.FileExists(tfile) then
40 Set f = fs.GetFile(tfile)
41 f.delete true
42 Set f = nothing
43 end if
44 Set fs = nothing
45
46 objExcelSheet.SaveAs tfile ' 保存文件
47 objExcelApp.Quit ' 释放对象
48 Set objExcelSheet = Nothing
49 Set objExcelApp = Nothing
使用Excel 的COM组件需要服务端安装Excel (真不知道有哪个服务器装这个东西),但是另我万万不解的是,为什么我在保存文件那步怎么也保存不了,组件没问题,权限也没问题。如果有哪位遇到同样问题还解决了的,希望指点下。
2 set objExcelApp = Server.CreateObject( " Excel.Application " )
3
4 objExcelApp.DisplayAlerts = false
5 objExcelApp.Application.Visible = true
6 objExcelApp.SheetsInNewWorkbook = 1 ' 指定excel中表的数量
7
8 set objExcelBook = objExcelApp.WorkBooks.Add
9 set objExcelSheet = objExcelBook.WorkSheets( 1 )
10
11 objExcelSheet.name = " sheet1 "
12
13 objExcelSheet.Range(objExcelSheet.Cells( 1 , 1 ), objExcelSheet.Cells( 1 , 13 )).MergeCells = True ' 合并列
14 objExcelSheet.Range( " A1 " ).value = " Title "
15 objExcelSheet.Range( " A1 " ).HorizontalAlignment = 3 ' 水平对齐
16
17 objExcelSheet.cells( 2 , 1 ).value = " 类别 "
18 objExcelSheet.cells( 2 , 2 ).value = " 名称 "
19 objExcelSheet.cells( 2 , 3 ).value = " 数量 "
20 objExcelSheet.cells( 2 , 4 ).value = " 单价 "
21 objExcelSheet.cells( 2 , 5 ).value = " 总价 "
22
23 set rs = server.CreateObject( " adodb.recordset " )
24 sql = " select * from jdb "
25 rs.open sql, conn, 1 , 1
26 set row = 3
27 do while not rs.eof
28 objExcelSheet.cells(row, 1 ).value = rs( " category " )
29 objExcelSheet.cells(row, 2 ).value = rs( " name " )
30 objExcelSheet.cells(row, 3 ).value = rs( " num " )
31 objExcelSheet.cells(row, 4 ).value = rs( " singleprice " )
32 objExcelSheet.cells(row, 5 ).value = rs( " totalprice " )
33 rs.movenext
34 row = cint (row) + 1
35 loop
36
37 tfile = Server.MapPath( "" & SitePath & ExcelPath & ExcelFileName & "" )
38 Set fs = CreateObject ( " Scripting.FileSystemObject " )
39 if fs.FileExists(tfile) then
40 Set f = fs.GetFile(tfile)
41 f.delete true
42 Set f = nothing
43 end if
44 Set fs = nothing
45
46 objExcelSheet.SaveAs tfile ' 保存文件
47 objExcelApp.Quit ' 释放对象
48 Set objExcelSheet = Nothing
49 Set objExcelApp = Nothing
最后,想了半天,用了一个很取巧的办法。就是设置ContentType 为 application/vnd.ms-excel
1
<%
2 Response.ContentType = " application/vnd.ms-excel "
3 %>
4
5 <%
6 set rs1 = server.CreateObject ( " adodb.recordset " )
7 sql = " select * from jdb "
8 rs1.open sql,conn, 1 , 1
9 %>
10 < table border ="1" >
11 < tr >
12 < td > 类别 </ td >
13 < td > 序号 </ td >
14 < td > 设备名称 </ td >
15 < td > 数量 </ td >
16 < td > 单价 </ td >
17 < td > 总价 </ td >
18 </ tr >
19 <%
20 Do while not rs1.eof
21 %>
22 < tr >
23 < td > <% = rs1( " category " ) %> </ td >
24 < td > <% = rs1( " xh " ) %> </ td >
25 < td > <% = rs1( " name " ) %> </ td >
26 < td > <% = rs1( " num " ) %> </ td >
27 < td > <% = rs1( " singleprice " ) %> </ td >
28 < td > <% = rs1( " totalprice " ) %> </ td >
29 </ tr >
30 <%
31 rs1.movenext
32 loop
33 rs1.close
34 set rs1 = nothing
35 %>
36 </ table >
这样多方便,连Excel 都不用装了。当然导出的Excel 是难看了点。这和在IE 上点右键导出到Excel 是一个原理。
2 Response.ContentType = " application/vnd.ms-excel "
3 %>
4
5 <%
6 set rs1 = server.CreateObject ( " adodb.recordset " )
7 sql = " select * from jdb "
8 rs1.open sql,conn, 1 , 1
9 %>
10 < table border ="1" >
11 < tr >
12 < td > 类别 </ td >
13 < td > 序号 </ td >
14 < td > 设备名称 </ td >
15 < td > 数量 </ td >
16 < td > 单价 </ td >
17 < td > 总价 </ td >
18 </ tr >
19 <%
20 Do while not rs1.eof
21 %>
22 < tr >
23 < td > <% = rs1( " category " ) %> </ td >
24 < td > <% = rs1( " xh " ) %> </ td >
25 < td > <% = rs1( " name " ) %> </ td >
26 < td > <% = rs1( " num " ) %> </ td >
27 < td > <% = rs1( " singleprice " ) %> </ td >
28 < td > <% = rs1( " totalprice " ) %> </ td >
29 </ tr >
30 <%
31 rs1.movenext
32 loop
33 rs1.close
34 set rs1 = nothing
35 %>
36 </ table >