I am trying to get excel sheet names with oledb.
My connection string:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filepath +
";Extended Properties=Excel 12.0;";
where filepathis the file name.
My code for this:
List<string> sheetNames = new List<string>();
_excel.Connect(_path);
DataTable dataTable = _excel.ExecuteSchema();
_excel.Disconnect();
foreach (DataRow row in dataTable.Rows)
{
string sheetName = row["TABLE_NAME"].ToString();
if(!sheetName.EndsWith("$'")) { continue; }
sheetNames.Add(sheetName);
}
The list with sheet names contains all valid sheet names and other sheet names. Example:
"'correctsheetname$'""'correctsheetname$'Print_Area"
I add only those sheets that end in $'
My problem is that if the sheet name contains one quote, I get it with two single quotes.
Example: for a sheet named asheetname'sI get'asheetname''s$''
Subsequently, when I try to get the data source of this sheet, I get an exception that this sheet does not exist.
query = "SELECT * FROM ['asheetname''s$']"
_command = new OleDbCommand(query, _connection);
_dataTable = new DataTable();
_dataReader = _command.ExecuteReader(); <-- Exception is thrown here
And the error message:
{System.Data.OleDb.OleDbException: ''asheetname''s$'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at PrestaImporter.Entity.Excel.ExecuteQuery(String query)
source
share