xlsx.js的使用
邵预鸿 Lv5
 2022-05-16 16:52:26  

为什么要用xlsx?

现在前端可惨，许多事都要做了。。。。读取excel的数据，抛弃了以前的上传一个excel文件到后台解析，直接改用了前端读取excel中的数据转成一个数组对象转数据给后台，那没办法，搞吧

安装

1
npm i xlsx -S

读取数据

1
XLSX.read(ArrayBuffer,配置项)

ArrayBuffer如何来? 参考以下链接: 参考链接：https://zhuanlan.zhihu.com/p/354707440

JS 文件base64、File、Blob、ArrayBuffer互转

1. file对象转base64

1
2
3
let reader = new FileReader();
 reader.readAsDataURL(file[0])
 console.log(reader)

2. base64 转成blob 上传

1
2
3
4
5
6
7
8
9
10
function dataURItoBlob(dataURI) {  
    var byteString = atob(dataURI.split(',')[1]);  
    var mimeString = dataURI.split(',')[0].split(':')[1].split(';')[0];  
    var ab = new ArrayBuffer(byteString.length);  
    var ia = new Uint8Array(ab);  
    for (var i = 0; i < byteString.length; i++) {  
        ia[i] = byteString.charCodeAt(i);  
    }  
    return new Blob([ab], {type: mimeString});  
}

3. blob 转成ArrayBuffer

1
2
3
4
5
6
let blob = new Blob([1,2,3,4])
let reader = new FileReader();
reader.onload = function(result) {
    console.log(result);
}
reader.readAsArrayBuffer(blob);

4. buffer 转成blob

1
let blob = new Blob([buffer])

5. base64 转 file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
const base64ConvertFile = function (urlData, filename) { // 64转file
  if (typeof urlData != 'string') {
    this.$toast("urlData不是字符串")
    return;
  }
  var arr = urlData.split(',')
  var type = arr[0].match(/:(.*?);/)[1]
  var fileExt = type.split('/')[1]
  var bstr = atob(arr[1])
  var n = bstr.length
  var u8arr = new Uint8Array(n)
  while (n--) {
    u8arr[n] = bstr.charCodeAt(n);
  }
  return new File([u8arr], 'filename.' + fileExt, {
    type: type
  });
}

6. 文件转ArrayBuffer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function toBuffer(file, func) {
        let reader = new FileReader();
        reader.readAsDataURL(file);
        reader.onload = function () {
            const dataURI = this.result;
            var byteString = atob(dataURI.split(',')[1]);
            var mimeString = dataURI.split(',')[0].split(':')[1].split(';')[0];
            var ab = new ArrayBuffer(byteString.length);
            var ia = new Uint8Array(ab);
            for (var i = 0; i < byteString.length; i++) {
                ia[i] = byteString.charCodeAt(i);
            }
            const blob = new Blob([ab], { type: mimeString });
            let bufferObject = new FileReader();
            bufferObject.onload = function (result) {
                func(result.target.result)
            }
            bufferObject.readAsArrayBuffer(blob);
        }

    }

文件转ArrayBuffer? 一个一个转吧

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
const xlsx = require('xlsx');
(function () {
    function createElement() {
        var template = `
           <input type="file">
           <button id="loadFile">读取文件</button>
        `;
        document.body.innerHTML += template;
    }
    
    //通用文件转接转ArrayBuffer
    function toBuffer(file, func) {
        let reader = new FileReader();
        reader.readAsDataURL(file);
        reader.onload = function () {
            const dataURI = this.result;
            var byteString = atob(dataURI.split(',')[1]);
            var mimeString = dataURI.split(',')[0].split(':')[1].split(';')[0];
            var ab = new ArrayBuffer(byteString.length);
            var ia = new Uint8Array(ab);
            for (var i = 0; i < byteString.length; i++) {
                ia[i] = byteString.charCodeAt(i);
            }
            const blob = new Blob([ab], { type: mimeString });
            let reader2 = new FileReader();
            reader2.onload = function (result) {
                func(result.target.result)
            }
            reader2.readAsArrayBuffer(blob);
        }

    }
    
    function getFileInfo(array) {
        console.log(array);
        const workbook = xlsx.read(array); //workbook就是xls文档对象
        const sheetNames = workbook.SheetNames; //获取表明
        const sheet = workbook.Sheets[sheetNames[0]]; //通过表明得到表对象              
        const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
        console.log(sheetNames, sheet, data);  //data就是读取得到excel的数据

    }
    
    function addMethods() {
        const btn = document.querySelector("#loadFile");
        btn.onclick = function () {
            const file = document.querySelector("input[type='file']");
            if (file) {
                const fileList = file.files;
                toBuffer(fileList[0], getFileInfo)
            }
        }
    }
    createElement();
    addMethods();
})();

