Excel export in Vue Treegrid component

2 Sep 202524 minutes to read

The excel export allows exporting TreeGrid data to Excel document. You need to use the excelExport method for exporting. To enable Excel export in the treegrid, set the allowExcelExport as true.

To use excel export, You need to inject the ExcelExport module in treegrid.

To get start quickly with excel export feature, you can check on this video:

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<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') {
      treegrid.value.excelExport();
    }
};

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') {
            this.$refs.treegrid.excelExport();
        }
    }
  },
  provide: {
      treegrid: [ Page, Toolbar, ExcelExport ]
  }
}
</script>

Custom data source

The excel export provides an option to define datasource dynamically before exporting. To export data dynamically, define the dataSource in exportProperties.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<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 = {
        dataSource: sampleData
      }
      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, ColumnsDirective, ColumnDirective } 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 = {
            dataSource: sampleData
          }
          this.$refs.treegrid.excelExport(excelExportProperties);
        }
    }
  },
  provide: {
      treegrid: [ Page, Toolbar, ExcelExport ]
  }
}
</script>

Exporting Custom Aggregates in Tree Grid

The Tree Grid enables exporting custom aggregates, which summarize column data, to an Excel document using the ExcelAggregateQueryCellInfo event.

In the provided example, the customAggregateFn function computes the item count for a selected category, while the ExcelAggregateQueryCellInfo event customizes the exported cell values in the Excel document.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
```vue
<template>
  <div class="col-lg-12 control-section">
    <div>
      <ejs-treegrid
        :dataSource="data"
        ref="treegrid"
        childMapping="subtasks"
        gridLines="Both"
        :treeColumnIndex="1"
        :allowExcelExport="true"
        :dataBound="dataBound"
        :toolbar="toolbarOptions"
        :toolbarClick="toolbarClick"
        :excelAggregateQueryCellInfo="excelAggregateQueryCellInfo"
        :height="380"
      >
        <e-columns>
          <e-column field="ID" headerText="Order ID" width="120" textAlign="Left"></e-column>
          <e-column field="Name" headerText="Shipment Name" width="230" clipMode="EllipsisWithTooltip"></e-column>
          <e-column field="shipmentDate" headerText="Shipment Date" width="150" type="date" format="yMd" textAlign="Right"></e-column>
          <e-column field="category" headerText="Category" width="220" minWidth="220"></e-column>
          <e-column field="units" headerText="Units" width="100" textAlign="Right" type="number"></e-column>
          <e-column field="unitPrice" headerText="Unit Price($)" format="c2" width="100" textAlign="Right" type="number"></e-column>
          <e-column field="price" headerText="Price($)" width="140" format="c" textAlign="Right"></e-column>
        </e-columns>
        <e-aggregates>
          <e-aggregate :showChildSummary="false">
            <e-columns>
              <e-column columnName="category" type="Custom" :customAggregate="customAggregateFn" :footerTemplate="footerTemplate"></e-column>
            </e-columns>
          </e-aggregate>
        </e-aggregates>
      </ejs-treegrid>
    </div>
  </div>
</template>

<script setup>
import { ref, h } from "vue";
import { TreeGridComponent, ColumnDirective, ColumnsDirective, AggregateDirective, AggregatesDirective, Aggregate, Page, Toolbar, ExcelExport } from "@syncfusion/ej2-vue-treegrid";
import { DropDownList } from "@syncfusion/ej2-dropdowns";
import { summaryData } from "./datasource";
import { getObject, isNullOrUndefined } from '@syncfusion/ej2-base';

// Reactive state
const data = ref(summaryData);
const selectedCategory = ref('Seafood');
const toolbarOptions = ref(['ExcelExport']);
const treegrid = ref(null);
let listObj = null;

const foods = [
  { food: 'Seafood' },
  { food: 'Dairy' },
  { food: 'Edible' },
  { food: 'Crystal' }
];

// Footer template
const FooterTemplate = () => {
  return h('span', {}, [
    'Count of ',
    h('input', { type: 'text', id: 'customers' }),
    `: ${selectedCategory.value}`
  ]);
};

const footerTemplate = FooterTemplate;

// Custom aggregate function
const customAggregateFn = (data) => {
  const sampleData = data.result ? getObject('result', data) : data;
  let countLength = 0;
  
  if (sampleData !== undefined) {
    sampleData.forEach((item) => {
      const category = getObject('category', item);
      if (category === selectedCategory.value) {
        countLength++;
      }
    });
  }
  return countLength;
};

// Data bound handler
const dataBound = () => {
  const treeGridObj = treegrid.value;
  if (!isNullOrUndefined(listObj)) {
    listObj.destroy();
  }
  listObj = new DropDownList({
    dataSource: foods,
    fields: { value: 'food' },
    placeholder: 'Select a Category',
    width: '110px',
    value: selectedCategory.value,
    change: () => {
      setTimeout(() => {
        if (listObj.value != null) {
          selectedCategory.value = listObj.value.toString();
          treeGridObj.refresh();
        }
      }, 300);
    }
  });
  listObj.appendTo('#customers');
};

// Toolbar click handler
const toolbarClick = (args) => {
  const treeGridObj = treegrid.value;
  if (args.item.text === 'Excel Export') {
    treeGridObj.excelExport();
  }
};

// Excel aggregate query cell info handler
const excelAggregateQueryCellInfo = (args) => {
  if (args.cell.column.headerText === "Category") {
    args.style.value = `Count of ${selectedCategory.value}: ${args.row.data.category.Custom}`;
  }
};

// Provide dependencies
provide('treegrid', [Aggregate, Page, Toolbar, ExcelExport]);
</script>
```
<template>
  <div class="col-lg-12 control-section">
    <div>
      <ejs-treegrid
        :dataSource="data"
        ref="treegrid"
        childMapping="subtasks"
        gridLines="Both"
        :treeColumnIndex="1"
        :allowExcelExport="true"
        :dataBound="dataBound"
        :toolbar="toolbarOptions"
        :toolbarClick="toolbarClick"
        :excelAggregateQueryCellInfo="excelAggregateQueryCellInfo"
        :height="380"
      >
        <e-columns>
          <e-column
            field="ID"
            headerText="Order ID"
            width="120"
            textAlign="Left"
          ></e-column>
          <e-column
            field="Name"
            headerText="Shipment Name"
            width="230"
            clipMode="EllipsisWithTooltip"
          ></e-column>
          <e-column
            field="shipmentDate"
            headerText="Shipment Date"
            width="150"
            type="date"
            format="yMd"
            textAlign="Right"
          ></e-column>
          <e-column
            field="category"
            headerText="Category"
            width="220"
            minWidth="220"
          ></e-column>
          <e-column
            field="units"
            headerText="Units"
            width="100"
            textAlign="Right"
            type="number"
          ></e-column>
          <e-column
            field="unitPrice"
            headerText="Unit Price($)"
            format="c2"
            width="100"
            textAlign="Right"
            type="number"
          ></e-column>
          <e-column
            field="price"
            headerText="Price($)"
            width="140"
            format="c"
            textAlign="Right"
          ></e-column>
        </e-columns>
        <e-aggregates>
          <e-aggregate :showChildSummary="false">
            <e-columns>
              <e-column
                columnName="category"
                type="Custom"
                :customAggregate="customAggregateFn"
                :footerTemplate="footerTemp"
              ></e-column>
            </e-columns>
          </e-aggregate>
        </e-aggregates>
      </ejs-treegrid>
    </div>
  </div>
