Click here to Skip to main content
15,881,173 members
Please Sign up or sign in to vote.
5.00/5 (1 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
Updated 17-Dec-10 0:14am
v2
Comments
ryuzakil 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

protected void Page_Load(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object 
        OleDbConnection oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();
 
            // 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;
            GridView1.DataBind();
        }
        catch
        {
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }      
 
    }
 
Share this answer
 
v2
Comments
Parul Singhal Agrawal 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
Hi

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())
{
	//excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();


you can assign That dataset to datagridview.

datagridview.DataSource = result;
datagridview.DataBind();


Happy coding.
 
Share this answer
 
Comments
ambarishtv 14-May-11 7:26am    
My 5 :)
Sam.S129 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.
Thanks
 
Share this answer
 
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 :)
 
Share this answer
 
Comments
vashikhan 21-Dec-10 4:08am    
Thank you sir.

Regards
Vasu Hajare

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900