写入数据

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
32
33
34
35
36
37
38
39
40
41
42
function downloadExcel() {
        const btn = document.querySelector("#download");
        btn.onclick = function () {
            let json = [
                { 姓名: 'name_01', 年龄: 21, 地址: 'address_01' },
                { 姓名: 'name_02', 年龄: 22, 地址: 'address_02' },
                { 姓名: 'name_03', 年龄: 23, 地址: 'address_03' },
                { 姓名: 'name_04', 年龄: 24, 地址: 'address_04' },
                { 姓名: 'name_05', 年龄: 25, 地址: 'address_05' },];

            let ws = xlsx.utils.json_to_sheet(json); //通过工具将json转表对象
            ws['!cols'] = [
                { wch: 50 },
                { wch: 50 },
                { wch: 50 }
            ] //设置列宽
             ws['!rows'] = [
                { hpt : 20 },
                { hpt : 20 },
                { hpt : 20 },
                { hpt : 20 },
            ] //设置行高
             ws['!merges'] = [ //可以存在多个  可以合并多个位置
              {
                s:{  //s代表start开始合并的位置 
                  c:0, //col 列A,B,C列  下标从0开始
                  r:0  //row  行  0,1,2,3  下标从0
                },
                e:{ //end 结束位置
                  c:3,
                  r:0
                }
              }
            ]
            // 创建 workbook
            const wb = xlsx.utils.book_new()
            // 生成xlsx文件(book,sheet数据,sheet命名)
            xlsx.utils.book_append_sheet(wb, ws, '数据详情')
            // 写文件(book,xlsx文件名称)
            xlsx.writeFile(wb, '列表详情.xlsx')
        }
    }

参考链接：https://www.npmjs.com/package/xlsx

http://server.yuhongshao.cn/static/yuhongshao/20220525165506.png

有样式

官方文档请参考： https://www.npmjs.com/package/xlsx-style

参考网址: https://blog.csdn.net/Yuoliku/article/details/109214337

安装xlsx-style

1
npm i xlsx-style -S

修改xlsx-style源码报错

1
2
在\node_modules\xlsx-style\dist\cpexcel.js  
var cpt = require('./cpt' + 'able');  改为   var cpt = cptable;

配置参考代码

参考网站：https://blog.csdn.net/qq_42440043/article/details/121974854

常用样式配置

Style Attribute Sub Attributes Values
fill patternType "solid" or "none"
fgColor COLOR_SPEC
bgColor COLOR_SPEC
font name "Calibri" // default
sz "11" // font size in points
color COLOR_SPEC
bold `true
underline `true
italic `true
strike `true
outline `true
shadow `true
vertAlign `true
numFmt "0" // integer index to built in formats, see StyleBuilder.SSF property
"0.00%" // string matching a built-in format, see StyleBuilder.SSF
"0.0%" // string specifying a custom format
"0.00%;\\(0.00%\\);\\-;@" // string specifying a custom format, escaping special characters
"m/dd/yy" // string a date format using Excel’s format notation
alignment vertical `”bottom”
horizontal `”bottom”
wrapText `true
readingOrder 2 // for right-to-left
textRotation Number from 0 to 180 or 255 (default is 0)
90 is rotated up 90 degrees
45 is rotated up 45 degrees
135 is rotated down 45 degrees
180 is rotated down 180 degrees
255 is special, aligned vertically
border top { style: BORDER_STYLE, color: COLOR_SPEC }
bottom { style: BORDER_STYLE, color: COLOR_SPEC }
left { style: BORDER_STYLE, color: COLOR_SPEC }
right { style: BORDER_STYLE, color: COLOR_SPEC }
diagonal { style: BORDER_STYLE, color: COLOR_SPEC }
diagonalUp `true
diagonalDown `true

