Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / Visual Basic

Read and Write Excel Files

Rate me:
Please Sign up or sign in to vote.
3.33/5 (20 votes)
3 Jul 2009CPOL 215K   27.3K   63   29
Read and Write Excel Files using VB.NET
Image 1

Introduction

This article helps user to handle excel file using VB.NET. It reads Excel file using OLEDB Provider and use an Interop.Excel.dll to write data into excel file.

Read data from Excel File.

For reading Excel file we are using ExcelHandler() class. Create an object of excel handler class, call method GetDataFromExcel, provide excel file name as a parameter. It will return all content of file in a dataset.

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

Write data into Excel File.

For writing data into Excel file we are using ExcelHandler() class. Create an object of excel handler class, call method ExportToExcel, provide excel file name, dataset of data, Title of file and string out parameter for error message as parameters it will create file at specified location.

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.

Copy below code or download attached file for excel handler class.

#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


	 

Points of Interest

I have face a lot of problems to find set data in proper format in excel file, also get particular column number and handling exceptions of com object. I handled all related problem in my class.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Accenture
India India
I am a Dot Net Developer working in MNC. I am interested to learn new technologies. I am always trying to improve my skills.

MCTS:ASP.NET 3.5
MCTS:WCF 3.5

Comments and Discussions

 
QuestionA first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll Pin
Member 1320563317-May-17 14:52
Member 1320563317-May-17 14:52 
QuestionRead and Write Excel Files - EXCEL instance left in Task Manager Pin
jamescollett28-May-15 8:28
jamescollett28-May-15 8:28 
Question5 out of 5 Pin
MarcusCole68331-May-15 6:42
professionalMarcusCole68331-May-15 6:42 
QuestionIssues with the Jet Pin
MarcusCole68331-May-15 4:27
professionalMarcusCole68331-May-15 4:27 
QuestionNeed Help Pin
Member 1076360121-Apr-14 1:17
Member 1076360121-Apr-14 1:17 
AnswerRe: Need Help Pin
MarcusCole68331-May-15 6:45
professionalMarcusCole68331-May-15 6:45 
QuestionCannot read the data Pin
dili12342-Oct-13 21:03
dili12342-Oct-13 21:03 
GeneralMy vote of 5 Pin
Member 1012667225-Jun-13 19:36
Member 1012667225-Jun-13 19:36 
QuestionQuery Pin
agshui2512-Jun-13 10:41
agshui2512-Jun-13 10:41 
QuestionWhile our app reading and writing excel file , I tried to open another excel file manually then app throw exception and close the excel file. Pin
Member 909227812-Dec-12 3:21
Member 909227812-Dec-12 3:21 
QuestionWindows 7 and Up Pin
stixoffire21-Apr-12 2:43
stixoffire21-Apr-12 2:43 
AnswerRe: Windows 7 and Up Pin
TheGodfather_2329-Oct-12 6:32
TheGodfather_2329-Oct-12 6:32 
Questioni have problem with witre into excel by useing ur code Pin
atefeh_boush19-Nov-11 22:24
atefeh_boush19-Nov-11 22:24 
GeneralMy vote of 5 Pin
BenCoding7-Jun-11 6:35
BenCoding7-Jun-11 6:35 
Questionhow to read hidden cell value in excel. Pin
_simi31-Aug-10 1:56
_simi31-Aug-10 1:56 
GeneralMy vote of 5 Pin
RITIK DODHIWALA5-Aug-10 1:15
RITIK DODHIWALA5-Aug-10 1:15 
GeneralError in export: Conversion from string "Error in export: Old format or i" to type 'Double' is not valid. Pin
kuschezor14-Jun-10 6:59
kuschezor14-Jun-10 6:59 
QuestionAdaptation? Pin
ahulting14-Apr-10 5:18
ahulting14-Apr-10 5:18 
AnswerRe: Adaptation? Pin
Vivek.Net14-Apr-10 20:09
Vivek.Net14-Apr-10 20:09 
GeneralGood Code for Reading Excel Files Pin
satcomengineer9-Feb-10 1:12
satcomengineer9-Feb-10 1:12 
GeneralAlternative Pin
FilipKrnjic9-Jul-09 5:49
FilipKrnjic9-Jul-09 5:49 
GeneralMy vote of 1 Pin
AxelM19-Jan-09 1:48
AxelM19-Jan-09 1:48 
GeneralGood Code for creating excel file Pin
shashi Bhoyar14-Jan-09 23:33
shashi Bhoyar14-Jan-09 23:33 
GeneralMy vote of 1 Pin
Selvin14-Jan-09 0:45
Selvin14-Jan-09 0:45 
GeneralMy vote of 1 Pin
Metaphor12-Jan-09 20:47
Metaphor12-Jan-09 20:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.