node-xlsx生成表格、生成多个sheet、以及部分样式实现。微信小程序同样适用

安装依赖 

npm i node-xlsx

生成表格 

const xlsx = require('node-xlsx');
const fs = require('fs');

let excelData = [
    {
        name:"考勤表",//sheet名字
        data:[
            [ "考勤表"],//第一行内容
            ["日期范围:2024-01-01 ~ 2024-01-31"],//第二行内容,
            ["用户编号:1","用户姓名:小明"],//第三行 用,间隔开不同单元格内容
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
    },
];

const buffer = xlsx.build(excelData);

fs.writeFileSync('./node.xlsx.xlsx', buffer);//第一个就是文件的名字

console.log('文件已保存为 node.xlsx');

生成多个sheet

只需要在数组中多添加一个对象数据

let excelData = [
    {
        name:"考勤表",
        data:[
            [ "考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小明"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
        
    },
    {
        name:"考勤表2",
        data:[
            ["考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小红"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
        ]
    },
];

表格样式

合并两列单元格

let excelData = [
    {
        name:"考勤表",
        data:[
            [ "考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小明"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
        options:{
            "!merges":[{s:{c:0,r:0},e:{c:12,r:0}}],//c就是列 r就是行 c0就是表格中的A1 c12就是M1
        }
    },
    {
        name:"考勤表2",
        data:[
            ["考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小红"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
        ]
    },
];

合并两行单元格

let excelData = [
    {
        name:"考勤表",
        data:[
            [ "考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小明"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
        options:{
            "!merges":[{s:{c:0,r:7},e:{c:0,r:8}}],//其实都是从0开始 
        }
    },
    {
        name:"考勤表2",
        data:[
            ["考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小红"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
        ]
    },
];

指定一块区域合并

let excelData = [
    {
        name:"考勤表",
        data:[
            [ "考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小明"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
        options:{
            "!merges":[{s:{c:0,r:7},e:{c:12,r:8}}],
        }
    },
    {
        name:"考勤表2",
        data:[
            ["考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小红"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
        ]
    },
];

调整列宽

let excelData = [
    {
        name:"考勤表",
        data:[
            [ "考勤表"],
            ["日期范围:2024-01-01 ~ 2024-01-31"],
            ["用户编号:1","用户姓名:小明"],
            ["日期",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
            ["考勤"],
            ["日期/星期",17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
            ["考勤"],
        ],
        options:{
            "!cols": [{wch: 20}, {wch: 50}],//列宽
        }
    },
];

---

我使用node-xlsx一直没有实现文字居中，如果有做到了的朋友不妨分享一下。

后面我自己使用了另外一个库实现了文字居中，有时间再写一篇博客分享出来。