I get incorrect results in datagridview while extracting values from an excel file

Question

1.00/5 (1 vote)

See more:

Hello,
I am not getting correct results in the datagridview control while extracting the data from an Excel file. It is happening when trying to get data from 2 sheets of the same workbook. The error is, datagridview control shows repetition of the rows. This happens only when I try to get data from two sheets. I get correct results only if I write select query for 1 sheet.

Please, suggest me the correct way to get data from more than one worksheets.

What I have tried:

C#

public DataTable ReadExcel(string fileName, string fileExt)
        {
            string conn = string.Empty;
            DataTable dtexcel = new DataTable();


           

            if (fileExt.CompareTo(".xls") == 0)
                conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
            else
                conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
            using (OleDbConnection con = new OleDbConnection(conn))
            {
                try
                {
                    
                        OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$],[Sheet2$] ", con); 
                  
                        oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
                    
                }
                catch { }
            }
            return dtexcel;
        }


private void btnReadFile_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            string fileExt = string.Empty;
            OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
            if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user  
            {
                filePath = file.FileName; //get the path of the file  
                fileExt = Path.GetExtension(filePath); //get the file extension  
                if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
                {
                    try
                    {
                        DataTable dtExcel = new DataTable();
                        dtExcel = ReadExcel(filePath, fileExt); //read excel file  
                        dataGridView1.Visible = true;
                        dataGridView1.DataSource = dtExcel;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                }
                else
                {
                    MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error  
                }
            }
        }

Posted 31-Jul-19 22:11pm

hemal p.shah

Updated 1-Aug-19 6:29am

Add a Solution

Comments

Richard MacCutchan 1-Aug-19 5:18am

I just tried a sample of this and it works correctly. You need to provide more details of exactly what your problem is.

hemal p.shah 1-Aug-19 7:11am

suppose, I have two worksheets sheet1 and sheet2 of the same workbook.
Now sheet1 has the following data in column A
name
hemal
sheet 2 has the following data in Column A
department
Engineering
now, I want to get the data in datagridview control like this,
name department
hemal Engineering
but it shows the data like this
name department
hemal department
name Engineering
hemal Engineering
This is the problem.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-08-01T06:29:00

Quote:

select * from [Sheet1$],[Sheet2$]

You're specifying a join with no predicate. That will produce the cross-product of the two sheets - every row from sheet 1 will be joined to every row on sheet 2.

Unfortunately, it looks like there is no applicable predicate you can join on, since you're associating the data based on its row number. You'll need to load the data from both sheets separately and then combine it.

Add a couple of helper methods:

C#

private static DataTable LoadAllDataFromSheet(OleDbConnection connection, string sheetName)
{
    using (var command = new OleDbCommand($"SELECT * FROM [{sheetName}$]", connection))
    {
        var table = new DataTable();
        var adapter = new OleDbDataAdapter(command);
        adapter.Fill(table);
        return table;
    }
}

private static object[] CombineRows(object[] row1, object[] row2)
{
    object[] result = new object[row1.Length + row2.Length];
    row1.CopyTo(result, 0);
    row2.CopyTo(result, row1.Length);
    return result;
}

private static DataTable MergeTables(DataTable sheet1, DataTable sheet2)
{
    var result = new DataTable();
    
    result.BeginInit();
    foreach (DataColumn column in sheet1.Columns)
    {
        result.Columns.Add($"Sheet1.{column.ColumnName}", column.DataType);
    }
    foreach (DataColumn column in sheet2.Columns)
    {
        result.Columns.Add($"Sheet2.{column.ColumnName}", column.DataType);
    }
    result.EndInit();
    
    result.BeginLoadData();
    foreach (object[] row in sheet1.Rows.Cast<DataRow>().Zip(sheet2.Rows.Cast<DataRow>(), (r1, r2) => CombineRows(r1.ItemArray, r2.ItemArray)))
    {
        result.Rows.Add(row);
    }
    result.EndLoadData();
    
    return result;
}

Then use them to load the data:

C#

using (OleDbConnection con = new OleDbConnection(conn))
{
    DataTable sheet1 = LoadAllDataFromSheet(con, "Sheet1");
    DataTable sheet2 = LoadAllDataFromSheet(con, "Sheet2");
    dtexcel = MergeTables(sheet1, sheet2);
}

Output:

Sheet1.F1  |  Sheet2.F1
=======================
name       |  department
hemal      |  Engineering

NB: Assuming the values in the first row are the column headings, you can change your connection string to specify HDR=YES, and you'll get:

Sheet1.name  |  Sheet2.department
=================================
hemal        |  Engineering

If the column headings are unique, then you can change the MergeTables function so that it doesn't prefix the column names with the sheet names.