如何使用vb.net在Excel中添加书籍 [英] How to add books in excel using vb.net

问题描述

我正在编写用于将sql记录导出到excel文件的代码.这段代码每次都在创建文件.如果该文件存在，则删除文件并进行创建和导出.但是我想将sql记录导出到已创建的文件中，但附加记录无需创建文件.

我的代码如下，请帮助我

I am writing code for export sql records to excel file. This code is creating file to every time.If the file is exists it delete file and the create and export.But I want export sql records to already creating file, that records are appended need not creating file.

My code is following,Please help me

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
      Dim wBook As Microsoft.Office.Interop.Excel.Workbook
      Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

      wBook = excel.Workbooks.Add()
      wSheet = wBook.ActiveSheet()

      Dim dt As System.Data.DataTable = dset.Tables(0)
      Dim dc As System.Data.DataColumn
      Dim dr As System.Data.DataRow
      Dim colIndex As Integer = 0
      Dim rowIndex As Integer = 0

      For Each dc In dt.Columns
          colIndex = colIndex + 1
          excel.Cells(1, colIndex) = dc.ColumnName
      Next

      For Each dr In dt.Rows
          rowIndex = rowIndex + 1
          colIndex = 0
          For Each dc In dt.Columns
              colIndex = colIndex + 1
              excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

          Next
      Next

      wSheet.Columns.AutoFit()
      Dim strFileName As String = "C:\tempdetails.xls"
      Dim blnFileOpen As Boolean = False
      Try
          Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
          fileTemp.Close()
      Catch ex As Exception
          blnFileOpen = False
      End Try

      'If System.IO.File.Exists(strFileName) Then
      '    System.IO.File.AppendText(strFileName)
      'End If

      wBook.SaveAs(strFileName)
      excel.Workbooks.Open(strFileName)
      excel.Visible = True

推荐答案

良好做法:切勿使用与对象相同的变量名称:excel，因为Microsoft.Office.Interop. ...

看看我的代码块:

Good practice: Never use the names of variables which are identical like objects: excel, becouse of Microsoft.Office.Interop.Excel...

Take a look at my code block:

Sub Export2Excel(ByVal sFileName as String)
'here is my correction:
Dim excApp As New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = excApp.Workbooks.Open(sFileName)
wSheet = wBook.WorkSheets(1)
'or
wSheet = wBook.WorkSheets("Sheet1")

'instructions to export data
'...
End Sub

例子:
使用VB.Net导出到Excel [ http://www.mssqltips.com/sqlservertip/1202/export -data-from-sql-server-to-excel/ [ http://www.dotnetcurry.com/ShowArticle.aspx?ID=323 [ ^ ]
3)使用ADO.NET数据集
http://social.msdn.microsoft.com /Forums/zh-CN/adodotnetdataset/thread/c6a7e528-38ef-4163-a3e4-fc0865fe55ab/ [

An Example:
Export to Excel using VB.Net[^]

Other (in my opinion better and quickest) ways to export data into excel file:
1) Direct from sql:
http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/[^]
2) Using SqlBulkCopy
http://www.dotnetcurry.com/ShowArticle.aspx?ID=323[^]
3) Using ADO.NET dataset
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/c6a7e528-38ef-4163-a3e4-fc0865fe55ab/[^]

这篇关于如何使用vb.net在Excel中添加书籍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！