合并单元格时，如A1-C1合并成一个单元格时，只需要对A1设置样式即可

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
<button @click="downloadExcel">下载</button>
downloadExcel() {
      let json = [
        {
          姓名: "name_01",
          年龄: 21,
          地址: "address_01",
          银行卡号: "6221506592552326252311",
        },
        {
          姓名: "name_02",
          年龄: 22,
          地址: "address_02",
          银行卡号: "6221506592552326252311",
        }
      ];

      let ws = xlsx.utils.json_to_sheet([...json]); //通过工具将json转表对象
      console.log(ws);
      ws["!cols"] = [{ wch: 50 }, { wch: 50 }, { wch: 50 }, { wch: 50 }]; //设置列宽
      ws["!merges"] = [
        {
          s: {
            c: 0,
            r: 0,
          },
          e: {
            c: 3,
            r: 0,
          },
        },
        {
          s: {
            c: 0,
            r: 5,
          },
          e: {
            c: 1,
            r: 5,
          },
        },
      ];
      for (const i in ws) {
        console.log(i, ws[i]);
        if (i === 'A1' || i==='A4') {  //指定样式
          ws[i].s = {
            ...ws[i].s,
            fill: {
              //背景色
              fgColor: { rgb: "ff0000" },
            },
            font: {
              //覆盖字体
              sz: 11,
              bold: true,
              color: { rgb: "ffffff" },
            },
          };
        }
      }
      this.openDownload(this.sheet2blob(ws, "列表详情"), "数据详情.xlsx");
    },

   sheet2blob(sheet, sheetName) {
      let wb = xlsx.utils.book_new();
      wb.SheetNames.push(sheetName);
      wb.Sheets[sheetName] = sheet;
      var wbout = xlsxStyle.write(wb, {
        bookType: "",
        bookSST: false,
        type: "binary",
      });
      var blob = new Blob([s2ab(wbout)], { type: "" }, sheetName);
      // 字符串转ArrayBuffer
      function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
        return buf;
      }
      return blob;
    },
    openDownload(url, saveName) {
      if (typeof url == "object" && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
      }
      var aLink = document.createElement("a");
      aLink.href = url;
      aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      var event;
      if (window.MouseEvent) event = new MouseEvent("click");
      else {
        event = document.createEvent("MouseEvents");
        event.initMouseEvent(
          "click",
          true,
          false,
          window,
          0,
          0,
          0,
          0,
          0,
          false,
          false,
          false,
          false,
          0,
          null
        );
      }
      aLink.dispatchEvent(event);
    },

http://server.yuhongshao.cn/static/yuhongshao/20220526094901.png

给每个单元格添加样式

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
for (const i in ws) {
 console.log(i, ws[i]);
 const line = Number(i.substring(1));
 if (/^\d+$/.test(line)) {
   ws[i].s = {
     ...ws[i].s,
     border: {
       top: {
         style: "thin",
         color: { rgb: "2a28c9" },
       },
       bottom: {
         style: "thin",
         color: { rgb: "2a28c9" },
       },
       left: {
         style: "thin",
         color: { rgb: "2a28c9" },
       },
       right: {
         style: "thin",
         color: { rgb: "2a28c9" },
       },
     },
   };
 }
 }

多个sheet

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
handleExport = () => {
  const sheetData1 = data1.map(item => ({
    'ID': item.id,
    '收件人': item.name,
    '手机号': item.phone,
    '扫描时间': item.scanTime,
  }));
  // 支持多 sheet
  // const sheetData2 = data2.map(item => ({
  //   // 在这里设置表头和对应项的值
  // }));
  // const sheetData3 = data3.map(item => ({
  //   // 在这里设置表头和对应项的值
  // }));
  // ...

  const sheet1 = XLSX.utils.json_to_sheet(sheetData1);
  // 支持多 sheet
  // const sheet2 = XLSX.utils.json_to_sheet(sheetData2);
  // const sheet3 = XLSX.utils.json_to_sheet(sheetData3);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, sheet1, '收件人列表');
  // 支持多 sheet
  // XLSX.utils.book_append_sheet(wb, sheet2, '表格 sheet2');
  // XLSX.utils.book_append_sheet(wb, sheet2, '表格 sheet2');
  const workbookBlob = workbook2blob(wb);
  // 导出最后的总表
  openDownloadDialog(workbookBlob, '收件人列表.xlsx');
}

