1. Project Settings
First, execute the following command in the console to install the xlsx component;
npm install xlsx --save or yarn add xlsx --save
Then, introduce the xls component in the vue project;
2. Prepare data
To export to Excel, you need to have data. You can use local data or get data from the API. In this example, we will use local data:
exportData: [
{ name: "John", age: 30, city: "New York" },
{ name: "Alice", age: 25, city: "Los Angeles" },
{ name: "Bob", age: 35, city: "Chicago" }
]
3. Implement the export function
Create a method to export to Excel. This method will trigger the generation and download of Excel files:
exportToExcel() {
const worksheet = .json_to_sheet();
const workbook = .book_new();
.book_append_sheet(workbook, worksheet, "Sheet1");
(workbook, "");
}This method uses the xlsx component to convert the data into an Excel worksheet, then create a workbook and add the worksheet to the workbook. Finally, it usesMethod saves the workbook as an Excel file named "".
4. Set column width adaptive
According to the length of the exported data, calculate the maximum length of each column by traversing, and then set the width of each column by the worksheet['!cols'] method;
// Set column width ((row, rowIndex) => {
((cell, cellIndex) => {
const list = (item => {
const val = item[cellIndex] as string;
if(isEmptyString(val)){
return 1;
} else if(().charCodeAt(0) > 255){ // Determine whether there is Chinese return ().length * 2
} else{
return ().length;
}
});
const maxLength = (...list);
const width = maxLength * 1.1; //Automatically adjust column width according to actual content length if(!worksheet["!cols"]) worksheet["!cols"] = [];
if(!worksheet["!cols"][cellIndex]) worksheet["!cols"][cellIndex] = {wch: 8};
worksheet['!cols'][cellIndex].wch = width; // Use worksheet's '!cols' to set the column width });
}); 5. Set up merged cells
The merged cells can be set through the worksheet["!merges"] method;
// Merge cells ((item) => {
if(!worksheet["!merges"]){
worksheet["!merges"] = [];
};
worksheet["!merges"].push(item);
// worksheet["!merges"].push({
// s: { r: 2, c: 1 }, // s ("start"): c = 1 r = 2 -> "B3"
// e: { r: 3, c: 4 } // e ("end"): c = 4 r = 3 -> "E4"
// });
});6. Set the style of exporting Excel tables
The xlsx component library does not support setting Excel table styles, but the style setting can be achieved by introducing the xlsx-style-vite component library; then export the file through file-saver;
6.1 Install and introduce xlsx-style-vite and file-saver components
yarn add xlsx-style-vite --save yarn add file-saver --save
import XLSXStyle from 'xlsx-style-vite'; import XLSX_SAVE from 'file-saver';
Among them, the xlsx-style-vite component is the vite version of the xlsx-style component, which is used to solve the problem of introducing xlsx-style exceptions under vite;
6.2 Set the cell to center
Iterate through all cells and then style each cell through the worksheet[column].s method;
((row, rowIndex) => {
((cell, cellIndex) => {
// Set all cells to center let column = utils.encode_cell({c: cellIndex, r: rowIndex});
worksheet[column].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: false, // Automatic line wrap },
}
});
}); 6.3 Set cell background color and font
((row, rowIndex) => {
((cell, cellIndex) => {
// Set all cells to center let column = utils.encode_cell({c: cellIndex, r: rowIndex});
if(worksheet[column]){
// Set background color and bold display worksheet[column].s = {
font: {
name: "Microsoft Yahei",
sz: 16,
color: { rgb: "000000" },
bold: true,
italic: false,
underline: false,
},
fill: {
fgColor: { rgb: "C5D9F1" },
},
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: false, // Automatic line wrap },
}
}
}
});
}); 6.4 Set cell borders
//Outside box line of cell const borderAll = {
top: {
style: "thin",
},
bottom: {
style: "thin",
},
left: {
style: "thin",
},
right: {
style: "thin",
},
};
// Set cell borders ((row, rowIndex) => {
((cell, cellIndex) => {
let column = utils.encode_cell({c: cellIndex, r: rowIndex});
if(worksheet[column]){
worksheet[column].s = {
border: borderAll,
}
}
}
});
}); 6.5 Export files
Excel files must be exported through file-saver component, and files cannot be exported through the writeFile method of xlsx component, otherwise the style will not take effect;
const wbout = (workbook, {
type: 'binary',
bookType: 'xlsx',
});
XLSX_SAVE.saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream',
}),
'',
);
// Data conversionfunction s2ab(s) {
var buf = new ArrayBuffer();
var view = new Uint8Array(buf);
for (var i=0; i!=; ++i) view[i] = (i) & 0xFF;
return buf;
}7. Complete code example
import * as xlsx from 'xlsx';
import type { WorkBook } from 'xlsx';
import type { JsonToSheet, AoAToSheet } from './typing';
import XLSXStyle from 'xlsx-style-vite';
import XLSX_SAVE from 'file-saver';
import { isEmptyString } from '@/utils/table';
const { utils, writeFile } = xlsx;
const DEF_FILE_NAME = '';
export function aoaToSheetXlsx<T = any>({
data,
header,
filename = DEF_FILE_NAME,
write2excelOpts = { bookType: 'xlsx' },
merges = [],
}: AoAToSheet<T>) {
const arrData = [...data];
if (header) {
(header);
}
const worksheet = utils.aoa_to_sheet(arrData);
/* add worksheet to workbook */
const workbook: WorkBook = {
SheetNames: [filename],
Sheets: {
[filename]: worksheet,
},
};
//Outside box line of cell const borderAll = {
top: {
style: "thin",
},
bottom: {
style: "thin",
},
left: {
style: "thin",
},
right: {
style: "thin",
},
};
// Set column width ((row, rowIndex) => {
((cell, cellIndex) => {
const list = (item => {
const val = item[cellIndex] as string;
if(isEmptyString(val)){
return 1;
} else if(().charCodeAt(0) > 255){ // Determine whether there is Chinese return ().length * 2
} else{
return ().length;
}
});
const maxLength = (...list);
const width = maxLength * 1.1; //Automatically adjust column width according to actual content length if(!worksheet["!cols"]) worksheet["!cols"] = [];
if(!worksheet["!cols"][cellIndex]) worksheet["!cols"][cellIndex] = {wch: 8};
worksheet['!cols'][cellIndex].wch = width; // Use worksheet's '!cols' to set the column width
// Set all cells to center let column = utils.encode_cell({c: cellIndex, r: rowIndex});
if(worksheet[column]){
if(rowIndex === 0) { // Set background color and bold display in the title line worksheet[column].s = {
border: borderAll,
font: {
// name: "Microsoft Yahei", // sz: 16,
color: { rgb: "000000" },
bold: true,
italic: false,
underline: false,
},
fill: {
fgColor: { rgb: "C5D9F1" },
},
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: false, // Automatic line wrap },
}
} else {
worksheet[column].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: false, // Automatic line wrap },
}
}
}
});
});
// Merge cells ((item) => {
if(!worksheet["!merges"]){
worksheet["!merges"] = [];
};
worksheet["!merges"].push(item);
// worksheet["!merges"].push({
// s: { r: 2, c: 1 }, // s ("start"): c = 1 r = 2 -> "B3"
// e: { r: 3, c: 4 } // e ("end"): c = 4 r = 3 -> "E4"
// });
});
const wbout = (workbook, {
type: 'binary',
bookType: 'xlsx',
});
XLSX_SAVE.saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream',
}),
`${filename}.${}`,
);
/* output format determined by filename */
// writeFile(workbook, filename, write2excelOpts);
/* at this point, will have been downloaded */
}
// Data conversionfunction s2ab(s) {
var buf = new ArrayBuffer();
var view = new Uint8Array(buf);
for (var i=0; i!=; ++i) view[i] = (i) & 0xFF;
return buf;
}The above is a detailed explanation of the steps to implement Excel export function using xlsx components in Vue. For more information about Vue xlsx components to implement Excel export, please pay attention to my other related articles!