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
    Posts
    2

    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?

    adapter.Fill(dataset);
    dataGridView1.DataSource = dataset;


    The sourcecode i already have:

    Code:
    // 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;
    
                     }
                     else
                     {
                             ofImport.Dispose();
                     }

  2. #2
    Join Date
    Aug 2008
    Posts
    9

    Re: from Excel to DataGreedView in C#

    Hi
    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))
    {
    conn.Open();
    using (OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$]", conn))
    {
    using (OleDbDataReader dr = cmd.ExecuteReader())
    {
    if (dt != null)
    dt.Dispose();
    dt = new DataTable();
    dt.Load(dr);
    }
    }
    }

    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
  •  





Click Here to Expand Forum to Full Width

Featured