读取多个sheet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
const workbook = xlsx.read(array); //workbook就是xls文档对象
const sheetNames = workbook.SheetNames; //获取表明
const sheet = workbook.Sheets[sheetNames[0]]; //通过表明得到表对象
const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json

const sheet2 = workbook.Sheets[sheetNames[1]]; //通过表明得到表对象
const data2 = xlsx.utils.sheet_to_json(sheet2); //通过工具将表对象的数据读出来并转成json

const sheet3 = workbook.Sheets[sheetNames[2]]; //通过表明得到表对象
const data3 = xlsx.utils.sheet_to_json(sheet3); //通过工具将表对象的数据读出来并转成json

console.log(sheetNames, sheet, data); //data就是读取得到excel的数据
console.log(sheetNames, sheet2, data2); //data就是读取得到excel的数据
console.log(sheetNames, sheet3, data3); //data就是读取得到excel的数据

单独设置某个单元格

你可以使用 xlsx-style 库中的 utils.cell_set_style 方法来单独设置某个单元格的样式。该方法接受一个工作表对象、单元格地址和样式对象作为参数，可以将指定单元格的样式设置为指定的样式对象。

例如，下面的代码演示了如何在 xlsx-style 中单独设置某个单元格的样式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
javascript复制const XLSX = require('xlsx-style');

const data = [
  ['姓名', '年龄', '地址'],
  ['张三', 18, '北京市海淀区中关村'],
  ['李四', 20, '上海市浦东新区张江'],
  ['王五', 22, '广州市天河区珠江新城'],
];

const ws = XLSX.utils.aoa_to_sheet(data);

// 设置单元格样式
ws['A1'].s = {
  font: { bold: true },
  fill: { fgColor: { rgb: 'FFFF0000' } },
};

// 将工作表添加到工作簿中
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// 将工作簿保存为 Excel 文件
XLSX.writeFile(wb, 'example.xlsx');

在上面的代码中，我们使用 utils.cell_set_style 方法将单元格 A1 的样式设置为一个包含字体加粗和红色填充颜色的样式对象。我们首先访问单元格 A1，然后将其 s 属性设置为样式对象。

需要注意的是，utils.cell_set_style 方法只能用于单独设置某个单元格的样式。如果你需要设置多个单元格的样式，你可以使用 utils.sheet_set_range_style 方法。

希望这个例子能够帮助你在 xlsx-style 中单独设置某个单元格的样式。

单独为某个单元格设置样式

你可以使用 xlsx-style 库中的 utils.cell_set_style 方法来单独设置某个单元格的样式。该方法接受一个工作表对象、单元格地址和样式对象作为参数，可以将指定单元格的样式设置为指定的样式对象。

例如，下面的代码演示了如何在 xlsx-style 中单独设置某个单元格的样式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
javascript复制const XLSX = require('xlsx-style');

const data = [
  ['姓名', '年龄', '地址'],
  ['张三', 18, '北京市海淀区中关村'],
  ['李四', 20, '上海市浦东新区张江'],
  ['王五', 22, '广州市天河区珠江新城'],
];

const ws = XLSX.utils.aoa_to_sheet(data);

// 设置单元格样式
ws['A1'].s = {
  font: { bold: true },
  fill: { fgColor: { rgb: 'FFFF0000' } },
};

// 将工作表添加到工作簿中
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// 将工作簿保存为 Excel 文件
XLSX.writeFile(wb, 'example.xlsx');

在上面的代码中，我们使用 utils.cell_set_style 方法将单元格 A1 的样式设置为一个包含字体加粗和红色填充颜色的样式对象。我们首先访问单元格 A1，然后将其 s 属性设置为样式对象。

需要注意的是，utils.cell_set_style 方法只能用于单独设置某个单元格的样式。如果你需要设置多个单元格的样式，你可以使用 utils.sheet_set_range_style 方法。

希望这个例子能够帮助你在 xlsx-style 中单独设置某个单元格的样式。

请注意 表头为合并单元格的情况，需要先设置了单元格内容，才能拿到ws[i].s

