xlsx-style导出excel

话不多说 上代码

1、控制台引入插件

npm install xlsx-style --save

npm install xlsx --save

2、需要修改源码：在\node_modules\xlsx-style\dist\cpexcel.js 807 行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable 不然会报错;

3、设置每个单元格样式 下面的参数  styleCallback

styleCallback(cellContent,cellPosition) {
    let border= {
                    top: {
                        style: 'thin',
                    },
                    bottom: {
                        style: 'thin'
                    },
                    left: {
                        style: 'thin'
                    },
                    right: {
                        style: 'thin'
                    }
                };
    let alignment={
                    horizontal: 'center',   //水平居中对齐
                    vertical:'center',
                    wrapText:true
                  };

    let textFont={
                      sz: 12, bold: true,
                      color: { rgb: "000000" }
                  };

             for(var po in cellPosition){
                 cellContent[cellPosition[po]].s = {border:border,
                   alignment: alignment,
                  // font:textFont,
                   fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFFFF" } }
                }
            }
  },

4、设置单元格的宽高 做为参数传到下面的colRowWHCallback

colRowWHCallback(cellContent) {
        cellContent['!cols']= [
            {wpx: 150},
            {wpx: 150},
            {wpx: 150},
            {wpx: 150},
        ];

        cellContent['!rows']=[
            {hpx: 30,},
            {hpx: 60}
        ];
    },

5、excel导出核心

import xlsxStyle from "xlsx-style";
const ExpExcel = {};

(function (exp) {
    //fileName: excel的名字
    //json:  数组   eg:[{0:"1",1:"2",......},{0:"1",1:"2",......}] 数组里几个json表里面就有几个sheet
    //type:  "xlsx"
    //mergeCell  表的样式  eg:[{0:{s:{r:1,c:0},e:{r:4,c:0}},1:{s:{r:2,c:0},e{r:3,c:0}}},{0:{s:{r:1,c:0},e:{r:4,c:0}},1:{s:{r:2,c:0},e{r:3,c:0}}}] 跟上面json数组里面的json一一对应
    // s:{r:1,c:0} A2              e:{r:4,c:0}A5
    // 0:{s:{r:1,c:0},e:{r:4,c:0}}表示合并excel单元格里面的A2-A5
    //styleCallback： 设置每个单元格的样式
    //colRowWHCallback :单元格的宽高之类的应该
    //biaomin ：excel里面每个sheet的名字 eg:["sheet1","sheet2"] 跟上面的json，mergeCell，都是一一对应的
    exp.downloadExl=function (fileName,json, type,mergeCell,styleCallback,colRowWHCallback,biaomin) {
        let  workSheet={
            SheetNames : [],
            Sheets:{}
        };;
        for(let k=0;k<json.length;k++){
            let ok;
            //每个单元格的内容
            var cellContent = getCellContent(json[k]);
            //获取所有的单元格
            var cellPosition = Object.keys(cellContent);

            if (styleCallback) {
                styleCallback(cellContent,cellPosition);
            }

            if (colRowWHCallback) {
                colRowWHCallback(cellContent);
            }



            //单元格合并
            cellContent["!merges"]=mergeCell[k];
            //保存的表标题
            workSheet.SheetNames.push(biaomin[k]);
            workSheet.Sheets[biaomin[k]]= Object.assign({},
                    cellContent, //内容
                    {
                        '!ref': cellPosition[0] + ':' + cellPosition[cellPosition.length - 1] //设置填充区域
                    });

        }
        //这里的数据是用来定义导出的格式类型  //创建二进制对象写入转换好的字节流
        let buf=s2ab(xlsxStyle.write(workSheet,
            {
                bookType: (type == undefined ? 'xlsx' : type),
                bookSST: false,
                type: 'binary'
            }));
        let exportExcelData = new Blob([buf],{type: ""});
        saveAs(exportExcelData, fileName +"."+(type == undefined ? 'xlsx':type));
    }
    

    //填充Excel每个单元格的内容
    function getCellContent(jsonData) {

        var keyMap = []; //获取keys
        for (var k in jsonData[0]) {
            keyMap.push(k);
        }

        var cellData=[];

        jsonData.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
            v: v[k],
            position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
        }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => cellData[v.position] = {
            v: v.v
        });

        return cellData;
    }

    
    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;
    }

    // 将指定的自然数转换为26进制表示。映射关系：[0-25] -> [A-Z]。
    function getCharCol(n) {
        let temCol = '',
            s = '',
            m = 0
        while (n > 0) {
            m = n % 26 + 1
            s = String.fromCharCode(m + 64) + s
            n = (n - m) / 26
        }
        return s
    }



    function saveAs(obj, fileName) {
        var tmpa = document.createElement("a");
        tmpa.download = fileName || "下载";
        tmpa.href = URL.createObjectURL(obj);
        tmpa.click();
        setTimeout(function () {
            URL.revokeObjectURL(obj);
        }, 100);
    }


})(ExpExcel);

export default ExpExcel;

6、调用核心  ExpExcel核心的类名 ys对应核心的mergeCell

ExpExcel.downloadExl("牛逼格拉斯",json, "xlsx",ys,_self.setCellStyle,_self.setColRowWH,biaom);