❧ ExcelJS使用

github地址

引入ExcelJS包

• npm安装

npm install exceljs

• 浏览器引入

<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js"></script>
<script src="exceljs.js"></script>

导入Excel

// 创建工作簿并读取数据
const wb = new ExcelJS.Workbook();
await wb.xlsx.load(fileData);

// 读取第一张表
const ws = wb.getWorksheet(1);

// 字段行数和数据开始行数
const fieldRowNum = 1;
const dataRowNum = 2;

// 遍历工作表中的所有行（不包括空行）
let results = [];
let header = [];
ws.eachRow((row, rowNumber) => {
    const values = dealCellValue(row.values);
    if (rowNumber == fieldRowNum) {
        header = values;
    } else if (rowNumber >= dataRowNum) {
        results.push(values);
    }
});

// 二维数组转数组对象
const len = header.length
results = results.map(t => {
    const tmp = {};
    for (let i = 0; i < len; i++) {
        if (t[i]) {
            tmp[header[i]] = t[i];
        }
    }
    return tmp;
});

// 删除第一列：无效列
header.shift();

// 处理单元格不同类型的值
const dealCellValue = (row) => {
    return row.map(t => {
        if (Object.prototype.toString.call(t) == '[object Date]') {
            return getDateStr(t);
        } else if (Object.prototype.toString.call(t) == '[object Object]') {
            const { text, result, richText, error } = t;
            return text || result || (richText && richText.text) || error;
        } else {
            return t;
        }
    });
}

// 日期对象转日期字符串
const getDateStr = (dateObj) => {
    const date = new Date(dateObj);
    const year = date.getFullYear();
    let month = date.getMonth() + 1;
    month = month > 9 ? month : '0' + month;
    let day = date.getDate();
    day = day > 9 ? day : '0' + day;
    return `${year}/${month}/${day}`;
}

导出数据

const exportExcel = (data, filename) => {
    // 创建工作簿
    const wb = new ExcelJS.Workbook();

    // 表名称
    const ws_name = "工作报表";

    // 添加工作表
    const ws = wb.addWorksheet(ws_name, { views: [{ showGridLines: false }], properties: { defaultRowHeight: 16.5, defaultColWidth: 13 } });

    const len = data.length;
    // 添加行
    for (let i = 0; i < len; i++) {
        ws.addRow(data[i]);
    }

    // 写入文件
    wb.xlsx.writeBuffer().then((buffer) => {
        downFile(buffer, filename);
    });
}

// 下载文件
function downFile(buffer, fileName) {

    const blob = new Blob([buffer])
    const reader = new FileReader();

    // 转换为base64，可以直接放入a标签href
    reader.readAsDataURL(blob);

    reader.onload = (e) => {
        let a = document.createElement("a");
        a.style.display = "none";
        a.download = fileName;
        a.href = e.target.result;
        const body = document.body;
        // 修复firefox中无法触发click
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
    };
}