Click here to Skip to main content
11,803,019 members (75,584 online)
Rate this: bad
Please Sign up or sign in to vote.
Hello sir,

In my Project I have to import data from an Excel spreadsheet and bind it with a datagridview control. In that I have to select particular sheet name(numbers). I have used opendialog throw which it will open excel sheet and please give me code.

How to take excel sheet in datatable that I can attach to the Datagridview? Please send me code logic.

Thanks & Regards

Vasu Hajare.
Posted 17-Dec-10 0:00am
Edited 17-Dec-10 0:14am
ryuzakil at 11-Mar-12 7:29am
DataBind() method is giving me this error:
'System.Windows.Forms.DataGridView' does not contain a definition for 'DataBind' and no extension method 'DataBind' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)

help please
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

protected void Page_Load(object sender, EventArgs e)
        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object 
        OleDbConnection oledbConn = new OleDbConnection(connString);
            // Open connection
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
            // Create new OleDbDataAdapter 
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Employees");
            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            // Close connection
Parul Singhal Agrawal at 20-Dec-11 23:01pm
while uploading date field from excel to dataset. It converts date to 5 digit number. Please suggest solution to accept date in same format as given in excel
Rate this: bad
Please Sign up or sign in to vote.

Solution 1


Please download this Dll(free to use) from Excel Data Reader - Read Excel files[^]

Use this C# code :
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
//6. Free resources (IExcelDataReader is IDisposable)

you can assign That dataset to datagridview.

datagridview.DataSource = result;

Happy coding.
ambarishtv at 14-May-11 7:26am
My 5 :)
Sam.S129 at 11-Oct-12 3:35am
Actually, I am looking for a solution for this question. Your solution helps a lot. Thanks very much. And I have a question. If I have formatted Excel cells, is it possible to keep formats when exporting to DataGridView?
I look forward to reply from anyone.
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Hi Vasu Hajare,

Already i had written a code snippet in other website.I think its suitable to you.
Refer this link...

Read and Get a Data by Sheetwise[^]

Cheers Smile | :)
vashikhan at 21-Dec-10 4:08am
Thank you sir.

Vasu Hajare

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 365
1 CPallini 270
2 F-ES Sitecore 270
3 KrunalRohit 236
4 Sergey Alexandrovich Kryukov 234
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,735
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 19 Jun 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100