Loading a Excel File From a Web Upload With Npoi

Nosotros are using NPOI library here, which will assist to perform import and export operations. In lodge to run across how to create a .Cyberspace Core Web Awarding with Razor Pages and retrieve data from SQL Server using Entity Framework, yous tin visit my previous article.

Beneath are the software/concepts used in this document.

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

Brief description of NPOI

NPOI is an open up source project which can aid you read/write XLS, DOC, PPT file extensions. This tool is the .NET version of POI Coffee project (http://poi.apache.org/). It covers almost of the features of Excel like styling, formatting, data formulas, extract images, etc. The good affair is that it does non crave Microsoft Role on the server. For example, you can use information technology to -

• Generate an Excel written report without Microsoft Part suite installed on your Server, which is more than efficient than calling Microsoft Excel ActiveX in the background.
• Extract text from Office documents to help y'all implement full-text indexing feature (most of the times, this feature is used to create search engines).
• Extract images from Role 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 tin can also specify custom names. The custom proper name must come later on the followed naming convention similar, for example -

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

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

Open up your project in Visual Studio 2019

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

Add NuGet Packages to the higher up-created projection

In the Visual Studio carte, browse to Tools >> NuGet Package Director >> Bundle Manager Panel.

Then, execute the following control to install the NPOI.

Install-Packet 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 instance, I am opening Alphabetize.cshtml under "Customers" folder where my customer data is displayed.

Put the beneath lawmaking within the Alphabetize.cshtml folio which would call the handler method "asp-page-handler="ExporttoExcel"".

1. <form method= "post"  enctype= "multipart/grade-information" >
2.     <divcourse = "row" >
3.         <divform = "col-md-viii"  way= "padding-top:10px;" >
4.             <button asp-folio-handler="ExporttoExcel" >Export to Excel</push button>
5.         </div>
6.     </div>
7.     <div id="divData" ></div>
8. </form>

Open Index.cshtml.cs file and put the below lawmaking which would create .xlsx file in the wwwroot folder by injecting IHostingEnvironment in the constructor.

1. individual  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 volition work with the .xlsx file. In the Index.cshtml.cs file, put the below new method.

1. public  async Job<IActionResult> OnPostExporttoExcel()
2. {
3. cord  webRootPath = _hostingEnvironment.WebRootPath;
4. cord  fileName = @ "Testingdummy.xlsx" ;
5. string  URL = string .Format( "{0}://{one}/{two}" , Request.Scheme, Request.Host, fileName);
6.     FileInfo file =new  FileInfo(Path.Combine(webRootPath, fileName));
7.     var memoryStream =new  MemoryStream();
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("ID" );
16.         row.CreateCell(one).SetCellValue("Name" );
18.         row = excelSheet.CreateRow(1);
19.         row.CreateCell(0).SetCellValue(ane);
20.         row.CreateCell(1).SetCellValue("Mike" );
22.         row = excelSheet.CreateRow(2);
23.         row.CreateCell(0).SetCellValue(two);
24.         row.CreateCell(1).SetCellValue("James" );
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, "awarding/vnd.openxmlformats-officedocument.spreadsheetml.sail" , fileName);
34. }

Test the files by right-clicking on the Alphabetize file and opening it in browser. And then, click on the "Export to Excel" button. The "Testingdummy.xlsx" file is downloaded.

Export to Excel by taking information from the screen

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

