Adding Excel Export

sayuga edited this page Jul 11, 2016 · 2 revisions
Clone this wiki locally

Adding Excel Export (Basic): TypeScript

I have found Serenity as a huge help and wanted to return the favor with the little snippets of re-usable code that doesn't always get mentioned but that are useful too keep in a "toolbox" of sorts.

Adding an excel export to your table is simpler than you think. ReportHelper and ExcelExportHelper do most of the work for you when setting things up. You just need to add code in 2 places: xyzGrid.ts and xyzEndpoint.cs


In xyzGrid.ts add

getButtons() {
        var buttons = super.getButtons();
        var MyRow = xyzRow.Fields;

        buttons.push(myProject.Common.ExcelExportHelper.createToolButton({
            grid: this,
            onViewSubmit: () => this.onViewSubmit(),
            service: myTableService.baseUrl + '/ListExcel',
            separator: true,
            hint: "This is the Hint",
            title:"This is the Title"
        }));

        return buttons;
       }

Explanation of fields:

  • Grid: takes the current view of the grid (if you hid columns using column picker it ignores them)
  • onViewSubmit: action being taken when event occurs
  • service: Location of list repository (See xyzEndpoint.cs)
  • hint: Caption that appears in the button itself.
  • title: Caption that appears as label/name of button when you hover

On the xyzEndpoint.cs file, add the following code

using Serenity.Reporting;
using Serenity.Web;
using System;

//.....///

public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
    {
        var data = List(connection, request).Entities;
        var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.xyzColumns));
        var bytes = new ReportRepository().Render(report);
        var reportName = "xyzList_";
        return ExcelContentResult.Create(bytes, reportName + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
    }

You'll need to have Serenity.Reporting, Serenity.Web and System to be able to run the code properly. The two fields that you will be needing to update are: reportName and typeof(Columns.xyzColumns).

-reportName: Designates name of the output file. -typeof(Columns.xyzColumns): Defines the source columns.

With that all in place, you are good to go. The tables with this code will display the excel export button on the toolbar. Something worth remembering is that the export will pull only the columns currently active in the view thus if you have 5 columns but you are only displaying 3, you'll only export 3 columns.


The Excel Export is based on EPPLus ( http://epplus.codeplex.com/) which allows further editing of the output but I leave that for another time.