ASP 导入导出 Excel
前两天做个小网站要求能够从Excel 里面导入数据,同时还需要把数据导出到Excel 中,
。在网上找了半天,导入相对比较简单,这里我采用Excel 提供的ODBC,可以直接使用SQL来操作Excel。连接查询方法如下:![](https://www.cnblogs.com/Emoticons/tusiji/203330839.gif)
这里的sheetname就是excel 中的工作表名,剩下的工作就是在adodb.recordset 中处理了,和access 操作一样。这里对excel 格式要求比较严格,第一行必须是标题行,也就是对应数据库里的列名。而且excel 里面不能有合并单元格,拆分单元格,不然读出的数据是乱的。
导出Excel,网上大部分都是说使用 COM组件 Excel.Application,一些常规的用法我也放在下面:
![](https://www.cnblogs.com/Emoticons/tusiji/203330868.gif)
最后,想了半天,用了一个很取巧的办法。就是设置ContentType 为 application/vnd.ms-excel
![](https://www.cnblogs.com/Emoticons/tusiji/202939796.gif)
![](https://www.cnblogs.com/Emoticons/tusiji/203330839.gif)
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
![](https://www.cnblogs.com/Emoticons/tusiji/203330868.gif)
最后,想了半天,用了一个很取巧的办法。就是设置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>