我想从excel表中获取标题列，但对于某些列，它显示为null [英] I want to get header columns from excel sheet but for some column it showing null

问题描述

我想使用c＃代码从Excel中获取标题行所有列名称

但它不是从Excel中提供所有列标题



Excel表格栏目标题如下

aa aa年2016年3月31日[实际]2015年3月31日[实际]

31- 2014年3月[实际]





最后3列包含十进制数据值，但它没有显示最后3列<数据集中的












它没有显示最后3个标题列的值



以下行是从给定代码行开始逐个标题行

string columnName = HeaderColumns.Rows [0] [column.ColumnName] .ToString（）;



我的尝试：



我已创建以下代码为

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

推荐答案

您使用 column.ColumnName 作为索引，但如果该值不是字符串，则可能无效。您最好使用索引值并在每个名称上测试null。类似于：

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:

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

[edit]

这是我过去用来通过OLEDB捕获信息的方法：

[edit]
This is what I have used in the past to capture information via OLEDB:

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]

[/edit]

这篇关于我想从excel表中获取标题列，但对于某些列，它显示为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！