pnsoftwarestudies: Simplest Code to Read and Import Excel file into DataSet or DataTable using C#

Hi everyone in this post I will show you the simplest Code I mean the most simplest code in C# to Read an Excel File.

Writing this post because of a scenario in Office where i had to generate XML Files by reading an Excel File,

So one of my approach was to read the Excel file using C# console application and populate into dataset and later use the xsd.exe to generate the XML File.

In this post I am talking only half my approach that is to populate the Excel data into DataSet Table using OLEDB.

Note About OLEDB - Microsoft designed ODBC to access SQL data and OLE DB to access any type of data in a COM environment.

Code to Read Excel and Store into Data Table using C#

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.OleDb;

namespace ReadExcelFileIntoDataSetInCSharp

{

class Program

{

static void Main(string[] args)

{

String file_path = "C:\\ExcelData.xlsx";

String excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

DataTable dataTableObj = new DataTable("ExcelDataTable");

OleDbDataAdapter adp = new OleDbDataAdapter("SELECT [SLNo],[Name],[Age] FROM [Sheet1$]", excelConnectionString);

adp.Fill(dataTableObj);

excelConnection.Close();

}

Explanation

Line 1 - String file_path = "C:\\ExcelData.xlsx";

Path where the Excel file is located Note – Use Double backslashes in you path.

Line 2 - String excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

Connection String Use as it is , we also can see that the file_path string variable created in Line 1 is used here.

Line 3 - OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

Create an OleDbConnection object by passing the connection string.

Line 4 – DataTable dataTableObj = new DataTable("Particulars");

Create a DataTable with name ExcelDataTable, Note – You can give any name as desired.

Line 5 - OleDbDataAdapter adp = new OleDbDataAdapter("SELECT [SLNo],[Name],[Age] FROM [Sheet1$]", excelConnectionString);

Create a OleDBDataAdapter object by passing two parameter –

Parameter 1 – Select Query with Column Names to be read from Excel In Our case the column names are SLNo, Name, Age and name of the Excel Shee you are reading in our case it is Sheet1 Hence put as - Sheet1$