- To customize excel export
Contact Support
Excel export options in Vue Treegrid component
11 Jun 202423 minutes to read
To customize excel export
The excel export provides an option to customize mapping of the treegrid to excel document.
Export hidden columns
The excel export provides an option to export hidden columns of treegrid by defining includeHiddenColumn
as true
.
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' :visible='false' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script setup>
import { provide, ref } from "vue";
import { TreeGridComponent as EjsTreegrid, Page, Toolbar, ExcelExport, ColumnsDirective as EColumns, ColumnDirective as EColumn } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
const treegrid = ref(null);
const data = sampleData;
const pageSettings = { pageSize: 7 };
const toolbarOptions = ['ExcelExport'];
const toolbarClick = function(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
includeHiddenColumn: true
}
treegrid.value.excelExport(excelExportProperties);
}
};
provide('treegrid', [ Page, Toolbar, ExcelExport ]);
</script>
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' :visible='false' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script>
import { TreeGridComponent, Page, Toolbar, ExcelExport, ColumnDirective, ColumnsDirective } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
export default {
name: "App",
components: {
"ejs-treegrid":TreeGridComponent,
"e-columns":ColumnsDirective,
"e-column":ColumnDirective,
},
data () {
return {
data: sampleData,
toolbarOptions: ['ExcelExport'],
pageSettings: { pageSize: 7 }
};
},
methods: {
toolbarClick(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
includeHiddenColumn: true
}
this.$refs.treegrid.excelExport(excelExportProperties);
}
}
},
provide: {
treegrid: [ Page, Toolbar, ExcelExport ]
}
}
</script>
Show or hide columns on exported excel
You can show a hidden column or hide a visible column while printing the treegrid using toolbarClick
and excelExportComplete
events.
In the toolbarClick
event, based on args.item.text
as Excel Export
. We can show or hide columns by setting column.visible
property to true
or false
respectively.
In the excelExportComplete event, We have reversed the state back to the previous state.
In the below example, we have Duration
as a hidden column in the treegrid. While exporting, we have changed Duration
to visible column and StartDate
as hidden column.
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick' :excelExportComplete='excelExportComplete'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' :visible='false' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script setup>
import { provide, ref } from "vue";
import { TreeGridComponent as EjsTreegrid, Page, Toolbar, ExcelExport, ColumnDirective as EColumn, ColumnsDirective as EColumns } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
const treegrid = ref(null);
const data = sampleData;
const pageSettings = { pageSize: 7 };
const toolbarOptions = ['ExcelExport'];
const toolbarClick = function(args) {
if (args['item'].text === 'Excel Export') {
treegrid.value.ej2Instances.grid.getColumns()[2].visible = false;
treegrid.value.ej2Instances.grid.getColumns()[3].visible = true;
treegrid.value.excelExport();
}
};
const excelExportComplete = function() {
treegrid.value.ej2Instances.grid.getColumns()[2].visible = true;
treegrid.value.ej2Instances.grid.getColumns()[3].visible = false;
};
provide('treegrid', [ Page, Toolbar, ExcelExport ]);
</script>
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick' :excelExportComplete='excelExportComplete'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' :visible='false' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script>
import { TreeGridComponent, Page, Toolbar, ExcelExport, ColumnDirective, ColumnsDirective } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
export default {
name: "App",
components: {
"ejs-treegrid":TreeGridComponent,
"e-columns":ColumnsDirective,
"e-column":ColumnDirective,
},
data () {
return {
data: sampleData,
toolbarOptions: ['ExcelExport'],
pageSettings: { pageSize: 7 }
};
},
methods: {
toolbarClick(args) {
if (args['item'].text === 'Excel Export') {
this.$refs.treegrid.ej2Instances.grid.getColumns()[2].visible = false;
this.$refs.treegrid.ej2Instances.grid.getColumns()[3].visible = true;
this.$refs.treegrid.excelExport();
}
},
excelExportComplete() {
this.$refs.treegrid.ej2Instances.grid.getColumns()[2].visible = true;
this.$refs.treegrid.ej2Instances.grid.getColumns()[3].visible = false;
}
},
provide: {
treegrid: [ Page, Toolbar, ExcelExport ]
}
}
</script>
File name for exported document
You can assign the file name for the exported document by defining fileName
property in ExcelExportProperties
.
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script setup>
import { provide, ref } from "vue";
import { TreeGridComponent as EjsTreegrid, Page, Toolbar, ExcelExport,
ColumnDirective as EColumn, ColumnsDirective as EColumns
} from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
const treegrid = ref(null);
const data = sampleData;
const pageSettings = { pageSize: 7 };
const toolbarOptions = ['ExcelExport'];
const toolbarClick = function(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
fileName:"new.xlsx"
}
treegrid.value.excelExport(excelExportProperties);
}
};
provide('treegrid', [ Page, Toolbar, ExcelExport ]);
</script>
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script>
import { TreeGridComponent, Page, Toolbar, ExcelExport, ColumnDirective, ColumnsDirective } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
export default {
name: "App",
components: {
"ejs-treegrid":TreeGridComponent,
"e-columns":ColumnsDirective,
"e-column":ColumnDirective,
},
data () {
return {
data: sampleData,
toolbarOptions: ['ExcelExport'],
pageSettings: { pageSize: 7 }
};
},
methods: {
toolbarClick(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
fileName:"new.xlsx"
}
this.$refs.treegrid.excelExport(excelExportProperties);
}
}
},
provide: {
treegrid: [ Page, Toolbar, ExcelExport ]
}
}
</script>
Persist collapsed state
You can persist the collapsed state in the exported document by defining isCollapsedStatePersist
property as true in TreeGridExcelExportProperties
parameter of excelExport
method.
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script setup>
import { provide } from "vue";
import { TreeGridComponent as EjsTreegrid, Page, Toolbar, ExcelExport, ColumnDirective as EColumn,
ColumnsDirective as EColumns
} from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
const treegrid = ref(null);
const data = sampleData;
const pageSettings = { pageSize: 7 };
const toolbarOptions = ['ExcelExport'];
const toolbarClick = function(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
isCollapsedStatePersist: true
}
treegrid.value.excelExport(excelExportProperties);
}
};
provide('treegrid', [ Page, Toolbar, ExcelExport ]);
</script>
<template>
<div id="app">
<ejs-treegrid ref='treegrid' :dataSource='data' height='220' childMapping='subtasks' :treeColumnIndex='1' :allowPaging='true' :pageSettings='pageSettings' :allowExcelExport='true' :toolbar='toolbarOptions' :toolbarClick='toolbarClick'>
<e-columns>
<e-column field='taskID' headerText='Task ID' width='90' textAlign='Right'></e-column>
<e-column field='taskName' headerText='Task Name' width='160'></e-column>
<e-column field='startDate' headerText='Start Date' width='90' format="yMd" textAlign='Right'></e-column>
<e-column field='duration' headerText='Duration' width='80' textAlign='Right'></e-column>
</e-columns>
</ejs-treegrid>
</div>
</template>
<script>
import { TreeGridComponent, Page, Toolbar, ExcelExport, ColumnDirective, ColumnsDirective } from "@syncfusion/ej2-vue-treegrid";
import { sampleData } from "./datasource.js";
export default {
name: "App",
components: {
"ejs-treegrid":TreeGridComponent,
"e-columns":ColumnsDirective,
"e-column":ColumnDirective,
},
data () {
return {
data: sampleData,
toolbarOptions: ['ExcelExport'],
pageSettings: { pageSize: 7 }
};
},
methods: {
toolbarClick(args) {
if (args['item'].text === 'Excel Export') {
let excelExportProperties = {
isCollapsedStatePersist: true
}
this.$refs.treegrid.excelExport(excelExportProperties);
}
}
},
provide: {
treegrid: [ Page, Toolbar, ExcelExport ]
}
}
</script>