none
Import Excel (.xls) to datagridview RRS feed

  • Question

  • Hi,

    I know there is multiple solution out there, but none of them work in my case. I have tried debugging, but cant find the problem. I have a Excel (.xls file) named "TI_Testdata" and a datagridview named "testDGV". I want do to it as simple as just importing the Excel file to my DGV with a button click. Any help is strongly appreciated. 

    This is the code I'm using related to this operation:

    delegate void dataToGridTI(int deg, int de2, double ampl_Total, int seq);
    
    //Test DGV - Import TI notations
            private void adddataToGridTI(int deg, int deg2, double ampl_Total, int seq)
            {
                int row = 0;
                testDGV.Rows.Add();
                row = testDGV.Rows.Count - 2;
                testDGV["LIST4", row].Value = seq;
                testDGV["Theta4", row].Value = deg;
                testDGV["Phi4", row].Value = deg2;
                testDGV["Ampl_dB4", row].Value = ampl_Total;
    
                testDGV.FirstDisplayedScrollingRowIndex = testDGV.RowCount - 1;
            }
    
    private void fillTestDGV_Btn_Click(object sender, EventArgs e)
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "Net-informations.com");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                testDGV.DataSource = DtSet.Tables[0];
                MyConnection.Close();
            }

    Wednesday, May 15, 2019 7:45 AM

All replies

  • Hi okrus,

    Thank you for posting here.

    For your question, you could try the code below.

     private void Button1_Click(object sender, EventArgs e)
            {
                String name = "Sheet1";
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                "1.xls" +
                                ";Extended Properties='Excel 12.0 XML;HDR=YES;';";
    
                OleDbConnection con = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                con.Open();
    
                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();
                sda.Fill(data);
                dataGridView1.DataSource = data;
            }

    For more details, please refer to the similar thread in StackOverFlow.

    https://stackoverflow.com/questions/16293643/import-excel-to-datagridview

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 15, 2019 8:00 AM
    Moderator
  • Hi okrus,

    Here is a nice sample, where in you can also choose sheet.

    https://www.c-sharpcorner.com/uploadfile/ankurmee/import-data-from-excel-to-datagridview-in-C-Sharp/


    Regards, http://www.shwetalodha.blogspot.in/

    Wednesday, May 15, 2019 8:55 AM
  • Hi,

    Thank u for the help, but how can I then solve this problem?

    

    Wednesday, May 15, 2019 11:40 AM
  • Your data source can not be *.csv , Data Source= "FileName.csv";

    check out https://www.connectionstrings.com


    • Edited by Mel_W Wednesday, May 15, 2019 6:28 PM
    Wednesday, May 15, 2019 6:24 PM
  • Hi okrus,

    If you want to import data from .csv fil to datagridview, you could try the code below.

       private void Button1_Click(object sender, EventArgs e)
            {
                openFileDialog1.ShowDialog();
                textBox1.Text = openFileDialog1.FileName;
                BindDataCSV(textBox1.Text);
            }
            private void BindDataCSV(string filePath)
            {
                DataTable dt = new DataTable();
                string[] lines = File.ReadAllLines(filePath);
                if (lines.Length > 0)
                {
                    string firstLine = lines[0];
                    string[] headerLabels = firstLine.Split(',');
                    foreach (var headerWord in headerLabels)
                    {
                        dt.Columns.Add(new DataColumn(headerWord));
                    }
    
                    //for data
                    for (int r = 1; r < lines.Length; r++)
                    {
                        string[] dataWords = lines[r].Split(',');
                        DataRow dr = dt.NewRow();
                        int columnIndex = 0;
                        foreach (var headerWord in headerLabels)
                        {
                            dr[headerWord] = dataWords[columnIndex++];
                        }
                        dt.Rows.Add(dr);
                    }
                }
    
                if (dt.Rows.Count > 0)
                {
                    dataGridView1.DataSource = dt;
                }
            }


    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 16, 2019 3:13 AM
    Moderator