import { saveAs } from 'file-saver';
function exportExcel(
column,
dataSource,
fileName = 'example',
headerRows,
tableHeader,
tableFooter,
) {
// console.info('column:', column);
// console.info('dataSource:', dataSource);
// 新建工作谱
const file = new File();
// 新建表
let sheet = file.addSheet('sheet-test');
// 获取表头行数
let depth = getDepth(column);
// 获取表头的列数
let columnNum = getColumns2(0, column);
// 用于记录需要合并的单元格信息
const mergeCells = [
// {"startRow":3,"endRow":4,"startCol":0,"endCol":0},
// {"startRow":5,"endRow":6,"startCol":0,"endCol":0},
// {"startRow":7,"endRow":13,"startCol":0,"endCol":0},
// {"startRow":14,"endRow":16,"startCol":0,"endCol":0},
// {"startRow":17,"endRow":18,"startCol":0,"endCol":0}
];
// 新建表头行数
let rowArr = [];
for (let k = 0; k < depth; k++) {
rowArr.push(sheet.addRow());
}
// 根据列数填充单元格
rowArr.map((ele) => {
for (let j = 0; j < columnNum; j++) {
let cell = ele.addCell();
cell.value = j;
}
});
//表头上面新增了一行
if (tableHeader) {
// sheet.addRow();
let hCell = sheet.cell(0, 0);
hCell.value = tableHeader;
hCell.hMerge = columnNum - 1;
hCell.style.align.h = 'left';
hCell.style.align.v = 'center';
depth++;
// 初始化表头
init(column, 1, 0);
} else {
// 初始化表头
init(column, 0, 0);
}
// 按顺序展平column
let columnLineArr = [];
columnLine(column);
// 根据column,将dataSource里面的数据排序，并且转化为二维数组
let dataSourceArr = [];
dataSource.map((ele, i) => {
let dataTemp = [];
const rowSpan = getRowSpanCount(dataSource || [], 'index_value', i);
columnLineArr.map((item, index) => {
// console.info('dataSource111', item.dataIndex, ele[item.dataIndex]);
var dIndex = item.dataIndex;
if (dIndex == 'index_value' && isMergeCell == 1) {
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
rowSpan: rowSpan,
});
} else if (isMergeCell == 240) {
if (i == 0) {
if (index == 0) {
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
colSpan: 0,
});
} else if (index == 1) {
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
colSpan: 2,
});
} else {
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
});
}
} else {
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
});
}
} else {
if (dIndex) {
//经济监测系统表格
dataTemp.push({
// [item.dataIndex]: ele[item.dataIndex],
dIndex: ele[dIndex],
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
});
} else {
//数据填报系统表格
// var dIndex = item.dataIndex;
// console.info('dataSource', ele, dIndex, item, ele[dIndex]);
dataTemp.push({
indexValue: item.dataIndex,
value:
dIndex == 'sortId'
? ele['indexValue0']?.SortJson?.sortId
: ele[dIndex],
});
}
}
});
dataSourceArr.push(dataTemp);
});
// debugger;
// 绘画表格数据
let currentRow = 1;
dataSourceArr.forEach((item, index) => {
//根据数据,创建对应个数的行
let row = sheet.addRow();
row.setHeightCM(0.8);
//创建对应个数的单元格
item.map((ele, i) => {
let cell = row.addCell();
if (ele.hasOwnProperty('num')) {
cell.value = index + 1;
} else {
cell.value = ele.value;
}
cell.style.align.v = 'center';
cell.style.align.h = 'center';
if (
(ele.rowSpan && ele.rowSpan > 1) ||
(ele.colSpan && ele.colSpan > 1)
) {
console.log('currentRow', currentRow);
mergeCells.push({
startRow: currentRow + headerRows,
// startCol: cellIndex + 1,
endRow: currentRow + headerRows + ele.rowSpan - 1, //currentRow + ele.rowSpan - 1
startCol: 0,
endCol: ele.colSpan - 1,
// endCol: cellIndex + 1
});
}
});
currentRow++;
});
console.log('dataSourceArr', dataSourceArr, mergeCells);
//设置每列的宽度
for (var i = 0; i < 4; i++) {
sheet.col(i).width = 20;
}
// console.info("sheet===",sheet.maxRow,sheet);
if (tableFooter) {
let hCell = sheet.cell(sheet.maxRow, 0);
hCell.value = tableFooter;
hCell.hMerge = columnNum - 1;
hCell.style.align.h = 'left';
hCell.style.align.v = 'center';
}
// 执行单元格合并
mergeCells.forEach((mergeInfo) => {
// sheet.mergeCells(mergeInfo.startRow, mergeInfo.startCol, mergeInfo.endRow, mergeInfo.endCol);
const startCell = sheet.cell(mergeInfo.startRow, mergeInfo.startCol);
if (mergeInfo.endRow - mergeInfo.startRow > 0) {
startCell.vMerge = mergeInfo.endRow - mergeInfo.startRow;
}
if (mergeInfo.endCol - mergeInfo.startCol > 0) {
startCell.hMerge = mergeInfo.endCol - mergeInfo.startCol;
}
});
file.saveAs('blob').then(function (content) {
saveAs(content, fileName + '.xlsx');
});
// 按顺序展平column
function columnLine(column) {
column.map((ele) => {
if (
ele.children === null ||
ele.children === undefined ||
ele.children.length === 0
) {
columnLineArr.push(ele);
} else {
columnLine(ele.children);
}
});
}
// 初始化表头
function init(column, rowIndex, columnIndex) {
column.map((item, index) => {
let hCell = sheet.cell(rowIndex, columnIndex);
// 如果没有子元素, 撑满列
if (item.title === '操作') {
hCell.value = '';
return;
} else if (
item.children === null ||
item.children === undefined ||
item.children.length === 0
) {
// console.info(item.title, depth - rowIndex - 1, columnIndex);
// 第一行加一个单元格
hCell.value = item.title;
if (item.dataIndex == 'sortId') {
hCell.value = '序号';
}
hCell.vMerge = depth - rowIndex - 1;
hCell.style.align.h = 'center';
hCell.style.align.v = 'center';
columnIndex++;
// rowIndex++
} else {
let childrenNum = 0;
function getColumns(arr) {
arr.map((ele) => {
if (ele.children) {
getColumns(ele.children);
} else {
childrenNum++;
}
});
}
getColumns(item.children);
hCell.hMerge = childrenNum - 1;
hCell.value = item.title;
hCell.style.align.h = 'center';
hCell.style.align.v = 'center';
let rowCopy = rowIndex;
rowCopy++;
init(item.children, rowCopy, columnIndex);
// 下次单元格起点
columnIndex = columnIndex + childrenNum;
}
});
}
// 获取表头rows
function getDepth(arr) {
const eleDepths = [];
arr.forEach((ele) => {
let depth = 0;
if (Array.isArray(ele.children)) {
depth = getDepth(ele.children);
}
eleDepths.push(depth);
});
return 1 + max(eleDepths);
}
function max(arr) {
console.info('arr', arr, arr.length);
return arr && arr.length > 0
? arr.reduce((accu, curr) => {
if (curr > accu) return curr;
return accu;
})
: 0;
}
// 计算表头列数
function getColumns(arr) {
let columnNum = 0;
arr.map((ele) => {
if (ele.children) {
getColumns(ele.children);
} else {
console.info('表头', ele.title, columnNum);
columnNum++;
}
});
return columnNum;
}
// 计算表头列数
function getColumns2(num, arr) {
arr.map((ele) => {
if (ele.children) {
num = getColumns2(num, ele.children);
} else {
num++;
}
});
return num;
}
function getRowSpanCount(data, key, target) {
if (!Array.isArray(data)) return 1;
data = data.map((_) => _[key]); // 只取出筛选项
let preValue = data[0];
const res = [[preValue]]; // 放进二维数组里
let index = 0; // 二维数组下标
for (let i = 1; i < data.length; i++) {
if (data[i] === preValue) {
// 相同放进二维数组
res[index].push(data[i]);
} else {
// 不相同二维数组下标后移
index += 1;
res[index] = [];
res[index].push(data[i]);
preValue = data[i];
}
}
const arr = [];
res.forEach((_) => {
const len = _.length;
for (let i = 0; i < len; i++) {
arr.push(i === 0 ? len : 0);
}
});
return arr[target];
}
}
export { exportExcel };
需要根据实际数据修改代码
