Export All The Excel Sheets to DataSet in C# and VB.NET

watch_later Sunday, September 1, 2019

Introduction


This article gives an explanation about how to convert all the sheets of excel file into the dataset as well as how to read excel files in c# and vb.net and also explains how to export all excel sheets to a dataset with example in C# and VB.NET.

Export All The Excel Sheets to DataSet in C# and VB.NET


Many students, beginners, and developers get such requirement where they read excel file and get data from the individual sheet and copy data of that excel sheet into data table to performs various orations on it. But still many of them don't know how to read all the excel sheet at once instead of reading the individual sheet, I got many emails from them for sharing such articles and explains how to read all the excel sheet and return data of all the excel sheet in the dataset that contains sheet wise data table.

While you working with excel sometimes you have multiple sheets in your single excel file and you want to read data from the all the sheets then there are many different ways are available on the internet to read that all the excel sheets but in this article, I will show you how you can read every excel sheet at once and return result in dataset. Suppose, you have a single excel file and that file contains 2 or 3 different excel sheets with different data and you wants to prepare sheet wise data in the table, like data of 1st excel sheet in table1, data of 2nd excel sheet in table2 and data of 3rd excel sheet in table3, then in this article I'll explain the same requirement with simple example in c# and VB.NET.

Requirement 


1) What is dataset? Explain in a single statement.
2) What is datatable? Explain in a single statement.
3) I have following excel file which contains two different sheets Employee and Department.

Excel Sheet

I want both sheets with its data in the dataset. The name of datatable in the dataset should be same as excel sheet name.
4) Explain how to read all the excel sheet from excel file and return a dataset in c# and vb.net.

Dataset


Dataset is a collection of a data table.

Datatable


Datatable is a collection of rows and columns.

Let's start with a simple example so you will get more idea about how to read excel file as well as read all the excel sheet and return a dataset.

Read all the excel sheet from excel file


Step 1: So, first, we have to create a new project in your visual studio and design a form same a shown in the screen above, where I have taken one textbox for getting excel file path and one simple button for reading excel file and return a dataset.

Step 2: Then after we have to add the following required namespace in the code behind.

C#

using System.Data.OleDb;

VB.NET

Imports System.Data.OleDb
Step 3: After adding a required namespace we have to create the following function for getting the name of sheets in the excel file and this method will returns a string array.

C#

public static string[] GetSheetNamesFromExcel(string connectionString)
        {
            try
            {
                DataTable dt = null;
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        String[] excelSheetNames = new String[dt.Rows.Count];
                        int i = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            excelSheetNames[i] = row["TABLE_NAME"].ToString();
                            i++;
                        }
                        return excelSheetNames;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
 
                throw ex;
            }
        }

VB.NET

