生成/读取(反向更新数据库) Excel文件(示例代码下载)

原文出处http://blog.csdn.net/chengking/archive/2005/11/29/539514.aspx

<script type="text/javascript"> document.body.oncopy = function() { if (window.clipboardData) { setTimeout(function() { var text = clipboardData.getData("text"); if (text && text.length>300) { text = text + "/r/n/n本文来自CSDN博客，转载请标明出处：" + location.href; clipboardData.setData("text", text); } }, 100); } } </script> <script type="text/javascript">function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}</script>

 

(一).内容

   在操作Excel的过程中遇到了一些问题. 比如： 访问Com组件权限，无法读取Excel等
   文章描述了怎样双向操作(读取和生成)Excel文件，以及怎样解决遇到的问题!

(二).代码
   开始时用了下面两个方法进行生成和读取 Excel:
    1.生成Excel文件方法一:     

  '  <summary>
     '   下载Excel方法1(用流实现)
     '  </summary>
     '  <param name="dt">要转换为Excel文件的表</param>
     '  <param name="page">页面Page对象,用法: 将me.Page传递过来即可</param>
    Public Sub DownLoadExcelToClient1(ByVal dt As DataTable, ByVal FileName As String)
        Dim resp As HttpResponse
        resp  =  Page.Response
        resp.ContentEncoding  =  System.Text.Encoding.Default 

' System.Text.Encoding.GetEncoding("GB2312")
        resp.AppendHeader( " Content-Disposition " ,  " attachment;filename= "   +  FileName)
        Dim colHeaders As String  =   "" , ls_item  =   ""
        Dim i As Int16  =   0

         ' 取得数据表各列标题，各标题之间以 分割，最后一个列标题后加回车符 
        For i  =   0  To dt.Columns.Count  -   2
            colHeaders  +=  dt.Columns(i).Caption.ToString()  &  Chr( 9 )
        Next
        colHeaders  +=  dt.Columns(i).Caption.ToString()  &  Chr( 13 )
         ' 向HTTP输出流中写入取得的数据信息 
        resp.Write(colHeaders)

        Dim row As DataRow
         ' 逐行处理数据   
        For Each row In dt.Rows
             ' 在当前行中，逐列获得数据，数据之间以 分割，结束时加回车符  
            For i  =   0  To dt.Columns.Count  -   2
                ls_item  &=  row(i).ToString()  &  Chr( 9 )
            Next i
            ls_item  &=  row(i).ToString()  &  Chr( 13 )

             ' 当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
            resp.Write(ls_item)
            ls_item  =   ""
        Next
         ' 写缓冲区中的数据到HTTP头文件中 
        resp.End()
    End Sub

    2.读取Excel文件      

'  <summary>
     '   读取Excel文件
     '  </summary>
     '  <param name="dt">要转换为Excel文件的表</param>
     '  <param name="page">页面Page对象,用法: 将me.Page传递过来即可</param>
     '  <return>数据集DataSet</return>
    Public Function ReadExcelFileToDataSet(ByVal strFileName As String) As DataSet
        Try

             ' 建立一个专门存放Excel文件的目录
            If Directory.Exists(Page.Server.MapPath( " ExcelFolder " ))  =  False Then
                Directory.CreateDirectory(Page.Server.MapPath( " ExcelFolder " ))
            End If

            Dim strConn As String
            strConn  =   " Provider=Microsoft.Jet.OLEDB.4.0; "   &   " Data Source= "   &  

Page.Server.MapPath( " . " )  &   " ExcelFolder" & strFileName &  " ; "  &  " Extended 