</template>
<!-- custom code start -->
<style>
T .e-input {
  padding-bottom: 1px !important;
}
.e-treegrid .e-summarycell.e-templatecell {
  pointer-events: visible !important;
}
.fluent2 input#customers {
  padding-bottom: 8px !important;
}
.bootstrap5\.3 input#customers {
  padding-bottom: 7px !important;
}
.tailwind3 input#customers {
  padding-bottom: 1px !important;
}
.e-treegrid .e-summarycell.e-templatecell .e-input-group {
  padding-left: 6px !important;
}
</style>
<!-- custom code end -->
<script lang="ts">
import { createApp } from "vue";
import {
  TreeGridComponent,
  ColumnDirective,
  ColumnsDirective,
  AggregateDirective,
  AggregatesDirective,
  Aggregate,
  Page,
  Toolbar,
  ExcelExport,
} from "@syncfusion/ej2-vue-treegrid";
import { summaryData } from "./datasource";
import {
  DropDownList,
  DropDownListComponent,
} from "@syncfusion/ej2-vue-dropdowns";
import { getObject, CustomSummaryType } from "@syncfusion/ej2-grids";
import { isNullOrUndefined } from "@syncfusion/ej2-base";

let listObj: DropDownList;
let selectedCategory: string = "Seafood";
let foods = [
  { food: "Seafood" },
  { food: "Dairy" },
  { food: "Edible" },
  { food: "Crystal" },
];

export default {
  components: {
    "ejs-treegrid": TreeGridComponent,
    "e-column": ColumnDirective,
    "e-columns": ColumnsDirective,
    "e-aggregates": AggregatesDirective,
    "e-aggregate": AggregateDirective,
    "ejs-dropdownlist": DropDownListComponent,
  },
  data: () => {
    return {
      data: summaryData,
      selectedCategory: "Seafood",
      toolbarOptions: ["ExcelExport"],
      footerTemp: function () {
        return {
          template: createApp({}).component("footerTemplate", {
            template: `<span>Count of <input type="text" id="customers" /> : </span>`,
            data() {
              return { data: {} };
            },
          }),
        };
      },
    };
  },
  provide: {
    treegrid: [Aggregate, Page, Toolbar, ExcelExport],
  },
  methods: {
    //Custom aggregate function to calculate the count of items for the selected category.
    customAggregateFn: function (data: any) {
      (this as any).$refs.treegrid.ej2Instances.grid.vueInstance = null;
      let sampleData: any = data.result ? getObject("result", data) : data;

      let countLength: number;
      countLength = 0;
      if (sampleData !== undefined) {
        sampleData.filter((item: Object) => {
          let data: string = getObject("category", item);
          if (data === selectedCategory) {
            countLength++;
          }
        });
      }
      return countLength;
    },

    //Initializes a DropDownList in the footer for category selection.
    dataBound: function () {
      let treeGridObj = (this as any).$refs.treegrid;
      if (!isNullOrUndefined(listObj)) {
        listObj.destroy();
      }
      listObj = new DropDownList({
        dataSource: foods,
        fields: { value: "food" },
        placeholder: "Select a Category",
        width: "110px",
        value: selectedCategory,
        change: function () {
          setTimeout(function () {
            if (listObj.value != null) {
              selectedCategory = listObj.value.toString();
              treeGridObj.refresh();
            }
          }, 300);
        },
      });
      listObj.appendTo("#customers");
    },
    toolbarClick: function (args: any) {
      let treeGridObj = (this as any).$refs.treegrid;
      if (args["item"].text === "Excel Export") {
        treeGridObj.excelExport();
      }
    },

    //Handles the 'excelAggregateQueryCellInfo' event to customize aggregate cells during Excel export.
    excelAggregateQueryCellInfo(args: any) {
      if (args.cell.column.headerText === "Category") {
        args.style.value = `Count of ${selectedCategory}: ${args.row.data.category.Custom}`;
      }
    },
  },
};
</script>