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;

C#

Copy

Answer 1

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

Answer 2

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!

Answer 3

Hello Gowtham Cp 
Thanks for all the solutions.

Answer 4

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