Search results

Excel Exporting in ASP.NET Core Pivot Grid control

21 Oct 2021 / 2 minutes to read

The Excel export allows Pivot Grid data to export as Excel document. To enable Excel export in the pivot grid, set the allowExcelExport as true. You need to use the excelExport method for Excel exporting.

tagHelper
Export.cs
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        pivotGridObj.excelExport();
    }
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

Multiple pivot grid exporting

The Excel export provides an option to export multiple pivot grid data in the same Excel file.

Same WorkSheet

The Excel export provides support to export multiple pivot grids in same sheet. To export in same sheet, define multipleExport.type as AppendToSheet in excelExportProperties. It has an option to provide blank rows between pivot grids and these blank row(s) count can be defined using themultipleExport.blankRows property.

By default, multipleExport.blankRows value is 5 between pivot grid’s within the same sheet.

tagHelper
SameSheetExport.cs
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>
<br />
<ejs-pivotview id="PivotGrid2" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-columns>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-columns>
        <e-rows>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-rows>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj; var pivotGridObj2;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        pivotGridObj2 = document.getElementById('PivotGrid2').ej2_instances[0];

        var excelExportProperties = {
            multipleExport: { type: 'AppendToSheet', blankRows: 2 }
        };
        var firstGridExport = pivotGridObj.grid.excelExport(excelExportProperties, true);
        firstGridExport.then(function (fData) {
            pivotGridObj2.excelExport(excelExportProperties, false, fData);
        });
    }
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

New WorkSheet

Excel export provides support to export multiple pivot grids into new sheets. To export in new sheets, define multipleExport.type as NewSheet in excelExportProperties.

tagHelper
NewSheetExport.cs
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>
<br />
<ejs-pivotview id="PivotGrid2" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-columns>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-columns>
        <e-rows>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-rows>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj; var pivotGridObj2;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        pivotGridObj2 = document.getElementById('PivotGrid2').ej2_instances[0];

        var excelExportProperties = {
            multipleExport: { type: 'NewSheet' }
        };
        var firstGridExport = pivotGridObj.grid.excelExport(excelExportProperties, true);
        firstGridExport.then(function (fData) {
            pivotGridObj2.excelExport(excelExportProperties, false, fData);
        });
    }
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

Customization during excel export

The Excel export provides an option to customize mapping of the pivot grid to excel document.

Theme

The Excel export provides an option to include theme for exported Excel document.

To apply theme in exported Excel, define the theme in excelExportProperties.

By default, material theme is applied to exported Excel document.

tagHelper
ThemeExport.cs
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        var excelExportProperties = {
            theme: {
                header: { fontName: 'Segoe UI', fontColor: '#666666' },
                record: { fontName: 'Segoe UI', fontColor: '#666666' },
                caption: { fontName: 'Segoe UI', fontColor: '#666666' }
            }
        };
        pivotGridObj.excelExport(excelExportProperties);
    }
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

The Excel export provides an option to include header and footer content for the exported Excel document.

tagHelper
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        var excelExportProperties = {
            header: {
                headerRows: 2,
                rows: [
                    { cells: [{ colSpan: 4, value: "Pivot Grid", style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center', bold: true, underline: true } }] }
                ]
            },
            footer: {
                footerRows: 4,
                rows: [
                    { cells: [{ colSpan: 4, value: "Thank you for your business!", style: { hAlign: 'Center', bold: true } }] },
                    { cells: [{ colSpan: 4, value: "!Visit Again!", style: { hAlign: 'Center', bold: true } }] }
                ]
            }
        };
        pivotGridObj.excelExport(excelExportProperties);
    }
</script>

CSV Export

Also, the Excel export allows Pivot Grid data to be exported in CSV file format. To export pivot grid in CSV file format, you need to use the csvExport method.

tagHelper
CSVExport.cs
Copied to clipboard
<ejs-button id="excel" content="Export To Excel" isPrimary="true"></ejs-button>

<ejs-pivotview id="PivotGrid" height="300" allowExcelExport="true">
    <e-datasource data="@ViewBag.data" expandAll="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasource>
</ejs-pivotview>

<script>
    var pivotGridObj;
    document.getElementById('excel').onclick = function () {
        pivotGridObj = document.getElementById('PivotGrid').ej2_instances[0];
        pivotGridObj.csvExport();
    }
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

See Also