Excel export options in Vue Grid component
23 Dec 202324 minutes to read
The excel export provides an option to customize mapping of the grid to excel document.
To get start quickly with Excel export Options, you can check on this video:
Export current page
The excel export provides an option to export the current page into excel. To export current page, define exportType to CurrentPage.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :toolbar='toolbarOptions' height='220px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport, Page } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport']
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
let excelExportProperties = {
exportType: 'CurrentPage'
};
this.$refs.grid.excelExport(excelExportProperties);
}
}
},
provide: {
grid: [Toolbar, ExcelExport, Page]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Export the selected records only
You can export the selected records data by passing it to exportProperties.dataSource property in the toolbarClick event.
In the below exporting demo, We can get the selected records using getSelectedRecords method and pass the selected data to PdfExport or excelExport property.
<template>
<div id="app">
<ejs-grid ref='grid' :dataSource='data' :toolbar='toolbarOptions' :allowPaging='true' :allowFiltering='true' :allowPdfExport='true' :allowExcelExport='true' :pageSettings='pageSettings' :toolbarClick='toolbarClick' :selectionSettings='selectionOption'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=100></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, PdfExport, Filter, Page, ExcelExport } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
import { DataManager } from "@syncfusion/ej2-data";
Vue.use(GridPlugin);
export default {
data: () => {
return {
data: data,
toolbarOptions: ['PdfExport', 'ExcelExport'],
pageSettings: { pageSize: 5, pageCount:5 },
selectionOption: {type: 'Multiple'}
};
},
methods: {
toolbarClick(args: ClickEventArgs) {
if (args['item'].id.indexOf("pdfexport") != -1) {
let selectedRecords = this.$refs.grid.getSelectedRecords();
let exportProperties = {
dataSource: selectedRecords
};
this.$refs.grid.pdfExport(exportProperties);
}
else if (args['item'].id.indexOf("excelexport") != -1) {
let selectedRecords = this.$refs.grid.getSelectedRecords();
let exportProperties = {
dataSource: selectedRecords
};
this.$refs.grid.excelExport(exportProperties);
}
},
},
provide: {
grid: [Toolbar, PdfExport, Filter, Page, ExcelExport]
},
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Export hidden columns
The excel export provides an option to export hidden columns of grid by defining includeHiddenColumn as true.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :toolbar='toolbarOptions' height='270px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150 :visible='false'></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport']
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
let excelExportProperties = {
includeHiddenColumn: true
};
this.$refs.grid.excelExport(excelExportProperties);
}
}
},
provide: {
grid: [Toolbar, ExcelExport]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Show or hide columns
You can show a hidden column or hide a visible column while printing the grid using toolbarClick and pdfExportComplete events.
In the toolbarClick event, based on args.item.id as Grid_pdfexport. We can show or hide columns by setting column.visible property to true or false respectively.
In the pdfExportComplete event, We have reversed the state back to the previous state.
In the below example, we have CustomerID as a hidden column in the grid. While exporting, we have changed CustomerID to visible column and ShipCity as hidden column.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :toolbar='toolbarOptions' height='272px' :allowExcelExport='true' :excelExportComplete='excelExportComplete' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' :visible='false' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport']
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') {
this.$refs.grid.getColumns()[1].visible = true;
this.$refs.grid.getColumns()[3].visible = false;
this.$refs.grid.excelExport();
}
},
excelExportComplete(args) {
this.$refs.grid.getColumns()[1].visible = true;
this.$refs.grid.getColumns()[3].visible = true;
}
},
provide: {
grid: [Toolbar, ExcelExport]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Export with filter options
The excel export provides an option to export with filter option in excel by defining enableFilter as true . It requires the allowFiltering to be true.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :allowFiltering='true' :toolbar='toolbarOptions' height='270px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150 :visible='false'></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport, Filter } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport']
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
let excelExportProperties = {
enableFilter: true
};
this.$refs.grid.excelExport(excelExportProperties);
}
}
},
provide: {
grid: [Toolbar, ExcelExport, Filter]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Exporting grouped records
The excel export provides outline option for grouped records which hides the detailed data for better viewing. In grid, we have provided the outline option for the exported document when the data’s are grouped.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :allowGrouping='true' :groupSettings='groupOptions' :toolbar='toolbarOptions' height='220px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport, Page, Group } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport'],
groupOptions: { columns: ['CustomerID', 'ShipCity'] }
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
this.$refs.grid.excelExport();
}
}
},
provide: {
grid: [Toolbar, ExcelExport, Page, Group]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Define file name
You can assign the file name for the exported document by defining fileName property in excelExportProperties.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :toolbar='toolbarOptions' height='270px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport']
};
},
methods: {
toolbarClick: function(args) {
if (args.item.id === 'Grid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
let excelExportProperties = {
fileName:"new.xlsx"
};
this.$refs.grid.excelExport(excelExportProperties);
}
}
},
provide: {
grid: [Toolbar, ExcelExport]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Export the master detail grid
It is possible to export the master-detail grid on the same Excel sheet using the ExcelExportProperties class in the grid.
To export the master-detail grid on the same sheet in the following sample, you need to set the multipleExport.type to AppendToSheet in the exportProperties. A promise object is created by exporting the master grid first, and then the detail grid is exported after the master grid has been successfully exported.
<template>
<div id="app">
<ejs-grid ref="MasterGrid" :dataSource="data" id="MasterGrid" :selectedRowIndex="1" :toolbar="toolbar" :rowSelected="rowSelected" :toolbarClick="toolbarClick" allowExcelExport="true">
<e-columns>
<e-column field="ContactName" headerText="Customer Name" width="150"></e-column>
<e-column field="CompanyName" headerText="Company Name" width="150"></e-column>
<e-column field="Address" headerText="Address" width="150"></e-column>
<e-column field="Country" headerText="Country" width="130"></e-column>
</e-columns>
</ejs-grid>
<div class="e-statustext">Showing orders of Customer: <b id="key"></b></div>
<ejs-grid ref="grid" :allowSelection="false" allowExcelExport="true">
<e-columns>
<e-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-column>
<e-column field="Freight" headerText="Freight" format="C2" width="100" type="number"></e-column>
<e-column field="ShipName" headerText="Ship Name" width="200"></e-column>
<e-column field="ShipCountry" headerText="Ship Country" width="150"></e-column>
<e-column field="ShipAddress" headerText="Ship Address" width="200"></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport } from "@syncfusion/ej2-vue-grids";
import { customerData, data } from "./datasource.js";
Vue.use(GridPlugin);
export default {
data() {
var names = ["AROUT", "BERGS", "BLONP", "CHOPS", "ERNSH"];
return {
toolbar: ["ExcelExport"],
data: customerData.filter(function (e) {
return names.indexOf(e.CustomerID) !== -1;
})
};
},
methods: {
rowSelected: function (args) {
let selectedRecord = args.data;
this.$refs.grid.ej2Instances.dataSource = data.filter((record) => record.CustomerName === selectedRecord.ContactName).slice(0, 5);
document.getElementById("key").innerHTML = selectedRecord.ContactName;
},
toolbarClick: function (args) {
const appendExcelExportProperties = {
multipleExport: { type: "AppendToSheet", blankRows: 2 },
};
const firstGridExport = this.$refs.MasterGrid.ej2Instances.excelExport(
appendExcelExportProperties, true);
firstGridExport.then((fData) => {
this.$refs.grid.ej2Instances.excelExport(appendExcelExportProperties, false, fData);
});
}
},
provide: {
grid: [Toolbar, ExcelExport],
}
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>Export grid as blob
The Grid offers an option to export the data as a Blob instead of downloading it as a file in the browser. To export the grid as a Blob, set the isBlob parameter to true in the excelExport method. The grid returns the promise of a blob in the excelExportComplete event.
The following example demonstrates how to obtain the blob data of the exported grid by executing the promise in the excelExportComplete event.
<template>
<div id="app">
<ejs-grid ref='grid' id='Grid' :dataSource='data' :toolbar='toolbarOptions' height='220px' :allowPaging='true' :allowExcelExport='true' :toolbarClick='toolbarClick' :excelExportComplete="excelExportComplete">
<e-columns>
<e-column field='OrderID' headerText='Order ID' textAlign='Right' width=120></e-column>
<e-column field='CustomerID' headerText='Customer ID' width=150></e-column>
<e-column field='ShipCity' headerText='Ship City' width=150></e-column>
<e-column field='ShipName' headerText='Ship Name' width=150></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Toolbar, ExcelExport, Page } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js';
Vue.use(GridPlugin);
export default {
data() {
return {
data: data,
toolbarOptions: ['ExcelExport', 'CsvExport']
};
},
methods: {
toolbarClick(args) {
if (args.item.id === 'Grid_excelexport') {
// pass fourth parameter as true to get the blob data of exported grid
this.$refs.grid.excelExport(null, null, null, true);
}
if (args.item.id === 'Grid_csvexport') {
// pass fourth parameter as true to get the blob data of exported grid
this.$refs.grid.csvExport(null, null, null, true);
}
},
excelExportComplete(args) {
// execute the promise to get the blob data
args.promise.then((e) => {
console.log(e.blobData);
});
},
},
provide: {
grid: [Toolbar, ExcelExport, Page]
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>