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.
In my previous article, I explained how to read and write a text file in asp.net using c# and how to export dataset/datatable to CSV file using c# and vb.net and export JSON data to excel/CSV file using angular js with bootstrap and how to fix the error the 'microsoft.ace.oledb.12.0' the provider is not registered on the local machine.
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.
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 String) As 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 String) As 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 EventArgs) Handles 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.
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.
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.
