get excel sheet row id using exceldatareader into dataset

Question

I have chosen the ExcelDataReader Library for Reading the .XLS and .XLSX files using C#. ExcelDataReader is perfectly working with both file formats with my local and deployment server environment.

I'm facing issue, how to get all the row id from given Excel files?

INPUT Excel file:

And i want output in dataset format like that

Answer 1

Lets explain by code

    using (var stream = System.IO.File.Open(Server.MapPath("yourExcelfileName.xlsx"), FileMode.Open, FileAccess.Read))
    {
        using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
        {
            var conf = new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = a => new ExcelDataTableConfiguration
                {
                    UseHeaderRow = true
                }
            };
            var dataSet = reader.AsDataSet(conf);
            var sheet = dataSet.Tables["sheetName"].Rows.Cast<DataRow>(); // instead of sheetName you can use Index of it like 0 ,1 , ...
            foreach (var row in sheet)
            {
                // some code
                var rowId = row.Table.Rows.IndexOf(row);
                var rowValueByHeaderFieldName = row["HeaderFieldName"]; // you can also use index instead of HeaderFieldName like row[1] , ...
                // some code
            }
        }
    }

Hope this help ;)