How do I display user identified excel tables separately in winforms C#

See more:

C#

WinForms

DataGridView

I want to display an Excel file that contains multiple user identified tables as separate tables:

Sample file image[^]
(i.e The data in the sample image above is to be displayed as 2 separate tables)

Currently I'm using Oledb to load the Excel file into a DataGridView.

This is my current code:

        private void browseButton_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Title = "Select Excel file";
            openFileDialog.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx";
            openFileDialog.RestoreDirectory = true;

            openFileDialog.ShowDialog();
            fileNameTextBox.Text = openFileDialog.FileName;

            if (!string.IsNullOrEmpty(openFileDialog.FileName))
            {
                oleDbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    openFileDialog.FileName + ";Extended Properties=Excel 12.0;");
                oleDbConn.Open();
                DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                oleDbConn.Close();

                comboBox1.Items.Clear();

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();
                    sheetName = sheetName.Substring(0, sheetName.Length - 1);
                    comboBox1.Items.Add(sheetName);
                }
            }
        }

        private void fileNameTextBox_TextChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("Select * from [" + comboBox1.Text + "$]", oleDbConn);
            DataTable dt = new DataTable();
            oleDbDataAdapter.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}

I thought to code it such that I can manually grab each table in the worksheet, but I'm not sure how to go about doing so.

I cannot separate the tables in the file on my own either.
The scenario given to us is that in a company, some Excel files can contain multiple tables in a single worksheet.
This app is meant to extract each table for the user to view them more clearly. Hence, it will not be feasible for me to manually separate the tables onto different worksheets as I will not have access to xls/xlsx files that the user uploads.

Can anyone advise me on what to do? I've been squeezing my brain dry but I still can't solve this.

Thank you!

What I have tried:

I've tried removing the blank DataGridView cells, but the entire worksheet is still displayed in the View (i.e. the whole worksheet is regarded as the table, instead of separate tables). This was the code I used to do so:

for (int i = dataGridView1.Rows.Count; i < 0; i--)
            {
                DataGridViewRow row = dataGridView1.Rows[i];
                if (!row.IsNewRow && row.Cells[0].Value == null)
                {
                    dataGridView1.Rows.RemoveAt(i);
                }

                DataGridViewColumn col = dataGridView1.Columns[i];
                if (row.Cells[0].Value == null)
                {
                    dataGridView1.Columns.RemoveAt(i);
                }
            }

(I typed this in `comboBox1_SelectedIndexChanged`, below `dataGridView1.DataSource = dt;`)