读取和写入 Excel 文件

• 下载 ExcelFileHandling_demo - 284.16 KB
• 下载 ExcelFileHandling_src - 2.38 KB
• 下载 Interop - 261.58 KB

引言

本文档帮助用户使用VB.NET处理Excel文件。它使用OLEDB提供程序读取Excel文件，并使用Interop.Excel.dll将数据写入Excel文件。

从Excel文件读取数据。

为了读取Excel文件，我们使用ExcelHandler()类。创建excel处理类的一个对象，调用GetDataFromExcel方法，将Excel文件名作为参数传递。它将以数据集的形式返回文件中的所有内容。

			
    Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click
        If Not String.IsNullOrEmpty(txtFileName.Text) Then
            Try
                btnClose.Enabled = False
                Dim OExcelHandler As New ExcelHandler()
                Dim ds As DataSet = OExcelHandler.GetDataFromExcel(txtFileName.Text.Trim())

                If ds IsNot Nothing Then
                    dgvExcelData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                    dgvExcelData.EditMode = DataGridViewEditMode.EditProgrammatically
                    dgvExcelData.DataSource = ds.Tables(0)
                End If
               
            Catch ex As Exception

            Finally
                btnClose.Enabled = True
            End Try
        End If
    End Sub

	

将数据写入Excel文件。

为了将数据写入Excel文件，我们使用ExcelHandler()类。创建excel处理类的一个对象，调用ExportToExcel方法，将Excel文件名、数据集、文件标题以及用于错误消息的字符串输出参数作为参数传递。它将在指定位置创建文件。

			
    Private Sub btnWrite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWrite.Click
        If Not String.IsNullOrEmpty(txtFileName.Text) Then
            Dim ErrorMessage As String = String.Empty
            Dim OExcelHandler As New ExcelHandler()
            btnClose.Enabled = False
            Try
                Dim ds As DataSet = GetGridData()
                If ds IsNot Nothing Then
                    OExcelHandler.ExportToExcel(txtFileName.Text.Trim(), ds, "Write In Excel", ErrorMessage)
                End If
            Catch ex As Exception

            Finally
                btnClose.Enabled = True
                If Not String.IsNullOrEmpty(ErrorMessage) Then
                    MessageBox.Show(ErrorMessage)
                Else
                    MessageBox.Show("Operation Successful!")
                End If
            End Try

        End If
    End Sub

	

ExcelHandler。

复制以下代码或下载附件中的文件以获取excel处理类。

 			
#Region " Information "
 ' Class Name : Excel File Handler '
 ' Programmer : Vivek Purohit '
 ' Purpose    : Handle Excel File Operations. '
 ' Date       : 20-Dec-2008'
   #End Region

#Region " Import Section"
 Imports System
 Imports System.Collections.Generic
 Imports System.Text
 Imports System.Data
 Imports System.Data.OleDb
 Imports Excel
 Imports System.Reflection
 Imports System.Runtime.InteropServices
#End Region


