Using ExcelDataReader to read Excel data starting from a specific cell

Question

I am using ExcelDataReader to read data from my Excel workbook in C #.
But the structure of my Excel worksheet is such that the read data can start from any particular cell and not necessarily A1 .

Can someone please suggest a way how this can be achieved using ExcelDataReader ?

Answer 1

If you use ExcelDataReader 3+ you will find that there is no method for AsDataSet() for your reader object. You also need to install another package for ExcelDataReader.DataSet , then you can use the AsDataSet() method.
There is also no property for IsFirstRowAsColumnNames instead, you need to set it inside ExcelDataSetConfiguration .

Example:

 using (var stream = File.Open(originalFileName, FileMode.Open, FileAccess.Read)) { IExcelDataReader reader; // Create Reader - old until 3.4+ ////var file = new FileInfo(originalFileName); ////if (file.Extension.Equals(".xls")) //// reader = ExcelDataReader.ExcelReaderFactory.CreateBinaryReader(stream); ////else if (file.Extension.Equals(".xlsx")) //// reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream); ////else //// throw new Exception("Invalid FileName"); // Or in 3.4+ you can only call this: reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream) //// reader.IsFirstRowAsColumnNames var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; var dataSet = reader.AsDataSet(conf); var dataTable = dataSet.Tables[0]; //... } 

---

You can find the row number and column number of the cell link as follows:

 var cellStr = "AB2"; // var cellStr = "A1"; var match = Regex.Match(cellStr, @"(?<col>[AZ]+)(?<row>\d+)"); var colStr = match.Groups["col"].ToString(); var col = colStr.Select((t, i) => (colStr[i] - 64) * Math.Pow(26, colStr.Length - i - 1)).Sum(); var row = int.Parse(match.Groups["row"].ToString()); 

Now you can use several loops to read data from this cell, for example:

 for (var i = row; i < dataTable.Rows.Count; i++) { for (var j = col; j < dataTable.Columns.Count; j++) { var data = dataTable.Rows[i][j]; } } 

---

Update:

You can filter the rows and columns of your Excel worksheet while reading with this configuration:

 var i = 0; var conf = new ExcelDataSetConfiguration { UseColumnDataType = true, ConfigureDataTable = _ => new ExcelDataTableConfiguration { FilterRow = rowReader => fromRow <= ++i - 1, FilterColumn = (rowReader, colIndex) => fromCol <= colIndex, UseHeaderRow = true } }; 

Answer 2

To be more clear, I will start from the very beginning.

I will describe a sample code found at https://exceldatareader.codeplex.com/ , but with some modifications to avoid inconvenience.

The following code defines the file format: xls or xlsx.

 FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read); IExcelDataReader excelReader; //1. Reading Excel file if (Path.GetExtension(filePath).ToUpper() == ".XLS") { //1.1 Reading from a binary Excel file ('97-2003 format; *.xls) excelReader = ExcelReaderFactory.CreateBinaryReader(stream); } else { //1.2 Reading from a OpenXml Excel file (2007 format; *.xlsx) excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); } //2. DataSet - The result of each spreadsheet will be created in the result.Tables DataSet result = excelReader.AsDataSet(); //3. DataSet - Create column names from first row excelReader.IsFirstRowAsColumnNames = false; 

Now we can access the contents of the file in a more convenient way. For this, I use a DataTable. The following is an example of accessing a specific cell and printing its value in the console:

 DataTable dt = result.Tables[0]; Console.WriteLine(dt.Rows[rowPosition][columnPosition]); 

If you do not want to make a DataTable, you can do the same:

 Console.WriteLine(result.Tables[0].Rows[rowPosition][columnPosition]); 

It is important not to try to read outside the table, for this you can see the number of rows and columns as follows:

 Console.WriteLine(result.Tables[0].Rows.Count); Console.WriteLine(result.Tables[0].Columns.Count); 

Finally, when you are done, you should close the reader and free resources:

 //5. Free resources (IExcelDataReader is IDisposable) excelReader.Close(); 

I hope you find this helpful.

(I understand that the question is old, but I am making this contribution to improve the knowledge base, because there is little information about specific implementations of this library).

Answer 3

I found it useful to read from a specific column and row

  FileStream stream = File.Open(@"C:\Users\Desktop\ExcelDataReader.xlsx", FileMode.Open, FileAccess.Read); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); DataSet result = excelReader.AsDataSet(); excelReader.IsFirstRowAsColumnNames = true; DataTable dt = result.Tables[0]; string text = dt.Rows[1][0].ToString(); 

Answer 4

One way to do this:

 FileStream stream = File.Open(@"c:\working\test.xls", FileMode.Open, FileAccess.Read); IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream); excelReader.IsFirstRowAsColumnNames = true; DataSet result = excelReader.AsDataSet(); 

result.Tables contains sheets, and result.tables[0].Rows contains rows of cells.

Answer 5

Very easy with ExcelReaderFactory 3.1 and higher:

 using (var openFileDialog1 = new OpenFileDialog { Filter = "Excel Workbook|*.xls;*.xlsx;*.xlsm", ValidateNames = true }) { if (openFileDialog1.ShowDialog() == DialogResult.OK) { var fs = File.Open(openFileDialog1.FileName, FileMode.Open, FileAccess.Read); var reader = ExcelReaderFactory.CreateBinaryReader(fs); var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true // Use first row is ColumnName here :D } }); if (dataSet.Tables.Count > 0) { var dtData = dataSet.Tables[0]; // Do Something } } } 

Answer 6

You can use the .NET library to do the same, which I find more simple.

 string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; data source={path of your excel file}; Extended Properties=Excel 12.0;"; OleDbConnection objConn = null; System.Data.DataTable dt = null; //Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); objConn.Open(); //Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sql = string.Format("select * from [{0}$]", sheetName); var adapter = new System.Data.OleDb.OleDbDataAdapter(sql, ConnectionString); var ds = new System.Data.DataSet(); string tableName = sheetName; adapter.Fill(ds, tableName); System.Data.DataTable data = ds.Tables[tableName]; 

Once you have the data in a datatable, you can access it, as usual, with the DataTable class.