1

I am trying to import an xlsx file data into a DataTable. I want to read the 2nd row as columns row or maybe 4th row as columns. currently I am using the below code which is working fine, wanted to know is there any other way to read the excel data from 2nd/4th rows?

    public static DataTable GetDataTableFromSecondRow(string filePath,string sheetName)
    {
        var oleDbConnection = new ExcelToDb(filePath).GetOleDbConnection();
        using (OleDbCommand oleDbCommand = new OleDbCommand(String.Format("select * from [{0}${1}]", sheetName, "A2:end"), oleDbConnection))
        {
            oleDbCommand.ExecuteNonQuery();
            using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
            {
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);
                return dataTable;
            }
        }
    }

The issue with the code is when I am trying to read the excel file to the end by using "A2:end" it is leaving the data after few blank rows which is not a correct way. can we use something like "dt1.AsEnumerable.Skip(3)" which skips not just the rows but also the first row default column?

Example picture - would like to read my data as second table by skipping first 2-3 lines.

enter image description here

Is'haq
  • 182
  • 1
  • 9

1 Answers1

1

Try use NPOI to read excel, it can easily use rowNumber and colNumber to get cell value, see this post:

sheet.GetRow(rowNumber).GetCell(colNumber).StringCellValue))

NPOI can add by Nuget.

update:

Select * From [SheetName$] may work, it can select all data on sheet including middle empty cell, then it can use datatable.Rows[rowNum][colNum] to get any cell value, reference post.

yu yang Jian
  • 6,680
  • 7
  • 55
  • 80