Guide For .Net Developers.........

RETRIEVING DATA FROM EXCEL SHEETS TO DATA GRID VIEW

/*EXCEL SHEET DATA FIELDS SHOULD MATCH THE VIRTUAL TABLE COLUMNS. THERE CAN BE EXTRA FIELDS IN VIRTUAL TABLE BUT IT MUST POSSESS THE FIELDS CONTAINED IN SHEET..*/

// virtual table declaration

System.Data.DataTable tabModule = new System.Data.DataTable();

/* virtual table that acts as datasource for data grid view....
we can perform any kind of updation in that virtual table without affecting the actual record.
after performing updation perform save operation to reflect changes in the original data.*/

//definition of virtual table that matches the actual data schema

private void virtual_table()
{
DataColumn c0 = new DataColumn("id");
DataColumn c1 = new DataColumn("Name");
DataColumn c2 = new DataColumn("class");
DataColumn c3 = new DataColumn("Eng");
DataColumn c4 = new DataColumn("Math");
DataColumn c5 = new DataColumn("Science");
DataColumn c6 = new DataColumn("Marks Obt");
DataColumn c7 = new DataColumn("Total Marks");
DataColumn c8 = new DataColumn("%age");

tabModule.Columns.Add(c0);
tabModule.Columns.Add(c1);
tabModule.Columns.Add(c2);
tabModule.Columns.Add(c3);
tabModule.Columns.Add(c4);
tabModule.Columns.Add(c5);
tabModule.Columns.Add(c6);
tabModule.Columns.Add(c7);
tabModule.Columns.Add(c8);

DataColumn[] pk = new DataColumn[1];
pk[0] = c0;

tabModule.PrimaryKey = pk;
tabModule.Clear();
}


/* sheet location variable stores the location of file and acts as soure for data provider object*/

string sheet_location;

//button on the interface the shows open file dialog box
private void btnBrowse_Click(object sender, EventArgs e)
{

// opd stands for open file dialog.....
opd1.ShowDialog();

// puts the selected file path into sheet_location
sheet_location = opd1.FileName;

// shows the path in the textbox on the interface....

textBox1.Text = sheet_location;
}


/* the main code..that makes connection with excel sheet and retrieves data from it....*/

/*on clicking load button on interface data is loaded into data grid view*/

private void btnLoad_Click(object sender, EventArgs e)
{

// textbox1 contains the location of file....

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " +textBox1.Text+ ";" + "Extended Properties="+";"+"Excel 8.0;HDR=YES;";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT * from [mk$]";
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataSet cities = new DataSet();
virtual_table();
System.Data.DataTable temp = new System.Data.DataTable();
adapter.Fill(temp);

foreach (DataRow rw in temp.Rows)
{
DataRow tempRow = tabModule.NewRow();
tempRow[0] = rw[0];
tempRow[1] = rw[1];
tempRow[2] = rw[2];
tempRow[3] = rw[3];
tempRow[4] = rw[4];
tempRow[5] = rw[5];
tempRow[6] = rw[6];
tempRow[7] = rw[7];

// this column for finding percentage of marks.... additional column in virtual table...
tempRow[8]=(Convert.ToSingle(rw[6].ToString())*100)/ Convert.ToDouble(rw[7].ToString());

tabModule.Rows.Add(tempRow);

}
dgv1.DataSource = tabModule;
dgv1.Columns[0].Visible = false;
}
}
}