I have a connection string with excel, but it is only .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, as 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";
When I have questions about a Connection String, I refer to the link site.
In the Excel section at link , you have multiple connection types.
I think the type that can handle you for xls and xlsx is the Standard ODBC link
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xlsx;
I tried with the standard ODBC string, unsuccessfully ... Below is the code ...
//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();
}