question

jayjiaaa-5948 avatar image
0 Votes"
jayjiaaa-5948 asked DanielZhang-MSFT commented

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

Hi!

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

84695-image.png
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!









dotnet-csharpwindows-forms
image.png (52.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered DanielZhang-MSFT commented

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:
85029-47.png
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.



47.png (5.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your help! However, importing specific rows & columns of data is not the most ideal in this case. I cannot base my code on the sample file 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.

0 Votes 0 ·

Hi @jayjiaaa-5948,
If your table is inserted in the form of excel, as shown below:
85274-471.png
You can retrieve some kind of table object which was created in Excel sheet.
thejano has provided a code example in this thread you can refer to.
Best Regards,
Daniel Zhang


0 Votes 0 ·
471.png (17.7 KiB)