Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.0 Excel Controls
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 1:00am
Edited 17-Dec-10 1:14am
v2
Comments
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
good
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);
        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();
        }      
 
    }
  Permalink  
v2
Comments
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
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
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.
Thanks
Rate this: bad
good
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 | :)
  Permalink  
Comments
vashikhan at 21-Dec-10 4:08am
   
Thank you sir.
 
Regards
Vasu Hajare
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 7,373
1 OriginalGriff 5,571
2 Peter Leow 4,097
3 CHill60 2,858
4 Mika Wendelius 2,850


Advertise | Privacy | Mobile
Web04 | 2.8.150224.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