NOde.js生成excel文件

在 Node.js 环境中使用 xlsx.js 库生成 Excel 文件，可以使用 fs 模块将生成的 Excel 文件写入到本地文件系统中。以下是一个简单的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
javascript复制const XLSX = require('xlsx');
const fs = require('fs');

// 创建工作簿
const wb = XLSX.utils.book_new();

// 创建工作表
const ws = XLSX.utils.aoa_to_sheet([
  ['姓名', '年龄', '地址'],
  ['张三', 18, '北京市海淀区中关村'],
  ['李四', 20, '上海市浦东新区张江'],
  ['王五', 22, '广州市天河区珠江新城'],
]);

// 将工作表添加到工作簿中
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// 将工作簿写入到本地文件系统中
const buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
fs.writeFileSync('example.xlsx', buffer);

在上面的代码中，我们首先创建了一个工作簿和一个工作表，并将工作表添加到工作簿中。然后，我们使用 XLSX.write 方法将工作簿转换为二进制数据，并将其写入到本地文件系统中。

需要注意的是，XLSX.write 方法的第一个参数是工作簿对象，第二个参数是一个选项对象，用于指定输出的格式和类型。在本例中，我们将输出格式设置为二进制数据，输出类型设置为 xlsx。

希望这个例子能够帮助你在 Node.js 环境中使用 xlsx.js 库生成 Excel 文件。

读取xlsx文件

在 Node.js 环境中使用 xlsx.js 库读取本地 Excel 文件，可以使用 fs 模块将 Excel 文件读取到内存中，然后使用 XLSX 库将其解析为工作簿对象。以下是一个简单的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
javascript复制const XLSX = require('xlsx');
const fs = require('fs');

// 读取 Excel 文件
const buffer = fs.readFileSync('example.xlsx');

// 将 Excel 文件解析为工作簿对象
const wb = XLSX.read(buffer, { type: 'buffer' });

// 获取工作表
const ws = wb.Sheets['Sheet1'];

// 将工作表转换为 JSON 对象
const data = XLSX.utils.sheet_to_json(ws);

console.log(data);在上面的代码中，我们首先使用 `fs` 模块读取本地的 Excel 文件，并将其保存到一个 Buffer 对象中。然后，我们使用 `XLSX.read` 方法将 Buffer 对象解析为工作簿对象。接着，我们使用 `wb.Sheets` 属性获取工作簿中的工作表，并使用 `XLSX.utils.sheet_to_json` 方法将工作表转换为 JSON 对象。

需要注意的是，XLSX.read 方法的第一个参数是一个二进制数据，第二个参数是一个选项对象，用于指定输入的格式和类型。在本例中，我们将输入格式设置为二进制数据，输入类型设置为自动检测。

希望这个例子能够帮助你在 Node.js 环境中使用 xlsx.js 库读取本地 Excel 文件

常见问题

jszip 不是构造函数

当我使用“xlsx”：“^0.15.6”时，
TypeError: jszip is not a constructor at zip_read (xlsx.js:2867) at read_zip (xlsx.js:20706) at Object.readSync [as read] (xlsx.js:20773) at FileReader.fileReader.onload (import.js:80)
使用“import * as XLSX from ‘xlsx’”来导入 xlsx
我该如何修复它？

解答：

1
2
3
4
5
6
const NodePolyfillPlugin = require("node-polyfill-webpack-plugin")
module.exports = {
    plugins: [
        new NodePolyfillPlugin()
    ]
}

https://github.com/SheetJS/sheetjs/issues/1879

vue引入xlsx-style一直报错找不到“fs”

1
2
3
Module not found: 
Error: Can't resolve 'fs' in 'C:\Users\TJ-
YFB01\Desktop\cctv\node_modules\xlsx-style

解答

在vue.config.js中配置

1
2
3
4
5
6
7
8
9
10
configureWebpack: {
  resolve: {
    fallback:{
      fs: false
    }
  },
  externals: {
    './cptable': 'var cptable',
  },
},

https://segmentfault.com/q/1010000041737065

  • 本文标题：xlsx.js的使用
  • 本文作者：邵预鸿
  • 创建时间：2022-05-16 16:52:26
  • 本文链接：/images/logo.jpg2022/05/16/xlsx-js的使用/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！