react+excel.js创建EXCEL表格

最新推荐文章于 2024-08-19 09:58:38 发布

墨·殇

最新推荐文章于 2024-08-19 09:58:38 发布

阅读量501

点赞数

文章标签： excel javascript react.js

本文链接：https://blog.csdn.net/freedms/article/details/128979902

版权

这段代码展示了如何使用JavaScript库ExcelJS创建和填充工作簿，然后导出为Excel文件。它涉及到数据的组织，如线网、线路和车站的客运量信息，以及数据的格式化和居中对齐。此外，还包括了创建图例和设定页面布局的细节。

摘要由CSDN通过智能技术生成

安装

npm install exceljs

引入

import Excel from "exceljs";

创建工作簿

const workbook = new ExcelJS.Workbook();

导出

const exportToExcel = (net,lines,s,netOptimize,linesOptimize,sOptimize) => {

let dataTableNet = []; //excel文件中的数据内容

for (let i in net) { //循环获取excel中每一行的数据
let obj = {
startTime:net[i].startTime,
inNetPassenger:net[i].volume,
outNetPassenger:netOptimize[i].volume,
}
dataTableNet.push(obj); //设置excel中每列所获取的数据源
}

let dataTableNetOptimize = [];

for (let i in netOptimize) {
let objx = {
inIncomingPassenger: net[i].inFlow,
outIncomingPassenger:netOptimize[i].inFlow,
}
dataTableNetOptimize.push(objx);
}

let dataTable = dataTableNet.map((item, index) => {
return {...item, ...dataTableNetOptimize[index]}
})

//线路
let dataTableLine1 = [];

for (let i in lines) {
let obj = {
inlinePassenger:lines[i].volume,
outlinePassenger:linesOptimize[i].volume,
}
dataTableLine1.push(obj);
}

let dataTableLineOptimize = [];
for (let i in linesOptimize) {
let obj = {
inIncomingPassengerLine: lines[i].inFlow,
outIncomingPassengerLine: linesOptimize[i].inFlow,
}
dataTableLineOptimize.push(obj);
}

let dataTableLine = dataTableLine1.map((item, index) => {
return {...item, ...dataTableLineOptimize[index]}
})

//车站
let dataTableStations=[]
for (let i in s) {
let obj = {
inStationPassenger: s[i].inFlow,
outStationPassenger: sOptimize[i].inFlow,
}
dataTableStations.push(obj);
}

let dataTableStationOptimize = [];
for (let i in sOptimize) {
let obj = {
inStationPassengerCome:s[i].outFlow,
outStationPassengerLeave:sOptimize[i].outFlow,
}
dataTableStationOptimize.push(obj);
}

let dataTableStation = dataTableStations.map((item, index) => {
return {...item, ...dataTableStationOptimize[index]}
})

let table=dataTable.map((item, index) => {
return {...item, ...dataTableLine[index],...dataTableStation[index]}
})

console.log(111,table)
let sheetName = "综合交通评估.xlsx";

let workbook = new ExcelJs.Workbook();

const worksheet2 = workbook.addWorksheet('算法说明', {
pageSetup:{paperSize: 9, orientation:'landscape'}
});

const worksheet = workbook.addWorksheet('综合交通评估', {
pageSetup:{paperSize: 9, orientation:'landscape'}
});

//图例表
worksheet2.properties.defaultRowHeight = 30;
worksheet2.properties.defaultColWidth=30
worksheet2.mergeCells('B6:B7')
worksheet2.getCell('B6').value = '图例';
worksheet2.getCell('C6').value = '内部算法';
worksheet2.getCell('C7').value = '外部算法';
worksheet2.getCell('D6').value = '226066';
worksheet2.getCell('D7').value = '259309';
worksheet2.getCell('B6').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('C6').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('D7').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('C7').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('D6').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('D7').alignment = { vertical: 'middle', horizontal: 'center' };
worksheet2.getCell('B6').font = {
size: 26,
bold:true,
};
worksheet2.getCell('C6').font = {
size: 26,
bold:true,
};
worksheet2.getCell('C7').font = {
size: 26,
bold:true,
};
worksheet2.getCell('D7').font = {
size: 26,
color: { argb: '00FA9A' },
italic: true
};
worksheet2.getCell('D6').font = {
size: 26,
};
worksheet2.views = [
{state: 'normal',zoomScale:120, activeCell: 'B6'}
];

worksheet.properties.defaultRowHeight = 20;
// 之后调整页面设置配置
worksheet.pageSetup.margins = {
left: 0.7, right: 0.7,
top: 0.75, bottom: 0.75,
header: 0.3, footer: 0.3
};
worksheet.columns = [
{ header: '时间', key: 'id', width: 50, },
{ header: '线网', key: 'net', width: 50, },
];
worksheet.getColumn(6).values = ['线路'];
worksheet.getColumn(10).values = ['车站'];
worksheet.getCell('B2').value = '线网客运量';
worksheet.getCell('D2').value = '线网进站客运量';
worksheet.getCell('F2').value = '线路客运量';
worksheet.getCell('H2').value = '线路进站客流量';
worksheet.getCell('J2').value = '车站进站客流量';
worksheet.getCell('L2').value = '车站出站客流量';
worksheet.mergeCells('A1:A2');
worksheet.mergeCells('B1:E1');
worksheet.mergeCells('F1:I1');
worksheet.mergeCells('J1:M1');
worksheet.mergeCells('B2:C2');
worksheet.mergeCells('D2:E2');
worksheet.mergeCells('F2:G2');
worksheet.mergeCells('H2:I2');
worksheet.mergeCells('J2:K2');
worksheet.mergeCells('L2:M2');

//插入数据
for (let i=3;i<table.length+3;i++){
worksheet.getCell('A'+i).value = table[i-3].startTime;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('B'+i).value = table[i-3].inNetPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('C'+i).value = table[i-3].outNetPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('D'+i).value = table[i-3].inIncomingPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('E'+i).value = table[i-3].outIncomingPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('F'+i).value = table[i-3].inlinePassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('G'+i).value = table[i-3].outlinePassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('H'+i).value = table[i-3].inIncomingPassengerLine;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('I'+i).value = table[i-3].outIncomingPassengerLine;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('J'+i).value = table[i-3].inStationPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('K'+i).value = table[i-3].outStationPassenger;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('L'+i).value = table[i-3].inStationPassengerCome;
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('M'+i).value = table[i-3].outStationPassengerLeave;
}

// for (let x1 of table)
// {
// let x2=Object.keys(x1);
// let temp=[]
// for(let y of x2)
// {
// temp.push(x1[y])
// }
// worksheet.addRow(temp)
// }

for (let i=3;i<table.length+3;i++){
worksheet.getCell('C'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('E'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('G'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('I'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('K'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}
for (let i=3;i<table.length+3;i++){
worksheet.getCell('M'+i).font = {
color: { argb: '00FA9A' },
italic: true
};
}

//数据居中
worksheet.columns.forEach((item, i) => {
worksheet.getColumn(i + 1).width = 10
worksheet.getColumn(i + 1).alignment = { vertical: 'middle', horizontal: 'center' }
})

//工作表视图
worksheet.views = [
{state: 'normal', zoomScale:120, }
];

const writeFile = (fileName, content) => {
const link = document.createElement("a");
const blob = new Blob([content], {
type: "application/vnd.ms-excel;charset=utf-8;"
});
link.download = fileName;
link.href = URL.createObjectURL(blob);
link.click();
};

// 表格的数据绘制完成，定义下载方法，将数据导出到Excel文件
workbook.xlsx.writeBuffer().then((buffer) => {
writeFile(sheetName, buffer);
});
};