ASP 操作ADO 读写Excel文件

最新推荐文章于 2022-06-13 16:49:03 发布

e_zhiwen

最新推荐文章于 2022-06-13 16:49:03 发布

阅读量4.8k

点赞数

分类专栏： DataBase relate About Office asp 文章标签： excel asp database properties date 工作

本文链接：https://blog.csdn.net/e_zhiwen/article/details/5988827

版权

DataBase relate 同时被 3 个专栏收录

24 篇文章 0 订阅

订阅专栏

asp

23 篇文章 0 订阅

订阅专栏

About Office

2 篇文章 0 订阅

订阅专栏

为了把数据库中的数据导出Excel，在网上找了N多的资料有直接服务器中创建excel的，也有用owc生成的，还有javascript输出的，每种方法开始是以为不错最后经过调试和试用都发现了这样和那样的问题。比如，CreateObject("Excel.Application") 此方法无法创建对象，服务器端有excel office；Response.ContentType = "application/vnd.ms-excel" ，此方法容易输入乱码并且操作excel的格式困难，数据量较大(试用生成的文件才2M多)时打开excel超慢；Scripting.FileSystemObject，此方法也可以直接生成excel文件，但无法操作utf-8编码，同样是乱码；Javascript excelapp=new activexObject("excel.application") 此方法与application/vnd.ms-excel问题类似，并且在用<input οnclick='';>调用时IE8点击无反应。

最后找到了微软介绍的方法：ExcelADO 演示如何使用 ADO 来读取和写入数据在 Excel 工作簿中http://support.microsoft.com/kb/278973

終於解決了問題並貼出相關代碼：

[code]

<%

    Dim sSourceXLS
    Dim sDestXLS
    Dim sNWind

    sSourceXLS = Server.MapPath(".") & "/templet/export.xls"  '服務器excel模板位置
    filename="export_"&session("username")&"_"&year(date())&month(date())&day(date())&hour(time())&Minute(time())&second(time())&".xls"
    filepath="/export/"&filename                                                '要創建文件的位置
    sDestXLS = Server.MapPath(".") & filepath
   exportpath="/sales"&filepath                                               '子目錄位置這個與程序位置環境有關
     'Copy the source workbook file (the "template") to the destination filename
    Dim fso
    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    fso.GetFile(sSourceXLS).Copy sDestXLS
    Set fso = Nothing
    'Open the ADO connection to the Excel workbook
    Dim oConn
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & sDestXLS & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

    'Open a connection to the Northwind database and retrieve the Order information
    'in OrderDetails table
    Dim oNWindConn, oOrdersRS

    Set oOrdersRS = Server.CreateObject("ADODB.Recordset")

    sqlstr="select * from sales"
    oOrdersRS.open sqlstr,conn,3
    'oOrdersRS.Open "SELECT [Order Details].OrderID, Products.ProductName, " & _
    '               "[Order Details].UnitPrice , [Order Details].Quantity, " & _
    '               "[Order Details].Discount FROM Products INNER JOIN " & _
    '               "[Order Details] ON Products.ProductID = " & _
    '               "[Order Details].ProductID ORDER BY [Order Details].OrderID", _
    '               conn, 3

    '**Note: The first "row" in the Orders_Table is hidden -- it contains dummy data that
    '        the OLE DB Provider uses to determine the data types for the table.

    'Add the data from the Order Details table in Northwind to the workbook

    Dim oRS
    Set oRS = Server.CreateObject("ADODB.Recordset")
    oRS.Open "Select * from export_table", oConn, 1, 3          '注意這裏在模板中要先定義一個名稱，此工具在excel文件菜單欄的插入--名稱--定義中找到。

    oRS.addnew
      For i = 0 To oOrdersRS.fields.count-1
            oRS.Fields(i).Value = oOrdersRS.Fields(i).name
        Next
     oRS.Update
        oOrdersRS.MoveNext
    Do While Not (oOrdersRS.EOF)
        oRS.AddNew
        For i = 0 To oOrdersRS.fields.count-1
            oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
        Next
        oRS.Update
        oOrdersRS.MoveNext
    Loop

    'Close the recordset and connection to Northwind
    oOrdersRS.Close
    Set oOrdersRS = Nothing
    oNWindConn.Close
    Set oNWindConn = Nothing

    'Close the connection to the workbook
    oConn.Close
    Set oConn = Nothing

response.write "pls,right hit save as to download your export file:<a href='"&exportpath&"'>"&filename&" </a>"

[/code]

另外此种方法亦有个小问题，模板设置好的表名称不知道用什么方法修改，通过次找到微软的一个页面可以在创建表时设置名称如下链接

http://support.microsoft.com/kb/295646/

如何复制模板中的表

您可以使用 SELECT INTO 语句将 Jet 可以读取的任何数据源中的数据复制到任何数据目标，随时创建新的表（在 Excel 中为新工作表）。将工作表名称用作目标时，不要使用美元符号语法，例如 [Sheet1$]。目标工作簿可以存在，也可以不存在；但是，目标工作表必须尚未存在。

对于将整个 Customers 表从 Microsoft Access Northwind 数据库复制到 Excel 工作簿的新工作表中的复制命令，有三种编写方法。每种语法需要一个 SQL 语句并在目标工作表的第一行创建列标题。

