import * as ExcelJs from 'exceljs/dist/exceljs';
export function jsonToExcel(data) {
const tableName = "表名称";
const borderStyle = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
};
let newData = undefined;
let colInfo = undefined;
if (data.tBody && data.tHeader) {
newData = resetData(data.tBody, data.tHeader);
const colData = rowToCol(newData); // 行列转换
colInfo = getMaxValueLength(colData, 1); // 获取整列数据表格宽度信息
}
let secHeader = undefined;
if (data.tHeader) {
secHeader = resetHeader(data.tHeader);
}
const workbook = new ExcelJs.Workbook();
workbook.created = new Date();
workbook.modified = new Date();
workbook.lastPrinted = new Date();
workbook.properties.date1904 = true; // 设置日期系统
const worksheet = workbook.addWorksheet('sheet', { properties: { tabColor: { argb: 'FF00FF00' } } });
for (const key in colInfo) { // 设置列宽
const dobCol = worksheet.getColumn(colInfo[key].colLocation);
if (colInfo[key] && dobCol) {
if (colInfo[key].colWidth && colInfo[key].colWidth > 15) {
dobCol.width = colInfo[key].colWidth;
} else {
dobCol.width = 15;
}
} else {
dobCol.width = 15;
}
}
const firstRow = worksheet.getRow(1);
firstRow.getCell(1).border = borderStyle;
firstRow.getCell(1).value = {
'richText': [{
'font': {
'bold': true,
'size': 16,
'color': { 'theme': 1 },
'name': '黑体',
'family': 2,
'scheme': 'minor'
},
'text': tableName
}]
};
firstRow.alignment = { vertical: 'middle', horizontal: 'center' };
worksheet.mergeCells(1, 1, 1, secHeader.length + 1);
worksheet.mergeCells('A2', 'A3');
if (data.tHeader && data.tBody && data.tBody.length > 0) { // 获取表头
const header = data.tHeader;
const headerRow = worksheet.getRow(2); // 标题行
headerRow.alignment = { vertical: 'middle', horizontal: 'center' };
let startRow = 2; // 从第二列开始
for (const index in header) {
headerRow.getCell(1).value = header[0].name;
headerRow.getCell(1).border = borderStyle;
if (index > 0) {
let step = header[index].children.length;
let endRow = startRow + step - 1;
headerRow.getCell(startRow).value = header[index].name;
headerRow.getCell(startRow).border = borderStyle;
worksheet.mergeCells(2, startRow, 2, endRow);
startRow = endRow + 1;
}
}
const secHeaderRow = worksheet.getRow(3); // 遍历二级标题行
for (const key in secHeader) {
const index = parseInt(key);
secHeaderRow.getCell(index + 2).value = secHeader[index];
secHeaderRow.getCell(index + 2).alignment = { vertical: 'middle', horizontal: 'center' }; // 内容居中
secHeaderRow.getCell(index + 2).border = borderStyle;
}
for (const key in newData) { // 遍历数据体;
const lineData = newData[key];
if (lineData.length > 0) {
let row = worksheet.getRow(parseInt(key) + 4);
for (const index in lineData) {
const num = parseInt(index);
if (num > 0) {
row.getCell(num + 1).value = changeType(lineData[num]);
} else {
row.getCell(num + 1).value = lineData[num];
}
row.getCell(num + 1).alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; // 内容居中
row.getCell(num + 1).border = borderStyle;
}
}
}
workbook.xlsx.writeBuffer().then(buffer => { // 保存设置;
saveAs(new Blob([buffer], {
type: 'application/octet-stream'
}), tableName + ".xlsx");
});
}
function getMaxValueLength(data, startColNum) {
let colInfo = [];
let location = startColNum;
if (data.length > 0) {
for (const index in data) {
if (index > 0) {
let maxNum = 0;
for (const key in data[index]) {
const str = String(changeType(data[index][key]));
let strLength = 0;
if (!str) {
strLength = 15;
} else {
strLength = getStringLength(str);
}
if (maxNum < strLength) {
maxNum = strLength;
}
}
let col = {
colLocation: getExcelColName(location - 1),
colWidth: maxNum
};
colInfo.push(col);
} else {
let col = {
colLocation: getExcelColName(location - 1),
colWidth: 20
};
colInfo.push(col);
}
location += 1;
}
}
return colInfo;
}
function getStringLength(str) {
let length = 0;
for (let index = 0; index < str.length; index++) {
if (isChinese(str.charAt(index))) {
length += 3;
} else {
length += 1;
}
}
return length + 4;
}
function isChinese(temp) {
var re = /[^\u4E00-\u9FA5]/;
if (re.test(temp)) return false;
return true;
}
function getExcelColName(n) {
var ordA = 'A'.charCodeAt(0);
var ordZ = 'Z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (n >= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}
function rowToCol(data) {
let colData = [];
if (data && data.length > 0) {
for (const key in data[0]) {
colData.push([]);
}
for (const key in data) {
for (const index in data[key]) {
colData[index][key] = data[key][index];
}
}
}
return colData;
}
function resetHeader(data) {
const resArr = [];
for (const key in data) {
if (key > 0 && data[key].children.length > 0) {
for (const index in data[key].children) {
resArr.push(data[key].children[index]);
}
}
}
return resArr;
}
function changeType(data) {
let newData = data;
if (newData === "") {
return "";
}
if (newData.type === "text" || newData.type === "text" || newData.type === "text") {
return newData.value;
}
if (newData.type === "checkboxgroup") {
const arr = newData.value || [];
let res = "";
for (const key in arr) {
if (key > 0) {
res += ", " + arr[key];
} else {
res += arr[key];
}
}
return res;
}
if (newData.type === "image") {
const arr = newData.value || [];
let res = "";
for (const key in arr) {
if (key > 0) {
res += ", " + arr[key].url;
} else {
res += arr[key].url;
}
}
return res;
}
return newData.value;
}
function resetData(data, headerData) {
const returnData = [];
const arr = data || [];
for (const key in arr) {
const line = [];
line.push(arr[key].time);
for (const index in arr[key].oneLine) {
if (arr[key].oneLine[index].items && arr[key].oneLine[index].items.length > 0) {
for (const k in arr[key].oneLine[index].items) {
line.push(arr[key].oneLine[index].items[k]);
}
} else {
// 空数据长度获取
let spaceLength = getLong(index, headerData);
for (let index = 0; index < spaceLength; index++) {
line.push("");
}
}
}
returnData.push(line);
}
return returnData;
}
function getLong(index, data) {
let length = 0;
for (const key in data) {
if ((parseInt(key) - 1) === parseInt(index)) {
length = data[key].children.length;
}
}
return length;
}
function saveAs(obj, fileName) {
let excelTable = document.createElement('a');
excelTable.download = fileName || '下载';
excelTable.href = URL.createObjectURL(obj);
excelTable.click();
setTimeout(function () {
URL.revokeObjectURL(obj);
}, 100);
}
}
excel.js实践(2021-6-25)
最新推荐文章于 2024-08-06 16:10:14 发布