Reading data from excel to visual c# 2008

Question

Hello dear members Using visual c# 2008 I want to read data from excel sheet to visual c# 2008 and view it in a dataGridView? Hope you can help with that. Regards Ali

Hirendra Sisodiya · Answer

try this code: DataSet ds= new DataSet (); string ConnectionString = your ConnectionState string  //@Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + strfile + ;Extended Properties=Excel 5.0; OleDbConnection objConn = new OleDbConnection (ConnectionString); objConn.Open(); String strConString = SELECT * from [Sheet1$] ; OleDbCommand objCmdSelect = new OleDbCommand (strConString objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter (); objAdapter1.SelectCommand = objCmdSelect; DataTable dt = new DataTable (); objAdapter1.Fill(ds); DataGridView1.DataSource = ds.Tables[0]; objConn.Close(); thanks Please mark as answer if it helps

Suthish Nair · Answer

Follow the link posted by Krishna. Remember to use the provider as below. For .xls --> .ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Path.GetFullPath(item) + ;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'; For .xlsx --> .ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + Path.GetFullPath(item) + ;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';

Krishna Garad · Answer

Check out the article Import Excel Data into GridView & Export to Excel Sheet it may help you

Garry Gibson · Answer

Hi here is a code from this article How to export/import DataGridView to Excel : // Load excel file. var workbook = ExcelFile .Load( Workbook.xlsx ); // Export active worksheet to DataGridView. DataGridViewConverter .ExportToDataGridView( workbook.Worksheets.ActiveWorksheet dataGridView new ExportToDataGridViewOptions ( A4  7 1)); I think this is a pretty straightforward way you directly read an excel file in C# and use that api to pass the cells values and the formatting's.

Ali Alfaraj · Answer

Thanks for the reply. What if I want to read a specific data row from the excel spreadsheet (e.g. A4:A11). How can I code that in visual c#?

Ibrahim Ersoy · Answer

Are you talking about sending Excel Sheet from an open Excel application to your windows/web application or are you trying to view Excel Sheets in your Windows/Web application? If you are looking for the first one: you need to write an Add-in For Excel using VSTO. If you are looking for the other one: listen to the guys before me.

Sam Hobbs · Answer

See my Database Table Update in a DataGridView without Writing Code . I am not sure it will work for your Excel spreadsheet too but probably. Others have posted suggestions for creating connection strings and assuming it is possible you should be able to create a data source for the spreadsheet and use that as described in my article. If you don't want to allow the data to be updated then it would be possible to disable that somehow.

Amruta Kirdat · Answer

Hiiii try this... string strConn; //ConnectionString where your excel file reside strConn = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source=D:\Book1.xls; + Extended Properties=Excel 8.0;; //You must use the $ after the object you reference in the spreadsheet //Sheet1 is sheet where you want to search you can also add where condition in this query for that your should require column Name to check... OleDbDataAdapter myCommand = new OleDbDataAdapter(SELECT * FROM [Sheet1$] strConn); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet ExcelInfo); DataGrid1.DataSource = myDataSet.Tables[ExcelInfo].DefaultView; DataGrid1.DataBind();

Dorababu Meka · Answer

Check this http://www.c-sharpcorner.com/UploadFile/ankurmee/2292/