loading...
Cover image for Export to Excel from a .Net Core WebApp (Razor Pages)

Export to Excel from a .Net Core WebApp (Razor Pages)

zoltanhalasz profile image Zoltan Halasz ・3 min read

Some background knowledge is needed for this tutorial:

  1. intermediate C#
  2. basic .Net core Razor Pages, check Microsoft tutorial here https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/?view=aspnetcore-2.2

For Excel Export: install latest EPPlus Nuget package

In my Razor pages application (with .Net Core 2.2) I wanted to export some of my lists/data tables to excel files. Then I found some instruction on the internet, which I implemented to my Razor Page app. Following the base tutorial, here's how you do this.

I want to export a list of Business Units, defined by the class:

    public class BusinessUnits
    {
        public int Id { get; set; }
        public string Buname { get; set; }
        public string Bucode { get; set; }
    }

This class corresponds to a table of my database, called BusinessUnits, scaffolded to models/classes using EF Core database first approach. (not included in the scope of this tutorial).

I want the user to be able to see/export the list of business units.
For this, a page will be generated to the Page folder, using the above model and the corresponding context. The page will be able to List all the elements of the table, and the Razor page will be generated automatically.
See the scaffolding, following the model from the Microsoft Tutorial:
https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/model?view=aspnetcore-2.2&tabs=visual-studio
The main table generated will look like below, according to the model scaffolding:

<table class="table table-sm table-bordered table-condensed">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.BusinessUnits[0].Buname)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.BusinessUnits[0].Bucode)
            </th>           
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.BusinessUnits)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Buname)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Bucode)
                </td>                
            </tr>
        }
    </tbody>
</table>

Then just above the < table > tag we will include a form with one button, to trigger the export in Excel/download of file. Corresponding to this there will be a page handler method later in the PageModel class.

   <form method="post" asp-page-handler="ExportExcel">
      <button class="btn btn-sm btn-secondary order-button ">ExportExcel</button>
   </form>

Obviously, having generated the cshtml page from a model, there will be a .cs class behind it, containing the Pagemodel implementation.

The list with the business units is automatically generated as a property of the class. This is populated with EF Core using the data from the database table (or any data source modelled by the entity BusinessUnits).

        public IList<BusinessUnits> BusinessUnits { get;set; }

Then, corresponding to the above page handler method mentioned in the Razor page html code, we need to write the function that does the export of the Excel file with the Business Units. But before this, add the at the end of the using statements the following:

using OfficeOpenXml;

The below code will be the implementation of the Handler that will export the file.

        public async Task<IActionResult> OnPostExportExcelAsync()
        {

            var myBUs = await _context.BusinessUnits.ToListAsync(); 
// above code loads the data using LINQ with EF (query of table), you can substitute this with any data source.
            var stream = new MemoryStream();

            using (var package = new ExcelPackage(stream))
            {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");
                workSheet.Cells.LoadFromCollection(myBUs, true);
                package.Save();
            }
            stream.Position = 0;

            string excelName = $"BusinessUnits-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx"; 
// above I define the name of the file using the current datetime.

            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); // this will be the actual export.
        }

After running the app please navigate to the Business Unit page and when triggering the Export button, the result will be a file download, as seen in the bottom of the picture below:
Download Excel

Discussion

pic
Editor guide