Read the Column Values of the Header Row in Excel 2003 and 2007 file C#

Vasanth
 
on Jul 17, 2012 05:53 AM
14445 Views

Hi

Can any one explain about how to get the first column heading of the excel sheet that is uploaded using fileuploader.

Thanks.

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mudassar
 
on Jul 17, 2012 05:53 AM
    protected void ReadHeaderRow()
    {
        string filePath = "C:\\Book1.xlsx";
        string connString = string.Empty;
        if (filePath.EndsWith(".xlsx"))
        {
            //2007 Format
            connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath);
        }
        else
        {
            //2003 Format
            connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath);
        }
        using (OleDbConnection con = new OleDbConnection(connString))
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                //Read the First Sheet
                cmd.Connection = con;
                con.Open();
                DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                con.Close();
                string firstSheet = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

                //Read the Header Row
                cmd.CommandText = "SELECT top 1 * From [" + firstSheet + "]";
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    DataTable HeaderColumns = new DataTable();
                    da.SelectCommand = cmd;
                    da.Fill(HeaderColumns);
                    foreach (DataColumn column in HeaderColumns.Columns)
                    {
                        string columnName = HeaderColumns.Rows[0][column.ColumnName].ToString();
                    }
                }
            }
        }
}