Import and Export xlsx in ASP.NET Core

This post shows how to import and export .xls or .xlsx (Excel files) in ASP.NET Core. And when thinking about dealing with excel with .NET, we always look for third-party libraries or component. And one of the most popular .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx) is EPPlus. However, at the time of writing this post, this library is not updated to support .NET Core. But there exists an unofficial version of this library EPPlus.Core which can do the job of import and export xlsx in ASP.NET Core. This works on Windows, Linux and Mac.

Import and Export xlsx in ASP.NET Core

So let’s create a new ASP.NET Core WEB API application and install EPPlus.Core. To install EPPlus.Core, run the following command in the Package Manager Console:

PM> Install-Package EPPlus.Core

Or you can also use Nuget Package Manager UI to install it.

Let’s add a new Web API controller and name it ImportExportController. Once added, let’s first create export method.

Export xlsx in ASP.NET Core

For the demo, we will be creating the xlsx file in the wwwroot folder. To get wwwroot folder path, we need to inject IHostingEnvironment dependency in the constructor. Read how to Get application base and wwwroot path in ASP.NET Core.

public class ImportExportController : Controller
{
    private readonly IHostingEnvironment _hostingEnvironment;

    public ImportExportController(IHostingEnvironment hostingEnvironment)
    {
        _hostingEnvironment = hostingEnvironment;
    }
}

ExcelPackage class, available in OfficeOpenXml namespace will be used for reading and writing xlsx. Define a new web api action method named “Export” which returns the URL of generated xlsx file. So here is the complete code for exporting data to xlsx.

[HttpGet]
[Route("Export")]
public string Export()
{
    string sWebRootFolder = _hostingEnvironment.WebRootPath;
    string sFileName = @"demo.xlsx";
    string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
    FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
    if (file.Exists)
    {
        file.Delete();
        file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
    }
    using (ExcelPackage package = new ExcelPackage(file))
    {
        // add a new worksheet to the empty workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Employee");
        //First add the headers
        worksheet.Cells[1, 1].Value = "ID";
        worksheet.Cells[1, 2].Value = "Name";
        worksheet.Cells[1, 3].Value = "Gender";
        worksheet.Cells[1, 4].Value = "Salary (in $)";

        //Add values
        worksheet.Cells["A2"].Value = 1000;
        worksheet.Cells["B2"].Value = "Jon";
        worksheet.Cells["C2"].Value = "M";
        worksheet.Cells["D2"].Value = 5000;

        worksheet.Cells["A3"].Value = 1001;
        worksheet.Cells["B3"].Value = "Graham";
        worksheet.Cells["C3"].Value = "M";
        worksheet.Cells["D3"].Value = 10000;

        worksheet.Cells["A4"].Value = 1002;
        worksheet.Cells["B4"].Value = "Jenny";
        worksheet.Cells["C4"].Value = "F";
        worksheet.Cells["D4"].Value = 5000;

        package.Save(); //Save the workbook.
    }
    return URL;
}

That’s all. Now when you run this application and call export method. Once finished, visit the wwwroot folder of your application. You should see “demo.xlsx” created on the system. And when you open it, you should see following.

You can also format the excel cells. Following code will apply light-Gray color and bold font style to the header row.

using (var cells = worksheet.Cells[1, 1, 1, 4])
{
    cells.Style.Font.Bold = true;
    cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
    cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}

To apply styles to excel cells, you need to include namespace OfficeOpenXml.Style.

Simple, isn’t it? All the features of the EPPlus library are supported by this unofficial port called EPPlus.Core

Import xlsx in ASP.NET Core

Real world import functionality is complex and it involves validations, applying business rules and finally saving it in database. But for the demo, let’s import the same file created via Export API and print its content. Import API will read the file and returns the file content in a formatted string. Here is the complete code for import API to read the xlsx, create a formatted string of file content and returns the same.

[HttpGet]
[Route("Import")]
public string Import()
{
    string sWebRootFolder = _hostingEnvironment.WebRootPath;
    string sFileName = @"demo.xlsx";
    FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
    try
    {
        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 <= rowCount; row++)
            {
                for (int col = 1; col <= ColCount; col++)
                {
                    if (bHeaderRow)
                    {
                        sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                    }
                    else
                    {
                        sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                    }
                }
                sb.Append(Environment.NewLine);
            }
            return sb.ToString();
        }
    }
    catch (Exception ex)
    {
        return "Some error occured while importing." + ex.Message;
    }
}

Import API gets the row and column count and then loops through them to create a formatted string. That’s all. Now when you run call to import API, you should see following in the browser.

That’s it. I hope it helped you.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then