Read from Excel and write to another Excel SpreadSheet using C# Win Forms

My Team Manager has a requirement of getting Email address of a District Managers in a Spread sheet. Where he gave me First and the Last name of the persons. IN one of our application i can go and enter the name and it returns me the person details. So I can pick email address and past in excel spreadsheet. It's kind of time taking process. Because our app is a huge application and it take long time to get the result. So i decided generate a spread sheet with names and their email address in one button click. Read data from excel and write to excel.

So i created a test.xls and added the entire first and last name in both the columns. And created a win form app where Button click Read Excel and another button click is generate excel with names and email address.

Create a table tblemp with columns EMP_PK, EMP_FNAME, EMP_LNAME, and EMP_EMAIL.

You have to reference the Microsoft.Office.Interop.Excel; in your project reference folder. To use the Excel object in you app. I created a database connection in app config.

Create a connection based on your Database provider User ID Password and Data Source and out in App Settings tag in app.config file.

First Name Last Name
James mic
Rick onion
Mic son
Dave hart
Chris moris
Tom Letter
Mark June
Jim teens
Frank juhi
toly budd
Tom berry

Create a table tblemp with columns EMP_PK,EMP_FNAME,EMP_LNAME,EMP_EMAIL.

ce.Interop.Excel;

namespace GenerateEmailAddress
{
public partial class Form1 : Form
{
System.Data.OleDb.OleDbConnection oledbconn;
OleDbCommand oledbcommand;
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
DataRow dr;
DataColumn dc;
OleDbDataAdapter oledbreader;
System.Data.DataTable dt = new System.Data.DataTable();

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
try
{
string conn = System.Configuration.ConfigurationSettings.AppSettings["ConnString"];
oledbconn = new System.Data.OleDb.OleDbConnection(conn);
oledbconn.Open();


dc = new DataColumn("EMP_PK");
dt.Columns.Add(dc);
dc = new DataColumn("EMP_EMAIL");
dt.Columns.Add(dc);
}
catch (Exception ex)
{ }
}

private void btnReadExcel_Click(object sender, EventArgs e)
{

// Path for the test excel application
string Path = @"c:\Test.xls";
// Initialize the Excel Application class
ApplicationClass app = new ApplicationClass();
// Create the workbook object by opening the excel file.
Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// Get the active worksheet using sheet name or active sheet
Worksheet workSheet = (Worksheet)workBook.ActiveSheet;

// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
int index = 1;
object rowIndex = 1;
object colIndex1 = 1;
object colIndex2 = 2;

try
{
while (((Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
{
// Read the Cells to get the required value.
string firstName = ((Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string lastName = ((Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
//Console.WriteLine("Name : {0},{1} ", firstName, lastName);
index++;
rowIndex = index;

string strSqlEmail = "Select EMP_PK,EMP_EMAIL from tblemp where EMP_FNAME = upper('" + firstName + "') AND EMP_LNAME = upper('" + lastName + "')) order by EMP_PK asc";

oledbreader = new OleDbDataAdapter(strSqlEmail, oledbconn);
oledbreader.Fill(dt);
//oledbreader = oledbcommand.ExecuteReader();
dr = dt.NewRow();
dr["EMP_NAME"] = dt.Rows[0]["EMP_FNAME"].ToString() + dt.Rows[0]["EMP_LNAME"].ToString();
dr["EMP_EMAIL"] = dt.Rows[0]["EMP_EMAIL"].ToString(); ;
dt.Rows.Add(dr);


}
ds1.Tables.Add(dt);
MessageBox.Show("READING COMPLETED");
}
catch (Exception ex)
{
// Log the exception and quit...
app.Quit();
//Console.WriteLine(ex.Message);
}
finally
{
//ds1.Dispose();
}
}

private void btnGenerateExcel_Click(object sender, EventArgs e)
{
//dr = dt.NewRow();
ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);

//System.Data.DataSet dstable = ds1.Tables[;
int ColumnIndex = 0;
//for (int j = 0; j <= ds1.Tables.Count-1; j++)
//{
foreach (DataColumn col in ds1.Tables[0].Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
//}

int rowIndex = 0;

foreach (DataRow row in ds1.Tables[0].Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in ds1.Tables[0].Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
// worksheet.SaveAs("C:\\Family_Details.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


}
}
}