export.js 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979
  1. // import { createCellPos } from './translateNumToLetter'
  2. import Excel from "exceljs";
  3. import Big from "big.js";
  4. import FileSaver from "file-saver";
  5. function exportExcel(luckysheet, name, excelType) {
  6. // 1.创建工作簿,可以为工作簿添加属性
  7. const workbook = new Excel.Workbook();
  8. // 2.创建表格,第二个参数可以配置创建什么样的工作表
  9. luckysheet.forEach(function (table) {
  10. // debugger
  11. if (table.data.length === 0) return true;
  12. const worksheet = workbook.addWorksheet(table.name);
  13. const merge = (table.config && table.config.merge) || {}; //合并单元格
  14. const borderInfo = (table.config && table.config.borderInfo) || {}; //边框
  15. const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
  16. const rowHeight = (table.config && table.config.rowlen) || {}; //行高
  17. const frozen = table.frozen || {}; //冻结
  18. const rowhidden = (table.config && table.config.rowhidden) || {}; //行隐藏
  19. const colhidden = (table.config && table.config.colhidden) || {}; //列隐藏
  20. const filterSelect = table.filter_select || {}; //筛选
  21. const hide = table.hide; //工作表 sheet 1隐藏
  22. if (hide === 1) {
  23. // 隐藏工作表
  24. worksheet.state = "hidden";
  25. }
  26. setStyleAndValue(table.data, worksheet);
  27. setMerge(merge, worksheet);
  28. setBorder(borderInfo, worksheet);
  29. setImages(table, worksheet, workbook);
  30. setColumnWidth(columnWidth, worksheet);
  31. //行高设置50导出后在ms-excel中打开显示25,在wps-excel中打开显示50这个bug不会修复
  32. setRowHeight(rowHeight, worksheet, excelType);
  33. setFrozen(frozen, worksheet);
  34. setRowHidden(rowhidden, worksheet);
  35. setColHidden(colhidden, worksheet);
  36. setFilter(filterSelect, worksheet);
  37. return true;
  38. });
  39. const buffer = workbook.xlsx.writeBuffer().then((data) => {
  40. // console.log('data', data)
  41. const blob = new Blob([data], {
  42. type: "application/vnd.ms-excel;charset=utf-8",
  43. });
  44. console.log("导出成功!");
  45. FileSaver.saveAs(blob, name + `.xlsx`);
  46. });
  47. return buffer;
  48. }
  49. function getExcelBlob(luckysheet, name, excelType) {
  50. // 1.创建工作簿,可以为工作簿添加属性
  51. const workbook = new Excel.Workbook();
  52. // 2.创建表格,第二个参数可以配置创建什么样的工作表
  53. luckysheet.forEach(function (table) {
  54. // debugger
  55. if (table.data.length === 0) return true;
  56. const worksheet = workbook.addWorksheet(table.name);
  57. const merge = (table.config && table.config.merge) || {}; //合并单元格
  58. const borderInfo = (table.config && table.config.borderInfo) || {}; //边框
  59. const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
  60. const rowHeight = (table.config && table.config.rowlen) || {}; //行高
  61. const frozen = table.frozen || {}; //冻结
  62. const rowhidden = (table.config && table.config.rowhidden) || {}; //行隐藏
  63. const colhidden = (table.config && table.config.colhidden) || {}; //列隐藏
  64. const filterSelect = table.filter_select || {}; //筛选
  65. const hide = table.hide; //工作表 sheet 1隐藏
  66. if (hide === 1) {
  67. // 隐藏工作表
  68. worksheet.state = "hidden";
  69. }
  70. setStyleAndValue(table.data, worksheet);
  71. setMerge(merge, worksheet);
  72. setBorder(borderInfo, worksheet);
  73. setImages(table, worksheet, workbook);
  74. setColumnWidth(columnWidth, worksheet);
  75. //行高设置50导出后在ms-excel中打开显示25,在wps-excel中打开显示50这个bug不会修复
  76. setRowHeight(rowHeight, worksheet, excelType);
  77. setFrozen(frozen, worksheet);
  78. setRowHidden(rowhidden, worksheet);
  79. setColHidden(colhidden, worksheet);
  80. setFilter(filterSelect, worksheet);
  81. return true;
  82. });
  83. const buffer = workbook.xlsx.writeBuffer().then((data) => {
  84. // console.log('data', data)
  85. const blob = new Blob([data], {
  86. type: "application/vnd.ms-excel;charset=utf-8",
  87. });
  88. return new Promise((resolve, reject) => {
  89. resolve(blob);
  90. });
  91. });
  92. return buffer;
  93. }
  94. /**
  95. * 列宽
  96. * @param columnWidth
  97. * @param worksheet
  98. */
  99. var setColumnWidth = function (columnWidth, worksheet) {
  100. for (let key in columnWidth) {
  101. worksheet.getColumn(parseInt(key) + 1).width = columnWidth[key] / 7.5;
  102. }
  103. };
  104. /**
  105. * 行高
  106. * @param rowHeight
  107. * @param worksheet
  108. * @param excelType
  109. */
  110. var setRowHeight = function (rowHeight, worksheet, excelType) {
  111. for (let key in rowHeight) {
  112. worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75;
  113. }
  114. /*//导出的文件用wps打开和用excel打开显示的行高大一倍
  115. if (excelType == "wps") {
  116. for (let key in rowHeight) {
  117. worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75
  118. }
  119. }
  120. if (excelType == "office" || excelType == undefined) {
  121. for (let key in rowHeight) {
  122. worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 1.5
  123. }
  124. }*/
  125. };
  126. /**
  127. * 合并单元格
  128. * @param luckyMerge
  129. * @param worksheet
  130. */
  131. var setMerge = function (luckyMerge = {}, worksheet) {
  132. const mergearr = Object.values(luckyMerge);
  133. mergearr.forEach(function (elem) {
  134. // elem格式:{r: 0, c: 0, rs: 1, cs: 2}
  135. // 按开始行,开始列,结束行,结束列合并(相当于 K10:M12)
  136. worksheet.mergeCells(
  137. elem.r + 1,
  138. elem.c + 1,
  139. elem.r + elem.rs,
  140. elem.c + elem.cs
  141. );
  142. });
  143. };
  144. /**
  145. * 设置边框
  146. * @param luckyBorderInfo
  147. * @param worksheet
  148. */
  149. var setBorder = function (luckyBorderInfo, worksheet) {
  150. if (!Array.isArray(luckyBorderInfo)) return;
  151. //合并边框信息
  152. var mergeCellBorder = function (border1, border2) {
  153. if (undefined === border1 || Object.keys(border1).length === 0)
  154. return border2;
  155. return Object.assign({}, border1, border2);
  156. };
  157. // console.log('luckyBorderInfo', luckyBorderInfo)
  158. luckyBorderInfo.forEach(function (elem) {
  159. // 现在只兼容到borderType 为range的情况
  160. // console.log('ele', elem)
  161. if (elem.rangeType === "range") {
  162. let border = borderConvert(elem.borderType, elem.style, elem.color);
  163. let rang = elem.range[0];
  164. let row = rang.row;
  165. let column = rang.column;
  166. let rowBegin = row[0];
  167. let rowEnd = row[1];
  168. let colBegin = column[0];
  169. let colEnd = column[1];
  170. //处理外边框的情况 没有直接对应的外边框 需要转换成上下左右
  171. if (border.all) {
  172. //全部边框
  173. let b = border.all;
  174. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  175. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  176. let border = {};
  177. border["top"] = b;
  178. border["bottom"] = b;
  179. border["left"] = b;
  180. border["right"] = b;
  181. worksheet.getCell(i, y).border = border;
  182. // console.log(i, y, worksheet.getCell(i, y).border)
  183. }
  184. }
  185. } else if (border.top) {
  186. //上边框
  187. let b = border.top;
  188. let i = row[0] + 1;
  189. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  190. let border = {};
  191. border["top"] = b;
  192. worksheet.getCell(i, y).border = border;
  193. // console.log(i, y, worksheet.getCell(i, y).border)
  194. }
  195. } else if (border.right) {
  196. //右边框
  197. let b = border.right;
  198. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  199. let y = column[1] + 1;
  200. let border = {};
  201. border["right"] = b;
  202. worksheet.getCell(i, y).border = border;
  203. // console.log(i, y, worksheet.getCell(i, y).border)
  204. }
  205. } else if (border.bottom) {
  206. //下边框
  207. let b = border.bottom;
  208. let i = row[1] + 1;
  209. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  210. let border = {};
  211. border["bottom"] = b;
  212. worksheet.getCell(i, y).border = border;
  213. // console.log(i, y, worksheet.getCell(i, y).border)
  214. }
  215. } else if (border.left) {
  216. //左边框
  217. let b = border.left;
  218. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  219. let y = column[0] + 1;
  220. let border = {};
  221. border["left"] = b;
  222. worksheet.getCell(i, y).border = border;
  223. // console.log(i, y, worksheet.getCell(i, y).border)
  224. }
  225. } else if (border.outside) {
  226. //外边框
  227. let b = border.outside;
  228. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  229. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  230. let border = {};
  231. if (i === rowBegin + 1) {
  232. border["top"] = b;
  233. }
  234. if (i === rowEnd + 1) {
  235. border["bottom"] = b;
  236. }
  237. if (y === colBegin + 1) {
  238. border["left"] = b;
  239. }
  240. if (y === colEnd + 1) {
  241. border["right"] = b;
  242. }
  243. let border1 = worksheet.getCell(i, y).border;
  244. worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
  245. // console.log(i, y, worksheet.getCell(i, y).border)
  246. }
  247. }
  248. } else if (border.inside) {
  249. //内边框
  250. let b = border.inside;
  251. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  252. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  253. let border = {};
  254. if (i !== rowBegin + 1) {
  255. border["top"] = b;
  256. }
  257. if (i !== rowEnd + 1) {
  258. border["bottom"] = b;
  259. }
  260. if (y !== colBegin + 1) {
  261. border["left"] = b;
  262. }
  263. if (y !== colEnd + 1) {
  264. border["right"] = b;
  265. }
  266. let border1 = worksheet.getCell(i, y).border;
  267. worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
  268. // console.log(i, y, worksheet.getCell(i, y).border)
  269. }
  270. }
  271. } else if (border.horizontal) {
  272. //内侧水平边框
  273. let b = border.horizontal;
  274. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  275. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  276. let border = {};
  277. if (i === rowBegin + 1) {
  278. border["bottom"] = b;
  279. } else if (i === rowEnd + 1) {
  280. border["top"] = b;
  281. } else {
  282. border["top"] = b;
  283. border["bottom"] = b;
  284. }
  285. let border1 = worksheet.getCell(i, y).border;
  286. worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
  287. // console.log(i, y, worksheet.getCell(i, y).border)
  288. }
  289. }
  290. } else if (border.vertical) {
  291. //内侧垂直边框
  292. let b = border.vertical;
  293. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  294. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  295. let border = {};
  296. if (y === colBegin + 1) {
  297. border["right"] = b;
  298. } else if (y === colEnd + 1) {
  299. border["left"] = b;
  300. } else {
  301. border["left"] = b;
  302. border["right"] = b;
  303. }
  304. let border1 = worksheet.getCell(i, y).border;
  305. worksheet.getCell(i, y).border = mergeCellBorder(border1, border);
  306. // console.log(i, y, worksheet.getCell(i, y).border)
  307. }
  308. }
  309. } else if (border.none) {
  310. //当luckysheet边框为border-none的时候表示没有边框 则将对应的单元格border清空
  311. for (let i = row[0] + 1; i <= row[1] + 1; i++) {
  312. for (let y = column[0] + 1; y <= column[1] + 1; y++) {
  313. worksheet.getCell(i, y).border = {};
  314. // console.log(i, y, worksheet.getCell(i, y).border)
  315. }
  316. }
  317. }
  318. }
  319. if (elem.rangeType === "cell") {
  320. // col_index: 2
  321. // row_index: 1
  322. // b: {
  323. // color: '#d0d4e3'
  324. // style: 1
  325. // }
  326. const { col_index, row_index } = elem.value;
  327. const borderData = Object.assign({}, elem.value);
  328. delete borderData.col_index;
  329. delete borderData.row_index;
  330. let border = addborderToCell(borderData, row_index, col_index);
  331. let border1 = worksheet.getCell(row_index + 1, col_index + 1).border;
  332. worksheet.getCell(row_index + 1, col_index + 1).border = mergeCellBorder(
  333. border1,
  334. border
  335. );
  336. // console.log(row_index + 1, col_index + 1, worksheet.getCell(row_index + 1, col_index + 1).border)
  337. }
  338. });
  339. };
  340. var setStyleAndValue = function (cellArr, worksheet) {
  341. if (!Array.isArray(cellArr)) return;
  342. cellArr.forEach(function (row, rowid) {
  343. row.every(function (cell, columnid) {
  344. if (!cell) return true;
  345. let fill = fillConvert(cell.bg);
  346. let font = fontConvert(
  347. cell.ff,
  348. cell.fc,
  349. cell.bl,
  350. cell.it,
  351. cell.fs,
  352. cell.cl,
  353. cell.ul
  354. );
  355. let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
  356. let value = "";
  357. if (cell.f) {
  358. value = { formula: cell.f, result: cell.v };
  359. } else if (!cell.v && cell.ct && cell.ct.s) {
  360. // xls转为xlsx之后,内部存在不同的格式,都会进到富文本里,即值不存在与cell.v,而是存在于cell.ct.s之后
  361. // value = cell.ct.s[0].v
  362. cell.ct.s.forEach((arr) => {
  363. value += arr.v;
  364. });
  365. } else {
  366. value = cell.v;
  367. }
  368. // style 填入到_value中可以实现填充色
  369. let letter = createCellPos(columnid);
  370. let target = worksheet.getCell(letter + (rowid + 1));
  371. // console.log('1233', letter + (rowid + 1))
  372. for (const key in fill) {
  373. target.fill = fill;
  374. break;
  375. }
  376. target.font = font;
  377. target.alignment = alignment;
  378. target.value = value;
  379. return true;
  380. });
  381. });
  382. };
  383. /*/!**
  384. * 设置带样式的值
  385. * @param cellArr
  386. * @param worksheet
  387. *!/
  388. var setStyleAndValue = function (cellArr, worksheet) {
  389. if (!Array.isArray(cellArr)) return
  390. cellArr.forEach(function (row, rowid) {
  391. row.every(function (cell, columnid) {
  392. if (!cell) return true
  393. let fill = fillConvert(cell.bg)
  394. let font = fontConvert(
  395. cell.ff,
  396. cell.fc,
  397. cell.bl,
  398. cell.it,
  399. cell.fs,
  400. cell.cl,
  401. cell.un
  402. )
  403. let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
  404. let value = ''
  405. if (cell.f) {
  406. value = {formula: cell.f, result: cell.v}
  407. } else if (!cell.v && cell.ct && cell.ct.s) {
  408. // xls转为xlsx之后,内部存在不同的格式,都会进到富文本里,即值不存在与cell.v,而是存在于cell.ct.s之后
  409. let richText = [];
  410. let cts = cell.ct.s
  411. for (let i = 0; i < cts.length; i++) {
  412. let rt = {
  413. text: cts[i].v,
  414. font: fontConvert(cts[i].ff, cts[i].fc, cts[i].bl, cts[i].it, cts[i].fs, cts[i].cl, cts[i].un)
  415. }
  416. richText.push(rt)
  417. }
  418. value = {
  419. richText: richText
  420. };
  421. } else {
  422. //设置值为数字格式
  423. if (cell.v !== undefined && cell.v !== '') {
  424. var v = +cell.v;
  425. if (isNaN(v)) v = cell.v
  426. value = v
  427. }
  428. }
  429. // style 填入到_value中可以实现填充色
  430. let letter = createCellPos(columnid)
  431. let target = worksheet.getCell(letter + (rowid + 1))
  432. // console.log('1233', letter + (rowid + 1))
  433. for (const key in fill) {
  434. target.fill = fill
  435. break
  436. }
  437. target.font = font
  438. target.alignment = alignment
  439. target.value = value
  440. try {
  441. //设置单元格格式
  442. target.numFmt = cell.ct.fa;
  443. } catch (e) {
  444. console.warn(e)
  445. }
  446. return true
  447. })
  448. })
  449. }*/
  450. //获取图片在单元格的位置
  451. /*var getImagePosition = function (num, arr) {
  452. let index = 0;
  453. let minIndex;
  454. let maxIndex;
  455. for (let i = 0; i < arr.length; i++) {
  456. if (num < arr[i]) {
  457. index = i;
  458. break;
  459. }
  460. }
  461. if (index == 0) {
  462. minIndex = 0;
  463. maxIndex = 1;
  464. return Math.abs((num - 0) / (arr[maxIndex] - arr[minIndex])) + index;
  465. } else if (index == arr.length - 1) {
  466. minIndex = arr.length - 2;
  467. maxIndex = arr.length - 1;
  468. } else {
  469. minIndex = index - 1;
  470. maxIndex = index;
  471. }
  472. let min = arr[minIndex];
  473. let max = arr[maxIndex];
  474. let radio = Math.abs((num - min) / (max - min)) + index;
  475. return radio;
  476. };*/
  477. var getImagePosition = function (num, arr) {
  478. let returnObj = null;
  479. for (let i = 0; i < arr.length; i++) {
  480. const item = arr[i];
  481. if (num < item) {
  482. const cell = i > 0 ? arr[i] - arr[i - 1] : item;
  483. // 偏移量单位为Emu,所以单元格的宽高需要转换为Emu的单位,cellWidth = cellWidth*10000
  484. const cellInEmu = cell * 10000;
  485. const rowOrCol =
  486. i > 0 ? (num - arr[i - 1]) / (arr[i] - arr[i - 1]) + i : num / item;
  487. const native = Math.floor(rowOrCol);
  488. const nativeOff = parseInt(
  489. new Big(rowOrCol).minus(native).toNumber() * cellInEmu
  490. );
  491. returnObj = { rowOrCol, native, nativeOff };
  492. break;
  493. }
  494. }
  495. if (returnObj == undefined || returnObj == null) {
  496. const currentV = arr[arr.length - 1];
  497. const frontV = arr[arr.length - 2];
  498. const cell = currentV - frontV;
  499. arr.push(currentV + cell);
  500. returnObj = getImagePosition(num, arr);
  501. }
  502. return returnObj;
  503. };
  504. /**
  505. * 设置图片
  506. * @param images
  507. * @param worksheet
  508. * @param workbook
  509. */
  510. /*var setImages = function (table, worksheet, workbook) {
  511. let {
  512. images,
  513. visibledatacolumn,//所有行的位置
  514. visibledatarow //所有列的位置
  515. } = {...table}
  516. if (typeof images != 'object') return;
  517. for (let key in images) {
  518. // 通过 base64 将图像添加到工作簿
  519. const myBase64Image = images[key].src;
  520. //开始行 开始列 结束行 结束列
  521. const item = images[key];
  522. const imageId = workbook.addImage({
  523. base64: myBase64Image,
  524. extension: 'png'
  525. });
  526. const col_st = getImagePosition(item.default.left,visibledatacolumn);
  527. const row_st = getImagePosition(item.default.top,visibledatarow);
  528. console.log(item.default.left,item.default.top,visibledatacolumn,visibledatarow)
  529. console.log("1111111111111::" + col_st)
  530. console.log("2222222222222::" + row_st)
  531. //模式1,图片左侧与luckysheet位置一样,像素比例保持不变,但是,右侧位置可能与原图所在单元格不一致
  532. worksheet.addImage(imageId, {
  533. tl: { col: col_st, row: row_st},
  534. ext: { width: item.default.width, height: item.default.height },
  535. });
  536. //模式2,图片四个角位置没有变动,但是图片像素比例可能和原图不一样
  537. // const w_ed = item.default.left+item.default.width;
  538. // const h_ed = item.default.top+item.default.height;
  539. // const col_ed = getImagePosition(w_ed,visibledatacolumn);
  540. // const row_ed = getImagePosition(h_ed,visibledatarow);
  541. // worksheet.addImage(imageId, {
  542. // tl: { col: col_st, row: row_st},
  543. // br: { col: col_ed, row: row_ed},
  544. // });
  545. }
  546. };*/
  547. var setImages = function (table, worksheet, workbook) {
  548. let {
  549. images,
  550. visibledatacolumn, //所有行的位置
  551. visibledatarow, //所有列的位置
  552. } = { ...table };
  553. if (typeof images != "object") return;
  554. for (let key in images) {
  555. // 通过 base64 将图像添加到工作簿
  556. const myBase64Image = images[key].src;
  557. //开始行 开始列 结束行 结束列
  558. const item = images[key];
  559. const imageId = workbook.addImage({
  560. base64: myBase64Image,
  561. extension: "png",
  562. });
  563. // 只有设置tl、br的图片可以被luckysheet识别并展示,设置ext宽高的不行
  564. const col_st = getImagePosition(item.default.left, visibledatacolumn);
  565. const row_st = getImagePosition(item.default.top, visibledatarow);
  566. const w_ed = item.default.left + item.default.width;
  567. const h_ed = item.default.top + item.default.height;
  568. const col_ed = getImagePosition(w_ed, visibledatacolumn);
  569. const row_ed = getImagePosition(h_ed, visibledatarow);
  570. // 需要设置nativeCol,nativeColOff,nativeRow,nativeRowOff,可以省略col,row
  571. if (row_ed != undefined && col_ed != undefined) {
  572. worksheet.addImage(imageId, {
  573. tl: {
  574. nativeCol: col_st.native,
  575. nativeColOff: col_st.nativeOff,
  576. nativeRow: row_st.native,
  577. nativeRowOff: row_st.nativeOff,
  578. },
  579. br: {
  580. nativeCol: col_ed.native,
  581. nativeColOff: col_ed.nativeOff,
  582. nativeRow: row_ed.native,
  583. nativeRowOff: row_ed.nativeOff,
  584. },
  585. editAs: "oneCell",
  586. });
  587. }
  588. }
  589. };
  590. /**
  591. * 冻结行列
  592. * @param frozen
  593. * @param worksheet
  594. */
  595. var setFrozen = function (frozen = {}, worksheet) {
  596. switch (frozen.type) {
  597. // 冻结首行
  598. case "row": {
  599. worksheet.views = [{ state: "frozen", xSplit: 0, ySplit: 1 }];
  600. break;
  601. }
  602. // 冻结首列
  603. case "column": {
  604. worksheet.views = [{ state: "frozen", xSplit: 1, ySplit: 0 }];
  605. break;
  606. }
  607. // 冻结行列
  608. case "both": {
  609. worksheet.views = [{ state: "frozen", xSplit: 1, ySplit: 1 }];
  610. break;
  611. }
  612. // 冻结行到选区
  613. case "rangeRow": {
  614. let row = frozen.range.row_focus + 1;
  615. worksheet.views = [{ state: "frozen", xSplit: 0, ySplit: row }];
  616. break;
  617. }
  618. // 冻结列到选区
  619. case "rangeColumn": {
  620. let column = frozen.range.column_focus + 1;
  621. worksheet.views = [{ state: "frozen", xSplit: column, ySplit: 0 }];
  622. break;
  623. }
  624. // 冻结行列到选区
  625. case "rangeBoth": {
  626. let row = frozen.range.row_focus + 1;
  627. let column = frozen.range.column_focus + 1;
  628. worksheet.views = [{ state: "frozen", xSplit: column, ySplit: row }];
  629. }
  630. }
  631. };
  632. /**
  633. * 行隐藏
  634. * @param rowhidden
  635. * @param worksheet
  636. */
  637. var setRowHidden = function (rowhidden = {}, worksheet) {
  638. for (const key in rowhidden) {
  639. //如果当前行没有内容则隐藏不生效
  640. const row = worksheet.getRow(parseInt(key) + 1);
  641. row.hidden = true;
  642. }
  643. };
  644. /**
  645. * 列隐藏
  646. * @param colhidden
  647. * @param worksheet
  648. */
  649. var setColHidden = function (colhidden = {}, worksheet) {
  650. for (const key in colhidden) {
  651. const column = worksheet.getColumn(parseInt(key) + 1);
  652. column.hidden = true;
  653. }
  654. };
  655. /**
  656. * 自动筛选器
  657. * @param filter
  658. * @param worksheet
  659. */
  660. var setFilter = function (filter = {}, worksheet) {
  661. if (Object.keys(filter).length === 0) return;
  662. const from = {
  663. row: filter.row[0] + 1,
  664. column: filter.column[0] + 1,
  665. };
  666. const to = {
  667. row: filter.row[1] + 1,
  668. column: filter.column[1] + 1,
  669. };
  670. worksheet.autoFilter = {
  671. from: from,
  672. to: to,
  673. };
  674. };
  675. /*var fillConvert = function (bg) {
  676. if (!bg) {
  677. return {}
  678. }
  679. // const bgc = bg.replace('#', '')
  680. let fill = {
  681. type: 'pattern',
  682. pattern: 'solid',
  683. fgColor: {argb: bg.startsWith("#") ? bg.replace('#', '') : colorRGBtoHex(bg).replace("#", "")},
  684. }
  685. return fill;
  686. }
  687. var fontConvert = function (
  688. ff = 0,
  689. fc = '#000000',
  690. bl = 0,
  691. it = 0,
  692. fs = 10,
  693. cl = 0,
  694. ul = 0
  695. ) {
  696. // luckysheet:ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  697. const luckyToExcel = {
  698. 0: '微软雅黑',
  699. 1: '宋体(Song)',
  700. 2: '黑体(ST Heiti)',
  701. 3: '楷体(ST Kaiti)',
  702. 4: '仿宋(ST FangSong)',
  703. 5: '新宋体(ST Song)',
  704. 6: '华文新魏',
  705. 7: '华文行楷',
  706. 8: '华文隶书',
  707. 9: 'Arial',
  708. 10: 'Times New Roman ',
  709. 11: 'Tahoma ',
  710. 12: 'Verdana',
  711. num2bl: function (num) {
  712. return num !== 0
  713. }
  714. }
  715. // 出现Bug,导入的时候ff为luckyToExcel的val
  716. let font = {
  717. name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
  718. family: 1,
  719. size: fs,
  720. color: {argb: fc.startsWith("#") ? fc.replace('#', '') : colorRGBtoHex(fc).replace("#", "")},
  721. bold: luckyToExcel.num2bl(bl),
  722. italic: luckyToExcel.num2bl(it),
  723. underline: luckyToExcel.num2bl(ul),
  724. strike: luckyToExcel.num2bl(cl)
  725. }
  726. return font
  727. }*/
  728. var fillConvert = function (bg) {
  729. if (!bg) {
  730. return {};
  731. }
  732. // const bgc = bg.replace('#', '')
  733. let fill = {
  734. type: "pattern",
  735. pattern: "solid",
  736. fgColor: { argb: bg.replace("#", "") },
  737. };
  738. return fill;
  739. };
  740. var fontConvert = function (
  741. ff = 0,
  742. fc = "#000000",
  743. bl = 0,
  744. it = 0,
  745. fs = 10,
  746. cl = 0,
  747. ul = 0
  748. ) {
  749. // luckysheet:ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  750. const luckyToExcel = {
  751. 0: "微软雅黑",
  752. 1: "宋体(Song)",
  753. 2: "黑体(ST Heiti)",
  754. 3: "楷体(ST Kaiti)",
  755. 4: "仿宋(ST FangSong)",
  756. 5: "新宋体(ST Song)",
  757. 6: "华文新魏",
  758. 7: "华文行楷",
  759. 8: "华文隶书",
  760. 9: "Arial",
  761. 10: "Times New Roman ",
  762. 11: "Tahoma ",
  763. 12: "Verdana",
  764. num2bl: function (num) {
  765. return num === 0 ? false : true;
  766. },
  767. };
  768. // 出现Bug,导入的时候ff为luckyToExcel的val
  769. let font = {
  770. name: typeof ff === "number" ? luckyToExcel[ff] : ff,
  771. family: 1,
  772. size: fs,
  773. color: { argb: fc.replace("#", "") },
  774. bold: luckyToExcel.num2bl(bl),
  775. italic: luckyToExcel.num2bl(it),
  776. underline: luckyToExcel.num2bl(ul),
  777. strike: luckyToExcel.num2bl(cl),
  778. };
  779. return font;
  780. };
  781. var alignmentConvert = function (
  782. vt = "default",
  783. ht = "default",
  784. tb = "default",
  785. tr = "default"
  786. ) {
  787. // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  788. const luckyToExcel = {
  789. vertical: {
  790. 0: "middle",
  791. 1: "top",
  792. 2: "bottom",
  793. default: "middle",
  794. },
  795. horizontal: {
  796. 0: "center",
  797. 1: "left",
  798. 2: "right",
  799. default: "center",
  800. },
  801. wrapText: {
  802. 0: false,
  803. 1: false,
  804. 2: true,
  805. default: false,
  806. },
  807. textRotation: {
  808. 0: 0,
  809. 1: 45,
  810. 2: -45,
  811. 3: "vertical",
  812. 4: 90,
  813. 5: -90,
  814. default: 0,
  815. },
  816. };
  817. let alignment = {
  818. vertical: luckyToExcel.vertical[vt],
  819. horizontal: luckyToExcel.horizontal[ht],
  820. wrapText: luckyToExcel.wrapText[tb],
  821. textRotation: luckyToExcel.textRotation[tr],
  822. };
  823. return alignment;
  824. };
  825. var borderConvert = function (borderType, style = 1, color = "#000") {
  826. // 对应luckysheet的config中borderinfo的的参数
  827. if (!borderType) {
  828. return {};
  829. }
  830. const luckyToExcel = {
  831. type: {
  832. "border-all": "all",
  833. "border-top": "top",
  834. "border-right": "right",
  835. "border-bottom": "bottom",
  836. "border-left": "left",
  837. "border-outside": "outside",
  838. "border-inside": "inside",
  839. "border-horizontal": "horizontal",
  840. "border-vertical": "vertical",
  841. "border-none": "none",
  842. },
  843. style: {
  844. 0: "none",
  845. 1: "thin",
  846. 2: "hair",
  847. 3: "dotted",
  848. 4: "dashDot", // 'Dashed',
  849. 5: "dashDot",
  850. 6: "dashDotDot",
  851. 7: "double",
  852. 8: "medium",
  853. 9: "mediumDashed",
  854. 10: "mediumDashDot",
  855. 11: "mediumDashDotDot",
  856. 12: "slantDashDot",
  857. 13: "thick",
  858. },
  859. };
  860. let border = {};
  861. border[luckyToExcel.type[borderType]] = {
  862. style: luckyToExcel.style[style],
  863. color: { argb: color.replace("#", "") },
  864. };
  865. return border;
  866. };
  867. function addborderToCell(borders, row_index, col_index) {
  868. let border = {};
  869. const luckyExcel = {
  870. type: {
  871. l: "left",
  872. r: "right",
  873. b: "bottom",
  874. t: "top",
  875. },
  876. style: {
  877. 0: "none",
  878. 1: "thin",
  879. 2: "hair",
  880. 3: "dotted",
  881. 4: "dashDot", // 'Dashed',
  882. 5: "dashDot",
  883. 6: "dashDotDot",
  884. 7: "double",
  885. 8: "medium",
  886. 9: "mediumDashed",
  887. 10: "mediumDashDot",
  888. 11: "mediumDashDotDot",
  889. 12: "slantDashDot",
  890. 13: "thick",
  891. },
  892. };
  893. // console.log('borders', borders)
  894. for (const bor in borders) {
  895. // console.log(bor)
  896. if (borders[bor].color.indexOf("rgb") === -1) {
  897. border[luckyExcel.type[bor]] = {
  898. style: luckyExcel.style[borders[bor].style],
  899. color: { argb: borders[bor].color.replace("#", "") },
  900. };
  901. } else {
  902. border[luckyExcel.type[bor]] = {
  903. style: luckyExcel.style[borders[bor].style],
  904. color: { argb: borders[bor].color },
  905. };
  906. }
  907. }
  908. return border;
  909. }
  910. function createCellPos(n) {
  911. let ordA = "A".charCodeAt(0);
  912. let ordZ = "Z".charCodeAt(0);
  913. let len = ordZ - ordA + 1;
  914. let s = "";
  915. while (n >= 0) {
  916. s = String.fromCharCode((n % len) + ordA) + s;
  917. n = Math.floor(n / len) - 1;
  918. }
  919. return s;
  920. }
  921. //rgb(255,255,255)转16进制 #ffffff
  922. function colorRGBtoHex(color) {
  923. color = color.replace("rgb", "").replace("(", "").replace(")", "");
  924. var rgb = color.split(",");
  925. var r = parseInt(rgb[0]);
  926. var g = parseInt(rgb[1]);
  927. var b = parseInt(rgb[2]);
  928. return "#" + ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1);
  929. }
  930. export { exportExcel, getExcelBlob };