How to import and export Excel xlsx files by ASP.NET Core

This article introduces the relevant knowledge of "how to import and export Excel xlsx files from ASP.NET Core". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

ASP.NET Core uses EPPlus.Core to import and export Excel xlsx files. EPPlus.Core supports Excel 2007 Universe 2010 xlsx files to import and export, which can be run on Windows, Linux and Mac.

EPPlus.Core is based on EPPlus changes, and libgdiplus needs to be installed under Linux.

EPPlus: http://epplus.codeplex.com/

EPPlus.Core: https://github.com/VahidN/EPPlus.Core

Let's import and export the Excel xlsx file in ASP.NET Core.

New project

Create a new ASP.NET Core Web Application project ASPNETCoreExcel and select the Web application without authentication.

Then add an EPPlus.Core reference.

Use the NuGet command line:

Install-Package EPPlus.Core

You can also install using the NuGet package manager.

Export xlsx fil

Create a new XlsxController and add an Export operation.

Public class XlsxController: Controller {private IHostingEnvironment _ hostingEnvironment; public XlsxController (IHostingEnvironment hostingEnvironment) {_ hostingEnvironment = hostingEnvironment;} public IActionResult Index () {return View ();} public IActionResult Export () {string sWebRootFolder = _ hostingEnvironment.WebRootPath; string sFileName = $"{Guid.NewGuid ()} .xlsx"; FileInfo file = new FileInfo (Path.Combine (sWebRootFolder, sFileName)) Using (ExcelPackage package = new ExcelPackage (file)) {/ / add worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add ("aspnetcore"); / / add header worksheet.Cells [1,1] .value = "ID"; worksheet.Cells [1,2] .value = "Name"; worksheet.Cells [1,3] .value = "Url"; / / add value worksheet.Cells ["A2"]. Value = 1000 Worksheet.Cells ["B2"]. Value = "LineZero"; worksheet.Cells ["C2"]. Value = "http://www.cnblogs.com/linezero/"; worksheet.Cells [" A3 "]. Value = 1001; worksheet.Cells [" B3 "]. Value =" LineZero GitHub "; worksheet.Cells [" C3 "]. Value =" https://github.com/linezero"; worksheet.Cells ["C3"]. Style.Font.Bold = true; package.Save () } return File (sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");}

HostingEnvironment can be obtained by dependency injection, corresponding to the relevant directories and attributes of the program.

Then add the Index view and add a link to export Excel

@ {} ASP.NET Core Import and Export Excel xlsx File Export Excel

Click to export the file, and the results are as follows.

Import xlsx Fil

Add an upload file in the index view and add an Import operation.

Index.cshtml

@ {} ASP.NET Core Import and Export Excel xlsx File Export Excel [HttpPost] public IActionResult Import (IFormFile excelfile) {string sWebRootFolder = _ hostingEnvironment.WebRootPath; string sFileName = $"{Guid.NewGuid ()} .xlsx"; FileInfo file = new FileInfo (Path.Combine (sWebRootFolder, sFileName)); try {using (FileStream fs = new FileStream (file.ToString (), FileMode.Create)) {excelfile.CopyTo (fs); fs.Flush () } using (ExcelPackage package = new ExcelPackage (file)) {StringBuilder sb = new StringBuilder (); ExcelWorksheet worksheet = package.Workbook.Worksheets [1]; int rowCount = worksheet.Dimension.Rows; int ColCount = worksheet.Dimension.Columns; bool bHeaderRow = true; for (int row = 1; row