The IExcelDataReader is an invaluable tool when reading data from an excel file. The .dll can be found at: https://github.com/ExcelDataReader/ExcelDataReader. It took me a days to find a way to read an excel file and I was greatly enthused by the ease of use of this reader. The use of the reader is extremely easy and contains a method that creates a datset. The code to use the reader is found below.
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
As you can see the ExcelReaderFactory reads the different versions of an Excel file differently. The CreateOpenXMLReader() method is used on 2007 file versions of Excel and the CreateBinaryReader() method is used on earlier version of Excel. With Excel 2007's implementation of a more robust XML functionality, it becomes much easier to parse Excel data to XML. This being said, can we use this CreateOpenXMLReader() method on other 2007 applications? More to come on that. The AsDataSet() method is the most influential method in the class in that it converts the data to a DataSet that allows access to individual sheets in the excel file referenced by table in the DataSet. That being said, if you know the index of the table and or the name, an individual sheet can be added to a DataTable. The code is below.
DataTable myDataTable = result.Tables["Table Name"];
The IExcelDataReader is a great resource to implement when using Excel files as DataSources, but the most important usage of this class is the fact that when your web application sits on a production server, it is not necessary for the server to have the Office suite installed. In most cases a production server does not want or need these applications installed on them, because the servers resources need to be implemented in other ways. This get around having to use the Office Interop library to read the Excel spreadsheet, and when using the Interop library, an instance of the application has to be started to read the data in the file. In the end the IExcelDataReader is an extremely valuable tool for a .net programmer, and as we find more methods and uses of this resource we will update this blog. More to come later.