rhb-server/mes-ui/rhb-app/common/exceltool.js

import * as XLSX from 'xlsx'
import FileSaver from 'file-saver';
import XLSXStyle from 'xlsx-style-medalsoft';
 
let OMS = {};
 
// 导出Excel-单表格Excel  带样式
OMS.downLoadXlsx = ({ dom = 'el-table', name = '文件', ColumnWdth = [] }) => {
  let table = document.getElementById(dom);
  // 因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉
  if (table.querySelector('.el-table__fixed')) {
    table.removeChild(table.querySelector('.el-table__fixed'));
  }
  if (table.querySelector('.el-table__fixed-right')) {
    table.removeChild(table.querySelector('.el-table__fixed-right'));
  }
 
  let et = XLSX.utils.table_to_book(table, { raw: true }); //此处传入table的DOM节点,raw为true表示单元格为文本格式(未加工)
  let wbs = et.Sheets.Sheet1;
  // 删掉末尾空行
  Object.keys(wbs).forEach((item, index) => {
	
    if (!item.startsWith('!') && wbs[item].v == '') {
      delete wbs[item];
    }
  });
 
  //设置表格列宽度
  if (ColumnWdth.length === 0) {
    for (let i = 0; i < 30; i++) {
      wbs['!cols'][i] = { wch: 12.5 };
    }
  } else {
    ColumnWdth.forEach((item, i) => {
		console.log(i)
		if(i==0){ wbs['!rows'][i] = { hpt: 69.75 };}
		else if(i==4){ wbs['!rows'][i] = { hpt: 33 };}
		// else{
		// 	const value = cell.v;
		// 	const length = value.length || 0;
		// 	const newHeight = Math.ceil(length / 10) + 1; // 每10个字符设置行高加1
		// 	worksheet[XLSX.utils.encode_row(row)].h = newHeight * 20; // 假设单位是EMU(English Metric Unit)
		// }
      wbs['!cols'][i] = { wch: item };
    });
  }
  //循环遍历每一个表格，设置样式
  for (const key in wbs) {
	if(key == 'A1'){
		wbs[key].v =wbs[key].v.replaceAll(" ","\r\n");
		 
		wbs[key].s = {
		  font: {
		    sz: 11, //字体大小
		    bold: false, //加粗
		    name: '宋体', //字体
		    color: {
		      rgb: '000000', //十六进制，不带#
		    },
		  },
		  alignment: {
		    //文字居中
		    horizontal: 'left',
		    vertical: 'center',
		    wrapText: true, //文本自动换行
		  },
		  border: {
		    // 设置边框
		    top: { style: 'thin' },
		    bottom: { style: 'thin' },
		    left: { style: 'thin' },
		    right: { style: 'thin' },
		  },
		};
	}else if(key == 'A2'){
		wbs[key].s = {
		  font: {
		    sz: 16, //字体大小
		    bold: true, //加粗
		    name: '宋体', //字体
		    color: {
		      rgb: '000000', //十六进制，不带#
		    },
		  },
		  alignment: {
		    //文字居中
		    horizontal: 'center',
		    vertical: 'center',
		    wrapText: false, //文本自动换行
		  },
		  border: {
		    // 设置边框
		    top: { style: 'thin' },
		    bottom: { style: 'thin' },
		    left: { style: 'thin' },
		    right: { style: 'thin' },
		  },
		};
	}
	else if (!key.startsWith('!')) {
      wbs[key].s = {
        font: {
          sz: 11, //字体大小
          bold: false, //加粗
          name: '宋体', //字体
          color: {
            rgb: '000000', //十六进制，不带#
          },
        },
        alignment: {
          //文字居中
          horizontal: 'center',
          vertical: 'center',
          wrapText: false, //文本自动换行
        },
        border: {
          // 设置边框
          top: { style: 'thin' },
          bottom: { style: 'thin' },
          left: { style: 'thin' },
          right: { style: 'thin' },
        },
      };
    }
  }
 
  const arr = [
    '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',
  ];
 
  // 行列合并
  let range = wbs['!merges'];
  if (range) {
    range.forEach((item) => {
      let startColNumber = Number(item.s.r),
        endColNumber = Number(item.e.r);
      let startRowNumber = Number(item.s.c),
        endRowNumber = Number(item.e.c);
      const test = wbs[arr[startRowNumber] + (startColNumber + 1)];
      for (let col = startColNumber; col <= endColNumber; col++) {
        for (let row = startRowNumber; row <= endRowNumber; row++) {
          wbs[arr[row] + (col + 1)] = test;
        }
      }
    });
  }
 
  let etout = XLSXStyle.write(et, {
    bookType: 'xlsx',
    type: 'buffer',
  });
  try {
    FileSaver.saveAs(new Blob([etout], { type: 'application/octet-stream' }), `${name}.xlsx`); //导出的文件名
  } catch (e) {
    throw e;
  }
};
 
export default OMS;