OLEDB 在尝试获取数据时出现错误

Question

当我尝试使用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

Answer 1

如果列名称包含空格，则需要在列名称两边添加方括号：

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" });

Answer 2

我就是这样做的

 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 += ",";

            }

        }