' Excel File handler used to read and write excel file. '
 Public Class ExcelHandler
    
    ' Return data in dataset from excel file. '   
    Public Function GetDataFromExcel(ByVal a_sFilepath As String) As DataSet
        Dim ds As New DataSet()
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
        Try
            cn.Open()
        Catch ex As OleDbException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        ' It Represents Excel data table Schema.'
        Dim dt As New System.Data.DataTable()
        dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then
            For sheet_count As Integer = 0 To dt.Rows.Count - 1
                Try
                    ' Create Query to get Data from sheet. '
                    Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()
                    Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
                    da.Fill(ds, sheetname)
                Catch ex As DataException
                    Console.WriteLine(ex.Message)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            Next
        End If
        cn.Close()
        Return ds
    End Function

    
    ' Write Excel file as given file name with given data.'  
    
    Public Function ExportToExcel(ByVal a_sFilename As String, ByVal a_sData As DataSet, ByVal a_sFileTitle As String, ByRef a_sErrorMessage As String) As Boolean
        a_sErrorMessage = String.Empty
        Dim bRetVal As Boolean = False
        Dim dsDataSet As DataSet = Nothing
        Try
            dsDataSet = a_sData

            Dim xlObject As Excel.Application = Nothing
            Dim xlWB As Excel.Workbook = Nothing
            Dim xlSh As Excel.Worksheet = Nothing
            Dim rg As Range = Nothing
            Try
                xlObject = New Excel.Application()
                xlObject.AlertBeforeOverwriting = False
                xlObject.DisplayAlerts = False

                ' This Adds a new woorkbook, you could open the workbook from file also '
                xlWB = xlObject.Workbooks.Add(Type.Missing)
                xlWB.SaveAs(a_sFilename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value)

                xlSh = DirectCast(xlObject.ActiveWorkbook.ActiveSheet, Excel.Worksheet)

                Dim sUpperRange As String = "A1"
                Dim sLastCol As String = "E"
                Dim sLowerRange As String = sLastCol + (dsDataSet.Tables(0).Rows.Count + 1).ToString()

                rg = xlSh.Range(sUpperRange, sLowerRange)
                rg.Value2 = GetData(dsDataSet.Tables(0))

                ' formating '
                xlSh.Range("A1", sLastCol & "1").Font.Bold = True
                xlSh.Range("A1", sLastCol & "1").HorizontalAlignment = XlHAlign.xlHAlignCenter
                xlSh.Range(sUpperRange, sLowerRange).EntireColumn.AutoFit()

                If String.IsNullOrEmpty(a_sFileTitle) Then
                    xlObject.Caption = "untitled"
                Else
                    xlObject.Caption = a_sFileTitle
                End If

                xlWB.Save()
                bRetVal = True
            Catch ex As System.Runtime.InteropServices.COMException
                If ex.ErrorCode = -2147221164 Then
                    a_sErrorMessage = "Error in export: Please install Microsoft Office (Excel) to use the Export to Excel feature."
                ElseIf ex.ErrorCode = -2146827284 Then
                    a_sErrorMessage = "Error in export: Excel allows only 65,536 maximum rows in a sheet."
                Else
                    a_sErrorMessage = (("Error in export: " & ex.Message) + Environment.NewLine & " Error: ") + ex.ErrorCode
                End If
            Catch ex As Exception
                a_sErrorMessage = "Error in export: " & ex.Message
            Finally
                Try
                    If xlWB IsNot Nothing Then
                        xlWB.Close(Nothing, Nothing, Nothing)
                    End If
                    xlObject.Workbooks.Close()
                    xlObject.Quit()
                    If rg IsNot Nothing Then
                        Marshal.ReleaseComObject(rg)
                    End If
                    If xlSh IsNot Nothing Then
                        Marshal.ReleaseComObject(xlSh)
                    End If
                    If xlWB IsNot Nothing Then
                        Marshal.ReleaseComObject(xlWB)
                    End If
                    If xlObject IsNot Nothing Then
                        Marshal.ReleaseComObject(xlObject)
                    End If

                Catch
                End Try
                xlSh = Nothing
                xlWB = Nothing
                xlObject = Nothing
                ' force final cleanup! '
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End Try
        Catch ex As Exception
            a_sErrorMessage = "Error in export: " & ex.Message
        End Try

        Return bRetVal
    End Function

    
    ' returns data as two dimentional object array.   ' 
    Private Function GetData(ByVal a_dtData As System.Data.DataTable) As Object(,)
        Dim obj As Object(,) = New Object((a_dtData.Rows.Count + 1) - 1, a_dtData.Columns.Count - 1) {}

        Try
            For j As Integer = 0 To a_dtData.Columns.Count - 1
                obj(0, j) = a_dtData.Columns(j).Caption
            Next

            Dim dt As New DateTime()
            Dim sTmpStr As String = String.Empty

            For i As Integer = 1 To a_dtData.Rows.Count
                For j As Integer = 0 To a_dtData.Columns.Count - 1
                    If a_dtData.Columns(j).DataType Is dt.[GetType]() Then
                        If a_dtData.Rows(i - 1)(j) IsNot DBNull.Value Then
                            DateTime.TryParse(a_dtData.Rows(i - 1)(j).ToString(), dt)
                            obj(i, j) = dt.ToString("MM/dd/yy hh:mm tt")
                        Else
                            obj(i, j) = a_dtData.Rows(i - 1)(j)
                        End If
                    ElseIf a_dtData.Columns(j).DataType Is sTmpStr.[GetType]() Then
                        If a_dtData.Rows(i - 1)(j) IsNot DBNull.Value Then
                            sTmpStr = a_dtData.Rows(i - 1)(j).ToString().Replace(vbCr, "")
                            obj(i, j) = sTmpStr
                        Else
                            obj(i, j) = a_dtData.Rows(i - 1)(j)
                        End If
                    Else
                        obj(i, j) = a_dtData.Rows(i - 1)(j)
                    End If

                Next
            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        Return obj
    End Function

End Class


	 

关注点

我在将数据以正确的格式设置到Excel文件中，以及获取特定列号和处理COM对象的异常方面遇到了很多问题。我在我的类中处理了所有相关问题。