Post
Ask Question

Export And Import Excel Data Using NPOI Library In .NET Core 2 Razor Pages

We are using NPOI library here, which will help to perform import and export operations. In order to see how to create a .NET Core Web Application with Razor Pages and retrieve data from SQL Server using Entity Framework, you can visit my previous article.

Below are the software/concepts used in this document.

  1. Visual Studio 2019
  2. NPOI Library
  3. NuGet Packages
  4. Razor Pages
  5. .NET Core 2.0
  6. .NETCore Web Application
  7. C# Language

Brief description of NPOI

NPOI is an open source project which can help you read/write XLS, DOC, PPT file extensions. This tool is the .NET version of POI Java project (http://poi.apache.org/). It covers most of the features of Excel like styling, formatting, data formulas, extract images, etc. The good thing is that it does not require Microsoft Office on the server. For example, you can use it to -

  • Generate an Excel report without Microsoft Office suite installed on your Server, which is more efficient than calling Microsoft Excel ActiveX in the background.
  • Extract text from Office documents to help you implement full-text indexing feature (most of the times, this feature is used to create search engines).
  • Extract images from Office documents.
  • Generate Excel sheets which contain formulas.

How Razor Pages handle incoming HTTP Requests

Razor pages use handler methods to deal with the incoming HTTP request (GET/POST/PUT/Delete). They are prefixed with “On” and the name of HTTP verbs like -

  • OnGet
  • OnPost
  • OnPut
  • OnGetAsync
  • OnPostAsync
  • OnPutAsync

Besides these default handlers, we can also specify custom names. The custom name must come after the followed naming convention like, for example -

  • OnGetCountries()
  • OnPostUserMaster()
  • OnPostUserDetails()

We will be using custom names for our Import and Export handler methods.

Open your project in Visual Studio 2019

In my case, I am opening the earlier created project where Razor pages are present.

Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

Add NuGet Packages to the above-created project

In the Visual Studio menu, browse to Tools >> NuGet Package Manager >> Package Manager Console.

Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

Then, execute the following command to install the NPOI.

Install-Package DotNetCore.NPOI

Export to Excel by creating Excel (.xlsx/.xls) with dummy data

Open the Index page (Razor page) where the data is present. In my example, I am opening Index.cshtml under “Customers” folder where my customer data is displayed.

Put the below code inside the Index.cshtml page which would call the handler method “asp-page-handler="ExporttoExcel"”.
  1. <form method="post" enctype="multipart/form-data">      
  2.     <div class="row">  
  3.         <div class="col-md-8" style="padding-top:10px;">  
  4.             <button asp-page-handler="ExporttoExcel">Export to Excel</button>  
  5.         </div>  
  6.     </div>  
  7.     <div id="divData"></div>  
  8. </form>  
Open Index.cshtml.cs file and put the below code which would create .xlsx file in the wwwroot folder by injecting IHostingEnvironment in the constructor.
  1. private IHostingEnvironment _hostingEnvironment;  
  2. public IndexModel(IHostingEnvironment hostingEnvironment)  
  3. {  
  4.     _hostingEnvironment = hostingEnvironment;  
  5. }  
The NPOI package supports both “xls” and “xlsx” extensions using HSSFWorkbook and XSSFWorkbook classes respectively. In my example, I would be using XSSFWorkbook class, as I will work with the .xlsx file. In the Index.cshtml.cs file, put the below new method.
  1. public async Task<IActionResult> OnPostExporttoExcel()  
  2. {  
  3.     string webRootPath = _hostingEnvironment.WebRootPath;  
  4.     string fileName = @"Testingdummy.xlsx";  
  5.     string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, fileName);  
  6.     FileInfo file = new FileInfo(Path.Combine(webRootPath, fileName));  
  7.     var memoryStream = new MemoryStream();  
  8. // --- Below code would create excel file with dummy data----  
  9.     using (var fs = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Create, FileAccess.Write))  
  10.     {  
  11.         IWorkbook workbook = new XSSFWorkbook();  
  12.         ISheet excelSheet = workbook.CreateSheet("Testingdummy");  
  13.   
  14.         IRow row = excelSheet.CreateRow(0);  
  15.         row.CreateCell(0).SetCellValue("ID");  
  16.         row.CreateCell(1).SetCellValue("Name");  
  17.   
  18.         row = excelSheet.CreateRow(1);  
  19.         row.CreateCell(0).SetCellValue(1);  
  20.         row.CreateCell(1).SetCellValue("Mike");  
  21.   
  22.         row = excelSheet.CreateRow(2);  
  23.         row.CreateCell(0).SetCellValue(2);  
  24.         row.CreateCell(1).SetCellValue("James");  
  25.   
  26.         workbook.Write(fs);  
  27.     }  
  28.     using (var fileStream = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Open))  
  29.     {  
  30.         await fileStream.CopyToAsync(memoryStream);  
  31.     }  
  32.     memoryStream.Position = 0;  
  33.     return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);  
  34. }  
