當(dāng)我嘗試使用OLEDB從Excel中選擇所有數(shù)據(jù)時(shí)。我收到錯(cuò)誤查詢表達(dá)式“創(chuàng)建者”中的語法錯(cuò)誤（缺少運(yùn)算符）這是因?yàn)榱忻杏锌崭駟?？查詢?yōu)椋哼x擇代碼，名稱，創(chuàng)建者，日期從 [模板$]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;}這是我嘗試的代碼，這里包含它們是“代碼，名稱，創(chuàng)建者，日期”的列dataExchangeSelectedColum
2 回答
吃雞游戲
TA貢獻(xiàn)1829條經(jīng)驗(yàn) 獲得超7個(gè)贊
如果列名稱包含空格，則需要在列名稱兩邊添加方括號(hào)：
cmd.CommandText = $"SELECT [{dataExchangeSelectedColum}] FROM [{firstSheet}]";
評(píng)論后編輯：
如果要選擇名稱可能包含空格的多個(gè)列：
public DataTable GetExcelDataToTable(string filename, IEnumerable<string> columns)
{
...
string formattedColumns = string.Join("," columns.Select(column => $"[{column}]"));
cmd.CommandText = $"SELECT {formattedColumns} FROM [{firstSheet}]";
...
}
可以通過以下方式調(diào)用：
DataTable table = GetExcelDataToTable(fileName,
new string[] { "Code", "Name", "Created By", "Date" });
慕哥6287543
TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超10個(gè)贊
我就是這樣做的
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 關(guān)注
- 176 瀏覽
添加回答
舉報(bào)
0/150
提交
取消