Introduction

There are some popular libraries that can help us deal with Excel files, such as DotNetCore.NPOI, npoi and EPPlus .etc.
In this article, we will use EPPlus to import and export Excel files in ASP.NET Core. EPPlus is a .NET library that reads and writes Excel files using the Office Open XML format (.xlsx). EPPlus has no dependencies other than .NET.
Let's take a look at how to do that.


Preparations

Create a new ASP.NET Core Web API Application and install EPPlus via nuGet.
  1. Install-Package EPPlus -Version 4.5.2.1
Create two classes we need for this demo, one is UserInfo class that contains two properties.
  1. public class UserInfo
  2. {
  3. public string UserName { get; set; }
  5. public int Age { get; set; }
  6. }
The other one is DemoResponse which unifies the response structure.
  1. public class DemoResponse<T>
  2. {
  3. public int Code { get; set; }
  5. public string Msg { get; set; }
  7. public T Data { get; set; }
  9. public static DemoResponse<T> GetResult(int code, string msg, T data = default(T))
  10. {
  11. return new DemoResponse<T>
  12. {
  13. Code = code,
  14. Msg = msg,
  15. Data = data
  16. };
  17. }
  18. }
Adding a new Web API controller named EPPlusController, we will add import and export methods here.

Import

In the real world, import functionality is complex and it involves validation, applying business rules and finally saving it in the database. But to show you, will define an import handler method to read and return the data of the Excel file.
  1. [HttpPost("import")]
  2. public async Task<DemoResponse<List<UserInfo>>> Import(IFormFile formFile, CancellationToken cancellationToken)
  3. {
  4. if (formFile == null || formFile.Length <= 0)
  5. {
  6. return DemoResponse<List<UserInfo>>.GetResult(-1, "formfile is empty");
  7. }
  9. if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
  10. {
  11. return DemoResponse<List<UserInfo>>.GetResult(-1, "Not Support file extension");
  12. }
  14. var list = new List<UserInfo>();
  16. using (var stream = new MemoryStream())
  17. {
  18. await formFile.CopyToAsync(stream, cancellationToken);
  20. using (var package = new ExcelPackage(stream))
  21. {
  22. ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
  23. var rowCount = worksheet.Dimension.Rows;
  25. for (int row = 2; row <= rowCount; row++)
  26. {
  27. list.Add(new UserInfo
  28. {
  29. UserName = worksheet.Cells[row, 1].Value.ToString().Trim(),
  30. Age = int.Parse(worksheet.Cells[row, 2].Value.ToString().Trim()),
  31. });
  32. }
  33. }
  34. }
  36. // add list to db ..
  37. // here just read and return
  39. return DemoResponse<List<UserInfo>>.GetResult(0, "OK", list);
  40. }
We have an Excel file named aa.xlsx, and the following screenshot shows the contents of it.
Using EPPlus To Import And Export Data In ASP.NET Core
We will import this file, and may get the following result.
Using EPPlus To Import And Export Data In ASP.NET Core

Export

There are two ways to export an Excel file.
  • Create a file and return the download link
  • Return the file directly
For the first way,
  1. private readonly IHostingEnvironment _hostingEnvironment;
  3. public EPPlusController(IHostingEnvironment hostingEnvironment)
  4. {
  5. this._hostingEnvironment = hostingEnvironment;
  6. }
  8. [HttpGet("export")]
  9. public async Task<DemoResponse<string>> Export(CancellationToken cancellationToken)
  10. {
  11. string folder = _hostingEnvironment.WebRootPath;
  12. string excelName = $"UserList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  13. string downloadUrl = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, excelName);
  14. FileInfo file = new FileInfo(Path.Combine(folder, excelName));
  15. if (file.Exists)
  16. {
  17. file.Delete();
  18. file = new FileInfo(Path.Combine(folder, excelName));
  19. }
  21. // query data from database
  22. await Task.Yield();
  24. var list = new List<UserInfo>()
  25. {
  26. new UserInfo { UserName = "catcher", Age = 18 },
  27. new UserInfo { UserName = "james", Age = 20 },
  28. };
  30. using (var package = new ExcelPackage(file))
  31. {
  32. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  33. workSheet.Cells.LoadFromCollection(list, true);
  34. package.Save();
  35. }
  37. return DemoResponse<string>.GetResult(0, "OK", downloadUrl);
  38. }
After executing this method, we will get the link, and it will create a file in the wwwroot folder,
Using EPPlus To Import And Export Data In ASP.NET Core
Using EPPlus To Import And Export Data In ASP.NET Core
Opening this file, you may get the following content.
Using EPPlus To Import And Export Data In ASP.NET Core

For the second way,
  1. [HttpGet("exportv2")]
  2. public async Task<IActionResult> ExportV2(CancellationToken cancellationToken)
  3. {
  4. // query data from database
  5. await Task.Yield();
  6. var list = new List<UserInfo>()
  7. {
  8. new UserInfo { UserName = "catcher", Age = 18 },
  9. new UserInfo { UserName = "james", Age = 20 },
  10. };
  11. var stream = new MemoryStream();
  13. using (var package = new ExcelPackage(stream))
  14. {
  15. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  16. workSheet.Cells.LoadFromCollection(list, true);
  17. package.Save();
  18. }
  19. stream.Position = 0;
  20. string excelName = $"UserList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  22. //return File(stream, "application/octet-stream", excelName);
  23. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  24. }
After executing this method, we will get the file directly.
Using EPPlus To Import And Export Data In ASP.NET Core
Here is the source code you can find in my GitHub page.

Summary

This short article shows how to use EPPlus library to import and export the Excel 2007+ file in ASP.NET Core simply. There are many other usages you can do with EPPlus, such as Cell styling, Charts. etc. We can visit its Github page for more information.