[JavaScript知识库]exceljs导出excel表以及样式修改（react导出）

下载依赖：
npm install exceljs
npm install file-saver

exceljs：
import Excel from ‘exceljs’;
import FileSaver from ‘file-saver’;
import dayjs from ‘dayjs’;

const getCellWidth = (value: any) => {
// 判断是否为null或undefined
if (!value) {
return 15;
} else if (/.[\u4e00-\u9fa5]+.$/.test(value)) {
// 判断是否包含中文
return value.toString().length * 2.1 + 2 > 15 ? value.toString().length * 2.1 + 2 : 15;
} else {
return value.toString().length * 1.1 + 2 > 15 ? value.toString().length * 1.1 + 2 : 15;
}
};

function exportExcel(heard: any, data: any, name: any) {
const EXCEL_TYPE = ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8’;
// 创建工作簿
let workbook = new Excel.Workbook();
// 设置工作簿的属性
// 工作簿视图，工作簿视图控制在查看工作簿时 Excel 将打开多少个单独的窗口
workbook.views = [
{
x: 0,
y: 0,
width: 1000,
height: 2000,
firstSheet: 0,
activeTab: 1,
visibility: ‘visible’,
},
];
let worksheet = workbook.addWorksheet(name, { properties: { tabColor: { argb: ‘FFFFFF’ } } });
// 将数据源添加到sheet页中

worksheet.addRows(data);

const listHearder = Object.keys(heard);
// 插入表格数据
data.forEach((item: any, index: any) => {
worksheet.insertRow(
index,
listHearder.map((ite) => item[ite]),
);
});
const rows: any[] = [];
data.forEach((item: any, index: any) => {
rows.push(
listHearder.map((ite, num) => {
worksheet.columns[num].width = getCellWidth(item[ite]);
worksheet.columns[num].alignment = { vertical: ‘middle’, horizontal: ‘center’ };
return item[ite];
}),
);
});

// 设置默认单元格样式
worksheet.getRow(1).height = 16;
worksheet.getRow(1).font = { name: ‘宋体’, size: 13, bold: true };
worksheet.getRow(1).fill = {
type: ‘pattern’,
pattern: ‘solid’,
fgColor: { rgb: ‘fff’ },
};

worksheet.addTable({
name: ‘MyTable’,
ref: ‘A1’,
// bold: true,
headerRow: true,
totalsRow: false,
style: {
showRowStripes: false,
},
columns: listHearder.map((item) => {
return { name: heard[item] };
}),
rows,
});
// 给表格添加边框
const renderTabBorder = (index: any, ind: any, obj: any) => {
worksheet.getCell(${String.fromCharCode(65 + index)}${ind + 1}).border = {
…obj,
top: { style: ‘thin’ },
right: { style: ‘thin’ },
};
};

const len = rows.length + 1;
for (let ind = 0; ind < len; ind++) {
listHearder.forEach((item, index) => {
if (index === 0 && ind !== rows.length) {
renderTabBorder(index, ind, {
left: { style: ‘thin’ },
});
} else if (ind === rows.length && index !== 0) {
renderTabBorder(index, ind, {
bottom: { style: ‘thin’ },
});
} else {
renderTabBorder(index, ind, {
left: { style: ‘thin’ },
bottom: { style: ‘thin’ },
});
}
});
}
// 导出表格数据
workbook.xlsx.writeBuffer().then((data) => {
const blob = new Blob([data], { type: EXCEL_TYPE });
console.log(blob.stream(), 6664744);
FileSaver.saveAs(blob, name + dayjs().format(‘YYYY-MM-DD’) + ‘.xlsx’);
});
}

export default exportExcel;

使用：
import exportExcel from ‘@/models/excel’;
const heard = {
position: ‘阵地’,
…
};
exportExcel(heard, res.result.records, ‘表格名称’);