How to get Excel Column Headers using C#

Last Reply 14 days ago By dharmendr

Posted 15 days ago

hi I am importing the Excel data to database by comparing both excel and database. so getting Excel Data using OLEDB, so i need to get exactly column header in excel and in each excel file column header different location will there like example in excel_file1 column header will be at A2 and in excel_file2 column header will be at B3. so for excel_file1 i have to write the Query like this

Select * From [SheetName$A2:K10000]

and for second file i have to change the query to B3 so i don't want to change the query for Excel file i want automatically should search for column headers. and this is my code

private static void GetExcelData(string sheetName)
        {
            try
            {
                var filelocation = @"C:\Users\Vincit\Downloads\MCA Companies for last 3 years\MCA Companies for last 3 years\MCA_Copy of eir_April2017.xlsx";
                string StrConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;\";";
                string xlQuery = @"Select * from [" + sheetName + "A2:R20000]";
                OleDbConnection oledbConn = new OleDbConnection(StrConn);
                oledbConn.Open();
                OleDbCommand oledbCmd = new OleDbCommand();
                OleDbDataAdapter oledbda = new OleDbDataAdapter();
                DataTable ds = new DataTable();
                oledbCmd.Connection = oledbConn;
                oledbCmd.CommandText = xlQuery;
                oledbda = new OleDbDataAdapter(oledbCmd);
                oledbda.Fill(ds);
                var column = ds.Columns;
                foreach (DataRow rows in ds.Rows)
                {
                    var roc = rows["ROC"].ToString();
                    
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

 

You are viewing reply posted by: dharmendr 14 days ago.
Posted 14 days ago

No without passing the range its not possible to find the column header from the excel.