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

Question

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

Navigation

#1 by (3 votes)

3

In this code I can read all data from the Plan1 worksheet that has the name "Data" from the ".xls" file, but if I have more worksheet in the same file, how can I read it?

Coderead:

staticList<Entidade>ObterDadosExcel(stringcaminhoArquivo){OleDbConnectionconnect=newOleDbConnection(@"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;

    }

sql-server c# asp.net-mvc

asked by anonymous 28.08.2018 / 04:11

1 answer

Analogy between OOP and database Size of a very large Xamarin Forms application

score 3 · Answer 1

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

To read the other sheets just change the name of the table:

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

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

You can even create a method to centralize command execution:

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;
}