Are you using traditional way to read .xls and .xlsx files in ASP.NET/C#? If yes, you might be using following providers:
Microsoft.Jet.OLEDB.4.0 provider for Excel 97-2003 format (.xls)
Microsoft.ACE.OLEDB.12.0 provider for Excel 2007+ format (.xlsx)
and you may get the following common errors:
The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.
and probably, you would go for following approach to fix them
1. Installation of Office 2007 Data Connectivity Components or Office 2010 Database Engine on the Server.
2. The application pool that the site is running set “Enable 32-bit applications” to “True”
3. In project, Change Platform target from: Any CPU to x86 and Rebuild it.
What would happen if you have shared hosting or Azure Website hosting and You are not allowed to install ACE Database engine on the server?
In this post, we will see how to upload and read both .xls and .xlsx excel files without any server dependencies in ASP.NET MVC. We will use ExcelDataReader, an Open Source project to read excel files.
Steps:
1. Create ASP.NET MVC 5 Empty Project
2. To install ExcelDataReader, run the following command in the Package Manager Console:
Install-Package ExcelDataReader
3. Add New Controller say HomeController and add following action:
public ActionResult Upload() { return View(); }
4. Add View of Upload action and use following code:
@model System.Data.DataTable @using System.Data; <h2>Upload File</h2> @using (Html.BeginForm("Upload", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" })) { @Html.AntiForgeryToken() @Html.ValidationSummary() <div class="form-group"> <input type="file" id="dataFile" name="upload" /> </div> <div class="form-group"> <input type="submit" value="Upload" class="btn btn-default" /> </div> if (Model != null) { <table> <thead> <tr> @foreach (DataColumn col in Model.Columns) { <th>@col.ColumnName</th> } </tr> </thead> <tbody> @foreach (DataRow row in Model.Rows) { <tr> @foreach (DataColumn col in Model.Columns) { <td>@row[col.ColumnName]</td> } </tr> } </tbody> </table> } }
We will read excel, get data in DataTable and show DataTable in View.
5. To read the submitted file:
[HttpPost] [ValidateAntiForgeryToken] public ActionResult Upload(HttpPostedFileBase upload) { if (ModelState.IsValid) { if (upload != null && upload.ContentLength > 0) { // ExcelDataReader works with the binary Excel file, so it needs a FileStream // to get started. This is how we avoid dependencies on ACE or Interop: Stream stream = upload.InputStream; // We return the interface, so that IExcelDataReader reader = null; if (upload.FileName.EndsWith(".xls")) { reader = ExcelReaderFactory.CreateBinaryReader(stream); } else if (upload.FileName.EndsWith(".xlsx")) { reader = ExcelReaderFactory.CreateOpenXmlReader(stream); } else { ModelState.AddModelError("File", "This file format is not supported"); return View(); } reader.IsFirstRowAsColumnNames = true; DataSet result = reader.AsDataSet(); reader.Close(); return View(result.Tables[0]); } else { ModelState.AddModelError("File", "Please Upload Your file"); } } return View(); }
It is assumed the file will have column names in first row.
Output:
Conclusion:
ExcelDataReader has no dependencies on external database engines which may not be available on a remote server. It is helpful to deploy application to Windows Azure Websites, Shared hosting or any other machine where the ACE Database engine is not available. We have seen how easy to read excel sheets in ASP.NET MVC with ExcelDataReader.
Hope It helps. Feel free to share your opinion in comment box.
When reading .xls file, i am getting “Invalid file signature”.
Please provide solution.
Very useful and awesome post.
You can give a try the ZetExcel.com for NET speeds up the spreadsheet processing and conversion tasks.
Thank you. Can you please make an example on how to save it to the data base. Or how to upload files and save the file name to a database so users can go to the vie page and select a file to download. Thank you.
Thank you for this great example !. But I have a question. Is it possible to after read the excel file that the code is changed in a way that he can also read data from each cell from the spreadsheet and then save this data in a text file or database? If possible how we could do this ?
I get result value null :(
You have put @ before if (Model != null)
it is does not work, this part
reader.IsFirstRowAsColumnNames = true;
its a lier!!!! :@
the lib used in nugget it has been updated.
this should works
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
It’s throwing error
‘IExcelDataReader’ does not contain a definition for ‘IsFirstRowAsColumnNames’ and no extension method ‘IsFirstRowAsColumnNames’ accepting a first argument of type ‘IExcelDataReader’ could be found (are you missing a using directive or an assembly reference?)
If you are using ExcelDataReader 3+ you will find that there isn’t any method for AsDataSet() for your reader object, You need also to install another package for ExcelReader.DataSet, then you can use AsDataSet() method.
Also there is not a property for IsFirstRowAsColumnNames instead of that you need to set it inside of ExcelDataSetConfiguration
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
it is true. ExcelReader.DataSet is needed.
But “UseHeaderRow = true” doen’t work. Can you let me know why?
Great Work!
Thanks it helped me , however I would like to write the displayed data into sql table after reading
Hi great work sir, can you help me this i want uploaded .xls file save in databse table with prorper cloumns entry in table.
Hi, How to use excel sheet with extension .csv?
thanks buddy..
I tried the Same code in view if (Model != null) , Model is getting Null , I Have use the namespace of Datatable Though Model is getting null . Any reason ??
Thank you Sir. It worked great!!!!