123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979 |
- // import { createCellPos } from './translateNumToLetter'
- import Excel from "exceljs";
- import Big from "big.js";
- import FileSaver from "file-saver";
- function exportExcel(luckysheet, name, excelType) {
- // 1.创建工作簿,可以为工作簿添加属性
- const workbook = new Excel.Workbook();
- // 2.创建表格,第二个参数可以配置创建什么样的工作表
- luckysheet.forEach(function (table) {
- // debugger
- if (table.data.length === 0) return true;
- const worksheet = workbook.addWorksheet(table.name);
- const merge = (table.config && table.config.merge) || {}; //合并单元格
- const borderInfo = (table.config && table.config.borderInfo) || {}; //边框
- const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
- const rowHeight = (table.config && table.config.rowlen) || {}; //行高
- const frozen = table.frozen || {}; //冻结
- const rowhidden = (table.config && table.config.rowhidden) || {}; //行隐藏
- const colhidden = (table.config && table.config.colhidden) || {}; //列隐藏
- const filterSelect = table.filter_select || {}; //筛选
- const hide = table.hide; //工作表 sheet 1隐藏
- if (hide === 1) {
- // 隐藏工作表
- worksheet.state = "hidden";
- }
- setStyleAndValue(table.data, worksheet);
- setMerge(merge, worksheet);
- setBorder(borderInfo, worksheet);
- setImages(table, worksheet, workbook);
- setColumnWidth(columnWidth, worksheet);
- //行高设置50导出后在ms-excel中打开显示25,在wps-excel中打开显示50这个bug不会修复
- setRowHeight(rowHeight, worksheet, excelType);
- setFrozen(frozen, worksheet);
- setRowHidden(rowhidden, worksheet);
- setColHidden(colhidden, worksheet);
- setFilter(filterSelect, worksheet);
- return true;
- });
- const buffer = workbook.xlsx.writeBuffer().then((data) => {
- // console.log('data', data)
- const blob = new Blob([data], {
- type: "application/vnd.ms-excel;charset=utf-8",
- });
- console.log("导出成功!");
- FileSaver.saveAs(blob, name + `.xlsx`);
- });
- return buffer;
- }
- function getExcelBlob(luckysheet, name, excelType) {
- // 1.创建工作簿,可以为工作簿添加属性
- const workbook = new Excel.Workbook();
- // 2.创建表格,第二个参数可以配置创建什么样的工作表
- luckysheet.forEach(function (table) {
- // debugger
- if (table.data.length === 0) return true;
- const worksheet = workbook.addWorksheet(table.name);
- const merge = (table.config && table.config.merge) || {}; //合并单元格
- const borderInfo = (table.config && table.config.borderInfo) || {}; //边框
- const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
- const rowHeight = (table.config && table.config.rowlen) || {}; //行高
- const frozen = table.frozen || {}; //冻结
- const rowhidden = (table.config && table.config.rowhidden) || {}; //行隐藏
- const colhidden = (table.config && table.config.colhidden) || {}; //列隐藏
- const filterSelect = table.filter_select || {}; //筛选
- const hide = table.hide; //工作表 sheet 1隐藏
- if (hide === 1) {
- // 隐藏工作表
- worksheet.state = "hidden";
- }
- setStyleAndValue(table.data, worksheet);
- setMerge(merge, worksheet);
- setBorder(borderInfo, worksheet);
- setImages(table, worksheet, workbook);
- setColumnWidth(columnWidth, worksheet);
- //行高设置50导出后在ms-excel中打开显示25,在wps-excel中打开显示50这个bug不会修复
- setRowHeight(rowHeight, worksheet, excelType);
- setFrozen(frozen, worksheet);
- setRowHidden(rowhidden, worksheet);
- setColHidden(colhidden, worksheet);
- setFilter(filterSelect, worksheet);
- return true;
- });
- const buffer = workbook.xlsx.writeBuffer().then((data) => {
- // console.log('data', data)
- const blob = new Blob([data], {
- type: "application/vnd.ms-excel;charset=utf-8",
- });
- return new Promise((resolve, reject) => {
- resolve(blob);
- });
- });
- return buffer;
- }
- /**
- * 列宽
- * @param columnWidth
- * @param worksheet
- */
- var setColumnWidth = function (columnWidth, worksheet) {
- for (let key in columnWidth) {
- worksheet.getColumn(parseInt(key) + 1).width = columnWidth[key] / 7.5;
- }
- };
- /**
- * 行高
- * @param rowHeight
- * @param worksheet
- * @param excelType
- */
- var setRowHeight = function (rowHeight, worksheet, excelType) {
- for (let key in rowHeight) {
- worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75;
- }
- /*//导出的文件用wps打开和用excel打开显示的行高大一倍
- if (excelType == "wps") {
- for (let key in rowHeight) {
- worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75
- }
- }
- if (excelType == "office" || excelType == undefined) {
- for (let key in rowHeight) {
- worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 1.5
- }
- }*/
- };
- /**
- * 合并单元格
- * @param luckyMerge
- * @param worksheet
- */
- 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 luckyBorderInfo
- * @param worksheet
- */
- var setBorder = function (luckyBorderInfo, worksheet) {
- if (!Array.isArray(luckyBorderInfo)) return;
- //合并边框信息
- var mergeCellBorder = function (border1, border2) {
- if (undefined === border1 || Object.keys(border1).length === 0)
- return border2;
- return Object.assign({}, border1, border2);
- };
- // console.log('luckyBorderInfo', luckyBorderInfo)
- luckyBorderInfo.forEach(function (elem) {
- // 现在只兼容到borderType 为range的情况
- // console.log('ele', elem)
- if (elem.rangeType === "range") {
- let border = borderConvert(elem.borderType, elem.style, elem.color);
- let rang = elem.range[0];
- let row = rang.row;
- let column = rang.column;
- let rowBegin = row[0];
- let rowEnd = row[1];
- let colBegin = column[0];
- let colEnd = column[1];
- //处理外边框的情况 没有直接对应的外边框 需要转换成上下左右
- if (border.all) {
- //全部边框
- let b = border.all;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- border["top"] = b;
- border["bottom"] = b;
- border["left"] = b;
- border["right"] = b;
- worksheet.getCell(i, y).border = border;
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- } else if (border.top) {
- //上边框
- let b = border.top;
- let i = row[0] + 1;
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- border["top"] = b;
- worksheet.getCell(i, y).border = border;
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- } else if (border.right) {
- //右边框
- let b = border.right;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- let y = column[1] + 1;
- let border = {};
- border["right"] = b;
- worksheet.getCell(i, y).border = border;
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- } else if (border.bottom) {
- //下边框
- let b = border.bottom;
- let i = row[1] + 1;
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- border["bottom"] = b;
- worksheet.getCell(i, y).border = border;
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- } else if (border.left) {
- //左边框
- let b = border.left;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- let y = column[0] + 1;
- let border = {};
- border["left"] = b;
- worksheet.getCell(i, y).border = border;
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- } else if (border.outside) {
- //外边框
- let b = border.outside;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- if (i === rowBegin + 1) {
- border["top"] = b;
- }
- if (i === rowEnd + 1) {
- border["bottom"] = b;
- }
- if (y === colBegin + 1) {
- border["left"] = b;
- }
- if (y === colEnd + 1) {
- border["right"] = b;
- }
- let border1 = worksheet.getCell(i, y).border;
- worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- } else if (border.inside) {
- //内边框
- let b = border.inside;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- if (i !== rowBegin + 1) {
- border["top"] = b;
- }
- if (i !== rowEnd + 1) {
- border["bottom"] = b;
- }
- if (y !== colBegin + 1) {
- border["left"] = b;
- }
- if (y !== colEnd + 1) {
- border["right"] = b;
- }
- let border1 = worksheet.getCell(i, y).border;
- worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- } else if (border.horizontal) {
- //内侧水平边框
- let b = border.horizontal;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- if (i === rowBegin + 1) {
- border["bottom"] = b;
- } else if (i === rowEnd + 1) {
- border["top"] = b;
- } else {
- border["top"] = b;
- border["bottom"] = b;
- }
- let border1 = worksheet.getCell(i, y).border;
- worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- } else if (border.vertical) {
- //内侧垂直边框
- let b = border.vertical;
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- let border = {};
- if (y === colBegin + 1) {
- border["right"] = b;
- } else if (y === colEnd + 1) {
- border["left"] = b;
- } else {
- border["left"] = b;
- border["right"] = b;
- }
- let border1 = worksheet.getCell(i, y).border;
- worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- } else if (border.none) {
- //当luckysheet边框为border-none的时候表示没有边框 则将对应的单元格border清空
- for (let i = row[0] + 1; i <= row[1] + 1; i++) {
- for (let y = column[0] + 1; y <= column[1] + 1; y++) {
- worksheet.getCell(i, y).border = {};
- // console.log(i, y, worksheet.getCell(i, y).border)
- }
- }
- }
- }
- if (elem.rangeType === "cell") {
- // col_index: 2
- // row_index: 1
- // b: {
- // color: '#d0d4e3'
- // style: 1
- // }
- const { col_index, row_index } = elem.value;
- const borderData = Object.assign({}, elem.value);
- delete borderData.col_index;
- delete borderData.row_index;
- let border = addborderToCell(borderData, row_index, col_index);
- let border1 = worksheet.getCell(row_index + 1, col_index + 1).border;
- worksheet.getCell(row_index + 1, col_index + 1).border = mergeCellBorder(
- border1,
- border
- );
- // console.log(row_index + 1, col_index + 1, worksheet.getCell(row_index + 1, col_index + 1).border)
- }
- });
- };
- var setStyleAndValue = function (cellArr, worksheet) {
- if (!Array.isArray(cellArr)) return;
- cellArr.forEach(function (row, rowid) {
- row.every(function (cell, columnid) {
- 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 = "";
- if (cell.f) {
- value = { formula: cell.f, result: cell.v };
- } else if (!cell.v && cell.ct && cell.ct.s) {
- // xls转为xlsx之后,内部存在不同的格式,都会进到富文本里,即值不存在与cell.v,而是存在于cell.ct.s之后
- // value = cell.ct.s[0].v
- cell.ct.s.forEach((arr) => {
- value += arr.v;
- });
- } else {
- value = cell.v;
- }
- // style 填入到_value中可以实现填充色
- let letter = createCellPos(columnid);
- let target = worksheet.getCell(letter + (rowid + 1));
- // console.log('1233', letter + (rowid + 1))
- for (const key in fill) {
- target.fill = fill;
- break;
- }
- target.font = font;
- target.alignment = alignment;
- target.value = value;
- return true;
- });
- });
- };
- /*/!**
- * 设置带样式的值
- * @param cellArr
- * @param worksheet
- *!/
- var setStyleAndValue = function (cellArr, worksheet) {
- if (!Array.isArray(cellArr)) return
- cellArr.forEach(function (row, rowid) {
- row.every(function (cell, columnid) {
- 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.un
- )
- let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
- let value = ''
- if (cell.f) {
- value = {formula: cell.f, result: cell.v}
- } else if (!cell.v && cell.ct && cell.ct.s) {
- // xls转为xlsx之后,内部存在不同的格式,都会进到富文本里,即值不存在与cell.v,而是存在于cell.ct.s之后
- let richText = [];
- let cts = cell.ct.s
- for (let i = 0; i < cts.length; i++) {
- let rt = {
- text: cts[i].v,
- font: fontConvert(cts[i].ff, cts[i].fc, cts[i].bl, cts[i].it, cts[i].fs, cts[i].cl, cts[i].un)
- }
- richText.push(rt)
- }
- value = {
- richText: richText
- };
- } else {
- //设置值为数字格式
- if (cell.v !== undefined && cell.v !== '') {
- var v = +cell.v;
- if (isNaN(v)) v = cell.v
- value = v
- }
- }
- // style 填入到_value中可以实现填充色
- let letter = createCellPos(columnid)
- let target = worksheet.getCell(letter + (rowid + 1))
- // console.log('1233', letter + (rowid + 1))
- for (const key in fill) {
- target.fill = fill
- break
- }
- target.font = font
- target.alignment = alignment
- target.value = value
- try {
- //设置单元格格式
- target.numFmt = cell.ct.fa;
- } catch (e) {
- console.warn(e)
- }
- return true
- })
- })
- }*/
- //获取图片在单元格的位置
- /*var getImagePosition = function (num, arr) {
- let index = 0;
- let minIndex;
- let maxIndex;
- for (let i = 0; i < arr.length; i++) {
- if (num < arr[i]) {
- index = i;
- break;
- }
- }
- if (index == 0) {
- minIndex = 0;
- maxIndex = 1;
- return Math.abs((num - 0) / (arr[maxIndex] - arr[minIndex])) + index;
- } else if (index == arr.length - 1) {
- minIndex = arr.length - 2;
- maxIndex = arr.length - 1;
- } else {
- minIndex = index - 1;
- maxIndex = index;
- }
- let min = arr[minIndex];
- let max = arr[maxIndex];
- let radio = Math.abs((num - min) / (max - min)) + index;
- return radio;
- };*/
- var getImagePosition = function (num, arr) {
- let returnObj = null;
- 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
- );
- returnObj = { rowOrCol, native, nativeOff };
- break;
- }
- }
- if (returnObj == undefined || returnObj == null) {
- const currentV = arr[arr.length - 1];
- const frontV = arr[arr.length - 2];
- const cell = currentV - frontV;
- arr.push(currentV + cell);
- returnObj = getImagePosition(num, arr);
- }
- return returnObj;
- };
- /**
- * 设置图片
- * @param images
- * @param worksheet
- * @param workbook
- */
- /*var setImages = function (table, worksheet, workbook) {
- 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'
- });
- const col_st = getImagePosition(item.default.left,visibledatacolumn);
- const row_st = getImagePosition(item.default.top,visibledatarow);
- console.log(item.default.left,item.default.top,visibledatacolumn,visibledatarow)
- console.log("1111111111111::" + col_st)
- console.log("2222222222222::" + row_st)
- //模式1,图片左侧与luckysheet位置一样,像素比例保持不变,但是,右侧位置可能与原图所在单元格不一致
- worksheet.addImage(imageId, {
- tl: { col: col_st, row: row_st},
- ext: { width: item.default.width, height: item.default.height },
- });
- //模式2,图片四个角位置没有变动,但是图片像素比例可能和原图不一样
- // 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);
- // worksheet.addImage(imageId, {
- // tl: { col: col_st, row: row_st},
- // br: { col: col_ed, row: row_ed},
- // });
- }
- };*/
- var setImages = function (table, worksheet, workbook) {
- 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
- if (row_ed != undefined && col_ed != undefined) {
- 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",
- });
- }
- }
- };
- /**
- * 冻结行列
- * @param frozen
- * @param worksheet
- */
- var setFrozen = function (frozen = {}, worksheet) {
- switch (frozen.type) {
- // 冻结首行
- case "row": {
- worksheet.views = [{ state: "frozen", xSplit: 0, ySplit: 1 }];
- break;
- }
- // 冻结首列
- case "column": {
- worksheet.views = [{ state: "frozen", xSplit: 1, ySplit: 0 }];
- break;
- }
- // 冻结行列
- case "both": {
- worksheet.views = [{ state: "frozen", xSplit: 1, ySplit: 1 }];
- break;
- }
- // 冻结行到选区
- case "rangeRow": {
- let row = frozen.range.row_focus + 1;
- worksheet.views = [{ state: "frozen", xSplit: 0, ySplit: row }];
- break;
- }
- // 冻结列到选区
- case "rangeColumn": {
- let column = frozen.range.column_focus + 1;
- worksheet.views = [{ state: "frozen", xSplit: column, ySplit: 0 }];
- break;
- }
- // 冻结行列到选区
- case "rangeBoth": {
- let row = frozen.range.row_focus + 1;
- let column = frozen.range.column_focus + 1;
- worksheet.views = [{ state: "frozen", xSplit: column, ySplit: row }];
- }
- }
- };
- /**
- * 行隐藏
- * @param rowhidden
- * @param worksheet
- */
- var setRowHidden = function (rowhidden = {}, worksheet) {
- for (const key in rowhidden) {
- //如果当前行没有内容则隐藏不生效
- const row = worksheet.getRow(parseInt(key) + 1);
- row.hidden = true;
- }
- };
- /**
- * 列隐藏
- * @param colhidden
- * @param worksheet
- */
- var setColHidden = function (colhidden = {}, worksheet) {
- for (const key in colhidden) {
- const column = worksheet.getColumn(parseInt(key) + 1);
- column.hidden = true;
- }
- };
- /**
- * 自动筛选器
- * @param filter
- * @param worksheet
- */
- var setFilter = function (filter = {}, worksheet) {
- if (Object.keys(filter).length === 0) return;
- const from = {
- row: filter.row[0] + 1,
- column: filter.column[0] + 1,
- };
- const to = {
- row: filter.row[1] + 1,
- column: filter.column[1] + 1,
- };
- worksheet.autoFilter = {
- from: from,
- to: to,
- };
- };
- /*var fillConvert = function (bg) {
- if (!bg) {
- return {}
- }
- // const bgc = bg.replace('#', '')
- let fill = {
- type: 'pattern',
- pattern: 'solid',
- fgColor: {argb: bg.startsWith("#") ? bg.replace('#', '') : colorRGBtoHex(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
- }
- }
- // 出现Bug,导入的时候ff为luckyToExcel的val
- let font = {
- name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
- family: 1,
- size: fs,
- color: {argb: fc.startsWith("#") ? fc.replace('#', '') : colorRGBtoHex(fc).replace("#", "")},
- bold: luckyToExcel.num2bl(bl),
- italic: luckyToExcel.num2bl(it),
- underline: luckyToExcel.num2bl(ul),
- strike: luckyToExcel.num2bl(cl)
- }
- return font
- }*/
- var fillConvert = function (bg) {
- if (!bg) {
- return {};
- }
- // const bgc = bg.replace('#', '')
- 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;
- },
- };
- // 出现Bug,导入的时候ff为luckyToExcel的val
- let font = {
- name: typeof ff === "number" ? luckyToExcel[ff] : 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: "middle",
- },
- horizontal: {
- 0: "center",
- 1: "left",
- 2: "right",
- default: "center",
- },
- 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;
- };
- var borderConvert = function (borderType, style = 1, color = "#000") {
- // 对应luckysheet的config中borderinfo的的参数
- if (!borderType) {
- return {};
- }
- const luckyToExcel = {
- type: {
- "border-all": "all",
- "border-top": "top",
- "border-right": "right",
- "border-bottom": "bottom",
- "border-left": "left",
- "border-outside": "outside",
- "border-inside": "inside",
- "border-horizontal": "horizontal",
- "border-vertical": "vertical",
- "border-none": "none",
- },
- 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",
- },
- };
- let border = {};
- border[luckyToExcel.type[borderType]] = {
- style: luckyToExcel.style[style],
- color: { argb: color.replace("#", "") },
- };
- return border;
- };
- function addborderToCell(borders, row_index, col_index) {
- let border = {};
- const luckyExcel = {
- type: {
- l: "left",
- r: "right",
- b: "bottom",
- t: "top",
- },
- 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",
- },
- };
- // console.log('borders', borders)
- for (const bor in borders) {
- // console.log(bor)
- if (borders[bor].color.indexOf("rgb") === -1) {
- border[luckyExcel.type[bor]] = {
- style: luckyExcel.style[borders[bor].style],
- color: { argb: borders[bor].color.replace("#", "") },
- };
- } else {
- border[luckyExcel.type[bor]] = {
- style: luckyExcel.style[borders[bor].style],
- color: { argb: borders[bor].color },
- };
- }
- }
- return border;
- }
- function createCellPos(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;
- }
- //rgb(255,255,255)转16进制 #ffffff
- function colorRGBtoHex(color) {
- color = color.replace("rgb", "").replace("(", "").replace(")", "");
- var rgb = color.split(",");
- var r = parseInt(rgb[0]);
- var g = parseInt(rgb[1]);
- var b = parseInt(rgb[2]);
- return "#" + ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1);
- }
- export { exportExcel, getExcelBlob };
|