How to Import Multiple Excel to Datagridview with Checklistbox path

Question

I want to Compiling data from multiple excel to Datagridview
I'm use CheckListBox for Provider excel Path But not work . dataGridView display data single excel path
Help me please , Thank you.

foreach (var item in checkedListBox1.CheckedItems)
            {
               
                string constr = "Provider = Microsoft.ACE.OLEDB.12.0; data source = '" + item + "'" +
                        @";extended properties=" + "\"excel 8.0;hdr=yes;\"";

                string sheetname = "[MySheet$]";
                conOle = new OleDbConnection(constr);
                comOle = new OleDbCommand("Select * From " + sheetname + "", conOle);
                conOle.Open();
                datpOle = new OleDbDataAdapter(comOle);
            }

            dtable = new DataTable();
            datpOle.Fill(dtable);
            dataGridView2.DataSource = dtable;

Gowtham Cp · Answer

Hello Paradorn, Glad the solution worked! The issue with cell types is often due to mixed data types in Excel. You can handle this by using the IMEX=1 property in the connection string, which forces all data to be treated as text. Here’s an updated solution: DataTable dtable = new DataTable(); // Initialize outside the loop foreach (var item in checkedListBox1.CheckedItems) { string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + item.ToString() + "';" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\""; // Added IMEX=1 to treat all data as text string sheetname = "[MySheet$]"; using (OleDbConnection conOle = new OleDbConnection(constr)) { using (OleDbCommand comOle = new OleDbCommand("SELECT * FROM " + sheetname, conOle)) { conOle.Open(); using (OleDbDataAdapter datpOle = new OleDbDataAdapter(comOle)) { DataTable tempTable = new DataTable(); // Temporary table for each Excel file datpOle.Fill(tempTable); dtable.Merge(tempTable); // Merge the temp table into the main DataTable } } } } dataGridView2.DataSource = dtable; // Bind the merged data to DataGridView If you need more fine-grained control over column types, you may need to process the DataTable further after loading the data to adjust the types accordingly. Hope this helps! Best regards, Gowtham Cp

Gowtham Cp · Answer

Hi Paradorn , You need to merge the data from all the Excel files instead of overwriting it each time. Right now, you're re-initializing the DataTable ( dtable ) in every loop, which is why only the data from the last file is displayed. The solution is to initialize the DataTable outside the loop and use the Merge() method to add rows from each file into the same DataTable . Here’s the code: DataTable dtable = new DataTable(); // Initialize outside the loop foreach (var item in checkedListBox1.CheckedItems) { string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + item.ToString() + "';" + "Extended Properties=\"Excel 8.0;HDR=YES;\""; string sheetname = "[MySheet$]"; using (OleDbConnection conOle = new OleDbConnection(constr)) { using (OleDbCommand comOle = new OleDbCommand("SELECT * FROM " + sheetname, conOle)) { conOle.Open(); using (OleDbDataAdapter datpOle = new OleDbDataAdapter(comOle)) { DataTable tempTable = new DataTable(); // Temporary table for each Excel file datpOle.Fill(tempTable); dtable.Merge(tempTable); // Merge the temp table into the main DataTable } } } } dataGridView2.DataSource = dtable; // Bind the merged data to DataGridView This way, all selected Excel files will be merged into one DataTable , and the DataGridView will display the combined data. Hope it helps!

Paradorn · Answer

Hello Gowtham Cp Thanks for all the solutions.

Paradorn · Answer

Hi Gowtham Cp Thank you for reply it's work and so great , Then I have a problem with the cell type of each file I am looking for a solution

How to Import Multiple Excel to Datagridview with Checklistbox path

Answers (4)