_KillerCodes: Import Excel data to grid

This example shows how to open,read and import excel file into DataSet or DataTable using C#.

	private void Import_To_Grid(string FilePath, string Extension, string isHDR)
	{
	string conStr="";
	switch (Extension)
	{
	case ".xls": //Excel 97-03
	conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
	.ConnectionString;
	break;
	case ".xlsx": //Excel 07
	conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
	.ConnectionString;
	break;
	}
	conStr = String.Format(conStr, FilePath, isHDR);
	OleDbConnection connExcel = new OleDbConnection(conStr);
	OleDbCommand cmdExcel = new OleDbCommand();
	OleDbDataAdapter oda = new OleDbDataAdapter();
	DataTable dt = new DataTable();
	cmdExcel.Connection = connExcel;

	//Get the name of First Sheet
	connExcel.Open();
	DataTable dtExcelSchema;
	dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
	string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
	connExcel.Close();

	//Read Data from First Sheet
	connExcel.Open();
	cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
	oda.SelectCommand = cmdExcel;
	oda.Fill(dt);
	connExcel.Close();

	//Bind Data to GridView
	GridView1.Caption = Path.GetFileName(FilePath);
	GridView1.DataSource = dt;
	GridView1.DataBind();
	}

Search