In this article, we are going to learn how to read the uploaded Excel file without using "Microsoft.Interop.Excel" library. Instead of it, we are going to use some third party component. Here, I am going to use NPOI library file, which was available in NuGet Package Manager. We also have some other third party components ExcelDataReader etc.
What is NPOI?
This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project, which can help you read/write XLS, DOC, PPT files. It has a wide Application.
For example, you can use it to
  • Generate an Excel report without Microsoft Office suite installed on your Server and more efficient than call Microsoft Excel ActiveX at the background;
  • Extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines).
  • Extract images from Office documents
  • Generate Excel sheets, which contains formulas
To know more about this package, refer the link https://npoi.codeplex.com/
Step 1

Install NPOI library from NuGet Package Manager.
Tools -> Nuget Package Manager -> Manager NuGet Package for the solution.


Step 2

A dialog Window will open. On the right corner of the dialog, we have search box option. In this, search NPOI. If you found NPOI lib in this, click Install button to install the library in our project reference.
Step 3

Now, you can see the installed NPOI package in our Project Reference.
Step 4

Create an Action Method to create a view page.
  1. [HttpGet]
  2. public ActionResult UploadExcel()
  3. {
  4. return View();
  5. }
Step 5

Create a view for "UploadExcel", to create it, right click on action ->add view->view name ->click OK.
  1. <form id="ExcelUpload">
  2. <div>
  3. <input type="file" id="ExcelFileUpload" class="file">
  4. <span style="color:red;font:bolder;" id="fileErrorMessage"></span>
  5. <button type="button" onclick="UploadExcel()">Upload</button>
  6. </div>
  7. </form>
  8. <script>
  9. $(document).ready(function () {
  11. //validating uploaded file whether it is Excel or not while uploading
  12. $("#ExcelFileUpload").change(function () {
  13. fileErrorMessage.innerHTML = '';
  14. var vtrUpload = $("#ExcelFileUpload").val().toLowerCase();
  15. var regexVTRUpload = new RegExp("(.*?)\.(xlsx|xls)$");
  16. if (!(regexVTRUpload.test(vtrUpload))) {
  17. fileErrorMessage.innerHTML = 'Please select .xls or .xlsx files';
  18. }
  19. });
  20. });
  22. function UploadExcel()
  23. {
  24. var formdata = new FormData(); //FormData object
  25. var fileInput = document.getElementById('ExcelFileUpload');
  26. var filename = fileInput.files[0].name
  27. var extension = filename.split('.').pop().toUpperCase();
  28. if (extension != "XLS" && extension != "XLSX") {
  29. fileErrorMessage.innerHTML = 'Please select .xls or .xlsx files';
  30. }
  31. else
  32. {
  33. //Iterating through each files selected in fileInput
  34. for (i = 0; i < fileInput.files.length; i++) {
  35. //Appending each file to FormData object
  36. formdata.append(fileInput.files[i].name, fileInput.files[i]);
  37. }
  39. $.ajax({
  40. url: '@Url.Action("ReadExcelFile", "ExcelUpload")', //ReadExcelFile is a Action Name and ExcelUpload is Controller name
  41. type: 'POST',
  42. data: formdata, // Posting the uploaded excel file to controller
  43. async: false,
  44. success: function (data) {
  45. if (data) {
  46. Alert("Upload successfully")
  47. }
  48. },
  49. cache: false,
  50. contentType: false,
  51. processData: false
  52. });
  53. }
  54. }
  55. </script>
Step 5

Creat the ActionResult to read the uploaded Excel file. Prior to it, the header file was given below to read the uploaded Excel file.
Step 6

Create the ActionResult to read the Excel file.
  1. [HttpPost]
  2. public ActionResult ReadExcelFile()
  3. {
  4. HttpPostedFileBase files = Request.Files[0]; //Read the Posted Excel File
  5. ISheet sheet; //Create the ISheet object to read the sheet cell values
  6. string filename = Path.GetFileName(Server.MapPath(files.FileName)); //get the uploaded file name
  7. var fileExt = Path.GetExtension(filename); //get the extension of uploaded excel file
  8. if (fileExt == ".xls")
  9. {
  10. HSSFWorkbook hssfwb = new HSSFWorkbook(files.InputStream); //HSSWorkBook object will read the Excel 97-2000 formats
  11. sheet = hssfwb.GetSheetAt(0); //get first Excel sheet from workbook
  12. }
  13. else
  14. {
  15. XSSFWorkbook hssfwb = new XSSFWorkbook(files.InputStream); //XSSFWorkBook will read 2007 Excel format
  16. sheet = hssfwb.GetSheetAt(0); //get first Excel sheet from workbook
  17. }
  18. for (int row = 0; row <= sheet.LastRowNum; row++) //Loop the records upto filled row
  19. {
  20. if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
  21. {
  22. string value = sheet.GetRow(row).GetCell(0).StringCellValue; //Here for sample , I just save the value in "value" field, Here you can write your custom logics...
  23. }
  24. }
  25. return Json(true, JsonRequestBehavior.AllowGet); //return true to display the success message
  26. }
In NuGet Package Manger, we can get lots of third party components. Some components are open source components.
NPOI is an open source component and you can use it everywhere.