xlsx 导出导入excel，xlsx-style 修改excel样式

首先npm下载两个依赖

npm install xlsx --save
npm install xlsx-style --save

还需要下载一个浏览器文件下载依赖

npm install file --save

引入xlsx-style会产生报错，需要在vue.config.js配置下
vue.config.js

module.exports = {
    chainWebpack(config) {
        config.externals({ './cptable': 'var cptable' })
    }
}

excel.js

/*
在这里统一进行处理，
页面上把数据传过来就行了
*/
import * as XLSX from 'xlsx'
import { saveAs } from 'file-saver'
import XLSXStyle from 'xlsx-style'
import XSU from './xlsxStyle.utils'
// 自动宽度计算
function AutoWidth(ws, arr) {
  // 设置worksheet每列的最大宽度
  const colWidth = arr.map(row => row.map(val => {
    // 判断是否为null/undefined
    if (val == null) {
      return { wch: 10 }
    } else if (val.toString().charCodeAt(0) > 255) { // 判断是否为中文
      return { wch: val.toString().length * 2 }
    } else {
      return { wch: val.toString().length }
    }
  }))
  // 以第一行为初始值
  const result = colWidth[0]
  for (let i = 1; i < colWidth.length; i++) {
    for (let j = 0; j < colWidth[i].length; j++) {
      if (result[j].wch < colWidth[i][j].wch) {
        result[j].wch = colWidth[i][j].wch
      }
    }
  }
  ws['!cols'] = result
}

// 数组转换成JSON
function formatJSON(key, data) {
  return data.map(v => key.map(i => { return v[i] }))
}

// 字符串转ArrayBuffer
function 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
}
/*
**将我的数据转换成xlsx需要的
*/
export function exportDataToExcelList(data,packageNamesIndex) {
	
  const header = [] // 头部表头，可以为空，和下面的key一一对应
  const key = ['productName', 'productCode', 'priceText', 'num', 'count']; // 表格各项对应key值  
  // data可以是这样的数据
  /*
	data = [
	{
	productName:'productName',
	productCode:'productCode',
	priceText:'priceText',
	num:'num',
	count:'count'
	}
	]
  */
  const params = {
    header,
    key,
    data,
    autoWidth: true, // 是否自动宽度
    fileName: '产品预算清单', // 文件名字
    bookType: 'xlsx', // 文件类型
  };
  // return
  exportDataToExcel(params,packageNamesIndex);
}
// 这个是业务逻辑，可以忽略
export function getPruductData(arr,count) {
  let result = []
  let packageNamesIndex = []
  // console.log(arr);
  const array = JSON.parse(JSON.stringify(arr))
  array.map((ele) => {
    if (ele.serveType.length) {
      ele.serveType.map((child, index) => {
        if (index === 0) {
          result.push({ productName: ele.packageName ,bold:true}); 
          packageNamesIndex.push(result.length)
          result.push({
            productName: '产品服务名称',
            productCode: '预算编码',
            priceText: '单价',
            num: '购买数量',
            count: '金额'
          })
        }
        if (child.productType.length) {
          child.productType.forEach((item) => {
            result.push(item)
          })
        }
      })
    }
  })
  result.push({
    num: '合计年预算',
    count
  })
  // console.log(JSON.stringify(arr));
  // return
  exportDataToExcelList(result,packageNamesIndex)
  return result
}
// 导出EXCEL表格
export function exportDataToExcel(
  {
    header, // 表头名数组
    key, // 列对应字段数组
    data, // 需要导出数据的数组
    fileName, // 导出文件名
    autoWidth = true, // 是否自动宽度
    bookType = 'xlsx' // 导出文件格式
  } = {},packageNamesIndex) {
  // 创建Workbook对象
  const wb = XLSX.utils.book_new()
  const arr = formatJSON(key, data)
  fileName = fileName || 'excel-list'
  // arr.unshift(header)
  // 将数组数据转换为worksheet
  const ws = XLSX.utils.aoa_to_sheet(arr)
  if (autoWidth) {
    AutoWidth(ws, arr)
  }
    // 向Workbook对象中追加worksheet和fileName
  XLSX.utils.book_append_sheet(wb, ws, fileName)
  // 调用样式处理方法
  const wbStyle= utilsTest(wb,packageNamesIndex)
  // 生成EXCEL的配置项
  // 这里调用XLSXStyle 来调整excel样式
  const wbout = XLSXStyle.write(wbStyle, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  })
  // return
  // 浏览器下载
  saveAs(new Blob([s2ab(wbout)], {
    type: 'application/octet-stream'
  }), `${fileName}.${bookType}`)
}
// 表格样式编辑
function utilsTest(wb,packageNamesIndex=[]) {
  packageNamesIndex?.forEach(ele=>{
    XSU.setFontBold(wb,wb.SheetNames[0],`A${ele}`,true) 
  })
  XSU.setAlignmentWrapTextAll(wb,wb.SheetNames[0],true)
  XSU.setFontTypeAll(wb,wb.SheetNames[0],'等线')
  XSU.setAlignmentHorizontalAll(wb,wb.SheetNames[0],'center')
  XSU.setAlignmentVerticalAll(wb,wb.SheetNames[0],'center')
  XSU.setFontSizeAll(wb,wb.SheetNames[0],11)
  return wb
}
// 从Excel文件中获取表格头
function getHeaderRow(sheet) {
  const headers = []
  // 将 A1:G8 这种字符串转换为行列对象
  const range = XLSX.utils.decode_range(sheet['!ref'])
  let C
  const R = range.s.r
  // 从第一列开始，遍历范围中的每一列
  for (C = range.s.c; C <= range.e.c; ++C) {
    // 将行列对象转换为 A1 这种字符串
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
    // 用默认值替换
    let hdr = 'UNKNOWN ' + C
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
    headers.push(hdr)
  }
  return headers
}

