Complete steps for importing and exporting Excel files using EPPlus in ASP.NET Core

Time:2019-10-1

Preface

This article explains how to use EPPlus to import and export. xls /. xlsx files (Excel) in ASP. NET Core. When considering excel with. NET, we always look for third-party libraries or components. One of the most popular. net libraries for reading and writing Excel 2007/2010 files using Open Office Xml format (xlsx) is EPPlus. This library has been supporting. NET Core for a long time now. This applies to Windows, Linux and Mac.

So let’s create a new ASP.NET Core WEB API application and install EPPlus.Core. To install EPPlus.Core, run the following commands in the package manager console:

PM->Install-Package EPPlus.Core

Or you can install it through the UI interface.

Everything is ready. Now create a controller named ImportExport Controller. After adding it, let’s write the export method.

For demonstration purposes, I created an excel file in the wwwroot folder, so we need to get the absolute path of our project.


 public class ImportExportController : ControllerBase
 {
  private readonly IHostingEnvironment _hostingEnvironment;

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

Excel Package classes available in the Office OpenXml namespace will be used to read and write xlsx. Define a new Web API operation method called Export, which returns the URL of the generated xlsx file. So this is the complete code for exporting data to xlsx. You need to use Office OpenXml;


  [HttpGet]
  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 it. Now, when you run the application and call the export method. When you’re done, visit the folder of your application at wwroot. You should see “demo. xlsx” on the system. When you open it, you should see the following.

You can also bold the title, which is not provided by EPPlus. Core. You need to refer to it.using OfficeOpenXml; using OfficeOpenXml.Style;


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);
    }

 

As for import, the real situation is still more complicated. We will not verify it here. For demonstration, we just read the file just saved. The Import API reads the file and returns the file content with a formatted string. The following is the complete code for importing the API to read xlsx, create a formatted string of file content and return the same content.


[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;
   }
  }

summary

The above is the whole content of this article. I hope that the content of this article will have some reference and learning value for everyone’s study or work. If there is any doubt, you can leave a message to exchange. Thank you for your support for developpaer.