Public Shared Function GetSheetNamesFromExcel(ByVal connectionString As StringAs String()
        Try
            Dim dt As DataTable = Nothing
            Dim connection As OleDbConnection = New OleDbConnection(connectionString)
            connection.Open()
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            If ((Not (dt) Is Nothing) _
                        AndAlso (dt.Rows.Count > 0)) Then
                Dim excelSheetNames() As String = New String((dt.Rows.Count) - 1) {}
                Dim i As Integer = 0
                For Each row As DataRow In dt.Rows
                    excelSheetNames(i) = row("TABLE_NAME").ToString
                    i = (i + 1)
                Next
                Return excelSheetNames
            Else
                Return Nothing
            End If
 
        Catch ex As Exception
            Throw ex
        End Try
 
    End Function

Explanation


As you can see from the screen we have declared a data table and creates an oledbconnection object connection and open the connection for thee read excel file and using GetOleDbSchemaTable method returns schema information of data source as indicated by GUID and set it into the data table. Then after we declared a string array and using loop set name of excel sheet into a string array and return that string array.

Step 4: Now we have to create the following method for convert excel sheet to the dataset and this method will return a dataset.

C#

public static DataSet ConvertExcelSheetToDataset(string filePath)
        {
            try
            {
                string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", filePath);
                 
                DataSet ds = new DataSet();
                foreach (var excelSheetName in GetSheetNamesFromExcel(connectionString))
                {
                    using (OleDbConnection connection = new OleDbConnection(connectionString))
                    {
                        var dataTable = new DataTable(excelSheetName.ToString().Replace("$"string.Empty));
                        string query = string.Format("SELECT * FROM [{0}]", excelSheetName);
                        connection.Open();
                        OleDbDataAdapter da = new OleDbDataAdapter(query, connection);
                        da.Fill(dataTable);
                        ds.Tables.Add(dataTable);
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
 
                throw ex;
            }
        }

VB.NET

Public Shared Function ConvertExcelSheetToDataset(ByVal filePath As StringAs DataSet
        Try
            Dim connectionString As String = String.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", filePath)
            Dim ds As DataSet = New DataSet
            For Each excelSheetName In GetSheetNamesFromExcel(connectionString)
                Dim connection As OleDbConnection = New OleDbConnection(connectionString)
                Dim dataTable = New DataTable(excelSheetName.ToString.Replace("$"String.Empty))
                Dim query As String = String.Format("SELECT * FROM [{0}]", excelSheetName)
                connection.Open()
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(query, connection)
                adapter.Fill(dataTable)
                ds.Tables.Add(dataTable)
            Next
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
 
    End Function

Explanation


As you can see in the above method here we have a written a connection string for reading excel sheet and store this connection string in a string variable connectionString. Then we have declared an object of the dataset and using foreach loop get the name of excel sheet one by one using the created function GetSheetNamesFromExcel as explained in step 3. Now, we have created the oledbconnection object and then create data table and pass the name of the table as an argument where the name of the sheet will name of the data table. Then generate a query to read data from the excel sheet and open the connection and created an object of OleDbDataAdapter class and then using the fill method of OleDbDataAdapter for add rows in the specified range in the dataset to match those in the data source using the data table name and then add that data table in the dataset using Tables.Add() and pass datatable as an argument. Finally, return declared dataset ds.

Step 5: Now, we have to write the following code on the click event of a button convert.

C#

private void btnConvert_Click(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(txtPath.Text.Trim()))
                {
                    DataSet ds = ConvertExcelSheetToDataset(txtPath.Text.Trim());
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        MessageBox.Show("ExcelSheet To Dataset converted successfully.");
                    }
                }
            }
            catch (Exception ex)
            {
 
                throw ex;
            }
        }

VB.NET

Private Sub btnConvert_Click(sender As Object, e As EventArgsHandles btnConvert.Click
        Try
            If Not String.IsNullOrEmpty(txtPath.Text.Trim) Then
                Dim ds As DataSet = ConvertExcelSheetToDataset(txtPath.Text.Trim)
                If ((Not (ds) Is Nothing) _
                            AndAlso (ds.Tables.Count > 0)) Then
                    MessageBox.Show("ExcelSheet To Dataset converted successfully.")
                End If
 
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Explanation


As you can see in the screen above if the path of excel file is not blank or empty then we have used ConvertExcelSheetToDataset method as explained in step 4 and pass the file path as a parameter and as you know ConvertExcelSheetToDataset will return dataset so we sored a result in a dataset and if dataset is not null and if contains at least one data table then simply display message something like ExcelSheet To Dataset converted successfully, Now, you have all the data for your excel file in data tables so instead of message you can do your further operations based on the contained data tables. 

Output


While you debug the code as per shown in the screen below and check the dataset visualizer then you can find following data table as per data contained in the excel sheet.
Dataset Visualizer

Dataset Visualizer Datatables

Employee Table

Department Table

Known Errors 


While you will create a connection object of oledbconnection and pass connection string as an argument and then when you will open the connection using the connection.open() you might get the following error.

Unhandled Exception

I already shared an article about how to fix this error, So you can visit this article to fix this error. There are many solutions are available for this king of an error on the internet but in my article, I have explained each and every possible way in easy steps so you can read my article the 'microsoft.ace.oledb.12.0' the provider has not registered on the local machine also.

Summary


In this article, we have learned how to read all the excel sheets from a single excel file using c# and vb.net.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon