前端/后台生成Excel

需要引入XLSX

前端：
1.读取：

// html
<div class="read">
  <input type="file" name="excel" id="excel" />
  <button onclick="readFile()">Read File</button>
</div>
// js
// XLSX
<script lang="javascript" src="dist/xlsx.full.min.js"></script>
let workbook;
let excelData;
let excel = document
  .getElementById("excel")
  .addEventListener("change", e => {
    let files = e.target.files;
    let fileReader = new FileReader();
    fileReader.onload = function(ev) {
      try {
        let data = ev.target.result;
        workbook = XLSX.read(data, {
          type: "binary"
        }); // 以二进制流方式读取得到整份excel表格对象
      } catch (e) {
        console.log("文件类型不正确");
        return;
      }
      console.log(workbook);
    };
    // 以二进制方式打开文件
    fileReader.readAsBinaryString(files[0]);
  });
const readFile = () => {
  let sheet = workbook.Sheets[workbook.SheetNames[0]];
  let data = XLSX.utils.sheet_to_html(sheet);
  excelData = XLSX.utils.sheet_to_json(sheet);
};

2.生成：

// html
<div class="download">
  <button onclick="download()">download</button>
</div>

// XLSX
<script lang="javascript" src="dist/xlsx.full.min.js"></script>
// saveAs
<script lang="javascript" src="dist/FileSaver.min.js"></script>

const download = () => {
  let sheet = XLSX.utils.json_to_sheet(excelData);
  const wb = XLSX.utils.book_new();
  wb.SheetNames.push("sheet1");
  wb.Sheets["sheet1"] = sheet;

  let wopts = { bookType: "xlsx", bookSST: false, type: "array" };

  let wbout = XLSX.write(wb, wopts);

  /* the saveAs call downloads a file on the local machine */
  saveAs(
    new Blob([wbout], { type: "application/octet-stream" }),
    "demo.xlsx"
  );
};

后端(node)：
1.读取：

const XLSX = require('xlsx');

let workbook = XLSX.readFile(`${path.resolve('./')}/data.xlsx`);
let SheetNames = workbook.SheetNames  // ['sheet1','sheet2'];
let sheet = workbook.Sheet['sheet1'];
let data = XLSX.utils.sheet_to_json(sheet);
data = [{'title1':'data1'},{'title1':'data2'}]

2.生成：

const XLSX = require('xlsx');

let aoa = [['tittle1','tittle2'],['data1','data2']];
let workbook = XLSX.utils.book_new();
let worksheet = XLSX.utils.aoa_to_sheet(aoa);
workbook.SheetNames.push('Sheet 1');
workbook.Sheets['Sheet 1'] = worksheet;
XLSX.writeFile(workbook, `${path.resolve('./')}/data.xlsx`, { compression: true });