Test the files by right-clicking on the Index file and opening it in browser. Then, click on the “Export to Excel” button. The “Testingdummy.xlsx” file is downloaded.
 
Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

Export to Excel by taking data from the screen

Open the Index page (Razor page) where the data is present. In my example, I am opening Index.cshtml under “Customers” folder where my customer data is displayed.

  1. Repeat the two steps from above (where we put Form and IHostingEnvironment code on the index.cshtml and index.cshtml.cs pages).
  2. My data already gets loaded on the page and the same data I want to export to the Excel.

    Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

  3. The NPOI package supports both “xls” and “xlsx” extensions using HSSFWorkbook and XSSFWorkbook classes. In my example, I would be using XSSFWorkbook class, as I will work with .xlsx file. In Index.cshtml.cs file, put the following code. Also, since I have 4 columns of data to be exported to Excel, in my example, I will have 4 columns.
    1. public async Task<IActionResult> OnPostExporttoExcel()  
    2.         {  
    3. string webRootPath = _hostingEnvironment.WebRootPath;  
    4.             string fileName = @"Testingdummy.xlsx";  
    5.             string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, fileName);  
    6.             FileInfo file = new FileInfo(Path.Combine(webRootPath, fileName));  
    7.             var memoryStream = new MemoryStream();  
    8.             // --- Below code would create excel file with dummy data----  
    9.             using (var fs = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Create, FileAccess.Write))  
    10.             {  
    11.                 IWorkbook workbook = new XSSFWorkbook();  
    12.                 ISheet excelSheet = workbook.CreateSheet("Testingdummy");  
    13.   
    14.                 IRow row = excelSheet.CreateRow(0);  
    15.                 row.CreateCell(0).SetCellValue("CustomerId");  
    16.                 row.CreateCell(1).SetCellValue("FirstName");  
    17.                 row.CreateCell(2).SetCellValue("LastName");  
    18.                 row.CreateCell(3).SetCellValue("Email");  
    19.   
    20.                 cust = from s in _context.Customers select s;  
    21.                 int counter = 1;  
    22.                 foreach(var customer in cust)  
    23.                 {  
    24.                     string FirstName = string.Empty;  
    25.                     if (customer.FirstName.Length > 100)  
    26.                         FirstName = customer.FirstName.Substring(0, 100);  
    27.                     else  
    28.                         FirstName = customer.FirstName;  
    29.                     row = excelSheet.CreateRow(counter);  
    30.                     row.CreateCell(0).SetCellValue(customer.CustomerId);  
    31.                     row.CreateCell(1).SetCellValue(FirstName);  
    32.                     row.CreateCell(2).SetCellValue(customer.LastName);  
    33.                     row.CreateCell(3).SetCellValue(customer.Email);  
    34.                     counter++;  
    35.                 }  
    36.                 workbook.Write(fs);  
    37.             }  
    38.             using (var fileStream = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Open))  
    39.             {  
    40.                 await fileStream.CopyToAsync(memoryStream);  
    41.             }  
    42.             memoryStream.Position = 0;  
    43.             return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);  
    44.         }  
  1. Test the files by right-clicking on the Index file and opening it with browser. Then, click on the “Export to Excel” button. The “Testingdummy.xlsx” file is downloaded.

    Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

Import data from Excel (.xls or .xlsx) and display it on the screen

I would read the data from Excel and perform the client-side validation for file selection and extension checking. Once the request is successful, it appends the server response to the HTML and displays it on the screen in tabular format.

  1. Create a dummy Excel with the name “Testingdummy.xlsx”. In my example, I have four columns “CustomerID”, “FirstName”, “LastName” and “Email”.

    Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

  2. Open the Index page (Razor page) where the data is present. In my example, I am opening Index.cshtml under “Customers” folder where my customer data is displayed.

  3. Put the below code inside the Index.cshtml page.
    1. <form method="post" enctype="multipart/form-data">      
    2. @* New code to add file Upload and button for importing the data from excel *@  
    3.     <div class="row">  
    4.         <div class="col-md-4">  
    5.             <input type="file" id="fileUpload" name=" fileUpload" class="form-control" />  
    6.         </div>  
    7.         <div class="col-md-8">  
    8.             <input type="button" id="btnUpload" value="Upload File" />  
    9.         </div>  
    10.     </div>  
    11. @*--- Existing code for exporting data to excel----*@  
    12. <div class="row">  
    13.         <div class="col-md-8" style="padding-top:10px;">  
    14.             <button asp-page-handler="ExporttoExcel">Export to Excel</button>  
    15.         </div>  
    16.     </div>  
    17.     <div id="divData"></div>  
    18. </form>  
