Click here to Skip to main content
Click here to Skip to main content

Read and Write Excel Files

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

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)

Share

About the Author

Vivek.Net
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

 
QuestionNeed Help PinmemberMember 1076360121-Apr-14 1:17 
QuestionCannot read the data [modified] Pinmemberdili12342-Oct-13 21:03 
GeneralMy vote of 5 PinmemberMember 1012667225-Jun-13 19:36 
QuestionQuery PinmemberMember 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. PinmemberMember 909227812-Dec-12 3:21 
QuestionWindows 7 and Up Pinmemberstixoffire21-Apr-12 2:43 
AnswerRe: Windows 7 and Up PinmemberTheGodfather_2329-Oct-12 6:32 
Questioni have problem with witre into excel by useing ur code Pinmemberatefeh_boush19-Nov-11 22:24 
GeneralMy vote of 5 PinmemberBenJAM'N7-Jun-11 6:35 
Code works. And I like the handling of excel data.
Questionhow to read hidden cell value in excel. Pinmemberjaanu_simi31-Aug-10 1:56 
GeneralMy vote of 5 PinmemberRITIK DODHIWALA5-Aug-10 1:15 
GeneralError in export: Conversion from string "Error in export: Old format or i" to type 'Double' is not valid. Pinmemberkuschezor14-Jun-10 6:59 
QuestionAdaptation? Pinmemberahulting14-Apr-10 5:18 
AnswerRe: Adaptation? PinmemberVivek.Net14-Apr-10 20:09 
GeneralGood Code for Reading Excel Files Pinmembersatcomengineer9-Feb-10 1:12 
GeneralAlternative PinmemberFilipKrnjic9-Jul-09 5:49 
GeneralMy vote of 1 PinmemberAxelM19-Jan-09 1:48 
GeneralGood Code for creating excel file Pinmembershashi Bhoyar14-Jan-09 23:33 
GeneralMy vote of 1 PinmemberSelvin14-Jan-09 0:45 
GeneralMy vote of 1 PinmemberMetaphor12-Jan-09 20:47 
GeneralMy vote of 1 Pinmembersuresh suthar12-Jan-09 18:51 
GeneralMy vote of 1 PinmemberDpMozz12-Jan-09 18:09 
QuestionWhats the fun ? PinmemberMember 366281812-Jan-09 15:16 
GeneralMy vote of 2 PinmemberTarabanko Yury12-Jan-09 9:24 

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

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

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