Reading data from excel to visual c# 2008

Ali Alfaraj

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hirendra Sisodiya15y ago
Accepted 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
0

Garry Gibson12y ago

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 Alfaraj15y ago
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#?
0
Ibrahim Ersoy15y ago
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.
0
Sam Hobbs15y ago
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.
0
Amruta Kirdat15y ago
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();
0
Suthish Nair15y ago
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'";
0
Dorababu Meka15y ago
Check this http://www.c-sharpcorner.com/UploadFile/ankurmee/2292/
0
Krishna Garad15y ago
Check out the article
Import Excel Data into GridView & Export to Excel Sheet
it may help you
0