Export2Excel.js 4.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144
  1. /* eslint-disable */
  2. import('file-saver');
  3. import('./Blob.js');
  4. import XLSX from 'xlsx';
  5. function generateArray(table) {
  6. var out = [];
  7. var rows = table.querySelectorAll('tr');
  8. var ranges = [];
  9. for (var R = 0; R < rows.length; ++R) {
  10. var outRow = [];
  11. var row = rows[R];
  12. var columns = row.querySelectorAll('td');
  13. for (var C = 0; C < columns.length; ++C) {
  14. var cell = columns[C];
  15. var colspan = cell.getAttribute('colspan');
  16. var rowspan = cell.getAttribute('rowspan');
  17. var cellValue = cell.innerText;
  18. if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
  19. //Skip ranges
  20. ranges.forEach(function (range) {
  21. if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
  22. for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
  23. }
  24. });
  25. //Handle Row Span
  26. if (rowspan || colspan) {
  27. rowspan = rowspan || 1;
  28. colspan = colspan || 1;
  29. ranges.push({ s: { r: R, c: outRow.length }, e: { r: R + rowspan - 1, c: outRow.length + colspan - 1 } });
  30. };
  31. //Handle Value
  32. outRow.push(cellValue !== "" ? cellValue : null);
  33. //Handle Colspan
  34. if (colspan)
  35. for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
  36. }
  37. out.push(outRow);
  38. }
  39. return [out, ranges];
  40. };
  41. function datenum(v, date1904) {
  42. if (date1904) v += 1462;
  43. var epoch = Date.parse(v);
  44. return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
  45. }
  46. function sheet_from_array_of_arrays(data, opts) {
  47. var ws = {};
  48. var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
  49. for (var R = 0; R != data.length; ++R) {
  50. for (var C = 0; C != data[R].length; ++C) {
  51. if (range.s.r > R) range.s.r = R;
  52. if (range.s.c > C) range.s.c = C;
  53. if (range.e.r < R) range.e.r = R;
  54. if (range.e.c < C) range.e.c = C;
  55. var cell = { v: data[R][C] };
  56. if (cell.v == null) continue;
  57. var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
  58. if (typeof cell.v === 'number') cell.t = 'n';
  59. else if (typeof cell.v === 'boolean') cell.t = 'b';
  60. else if (cell.v instanceof Date) {
  61. cell.t = 'n';
  62. cell.z = XLSX.SSF._table[14];
  63. cell.v = datenum(cell.v);
  64. } else cell.t = 's';
  65. ws[cell_ref] = cell;
  66. }
  67. }
  68. if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  69. return ws;
  70. }
  71. function Workbook() {
  72. if (!(this instanceof Workbook)) return new Workbook();
  73. this.SheetNames = [];
  74. this.Sheets = {};
  75. }
  76. function s2ab(s) {
  77. var buf = new ArrayBuffer(s.length);
  78. var view = new Uint8Array(buf);
  79. for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  80. return buf;
  81. }
  82. export function export_table_to_excel(id) {
  83. var theTable = document.getElementById(id);
  84. console.log('a')
  85. var oo = generateArray(theTable);
  86. var ranges = oo[1];
  87. /* original data */
  88. var data = oo[0];
  89. var ws_name = "SheetJS";
  90. console.log(data);
  91. var wb = new Workbook(),
  92. ws = sheet_from_array_of_arrays(data);
  93. /* add ranges to worksheet */
  94. // ws['!cols'] = ['apple', 'banan'];
  95. ws['!merges'] = ranges;
  96. /* add worksheet to workbook */
  97. wb.SheetNames.push(ws_name);
  98. wb.Sheets[ws_name] = ws;
  99. var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
  100. saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), "test.xlsx")
  101. }
  102. function formatJson(jsonData) {
  103. console.log(jsonData)
  104. }
  105. export function export_json_to_excel(th, jsonData, defaultTitle) {
  106. /* original data */
  107. var data = jsonData;
  108. data.unshift(th);
  109. var ws_name = "SheetJS";
  110. var wb = new Workbook(),
  111. ws = sheet_from_array_of_arrays(data);
  112. /* add worksheet to workbook */
  113. wb.SheetNames.push(ws_name);
  114. wb.Sheets[ws_name] = ws;
  115. var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
  116. var title = defaultTitle || '列表'
  117. saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
  118. }