CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012

    from Excel to DataGreedView in C#

    Good morning

    I have an excel file from wich i want to extract the first or more column to a DataGreedView "as is".

    File: c:\temp.xlsx
    Sheet: "Sheet1"
    Column: A

    When i press the buton nothing happens and data is not imported.
    Any idea Why?

    dataGridView1.DataSource = dataset;

    The sourcecode i already have:

    // Abre janela para selecionar ficheiro a importar.
                     OpenFileDialog ofImport = new OpenFileDialog();
                     // Define propriedades para a janela de selecionar ficheiro.
                     ofImport.Title = "Select file";
                     ofImport.InitialDirectory = @"c:\";
                     // ofImport.FileName = "temp.xlsx";
                     ofImport.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
                     ofImport.FilterIndex = 1;
                     ofImport.RestoreDirectory = true;
                     if (ofImport.ShowDialog() == DialogResult.OK)
                             // Define Caminho completo para ficheiro em STRING.
                             string path = System.IO.Path.GetFullPath(ofImport.FileName);
                             // Define pedido a fazer em forma de STRING.
                             string query = "SELECT * FROM [Sheet1$]";
                             // Define ligação como nova ligação OleDB.
                             OleDbConnection conn = new OleDbConnection();
                             // Define linha de comunicação com todos os dados recuperados até aqui.
                             conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofImport.FileName + ";Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
                             // Define adaptador para pedido "query" (tipo string) ao adaptador que comunica com o ficheiro excel através da ligação CONN definida anteriormente.
                             OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
                             DataSet dataSet = new DataSet();
                             adapter.Fill(Sheet1, A:A);
                             dataGridView1.DataSource = Sheet1;

  2. #2
    Join Date
    Aug 2008

    Re: from Excel to DataGreedView in C#

    you can do the following:

    public static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
    return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
    Filename.Replace("'", "''"),
    FirstRowContainsHeaders ? "Yes" : "No");

    inside the button put the below.

    string connStr = BuildExcelConnectionString(fileName, true);
    using (OleDbConnection conn = new OleDbConnection(connStr))
    using (OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$]", conn))
    using (OleDbDataReader dr = cmd.ExecuteReader())
    if (dt != null)
    dt = new DataTable();

    this should work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.