我使用 OleDb 从 Excel 文件中获取无效的工作表名称。怎么了？

发布于 2024-12-21 11:21:40 字数 1921 浏览 2 评论 0原文

我尝试使用 oledb 获取 Excel 工作表名称。

我的连接字符串是：

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filepath + 
";Extended Properties=Excel 12.0;";

其中 filepath 是文件名。

我的代码：

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

包含工作表名称的列表包含所有有效的工作表名称和一些其他工作表名称。示例：

"' Correctsheetname$'"
"' Correctsheetname$'Print_Area"

我只添加以 $' 结尾的工作表

我的问题是，如果工作表名称包含一个单引号，我用两个单引号得到它。

例子：对于名为 asheetname's 的工作表，我得到 'asheetname''s$''

之后，当我尝试获取此工作表的数据源时，我收到一个异常，该工作表没有不存在。

query = "SELECT * FROM ['asheetname''s$']"
_command = new OleDbCommand(query, _connection);
_dataTable = new DataTable();
_dataReader = _command.ExecuteReader();   <-- Exception is thrown here

异常消息：

{System.Data.OleDb.OleDbException: ''asheetname''s$'' 不是有效名称。确保它不包含无效字符或标点符号，并且不要太长。在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling（OleDbHResult hr）在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult（tagDBPARAMS dbParams，Object&executeResult）在 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&executeResult) 在System.Data.OleDb.OleDbCommand.ExecuteCommand（CommandBehavior行为，对象和executeResult）在 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal（CommandBehavior 行为，字符串方法）在 System.Data.OleDb.OleDbCommand.ExecuteReader（CommandBehavior 行为）在 System.Data.OleDb.OleDbCommand.ExecuteReader() 在 PrestaImporter.Entity.Excel.ExecuteQuery（字符串查询）

原文

I try to get excel sheet names, with oledb.

My connection string is:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filepath + 
";Extended Properties=Excel 12.0;";

where filepath is a filename.

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("
the list with the sheets names contains all valid sheet names and some other sheet names.

Example:

"'correctsheetname$'"
"'correctsheetname$'Print_Area"

I only add sheets that end in $'
My problem is that if a sheet name contains a single quote, I get it with two single quotes.
Example:

 for sheet named asheetname's I get 'asheetname''s$''
Afterwards, when I try to get the datasource of this sheet, I am getting an exception that this sheet doesn't exist.
query = "SELECT * FROM ['asheetname''s
And the exception 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)
")) { continue; }
    sheetNames.Add(sheetName);
 }

the list with the sheets names contains all valid sheet names and some other sheet names.

Example:

"'correctsheetname$'"
"'correctsheetname$'Print_Area"

I only add sheets that end in $'
My problem is that if a sheet name contains a single quote, I get it with two single quotes.
Example:

 for sheet named asheetname's I get 'asheetname''s$''
Afterwards, when I try to get the datasource of this sheet, I am getting an exception that this sheet doesn't exist.

And the exception 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)
]"
_command = new OleDbCommand(query, _connection);
_dataTable = new DataTable();
_dataReader = _command.ExecuteReader();   <-- Exception is thrown here

And the exception 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)

")) { continue; } sheetNames.Add(sheetName); }

the list with the sheets names contains all valid sheet names and some other sheet names.
Example: