JOSNToExcel.js 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103
  1. import { saveAs } from 'file-saver'
  2. import XLSX from 'xlsx'
  3. const jsontoExcel = {
  4. downloadExl: function (header, data, fileName) {
  5. // var keys = Object.keys(data[0]);
  6. let copyData = JSON.parse(JSON.stringify(data))
  7. let firstRow = {};
  8. let keys = header.map((item) => {
  9. this.setDataForKey(firstRow, item.fieldName, item.headerName);
  10. return item.fieldName
  11. });
  12. // header.forEach(function (item) {
  13. // firstRow[item] = item;
  14. // });
  15. copyData.unshift(firstRow);
  16. let content = {};
  17. // 把json格式的数据转为excel的行列形式
  18. let sheetsData = copyData.map((item, rowIndex) => {
  19. return keys.map((key, columnIndex) => {
  20. return Object.assign({}, {
  21. value: this.dataForKey(item, key),
  22. // value: item[key],
  23. position: (columnIndex > 25 ? this.getCharCol(columnIndex) : String.fromCharCode(65 + columnIndex)) + (rowIndex + 1),
  24. });
  25. });
  26. }).reduce(function (prev, next) {
  27. return prev.concat(next);
  28. });
  29. sheetsData.forEach((item) => {
  30. content[item.position] = {
  31. v: item.value
  32. };
  33. });
  34. //设置区域,比如表格从A1到D10,SheetNames:标题,
  35. let coordinate = Object.keys(content);
  36. let workBook = {
  37. SheetNames: ["Sheet1"],
  38. Sheets: {
  39. "Sheet1": Object.assign({}, content, { "!ref": coordinate[0] + ":" + coordinate[coordinate.length - 1] }),
  40. }
  41. };
  42. //这里的数据是用来定义导出的格式类型
  43. var excelData = XLSX.write(workBook, { bookType: "xlsx", bookSST: false, type: "binary" });
  44. var blob = new Blob([this.string2ArrayBuffer(excelData)], { type: "" });
  45. saveAs(blob, `${fileName}.xlsx`);
  46. },
  47. //字符串转字符流
  48. string2ArrayBuffer: function (s) {
  49. var buf = new ArrayBuffer(s.length);
  50. var view = new Uint8Array(buf);
  51. for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  52. return buf;
  53. },
  54. // 将指定的自然数转换为26进制表示。映射关系:[0-25] -> [A-Z]。
  55. getCharCol: function (n) {
  56. let s = "",
  57. m = 0
  58. while (n > 0) {
  59. m = n % 26 + 1
  60. s = String.fromCharCode(m + 64) + s
  61. n = (n - m) / 26
  62. }
  63. return s
  64. },
  65. //根据key值去对象中取对应的值
  66. dataForKey: function (data, key) {
  67. let arr = key.split(".")
  68. if (arr && arr[0]) {
  69. for (let i = 0; i < arr.length; i++) {
  70. if (arr[i] && data[arr[i]]) {
  71. data = data[arr[i]]
  72. } else {
  73. return ''
  74. }
  75. }
  76. return data
  77. }
  78. },
  79. //根据key值去设置对象的值
  80. setDataForKey: function (data, key, value) {
  81. let arr = key.split(".")
  82. if (arr && arr[0]) {
  83. for (let i = 0; i < arr.length; i++) {
  84. if (!(arr[i] && data[arr[i]])) {
  85. data[arr[i]] = {}
  86. }
  87. if (value !== undefined) {
  88. if (i == arr.length - 1) {
  89. data[arr[i]] = value
  90. }
  91. }
  92. data = data[arr[i]]
  93. }
  94. }
  95. }
  96. }
  97. export default jsontoExcel