OleDbCommand create excel column name with bracket

Viewed 117

I want to create excel file by oledb, and my code is

int index = 0;
foreach (DataColumn col in dt.Columns)
{   
    datatype[index] = $"[{col.ColumnName}]" + " String";
    index++;
}

string query = string.Join(",", datatype);
cmd.CommandText = $"CREATE TABLE [{sheetName}] ({query});";
cmd.ExecuteNonQuery();

and I have column name like Product material[EN]

I used break point and check my command string

CREATE TABLE [products] 
(
    [another Title] String,
    [Use of product[EN]] String,
    [Product material[EN]] String,
    [another Title] String
);

I've tried

  1. Product material[EN]
  2. Product material[[EN]]]
  3. [Product material[EN]]
  4. '[Product material[EN]]'
  5. Product material[[]EN]
  6. ['Product material[EN]']
  7. let all columns in query without bracket
CREATE TABLE [products] 
(
    another Title String,
    Use of product[EN] String,
    Product material[EN] String,
    another Title String
);
  1. let all value in query without bracket
CREATE TABLE [products] 
(
    another Title String,
    Use of product EN String,
    Product material EN String,
    another Title String
);

but the above syntax all received Syntax error in field definition.

If using parameter and modify the code

cmd.CommandText = $"CREATE TABLE [{sheetName}] (";
for (int i = 0; i < dt.Columns.Count; i++)
{
    cmd.CommandText = cmd.CommandText + "[@var" + i.ToString() + "] String,";
    cmd.Parameters.AddWithValue("@var" + i.ToString(), datatype[i]);
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1, 1) + ");";

or change

cmd.Parameters.AddWithValue("@var" + i.ToString(), datatype[i]);

to

cmd.Parameters.Add(new OleDbParameter("@var" + i.ToString(), datatype[i]));

and get the command string

CREATE TABLE [products] 
(
  [@var0] String,
  ...
  [@varN] String
);

will get a successful excel file but with @varxx column names

change to Product material(EN) is work but I need to use bracket []

how do I add bracket into column name use oledb ?

0 Answers
Related