vue,js直接导出excel，xlsx的方法，XLSX_STYLE 行高设置失效的问题解决，冻结窗体修改支持

1、先安装依赖：xlsx、xlsx-style、file-saver三个包

npm install xlsx xlsx-style file-saver

出现错误：

This relative module was not found:
* ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js

解决：

找到node_modules/xlsx-style/dist/cpexcel.js文件搜索：

var cpt = require('./cpt' + 'able');

改成：
var cpt = cptable;

2、一个完整的实例：

<template>
  <div class="hello" style="text-align:center;padding:20px 10px;font-size:16px;">
    <p>Welcome to <a @click="exportExcel()">下载</a></p>
  </div>
</template>

<script>
import * as XLSX from 'xlsx/xlsx.mjs'
import XLSX_STYLE from 'xlsx-style';
import { saveAs } from 'file-saver';
export default {
  name: 'HelloWorld',
  data () {
    return {
      showData:[
        {type:1,createdTime:222,logistNo:"大课间分数段赛覅u奥菲娜啥的都能常偶第四节佛安抚巾哦说打就打覅欧派",note:"科诞节福利卡仕达付款单撒胡覅u阿鹅胡覅是的你师的话副科老师"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
        {type:1,createdTime:222,logistNo:3,note:"asdfads"},
      ]
    }
  },
  methods:{
    exportExcel() {
      const data = this.showData.map(item => {
        return {
          '类型': item.type,
          '订单日期': item.createdTime,
          '订单号': item.logistNo,
          '备注': item.note,
        }
      });
      // 定义表头样式
      const headerStyle = {
        fill: {
          fgColor: { rgb: '0070C0' },
        },
        font: {
          color: { rgb: 'ffffff' },
          name: '微软雅黑',
          sz: 11,
          bold:true
        },
        border: {
          top: { style: 'thin', color: { rgb: '000000' } },
          bottom: { style: 'thin', color: { rgb: '000000' } },
          left: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } },
        },
        alignment: {
          horizontal: 'center',
          vertical: 'center',
          wrapText: true,
        },
      };
      const contentStyle = {
        font: {
          name: '宋体',
          sz: 10,
        },
        border: {
          top: { style: 'thin', color: { rgb: '000000' } },
          bottom: { style: 'thin', color: { rgb: '000000' } },
          left: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } },
        },
        alignment: {
          horizontal: 'center',
          vertical: 'center',
          wrapText: true,
        },
      };

      const worksheet = XLSX.utils.json_to_sheet(data);
      var wscols = [{ wch: 30 }, { wch: 70 }, { wch: 30 }]
      //var wsrows = [{ hpt: 20 }, { hpt: 30 }]
      worksheet['!cols'] = wscols
      //worksheet['!rows'] = wsrows


      worksheet['!freeze'] = {
        xSplit: ""+1,  //冻结列
        ySplit: ""+1,  //冻结行
        //topLeftCell: "A2",  //在未冻结区域的左上角显示的单元格，默认为第一个未冻结的单元格
        activePane:"bottomRight",
        state: "frozen"
      }

      // 将表头样式应用到 worksheet 对象中的表头单元格
      const headerRange = XLSX.utils.decode_range(worksheet['!ref']);
      for (let col = headerRange.s.c; col <= headerRange.e.c; col++) {
        const headerCell = XLSX.utils.encode_cell({ r: headerRange.s.r, c: col });
        worksheet[headerCell].s = headerStyle;
      }

      // 将内容样式应用到 worksheet 对象中的所有单元格
      const contentRange = XLSX.utils.decode_range(worksheet['!ref']);
      for (let row = contentRange.s.r + 1; row <= contentRange.e.r; row++) {
        for (let col = contentRange.s.c; col <= contentRange.e.c; col++) {
          const contentCell = XLSX.utils.encode_cell({ r: row, c: col });
          worksheet[contentCell].s = contentStyle;
        }
      }
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, '数据导出');
      // 将 workbook 对象转换为二进制数据流并下载
      const wbout = XLSX_STYLE.write(workbook, { bookType:'xlsx',type:'binary'});
      const blob = new Blob([this.s2ab(wbout)],{type:'application/octet-stream'});
      saveAs(blob,'table.xlsx');
    },
    s2ab(s) {
      const buf = new ArrayBuffer(s.length);
      const view = new Uint8Array(buf);
      for (let i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xFF;
      }
      return buf;
    },
  },
  mounted:function(){
  },
}
</script>

问题解决1：

行高的设置不起作用，是因为XLSX_STYLE包里面没有这个方法，但是xlsx里面有：

\node_modules\xlsx-style\xlsx.js

里面找到：write_ws_xml_data 函数注释掉

用这个里面的函数替换：

\node_modules\xlsx\xlsx.js

function write_ws_xml_data(ws, opts, idx, wb) {......}

同时根据报错，一起加入：

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=0, C=0, rows = ws['!rows'];
    var dense = Array.isArray(ws);
    var params = ({r:rr}), row, height = -1;
    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;
        var _cell = dense ? (ws[R]||[])[C]: ws[ref];
        if(_cell === undefined) continue;
        if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
      }
      if(r.length > 0 || (rows && rows[R])) {
        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("");
  }

问题解决2：

冻结窗口不起作用，解决方法：

\node_modules\xlsx-style\xlsx.js 里面找到：

function write_ws_xml(idx, opts, wb)

注释掉：
//  o[o.length] = writextag('sheetViews', sheetView);

修改成：

var pane = null;
  var freeze = ws['!freeze'];
  // console.log(freeze)
  if (freeze !== undefined) {
    pane = writextag('pane', null, {
      xSplit: freeze.xSplit, // 冻结列
      ySplit: freeze.ySplit, // 冻结行
      topLeftCell: freeze.topLeftCell, // 在未冻结区域的左上角显示的单元格，默认为第一个未冻结的单元格
      activePane: freeze.activePane,
      state: freeze.state || 'frozen'
    })
  }
  console.log("ssss ok")
  o[o.length]=writextag("sheetViews", writextag("sheetView", pane, sheetView), {});

可惜msexcel打不开，还没找到解决办法。

本文借鉴内容来自：

https://www.cnblogs.com/gaoxiong666/p/17434607.html

https://blog.csdn.net/cs23405/article/details/133772940

posted on 2024-04-27 11:28 长虫山小木屋阅读(809) 评论(0) 编辑收藏举报

刷新页面返回顶部