Read Posted Excel File Using NPOI Library In ASP MVC With AJAX

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 () {  
  10.   
  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.         });  
  21.   
  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.         }  
  38.          
  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.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now