how to read data column headers and data of each cell in Excel Using c#

Question

I have an excel sheet similar to :

excel screenshot

I want to read data columns header:All,col1,col2,col3,col4,col5

and get all cell data.for example cell in Row = 2 and column 2 = 0

I currently write this code :

OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT top 5 * FROM " + excelSheets[j], connString);
DataTable fooData = new DataTable();
dbAdapter.Fill(fooData);
foreach (DataRow row in fooData.Rows)
{
    Response.Write(row[0].ToString());
    //Response.Write(row[1].ToString());
}

but it return just a data table with 1 column and just row 1..5 text.

How I can do this?

Please say answer without using Linq to Excel Provider and Open Xml.

Edit 1:

string file_name = "C:\\Book1.xlsx";
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_name + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        objConn = new OleDbConnection(connString);
        objConn.Open();
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            Response.Write("Not Exist");
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        // Loop through all of the sheets if you want too...
        for (int j = 0; j < excelSheets.Length; j++)
        {
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT top 100 * FROM " + excelSheets[j], connString);
            DataTable fooData = new DataTable();
            dbAdapter.Fill(fooData);
            foreach (DataRow row in fooData.Rows)
            {
                Response.Write(row[0].ToString());
            }

        }

Unfortunately no,My data table just has 1 column,and I don't know why — DooDoo
– DooDoo, Commented Aug 23, 2011 at 16:52

Yahia · Accepted Answer · 2011-08-23 17:29:15Z

1

You get the column name for the first column for example by fooData.Columns[0].ColumnName - see http://msdn.microsoft.com/en-us/library/system.data.datacolumn.columnname.aspx

EDIT:

Change the SELECT to SELECT * FROM AND use Fill (0, 5, new DataTable[] { fooData }).

edited Aug 23, 2011 at 17:29

answered Aug 23, 2011 at 16:56

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DooDoo Over a year ago

It returns just 1 column in my Example "All"

Yahia Over a year ago

and what happens when you do fooData.Columns[1].ColumnName ?

DooDoo Over a year ago

it thorws an exception : Cannot find column 1.

Yahia Over a year ago

see my EDIT - this way you don't load all rows but just the first 5 rows... and should get all columns... then you can access the column names by fooData.Columns[1].ColumnName...

DooDoo Over a year ago

@Yahia let us continue this discussion in chat

emre nevayeshirazi · Accepted Answer · 2011-08-23 17:25:01Z

0

"SELECT * FROM [" +  excelSheets[j] + "$A1:C5]"

Try this one. It should return all of the cells from A1 to C5.

answered Aug 23, 2011 at 17:25

emre nevayeshirazi

19.3k12 gold badges67 silver badges82 bronze badges

1 Comment

DooDoo Over a year ago

I got this Error:

The Microsoft Office Access database engine could not find the object 'Nima$A1:C5'. Make sure the object exists and that you spell its name and the path name correctly.

Arun · Accepted Answer · 2011-08-23 17:43:00Z

0

The driver that you selected is for Excel 2007 ("Provider=Microsoft.ACE.OLEDB.12.0" in your connectionString). Is your Excel file 2007 or 2010?

answered Aug 23, 2011 at 17:43

Arun

2,55316 silver badges12 bronze badges

2 Comments

Arun Over a year ago

Try appending IMEX=1 to your ConnectionString. You can find more details on this at connectionstrings.com/excel-2007

Arun Over a year ago

You are missing an escape sequence. Try changing this: OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT top 100 * FROM " + excelSheets[j], connString); to OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT top 100 * FROM [" + excelSheets[j] + "]", connString);

DKR · Accepted Answer · 2017-09-24 10:28:16Z

For Only data column header for logic is here:

                 string filePath = "C:\\Book1.xlsx";
                string connString = string.Empty;
                if (path.EndsWith(".xlsx"))
                {
                    //2007 Format
                    connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", path);
                }
                else
                {
                    //2003 Format
                    connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", path);
                }
                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);
                            List<string> Filedata = new List<string>();
                            foreach (DataColumn column in HeaderColumns.Columns)
                            {
                                string columnName = HeaderColumns.Rows[0][column.ColumnName].ToString();


                                Filedata.Add(columnName);

                                ViewBag.Data = Filedata;
                            }
                        }
                    }
                }

Collectives™ on Stack Overflow

how to read data column headers and data of each cell in Excel Using c#

4 Answers 4

5 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related