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.

Add NuGet Packages to the above-created project

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

Then, execute the following command to install the 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.

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.

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.
   
   
   
   
   
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");
   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");
   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. }