// 读取Excel文件
export function readDataFromExcel(data, type) {
  // 读取Excel文件并保存到Workbook对象
  const workbook = XLSX.read(data, { type: type })
  const firstSheetName = workbook.SheetNames[0]
  // 获取Workbook对象的worksheet
  const worksheet = workbook.Sheets[firstSheetName]
  const header = getHeaderRow(worksheet)
  // 将worksheet转化成数组
  const results = XLSX.utils.sheet_to_json(worksheet)
  return { header, results }
}

export default {
  exportDataToExcel,
  readDataFromExcel,
  exportDataToExcelList,
  getPruductData,
  }

xlsxStyle.utils

/*
@author Ctrl
@version 20190917
@aim 对xlsx-style方法进行二次封装 方便调用以导出带样式Excel
@usage XSU.xxxFunction()
*/


var XSU;

XSU=({

	//字符串转字符流
	s2ab: function(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;
	},

	//初始化
	init: function(workBook, sheetName, cell) {
		if (!workBook.Sheets[sheetName][cell].s) {
			workBook.Sheets[sheetName][cell].s = {};
		}
	},

	init1: function(workBook, sheetName, cell, attr) {
		this.init(workBook, sheetName, cell);
		if (!workBook.Sheets[sheetName][cell].s[attr]) {
			workBook.Sheets[sheetName][cell].s[attr] = {};
		}
	},

	init2: function(workBook, sheetName, cell, attr1, attr2) {
		this.init(workBook, sheetName, cell);
		this.init1(workBook, sheetName, cell, attr1);
		if (!workBook.Sheets[sheetName][cell].s[attr1][attr2]) {
			workBook.Sheets[sheetName][cell].s[attr1][attr2] = {};
		}
	},

	//根据ref的单元格范围新建范围内所有单元格,不存在的单元格置为空值,已存在的不处理
	initAllCell: function(workBook, sheetName) {
		var ref = workBook.Sheets[sheetName]["!ref"].split(":");
		var startCell = ref[0];
		var endCell = ref[1];
		var sc = XLSX.utils.decode_cell(startCell).c;
		var sr = XLSX.utils.decode_cell(startCell).r;
		var ec = XLSX.utils.decode_cell(endCell).c;
		var er = XLSX.utils.decode_cell(endCell).r;
		var isExist;
		for(c = sc; c <= ec; c++) { //初始化所有单元格
			for(r = sr; r <= er; r++) {
				var temp = XLSX.utils.encode_cell({
					c: c,
					r: r
				});
				isExist = false;
				for(cell in workBook.Sheets[sheetName]) {
					if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
						if (temp == cell) {
							isExist = true;
							break;
						}
					}
				}
				if (!isExist) { //单元格不存在则新建单元格
					XLSX.utils.sheet_add_aoa(workBook.Sheets[sheetName], [
						['']
					], {
						origin: temp
					});
				}

			}
		}
	},

	//单元格合并 startCell=A1 endCell=B5
	mergeCells: function(workBook, sheetName, startCell, endCell) {
		/*var sc = startCell.substr(0, 1).charCodeAt(0) - 65;
		var sr = startCell.substr(1);
		sr = parseInt(sr) - 1;
		var ec = endCell.substr(0, 1).charCodeAt(0) - 65;
		var er = endCell.substr(1)
		er = parseInt(er) - 1;*/

		var sc = XLSX.utils.decode_cell(startCell).c;
		var sr = XLSX.utils.decode_cell(startCell).r;
		var ec = XLSX.utils.decode_cell(endCell).c;
		var er = XLSX.utils.decode_cell(endCell).r;

		var merges = [{
			s: { //s start 始单元格
				c: sc, //cols 开始列
				r: sr //rows 开始行
			},
			e: { //e end  末单元格
				c: ec, //cols 结束列
				r: er //rows 结束行
			}
		}];
		if (!workBook.Sheets[sheetName]["!merges"]) {
			workBook.Sheets[sheetName]["!merges"] = merges;
		} else {
			workBook.Sheets[sheetName]["!merges"] = workBook.Sheets[sheetName]["!merges"].concat(merges);
		}

		return workBook;

	},

	//merges=[{s: {c: 0, r: 0},e: {c: 3, r: 0}}]
	mergeCellsByObj: function(workBook, sheetName, merges) {
		if (workBook.Sheets[sheetName]["!merges"]) {
			workBook.Sheets[sheetName]["!merges"] = workBook.Sheets[sheetName]["!merges"].concat(merges);
		} else {
			workBook.Sheets[sheetName]["!merges"] = merges;
		}

		return workBook;

	},


	//设置每列列宽,单位px cols= [{wpx: 45}, {wpx: 165}, {wpx: 45}, {wpx: 45}]
	setColWidth: function(workBook, sheetName, cols) {
		workBook.Sheets[sheetName]["!cols"] = cols;
		return workBook;
	},

	//一次性设置多样式 styles etc： 
	/*
	{
	  "font": {
	    "sz": 14,
	    "bold": true,
	    "color": {
	      "rgb": "FFFFAA00"
	    }
	  },
	  "fill": {
	    "bgColor": {
	      "indexed": 64
	    },
	    "fgColor": {
	      "rgb": "FFFFFF00"
	    }
	  }
	}
	*/
	setCellStyle: function(workBook, sheetName, cell, styles) {
		workBook.Sheets[sheetName][cell].s = styles;
		return workBook;
	},

	/*Fill*/

	//填充颜色 fill={"bgColor": {"indexed": 64},"fgColor": {"rgb": "FFFFFF00"}}
	setFillStyles: function(workBook, sheetName, cell, styles) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.fill = styles;
		return workBook;
	},

	setFillStylesAll: function(workBook, sheetName, styles) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillStyles(workBook, sheetName, cell, styles);
			}
		}
	},

	//patternType="solid" or "none"”
	setFillPatternType: function(workBook, sheetName, cell, patternType) {
		this.init1(workBook, sheetName, cell, "fill");
		workBook.Sheets[sheetName][cell].s.fill.patternType = patternType;
		return workBook;
	},

	setFillPatternTypeAll: function(workBook, sheetName, patternType) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillPatternType(workBook, sheetName, cell, patternType);
			}
		}
	},

	//前景颜色(单元格颜色) rgb 
	//COLOR_SPEC属性值:{ auto: 1}指定自动值,{ rgb: "FFFFAA00" }指定16进制的ARGB,{ theme: "1", tint: "-0.25"}指定主题颜色和色调的整数索引（默认值为0）,{ indexed: 64} 默认值 fill.bgColor
	setFillFgColor: function(workBook, sheetName, cell, COLOR_SPEC) {
		this.init1(workBook, sheetName, cell, "fill");
		workBook.Sheets[sheetName][cell].s.fill.fgColor = COLOR_SPEC;
		return workBook;
	},

	setFillFgColorAll: function(workBook, sheetName, COLOR_SPEC) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillFgColor(workBook, sheetName, cell, COLOR_SPEC);
			}
		}
	},

	//使用RGB值设置颜色
	setFillFgColorRGB: function(workBook, sheetName, cell, rgb) {
		this.init2(workBook, sheetName, cell, "fill", "fgColor");
		workBook.Sheets[sheetName][cell].s.fill.fgColor.rgb = rgb;
		return workBook;
	},

	setFillFgColorRGBAll: function(workBook, sheetName, rgb) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillFgColorRGB(workBook, sheetName, cell, rgb);
			}
		}
	},

	//单元格背景颜色（貌似没用）
	setFillBgColor: function(workBook, sheetName, cell, COLOR_SPEC) {
		this.init1(workBook, sheetName, cell, "fill");
		workBook.Sheets[sheetName][cell].s.fill.bgColor = COLOR_SPEC;
		return workBook;
	},

	setFillBgColorAll: function(workBook, sheetName, COLOR_SPEC) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillBgColor(workBook, sheetName, cell, COLOR_SPEC);
			}
		}
	},

	//单元格背景颜色
	setFillBgColorRGB: function(workBook, sheetName, cell, rgb) {
		this.init2(workBook, sheetName, cell, "fill", "bgColor");
		workBook.Sheets[sheetName][cell].s.fill.bgColor.rgb = rgb;
		return workBook;
	},

	setFillBgColorRGBAll: function(workBook, sheetName, rgb) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFillBgColorRGB(workBook, sheetName, cell, rgb);
			}
		}
	},

	/*Font*/

	//字体风格，可一次性在styles中设置所有font风格
	setFontStyles: function(workBook, sheetName, cell, styles) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.font = styles;
		return workBook;
	},

	setFontStylesAll: function(workBook, sheetName, styles) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontStyles(workBook, sheetName, cell, styles);
			}
		})
	},

	//字体 type="Calibri" 
	setFontType: function(workBook, sheetName, cell, type) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.name = type;
		return workBook;
	},

	setFontTypeAll: function(workBook, sheetName, type) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontType(workBook, sheetName, cell, type);
			}
		})
	},

	//字体大小
	setFontSize: function(workBook, sheetName, cell, size) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.sz = size;
		return workBook;
	},

	setFontSizeAll: function(workBook, sheetName, size) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontSize(workBook, sheetName, cell, size);
			}
		})
	},

	//字体颜色 COLOR_SPEC
	setFontColor: function(workBook, sheetName, cell, COLOR_SPEC) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.color = COLOR_SPEC;
		//workBook.Sheets[sheetName][cell].s.font.color.rgb = rgb;
		return workBook;
	},

	setFontColorAll: function(workBook, sheetName, COLOR_SPEC) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontColor(workBook, sheetName, cell, COLOR_SPEC);
			}
		}
	},

	//字体颜色RGB
	setFontColorRGB: function(workBook, sheetName, cell, rgb) {
		this.init2(workBook, sheetName, cell, "font", "color");
		workBook.Sheets[sheetName][cell].s.font.color.rgb = rgb;
		return workBook;
	},

	setFontColorRGBAll: function(workBook, sheetName, rgb) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontColorRGB(workBook, sheetName, cell, rgb);
			}
		}
	},

	//是否粗体 boolean isBold
	setFontBold: function(workBook, sheetName, cell, isBold) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.bold = isBold;
		return workBook;
	},

	setFontBoldAll: function(workBook, sheetName, isBold) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontBold(workBook, sheetName, cell, isBold);
			}
		}
	},

	//设置某列为粗体
	setFontBoldOfCols: function(workBook, sheetName, isBold, col) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				if (cell.substr(0, 1) == col) {
					this.setFontBold(workBook, sheetName, cell, isBold);
				}
			}
		}
	},

	//设置某行为粗体
	setFontBoldOfRows: function(workBook, sheetName, isBold, row) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				if (cell.substr(1) == row) {
					this.setFontBold(workBook, sheetName, cell, isBold);
				}
			}
		}
	},

	//是否下划线 boolean isUnderline
	setFontUnderline: function(workBook, sheetName, cell, isUnderline) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.underline = isUnderline;
		return workBook;
	},

	setFontUnderlineAll: function(workBook, sheetName, isUnderline) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontUnderline(workBook, sheetName, cell, isUnderline);
			}
		}
	},

	//是否斜体 boolean isItalic
	setFontItalic: function(workBook, sheetName, cell, isItalic) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.italic = isItalic;
		return workBook;
	},

	setFontItalicAll: function(workBook, sheetName, isItalic) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontItalic(workBook, sheetName, cell, isItalic);
			}
		}
	},

	//是否删除线 boolean isStrike
	setFontStrike: function(workBook, sheetName, cell, isStrike) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.strike = isStrike;
		return workBook;
	},

	setFontStrikeAll: function(workBook, sheetName, isStrike) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontStrike(workBook, sheetName, cell, isStrike);
			}
		}
	},

	//是否outline boolean isOutline
	setFontOutline: function(workBook, sheetName, cell, isOutline) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.outline = isOutline;
		return workBook;
	},

	setFontOutlineAll: function(workBook, sheetName, isOutline) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontOutline(workBook, sheetName, cell, isOutline);
			}
		}
	},

	//是否阴影 boolean isShadow
	setFontShadow: function(workBook, sheetName, cell, isShadow) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.shadow = isShadow;
		return workBook;
	},

	setFontShadowAll: function(workBook, sheetName, isShadow) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontShadow(workBook, sheetName, cell, isShadow);
			}
		}
	},

	//是否vertAlign boolean isVertAlign
	setFontVertAlign: function(workBook, sheetName, cell, isVertAlign) {
		this.init1(workBook, sheetName, cell, "font");
		workBook.Sheets[sheetName][cell].s.font.vertAlign = isVertAlign;
		return workBook;
	},

	setFontVertAlignAll: function(workBook, sheetName, isVertAlign) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontVertAlign(workBook, sheetName, cell, isVertAlign);
			}
		}
	},

	/*numFmt*/

	setNumFmt: function(workBook, sheetName, cell, numFmt) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.numFmt = numFmt;
		return workBook;
	},

	setNumFmtAll: function(workBook, sheetName, numFmt) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setNumFmt(workBook, sheetName, cell, numFmt);
			}
		}
	},

	/*Alignment*/

	//文本对齐 alignment={vertical:top,horizontal:top,}
	setAlignmentStyles: function(workBook, sheetName, cell, styles) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.alignment = styles;
		return workBook;
	},

	setAlignmentStylesAll: function(workBook, sheetName, styles) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentStyles(workBook, sheetName, cell, styles);
			}
		})
	},

	//文本垂直对齐 vertical	="bottom" or "center" or "top"
	setAlignmentVertical: function(workBook, sheetName, cell, vertical) {
		this.init1(workBook, sheetName, cell, "alignment");
		workBook.Sheets[sheetName][cell].s.alignment.vertical = vertical;
		return workBook;
	},

	setAlignmentVerticalAll: function(workBook, sheetName, vertical) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentVertical(workBook, sheetName, cell, vertical);
			}
		})
	},

	//文本水平对齐 "bottom" or "center" or "top"
	setAlignmentHorizontal: function(workBook, sheetName, cell, horizontal) {
		this.init1(workBook, sheetName, cell, "alignment");
		workBook.Sheets[sheetName][cell].s.alignment.horizontal = horizontal;
		return workBook;
	},

	setAlignmentHorizontalAll: function(workBook, sheetName, horizontal) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentHorizontal(workBook, sheetName, cell, horizontal);
			}
		})
	},

	//自动换行
	setAlignmentWrapText: function(workBook, sheetName, cell, isWrapText) {
		this.init1(workBook, sheetName, cell, "alignment");
		workBook.Sheets[sheetName][cell].s.alignment.wrapText = isWrapText;
		return workBook;
	},

	setAlignmentWrapTextAll: function(workBook, sheetName, isWrapText) {
		Object.keys(workBook.Sheets[sheetName]).forEach(cell=>{
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentWrapText(workBook, sheetName, cell, isWrapText);
			}
		})
	},

	setAlignmentReadingOrder: function(workBook, sheetName, cell, readingOrder) {
		this.init1(workBook, sheetName, cell, "alignment");
		workBook.Sheets[sheetName][cell].s.alignment.readingOrder = readingOrder;
		return workBook;
	},

	setAlignmentReadingOrderAll: function(workBook, sheetName, readingOrder) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentReadingOrder(workBook, sheetName, cell, readingOrder);
			}
		}
	},

	//文本旋转角度 0-180，255 is special, aligned vertically
	setAlignmentTextRotation: function(workBook, sheetName, cell, textRotation) {
		this.init1(workBook, sheetName, cell, "alignment");
		workBook.Sheets[sheetName][cell].s.alignment.textRotation = textRotation;
		return workBook;
	},

	setAlignmentTextRotationAll: function(workBook, sheetName, textRotation) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setAlignmentTextRotation(workBook, sheetName, cell, textRotation);
			}
		}
	},

	/*Border*/

	//单元格四周边框默认样式
	borderAll :{
		top: {
			style: 'thin'
		},
		bottom: {
			style: 'thin'
		},
		left: {
			style: 'thin'
		},
		right: {
			style: 'thin'
		}
	},

	//边框默样式：细线黑色
	defaultBorderStyle : {
		style: 'thin'
	},

	//边框 styles={top:{ style:"thin",color:"FFFFAA00"},bottom:{},...}
	setBorderStyles: function(workBook, sheetName, cell, styles) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.border = styles;
		return workBook;
	},

	setBorderStylesAll: function(workBook, sheetName, styles) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderStyles(workBook, sheetName, cell, styles);
			}
		}
	},

	//设置单元格上下左右边框默认样式
	setBorderDefault: function(workBook, sheetName, cell) {
		this.init(workBook, sheetName, cell);
		workBook.Sheets[sheetName][cell].s.border = this.borderAll;
		return workBook;
	},

	//设置所有单元默认格边框
	setBorderDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderDefault(workBook, sheetName, cell);
			}
		}
	},

	//上边框
	setBorderTop: function(workBook, sheetName, cell, top) {
		this.init(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.top = top;
		return workBook;
	},

	setBorderTopAll: function(workBook, sheetName, top) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderTop(workBook, sheetName, cell, top);
			}
		}
	},

	//上边框默样式
	setBorderTopDefault: function(workBook, sheetName, cell) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.top = this.defaultBorderStyle;
		return workBook;
	},

	setBorderTopDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderTopDefault(workBook, sheetName, cell);
			}
		}
	},

	//下边框
	setBorderBottom: function(workBook, sheetName, cell, bottom) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.bottom = bottom;
		return workBook;
	},

	setBorderBottomAll: function(workBook, sheetName, bottom) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderBottom(workBook, sheetName, cell, bottom);
			}
		}
	},


	//下边框默样式
	setBorderBottomDefault: function(workBook, sheetName, cell) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.bottom = this.defaultBorderStyle;
		return workBook;
	},

	setBorderBottomDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderBottomDefault(workBook, sheetName, cell);
			}
		}
	},

	//左边框
	setBorderLeft: function(workBook, sheetName, cell, left) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.left = left;
		return workBook;
	},

	setBorderLeftAll: function(workBook, sheetName, left) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderLeft(workBook, sheetName, cell, left);
			}
		}
	},

	setBorderLeftDefault: function(workBook, sheetName, cell) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.left = this.defaultBorderStyle;
		return workBook;
	},

	setBorderLeftDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderLeftDefault(workBook, sheetName, cell);
			}
		}
	},

	//右边框
	setBorderRight: function(workBook, sheetName, cell, right) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.right = right;
		return workBook;
	},

	setBorderRightAll: function(workBook, sheetName, right) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderRight(workBook, sheetName, cell, right);
			}
		}
	},
	setBorderRightDefault: function(workBook, sheetName, cell) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.right = this.defaultBorderStyle;
		return workBook;
	},

	setBorderRightDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderRightDefault(workBook, sheetName, cell);
			}
		}
	},

	//对角线
	setBorderDiagonal: function(workBook, sheetName, cell, diagonal) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.diagonal = diagonal;
		return workBook;
	},

	setBorderDiagonalAll: function(workBook, sheetName, diagonal) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderDiagonal(workBook, sheetName, cell, diagonal);
			}
		}
	},

	setBorderDiagonalDefault: function(workBook, sheetName, cell) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.diagonal = this.defaultBorderStyle;
		return workBook;
	},

	setBorderDiagonalDefaultAll: function(workBook, sheetName) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderDiagonalDefault(workBook, sheetName, cell);
			}
		}
	},

	setBorderDiagonalUp: function(workBook, sheetName, cell, isDiagonalUp) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.diagonalUp = isDiagonalUp;
		return workBook;
	},

	setBorderDiagonalUpAll: function(workBook, sheetName, isDiagonalUp) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderDiagonalUp(workBook, sheetName, cell, isDiagonalUp);
			}
		}
	},

	setBorderDiagonalDown: function(workBook, sheetName, cell, isDiagonalDown) {
		this.init1(workBook, sheetName, cell, "border");
		workBook.Sheets[sheetName][cell].s.border.diagonalDown = isDiagonalDown;
		return workBook;
	},

	setBorderDiagonalDownAll: function(workBook, sheetName, isDiagonalDown) {
		this.initAllCell(workBook, sheetName);
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setBorderDiagonalDown(workBook, sheetName, cell, isDiagonalDown);
			}
		}
	},

	//默认样式，多单元格设置样式

	//设置所有单元格字体样式
	setFgColorStylesAll: function(workBook, sheetName, fontType, fontColor, fontSize) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				this.setFontType(workBook, sheetName, cell, fontType);
				this.setFontColorRGB(workBook, sheetName, cell, fontColor);
				this.setFontSize(workBook, sheetName, cell, fontSize);
			}
		}
	},

	//设置第一行标题自定义样式
	setTitleStyles: function(workBook, sheetName, fgColor, fontColor, alignment, isBold, fontSize, ) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				row = cell.substr(1);
				if (row == '1') {
					this.setFillFgColorRGB(workBook, sheetName, cell, fgColor);
					this.setFontColor(workBook, sheetName, cell, fontColor);
					this.setAlignmentHorizontal(workBook, sheetName, cell, alignment);
					this.setFontBold(workBook, sheetName, cell, isBold);
					this.setFontSize(workBook, sheetName, cell, fontSize);
				}
			}
		}
	},

	//设置第一行标题默认样式
	setTitleStylesDefault: function(workBook, sheetName) {
		for(cell in workBook.Sheets[sheetName]) {
			row = cell.substr(1);
			if (row == '1') {
				//setFillFgColorRGB(workBook, sheetName, cell, 'FFFF00');
				this.setAlignmentHorizontal(workBook, sheetName, cell, 'center');
				this.setFontBold(workBook, sheetName, cell, true);
				this.setFontSize(workBook, sheetName, cell, '20');
			}
		}
	},

	//设置双数行背景色灰色，便于阅读
	setEvenRowColorGrey: function(workBook, sheetName) {
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				row = parseInt(cell.substr(1));
				if (row % 2 == 0) {
					this.setFillFgColorRGB(workBook, sheetName, cell, 'DCDCDC');
				}

			}
		}
	},

	//合并同一列中内容一样的相邻行
	mergeSameColCells: function(workBook,sheetName,col) {
		var cells=[];
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				if (cell.substr(0, 1) == col) {
					cells.push(cell);//获得该列单元格数组，升序
				}
			}
		}
		for(var i = 0;i<cells.length-1;){
			for(var j=i+1;j<cells.length;j++){
				//内容一样且不为空则合并
				if(workBook.Sheets[sheetName][cells[i]].v == workBook.Sheets[sheetName][cells[j]].v && workBook.Sheets[sheetName][cells[i]].v != ""){
					this.mergeCells(workBook,sheetName,cells[i],cells[j]);
					if(j==cells.length-1){
						i=j;
					}
				}
				else{	//当且仅当相邻的两个cell值相同时才合并
					i=j;
					break;
				}
			}
		}
	},

	//合并同一行中内容一样的相邻列
	mergeSameRowCells: function(workBook,sheetName,row) {
		var cells=[];
		for(cell in workBook.Sheets[sheetName]) {
			if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
				if (cell.substr(1) == row) {
					cells.push(cell);//获得该列单元格数组，升序
				}
			}
		}
		for(var i = 0;i<cells.length-1;){
			for(var j=i+1;j<cells.length;j++){
				if(workBook.Sheets[sheetName][cells[i]].v == workBook.Sheets[sheetName][cells[j]].v){
					this.mergeCells(workBook,sheetName,cells[i],cells[j]);
					if(j==cells.length-1){
						i=j;
					}
				}
				else{	//当且仅当相邻的两个cell值相同时才合并
					i=j;
					break;
				}
			}
		}
	},

	//当前表格最大行数 return int
	getMaxRow: function(workBook,sheetName) {
		var length = 0;
		for(var ever in workBook.Sheets[sheetName]) {
			temp = parseInt(ever.substr(1));
			if (temp > length) {
				length = temp;
			}
		}
		return length;
	},

	//当前表格最大列数 A起步 return string
	getMaxCol: function(workBook,sheetName) {
		var length = 'A';
		for(var ever in workBook.Sheets[sheetName]) {
			temp = ever.substr(0, 1);
			if (temp > length) {
				length = temp;
			}
		}
		return length;
	},

})

export default XSU