前言
熟悉luckysheet的小伙伴都知道，这是一个纯前端实现的在线execl文档编辑器；目前官方没有给出导出的方案以及api，只能自己写导出的逻辑，话不多说，直接上代码
实现功能
封装了通用utils，里面暴露了两个方法：downloadExcel、getFile；
实现了包括批注、函数、图片、公式、单元格格式、超链接、视图的导出
downloadExcel
将luckysheet数据导出为execl文件
getFile
将luckysheet数据格式转为file文件
准备工作
- 安装 exceljs
npm i exceljs
- 安装big.js
npm i big.js
实现逻辑
export.js
import ExcelJS from 'exceljs';
import Big from 'big.js'
import { setConditions } from './export/setConditions'
import { createCellRange } from './export/utils'
// 默认的xlsx type
export const XLSX_BLOB_TYPE =
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8';
// 默认的excel高度，单元格宽高默认写死了，避免数据量大时，导出会导致浏览器奔溃问题
const DEFAULT_ROW_HEIGHT = 19;
const DEFAULT_EXCEL_ROW_HEIGHT = 14;
/**
* @description: luckyshell 导出
* @param {Array} tableArr luckyshell数据对象，通过 luckysheet.getluckysheetfile() 获取
* @param {Object} params 其他配置
* @param {String} params.name 导出的文件名
* @return {Promise} 导出 buffer 函数，可以通过 .then .catch 去做业务
*/
export default function exportSheetExcel(
tableArr = [],
params = { name: '导出数据' }
) {
console.time();
const { name } = params;
// 1.创建工作簿，可以为工作簿添加属性
const workbook = new ExcelJS.Workbook();
// 2.创建表格，第二个参数可以配置创建什么样的工作表
tableArr.forEach(function (thesheet) {
if (thesheet.data.length === 0) return true;
const worksheet = workbook.addWorksheet(thesheet.name);
// 设置单元格样式
setStyleAndValue(thesheet.data, worksheet);
// 设置单元格合并
setMerge(thesheet.config.merge, worksheet);
// 设置单元格边框
setBorder(thesheet, worksheet);
// 设置图片
setImages(thesheet, worksheet, workbook);
// 设置批注
setNote(thesheet.data, worksheet);
// 设置超链接
setHyperlink(thesheet.hyperlink, worksheet)
// 冻结视图
setFrozen(thesheet.frozen,worksheet)
// 条件格式
setConditions(thesheet.luckysheet_conditionformat_save, worksheet)
// 数据筛选
setFilter(thesheet.filter_select, worksheet)
// 数据透视图
setScenograph(thesheet.isPivotTable, worksheet)
return true;
});
console.timeEnd();
// 4.写入 buffer
const getBuffer = () => {
return new Promise(resolve => {
workbook.xlsx.writeBuffer().then(data => {
const buffer = new Blob([data], {
type: XLSX_BLOB_TYPE
});
resolve(buffer);
});
});
};
// 转换导出名称
const transFileName = name => {
if (name.indexOf('.xlsx') === -1 || name.indexOf('.xls') === -1) {
return name + '.xlsx';
}
return name;
};
// 导出为excel
const downloadExcel = async () => {
const buffer = await getBuffer();
return new Promise((resolve, reject) => {
try {
let blob = new Blob([buffer], {
type: XLSX_BLOB_TYPE
});
const downloadElement = document.createElement('a');
let href = window.URL.createObjectURL(blob);
downloadElement.href = href;
downloadElement.download = transFileName(name); // 文件名字
document.body.appendChild(downloadElement);
downloadElement.click();
document.body.removeChild(downloadElement); // 下载完成移除元素
window.URL.revokeObjectURL(href); // 释放掉blob对象
resolve();
} catch (error) {
reject(error);
}
});
};
// 转换为file文件
const getFile = async () => {
const buffer = await getBuffer();
return new Promise(resolve => {
const blob = new Blob([buffer], {
type: XLSX_BLOB_TYPE
});
const file = new File(
[blob],
name,
{
type: XLSX_BLOB_TYPE
}
);
resolve({
raw: file,
blob
});
});
};
return {
downloadExcel,
getFile
};
}
var letterArr = (function () {
const CHARCODE_A_UC = 65;
const uppercaseAlphabet = new Array(26)
.fill(null)
.map((v, i) => String.fromCharCode(CHARCODE_A_UC + i));
return uppercaseAlphabet;
})();
// 数据筛选
var setFilter = function(filter,worksheet){
if(!filter) return
worksheet.autoFilter = createCellRange(filter.row,filter.column)
}
// 数据透视图
var setScenograph = function (isPivotTables, worksheet) {
if(!isPivotTables) return;
worksheet.pivotTables = true;
let rmax = 0;
let cmax = 0;
//得到行与列的最大值
thesheet.celldata.forEach(itemCell => {
if (rmax < itemCell.r) rmax = itemCell.r;
if (cmax < itemCell.c) cmax = itemCell.c;
});
// 循环遍历添加边框
for (let i = 0; i <= rmax; i++) {
for (let j = 0; j <= cmax; j++) {
// 添加边框
worksheet.getCell(i + 1, j + 1).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
};
}
}
};
// 冻结视图
var setFrozen = function(frozen,worksheet){
//不存在冻结或取消冻结，则不执行后续代码
if(!frozen||frozen.type=='cancel') return
//执行冻结操作代码
let views = []
switch (frozen.type) {
//冻结首行
case 'row':
views = [
{state: 'frozen', xSplit: 0, ySplit: 1}
];
break;
//冻结首列
case 'column':
views = [
{state: 'frozen', xSplit: 1, ySplit: 0}
];
break;
//冻结首行首列
case 'both':
views = [
{state: 'frozen', xSplit: 1, ySplit: 1}
];
break;
//冻结行至选区
case 'rangeRow':
views = [
{state: 'frozen', xSplit: 0, ySplit: frozen.range.row_focus+1}
];
break;
//冻结列至选区
case 'rangeColumn':
views = [
{state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: 0}
];
break;
//冻结至选区
case 'rangeBoth':
views = [
{state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: frozen.range.row_focus+1}
];
break;
}
worksheet.views = views
}
// 设置批注
var setNote = function (luckyData = [], worksheet) {
if(!luckyData || !luckyData.length) return;
luckyData.forEach((row, rowIndex) => {
if(!row || !row.length) return;
row.forEach((line, lineIndex) => {
// 没有值就返回
if (!line) {
return;
}
const { ps } = line;
// 标注数据
if (ps) {
// 单位格位置符号
const unit = `${letterArr[lineIndex]}${rowIndex + 1}`;
const { value: text } = ps;
worksheet.getCell(unit).note = {
texts: [
{
text
}
],
margins: {
insetmode: 'auto'
// inset: [0.25, 0.25, 0.35, 0.35]
}
// protection: {
// locked: True,
// lockText: False
// },
// editAs: "twoCells"
};
}
});
});
};
// 设置超链接
var setHyperlink = function(hyperlink,worksheet){
if (!hyperlink) return;
for (const key in hyperlink) {
const row_col = key.split('_')
let cell =worksheet.getCell(Number(row_col[0])+1,Number(row_col[1])+1)
let font = cell.style.font
//设置导出后超链接的样式
// cell.font= fontConvert(font.name,'#0000ff',font.bold,font.italic,font.size,font.strike,true)
cell.font= fontConvert(font.name,'#0000ff',0,0,font.size,0,true)
if(hyperlink[key].linkType=="external"){
//外部链接
cell.value = {
text: cell.value,
hyperlink: hyperlink[key].linkAddress,
tooltip: hyperlink[key].linkTooltip
}
}else{
// 内部链接
const linkArr = hyperlink[key].linkAddress.split('!')
let hyper = '#\\'+linkArr[0]+'\\'+'!'+linkArr[1]
cell.value = {
text: cell.value,
hyperlink:hyper,
tooltip: hyperlink[key].linkTooltip
}
}
}
}
// 设置单元格合并
var setMerge = function (luckyMerge = {}, worksheet) {
const mergearr = Object.values(luckyMerge);
mergearr.forEach(function (elem) {
// elem格式：{r: 0, c: 0, rs: 1, cs: 2}
// 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
worksheet.mergeCells(
elem.r + 1,
elem.c + 1,
elem.r + elem.rs,
elem.c + elem.cs
);
});
};
/**
* 计算获取图片的坐标
* @param {number} num 坐标点的top,left值
* @param {array} arr 所有的行、列的长度集合
* @returns 满足exceljs的坐标值，需要计算图片在cell中的偏移量nativeOff。
*/
var getImagePosition = function (num, arr) {
for (let i = 0; i < arr.length; i++) {
const item = arr[i];
if (num < item) {
const cell = i > 0 ? arr[i] - arr[i - 1] : item;
// 偏移量单位为Emu,所以单元格的宽高需要转换为Emu的单位，cellWidth = cellWidth*10000
const cellInEmu = cell * 10000;
const rowOrCol =
i > 0 ? (num - arr[i - 1]) / (arr[i] - arr[i - 1]) + i : num / item;
const native = Math.floor(rowOrCol);
const nativeOff = parseInt(
new Big(rowOrCol).minus(native).toNumber() * cellInEmu
);
return { rowOrCol, native, nativeOff };
}
}
};
// 设置图片
var setImages = function (table, worksheet, workbook, imgPattern) {
let {
images,
visibledatacolumn, //所有行的位置
visibledatarow //所有列的位置
} = {
...table
};
if (typeof images != 'object') return;
for (let key in images) {
// 通过 base64 将图像添加到工作簿
const myBase64Image = images[key].src;
//开始行 开始列 结束行 结束列
const item = images[key];
const imageId = workbook.addImage({
base64: myBase64Image,
extension: 'png'
});
// 只有设置tl、br的图片可以被luckysheet识别并展示，设置ext宽高的不行
const col_st = getImagePosition(item.default.left, visibledatacolumn);
const row_st = getImagePosition(item.default.top, visibledatarow);
const w_ed = item.default.left + item.default.width;
const h_ed = item.default.top + item.default.height;
const col_ed = getImagePosition(w_ed, visibledatacolumn);
const row_ed = getImagePosition(h_ed, visibledatarow);
// 需要设置nativeCol,nativeColOff,nativeRow,nativeRowOff,可以省略col,row
worksheet.addImage(imageId, {
tl: {
nativeCol: col_st.native,
nativeColOff: col_st.nativeOff,
nativeRow: row_st.native,
nativeRowOff: row_st.nativeOff
},
br: {
nativeCol: col_ed.native,
nativeColOff: col_ed.nativeOff,
nativeRow: row_ed.native,
nativeRowOff: row_ed.nativeOff
},
editAs: 'oneCell'
});
}
};
var setBorder = function (lucksheetfile, worksheet) {
if (!lucksheetfile) return;
const luckyToExcel = {
style: {
0: 'none',
1: 'thin',
2: 'hair',
3: 'dotted',
4: 'dashDot', // 'Dashed',
5: 'dashDot',
6: 'dashDotDot',
7: 'double',
8: 'medium',
9: 'mediumDashed',
10: 'mediumDashDot',
11: 'mediumDashDotDot',
12: 'slantDashDot',
13: 'thick'
}
};
//获取所有的单元格边框的信息
const borderInfoCompute = getBorderInfo(lucksheetfile);
for (let x in borderInfoCompute) {
let border = {};
let info = borderInfoCompute[x];
let row = parseInt(x.substr(0, x.indexOf('_')));
let column = parseInt(x.substr(x.indexOf('_') + 1));
if (info.t != undefined) {
const tcolor =
info.t.color.indexOf('rgb') > -1 ? rgb2hex(info.t.color) : info.t.color;
border['top'] = {
style: luckyToExcel.style[info.t.style],
color: {
argb: tcolor.replace('#', '')
}
};
}
if (info.r != undefined) {
const rcolor =
info.r.color.indexOf('rgb') > -1 ? rgb2hex(info.r.color) : info.r.color;
border['right'] = {
style: luckyToExcel.style[info.r.style],
color: {
argb: rcolor.replace('#', '')
}
};
}
if (info.b != undefined) {
const bcolor =
info.b.color.indexOf('rgb') > -1 ? rgb2hex(info.b.color) : info.b.color;
border['bottom'] = {
style: luckyToExcel.style[info.b.style],
color: {
argb: bcolor.replace('#', '')
}
};
}
if (info.l != undefined) {
const lcolor =
info.l.color.indexOf('rgb') > -1 ? rgb2hex(info.l.color) : info.l.color;
border['left'] = {
style: luckyToExcel.style[info.l.style],
color: {
argb: lcolor.replace('#', '')
}
};
}
worksheet.getCell(row + 1, column + 1).border = border;
}
};
const getBorderInfo = function (luckysheetfile) {
let borderInfoCompute = {};
let cfg = luckysheetfile.config;
let data = luckysheetfile.data;
let borderInfo = cfg['borderInfo'];
//设置需要计算边框的区域
let dataset_row_st = 0,
dataset_row_ed = data.length,
dataset_col_st = 0,
dataset_col_ed = data[0].length;
if (borderInfo != null && borderInfo.length > 0) {
for (let i = 0; i < borderInfo.length; i++) {
let rangeType = borderInfo[i].rangeType;
if (rangeType == 'range') {
let borderType = borderInfo[i].borderType;
let borderColor = borderInfo[i].color;
let borderStyle = borderInfo[i].style;
let borderRange = borderInfo[i].range;
for (let j = 0; j < borderRange.length; j++) {
let bd_r1 = borderRange[j].row[0],
bd_r2 = borderRange[j].row[1];
let bd_c1 = borderRange[j].column[0],
bd_c2 = borderRange[j].column[1];
if (bd_r1 < dataset_row_st) {
bd_r1 = dataset_row_st;
}
if (bd_r2 > dataset_row_ed) {
bd_r2 = dataset_row_ed;
}
if (bd_c1 < dataset_col_st) {
bd_c1 = dataset_col_st;
}
if (bd_c2 > dataset_col_ed) {
bd_c2 = dataset_col_ed;
}
if (borderType == 'border-left') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
if (borderInfoCompute[bd_r + '_' + bd_c1] == null) {
borderInfoCompute[bd_r + '_' + bd_c1] = {};
}
borderInfoCompute[bd_r + '_' + bd_c1].l = {
color: borderColor,
style: borderStyle
};
let bd_c_left = bd_c1 - 1;
if (bd_c_left >= 0 && borderInfoCompute[bd_r + '_' + bd_c_left]) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_left]) == 'object' &&
data[bd_r][bd_c_left].mc != null
) {
let cell_left = data[bd_r][bd_c_left];
let mc = cfg['merge'][cell_left.mc.r + '_' + cell_left.mc.c];
if (mc.c + mc.cs - 1 == bd_c_left) {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
}
let mc = cfg['merge'] || {};
for (const key in mc) {
let { c, r, cs, rs } = mc[key];
if (
bd_c1 <= c + cs - 1 &&
bd_c1 > c &&
bd_r >= r &&
bd_r <= r + rs - 1
) {
borderInfoCompute[bd_r + '_' + bd_c1].l = null;
}
}
}
} else if (borderType == 'border-right') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
if (borderInfoCompute[bd_r + '_' + bd_c2] == null) {
borderInfoCompute[bd_r + '_' + bd_c2] = {};
}
borderInfoCompute[bd_r + '_' + bd_c2].r = {
color: borderColor,
style: borderStyle
};
let bd_c_right = bd_c2 + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_right]) == 'object' &&
data[bd_r][bd_c_right].mc != null
) {
let cell_right = data[bd_r][bd_c_right];
let mc =
cfg['merge'][cell_right.mc.r + '_' + cell_right.mc.c];
if (mc.c == bd_c_right) {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
}
let mc = cfg['merge'] || {};
for (const key in mc) {
let { c, r, cs, rs } = mc[key];
if (
bd_c2 < c + cs - 1 &&
bd_c2 >= c &&
bd_r >= r &&
bd_r <= r + rs - 1
) {
borderInfoCompute[bd_r + '_' + bd_c2].r = null;
}
}
}
} else if (borderType == 'border-top') {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r1] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (borderInfoCompute[bd_r1 + '_' + bd_c] == null) {
borderInfoCompute[bd_r1 + '_' + bd_c] = {};
}
borderInfoCompute[bd_r1 + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
let bd_r_top = bd_r1 - 1;
if (bd_r_top >= 0 && borderInfoCompute[bd_r_top + '_' + bd_c]) {
if (
data[bd_r_top] != null &&
getObjType(data[bd_r_top][bd_c]) == 'object' &&
data[bd_r_top][bd_c].mc != null
) {
let cell_top = data[bd_r_top][bd_c];
let mc = cfg['merge'][cell_top.mc.r + '_' + cell_top.mc.c];
if (mc.r + mc.rs - 1 == bd_r_top) {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
}
let mc = cfg['merge'] || {};
for (const key in mc) {
let { c, r, cs, rs } = mc[key];
if (
bd_r1 <= r + rs - 1 &&
bd_r1 > r &&
bd_c >= c &&
bd_c <= c + cs - 1
) {
borderInfoCompute[bd_r1 + '_' + bd_c].t = null;
}
}
}
} else if (borderType == 'border-bottom') {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r2] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (borderInfoCompute[bd_r2 + '_' + bd_c] == null) {
borderInfoCompute[bd_r2 + '_' + bd_c] = {};
}
borderInfoCompute[bd_r2 + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
let bd_r_bottom = bd_r2 + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
if (
data[bd_r_bottom] != null &&
getObjType(data[bd_r_bottom][bd_c]) == 'object' &&
data[bd_r_bottom][bd_c].mc != null
) {
let cell_bottom = data[bd_r_bottom][bd_c];
let mc =
cfg['merge'][cell_bottom.mc.r + '_' + cell_bottom.mc.c];
if (mc.r == bd_r_bottom) {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
}
let mc = cfg['merge'] || {};
for (const key in mc) {
let { c, r, cs, rs } = mc[key];
if (
bd_r2 < r + rs - 1 &&
bd_r2 >= r &&
bd_c >= c &&
bd_c <= c + cs - 1
) {
borderInfoCompute[bd_r2 + '_' + bd_c].b = null;
}
}
}
} else if (borderType == 'border-all') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc == undefined || mc == null) {
continue;
}
if (mc.r == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
if (mc.r + mc.rs - 1 == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
if (mc.c == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
}
if (mc.c + mc.cs - 1 == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
if (bd_r == bd_r1) {
let bd_r_top = bd_r1 - 1;
if (
bd_r_top >= 0 &&
borderInfoCompute[bd_r_top + '_' + bd_c]
) {
if (
data[bd_r_top] != null &&
getObjType(data[bd_r_top][bd_c]) == 'object' &&
data[bd_r_top][bd_c].mc != null
) {
let cell_top = data[bd_r_top][bd_c];
let mc =
cfg['merge'][cell_top.mc.r + '_' + cell_top.mc.c];
if (mc.r + mc.rs - 1 == bd_r_top) {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_r == bd_r2) {
let bd_r_bottom = bd_r2 + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
if (
data[bd_r_bottom] != null &&
getObjType(data[bd_r_bottom][bd_c]) == 'object' &&
data[bd_r_bottom][bd_c].mc != null
) {
let cell_bottom = data[bd_r_bottom][bd_c];
let mc =
cfg['merge'][cell_bottom.mc.r + '_' + cell_bottom.mc.c];
if (mc.r == bd_r_bottom) {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_c == bd_c1) {
let bd_c_left = bd_c1 - 1;
if (
bd_c_left >= 0 &&
borderInfoCompute[bd_r + '_' + bd_c_left]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_left]) == 'object' &&
data[bd_r][bd_c_left].mc != null
) {
let cell_left = data[bd_r][bd_c_left];
let mc =
cfg['merge'][cell_left.mc.r + '_' + cell_left.mc.c];
if (mc.c + mc.cs - 1 == bd_c_left) {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_c == bd_c2) {
let bd_c_right = bd_c2 + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_right]) == 'object' &&
data[bd_r][bd_c_right].mc != null
) {
let cell_right = data[bd_r][bd_c_right];
let mc =
cfg['merge'][cell_right.mc.r + '_' + cell_right.mc.c];
if (mc.c == bd_c_right) {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
}
}
}
}
} else if (borderType == 'border-outside') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (
!(
bd_r == bd_r1 ||
bd_r == bd_r2 ||
bd_c == bd_c1 ||
bd_c == bd_c2
)
) {
continue;
}
if (bd_r == bd_r1) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
let bd_r_top = bd_r1 - 1;
if (
bd_r_top >= 0 &&
borderInfoCompute[bd_r_top + '_' + bd_c]
) {
if (
data[bd_r_top] != null &&
getObjType(data[bd_r_top][bd_c]) == 'object' &&
data[bd_r_top][bd_c].mc != null
) {
let cell_top = data[bd_r_top][bd_c];
let mc =
cfg['merge'][cell_top.mc.r + '_' + cell_top.mc.c];
if (mc.r + mc.rs - 1 == bd_r_top) {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_r == bd_r2) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
let bd_r_bottom = bd_r2 + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
if (
data[bd_r_bottom] != null &&
getObjType(data[bd_r_bottom][bd_c]) == 'object' &&
data[bd_r_bottom][bd_c].mc != null
) {
let cell_bottom = data[bd_r_bottom][bd_c];
let mc =
cfg['merge'][cell_bottom.mc.r + '_' + cell_bottom.mc.c];
if (mc.r == bd_r_bottom) {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_c == bd_c1) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
let bd_c_left = bd_c1 - 1;
if (
bd_c_left >= 0 &&
borderInfoCompute[bd_r + '_' + bd_c_left]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_left]) == 'object' &&
data[bd_r][bd_c_left].mc != null
) {
let cell_left = data[bd_r][bd_c_left];
let mc =
cfg['merge'][cell_left.mc.r + '_' + cell_left.mc.c];
if (mc.c + mc.cs - 1 == bd_c_left) {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: borderColor,
style: borderStyle
};
}
}
}
if (bd_c == bd_c2) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
let bd_c_right = bd_c2 + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_right]) == 'object' &&
data[bd_r][bd_c_right].mc != null
) {
let cell_right = data[bd_r][bd_c_right];
let mc =
cfg['merge'][cell_right.mc.r + '_' + cell_right.mc.c];
if (mc.c == bd_c_right) {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: borderColor,
style: borderStyle
};
}
}
}
}
}
} else if (borderType == 'border-inside') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (bd_r == bd_r1 && bd_c == bd_c1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r2 && bd_c == bd_c1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r1 && bd_c == bd_c2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r2 && bd_c == bd_c2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.c == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
} else if (mc.c + mc.cs - 1 == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.c == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
} else if (mc.c + mc.cs - 1 == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_c == bd_c1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.r == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
} else if (mc.r + mc.rs - 1 == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_c == bd_c2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.r == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
} else if (mc.r + mc.rs - 1 == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.r == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
} else if (mc.r + mc.rs - 1 == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
if (mc.c == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
} else if (mc.c + mc.cs - 1 == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
}
}
}
} else if (borderType == 'border-horizontal') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (bd_r == bd_r1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_r == bd_r2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
}
} else {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c];
if (mc.r == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
} else if (mc.r + mc.rs - 1 == bd_r) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: borderColor,
style: borderStyle
};
}
}
}
}
} else if (borderType == 'border-vertical') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (bd_c == bd_c1) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else if (bd_c == bd_c2) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
}
} else {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c] || {};
if (mc.c == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
} else if (mc.c + mc.cs - 1 == bd_c) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
} else {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: borderColor,
style: borderStyle
};
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: borderColor,
style: borderStyle
};
}
}
}
}
} else if (borderType == 'border-none') {
for (let bd_r = bd_r1; bd_r <= bd_r2; bd_r++) {
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
for (let bd_c = bd_c1; bd_c <= bd_c2; bd_c++) {
if (borderInfoCompute[bd_r + '_' + bd_c] != null) {
delete borderInfoCompute[bd_r + '_' + bd_c];
}
if (bd_r == bd_r1) {
let bd_r_top = bd_r1 - 1;
if (
bd_r_top >= 0 &&
borderInfoCompute[bd_r_top + '_' + bd_c]
) {
delete borderInfoCompute[bd_r_top + '_' + bd_c].b;
}
}
if (bd_r == bd_r2) {
let bd_r_bottom = bd_r2 + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
delete borderInfoCompute[bd_r_bottom + '_' + bd_c].t;
}
}
if (bd_c == bd_c1) {
let bd_c_left = bd_c1 - 1;
if (
bd_c_left >= 0 &&
borderInfoCompute[bd_r + '_' + bd_c_left]
) {
delete borderInfoCompute[bd_r + '_' + bd_c_left].r;
}
}
if (bd_c == bd_c2) {
let bd_c_right = bd_c2 + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
delete borderInfoCompute[bd_r + '_' + bd_c_right].l;
}
}
}
}
}
}
} else if (rangeType == 'cell') {
let value = borderInfo[i].value;
let bd_r = value.row_index,
bd_c = value.col_index;
if (
bd_r < dataset_row_st ||
bd_r > dataset_row_ed ||
bd_c < dataset_col_st ||
bd_c > dataset_col_ed
) {
continue;
}
if (cfg['rowhidden'] != null && cfg['rowhidden'][bd_r] != null) {
continue;
}
if (
value.l != null ||
value.r != null ||
value.t != null ||
value.b != null
) {
if (borderInfoCompute[bd_r + '_' + bd_c] == null) {
borderInfoCompute[bd_r + '_' + bd_c] = {};
}
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c]) == 'object' &&
data[bd_r][bd_c].mc != null
) {
let cell = data[bd_r][bd_c];
let mc = cfg['merge'][cell.mc.r + '_' + cell.mc.c] || {};
if (value.l != null && bd_c == mc.c) {
//左边框
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: value.l.color,
style: value.l.style
};
let bd_c_left = bd_c - 1;
if (bd_c_left >= 0 && borderInfoCompute[bd_r + '_' + bd_c_left]) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_left]) == 'object' &&
data[bd_r][bd_c_left].mc != null
) {
let cell_left = data[bd_r][bd_c_left];
let mc_l =
cfg['merge'][cell_left.mc.r + '_' + cell_left.mc.c];
if (mc_l.c + mc_l.cs - 1 == bd_c_left) {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: value.l.color,
style: value.l.style
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: value.l.color,
style: value.l.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].l = null;
}
if (value.r != null && bd_c == mc.c + mc.cs - 1) {
//右边框
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: value.r.color,
style: value.r.style
};
let bd_c_right = bd_c + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_right]) == 'object' &&
data[bd_r][bd_c_right].mc != null
) {
let cell_right = data[bd_r][bd_c_right];
let mc_r =
cfg['merge'][cell_right.mc.r + '_' + cell_right.mc.c];
if (mc_r.c == bd_c_right) {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: value.r.color,
style: value.r.style
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: value.r.color,
style: value.r.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].r = null;
}
if (value.t != null && bd_r == mc.r) {
//上边框
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: value.t.color,
style: value.t.style
};
let bd_r_top = bd_r - 1;
if (bd_r_top >= 0 && borderInfoCompute[bd_r_top + '_' + bd_c]) {
if (
data[bd_r_top] != null &&
getObjType(data[bd_r_top][bd_c]) == 'object' &&
data[bd_r_top][bd_c].mc != null
) {
let cell_top = data[bd_r_top][bd_c];
let mc_t = cfg['merge'][cell_top.mc.r + '_' + cell_top.mc.c];
if (mc_t.r + mc_t.rs - 1 == bd_r_top) {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: value.t.color,
style: value.t.style
};
}
} else {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: value.t.color,
style: value.t.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].t = null;
}
if (value.b != null && bd_r == mc.r + mc.rs - 1) {
//下边框
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: value.b.color,
style: value.b.style
};
let bd_r_bottom = bd_r + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
if (
data[bd_r_bottom] != null &&
getObjType(data[bd_r_bottom][bd_c]) == 'object' &&
data[bd_r_bottom][bd_c].mc != null
) {
let cell_bottom = data[bd_r_bottom][bd_c];
let mc_b =
cfg['merge'][cell_bottom.mc.r + '_' + cell_bottom.mc.c];
if (mc_b.r == bd_r_bottom) {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: value.b.color,
style: value.b.style
};
}
} else {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: value.b.color,
style: value.b.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].b = null;
}
} else {
if (value.l != null) {
//左边框
borderInfoCompute[bd_r + '_' + bd_c].l = {
color: value.l.color,
style: value.l.style
};
let bd_c_left = bd_c - 1;
if (bd_c_left >= 0 && borderInfoCompute[bd_r + '_' + bd_c_left]) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_left]) == 'object' &&
data[bd_r][bd_c_left].mc != null
) {
let cell_left = data[bd_r][bd_c_left];
let mc_l =
cfg['merge'][cell_left.mc.r + '_' + cell_left.mc.c];
if (mc_l.c + mc_l.cs - 1 == bd_c_left) {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: value.l.color,
style: value.l.style
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_left].r = {
color: value.l.color,
style: value.l.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].l = null;
}
if (value.r != null) {
//右边框
borderInfoCompute[bd_r + '_' + bd_c].r = {
color: value.r.color,
style: value.r.style
};
let bd_c_right = bd_c + 1;
if (
bd_c_right < data[0].length &&
borderInfoCompute[bd_r + '_' + bd_c_right]
) {
if (
data[bd_r] != null &&
getObjType(data[bd_r][bd_c_right]) == 'object' &&
data[bd_r][bd_c_right].mc != null
) {
let cell_right = data[bd_r][bd_c_right];
let mc_r =
cfg['merge'][cell_right.mc.r + '_' + cell_right.mc.c];
if (mc_r.c == bd_c_right) {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: value.r.color,
style: value.r.style
};
}
} else {
borderInfoCompute[bd_r + '_' + bd_c_right].l = {
color: value.r.color,
style: value.r.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].r = null;
}
if (value.t != null) {
//上边框
borderInfoCompute[bd_r + '_' + bd_c].t = {
color: value.t.color,
style: value.t.style
};
let bd_r_top = bd_r - 1;
if (bd_r_top >= 0 && borderInfoCompute[bd_r_top + '_' + bd_c]) {
if (
data[bd_r_top] != null &&
getObjType(data[bd_r_top][bd_c]) == 'object' &&
data[bd_r_top][bd_c].mc != null
) {
let cell_top = data[bd_r_top][bd_c];
let mc_t = cfg['merge'][cell_top.mc.r + '_' + cell_top.mc.c];
if (mc_t.r + mc_t.rs - 1 == bd_r_top) {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: value.t.color,
style: value.t.style
};
}
} else {
borderInfoCompute[bd_r_top + '_' + bd_c].b = {
color: value.t.color,
style: value.t.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].t = null;
}
if (value.b != null) {
//下边框
borderInfoCompute[bd_r + '_' + bd_c].b = {
color: value.b.color,
style: value.b.style
};
let bd_r_bottom = bd_r + 1;
if (
bd_r_bottom < data.length &&
borderInfoCompute[bd_r_bottom + '_' + bd_c]
) {
if (
data[bd_r_bottom] != null &&
getObjType(data[bd_r_bottom][bd_c]) == 'object' &&
data[bd_r_bottom][bd_c].mc != null
) {
let cell_bottom = data[bd_r_bottom][bd_c];
let mc_b =
cfg['merge'][cell_bottom.mc.r + '_' + cell_bottom.mc.c];
if (mc_b.r == bd_r_bottom) {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: value.b.color,
style: value.b.style
};
}
} else {
borderInfoCompute[bd_r_bottom + '_' + bd_c].t = {
color: value.b.color,
style: value.b.style
};
}
}
} else {
borderInfoCompute[bd_r + '_' + bd_c].b = null;
}
}
} else {
delete borderInfoCompute[bd_r + '_' + bd_c];
}
}
}
}
return borderInfoCompute;
};
//获取数据类型
var getObjType = function (obj) {
let toString = Object.prototype.toString;
let map = {
'[object Boolean]': 'boolean',
'[object Number]': 'number',
'[object String]': 'string',
'[object Function]': 'function',
'[object Array]': 'array',
'[object Date]': 'date',
'[object RegExp]': 'regExp',
'[object Undefined]': 'undefined',
'[object Null]': 'null',
'[object Object]': 'object'
};
return map[toString.call(obj)];
};
var setStyleAndValue = function (cellArr, worksheet) {
if (!Array.isArray(cellArr)) return;
cellArr.forEach(function (row, rowid) {
const dbrow = worksheet.getRow(rowid + 1);
//设置单元格行高
const luckyRowHeight = window.luckysheet.getRowHeight([rowid])[rowid];
if (luckyRowHeight - DEFAULT_EXCEL_ROW_HEIGHT <= DEFAULT_ROW_HEIGHT) {
dbrow.height = DEFAULT_ROW_HEIGHT;
} else {
dbrow.height = luckyRowHeight - DEFAULT_EXCEL_ROW_HEIGHT;
}
row.every(function (cell, columnid) {
if (rowid == 0) {
const dobCol = worksheet.getColumn(columnid + 1);
// 设置单元格列宽除以8;
dobCol.width =
window.luckysheet.getColumnWidth([columnid])[columnid] / 8;
}
if (!cell) return true;
let fill = fillConvert(cell.bg);
let font = fontConvert(
cell.ff,
cell.fc,
cell.bl,
cell.it,
cell.fs,
cell.cl,
cell.ul
);
let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
// 返回值
let value;
// 数据格式
let numFmt;
var v = '';
if (cell.ct && cell.ct.fa) {
numFmt = cell.ct.fa;
}
if (cell.ct && cell.ct.t == 'inlineStr') {
var s = cell.ct.s;
s.forEach(function (val, num) {
v += val.v;
});
} else {
v = cell.v;
}
if (cell.f) {
value = {
formula: cell.f,
result: v
};
} else {
value = v;
}
let target = worksheet.getCell(rowid + 1, columnid + 1);
target.fill = fill;
target.font = font;
target.alignment = alignment;
target.value = value;
target.numFmt = numFmt;
return true;
});
});
};
//转换颜色
var rgb2hex = function (rgb) {
if (rgb.charAt(0) == '#') {
return rgb;
}
var ds = rgb.split(/\D+/);
var decimal = Number(ds[1]) * 65536 + Number(ds[2]) * 256 + Number(ds[3]);
return '#' + zero_fill_hex(decimal, 6);
function zero_fill_hex(num, digits) {
var s = num.toString(16);
while (s.length < digits) s = '0' + s;
return s;
}
};
var fillConvert = function (bg) {
if (!bg) {
return null;
// return {
// type: 'pattern',
// pattern: 'solid',
// fgColor:{argb:'#ffffff'.replace('#','')}
// }
}
bg = bg.indexOf('rgb') > -1 ? rgb2hex(bg) : bg;
let fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {
argb: bg.replace('#', '')
}
};
return fill;
};
var fontConvert = function (
ff = 0,
fc = '#000000',
bl = 0,
it = 0,
fs = 10,
cl = 0,
ul = 0
) {
// luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
const luckyToExcel = {
0: '微软雅黑',
1: '宋体（Song）',
2: '黑体（ST Heiti）',
3: '楷体（ST Kaiti）',
4: '仿宋（ST FangSong）',
5: '新宋体（ST Song）',
6: '华文新魏',
7: '华文行楷',
8: '华文隶书',
9: 'Arial',
10: 'Times New Roman ',
11: 'Tahoma ',
12: 'Verdana',
num2bl: function (num) {
return num === 0 ? false : true;
}
};
let font = {
name: ff,
family: 1,
size: fs,
color: {
argb: fc.replace('#', '')
},
bold: luckyToExcel.num2bl(bl),
italic: luckyToExcel.num2bl(it),
underline: luckyToExcel.num2bl(ul),
strike: luckyToExcel.num2bl(cl)
};
return font;
};
var alignmentConvert = function (
vt = 'default',
ht = 'default',
tb = 'default',
tr = 'default'
) {
// luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
const luckyToExcel = {
vertical: {
0: 'middle',
1: 'top',
2: 'bottom',
default: 'top'
},
horizontal: {
0: 'center',
1: 'left',
2: 'right',
default: 'left'
},
wrapText: {
0: false,
1: false,
2: true,
default: false
},
textRotation: {
0: 0,
1: 45,
2: -45,
3: 'vertical',
4: 90,
5: -90,
default: 0
}
};
let alignment = {
vertical: luckyToExcel.vertical[vt],
horizontal: luckyToExcel.horizontal[ht],
wrapText: luckyToExcel.wrapText[tb],
textRotation: luckyToExcel.textRotation[tr]
};
return alignment;
};
setConditions.js
/**
** 条件格式设置
*/
import { createCellRange } from './utils'
export const setConditions = function(conditions, worksheet){
//条件格式不存在，则不执行后续代码
if(conditions==undefined) return
//循环遍历规则列表
conditions.forEach(item => {
let ruleObj = {
ref: createCellRange(item.cellrange[0].row,item.cellrange[0].column),
rules:[]
}
//lucksheet对应的为----突出显示单元格规则和项目选区规则
if(item.type=='default'){
//excel中type为cellIs的条件下
if(item.conditionName=='equal'||'greaterThan'||'lessThan'||'betweenness'){
ruleObj.rules = setDefaultRules({
type:'cellIs',
operator:item.conditionName=='betweenness'?'between':item.conditionName,
condvalue:item.conditionValue,
colorArr:[item.format.cellColor,item.format.textColor]
})
worksheet.addConditionalFormatting(ruleObj)
}
//excel中type为containsText的条件下
if(item.conditionName=='textContains'){
ruleObj.rules = [
{
type:'containsText',
operator:'containsText', //表示如果单元格值包含在text 字段中指定的值，则应用格式
text:item.conditionValue[0],
style: setStyle([item.format.cellColor,item.format.textColor])
}
]
worksheet.addConditionalFormatting(ruleObj)
}
//发生日期--时间段
if(item.conditionName=='occurrenceDate'){
ruleObj.rules = [
{
type:'timePeriod',
timePeriod:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
style: setStyle([item.format.cellColor,item.format.textColor])
}
]
worksheet.addConditionalFormatting(ruleObj)
}
//重复值--唯一值
// if(item.conditionName=='duplicateValue'){
// ruleObj.rules = [
// {
// type:'expression',
// formulae:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
// style: setStyle([item.format.cellColor,item.format.textColor])
// }
// ]
// worksheet.addConditionalFormatting(ruleObj)
// }
//项目选区规则--top10前多少项的操作
if(item.conditionName=='top10'||'top10%'||'last10'||'last10%'){
ruleObj.rules = [
{
type:'top10',
rank:item.conditionValue[0], //指定格式中包含多少个顶部（或底部）值
percent:item.conditionName=='top10'||'last10'?false:true,
bottom:item.conditionName=='top10'||'top10%'?false:true,
style: setStyle([item.format.cellColor,item.format.textColor])
}
]
worksheet.addConditionalFormatting(ruleObj)
}
//项目选区规则--高于/低于平均值的操作
if(item.conditionName=='AboveAverage'||'SubAverage'){
ruleObj.rules = [
{
type:'aboveAverage',
aboveAverage:item.conditionName=='AboveAverage'?true:false,
style: setStyle([item.format.cellColor,item.format.textColor])
}
]
worksheet.addConditionalFormatting(ruleObj)
}
return
}
//数据条
if(item.type == 'dataBar'){
ruleObj.rules = [
{
type:'dataBar',
style:{}
}
]
worksheet.addConditionalFormatting(ruleObj)
return
}
//色阶
if(item.type == 'colorGradation'){
ruleObj.rules = [
{
type:'colorScale',
color:item.format,
style:{}
}
]
worksheet.addConditionalFormatting(ruleObj)
return
}
//图标集
if(item.type == 'icons'){
ruleObj.rules = [
{
type:'iconSet',
iconSet:item.format.len
}
]
worksheet.addConditionalFormatting(ruleObj)
return
}
});
}
/**
*
* @param {
* type:lucketsheet对应的条件导出类型；
* operator：excel对应的条件导入类型；
* condvalue：1个公式字符串数组，返回要与每个单元格进行比较的值；
* colorArr：颜色数组，第一项为单元格填充色，第二项为单元格文本颜色
* } obj
* @returns
*/
function setDefaultRules(obj){
let rules = [
{
type:obj.type,
operator:obj.operator,
formulae:obj.condvalue,
style:setStyle(obj.colorArr)
}
]
return rules
}
/**
*
* @param {颜色数组，第一项为单元格填充色，第二项为单元格文本颜色} colorArr
*/
function setStyle(colorArr){
return {
fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: colorArr[0].replace("#", "")}},
font: {color:{ argb: colorArr[1].replace("#", "")},}
}
}
utils.js
/**
* *创建单元格所在列的列的字母
* @param {列数的index值} n
* @returns
*/
export const createCellPos = function(n) {
let ordA = 'A'.charCodeAt(0)
let ordZ = 'Z'.charCodeAt(0)
let len = ordZ - ordA + 1
let s = ''
while (n >= 0) {
s = String.fromCharCode((n % len) + ordA) + s
n = Math.floor(n / len) - 1
}
return s
}
/**
* *创建单元格范围，期望得到如：A1:D6
* @param {单元格行数组（例如：[0,3]）} rowArr
* @param {单元格列数组（例如：[5,7]）} colArr
* */
export const createCellRange = function(rowArr,colArr){
const startCell = createCellPos(colArr[0])+(rowArr[0]+1)
const endCell = createCellPos(colArr[1])+(rowArr[1]+1)
return startCell+':'+endCell
}
