Exporting data to Excel format is a common requirement in modern web applications. Vue.js is a popular JavaScript framework that allows us to build dynamic front-end applications. This article will introduce how to use Vue.js and xlsx components to easily implement the Excel data export function.
1. Project settings
First, execute the following command on the console to install the xlsx component;
npm install xlsx --save or yarn add xlsx --save
Then, introduce the xls component into the vue project;
import XLSX from 'xlsx';
2. Prepare data
To export to Excel, you need to have the data. You can use local data or get data from 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 export function
Create a method to export to Excel. This method will trigger the generation and download of the Excel file:
exportToExcel() {
const worksheet = XLSX.utils.json_to_sheet(this.exportData);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
XLSX.writeFile(workbook, "exported-data.xlsx");
}
This method uses the xlsx component to convert the data into an Excel worksheet, then creates a workbook and adds the worksheet to the workbook. Finally, it uses the XLSX.writeFile method to save the workbook as an Excel file named “exported-data.xlsx”.
4. Set column width to be 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 through the worksheet[‘!cols’] method;
//Set column width
exportData.forEach((row, rowIndex) => {
row.forEach((cell, cellIndex) => {
const list = arrData.map(item => {
const val = item[cellIndex] as string;
if(isEmptyString(val)){
return 1;
} else if(val.toString().charCodeAt(0) > 255){ // Determine whether there is Chinese
return val.toString().length * 2
} else{
return val.toString().length;
}
});
const maxLength = Math.max(...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 column width
});
});
5. Set merged cells
Merged cells can be set through the worksheet[“!merges”] method;
//Merge cells
merges.forEach((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 exported Excel table
The xlsx component library does not support setting Excel table styles, but you can set the style 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';
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
Traverse all cells, and then set the style of each cell through the worksheet[column].s method;
exportData.forEach((row, rowIndex) => {
row.forEach((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 wrapping
},
}
});
});
6.3 Set cell background color and font
exportData.forEach((row, rowIndex) => {
row.forEach((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 wrapping
},
}
}
}
});
});
6.4 Set cell borders
//Cell outer border
const borderAll = {
top: {
style: "thin",
},
bottom: {
style: "thin",
},
left: {
style: "thin",
},
right: {
style: "thin",
},
};
//Set cell borders
arrData.forEach((row, rowIndex) => {
row.forEach((cell, cellIndex) => {
let column = utils.encode_cell({c: cellIndex, r: rowIndex});
if(worksheet[column]){
worksheet[column].s = {
border: borderAll,
}
}
}
});
});
6.5 Export file
The Excel file must be exported through the file-saver component, and the file cannot be exported through the writeFile method of the xlsx component, otherwise the style will not take effect;
const wbout = XLSXStyle.write(workbook, {
type: 'binary',
bookType: 'xlsx',
});
XLSX_SAVE.saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream',
}),
'exported-data.xlsx',
);
// data conversion
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; + + i) view[i] = s.charCodeAt(i) & amp; 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 = 'excel-list.xlsx';
export function aoaToSheetXlsx<T = any>({
data,
header,
filename = DEF_FILE_NAME,
write2excelOpts = { bookType: 'xlsx' },
merges = [],
}: AoAToSheet<T>) {
const arrData = [...data];
if (header) {
arrData.unshift(header);
}
const worksheet = utils.aoa_to_sheet(arrData);
/* add worksheet to workbook */
const workbook: WorkBook = {
SheetNames: [filename],
Sheets: {
[filename]: worksheet,
},
};
//Cell outer border
const borderAll = {
top: {
style: "thin",
},
bottom: {
style: "thin",
},
left: {
style: "thin",
},
right: {
style: "thin",
},
};
//Set column width
arrData.forEach((row, rowIndex) => {
row.forEach((cell, cellIndex) => {
const list = arrData.map(item => {
const val = item[cellIndex] as string;
if(isEmptyString(val)){
return 1;
} else if(val.toString().charCodeAt(0) > 255){ // Determine whether there is Chinese
return val.toString().length * 2
} else{
return val.toString().length;
}
});
const maxLength = Math.max(...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 column width
// Set all cells to center
let column = utils.encode_cell({c: cellIndex, r: rowIndex});
if(worksheet[column]){
if(rowIndex === 0) { //Set the background color and bold display of the title row
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 wrapping
},
}
} else {
worksheet[column].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: false, // Automatic line wrapping
},
}
}
}
});
});
// Merge Cells
merges.forEach((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 = XLSXStyle.write(workbook, {
type: 'binary',
bookType: 'xlsx',
});
XLSX_SAVE.saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream',
}),
`${filename}.${write2excelOpts.bookType}`,
);
/* output format determined by filename */
// writeFile(workbook, filename, write2excelOpts);
/* at this point, out.xlsb will have been downloaded */
}
// data conversion
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; + + i) view[i] = s.charCodeAt(i) & amp; 0xFF;
return buf;
}
New Era Migrant Workers
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Vue entry skill treevue3 basics (JS)Vue3 current situation 39462 people are learning the system