June 21, 2012

Data from Excel Sheet to Display In C#.Net Datagrid View

select data from an excel sheet and display it in a datagridview

 public void ImportExcel2007(string path)  
   FileInfo filePath = new FileInfo(path);  
   DataTable workSheetsTable = new DataTable();  
   DataTable columnsTable = new DataTable();  
   string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  
     path +  
     ";Extended Properties='Excel 12.0;IMEX=1; HDR=YES'";  
   OleDbConnection excelConnection = new OleDbConnection(connString);  
   //This bit assumes the first worksheet is the one to import,  
   //change this as required.  
   workSheetsTable =  
 excelConnection.GetOleDbSchemaTable(OleDbSchemaGui d.Tables,  
   string tableName = workSheetsTable.Rows[0]["TABLE_NAME"].To String();  
   //This section uses the tableName declared above to get a  
   //list of columns for that worksheet only.  
   columnsTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGui d.Columns,  
   DataRow[] rows = columnsTable.Select(string.Format(  
     "[TABLE_NAME] LIKE '%{0}%'", tableName.Replace("'", "")));  
   //Now, I prefer to work with a DataTable, it's easier to bind to  
   //and work with, so I create one from the selected DataRow[].  
   DataTable columnDefTable = columnsTable.Copy();  
   foreach (DataRow row in rows)  
   //Now we fill the DataSet with the data from our selected table  
   OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM ["  
 +tableName + "]", excelConnection);  
   adapter.Fill(DsImport);  //  
   //string VRTUNO1 = DsImport.Tables[0].Rows[8]["F4"].ToStrin g();