Sunday, November 29, 2009

Excel Integration with .Net

It is possible to interact with Excel from Ado .Net OleDb Provider. Here I am using Office 2007 and .xls format of Excel. I am saving the Excel file inside the bin directory of the application.
Here I am populating both a DataGridView and ComboBox with Excel Data.

using System.Data.OleDb;
OleDbConnection cnn;

public DataSet GetData(String SheetName){
String Query = "SELECT * FROM [" + SheetName + "];"; OleDbDataAdapter myCommand = new OleDbDataAdapter(Query, cnn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
return myDataSet;
private void Form1_Load(object sender, EventArgs e){

cnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "Alloc.xls" + ";" + "Extended Properties=Excel 8.0");
OleDbDataAdapter da;
DataSet ds = GetData("Sheet1$");
DataTable dt = ds.Tables[0];
dataGridView1.DataSource = ds.Tables[0];
comboBox1.DataSource = ds.Tables[0]; comboBox1.DisplayMember = "CategoryName";


Courtesy : Praseed Pai

No comments:

Post a Comment