How to display user identified excel tables separately in WinForms C#

Question

Hi!

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

i.e. The data here are 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'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;)

I thought to code it such that I can manually display each table, 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!

Answer 1

Hi jayjiaaa-5948,
Since you cannot separate the tables in the file separately, I suggest you can import specific columns and rows of Excel data into the datagridview.
Here is my test code example you can refer to.

private void Form1_Load(object sender, EventArgs e)  
{  
    FileSelect();  
}  
public void FileSelect()  
{  
    string filePath = string.Empty;  
    string fileExt = string.Empty;  
    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
    file.Filter = "Discovery Excel|*.xlsx| CSV (Coming Soon)| *.csv";  
    file.Title = "Please select a valid data 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); //read file      
                dataGridView1.DataSource = dtExcel;  
                dataGridView1.AutoResizeColumns();  
               
                for (int i = 1; i < dataGridView1.RowCount - 1; i++)  
                {  
                    if (dataGridView1.Rows[i].Cells[0].Value.ToString() == "" || dataGridView1.Rows[i].Cells[1].Value.ToString() == "")  
                    {  
                        dataGridView1.Rows.RemoveAt(i);  
                        i--;  
                    }  
                }  
  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show(" Something went wrong!");  
                         
            }  
        }  
        else  
        {  
            MessageBox.Show("Please choose .xlsx or .CSV file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error    
        }  
    }  
}  
public DataTable ReadExcel(string fileName)  
{  
    var excel = new Microsoft.Office.Interop.Excel.Application();  
    var wkb = excel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);  
  
    var sheet = wkb.Sheets["Sheet1"] as Microsoft.Office.Interop.Excel.Worksheet;  
   //Select the data range of the exported table in excel  
    var range = sheet.Range[sheet.Cells[6, 2], sheet.Cells[10, 4]];  
    var data = range.Value2;  
  
    var dt = new DataTable();  
    dt.Columns.Add("ID");  
    dt.Columns.Add("Name");  
    dt.Columns.Add("Amount");  
  
    for (int i = 1; i <= range.Rows.Count; i++)  
    {  
        dt.Rows.Add(data[i, 1], data[i, 2], data[i, 3]);  
    }  
  
    return dt;  
}  

The result:

Here is also a code example by using OLEDB you can refer to.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.