How to Use the Excel Export Mechanism with Export2Excel.js and Map Custom Columns in Vue-Element-Admin

Vue-Element-Admin provides a client-side Excel export utility in src/vendor/Export2Excel.js that wraps SheetJS and File-Saver, allowing you to map custom columns by defining header titles and corresponding data keys before generating the workbook.

The Excel export mechanism in PanJiaChen/vue-element-admin enables browser-based spreadsheet generation without server-side processing. By leveraging the Export2Excel.js vendor script, developers can transform JSON datasets into formatted Excel files while controlling column order, headers, and styling through a simple mapping configuration.

Core Export Workflow in Export2Excel.js

The src/vendor/Export2Excel.js file implements a four-step pipeline that converts raw data into a downloadable Excel file. Understanding this workflow is essential for customizing the export behavior.

Building the Worksheet

The internal sheet_from_array_of_arrays function (lines 61-99) handles the conversion of a two-dimensional array into a SheetJS worksheet object. It iterates through the data matrix, determines cell types (dates, numbers, strings), and applies merges if specified. The auto-width calculation block (lines 76-105) analyzes content length to set optimal column widths when the autoWidth option is enabled.

Assembling the Workbook

A Workbook constructor (lines 103-107) creates the container object that stores the worksheet under a named sheet (defaulting to "SheetJS"). This object follows the SheetJS workbook structure required for serialization.

Serialization and Download

The XLSX.write method (lines 112-116) serializes the workbook into a binary string using the specified bookType (defaulting to xlsx). Finally, the binary string is converted to a Blob and passed to saveAs from File-Saver (lines 117-122), which triggers the browser's download dialog with the desired filename.

Mapping Custom Columns with export_json_to_excel

The export_json_to_excel function accepts an options object that separates display headers from data keys, enabling flexible column mapping without modifying the source dataset.

The Header and FilterVal Pattern

The standard implementation in src/views/excel/export-excel.vue demonstrates the mapping pattern:

const tHeader = ['Id', 'Title', 'Author', 'Readings', 'Date']
const filterVal = ['id', 'title', 'author', 'pageviews', 'display_time']
const data = this.formatJson(filterVal, list)

excel.export_json_to_excel({
  header: tHeader,
  data: data,
  filename: this.filename,
  autoWidth: this.autoWidth,
  bookType: this.bookType
})

• tHeader defines the visible column titles in the Excel file.
• filterVal specifies the object keys to extract from each raw record.
• formatJson maps the raw objects into ordered arrays matching the tHeader sequence.

To customize columns, modify the tHeader and filterVal arrays. Removing a key from filterVal and its corresponding title from tHeader excludes that column from the export. Reordering the arrays changes the column sequence in the generated spreadsheet.

Practical Implementation Examples

Basic Vue Component Usage

The following example from src/views/excel/export-excel.vue shows a complete implementation with dynamic loading:

<template>
  <el-button @click="download">Export Users</el-button>
</template>

<script>
import { fetchUsers } from '@/api/user'

export default {
  methods: {
    async download() {
      const { data } = await fetchUsers()
      const header = ['User ID', 'Name', 'Email', 'Created']
      const keys = ['id', 'name', 'email', 'created_at']
      
      const rows = data.map(item => keys.map(k => {
        return k === 'created_at' 
          ? new Date(item[k]).toLocaleDateString() 
          : item[k]
      }))
      
      import('@/vendor/Export2Excel').then(excel => {
        excel.export_json_to_excel({
          header,
          data: rows,
          filename: 'users',
          autoWidth: true,
          bookType: 'xlsx'
        })
      })
    }
  }
}
</script>

This pattern allows you to transform data types during mapping, such as formatting dates or concatenating fields, before passing the array to the exporter.

Multi-Row Headers and Merges

For complex reports requiring grouped headers, use the multiHeader and merges options as demonstrated in src/views/excel/merge-header.vue:

excel.export_json_to_excel({
  multiHeader: [['User Report', '', '', '']],  // First row spans all columns
  header: ['ID', 'Name', 'Role', 'Department'],
  data: rows,
  merges: ['A1:D1'],                           // Merge A1 through D1
  filename: 'user-report',
  autoWidth: true
})

The multiHeader array creates additional rows above the main header, while merges specifies cell ranges using Excel notation (e.g., A1:D1). The sheet_from_array_of_arrays function processes these merges during worksheet construction.

Key Source Files and References

File	Purpose	Location
Export2Excel.js	Core utility implementing export_json_to_excel and export_table_to_excel using SheetJS and File-Saver.	src/vendor/Export2Excel.js
export-excel.vue	Reference implementation showing basic JSON-to-Excel export with custom column mapping via tHeader and filterVal.	src/views/excel/export-excel.vue
merge-header.vue	Advanced example demonstrating multi-row headers and cell merges.	src/views/excel/merge-header.vue
complex-table.vue	Integration example with complex tables and dynamic column selection.	src/views/table/complex-table.vue

Summary

• Vue-Element-Admin provides a client-side Excel export mechanism through src/vendor/Export2Excel.js, which wraps SheetJS and File-Saver to generate binary Excel files without server interaction.
• The export_json_to_excel function accepts a header array for display titles and a data array of ordered rows, enabling precise control over column presentation.
• Custom column mapping is achieved by defining filterVal (object keys) and tHeader (display names), then transforming raw objects into ordered arrays using a formatJson helper before export.
• Advanced features include multi-row headers via multiHeader, cell merges via merges, and automatic column width calculation via the autoWidth option.

Frequently Asked Questions

What is Export2Excel.js in vue-element-admin?

Export2Excel.js is a vendor utility located at src/vendor/Export2Excel.js that provides a thin wrapper around the SheetJS (XLSX) library and File-Saver. It exposes two main functions—export_table_to_excel for DOM table extraction and export_json_to_excel for JSON data conversion—enabling client-side Excel generation without requiring backend endpoints.

How do I map custom columns when exporting Excel?

To map custom columns, define two parallel arrays: tHeader for the visible column titles and filterVal for the corresponding object keys in your dataset. Use a helper function like formatJson to iterate over your raw data and extract values in the order specified by filterVal, producing a 2D array that aligns with your headers. Pass this transformed data to export_json_to_excel.

Can I export merged headers using Export2Excel.js?

Yes, the export_json_to_excel function supports multi-row headers through the multiHeader option, which accepts an array of header rows. To merge cells across columns (such as making a title span multiple columns), provide a merges array using Excel range notation (e.g., ['A1:D1']). The internal sheet_from_array_of_arrays function processes these merges during worksheet construction.

Is the Excel export in vue-element-admin server-side or client-side?

The export is entirely client-side. The Export2Excel.js utility runs in the browser, using SheetJS to serialize data into Excel binary format and File-Saver to trigger the download dialog. This approach eliminates server load and network latency for data transfers, though it is best suited for datasets that fit within browser memory constraints.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}