Click here to Skip to main content
15,301,624 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to take Header rows All column Name from Excel using c# code
But It Is not giving All column Header From Excel

Excel Sheet Column Header Is as Follows
aa aa Year "31-Mar-2016[Actual]" "31-Mar-2015 [Actual]"
"31-Mar-2014[Actual]"


last 3 columns contain Decimal Data values in It, But it is not showing Last 3 Columns
in dataset





It Is not Showing value of Last 3 header Columnns

following Line is from Given Codes line to take one by one header row
string columnName = HeaderColumns.Rows[0][column.ColumnName].ToString();

What I have tried:

I have Created Following Code For That

#region

protected void ReadHeaderRow(string filePath)
{
    string Fpath = filePath;
    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();
                }
            }
        }
    }
}
#endregion
Posted
Updated 9-Aug-17 21:53pm

1 solution

You are using column.ColumnName as an index, but that may not work if the value is not a string. You are better using an index value and testing for null on each name. Something like:
C#
for (int index = 0; ; ++index)
{
    string columnName = HeaderColumns.Rows[0][index].Value.ToString();
    if (string.IsNullOrEmpty(columnName))
        break;
// etc
}


[edit]
This is what I have used in the past to capture information via OLEDB:
C#
OleDbCommand command = new OleDbCommand(string.Format("Select * From [{0}]", tableName), dbConnection);
OleDbDataReader reader = command.ExecuteReader();

while (dataGridView.RowCount > 1)
{
	dataGridView.Rows.RemoveAt(0);
}
if (reader.HasRows)
{
	dataGridView.ColumnCount = reader.FieldCount;

	for (int row = 0; reader.Read(); ++row)
	{
		dataGridView.Rows.Add();
		for (int column = 0; column < reader.FieldCount; ++column)
		{
			dataGridView.Columns[column].HeaderText = reader.GetName(column);
			Type columnType = reader.GetFieldType(column);
			dataGridView[column, row].Value = reader.GetValue(column);
			//if (columnType == typeof(String))
			//    dataGridView[column, row].Value = reader.GetString(column);
			//if (columnType == typeof(Int32))
			//    dataGridView[column, row].Value = reader.GetInt32(column);
			//if (columnType == typeof(Double))
			//    dataGridView[column, row].Value = reader.GetDouble(column);
		}
	}
}

[/edit]
   
v2
Comments
paul_vin 10-Aug-17 4:02am
   
[index].Value here Value showing Error i.e Red Line

Code now is

//foreach (DataColumn column in HeaderColumns.Columns)
//{
// string columnName = HeaderColumns.Rows[0][column.ColumnName].ToString();
//}
for (int index = 0; ; ++index)
{
string columnName = HeaderColumns.Rows[0][index].Value.ToString();
if (string.IsNullOrEmpty(columnName))
break;
// etc
}

Showing Error Object does not contain a definition for 'Value' accepting first argument of type object could not be found (are you missing) a using directive or an assembly reference
Richard MacCutchan 10-Aug-17 4:36am
   
Sorry, that works for Excel Interop. I think you have to look at the information provided by the OLEDB classes. As far as I recall there is some method or property which identifies the type of the cell. And from that you can get the value as a string or whatever object type you require.
paul_vin 10-Aug-17 4:37am
   
ok thanks
Richard MacCutchan 10-Aug-17 4:39am
   
See my update above.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900