How to select excel sheet to import into a DataGridView C #

0

I managed to import an Excel file into a DataGridView, just that I have a problem, I would like to be able to import any Excel file without importing the name of the sheet because for now I can only import files that have the name [Sheet $],

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;


namespace ImportarExcelToDatagridview
{
    class Importar
    {
        OleDbConnection conn;
        OleDbDataAdapter MyDataAdapter;
        DataTable dt;

        public void importarExcel(DataGridView dgv,String nombreHoja)
        {
            String ruta = "";
            try
            {
                OpenFileDialog openfile1 = new OpenFileDialog();
                openfile1.Filter = "Excel Files |*.xlsx";
                openfile1.Title = "Seleccione el archivo de Excel";
                if (openfile1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    if (openfile1.FileName.Equals("") == false)
                    {
                        ruta = openfile1.FileName;
                    }
                }

                    conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + ruta + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'");
                    MyDataAdapter = new OleDbDataAdapter("Select * from [Sheet$]", conn);
                    dt = new DataTable();
                    MyDataAdapter.Fill(dt);
                    dgv.DataSource = dt;

            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}
    
asked by Francisco Ponce 05.06.2018 в 20:36
source

1 answer

0

A function that gets the whole list of sheets is:

conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

This is going to return a datatable, which you can then go through to get the names of the sheets doing something like this:

foreach(DataRow row in dt.Rows)
{
    nombre = row["TABLE_NAME"].ToString();
}
    
answered by 05.06.2018 в 21:16