Properties = Excel  8.0 ; "
            Dim conn As OleDb.OleDbConnection  =  New OleDb.OleDbConnection(strConn)
            Dim strExcel As String  =   " select * from [sheet1$] "
            Dim ds As DataSet  =  New DataSet
            conn.Open()
            Dim adapter As OleDbDataAdapter  =  New OleDbDataAdapter(strExcel, strConn)
            adapter.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

        生成是成功的，但读取不成功，提示:"数据源格式有误!"
   一直是认为:ReadExcelFileToDataSet方法有误,调试了半天，找了N多资料也不能成功!
   后来经过"时间的流逝",终久确认第二个方法没有错误，是完全正确的，是第一个方法
   生成格式的问题. 于是我将第一个方法换为下面的方法,读取和写入就OK了.

    另外还可以读取指定范围内的单元格:
      对 Excel 工作簿中表（或范围）的有效引用。
      若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：
      select * from [Sheet1$]
      若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： 
      select * from [Sheet1$A1:B10]
      若要引用指定的范围，请使用该范围的名称。例如：
      select * from [MyNamedRange]

 3.生成Excel文件方法二:

     '  <summary>
     '   下载Excel方法2(用office-Excel-Com组件对象实现)
     '  </summary>
     '  <param name="dt">要转换为Excel文件的表</param>
     '  <param name="page">页面Page对象,用法: 将me.Page传递过来即可</param>
    Public Sub DownLoadExcelToClient2(ByVal dt As DataTable)
         ' 生成Excel操作相关对象
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        xlApp  =  CType(CreateObject( " Excel.Application "), Excel.Application)
        xlBook  = CType(xlApp.Workbooks.Add, Excel.Workbook)
        xlSheet  =  CType(xlBook.Worksheets( 1), Excel.Worksheet)


         ' xlSheet.Range("A1:B1").Merge(0)  '合并单元格
         ' xlSheet.Cells(1, 1) = "员工资料信息:"

        ' 赋标题(Excel文件中的标题)
        Dim rowIndex As Integer  =   2
        Dim colIndex As Integer =   0
        Dim Col As DataColumn
        Dim Row As DataRow
        For Each Col In dt.Columns
            colIndex =  colIndex  +   1
            xlApp.Cells(rowIndex, colIndex)  = Col.ColumnName
        Next

         ' 将表dt的所有行写入xlApp对象(Excel文件中的内容)
        For Each Row In dt.Rows
            rowIndex  =  rowIndex  +   1
            colIndex =   0
            For Each Col In dt.Columns
                colIndex =  colIndex  +   1
                xlApp.Cells(rowIndex, colIndex)  = Row(Col.ColumnName)
            Next
        Next

        xlSheet.Application.Visible  =  True    ' 置为可见

        ' 建立一个专门存放Excel文件的目录
        If Directory.Exists(Page.Server.MapPath( " ExcelFolder " ))  = False Then
            Directory.CreateDirectory(Page.Server.MapPath( " ExcelFolder "))
        End If

         ' 删除服务端临时文件: download.xls
        If File.Exists(Page.Server.MapPath( " . " )  &   " ExcelFolderdownload.xls " )  = True Then
            File.Delete(Page.Server.MapPath( " . " )  &   " ExcelFolderdownload.xls ")
        End If

         ' 在服务端保存download.xls
        xlSheet.SaveAs(Page.Server.MapPath( " . " )  &   " ExcelFolderdownload.xls ")

         ' 杀死Excel进程
        Dim myproc As System.Diagnostics.Process  = New System.Diagnostics.Process
        Dim proc As Process
        Dim procs() As Process  =  Process.GetProcessesByName( " excel " )    ' 得到所有打开的进程
        Try
            For Each proc In procs
                If Not proc.CloseMainWindow() Then
                    proc.Kill()
                End If
            Next
        Catch
        End Try

         ' 这里用到个goto语句,是因为: 线程是异步执行的,下面的代码要访问download.xls文件,但有
少数情况下上面的线程' 未能及时释放download.xls文件的指针,那么下面代码执行语句时会抛出异常, 当发生异常时 需要等待资源释放后, ' 再重新访问该文件, 保证下载文件能够正确下载
again:  Try
             ' 输出到客户端()
            If File.Exists(Page.Server.MapPath( " . " )  &   " ExcelFolderdownload.xls ") Then
                Dim TargetFile As FileInfo  =  New FileInfo(Page.Server.MapPath( " . " )  & 

" ExcelFolderdownload.xls ")
                 ' 清除缓冲区流中的所有内容输出.
                Page.Response.Clear()
                 ' 向输出流添加HTTP头 [指定下载/保存 对话框的文件名]
                Page.Response.AppendHeader( " Content-Disposition " ,  " attachment; filename= " 

+ Page.Server.UrlEncode(TargetFile.Name))
                 ' 向输出流添加HTTP头 [指定文件的长度,这样下载文件就会显示正确的进度
                Page.Response.AppendHeader( " Content-Length ", TargetFile.Length.ToString())
                 ' 表明输出的HTTP为流[stream],因此客户端只能下载.
                Page.Response.ContentType  =   " application/octet-stream "
                ' 发送文件流到客户端.
                Page.Response.WriteFile(TargetFile.FullName)
                 ' 停止执行当前页
                Page.Response.End()
            End If
        Catch
            Thread.Sleep( 10)
            GoTo again
        End Try
    End Sub

