为什么在将Excel导入c# (WPF)时替换列名中的特定字符
本文关键字:替换 字符 Excel 导入 WPF 为什么 | 更新日期: 2023-09-27 18:12:21
当将Microsoft Excel文件导入c# (WPF)时,列名(列头)中的特定字符将被替换。例如,!
变成_
, .
变成#
。如何阻止它?
connectionString:
Provider=Microsoft.ACE.OLEDB.12.0; Data Source="+filePath+";Extended Properties="Excel 8.0;HDR=YES;CharacterSet=65001;";
和方法:
public DataSet ImportExcel(string fileName)
{
tableList = null;
DataSet ds = new DataSet();
string connectionString = GetConnectionString(fileName);
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tableList = new string[dtSheet.Rows.Count];
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
tableList[dtSheet.Rows.IndexOf(dr)] = sheetName;
Console.WriteLine("TABLE_NAME: " + sheetName);
if (!sheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
rowsCount += dt.Rows.Count;
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
基本上你被限制在列名中允许的字符,这些是字母,数字和字符:@#$_见这里。$符号和数字也不允许作为第一个字符