none
How to open multiselected files in datagridview in C# RRS feed

  • Question

  •  Hi

     I have alot of excel files I want to selecte Three files and show them in datagridview,

     I tried with  code but my code show only the last one, e.g I have 1,2,3 excel files,

     datagridview show only the last file 3. What should I do here please.

     Thank you!

    I tried with this code:

       private void Button2_Click(object sender, EventArgs e)
       { 
        try
           {
    
           OpenFileDialog openFileDialog1 = new OpenFileDialog();
           openFileDialog1.Filter = "XML Files, Text Files, Excel Files|  
            *.xlsx; *.xls; *.xml;   *.txt; "; ;
           openFileDialog1.Multiselect = true;
         if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
           {
            foreach (String file in openFileDialog1.FileNames)
            {
            //tb_path is textbox
            tb_path.Text = file;
            // excelFilePath_com = tb_path.Text;
            string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" +          tb_path.Text + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
    
            OleDbConnection con = new OleDbConnection(constr);
            con.Open();
            drop_down_sheet.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            //dro_down_sheet is combobox to choose which sheet to import 
            drop_down_sheet.DisplayMember = "TABLE_NAME";
            drop_down_sheet.ValueMember = "TABLE_NAME";
        }
       }
    




    • Moved by CoolDadTx Friday, November 22, 2019 2:35 PM Winforms related
    Thursday, November 21, 2019 10:17 AM

Answers

  • Hi sara87,

    It seems that your problem has been solved. If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon

    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.

    • Marked as answer by sara87 Monday, December 2, 2019 9:56 AM
    Friday, November 29, 2019 8:23 AM

All replies

  • Hi sara87,

    Thank you for posting here.

    For your question, you want to display more than one excel file in a datagridview.

    Datatable has a method datatable.Merge() that can merge two datatables.

    You can use it to put data from several excel files together and display them in the datagridview.

    This is a code example:

            private void Button1_Click(object sender, EventArgs e)
            {
                try
                {
                    OpenFileDialog openFileDialog1 = new OpenFileDialog();
                    openFileDialog1.Filter = "XML Files, Text Files, Excel Files|*.xlsx; *.xls; *.xml; *.txt; ";
                    openFileDialog1.Multiselect = true;
    
                    DataTable table = new DataTable();
    
                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        foreach (String file in openFileDialog1.FileNames)
                        {
                            //tb_path is textbox
                            tb_path.Text = file;
                            // excelFilePath_com = tb_path.Text;
                            string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + tb_path.Text + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
    
                            OleDbConnection con = new OleDbConnection(constr);
                            con.Open();
    
                            DataTable dt1 = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            table.Merge(dt1);
                            drop_down_sheet.DataSource = dt1;
                            //dro_down_sheet is combobox to choose which sheet to import 
                            drop_down_sheet.DisplayMember = "TABLE_NAME";
                            drop_down_sheet.ValueMember = "TABLE_NAME";
                        }
                    }
                    dataGridView1.DataSource = table;
                }
                catch (Exception e1)
                {
                    MessageBox.Show(e1.Message + e1.StackTrace);
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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.

    Friday, November 22, 2019 6:23 AM
  • Thank you for answer! your code loads the files which I selected,but it import only the last one.

    I have "import button" in my WFA to select which sheet I want to import it, should do anything with import button 

    here is Photo that show result of your code,

    And here is the importing after I clicked Import, It is the last file,

    Here is my code for import button; 

     private void Button1_Click(object sender, EventArgs e)
            {
                try
                {
                    string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + tb_path.Text + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                    OleDbConnection con = new OleDbConnection(constr);
                    OleDbDataAdapter sda = new OleDbDataAdapter("Select * From[" + drop_down_sheet.SelectedValue + "]", con);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    foreach (DataRow row in dt.Rows)
                    {
                        dataGridView1.DataSource = dt;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message,
                 "Important Note",
                 MessageBoxButtons.OK,
                 MessageBoxIcon.Error,
                 MessageBoxDefaultButton.Button1);
    
                }
            }

    Thank you!

    Friday, November 22, 2019 7:51 AM
  • Hi sara87,

    Thanks for your feedback.

    In my code, I select 2 excel files each time and then merge the data together.

    But in your program, you seem to use OpenFileDialog to select one file at a time.

    If so, you can do it like this:

            private DataTable table = new DataTable();
            private void Button1_Click(object sender, EventArgs e)
            {
                try
                {
                    string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + tb_path.Text + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                    OleDbConnection con = new OleDbConnection(constr);
                    OleDbDataAdapter sda = new OleDbDataAdapter("Select * From[" + drop_down_sheet.SelectedValue + "]", con);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    table.Merge(dt);
                    dataGridView1.DataSource = table;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message,
                 "Important Note",
                 MessageBoxButtons.OK,
                 MessageBoxIcon.Error,
                 MessageBoxDefaultButton.Button1);
    
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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.

    Friday, November 22, 2019 9:39 AM
  • Thank you for answer but it still dose not work,

    this is the result when I select button import, I want to open files and show them.

    Friday, November 22, 2019 10:05 AM
  • Hi, 

    In my task I want to selecte many files at the same time by(open button) and then I want to import them in datagridview behind each other by (import button).  

    Friday, November 22, 2019 10:16 AM
  • Hi sara87,

    Thanks for your feedback.

    I modified the code and it should work now.

    public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Button1_Click(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                try
                {
                    OpenFileDialog openFileDialog1 = new OpenFileDialog();
                    openFileDialog1.Filter = "XML Files, Text Files, Excel Files|*.xlsx; *.xls; *.xml; *.txt; "; ;
                    openFileDialog1.Multiselect = true;
                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        foreach (String file in openFileDialog1.FileNames)
                        {
                            //tb_path is textbox
                            textBox1.Text += file+";";
                            // excelFilePath_com = tb_path.Text;
                            string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + file + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
    
                            OleDbConnection con = new OleDbConnection(constr);
                            con.Open();
                            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            //dro_down_sheet is combobox to choose which sheet to import 
                            foreach (DataRow row in dt.Rows)
                            {
                                string name = row["Table_Name"].ToString();
                                comboBox1.Items.Add(new MyClass() { TableName = name ,Path = file});
                            }
                            comboBox1.DisplayMember = "TableName";
                            
                        }
                    }
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message + e2.StackTrace);
                }
            }
            DataTable dataTable = new DataTable();
            private void Button2_Click(object sender, EventArgs e)
            {
                try
                {
                    MyClass myClass = (MyClass)comboBox1.SelectedItem;
    
                    string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + myClass.Path + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                    OleDbConnection con = new OleDbConnection(constr);
                    DataTable dt = new DataTable();
                    using (OleDbCommand comm = new OleDbCommand())
                    {
    
                        comm.CommandText = "Select * From[" + myClass.TableName + "]";
                        comm.Connection = con;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            dataGridView1.DataSource = dt;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message,
                 "Important Note",
                 MessageBoxButtons.OK,
                 MessageBoxIcon.Error,
                 MessageBoxDefaultButton.Button1);
    
                }
            }
        }
        class MyClass
        {
            public string TableName { get; set; }
            public string Path { get; set; }
        }
    

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    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.

    Monday, November 25, 2019 8:08 AM
  • Thank you sir !

    It is work now, Can I drop down (choose ) multible sheet if I tryck button2 or that not possible. 

    Thursday, November 28, 2019 8:13 AM
  • Hi sara87,

    Thanks for your feedback.

    If you want to click a button to display all the tables, you can refer to the following code.

    private void Button3_Click(object sender, EventArgs e)
            {
                DataTable dataTable = new DataTable();
                ObjectCollection items = comboBox1.Items;
                foreach (var item in items)
                {
                    MyClass myClass = (MyClass)item;
                    string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + myClass.Path + ";Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                    OleDbConnection con = new OleDbConnection(constr);
                    DataTable dt = new DataTable();
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * From[" + myClass.TableName + "]";
                        comm.Connection = con;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            dataTable.Merge(dt);
                        }
                    }
                }
    
                dataGridView1.DataSource = dataTable;
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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.

    Friday, November 29, 2019 3:25 AM
  • Thank you , it is work know

    Friday, November 29, 2019 8:14 AM
  • Hi sara87,

    It seems that your problem has been solved. If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon

    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.

    • Marked as answer by sara87 Monday, December 2, 2019 9:56 AM
    Friday, November 29, 2019 8:23 AM