how to set column name in DataTable

Question

I am taking Excel data into DataTable but it is taking Excel Column name as first row of data in DataTable. So help me to set Excel's first row as columns in DataTable. PS: I am using Microsoft.Offfice.Interop.Excel and System.Data.DataTable namespace This is my code:

connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
new System.Data.OleDb.OleDbDataAdapter(cmdExcel).Fill(dt);
connExcel.Close();

I am getting all the data of specified excel sheet into DataTable dt but it is taking Excel column names as first row of data in Datatable

Don't use interop. It is gonna cause you much problems in production. — VDWWD, Nov 16 '17 at 10:40
I have to take excel data into dataTable. suggest me another better way than Interop — S_PATIL, Nov 16 '17 at 10:44

score 0 · Answer 1 · edited Nov 16 '17 at 12:10

0

DataTable tab = new DataTable();
for (int i = 0; i < tab.Columns.Count; i++)
{
    tab.Columns[i].Caption = "example"
}

edited Nov 16 '17 at 12:10

Alexey Subach

11,903
7
34
60

answered Nov 16 '17 at 10:26

skazied

46
3

1

Thanks, but what if user altered Excel Sheet before uploading and set "example" as 3rd Column? Then with above code i will have wrong data in wrong column – S_PATIL Nov 16 '17 at 10:41

score 0 · Answer 2 · answered Nov 16 '17 at 10:48

0

Hey you can do the following, it will disable taking the header from the excel workbook:

    conn = New System.Data.OleDb.OleDbConnection(
          "provider=Microsoft.ACE.OLEDB.12.0; " &
          "data source=" & path & "; " &
          "Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")

Alter the HDR to No in the connection string

answered Nov 16 '17 at 10:48

JKOU

255
2
14

I tried this but it is just adding empty row in DataTable in the first – S_PATIL Nov 16 '17 at 12:27

score 0 · Accepted Answer · answered Nov 16 '17 at 10:50

To change a column name, use ColumnName.

DataTable table = new DataTable();

table.Columns.Add("A", typeof(int));
table.Columns.Add("B", typeof(string));

table.Columns[0].ColumnName = "Column A";
table.Columns[1].ColumnName = "Column B";

table.Rows.Add(99, "test row");

how to set column name in DataTable

3 Answers3

Linked