It’s often useful to import Excel files directly into an ASP.NET web application in order to simplify data entry. Excel makes it easier to manipulate data, and has far better integration with other apps than most web grids. As a result, integrating a web application with Excel lets users leverage the best features of a spreadsheet software, while taking advantage of the RDBMS and business logic in the web application.
There are a variety of methods to read XLSX files through ASP.NET. The easiest is to use the built-in OleDbConnection and the Excel driver as the file interface. Unfortunately, this comes with two downsides – one is a possible security vulnerability through the OleDb interface, and the other is the requirement of a physical xlsx file on the hard drive in order to load the data. The physical file can also be a security vulnerability if the source files contain sensitive data. In addition, OleDb is only available on Windows, and is not a cross-platform solution. In order to bypass these limitations, a cross-platform library that operates directly on memory streams is preferred.
To the rescue comes the “Excel Data Reader” project from CodePlex. This tool is free, fast, efficient, and can convert an entire spreadsheet into a DataSet or DataTable for easy manipulation. Below are the steps to get it up and running.
1. First, download the project source code to disk. I would recommend against downloading binaries for open-source projects, since developers can occasionally cease development, and you will lose hope of fixing an otherwise minor issue that you could fix yourself.
2. Next, open the project. The “Excel Data Reader” project is integrated with Team Foundation Server; ignore those warnings when you open the project, and change the solution framework to the proper version of .Net (In my case, it was a .Net 4.0 application).
3. The project is dependent on another open-source project called “SharpZipLib”. Download the source code for that, change the solution framework to the proper version of .Net, and compile a release build. If the NAnt framework is not installed, you can ignore the Test project and only compile the primary project.
4. Reference “SharpZipLib” from the “Excel Data Reader” project, and compile a release build. Again, if the Nant framework is not installed, ignore the Test project.
5. Take the two resulting DLLs and the XML file, and reference those from your actual application.
Now that the DLL is installed, it can be used in the web application. Creating an Excel interface is as easy as:
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
The first line takes the memory stream and initializes the Excel reader. The next line converts the Excel file to a DataSet, which can be accessed just as any other ASP.NET database result. The final line closes the stream and releases resources.
Of course, once the Excel file has been loaded, the challenge is to accurately parse the information and convert it into a format compatible with your application database. Transaction-based updates and error-handling of data outliers are recommended to make sure that partial data is not committed to the system.
The “Excel Data Reader” library does a great job of enabling cross-platform XLSX import functionality in ASP.NET web projects. By leveraging this tool instead of the built-in OleDb interface, the web application’s security can be improved, permission-based bugs can be reduced, and the back-end will be able to operate directly on the source memory streams.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
