How can I create a ComboBox dropdown List that shows Excel Sheets names which I can select?

Question

So I've been working on an Application that opens any Excel file you want and can send the data to any Microsoft Access Database you want it to.

BUT I am an absolute novice at C# so I've been getting stuck a LOT. Anyways does anyone of you lovely dudes or dudettes know how to make a ComboBox drop down list from which you can select the Excel Sheet you want to see and also display it in the Gridview?

And since I'm asking for help, might as well also ask how would I make the: Send to Database button actually send the Data of the Excel Sheet to the database?

This is what the application looks like at the moment:

This is the code:

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Data_Importer
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //Buttons
        private void button1_Click(object sender, EventArgs e) //button Browse 1
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void button2_Click(object sender, EventArgs e) //button Run
        {
            string PathConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";
            OleDbConnection conn = new OleDbConnection(PathConn);

            var sqlQuery = "Select * from [Sheet1$]";
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, conn);
            DataTable dt = new DataTable();

            myDataAdapter.Fill(dt);
            dataGridView1.DataSource = dt;
        }

        private void button3_Click(object sender, EventArgs e) //button Browse 2
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.textBox2.Text = openFileDialog1.FileName;
            }
        }
    }
}

Where are you stuck at, what is your current problem ? Maybe this could help you: https://stackoverflow.com/a/1165281/13687491 — nanu_nana, Jun 24 '20 at 09:05
So i want to be able to click on the drop down menu from the combo box and select the Sheet for the excel file i want. However i don't know the code for this. — Zellion, Jun 24 '20 at 09:26
Since you're using OleDb to query the Excel sheets these answers should help: https://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order — Nathan Champion, Jun 24 '20 at 09:37
I have no use for a code that has the Excel location hard coded into it since the application works as following: You can "Browse" and select any excel file you want and it will show the Path to the file in the textBox1. And the "Browse" any Access database you want and it will show that path in the textBox2. so i need a way to have it know the location Dynamically. hope this clarifies any questions about what im looking for. — Zellion, Jun 24 '20 at 09:47
First I think you should edit all control's name. For example, for button's name should be like btnDoSomething, so your code will be more easier to understand. (with already generated event's method, try right click on method name, select Rename, then edit to btnDoSomething_Click - I'm using Visual Studio 2017) — VietDD, Jun 24 '20 at 10:12

score 2 · Accepted Answer · answered Jun 25 '20 at 07:13

Based on your description, you want to create a ComboBox that shows Excel Sheets names.

You can try the following code.

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        Excel.Application app = new Excel.Application();
        Excel.Workbook workbook = null;
        private void btnbrowser_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.textBox1.Text = openFileDialog1.FileName;
            }
            workbook = app.Workbooks.Open(textBox1.Text);
            foreach (Excel.Worksheet sheet in workbook.Worksheets)
            {
                comboBox1.Items.Add(sheet.Name);
            }
            app.Workbooks.Close();
            app.Quit();
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            string PathConn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + textBox1.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(PathConn);

            var sqlQuery = string.Format("Select * from [{0}$]",comboBox1.SelectedItem.ToString());
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, conn);
            DataSet set = new DataSet();

            myDataAdapter.Fill(set);
            dataGridView1.DataSource = set.Tables[0];
        }

        private void btnBrow_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.textBox2.Text = openFileDialog1.FileName;
            }
        }
       
        
    }

Besides, please install nuget package Microsoft.Office.Interop.Excel; and use the following code to use it. using Excel = Microsoft.Office.Interop.Excel;

Tested result:

As for add the datatable to the access database, I suggest that you need to create the table in the table first. Then, you can use the following link to add the data.

Insert dataset records in database

score 0 · Answer 2 · answered Jun 26 '20 at 14:47

this requirement doesn't lend itself to novice programming - with all due respect... a SQL database - Access or other brand - has fixed table definitions such that "open any excel" means a wide variety of structured data. So it is really a conflict that is very difficult to overcome - though not impossible, but very elaborate.

probably a better approach is to write the excel data to a NoSQL database such as MongoDB which by definition has a flexible schema.

How can I create a ComboBox dropdown List that shows Excel Sheets names which I can select?

2 Answers2