【exceljs】导出excel文件，比如设置列样式、单元格样式、数据校验、添加图片 |
您所在的位置：网站首页 › vba窗体输出表格样式 › 【exceljs】导出excel文件，比如设置列样式、单元格样式、数据校验、添加图片 |
【exceljs】导出excel文件，比如设置列样式、单元格样式、数据校验、添加图片
|
步骤
安装依赖
npm i [email protected]
npm i [email protected]
基本案例
点击导出excel
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
exportExcel() {
const excel_name = "test.xlsx";
// 创建工作簿
const wb = new ExcelJS.Workbook();
// 添加工作表
const ws = wb.addWorksheet("sheet1");
// 设置表格内容
const _titleCell = ws.getCell("A1");
_titleCell.value = "Hello ExcelJS!";
// 导出表格
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
设置表头，添加数据
点击导出excel
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
// 表头与key之间的关系
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 20,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 30,
},
{
header: "电话号码",
key: "phone_number",
width: 50,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
ws.addRows(this.tableData);
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
列样式设置+筛选+渐变
点击导出excel
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
// 方式1：单独为每个表头column设置
// ws.columns = [
// {
// header: "员工ID",
// key: "employee_id",
// },
// {
// header: "姓名-呀呀呀",
// key: "first_name",
// width: 30,
// style: {
// alignment: {
// horizontal: "center",
// vertical: "center",
// },
// },
// },
// {
// header: "电话号码",
// key: "phone_number",
// },
// {
// header: "雇佣日期",
// key: "hire_date",
// },
// ];
// ws.addRows(this.tableData);
// 方式2：为某个单元格设置
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 10,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 20,
},
{
header: "电话号码",
key: "phone_number",
width: 20,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
const row1 = ws.getRow(1);
row1.height = 30;
// 设置字体样式
row1.font = {
name: "黑体",
bold: true,
size: 14,
color: {
// 注意：在 exceljs 中所有的的颜色值均为 argb 格式，且不带 # 符号
argb: "ff0000",
},
};
// 设置对齐方式（水平垂直）
row1.alignment = {
vertical: "middle",
horizontal: "center",
};
// 设置单元格填充的样式
row1.fill = {
type: "pattern",
pattern: "solid",
fgColor: {
argb: "FFF5F7FA",
},
};
// 设置变框
row1.border = {
left: {
style: "dotted",
color: "#d81ef7",
},
};
// 设置筛选
// 方式1：设置某个单元格
// ws.autoFilter = "A1";
// 方式2：设置从哪个单元格到哪个单元格
// ws.autoFilter = {
// from: {
// row: 1,
// column: 1,
// },
// to: {
// row: 1,
// column: 3,
// },
// };
ws.addRows(this.tableData);
// 渐变
ws.getCell("C4").fill = {
type: "gradient",
gradient: "angle",
degree: 0,
stops: [
{ position: 0, color: { argb: "FF0000FF" } },
{ position: 0.5, color: { argb: "FFFFFFFF" } },
{ position: 1, color: { argb: "FF0000FF" } },
],
};
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
单元格合并
点击导出excel
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 10,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 20,
},
{
header: "电话号码",
key: "phone_number",
width: 20,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
// 注意：要在合并单元格前将数据填充进去
ws.addRows(this.tableData);
// 行与行之间的合并
ws.mergeCells("A2:A3");
ws.getCell("A2").alignment = {
vertical: "middle",
horizontal: "center",
};
ws.getCell("A2").font = {
bold: true,
};
// 按左上，右下合并
// ws.mergeCells("C5:D6");
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
公式值
比如：统计总和，平均值等 点击导出excel import ExcelJS from "exceljs"; import FileSaver from "file-saver"; export default { methods: { exportExcel() { const excel_name = "test.xlsx"; const wb = new ExcelJS.Workbook({}); const ws = wb.addWorksheet("sheet1"); ws.columns = [ { header: "学生ID", key: "stu_id", width: 10, }, { header: "语文", key: "chinese", width: 20, }, { header: "数学", key: "math", width: 20, }, { header: "英语", key: "english", width: 20, }, ]; const data = [ { stu_id: 100, chinese: 85, math: 99, english: 76, }, { stu_id: 101, chinese: 70, math: 90, english: 80, }, { stu_id: 102, chinese: 99, math: 99, english: 97, }, ]; ws.addRows(data); // 注意：ExcelJS 无法处理公式以生成结果，必须提供该公式。 // formula 只是设置公式 // ws.getCell("B5").value = { // formula: "SUM(B2,B4)", // }; // 方式1：自己去计算，将结果放到result里 // ws.getCell("B5").value = { // formula: "SUM(B2,B4)", // result: // ws.getCell("B2").value + // ws.getCell("B3").value + // ws.getCell("B4").value, // }; // 方式2:设置在初始加载时让excel重新计算所有结果。 // 缺点：打开文件后文件不是保存状态的了 // 参考文档：https://github.com/exceljs/exceljs/issues/431 wb.calcProperties.fullCalcOnLoad = true; ws.getCell("C5").value = { formula: "=AVERAGE(C2:C4)", }; wb.xlsx.writeBuffer().then((buffer) => { let file = new Blob([buffer], { type: "application/octet-stream", }); FileSaver.saveAs(file, excel_name); }); }, }, }; 数据验证 点击导出excel import ExcelJS from "exceljs"; import FileSaver from "file-saver"; export default { methods: { exportExcel() { const excel_name = "test.xlsx"; const wb = new ExcelJS.Workbook({}); const ws = wb.addWorksheet("sheet1"); ws.columns = [ { header: "学生ID", key: "stu_id", width: 10, }, { header: "语文", key: "chinese", width: 20, }, { header: "数学", key: "math", width: 20, }, { header: "英语", key: "english", width: 20, }, ]; const data = [ { stu_id: 100, chinese: 85, math: 99, english: 76, }, { stu_id: 101, chinese: 70, math: 90, english: 80, }, { stu_id: 102, chinese: 99, math: 99, english: 97, }, ]; ws.addRows(data); // 设置数据校验规则 ws.getCell("E1").dataValidation = { type: "list", // 注意格式 formulae: ['"值1,值2,值3"'], }; wb.xlsx.writeBuffer().then((buffer) => { let file = new Blob([buffer], { type: "application/octet-stream", }); FileSaver.saveAs(file, excel_name); }); }, }, }; 条件格式比如：根据不同的条件对单元格的样式进行调整 点击导出excel import ExcelJS from "exceljs"; import FileSaver from "file-saver"; export default { methods: { exportExcel() { const excel_name = "test.xlsx"; const wb = new ExcelJS.Workbook({}); const ws = wb.addWorksheet("sheet1"); ws.columns = [ { header: "学生ID", key: "stu_id", width: 10, }, { header: "语文", key: "chinese", width: 20, }, { header: "数学", key: "math", width: 20, }, { header: "英语", key: "english", width: 20, }, ]; const data = [ { stu_id: 100, chinese: 85, math: 99, english: 76, }, { stu_id: 101, chinese: 70, math: 90, english: 80, }, { stu_id: 102, chinese: 99, math: 99, english: 97, }, { stu_id: 103, chinese: 80, math: 80, english: 80, }, ]; ws.addRows(data); // 需求：对语文小于80分显示粉色 ws.addConditionalFormatting({ ref: "B2:B4", rules: [ { type: "cellIs", operator: "lessThan", priority: 1, formulae: [80], style: { fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FF0000" }, }, }, }, ], }); wb.xlsx.writeBuffer().then((buffer) => { let file = new Blob([buffer], { type: "application/octet-stream", }); FileSaver.saveAs(file, excel_name); }); }, }, }; 将图片添加到工作表 点击导出excel import ExcelJS from "exceljs"; import FileSaver from "file-saver"; export default { methods: { getBase64Image(url) { const img = new Image(); //因为是网络资源所以会有图片跨域问题产生，此属性可以解决跨域问题，下文详解 img.setAttribute("crossOrigin", "anonymous"); //如果需要兼容ios，这两个顺序一定不能换，先设置crossOrigin后设置src img.src = url; return new Promise((resolve, reject) => { img.onload = () => { //canvas基本配置 const canvas = document.createElement("canvas"); canvas.width = img.width; canvas.height = img.height; const ctx = canvas.getContext("2d"); ctx.drawImage(img, 0, 0, canvas.width, canvas.height); resolve({ success: true, //canvas.toDataURL的方法将图片的绝对路径转换为base64编码 base64: canvas.toDataURL(), }); }; img.onerror = () => { reject({ success: false }); }; }); }, exportExcel() { const excel_name = "test.xlsx"; const wb = new ExcelJS.Workbook({}); const ws = wb.addWorksheet("sheet1"); ws.columns = [ { header: "学生ID", key: "stu_id", width: 10, }, { header: "语文", key: "chinese", width: 20, }, { header: "数学", key: "math", width: 20, }, { header: "英语", key: "english", width: 20, }, ]; const data = [ { stu_id: 100, chinese: 85, math: 99, english: 76, }, { stu_id: 101, chinese: 70, math: 90, english: 80, }, { stu_id: 102, chinese: 99, math: 99, english: 97, }, { stu_id: 103, chinese: 80, math: 80, english: 80, }, ]; ws.addRows(data); // 需求：往某个单元格设置图片 // 方式1：通过base64编码方式 // const imgPath = require("./assets/1.png"); // const { base64 } = await this.getBase64Image(imgPath); // const imageId1 = wb.addImage({ // base64, // extension: "png", // }); // // 注意：参数2要求的格式 单元格x:单元格y // ws.addImage(imageId1, "E2:E2"); // wb.xlsx.writeBuffer().then((buffer) => { // let file = new Blob([buffer], { // type: "application/octet-stream", // }); // FileSaver.saveAs(file, excel_name); // }); // 设置行高 const row6 = ws.getRow(6); row6.height = 50; // 方式2：通过网络请求资源得到buffer // 参考文档：https://github.com/exceljs/exceljs/issues/1216 const filePath = require("./assets/1.png"); fetch(filePath).then((res) => { const imageId2 = wb.addImage({ buffer: res.arrayBuffer(), extension: "png", }); // ws.addImage(imageId2, "B6:D10"); // 将图片设置在 第6行第2列的位置； +0.5 即在单元格在偏移 （值要在[0,1)之间） ws.addImage(imageId2, { tl: { col: 2 - 1 + 0.8, row: 6 - 1 + 0.5, }, ext: { width: 30, height: 30, }, }); wb.xlsx.writeBuffer().then((buffer) => { let file = new Blob([buffer], { type: "application/octet-stream", }); FileSaver.saveAs(file, excel_name); }); }); }, }, };效果图：
|
【本文地址】
今日新闻 |
推荐新闻 |