Excel读写（NodeJs）

Excel读写

一、安装插件

这里用到的插件是 xlsx
直接 npm i xlsx
详细文档：https://www.npmjs.com/package/xlsx

二、上代码

ts里面：

//首先导入xlsx包
import * as XLSX from 'xlsx';

// 打开并获取Excel对象
openExcel(evt):Promise<any>{
  const target: DataTransfer = <DataTransfer>(evt.target);
  if (target.files.length !== 1) throw new Error('Cannot use multiple files');
  const reader: FileReader = new FileReader();
  return new Promise((resolve,reject)=> {
    reader.onload = (e: any) => {
      /* read workbook */
      const bstr: string = e.target.result;
      const wb: XLSX.WorkBook = XLSX.read(bstr, {type: 'binary'});
      //wb就是返回的excel对象
      resolve(wb);
    };
    reader.readAsBinaryString(target.files[0]);
  })
};

Excel读取数据方法（写了三个方法，请按需索取）：

//Excel读取数据(方法一)  sheet_to_json方法
//wb是excel对象，range是读取的范围
//range = {s:{c:0,r:3}, e:{c:3,r:6}}; 
//s是start，e是end，c是列，r是行，行列号都是从0开始
readExcelF1(wb, range){
  /* read worksheet */
  var first_sheet_name = wb.SheetNames[0];
  var ws = wb.Sheets[first_sheet_name];
  console.log(ws["!ref"]);
  console.log(XLSX.utils.decode_range(ws["!ref"]));
  console.log(ws);
  let inputdata = [];
  inputdata = (XLSX.utils.sheet_to_json(ws, {header: 1, range: range}));
  console.log(inputdata);
};

//Excel读取数据(方法二)  for循环的方法
readExcelF2(wb, range){
  var outdata = {};
  /* read worksheet */
  var first_sheet_name = wb.SheetNames[0];
  var ws = wb.Sheets[first_sheet_name];
  console.log(ws);
  for(var R = range.s.r; R <= range.e.r; ++R) {
    for(var C = range.s.c; C <= range.e.c; ++C) {
      var cell_address = {c:C, r:R};
      var cell_ref = XLSX.utils.encode_cell(cell_address);
      var desired_cell = ws[cell_ref];
      var desired_value = (desired_cell ? desired_cell.v : "");
      console.log(desired_value);
      outdata[cell_ref] = desired_value;
    }
  }
  console.log(outdata);
};

//Excel读取数据(方法三)
//range = {s:{c:2,r:3}, e:{c:-1,r:-1}};
//-1表示不限制范围，基本思想是先求出获得的excel对象的整体范围，
//来替代-1
readExcelF3(wb, range){
  /* read worksheet */
  var first_sheet_name = wb.SheetNames[0];
  var ws = wb.Sheets[first_sheet_name];
  let allrange = XLSX.utils.decode_range(ws["!ref"]);

  if (range.e.c == -1){
    range.e.c = allrange.e.c;
  };

  if (range.e.r == -1){
    range.e.r = allrange.e.r;
  };

  let inputdata = [];
  inputdata = (XLSX.utils.sheet_to_json(ws, {header: 1, range: range}));
  console.log(inputdata);
};

Excel写入数据方法（写了两个方法，请按需索取）：

/*Excel写入数据(方法一)
wb：写入的excel对象
dataArr：需要写入的json数据    
dataArr = [
      { 姓名: "张三", 性别: "男", 年龄: 12 },
      { 姓名: "李四", 性别: "女", 年龄: 13 }
    ];
origin：行顺序写入的起点("A2")*/
writeExcelF1(wb, dataArr, origin){
  let keyArr = [];
  for(let key in dataArr[0]){
    keyArr.push(key);
    console.log(keyArr);
  }

  /* read worksheet */
  var first_sheet_name = wb.SheetNames[0];
  var ws = wb.Sheets[first_sheet_name];

  XLSX.utils.sheet_add_json(ws, dataArr, {skipHeader: true, origin: origin, header: keyArr});
  XLSX.writeFile(wb, 'out.xlsx');
};

//按列顺序填充
writeExcelF2(wb, dataArr, origin){
  var cell_address = XLSX.utils.decode_cell(origin);
  let sr = cell_address.r;
  let sc = cell_address.c;
  /* read worksheet */
  var first_sheet_name = wb.SheetNames[0];
  var ws = wb.Sheets[first_sheet_name];

  for(let i=0;i<dataArr.length;i++){
    //row回到原始状态
    cell_address.r = sr;
    for (let key in dataArr[i])
    {
      console.log(dataArr[i][key]);
      XLSX.utils.sheet_add_aoa(ws, [[dataArr[i][key]]], {origin: cell_address});
      cell_address.r ++;
    }
    cell_address.c ++;
  }
  XLSX.writeFile(wb, 'out.xlsx');
};

//测试Excel
testExcel(e){
  this.openExcel(e).then((res)=>{
    let jsonData = [
      { 姓名: "张三", 性别: "男", 年龄: 12 },
      { 姓名: "李四", 性别: "女", 年龄: 13 }
    ];
    var first_sheet_name = res.SheetNames[0];
    var ws = res.Sheets[first_sheet_name];
    let range = {s:{c:2,r:3}, e:{c:-1,r:-1}};
    // this.writeExcelF2(res, jsonData,"A2");
    this.readExcelF3(res, range);
  })
};

.html

  <div>导入</div>
  <input type="file" (change)="testExcel($event)" multiple="false" />