The Excel export allows Pivot Table data to be exported as Excel document. To enable Excel export in the pivot table, set the AllowExcelExport
property in PivotView
class to true. Once the API is set, user needs to call the excelExport
method for exporting on external button click.
The pivot table component can be exported to Excel format using options available in the toolbar. For more details
refer
here.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj.excelExport();
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Excel export provides an option to export multiple pivot table data in the same Excel file.
The Excel export provides support to export multiple pivot tables 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 tables and these blank row(s) count can be defined using themultipleExport.blankRows
property.
By default,
multipleExport.blankRows
value is 5 between pivot table’s within the same sheet.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<br />
@Html.EJS().PivotView("PivotGrid2").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Columns(columns =>
{
columns.Name("Country").Add(); columns.Name("Products").Add();
}).Rows(rows =>
{
rows.Name("Year").Caption("Year").Add(); rows.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj2 = document.getElementById('PivotGrid2').ej2_instances[0];
var excelExportProperties = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 }
};
var firstGridExport = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then(function (fData) {
pivotObj2.excelExport(excelExportProperties, false, fData);
});
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Excel export provides support to export multiple pivot tables into new sheets. To export in new sheets, define multipleExport.type
as NewSheet
in excelExportProperties
.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<br />
@Html.EJS().PivotView("PivotGrid2").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Columns(columns =>
{
columns.Name("Country").Add(); columns.Name("Products").Add();
}).Rows(rows =>
{
rows.Name("Year").Caption("Year").Add(); rows.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj2 = document.getElementById('PivotGrid2').ej2_instances[0];
var excelExportProperties = {
multipleExport: { type: 'NewSheet' }
};
var firstGridExport = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then(function (fData) {
pivotObj2.excelExport(excelExportProperties, false, fData);
});
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Excel export provides an option to change colors for headers, caption and records in pivot table before exporting. In-order to apply colors, define theme settings in excelExportProperties object and pass it as a parameter to the excelExport
method.
By default, material theme is applied to exported Excel document.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
var excelExportProperties = {
theme: {
header: { fontName: 'Segoe UI', fontColor: '#666666' },
record: { fontName: 'Segoe UI', fontColor: '#666666' },
caption: { fontName: 'Segoe UI', fontColor: '#666666' }
}
};
pivotObj.excelExport(excelExportProperties);
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Excel export provides an option to include header and footer content for the excel document before exporting. In-order to add header and footer, define header and footer properties in excelExportProperties object and pass it as a parameter to the excelExport
method.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').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 } }] }
]
}
};
pivotObj.excelExport(excelExportProperties);
}
</script>
The Excel export provides an option to change file name of the document before exporting. In-order to change the file name, define fileName property in excelExportProperties object and pass it as a parameter to the excelExport
method.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
var excelExportProperties = {
filename:'sample.xslx'
};
pivotObj.excelExport(excelExportProperties);
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Also, the Excel export allows pivot table data to be exported in CSV
file format. To export pivot table in CSV
file format, you need to use the csvExport
method.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj.csvExport();
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
You can export the pivot table virtual scroll data as Excel/CSV document by using PivotEngine export without any performance degradation. To enable PivotEngine export in the pivot table, set the AllowExcelExport
as true. You need to use the exportToExcel
method for PivotEngine export.
PivotEngine export will be performed while enabling virtual scrolling by default.
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj.excelExportModule.exportToExcel('Excel');
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
@Html.EJS().Button("excel").Content("Export To Excel").IsPrimary(true).Render()
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).AllowExcelExport(true).Render()
<script>
var pivotObj; var pivotObj2;
document.getElementById('excel').onclick = function () {
pivotObj = document.getElementById('PivotView').ej2_instances[0];
pivotObj.excelExportModule.exportToExcel('csv');
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event ExcelQueryCellInfo
triggers while framing each row and value cell during Excel export. It allows the user to customize the cell value, style etc. of the current cell. It has the following parameters:
value
- It holds the cell value.column
- It holds column information for the current cell.data
- It holds the entire row data across the current cell.style
- It holds the style properties for the cell.@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ExcelQueryCellInfo("excelQueryCell")).Render()
<script>
function excelQueryCell(args) {
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event ExcelHeaderQueryCellInfo
triggers on framing each header cell during Excel export. It allows the user to customize the cell value, style etc. of the current cell. It has the following parameters:
cell
- It holds the current cell information.style
- It holds the style properties for the cell.@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ExcelHeaderCellInfo("excelHeaderCell")).Render()
<script>
function excelHeaderCell(args) {
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}