vb导出到excel

Option Explicit

Private Sub Form_Load()

Dim i As Long, j As Long

Me.MSHFlexGrid1.Rows = 2000

Me.MSHFlexGrid1.Cols = 10

For i = 0 To Me.MSHFlexGrid1.Rows - 1

For j = 0 To Me.MSHFlexGrid1.Cols - 1

Me.MSHFlexGrid1.TextMatrix(i, j) = i &"行" &j &"列"

Next

Next

Debug.Print Me.MSHFlexGrid1.TextArray(100)

End Sub

Private Sub cmdExport_Click()

Dim i As Long, j As Long

Dim CellsData() As String

Dim objApp As Excel.Application

Dim objWorkbook As Excel.Workbook

Dim objWorksheet As Excel.Worksheet

Dim objRange As Excel.Range

'构造二维数组

ReDim CellsData(1 To Me.MSHFlexGrid1.Rows, 1 To Me.MSHFlexGrid1.Cols)

For i = 1 To Me.MSHFlexGrid1.Rows

For j = 1 To Me.MSHFlexGrid1.Cols

CellsData(i, j) = Me.MSHFlexGrid1.TextMatrix(i - 1, j - 1)

Next

Next

'导出到Excel中

Set objApp = New Excel.Application

objApp.ScreenUpdating = False '禁止屏幕刷新

Set objWorkbook = objApp.Workbooks.Add

Set objWorksheet = objWorkbook.Sheets.Add

Set objRange = objWorksheet.Range(objWorksheet.Cells(1, 1), objWorksheet.Cells(Me.MSHFlexGrid1.Rows, Me.MSHFlexGrid1.Cols))

objRange.Value = CellsData

objApp.Visible = True

objApp.ScreenUpdating = True

'销毁二维数组

Erase CellsData

Me.SetFocus

MsgBox "导出完毕"

End Sub

介绍

下面通过一步一步的介绍，如何通过VB.NET来读取数据，并且将数据导入到Excel中。

第一步：

打开VS开发工具，并且添加引用。

然后选择。

Microsoft Excel 12.0 object library and。

Microsoft Excel 14.0 object library。

第二步：

创建一个Excle在你的电脑中。

第三步：

在VS中写入如下代码：

Imports System.Data

Imports System.Data.SqlClient

Imports Excel = Microsoft.Office.Interop.Excel。

Public Class excel

‘添加按钮

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

      Handles Button1.Click

      Try

          '创建连接

          Dim cnn As DataAccess = New DataAccess(CONNECTION_STRING)

          

          Dim i, j As Integer

          '创建Excel对象

          Dim xlApp As Microsoft.Office.Interop.Excel.Application

          Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

          Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

          Dim misValue As Object = System.Reflection.Missing.Value

          xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass

          xlWorkBook = xlApp.Workbooks.Add(misValue)

          ' 打开某一个表单

          xlWorkSheet = xlWorkBook.Sheets("sheet1")

          ' sql查询

          '  xlWorkBook.Sheets.Select("A1:A2")

          Dim sql As String = "SELECT * FROM EMP"

          ' SqlAdapter

          Dim dscmd As New SqlDataAdapter(sql, cnn.ConnectionString)

          ' 定义数据集

          Dim ds As New DataSet

          dscmd.Fill(ds)

         ‘添加字段信息到Excel表的第一行

          xlWorkSheet.Cells(1, 1).Value = "First Name"

          xlWorkSheet.Cells(1, 2).Value = "Last Name"

          xlWorkSheet.Cells(1, 3).Value = "Full Name"

          xlWorkSheet.Cells(1, 4).Value = "Salary"

          ' 将数据导入到excel

            For i = 0 To ds.Tables(0).Rows.Count - 1

              'Column

              For j = 0 To ds.Tables(0).Columns.Count - 1

                  ' this i change to header line cells >>>

                  xlWorkSheet.Cells(i + 3, j + 1) = _

                  ds.Tables(0).Rows(i).Item(j)

              Next

          Next

          'HardCode in Excel sheet

          ' this i change to footer line cells  >>>

         xlWorkSheet.Cells(i + 3, 7) = "Total"

          xlWorkSheet.Cells.Item(i + 3, 8) = "=SUM(H2:H18)"

          ' 保存到Excel

          xlWorkSheet.SaveAs("D:\vbexcel.xlsx")

          xlWorkBook.Close()

          xlApp.Quit()

          releaseObject(xlApp)

          releaseObject(xlWorkBook)

          releaseObject(xlWorkSheet)

          'd出对话框显示保存后的路径

          MsgBox("You can find the file D:\vbexcel.xlsx")

      Catch ex As Exception

      End Try

  End Sub

  ' Function of Realease Object in Excel Sheet

  Private Sub releaseObject(ByVal obj As Object)

      Try

          System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

          obj = Nothing

      Catch ex As Exception

          obj = Nothing

      Finally

          GC.Collect()

      End Try

  End Sub

End Class

复制代码。

第四步：

看到如下导出结果。

   

---