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!!