这说明：
    a. 生成Excel文件后，用户经过修改，还要反向读取此Excel文件(比如:反向更新到数据库中)
       就只能用: 3 和 2方法结合使用. 
    b. 如果只是单向输出Excel文件，用1和2方法都可以. 不过用2的话还要安装Office-Excel,
       一般还要设置一下Com组件访问权限,添加对Com组件的引用: 添加: 引用->com->Microsoft Excel 11.0 object Library

(三).不能读取的原因 以及 权限问题解决

  a. 原因是:  方法 1 是用流的格式实现的,简单的说它不是真正的Excel格式，而 3 是调用的
     Excel Com组件，生成的是真正的Excel文档,所以能读取(1也可以读取，但也要用Stream类读取，

    如果数据有些复杂的话，会很麻烦).  用1和3方法生成的文件及文件图标一模一样，并且用Excel 

    应用程序打开后显示效果也是一样的.  但当用记事本分别打开1和3生成的*.xls文件时，就明
     显看到它们的不同了.(您可以下载一下本示例代码程序，分别生成两个文件，对比一下)

  b.在使用Excel com组件时除了装Office-Excel以外，一般还要设置一下Com的访问权限，步骤如下: 
     I.如果是Window2003 ->控制面版 -> 管理工具 -> 组件服务 ->
       打开树级目录找到子目录DCOM配置 -> Microsoft Excel 应用程序
       -> 右击选“属性” -> 在弹出对话窗口中选“安全”选项卡->
       -> 将启动和激活权限设为自定义->点击编辑按钮->
       ->在新窗口中将Everyone用户加入,选中复选框"启动权限",给予启动权限

     II.如果是WindowXP ->控制面版 -> 管理工具 -> 组件服务(繁体为"元件服务") ->
       打开树级目录找到子目录DCOM配置 -> Microsoft Excel 应用程序
       -> 右击选“属性” -> 在弹出对话窗口中选“安全”选项卡->
       -> 将启动和激活权限设为自定义->点击编辑按钮->
       ->在新窗口中将Everyone用户加入,选中复选框"远程启动",给予远程启动权限
(四).使用Excel模板
       虽然Com组件功能已经比较全面，可以对任意一个单元格设置和赋值.但是如果数据集DataSet比

   较复杂的话，全部用Com提供的方法实现就很麻烦了. 尤其是文档头和尾最难处理.
    这时可以这样处理:
    a.先用Excel应用程序建立一个Excel文件，设置好头/尾样式和以及所有单元格的布局和格式
      (这里就是作用office 家簇的 excel进行表格布局，可以任意操作)   
    b.将此文件保存到工程的一个文夹下面即可.
      用法:
      只需将2中的:
        xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
           改为: 
        xlBook = xlApp.Workbooks.Add(page.MapPath(".") + "ExcelTemplate.xls")'使用現有模板
      即可。 这样xlBook实际上是基于自己创建的模板的.
    这样操作，一般表头和标题列，表尾列一般不用设置，只显示DataSet中的主要数据(甚至也不用显

   示DataSet列名)

    最近给客户做一些Excel报表,我们是这样做的，直接把客户给的Excel需求文件作为了模板.
    非常简便，更爽的是这样做报表跟客户要求的完全相同。

(五).代码示例下载

   http://www.cnblogs.com/Files/ChengKing/OPExcel.rar

 此示例在配置环境:   WinXP(繁体) VS.net 2002    WinXP(简体) Vs.net 2003 

                                    Win2003 VS.net 2003  测试能够正确运行!

相关文章:

http://blog.joycode.com/ghj/archive/2005/01/12/42861.aspx
http://cnbie.net/print_146048m63657.html
http://blog.csdn.net/net_lover/archive/2004/06/08/6963.aspx
http://dotnet.aspx.cc/ShowDetail.aspx?id=4EB79F05-B9A4-4E8A-836F-864393F40405
http://dotnet.aspx.cc/ShowDetail.aspx?id=6AFBF00B-459D-4642-AD14-8A4765FFAFCC
http://dotnet.aspx.cc/ShowDetail.aspx?id=8A4CBF47-B888-4832-3389-ED3A3A3C8AAB
http://support.microsoft.com/kb/317881/EN-US/
http://study.99net.net/study/program/vb/1049955696.html