前端使用xlsx、file-saver实现自定义excel格式导出（列宽、字体、边框、行高）

前端使用xlsx、file-saver实现excel导出

xlsx-style可以自定义表格样式

1.下载依赖

使用命令行下载所需要的依赖，会在在package.json文件内添加。

"dependencies": {
    "axios": "^0.19.2",
    "babel-polyfill": "^6.26.0",
    "element-ui": "^2.15.6",
    "file-saver": "^2.0.5",
    "pinyin-match": "^1.2.2",
    "print-js": "^1.6.0",
    "vue": "^2.5.2",
    "vue-router": "^3.0.1",
    "vuex": "^3.1.2",
    "xlsx": "^0.17.0",
    "xlsx-style": "^0.8.13"
  },

2.在需要导出excel的页面使用依赖

  import XLSX from "xlsx";
  import XLSXSTYLE from "xlsx-style";
  import FileSaver from "file-saver";

3.实现导出excel文件功能

3.1给需要导出的表格赋予id

<el-table size="mini" id="todaytable"
                  :header-cell-style="headerMerge"
                  :span-method="arraySpanMethod"
                  :cell-style="cellStyle"
                  ref="multipleTable"
                  :data="tableData"
                  border
                  stripe
                  style="width: 100%">

3.2按钮调用导出文件函数

                <el-button @click="handleDownload()">下载</el-button>

3.3函数实现导出功能

按照id定位到需要导出的表格

      handleDownload () {
        this.outputXLSX('文件名', '#todaytable', this);
      },
      outputXLSX (filename, selector, _this) {
        let ws = XLSX.utils.table_to_sheet(document.getElementById('todaytable'))
        //创建一个workbook对象
        let wb2 = XLSX.utils.book_new()
        //把worksheet对象添加进workbook对象，第三个参数是excel中sheet的名字
        XLSX.utils.book_append_sheet(wb2, ws, filename)
        this.setExlStyle(wb2['Sheets'][filename]); // 设置列宽 字号等
        this.addRangeBorder(wb2['Sheets'][filename]['!merges'],wb2['Sheets'][filename]) // 单元格合并
        let wb_out = XLSXSTYLE.write(wb2, { type: 'buffer'})
        try {
          FileSaver.saveAs(new Blob([wb_out], {
            type: 'application/octet-stream'
          }), filename+'.xlsx');   //filename.xlsx 为导出的文件名
        } catch (e) {
          console.log(e, wb_out) ;
        }
        return wb_out;
      },
      setExlStyle(data) {
        let borderAll = {  //单元格外侧框线
          top: {
            style: 'thin',
          },
          bottom: {
            style: 'thin'
          },
          left: {
            style: 'thin'
          },
          right: {
            style: 'thin'
          }
        };
        data['!cols'] = [];
        for (let key in data) {
        // 按照数值调整文件字体颜色，数值<0，字体变红。
          let col = '000000'
          if (data[key] instanceof Object) {
            if (data[key].v<0) {
              col = 'ff0000'
            } else {
              col = '000000'
            }
            data[key].s = {
              border: borderAll,
              alignment: {
                horizontal: 'center',   //水平居中对齐
                vertical:'center'
              },
              font:{
              // 自定义字体颜色
                color: {rgb: col},
                sz:11
              },
              bold:true,
              numFmt: 0
            }
            // 自定义列宽，每列都一样
            // data['!cols'].push({wpx: 80})
          }
        }
        // 自定义列宽, 每列不一样
        data['!cols'] = [{wpx: 40},{wpx: 100},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},
          {wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85}
          ,{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85}];
        return data;
      },
      addRangeBorder (range, ws) {
        let cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
        range.forEach(item => {
          let style = {
            s: {
              border: {
                top: { style: 'thin' },
                left: { style: 'thin' },
                bottom: { style: 'thin' },
                right: { style: 'thin' }
              }
            }
          }
          // 处理合并行
          for (let i = item.s.c; i <= item.e.c; i++) {
            ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style
            // 处理合并列
            for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
              ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
            }
          }
        })
        return ws;
      },

因为我的头部是三级而且有合并，导致导出的文件列会有错行或者合并单元格有错，可以输出每一列查看对应数值，自己重新赋值。
我的表头第3行数据会后退一列，使用下方函数进行重新赋值。
wb2[‘Sheets’][filename].D3 = wb2[‘Sheets’][filename].F3
意思是：让D列3行的数据等于F列3行的数据，就是把后一列的数据给前一列，表头就正确了。