以下示例使用 SELECT INTO 语法：

 Dim strSQL As String
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _ 
    "/book1.xls].[Sheet1] FROM Customers"
cnSrc.Execute strSQL

以下示例使用 SELECT INTO ...IN 语法：
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _ "/book1.xls] FROM Customers"
其中，括号中的目标数据库信息的前面有一对空的单引号，用来包括类型参数（“Excel 8.0”部分），当您使用此语法时，类型参数括在括号中。
以下示例使用 IN 子句的替代语法：
strSQL = "SELECT * INTO [Sheet1] IN '" & App.Path & _ "/book1.xls' 'Excel 8.0;' FROM Customers"
其中，类型参数现在被单独列在目标文件路径之后。

修改后的代码如下：

[code]

Dim sSourceXLS
Dim sDestXLS

    sSourceXLS = Server.MapPath(".") & "/templet/export.xls"
    filename=userdepart&"-"&session("username")&"_"&year(date())&month(date())&day(date())&hour(time())&Minute(time())&second(time())&".xls"
    filepath="/export/"&filename
    sDestXLS = Server.MapPath(".") & filepath
   exportpath="/annual"&filepath
     'Copy the source workbook file (the "template") to the destination filename
    Dim fso
    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    fso.GetFile(sSourceXLS).Copy sDestXLS
    Set fso = Nothing
    'Open the ADO connection to the Excel workbook
    Dim oConn
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & sDestXLS & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""
    Dim oOrdersRS,oRS
    Set oRS = Server.CreateObject("ADODB.Recordset")
    Set oOrdersRS = Server.CreateObject("ADODB.Recordset")
    if userdepart<>"" then
     sqlstr="select distinct userid,username from annual_v where department='"&userdepart&"'"
else
     sqlstr="select distinct userid,username from annual_v where userid="&mainkey
   end if
   set rs=conn.execute (sqlstr,0,1)

   do while not rs.eof
     sqlstr="select * into [Excel 8.0;Database="&sDestXLS&"].["&rs("username")&"] from [Templet$]"
    oConn.execute sqlstr '从模板中复制用户名表

      sqlstr="select joined from user_table where userid="&rs("userid")
    set temprs=conn.execute (sqlstr,0,1)
     joineddate=temprs("joined")
    set temprs=nothing

     sqlstr="select * from annual_v where userid="&rs("userid")&" order by whatdate"
    oOrdersRS.open sqlstr,conn,3

    sqlstr="select * from ["&rs("username")&"]"
    oRS.open sqlstr,oConn,1,3
    i=0
    do while not oRS.eof
     if i=3 then
      oRS.fields(2)=rs("username")
      oRS.update
     end if
     if i=4 then
      oRs.fields(2)=joineddate
      oRS.update
     end if
     i=i+1
     oRS.movenext
    loop


     Do While Not (oOrdersRS.EOF)
        oRS.AddNew

       oRS.Fields(0)=oOrdersRS("whatdate")&":"&oOrdersRS("something")

       if isnumeric(oOrdersRS("adddate")) then
         adddate=oOrdersRS("adddate")
       else
         adddate=0
       end if
       if isnumeric(oOrdersRS("divdate")) then
         divdate=oOrdersRS("divdate")
       else
         divdate=0
       end if
       select case oOrdersRS("sttype")
         case 1
          leave(1)=leave(1)+adddate-divdate
          oRS.Fields(1)=adddate
          oRS.Fields(3)=divdate
          oRS.Fields(4)=leave(1)
         case 2
          leave(2)=leave(2)+adddate-divdate
          oRS.Fields(5)=adddate
          oRS.Fields(7)=divdate
          oRS.Fields(8)=leave(2)
         case 3
          leave(3)=leave(3)+adddate-divdate
          oRS.Fields(9)=adddate
          oRS.Fields(11)=divdate
          oRS.Fields(12)=leave(3)
         case 4
          leave(4)=leave(4)+adddate-divdate
          oRS.Fields(13)=adddate
          oRS.Fields(14)=divdate
          oRS.Fields(16)=leave(4)
         case else
         end select

       oRS.Fields(17)=oOrdersRS("remark")
        oRS.Update
        oOrdersRS.MoveNext
    Loop
    oOrdersRS.Close
    oRS.close
   rs.movenext
  loop

    Set oOrdersRS = Nothing

    oConn.Close
    Set oConn = Nothing

   response.write "pls,right hit save as to download your export file:<a href='"&exportpath&"'>"&filename&" </a>"
   response.write "<input type='button' value='ReturnBack' οnclick='history.back(-1);'>"

[/code]

但此方法打开ADO进行写数据后模板中设置的格式丢失了。有没有二合一的方法呢？继续查资料中。

e_zhiwen

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
ASP 操作ADO 读写Excel文件

为了把数据库中的数据导出Excel，在网上找了N多的资料有直接服务器中创建excel的，也有用owc生成的，还有javascript输出的，每种方法开始是以为不错最后经过调试和试用都发现了这样和那样的问题。比如，CreateObject("Excel.Application") 此方法无法创建对象，服务器端有excel office；Response.ContentType = "application/vnd.ms-excel" ，此方法容易输入乱码并且操作excel的格式困难，数据量较大(试用生成的文件
复制链接

扫一扫