String Connection excel . xls . xlsx C#

Question

I have a connection string with excel, but it is only the . xlsx and is giving this error

Serverversion = 'connExcel.Serverversion' threw an Exception of type 'System.Invalidoperationexception'

I need a connection with both at the same time, because I can have the 2 file types at the time of reading.

string strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source = " + txtArquivo.Text + ";" + "Extend Properties=Excel 12.0 xml; HDR = YES";

Answer 1

When I have questions about a Connection String, I consult the site http://www.connectionstrings.com/ .

In the Excel section in http://www.connectionstrings.com/excel/, has various types of connection.

I believe the type that can suit you for xls and xlsx is the Standard ODBC http://www.connectionstrings.com/excel-2007-odbc/standard/

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xlsx;

Answer 2

I tried with the Standard ODBC string, unsuccessfully... Follow the code below...

    //Conexão com o Excel 
    OleDbConnection connExcel = new OleDbConnection("@PROVIDER=Microsoft.ACE.OLEDB.14.0 xml;Data source=" + txtArquivo + ";" + 
        "Extended Properties=Excel 8.0; HDR=YES");


    try
    {

        //Comando oledb excel
        OleDbCommand commExcel = new OleDbCommand();
        commExcel.Connection = connExcel;

        string alertaPasta = "Selecione uma pasta";
        string titulo = "Alerta";

        //Se o txtArquivo for Nulo ou o espaço estiver em branco {exiba alerta}
        if (string.IsNullOrWhiteSpace(txtArquivo.Text))
        {
            DialogResult resultadoAlerta = MessageBox.Show(alertaPasta, titulo);
            return;
        }

        //se o estado da conexão não estiver aberto, abra.
        if (connExcel.State != ConnectionState.Open)
            connExcel.Open();

        //Leitura das Sheets

        DataTable dtExcel;
        dtExcel = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        DataSet ds = new DataSet();

        string sheetName = dtExcel.Rows[0]["TABLE NAME"].ToString();

        commExcel.CommandText = "SELECT * From [" + sheetName + "]";

    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        connExcel.Close();
    }