export_excel.js 7.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date
  7. * @version
  8. */
  9. const SpreadExcelObj = (function() {
  10. const _createHideSpread = function () {
  11. const div = document.createElement("div");
  12. div.setAttribute('id', 'exportExcelSpread');
  13. //$(div).css("position", "absolute").css('top', 0).css('left', 0).css('height', 300).css('width', 500).css('z-index', 999);
  14. //document.body.insertBefore(div, null);
  15. div.style.display = 'none';
  16. return div;
  17. };
  18. const _removeHideSpread = function (div) {
  19. document.body.removeChild(div);
  20. };
  21. const exportSimpleXlsxSheetData = function (sheet, setting, data) {
  22. SpreadJsObj.beginMassOperation(sheet);
  23. sheet.options.isProtected = false;
  24. sheet.setColumnCount(setting.cols.length);
  25. sheet.setRowCount(setting.headRows + data.length);
  26. for (let iRow = 0; iRow < setting.headRowHeight.length; iRow++) {
  27. sheet.setRowHeight(iRow, setting.headRowHeight[iRow]);
  28. }
  29. for (let iCol = 0; iCol < setting.cols.length; iCol++) {
  30. const col = setting.cols[iCol];
  31. const title = col.title.split('|');
  32. const colSpan = col.colSpan ? col.colSpan.split('|') : ['1'],
  33. rowSpan = col.rowSpan ? col.rowSpan.split('|') : ['1'];
  34. for (let i = 0; i < title.length; i++) {
  35. const cell = sheet.getCell(i, iCol);
  36. cell.text(title[i]).wordWrap(true).hAlign(1).vAlign(1).font(setting.headerFont);
  37. if ((colSpan[i] !== '' && colSpan[i] !== '1') || (rowSpan[i] !== '' && rowSpan[i] !== '1')) {
  38. sheet.addSpan(i, iCol, parseInt(rowSpan[i]), parseInt(colSpan[i]));
  39. }
  40. }
  41. sheet.setColumnWidth(iCol, col.width);
  42. if (col.visible !== undefined && col.visible !== null) {
  43. sheet.setColumnVisible(iCol, col.visible);
  44. }
  45. }
  46. const autoFit = !!setting.cols.find(x => { return x.wordWrap; });
  47. for (let iRow = 0; iRow < data.length; iRow++) {
  48. const curRow = setting.headRows + iRow;
  49. const d = data[iRow];
  50. for (let iCol = 0; iCol < setting.cols.length; iCol++) {
  51. const cell = sheet.getCell(curRow, iCol);
  52. const col = setting.cols[iCol];
  53. if (col.field !== '' && d[col.field]) {
  54. cell.value(col.getValue ? col.getValue(d) : d[col.field]);
  55. if (col.wordWrap) cell.wordWrap(true);
  56. if (typeof d[col.field] === 'string') {
  57. cell.formatter('@');
  58. }
  59. }
  60. if (col.font) {
  61. cell.font(col.font);
  62. } else if (setting.font) {
  63. cell.font(setting.font);
  64. }
  65. if (col.formatter) {
  66. cell.formatter(col.formatter);
  67. cell.value(cell.text());
  68. }
  69. cell.hAlign(col.hAlign).vAlign(1);
  70. }
  71. if (autoFit) sheet.autoFitRow(curRow);
  72. }
  73. SpreadJsObj.endMassOperation(sheet);
  74. };
  75. const exportXlsxSheetGroup = function(sheet, setting, group) {
  76. if (!group) return;
  77. for (const g of group) {
  78. sheet.rowOutlines.group(g.start + (setting.headRows || 0), g.count);
  79. }
  80. };
  81. const exportSimpleXlsxSheet = function (setting, data, file, group) {
  82. const div = _createHideSpread();
  83. const spread = SpreadJsObj.createNewSpread(div, true);
  84. const sheet = spread.getActiveSheet();
  85. exportSimpleXlsxSheetData(sheet, setting, data);
  86. exportXlsxSheetGroup(sheet, setting, group);
  87. const excelIo = new GC.Spread.Excel.IO();
  88. const sJson = JSON.stringify(spread.toJSON());
  89. excelIo.save(sJson, function(blob) {
  90. saveAs(blob, file);
  91. _removeHideSpread(div);
  92. });
  93. };
  94. const exportSimpleXlsxSheets = function (sheets, file) {
  95. if (!sheets || sheets.length === 0) return;
  96. const div = _createHideSpread();
  97. const spread = new spreadNS.Workbook(div, {sheetCount: sheets.length});
  98. for (const [i, sheetData] of sheets.entries()) {
  99. const sheet = spread.getSheet(i);
  100. sheet.name(sheetData.name);
  101. exportSimpleXlsxSheetData(sheet, sheetData.setting, sheetData.data);
  102. }
  103. const excelIo = new GC.Spread.Excel.IO();
  104. const sJson = JSON.stringify(spread.toJSON());
  105. excelIo.save(sJson, function(blob) {
  106. saveAs(blob, file);
  107. _removeHideSpread(div);
  108. });
  109. };
  110. const exportSpread2XlsxWithHeader = function (spread, file) {
  111. spread.getActiveSheet().options.isProtected = false;
  112. const excelIo = new GC.Spread.Excel.IO();
  113. const sJson = JSON.stringify(spread.toJSON({columnHeadersAsFrozenRows: true, rowHeadersAsFrozenColumns: true}));
  114. excelIo.save(sJson, function(blob) {
  115. saveAs(blob, file);
  116. });
  117. spread.getActiveSheet().options.isProtected = true;
  118. };
  119. return {exportSimpleXlsxSheet, exportSpread2XlsxWithHeader, exportSimpleXlsxSheets}
  120. })();
  121. const XLSXObj = (function () {
  122. const transportSheetData = function (setting, data) {
  123. const headerStyle = {
  124. font: { sz: 10, bold: true },
  125. alignment: { horizontal: 'center' },
  126. };
  127. const sHeader = setting.header
  128. .map((v, i) => Object.assign({}, {v: v, s: headerStyle, position: String.fromCharCode(65+i) + 1 }))
  129. .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v, s: next.s}}), {});
  130. const sData = data
  131. .map((v, i) => v.map((k, j) => Object.assign({}, {
  132. v: k ? k : '',
  133. s: { font: { sz: 10 }, alignment: {horizontal: setting.hAlign[j]}},
  134. position: String.fromCharCode(65+j) + (i+2) })))
  135. .reduce((prev, next) => prev.concat(next))
  136. .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v, s: next.s}}), {});
  137. const output = Object.assign({}, sHeader, sData);
  138. const outputPos = Object.keys(output);
  139. const result = Object.assign({}, output,
  140. {'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1]},
  141. {'!cols': setting.width.map((w) => Object.assign({}, {wpx: w}))});
  142. return result;
  143. };
  144. const exportXlsxSheet = function (setting, data, file) {
  145. const result = transportSheetData(setting, data);
  146. const xlsxData = {
  147. SheetNames: ['Sheet1'],
  148. Sheets: {
  149. 'Sheet1': result
  150. }
  151. };
  152. const blob = xlsxUtils.format2Blob(xlsxData);
  153. saveAs(blob, file);
  154. };
  155. const exportXlsxSheets = function (sheets, file) {
  156. const xlsxData = { SheetNames: [], Sheets: {} };
  157. for (const sheet of sheets) {
  158. const xlsxSheet = transportSheetData(sheet.setting, sheet.data);
  159. xlsxData.SheetNames.push(sheet.name);
  160. xlsxData.Sheets[sheet.name] = xlsxSheet;
  161. }
  162. const blob = xlsxUtils.format2Blob(xlsxData);
  163. saveAs(blob, file);
  164. };
  165. return { exportXlsxSheet, exportXlsxSheets }
  166. })();