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