1. Repeat the two steps from above (where nosotros put Grade and IHostingEnvironment code on the index.cshtml and index.cshtml.cs pages).
2. My data already gets loaded on the folio and the aforementioned 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, every bit I will piece of work with .xlsx file. In Index.cshtml.cs file, put the following code. Besides, since I have 4 columns of data to be exported to Excel, in my instance, I will have 4 columns.
   
   1. public  async Chore<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();
   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(ii).SetCellValue("LastName" );
   18.                 row.CreateCell(iii).SetCellValue("Email" );
   20.                 cust = from due southin  _context.Customers select s;
   21. int  counter = 1;
   22. foreach (var customer in  cust)
   23.                 {
   24. string  FirstName = string .Empty;
   25. if  (client.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(three).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. render  File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sail" , fileName);
   44.         }

4. Test the files by right-clicking on the Index file and opening it with browser. So, click on the "Export to Excel" button. The "Testingdummy.xlsx" file is downloaded.

   

Import information 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 asking is successful, information technology appends the server response to the HTML and displays information technology 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 "Electronic mail".

   

   
   
2. Open the Index folio (Razor page) where the information is present. In my example, I am opening Index.cshtml nether "Customers" folder where my customer data is displayed.
3. Put the below code within the Index.cshtml page.
   
   1. <form method= "post"  enctype= "multipart/form-data" >
   2. @* New code to add file Upload and buttonfor  importing the data from excel *@
   3.     <divclass = "row" >
   4.         <divclass = "col-md-4" >
   5.             <input blazon="file"  id= "fileUpload"  name= " fileUpload" class = "form-control"  />
   6.         </div>
   7.         <divclass = "col-md-8" >
   8.             <input type="button"  id= "btnUpload"  value= "Upload File"  />
   9.         </div>
   10.     </div>
   11. @*--- Existing lawmakingfor  exporting data to excel----*@
   12. <divclass = "row" >
   13.         <divclass = "col-md-8"  way= "padding-superlative:10px;" >
   14.             <button asp-folio-handler="ExporttoExcel" >Export to Excel</push 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( role  () {
2.     $('#btnUpload' ).on( 'click' , role  () {
3. var  fileExtension = [ 'xls' , 'xlsx' ];
4. var  filename = $('#fileUpload').val();
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. render faux ;
15.             }
16.         }
17. var  filedata = new  FormData();
18. var  fileUpload = $( "#fileUpload" ).become(0);
19. var  files = fileUpload.files;
20.         filedata.append(files[0].proper name, files[0]);
21.         $.ajax({
22.             type:"Mail" ,
23.             url:"/Alphabetize?handler=ImportFromExcel" ,
24.             beforeSend:function  (xhr) {
25.                 xhr.setRequestHeader("XSRF-TOKEN" ,
26.                     $('input:hidden[proper noun="__RequestVerificationToken"]' ).val());
27.             },
28.             data: filedata,
29.             contentType:false ,
30.             processData:faux ,
31.             success:function  (response) {
32. if  (response.length == 0)
33.                     warning(Fault occurredwhile  uploading the excel file');
34. else  {
35.                     $('#divData' ).html(response);
36.                 }
37.             },
38.             fault:function  (east) {
39.                 $('#divData' ).html(e.responseText);
40.             }
41.         });
42.     })
43. });

In Alphabetize.cshtml.cs file, put below code which would handle the Import handler method called from the jQuery function in the in a higher place stride.

1. public  ActionResult OnPostImportFromExcel()
2. {
3.     IFormFile file = Asking.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. cord  sFileExtension = Path.GetExtension(file.FileName).ToLower();
13.         ISheet canvas;
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" )
20.             {
21.                 HSSFWorkbook hssfwb =new  HSSFWorkbook(stream);
22.                 canvas = hssfwb.GetSheetAt(0);
23.             }
24. else
25.             {
26.                 XSSFWorkbook hssfwb =new  XSSFWorkbook(stream);
27.                 canvass = hssfwb.GetSheetAt(0);
28.             }
29.             IRow headerRow = canvas.GetRow(0);
30. int  cellCount = headerRow.LastCellNum;
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  (jail cell == zero  || string .IsNullOrWhiteSpace(prison 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 = canvas.GetRow(i);
44. if  (row == null ) continue ;
45. if  (row.Cells.All(d => d.CellType == CellType.Blank)) proceed ;
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. render this .Content(sb.ToString());
57. }

Examination the files past correct-clicking on the Index file and opening it with the browser. Scan your dummy Excel file then click on the "Upload File" button. The "Testingdummy.xlsx" file is read and displayed on the screen in tabular format.

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

williamsates2000.blogspot.com

Source: https://www.c-sharpcorner.com/article/export-and-import-data-using-npoi-library-in-net-core-2-razor-pages/

Share this post