Click here to Skip to main content
15,312,009 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
C#
        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:
C#
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;`)
Posted
Updated 6-Apr-21 15:14pm
v6
Comments
Richard MacCutchan 6-Apr-21 3:43am
   
How do you identify each table?

Think about it; you cannot separate the tables unless you know how they are defined. For example if a sheet has three tables: A1:Q34, C44:P50, T1:X29, how do you discover those settings?
jayjiaaa 6-Apr-21 21:08pm
   
Correct me if I'm wrong - in this case, tables can be identified by the surrounding empty cells? Which would mean that I should write the code such that the program recognizes the if a block of filled cells are surrounded by blank ones, the said block of cells form a table?
Richard MacCutchan 7-Apr-21 3:07am
   
Well I have no information on the content of these worksheets so cannot say. You really should be talking to the people who create the data.

1 solution

If you want to do something like that - and I don't recommend it at all - then you need to actually separate the "Tables" so they are easy to access. Just shoving them into different parts of the same spreadsheet isn't a good idea at all.
Excel is a pretty good spreadsheet - and it's has a lot of organisation built in. Look at the bottom of the app page, and you'll see a tab bar: these are separate sheets within the same spreadsheet, and they act line separate tables do in a database.

Split your data across various sheets, and you can treat each as a separate table within your app.


But ... while Excel is a good spreadsheet, it's a piss-poor database, and if you want to use separate tables with foreign keys, joins, and suchlike, then I'd really strongly recommend that you don't use a spreadsheet at all. Use a database - even Access if you must - and your whole life becomes considerably easier. Slightly more work to get set up, but the rewards are well worth it!
   
Comments
jayjiaaa 7-Apr-21 2:39am
   
The scenario given is that this app is meant for multiple uses -- e.g. For a company with a lot of Excel files to clearly see what tables are in each file by simply uploading the files.
This program is supposed to do all the work (identifying the data and returning them respectively as separate tables), so I cannot split the sample file's tables into different sheets on my own.

I also cannot base my code on this sample file (e.g. I cannot take the range of cells that are filled in this specific sample to code) as if another user uses this program, there is no guarantee that their Excel files contain data in the same location. Hence the program might not work when other files are uploaded.
OriginalGriff 7-Apr-21 4:18am
   
Then your companies have a problem: unless table data is rigidly separated so you can tell where a table starts and stops, you can't process them automatically with out adding a lot of "knowledge" about the data which isn't apparent without a significant amount of real-world data.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900