當我嘗試使用OLEDB從Excel中選擇所有數據時。我收到錯誤查詢表達式“創建者”中的語法錯誤（缺少運算符）這是因為列名中有空格嗎？查詢為：選擇代碼，名稱，創建者，日期從 [模板$]public DataTable GetExcelDataToTable(string filename, string dataExchangeSelectedColum){ //List<DataExchangeDefinition> dataExchange = new List<DataExchangeDefinition>(); string extension = Path.GetExtension(filename); string connstring = string.Empty; DataTable ExcelData = null; try { switch (extension) { case ".xls": connstring = string.Format(ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString, filename); break; case ".xlsx": connstring = string.Format(ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString, filename); break; } using (OleDbConnection connExcel = new OleDbConnection(connstring)) { using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = connExcel; connExcel.Open(); var dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); connExcel.Close(); var firstSheet = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); cmd.CommandText = "SELECT " + dataExchangeSelectedColum + " FROM [" + firstSheet + "]"; ExcelData = new DataTable(); OleDbDataAdapter oda = new OleDbDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ExcelData); } } } catch (Exception ex) { throw ex; } return ExcelData;}這是我嘗試的代碼，這里包含它們是“代碼，名稱，創建者，日期”的列dataExchangeSelectedColum
2 回答
吃雞游戲
TA貢獻1829條經驗 獲得超7個贊
如果列名稱包含空格，則需要在列名稱兩邊添加方括號：
cmd.CommandText = $"SELECT [{dataExchangeSelectedColum}] FROM [{firstSheet}]";
評論后編輯：
如果要選擇名稱可能包含空格的多個列：
public DataTable GetExcelDataToTable(string filename, IEnumerable<string> columns)
{
...
string formattedColumns = string.Join("," columns.Select(column => $"[{column}]"));
cmd.CommandText = $"SELECT {formattedColumns} FROM [{firstSheet}]";
...
}
可以通過以下方式調用：
DataTable table = GetExcelDataToTable(fileName,
new string[] { "Code", "Name", "Created By", "Date" });
慕哥6287543
TA貢獻1831條經驗 獲得超10個贊
我就是這樣做的
List<string> selecttedColsList = dataExchangeSelectedColum.Split(',').ToList();
string formattedColumns = "";
//string comma = "";
for (int i = 0; i < selecttedColsList.Count; i++)
{
//formattedColumns = string.Join(",", selecttedColsList.Select(col => $"[" + selecttedColsList[i] + "]"));
formattedColumns+= ""+$"[" + selecttedColsList[i] + "]";
if (i != selecttedColsList.Count - 1)
{
formattedColumns += ",";
}
}
- 2 回答
- 0 關注
- 199 瀏覽
添加回答
舉報
0/150
提交
取消