如何处理OleDb Excel导入中的无效字符？

I am importing excel files to my application and sometimes worksheets column names have "$" sign in it. I receive this Exception:

我正在将excel文件导入我的应用程序，有时工作表列名称中有“$”符号。我收到这个例外：

System.Data.OleDb.OleDbException was unhandled
Message=''6um$'$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

In this sheet "6um$" is a column name.

在此工作表中，“6um $”是列名。

This is my code:

这是我的代码：

OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
    "select * from [" + worksheetName + "$]", con);

con.Open();
System.Data.DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
con.Close();

Any ideas how to handle this situation?

任何想法如何处理这种情况？

Edit:

编辑：

I thought the problem was having $ in column name. But turns out, The problem is having $ sign in worksheet name!

我认为问题是在列名中有$。但事实证明，问题是工作表名称中有$符号！

2 个解决方案

#1

Ok, I figured out the solution: For some reason, the worksheets that I renamed myself have extra $ sign (no idea why, there is no $ sign in the excel but OLEDB returns them with extra $ something like this '6um$'$'). I trim the first $ off and use this code to check is there is any extra $ sign still left:

好吧，我想出了解决方案：出于某种原因，我自己重命名的工作表有额外的$符号（不知道为什么，excel中没有$符号，但OLEDB返回它们的额外$这样的'6um $'$ “）。我修剪了第一个$ off并使用此代码检查是否还有剩余的$符号：

char delimiterChars = '$';
string[] words = worksheetName.Split(delimiterChars);
worksheetName=words[0];

#2

if (Directory.Exists(serverPath))
{
    string FileExist = sp + "book1.xlsx"; ;
    string Exten = Path.GetExtension(FileExist);
    string g = "sheet1";              

    if (File.Exists(FileExist))
    {
        if (Exten == ".xlsx")
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileExist + ";Extended Properties=Excel 12.0";

            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();
                string query = String.Format("select * from [{0}$]", g);

                // Create OleDbCommand object and select data from worksheet Sheet1
                OleDbCommand cmd = new OleDbCommand(query, oledbConn);

                // Create new OleDbDataAdapter 
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "sheetdt");                            

                GridView1.DataSource = ds.Tables[0].DefaultView;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                LblSuccess.Text = ex.Message;
            }
            finally
            {
                // Close connection
                oledbConn.Close();
            }
        }
    }
}