How to Read data from Excel sheet using DataSet

While coding the Automation the situation came that we need to read data from excel sheet and store at one place to use that data through out the code and storing the data from excel sheet to the data set is a good option. Reading the data one by one from excel sheet would impact the performance of the automation code.
So below is the code which will read the data from excel sheet and would store in to dataset.
——————————————————————————————–
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
//
namespace ReadFromExcel
{
//Class to read excel sheet and store in to dataset
class Program
{
static void Main(string[] args)
{
//Local variable to store the excelsheet location
string excelSheetLocation = “c:\\TestCases.xls”;
//Local variable to store the connection string to talk with excel sheet
string excelConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + excelSheetLocation + “;Extended Properties=Excel 8.0”;
//Declare a dataset
DataSet myDataSet=new DataSet();
//Declare oledb connection
OleDbConnection con = new OleDbConnection(excelConnectionString);
//Open the connection to communicat with excel sheet
con.Open();
//Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
OleDbDataAdapter myCommand = new OleDbDataAdapter(” SELECT * FROM [sheet1$]”, con);
//filled the dataset with the data of excel sheet
myCommand.Fill(myDataSet);
//close the connection
con.Close();
//show the data on console window from dataset
//Total Number of rows in excel sheet
int totalRow = myDataSet.Tables[0].Rows.Count;
//trace through each rows
for (int i = 0; i < totalRow; i++)
{
//trace through each coloumn
for (int j = 0; j < myDataSet.Tables[0].Columns.Count;j++ )
{
//Show the data on to console window
Console.WriteLine(myDataSet.Tables[0].Rows[i][j].ToString());
} //end of for loop
} //end of for loop
} //end of main
} //end of class
} //end
———————————————————————————————-
Thanks!!

One thought on “How to Read data from Excel sheet using DataSet

  1. Hi,

    here is a another approach with GemBox.Spreadsheet Excel library:

    var ef = new ExcelFile();
    ef.LoadXls(“Excel file.xls”);

    // DataSet schema has to be defined before this.
    for(int i = 0; i < ef.Worksheets.Count; ++i)
    {
    var ws = ef.Worksheets[i];
    ws.ExtractToDataTable(dataSet.Tables[i], ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s