Using EPPlus To Import And Export Data In ASP.NET Core

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.

Install-Package EPPlus -Version 4.5.2.1

Create two classes we need for this demo, one is UserInfo class that contains two properties.

public class UserInfo
{
    public string UserName { get; set; }

    public int Age { get; set; }
}

The other one is DemoResponse which unifies the response structure.

public class DemoResponse<T>
{
    public int Code { get; set; }

    public string Msg { get; set; }

    public T Data { get; set; }

    public static DemoResponse<T> GetResult(int code, string msg, T data = default(T))
    {
        return new DemoResponse<T>
        {
            Code = code,
            Msg = msg,
            Data = data
        };
    }
}

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.

[HttpPost("import")]
public async Task<DemoResponse<List<UserInfo>>> Import(IFormFile formFile, CancellationToken cancellationToken)
{
    if (formFile == null || formFile.Length <= 0)
    {
        return DemoResponse<List<UserInfo>>.GetResult(-1, "formfile is empty");
    }

    if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
    {
        return DemoResponse<List<UserInfo>>.GetResult(-1, "Not Support file extension");
    }

    var list = new List<UserInfo>();

    using (var stream = new MemoryStream())
    {
        await formFile.CopyToAsync(stream, cancellationToken);

        using (var package = new ExcelPackage(stream))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            var rowCount = worksheet.Dimension.Rows;

            for (int row = 2; row <= rowCount; row++)
            {
                list.Add(new UserInfo
                {
                    UserName = worksheet.Cells[row, 1].Value.ToString().Trim(),
                    Age = int.Parse(worksheet.Cells[row, 2].Value.ToString().Trim()),
                });
            }
        }
    }

    // add list to db ..
    // here just read and return

    return DemoResponse<List<UserInfo>>.GetResult(0, "OK", list);
}

We have an Excel file named aa.xlsx, and the following screenshot shows the contents of it.

 

 

 

We will import this file, and may get the following result.

 

 

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,

private readonly IHostingEnvironment _hostingEnvironment;

public EPPlusController(IHostingEnvironment hostingEnvironment)
{
    this._hostingEnvironment = hostingEnvironment;
}

[HttpGet("export")]
public async Task<DemoResponse<string>> Export(CancellationToken cancellationToken)
{
    string folder = _hostingEnvironment.WebRootPath;
    string excelName = $"UserList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
    string downloadUrl = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, excelName);
    FileInfo file = new FileInfo(Path.Combine(folder, excelName));
    if (file.Exists)
    {
        file.Delete();
        file = new FileInfo(Path.Combine(folder, excelName));
    }

    // query data from database
    await Task.Yield();

    var list = new List<UserInfo>()
    {
        new UserInfo { UserName = "catcher", Age = 18 },
        new UserInfo { UserName = "james", Age = 20 },
    };

    using (var package = new ExcelPackage(file))
    {
        var workSheet = package.Workbook.Worksheets.Add("Sheet1");
        workSheet.Cells.LoadFromCollection(list, true);
        package.Save();
    }

    return DemoResponse<string>.GetResult(0, "OK", downloadUrl);
}

After executing this method, we will get the link, and it will create a file in the wwwroot folder,

 

 

 

 

Opening this file, you may get the following content.

 

For the second way,

[HttpGet("exportv2")]
public async Task<IActionResult> ExportV2(CancellationToken cancellationToken)
{
    // query data from database
    await Task.Yield();
    var list = new List<UserInfo>()
    {
        new UserInfo { UserName = "catcher", Age = 18 },
        new UserInfo { UserName = "james", Age = 20 },
    };
    var stream = new MemoryStream();

    using (var package = new ExcelPackage(stream))
    {
        var workSheet = package.Workbook.Worksheets.Add("Sheet1");
        workSheet.Cells.LoadFromCollection(list, true);
        package.Save();
    }
    stream.Position = 0;
    string excelName = $"UserList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";

    //return File(stream, "application/octet-stream", excelName);
    return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
}

After executing this method, we will get the file directly.

 

 

 

Here is the source code you can find in my GitHub page.

• BasicEpplusDemo

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.