Excel file reading with more than 1 sheet in the same file using C#

Question

In this code I can read all the data of the spreadsheet Plan1 that has the name "Data" of the file ".xls", more if I have more spreadsheet in the same file, as I can read?.

Code for reading:

  static List<Entidade> ObterDadosExcel(string caminhoArquivo)
    {
        OleDbConnection connect = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + caminhoArquivo + "; " + "Extended Properties = 'Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0';");
        string commandoSql = "Select * from [Dados$]"; //nome da coluna e Dados

        OleDbCommand comando = new OleDbCommand(commandoSql, connect);
        List<Entidade> ListaDados = new List<Entidade>();
        try
        {
            connect.Open();
            OleDbDataReader rd = comando.ExecuteReader();

            while (rd.Read())
            {
                ListaDados.Add(new Entidade()
                {
                    ID = Convert.ToInt32(rd["ID"]),
                    Nome = rd["NOME"].ToString(),
                    Endereco = rd["ENDERECO"].ToString(),
                    Nascimento = Convert.ToDateTime(rd["NASCIMENTO"]),
                    Valor = Convert.ToDouble(rd["VALOR"])
                });
            }

            if (ListaDados.Count() > 0)
                return ListaDados;
            else
                return null;

        }
        catch (Exception)
        {

            Console.WriteLine("Não foi possível ler a planilha do excel");
        }


        finally
        {
            connect.Close();
        }

        return null;

    }

Answer 1

The SQL command you are running on OleDbCommand is that "controls" the information you are getting on Excel sheets.

To read the other sheets just change the table name:

// Folha "Endereco"
string commandoSql = "Select * from [Endereco$]";

// Folha "Plan3"
string commandoSql = "Select * from [Plan3$]";

You can even create a method to centralize the execution of commands:

private OleDbDataReader ExecutaComandoExcel(string strComando)
{
    OleDbDataReader objLeitor = null;

    try
    {
        using(OleDbConnection objConexao = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + caminhoArquivo + "; " + "Extended Properties = 'Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0';"))
        {
            using(OleDbCommand objComando = new OleDbCommand(strComando, objConexao))
            {
                objLeitor = objComando.ExecuteReader();
            }
        }
    }
    catch(Exception ex)
    {
        throw ex;
    }

    return objLeitor;
}