On the same Index.cshtml page, put the following code for performing the client side validation and call the Import Handler method named “ImportFromExcel”.
  1. $(document).ready(function () {  
  2.     $('#btnUpload').on('click', function () {  
  3.         var fileExtension = ['xls', 'xlsx'];  
  4.         var filename = $('#fileUpload’).val();  
  5. //--- Validation for excel file---  
  6.         if (filename.length == 0) {  
  7.             alert("Please select a file.");  
  8.             return false;  
  9.         }  
  10.         else {  
  11.             var extension = filename.replace(/^.*\./, '');  
  12.             if ($.inArray(extension, fileExtension) == -1) {  
  13.                 alert("Please select only excel files.");  
  14.                 return false;  
  15.             }  
  16.         }  
  17.         var filedata = new FormData();  
  18.         var fileUpload = $("#fileUpload").get(0);  
  19.         var files = fileUpload.files;  
  20.         filedata.append(files[0].name, files[0]);  
  21.         $.ajax({  
  22.             type: "POST",  
  23.             url: "/Index?handler=ImportFromExcel",  
  24.             beforeSend: function (xhr) {  
  25.                 xhr.setRequestHeader("XSRF-TOKEN",  
  26.                     $('input:hidden[name="__RequestVerificationToken"]').val());  
  27.             },  
  28.             data: filedata,  
  29.             contentType: false,  
  30.             processData: false,  
  31.             success: function (response) {  
  32.                 if (response.length == 0)  
  33.                     alert(Error occurred while uploading the excel file');  
  34.                 else {  
  35.                     $('#divData').html(response);  
  36.                 }  
  37.             },  
  38.             error: function (e) {  
  39.                 $('#divData').html(e.responseText);  
  40.             }  
  41.         });  
  42.     })  
  43. });  
In Index.cshtml.cs file, put below code which would handle the Import handler method called from the jQuery function in the above step.
  1. public ActionResult OnPostImportFromExcel()  
  2. {  
  3.     IFormFile file = Request.Form.Files[0];  
  4.     string folderName = "Upload";  
  5.     string webRootPath = _hostingEnvironment.WebRootPath;  
  6.     string newPath = Path.Combine(webRootPath, folderName);  
  7.     StringBuilder sb = new StringBuilder();  
  8.     if (!Directory.Exists(newPath))  
  9.         Directory.CreateDirectory(newPath);  
  10.     if (file.Length > 0)  
  11.     {  
  12.         string sFileExtension = Path.GetExtension(file.FileName).ToLower();  
  13.         ISheet sheet;  
  14.         string fullPath = Path.Combine(newPath, file.FileName);  
  15.         using (var stream = new FileStream(fullPath, FileMode.Create))  
  16.         {  
  17.             file.CopyTo(stream);  
  18.             stream.Position = 0;  
  19.             if (sFileExtension == ".xls")//This will read the Excel 97-2000 formats    
  20.             {  
  21.                 HSSFWorkbook hssfwb = new HSSFWorkbook(stream);   
  22.                 sheet = hssfwb.GetSheetAt(0);   
  23.             }  
  24.             else //This will read 2007 Excel format    
  25.             {  
  26.                 XSSFWorkbook hssfwb = new XSSFWorkbook(stream);   
  27.                 sheet = hssfwb.GetSheetAt(0);   
  28.             }  
  29.             IRow headerRow = sheet.GetRow(0);   
  30.             int cellCount = headerRow.LastCellNum;  
  31. // Start creating the html which would be displayed in tabular format on the screen  
  32.             sb.Append("<table class='table'><tr>");  
  33.             for (int j = 0; j < cellCount; j++)  
  34.             {  
  35.                 NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);  
  36.                 if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;  
  37.                 sb.Append("<th>" + cell.ToString() + "</th>");  
  38.             }  
  39.             sb.Append("</tr>");  
  40.             sb.AppendLine("<tr>");  
  41.             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)   
  42.             {  
  43.                 IRow row = sheet.GetRow(i);  
  44.                 if (row == null) continue;  
  45.                 if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;  
  46.                 for (int j = row.FirstCellNum; j < cellCount; j++)  
  47.                 {  
  48.                     if (row.GetCell(j) != null)  
  49.                         sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");  
  50.                 }  
  51.                 sb.AppendLine("</tr>");  
  52.             }  
  53.             sb.Append("</table>");  
  54.         }  
  55.     }  
  56.     return this.Content(sb.ToString());  
  57. }  
Test the files by right-clicking on the Index file and opening it with the browser. Browse your dummy Excel file and then click on the “Upload File” button. The “Testingdummy.xlsx” file is read and displayed on the screen in tabular format.
 
Export And Import Data Using NPOI Library In .NET Core 2 Razor Pages

That is it. I hope you have learned something new from this article and will utilize this in your work.


Similar Articles