Start reading data from a specific row of excel using Linq in C#

Question

In my project I had a requirement of reading excel sheet data and validate the same. Then store it in database. It is working fine, in this excel template row 1 is header and data will start from row 2.

eg:-

Name      Age     Arabic Name   Category
Jack      30             بيب     A

var mem = from memRec in excelFile.Worksheet("Addition Form")select memRec;

Then I can get the details using memRec["Age"],memRec["Name"] etc.

This is working fine.

Now the client has a different template in which first 3-4 rows are merged with their logos and all. The header will be at row 4 and data will start from row 5.

eg:

row1
row2
row3
row4    Name     Age    Arabic Name    Category
row5    Jack     30           بسيب     A

I tried below code to read this

var mem = from memRec in excelFile.Worksheet("Addition Form")select memRec;

it is not reading it and giving error because the heading is starting from row 4 only.

I don't know the range of data also because it depends on user filling data.

So I am unable to specify the worksheetrange.

Is there any way to specify the starting row to read the excel through Linq. Also once it is done can we specify the row as

memRec["Age"],memRec["Name"] etc.?

Edited

I used below code

 mem = from memRec in excelFile.Worksheet("Addition Form").Skip(2)  select memRec;

but when I tried to call memRec["Age"] it is giving error saying no such column and columns available are Logo1,namecomp...etc.. this is the content of first row of the excel sheet

my excel sheet is like this now

row1     Logo1     namecomp      place        situation
row2     Name      Age           ArabicName   Category
row3     John      30               يبي       A

I don't want row1 as my column headers. The column headers are in row 2 and data start from row3.

Now when I try to read memrec["Name"] it is giving error saying that no such column header. Available column headers are Logo1, namecomp etc..

Please help me to solve this issue..

Not exactly sure about the implementation details but I you could skip the first 4 results and set the column names yourself. — ameer, Jun 16 '15 at 05:20
@ameer how to set the column names myself? if i skip the first 3 result the 4rth row is column name..so can't I use that? — Sachu, Jun 16 '15 at 05:28
Linq seems to like all columns being the same. If you can't find a solution to this, don't be afraid to back out of that code and use the basic **for loop** to traverse your data. — , Jun 17 '15 at 14:16

score 2 · Answer 1 · edited May 23 '17 at 10:28

I am not a Linq expert, but I do not think Linq will work if the data is not consistent.

You may want to look into using an Ole Db Provider like in this post:

Insert DataTable into Excel Using Microsoft Access Database Engine via OleDb

You would need to use a connection string in this format:

    private const string EXCEL_CON =
      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" +
      @"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";

Using that, you could extract the data from the Excel file to a DataTable:

    public DataTable ExcelToDataTable(string fullFilename, string tableName)
    {
        var table = new DataTable();
        string strCon = string.Format(EXCEL_CON, fullFilename);
        using (var con = new System.Data.OleDb.OleDbConnection(strCon))
        {
            ConnectionState initialState = con.State;
            try
            {
                if ((initialState & ConnectionState.Open) != ConnectionState.Open)
                {
                    con.Open();
                }
                string sql = string.Format("SELECT * FROM `{0}`;", tableName);
                using (var cmd = new System.Data.OleDb.OleDbCommand(sql, con))
                {
                    table.Load(cmd.ExecuteReader());
                }
            }
            finally
            { // it seems like Access does not always close the connection
                if ((initialState & ConnectionState.Open) != ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        return table;
    }

Above, tableName would be the name of the Excel Worksheet you need to access your data on.

You may be doing something similar to that now with your excelFile variable. I don't know what that is.

Once you have your data in a DataTable object, you could simply loop over the data using a basic for loop, like so:

    public DataTable CustomTable(DataTable excelTable)
    {
        var table = new DataTable();
        var col0 = table.Columns.Add("Name", typeof(String));
        var col1 = table.Columns.Add("Age", typeof(int));
        var col2 = table.Columns.Add("Arabic Name", typeof(String));
        var col3 = table.Columns.Add("Category", typeof(String));
        var ok = false;
        for (var index = 0; index < excelTable.Rows.Count; index++)
        {
            DataRow excelRow = excelTable.Rows[index];
            if (ok)
            {
                DataRow row = table.NewRow();
                row[col0] = String.Format("{0}", excelRow["Name"]);
                row[col1] = Convert.ToInt32(excelRow["Age"]);
                row[col2] = String.Format("{0}", excelRow["Arabic Name"]);
                row[col3] = String.Format("{0}", excelRow["Category"]);
                table.Rows.Add(row);
            }
            else
            {
                ok = (excelRow[0].ToString() == "Name");
            }
        }
        return table;
    }

I don't know if this gets you where you want to be or not, but I hope it at least helps.

Start reading data from a specific row of excel using Linq in C#

1 Answers1