有时候表头最后一列是加边框的空单元格，也可以使用上述方法重新赋值，让有边框的空单元格等于他后一列没有边框的空单元格。

      outputXLSX (filename, selector, _this) {
        let ws = XLSX.utils.table_to_sheet(document.getElementById('todaytable'))
        //创建一个workbook对象
        let wb2 = XLSX.utils.book_new()
        //把worksheet对象添加进workbook对象，第三个参数是excel中sheet的名字
        XLSX.utils.book_append_sheet(wb2, ws, filename)
        // 输出查看每列数值
        console.log(wb2['Sheets'][filename])
        // 表头重新赋值
        wb2['Sheets'][filename].D3 = wb2['Sheets'][filename].F3
        wb2['Sheets'][filename].E3 = wb2['Sheets'][filename].G3
        wb2['Sheets'][filename].F3 = wb2['Sheets'][filename].H3
        wb2['Sheets'][filename].G3 = wb2['Sheets'][filename].I3
        wb2['Sheets'][filename].H3 = wb2['Sheets'][filename].J3
        wb2['Sheets'][filename].I3 = wb2['Sheets'][filename].K3
        wb2['Sheets'][filename].J3 = wb2['Sheets'][filename].L3
        // 合并重新调整
        wb2['Sheets'][filename]['!merges'][1].e.c = 1
        wb2['Sheets'][filename]['!merges'][1].s.c = 0
        this.setExlStyle(wb2['Sheets'][filename]); // 设置列宽 字号等
        this.addRangeBorder(wb2['Sheets'][filename]['!merges'],wb2['Sheets'][filename])
        let wb_out = XLSXSTYLE.write(wb2, { type: 'buffer'})

        try {
          FileSaver.saveAs(new Blob([wb_out], {
            type: 'application/octet-stream'
          }), filename+'.xlsx');   //trade-publish.xlsx 为导出的文件名
        } catch (e) {
          console.log(e, wb_out) ;
        }
        return wb_out;
      },

调整行高

百度搜了好久，参考了各个大神的做法，最后参考这个文章的实现了
链接: https://blog.csdn.net/jililin123/article/details/126249198#comments_24348365

需要修改xlxs-style的内容

# 第一步 修改nod_modules 里面xlsx-style文件夹下面dist文件夹下的cpexcel.js文件
807: var cpt = cptable;

# 第二步 修改xlsx-style文件夹下面ods.js文件
10: return require('./' + 'xlsx').utils;
12: try { return require('./' + 'xlsx').utils; }

# 第三步 修改xlsx-style文件夹下面的xlsx.js文件 替换write_ws_xml_data以下方法
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
 var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
 for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
 for(R = range.s.r; R <= range.e.r; ++R) {
   r = [];
   rr = encode_row(R);
   for(C = range.s.c; C <= range.e.c; ++C) {
     ref = cols[C] + rr;
     if(ws[ref] === undefined) continue;
     if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
   }
   if(r.length > 0){
  params = ({r:rr});
  if(rows && rows[R]) {
   row = rows[R];
   if(row.hidden) params.hidden = 1;
   height = -1;
   if (row.hpx) height = px2pt(row.hpx);
   else if (row.hpt) height = row.hpt;
   if (height > -1) { params.ht = height; params.customHeight = 1; }
   if (row.level) { params.outlineLevel = row.level; }
  }
  o[o.length] = (writextag('row', r.join(""), params));
 }
 }
if(rows) for(; R < rows.length; ++R) {
 if(rows && rows[R]) {
  params = ({r:R+1});
  row = rows[R];
  if(row.hidden) params.hidden = 1;
  height = -1;
  if (row.hpx) height = px2pt(row.hpx);
  else if (row.hpt) height = row.hpt;
  if (height > -1) { params.ht = height; params.customHeight = 1; }
  if (row.level) { params.outlineLevel = row.level; }
  o[o.length] = (writextag('row', "", params));
 }
}
 return o.join("");
}

在setExlStyle函数里增加设置行高的属性data[“!rows”]

		// 行高
        data["!rows"] = [{hpx: 40},{hpx: 40},{hpx: 40}]; 
        // 列宽
        data['!cols'] = [{wpx: 20},{wpx: 80},{wpx: 35},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},
          {wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55}];
        return data;

调整背景色

用到属性fill的bgColor。
COLOR_SPEC 指设定颜色的对象，取值如下：
{ rgb: “FFFFAA00” } 十六进制ARGB值
{ theme: “1”} 主题颜色的整数索引，默认是0。

更多内容参考： https://www.imooc.com/article/305180

4.xlxs-style属性

单元格属性

单元格样式