如何处理OleDb Excel导入中的无效字符
本文关键字:导入 无效 字符 Excel OleDb 何处理 处理 | 更新日期: 2023-09-27 17:59:24
我正在将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.
在这张表中,"6um$"是一个列名。
这是我的代码:
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();
有什么办法处理这种情况吗?
编辑:
我认为问题出在列名中有$。但事实证明,问题是在工作表名称中有$sign!
好的,我找到了解决方案:出于某种原因,我为自己重命名的工作表有额外的$符号(不知道为什么,excel中没有$符号,但OLEDB会返回额外的$,比如"6um$'$")。我修剪了第一个$,并使用此代码检查是否还有多余的$符号:
char delimiterChars = '$';
string[] words = worksheetName.Split(delimiterChars);
worksheetName=words[0];
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();
}
}
}
}