如何设置单元数据的公式（导出到.xlsx）SheetJS js-xlsx：https：//github.com/SheetJS/js-xlsx

2 回答

• 0

  单元格对象具有保护 f ，这是您要使用的公式 . 在这里，您可以看到所有选项：https://github.com/SheetJS/js-xlsx#cell-object .

  以下是使用公式的示例：

  var xlsx = require('xlsx');
  
  //workBook class
  function Workbook() {
      if(!(this instanceof Workbook)) return new Workbook();
      this.SheetNames = [];
      this.Sheets = {};
  }
  
  var exportBook = new Workbook();
  
  var worksheet = {};
  
  var cell = {f: 'A2+A3'};
  
  var cellRef = xlsx.utils.encode_cell({r:0, c:0});
  
  var range = {s:{r: 0, c: 0},
              e: {r: 10, c: 10}};
  
  
  
  worksheet[cellRef] = cell;
  worksheet['!ref'] = xlsx.utils.encode_range(range);
  
  exportBook.SheetNames.push('test');
  exportBook.Sheets.test = worksheet;
  
  
  xlsx.writeFile(exportBook, 'formula sample.xlsx');
  

  这里，A1应该具有公式A2 A3 .

  希望能帮助到你 ：）

  回复于 2024-04-29T02:11:05+08:00
• 2

  这是我的解决方案

  function download() {
      TheResourceService.get({ xId: $stateParams.xId }, function(result) {
          var sheetName = 'first_sheet';
          var wopts = { bookType: 'xlsx', bookSST: true, type: 'binary' };
          var fileName = "the_excel_file.xlsx";
  
          var columns = ['id', 'name', 'point'];
          var data = [
              [1, 'Kyle', 20],
              [2, 'Allen', 32],
              [3, 'Chris', 18],
              [4, 'Tim', 11]
          ];
  
          var wb = XLSX.utils.book_new();
          var ws = uigrid_to_sheet(data, columns);
  
          ws['!ref'] = XLSX.utils.encode_range({
              s: { c: 0, r: 0 },
              e: { c: 3, r: 1 + data.length + 1 }
          });
          ws['C6'] = { f: 'SUM(C2:C5)' };
          ws['B6'] = { v: 'Total' };
  
          XLSX.utils.book_append_sheet(wb, ws, sheetName);
          var wbout = XLSX.write(wb, wopts);
          saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), fileName);
      })
  
  }
  
  
  function uigrid_to_sheet(data, columns) {
      var o = [],
          oo = [],
          i = 0,
          j = 0;
  
      /* column headers */
      for (j = 0; j < columns.length; ++j) oo.push((columns[j]));
      o.push(oo);
  
      /* table data */
      for (i = 0; i < data.length; ++i) {
          oo = [];
          for (j = 0; j < data[i].length; ++j) oo.push((data[i][j]));
          o.push(oo);
      }
      /* aoa_to_sheet converts an array of arrays into a worksheet object */
      return XLSX.utils.aoa_to_sheet(o);
  }
  
  function s2ab(s) {
      if (typeof ArrayBuffer !== 'undefined') {
          var buf = new ArrayBuffer(s.length);
          var view = new Uint8Array(buf);
          for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
          return buf;
      } else {
          var buf = new Array(s.length);
          for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
          return buf;
      }
  }
  

  回复于 2024-04-29T02:11:05+08:00