两种方法将数据写入excel文件

废话少说，代码片段如下：

Try
                        Dim compid As String = e.Item.Cells(0).Text
                        Dim time As String = txt_Time.Text
                        Dim adp As New OleDb.OleDbDataAdapter("select id 电脑序号,insureid 公民身份证号码,name 姓名,INTYPE 缴费人员类别,AVGWAGE 上年月平均工资,base 缴费基数 from v_BaseCollect where compid='" + compid + "' and time='" + time + "'", New OleDb.OleDbConnection(Application("ConnStr").ToString))
                        Dim dt As New DataTable
                        Dim bs As New webapi.setapi
                        adp.Fill(dt)
                        adp.Dispose()

                        '打开excel文件
                        ViewState.Item("FileName") = ViewState("folder").ToString + "/" + Now.Ticks.ToString + ".xls"
                        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ViewState.Item("FileName").ToString + ";Extended Properties=Excel 8.0;"
                        FileCopy(ViewState("folder").ToString + "/缴费基数下载模板.xls", ViewState.Item("FileName").ToString)

                        '写入
                        Dim dr As DataRow
                        Dim comm As New OleDbCommand("", New OleDb.OleDbConnection(strConn))
                        comm.Connection.Open()
                        For Each dr In dt.Rows
                            comm.CommandText = "insert into [Sheet1$](电脑序号,公民身份证号码,姓名,缴费人员类别,上年月平均工资,缴费基数)values('" + dr("电脑序号").ToString + "','" + dr("公民身份证号码").ToString + "','" + dr("姓名").ToString + "','" + dr("缴费人员类别").ToString + "','" + dr("上年月平均工资").ToString + "','" + dr("缴费基数").ToString + "')"
                            comm.ExecuteNonQuery()
                        Next
                        comm.Connection.Close()

                        bs.DownLoad(Response, ViewState.Item("FileName").ToString)

                        System.IO.File.Delete(ViewState("FileName").ToString)
                    Catch ex As Exception
                        Response.Write(ex.ToString())
                    End Try
                    'Dim adp As New OleDbDataAdapter("", Application.Item("ConnStr").ToString)
                    'Dim ds As New DataSet
                    'Try
                    '    Dim excelApp As New Excel.Application
                    '    Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
                    '    Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

                    '    adp.SelectCommand.CommandText = "select  distinct * from v_BaseCollect where compid='" + Request.Cookies("compid").Value + "' and time='" + Request.Cookies("time").Value + "'"
                    '    adp.Fill(ds, "v_BaseCollect")

                    '    excelApp.Visible = True
                    '    With excelWorksheet
                    '        .Range("A1").Value = "序号"
                    '        .Range("A1").Font.Bold = True
                    '        .Range("B1").Value = "电脑序号"
                    '        .Range("B1").Font.Bold = True
                    '        .Range("C1").Value = "公民身份证号码"
                    '        .Range("C1").Font.Bold = True
                    '        .Range("D1").Value = "姓名"
                    '        .Range("D1").Font.Bold = True
                    '        .Range("E1").Value = "缴费人员类别"
                    '        .Range("E1").Font.Bold = True
                    '        .Range("F1").Value = "上年月均工资"
                    '        .Range("F1").Font.Bold = True
                    '        .Range("G1").Value = "缴费基数"
                    '        .Range("G1").Font.Bold = True

                    '        Dim i As Integer = 2
                    '        Dim dr As DataRow
                    '        For Each dr In ds.Tables("v_BaseCollect").Rows
                    '            .Range("A" & i.ToString).Value = i.ToString
                    '            .Range("B" & i.ToString).Value = dr("ID")
                    '            .Range("C" & i.ToString).Value = dr("INSUREID")
                    '            .Range("D" & i.ToString).Value = dr("NAME")
                    '            .Range("E" & i.ToString).Value = dr("INTYPE")
                    '            .Range("F" & i.ToString).Value = dr("AVGWAGE")
                    '            .Range("G" & i.ToString).Value = dr("BASE")
                    '            i += 1
                    '        Next
                    '    End With
                    'Catch ex As Exception
                    '    Response.Write(ex.ToString)
                    'End Try