Getting all the excel sheet names

Question

After selecting the excel file with OpenFileDialog, I want to see the worksheet names and select the worksheet page I want. How can we fix these codes according to my request?

private void button2_Click(object sender, EventArgs e)
{
    try
    {
        System.Threading.Thread.Sleep(1000);

        // Dosya seçme penceresi açmak için
        OpenFileDialog file = new OpenFileDialog
        {
            Filter = "Excel Dosyasi |*.xls;*.xlsx"
        };
        file.ShowDialog();

        string tamYol = file.FileName;
        string baglantiAdresi = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tamYol + ";Extended Properties='Excel 12.0;IMEX=1;'";
        OleDbConnection baglanti = new OleDbConnection(baglantiAdresi);

        OleDbCommand komut = new OleDbCommand("Select * From [" + "Sayfa1" + "$]", baglanti);
        baglanti.Open();
        OleDbDataAdapter da = new OleDbDataAdapter(komut);
        DataTable data = new DataTable();
        da.Fill(data);
        dataGridView1.DataSource = data;
        data.Dispose();
        baglanti.Close();
        komut.Parameters.Clear();
    }
    catch (Exception ex)
    {
        // Hata alirsak ekrana bastiriyoruz.
        MessageBox.Show("Dosya Seçilmedi!Hata :" + ex.Message);
    }

}

Mehmet Fatih · Answer

Thanks for your intrest Mithelesh Tata.IT was solved.

Mithilesh Tata · Answer

To achieve your request, you need to modify the code to first retrieve the worksheet names from the selected Excel file and then allow the user to choose the desired worksheet. Here's how you can do it: using System; using System.Data; using System.Data.OleDb; using System.Windows.Forms; namespace ExcelImport { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button2_Click(object sender, EventArgs e) { try { System.Threading.Thread.Sleep(1000); // Dosya seçme penceresi açmak için OpenFileDialog file = new OpenFileDialog { Filter = "Excel Dosyasi |*.xls;*.xlsx" }; if (file.ShowDialog() == DialogResult.OK) { string tamYol = file.FileName; string baglantiAdresi = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tamYol + ";Extended Properties='Excel 12.0;IMEX=1;'"; OleDbConnection baglanti = new OleDbConnection(baglantiAdresi); // Open the connection to get the schema information. baglanti.Open(); DataTable schemaTable = baglanti.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); baglanti.Close(); // Let the user choose the worksheet. string selectedWorksheet = ChooseWorksheet(schemaTable); if (!string.IsNullOrEmpty(selectedWorksheet)) { OleDbCommand komut = new OleDbCommand("Select * From [" + selectedWorksheet + "$]", baglantiAdresi); baglanti.Open(); OleDbDataAdapter da = new OleDbDataAdapter(komut); DataTable data = new DataTable(); da.Fill(data); dataGridView1.DataSource = data; data.Dispose(); baglanti.Close(); komut.Parameters.Clear(); } } } catch (Exception ex) { // Hata alirsak ekrana bastiriyoruz. MessageBox.Show("Dosya Seçilmedi!Hata :" + ex.Message); } } private string ChooseWorksheet(DataTable schemaTable) { // Create a form to display worksheet choices. Form worksheetSelectionForm = new Form(); worksheetSelectionForm.Text = "Select Worksheet"; // Add a combobox to display worksheet names. ComboBox comboBoxWorksheets = new ComboBox(); comboBoxWorksheets.DropDownStyle = ComboBoxStyle.DropDownList; comboBoxWorksheets.DataSource = schemaTable; comboBoxWorksheets.DisplayMember = "TABLE_NAME"; comboBoxWorksheets.SelectedIndexChanged += (sender, e) => { worksheetSelectionForm.DialogResult = DialogResult.OK; worksheetSelectionForm.Close(); }; // Add the combobox to the form. worksheetSelectionForm.Controls.Add(comboBoxWorksheets); // Add OK and Cancel buttons. Button buttonOK = new Button(); buttonOK.Text = "OK"; buttonOK.DialogResult = DialogResult.OK; worksheetSelectionForm.AcceptButton = buttonOK; worksheetSelectionForm.Controls.Add(buttonOK); Button buttonCancel = new Button(); buttonCancel.Text = "Cancel"; buttonCancel.DialogResult = DialogResult.Cancel; worksheetSelectionForm.CancelButton = buttonCancel; worksheetSelectionForm.Controls.Add(buttonCancel); // Set form properties and display it. worksheetSelectionForm.FormBorderStyle = FormBorderStyle.FixedDialog; worksheetSelectionForm.StartPosition = FormStartPosition.CenterScreen; worksheetSelectionForm.ShowDialog(); // Return the selected worksheet name. if (worksheetSelectionForm.DialogResult == DialogResult.OK) { return comboBoxWorksheets.Text; } else { return null; } } } } In this code, I've added a new method ChooseWorksheet which creates a form displaying the names of all the worksheets in the selected Excel file. The user can then select the desired worksheet from a dropdown list. The selected worksheet name is returned to the button2_Click method, and the data from that worksheet is then loaded into the DataGridView .