使用 VB.NET 导出到 Excel

• 下载源代码 - 450.5 KB

引言

目前需要将数据导出到 Microsoft Office 的 Excel。但许多人使用非常复杂的代码来进行简单的导出，无论是从 Datagrid 还是 DataTable。我使用简单的 Dataset 来导出数据。

背景

介绍从 datagrid 或通过 Datatable 发送数据的方法。我使用了易于使用的 Dataset。但为了代码优化，与 Reader 和 DataTable 相比，不建议使用 dataset。

Using the Code

目前，可用的导出数据到 Excel 的代码都比较完整，但不够直接，因为它们使用了下面的 Datagrid 和 DataTable

从 DataTable 到 Excel 表格

          Dim dt1 As New DataTable
          Dim I1, J1 As Integer
          For I1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                dt1.Columns.Add(dsmas1.Tables(0).Columns(I1).ColumnName)
           Next
           For I1 = 0 To dsmas1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt1.NewRow
                For J1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                    DR.Item(J1) = dsmas1.Tables(0).Rows(I1).ItemArray(J1)
                Next
                dt1.Rows.Add(DR)
           Next
           rel_ds.Tables.Add(dt1)
           Dim dt As New DataTable
           Dim I, J As Integer
           For I = 0 To dschd1.Tables(0).Columns.Count - 1
                dt.Columns.Add(dschd1.Tables(0).Columns(I).ColumnName)
           Next
           For I = 0 To dschd1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt.NewRow
                For J = 0 To dschd1.Tables(0).Columns.Count - 1
                    DR.Item(J) = dschd1.Tables(0).Rows(I).ItemArray(J)
                Next
                dt.Rows.Add(DR)
           Next
            rel_ds.Tables.Add(dt) 

对于 DataGrid 到 Excel 表格

        'verfying the datagridview having data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        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 = "D:\ss.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.Delete(strFileName)
        End If

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

但我只使用了 Dataset 到 Excel 表格。

在代码中，我使用了一个用户定义的函数，名为“Load_Excel_Details()”，其中我使用了旧的技术，例如为 Excel 创建对象，为工作簿添加工作表，为数据添加工作表。

例如：

• Excel <-- System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
  • Book <------ Excel Workbooks.Add()
    • Sheet <----- Excel SheetsInNewWorkbook = 1
      • Data <----- Excel cells(1, i).value

这在代码中使用的如下：

 With Excel
       .SheetsInNewWorkbook = 1
       .Workbooks.Add()
       .Worksheets(1).Select()

      Dim i As Integer = 1
      For col = 0 To ComDset.Tables(0).Columns.Count - 1
           .cells(1, i).value = ComDset.Tables(0).Columns(col).ColumnName
           .cells(1, i).EntireRow.Font.Bold = True
           i += 1
      Next

            i = 2

       Dim k As Integer = 1
       For col = 0 To ComDset.Tables(0).Columns.Count - 1
          i = 2
          For row = 0 To ComDset.Tables(0).Rows.Count - 1
              .Cells(i, k).Value = ComDset.Tables(0).Rows(row).ItemArray(col)
               i += 1
          Next
               k += 1
       Next
          filename = "c:\File_Exported.xls"
         .ActiveCell.Worksheet.SaveAs(filename)
 End With

关注点

我使用了简单的 For 循环....并以行、列和单元格格式插入值。

历史

我提出这个方案是在我的公司项目中使用的，并得到了客户的赞赏。