Update the Excel sheet using c#

Some time I got the requiremnt where I need to update the excel sheet corresponding to testcases through the code whether the test case is failed or passed while automation is running and send the updated testcases excel with current status of testcases through the email at the end of automation code.
i hope that this would be very usefull for you.

Below is the code for that purpose :
----------------------------------------------------------------
//write to excel sheet
public void writeToExcelsheet(int rowNumber, string status)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = Program.excelSheetDeclaration;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText =
"Update [sheet1$] Set Status =\"" + status + "\" WHERE TestCaseID ="+rowNumber;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
catch (Exception ex)
{
//
}
} //end of writeToExcelsheet method.
----------------------------------------------------
Thanks!!

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

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates