Click here to Skip to main content
12,954,522 members (61,455 online)
Click here to Skip to main content
Add your own
alternative version


63 bookmarked
Posted 12 Jan 2009

Read and Write Excel Files

, 3 Jul 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Read and Write Excel Files using VB.NET


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
            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

            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
            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

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

    End If
End Sub


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")
        Catch ex As OleDbException
        Catch ex As Exception
        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
                    ' 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
                Catch ex As Exception
                End Try
        End If
        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
            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
                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"
                    xlObject.Caption = a_sFileTitle
                End If

                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."
                    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
                    If xlWB IsNot Nothing Then
                        xlWB.Close(Nothing, Nothing, Nothing)
                    End If
                    If rg IsNot Nothing Then
                    End If
                    If xlSh IsNot Nothing Then
                    End If
                    If xlWB IsNot Nothing Then
                    End If
                    If xlObject IsNot Nothing Then
                    End If

                End Try
                xlSh = Nothing
                xlWB = Nothing
                xlObject = Nothing
                ' force final cleanup! '
            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) {}

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

            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")
                            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
                            obj(i, j) = a_dtData.Rows(i - 1)(j)
                        End If
                        obj(i, j) = a_dtData.Rows(i - 1)(j)
                    End If

        Catch ex As Exception
        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.


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


About the Author

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.


You may also be interested in...


Comments and Discussions

QuestionA first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll Pin
Member 1320563317-May-17 14:52
memberMember 1320563317-May-17 14:52 
QuestionRead and Write Excel Files - EXCEL instance left in Task Manager Pin
jamescollett28-May-15 8:28
memberjamescollett28-May-15 8:28 
Question5 out of 5 Pin
Member 114926491-May-15 6:42
memberMember 114926491-May-15 6:42 
QuestionIssues with the Jet Pin
Member 114926491-May-15 4:27
memberMember 114926491-May-15 4:27 
QuestionNeed Help Pin
Member 1076360121-Apr-14 1:17
memberMember 1076360121-Apr-14 1:17 
AnswerRe: Need Help Pin
Member 114926491-May-15 6:45
memberMember 114926491-May-15 6:45 
QuestionCannot read the data Pin
dili12342-Oct-13 21:03
memberdili12342-Oct-13 21:03 
GeneralMy vote of 5 Pin
Member 1012667225-Jun-13 19:36
memberMember 1012667225-Jun-13 19:36 
QuestionQuery Pin
Member 1010593112-Jun-13 10:41
memberMember 1010593112-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
memberMember 909227812-Dec-12 3:21 
Hi Vivek,
we developed application one VB.Net application, we are performing read/write operations on Excel as you guide .
While running the application, if we try to open other excel file(s) manually then
- the application is throwing an exception,
- the other excel file is closed automatically and
- the application is also closed automatically.

Please help me to resolve this issue.
QuestionWindows 7 and Up Pin
stixoffire21-Apr-12 2:43
memberstixoffire21-Apr-12 2:43 
AnswerRe: Windows 7 and Up Pin
TheGodfather_2329-Oct-12 6:32
memberTheGodfather_2329-Oct-12 6:32 
Questioni have problem with witre into excel by useing ur code Pin
atefeh_boush19-Nov-11 22:24
memberatefeh_boush19-Nov-11 22:24 
GeneralMy vote of 5 Pin
BenJAM'N7-Jun-11 6:35
memberBenJAM'N7-Jun-11 6:35 
Questionhow to read hidden cell value in excel. Pin
jaanu_simi31-Aug-10 1:56
memberjaanu_simi31-Aug-10 1:56 
GeneralMy vote of 5 Pin
memberRITIK 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
memberkuschezor14-Jun-10 6:59 
QuestionAdaptation? Pin
ahulting14-Apr-10 5:18
memberahulting14-Apr-10 5:18 
AnswerRe: Adaptation? Pin
Vivek.Net14-Apr-10 20:09
memberVivek.Net14-Apr-10 20:09 
GeneralGood Code for Reading Excel Files Pin
satcomengineer9-Feb-10 1:12
membersatcomengineer9-Feb-10 1:12 
GeneralAlternative Pin
FilipKrnjic9-Jul-09 5:49
memberFilipKrnjic9-Jul-09 5:49 
GeneralMy vote of 1 Pin
AxelM19-Jan-09 1:48
memberAxelM19-Jan-09 1:48 
GeneralGood Code for creating excel file Pin
shashi Bhoyar14-Jan-09 23:33
membershashi Bhoyar14-Jan-09 23:33 
GeneralMy vote of 1 Pin
Selvin14-Jan-09 0:45
memberSelvin14-Jan-09 0:45 
GeneralMy vote of 1 Pin
Metaphor12-Jan-09 20:47
memberMetaphor12-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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170525.1 | Last Updated 3 Jul 2009
Article Copyright 2009 by Vivek.Net
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid