C# Method to Load Excel File to DataTable or DateSet

Question

good afternoon, I am trying to create a method that returns the data of an excel file to call it through a FileDialog for example ... Here is my code:

public static DataSet MtdGetExcel(string prtlocalFile)
        {
            string sDBstrExcel = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"", prtlocalFile);

            OleDbConnection conexaoExcel = new OleDbConnection(sDBstrExcel);
            conexaoExcel.Open();
            DataTable dt = conexaoExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            DataSet output = new DataSet();
            foreach (DataRow row in dt.Rows)
            {
                 string sheet = row["TABLE_NAME"].ToString();    //Obtém o nome da planilha corrente
                 OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conexaoExcel); //Obtém todas linhas da planilha corrente
                 cmd.CommandType = CommandType.Text;
                 DataTable outputTable = new DataTable(sheet);   //Copia os dados da planilha para o DataTable
                 output.Tables.Add(outputTable);
                 new OleDbDataAdapter(cmd).Fill(outputTable);
            }
            conexaoExcel.Close();

            return output;
        }

But when I call the method does not return anything in the DataGridView...

public void testeImportExcel()
        {
            try
            {
                OpenFileDialog fdlg = new OpenFileDialog();
                fdlg.Title = "Selecione o relatório do Detran";
                fdlg.InitialDirectory = @"c:\";
                //string endereco = fdlg.FileName;
                //txtNomeArquivo.Text = fdlg.FileName;
                fdlg.Filter = "Excel File (*.xlsx)|*.xlsx";
                //fdlg.Filter = "Excel File (*.csv)|*.csv";
                fdlg.FilterIndex = 1;
                fdlg.RestoreDirectory = true;
                if (fdlg.ShowDialog() == DialogResult.OK)
                {
                    myDtGridView.DataSource = MtdGetExcel(fdlg.FileName);
                    myDtGridView.AutoGenerateColumns = true;
                }

             }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

Result:

Can you help me?

Kindly check this : https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable There are several similar examples — Anura Adhikari, Dec 07 '18 at 15:26

score 1 · Answer 1 · answered Dec 07 '18 at 15:24

Here is my exact method that I have in production:

    public DataTable ReadExcel(string fileName, string TableName)
    {
        DataTable dt = new DataTable();

        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0\"");
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + TableName, conn);


        try
        {
            conn.Open();
            OleDbDataReader reader = cmd.ExecuteReader();

            while (!reader.IsClosed)
            {
                dt.Load(reader);
            }
        }
        finally
        {
            conn.Close();
        }

        return dt;
    }

I'm sure just by looking at it you can tell- this method takes in the fileName (or file path) of the Excel you're wanting to read from. Creates the connection string and command. Excel will be read like a database and its worksheets will be read like tables. TableName is the name of the worksheet (table). This method returns a DataTable that can be added to a DataSet if needed.

C# Method to Load Excel File to DataTable or DateSet

1 Answers1