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){&nbsp; &nbsp; ...&nbsp; &nbsp; string formattedColumns = string.Join("," columns.Select(column => $"[{column}]"));&nbsp; &nbsp; cmd.CommandText = $"SELECT {formattedColumns} FROM [{firstSheet}]";&nbsp; &nbsp; ...}可以通过以下方式调用：DataTable table = GetExcelDataToTable(fileName,&nbsp;&nbsp; &nbsp; new string[] { "Code", "Name", "Created By", "Date" });

Answer 2

我就是这样做的&nbsp;List<string> selecttedColsList = dataExchangeSelectedColum.Split(',').ToList();&nbsp; &nbsp; &nbsp; &nbsp; string formattedColumns = "";&nbsp; &nbsp; &nbsp; &nbsp; //string comma = "";&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 0; i < selecttedColsList.Count; i++)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //formattedColumns = string.Join(",", selecttedColsList.Select(col => $"[" + selecttedColsList[i] + "]"));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; formattedColumns+= ""+$"[" + selecttedColsList[i] + "]";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (i != selecttedColsList.Count - 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; formattedColumns += ",";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;