云函数调用node-xlsx报错Cannot read properties of undefined (reading 'length')

初始报错信息 #

TypeError: Cannot read properties of undefined (reading 'length')
at xlsx (cloudfunctions\excel\node_modules\node-xlsx\lib\xlsx\xlsx.js:174:23)
at Object.build (cloudfunctions\excel\node_modules\node-xlsx\lib\node-xlsx.js:14:16)
at d.exports.main [as handler] (cloudfunctions\excel\index.js:57:29)
at \code\package.nw\js\common\cloud-functions-debugger-server\worker\node.js:1:44797
at processTicksAndRejections (node:internal/process/task_queues:96:5)

原本云函数可正常运行，执行npm install moment-timezone并通过npm audit fix修复漏洞后出现上述报错。已定位到报错来自node-xlsx的xlsx.js第174行w = file.worksheets.length;语句，尝试删除node_modules重新安装无效。

云函数调用node-xlsx的index.js代码 #

//index.js of cloud function to call node-xlsx
const cloud = require('wx-server-sdk')
const moment = require("moment-timezone")
cloud.init({ 
  traceUser: true,
  env: cloud.DYNAMIC_CURRENT_ENV })

const xlsx = require('node-xlsx');

exports.main = async(event, context) => {
  try {
    let {userdata} = event
    
      // javascript date
    //  console.log(new Date()); // 2021-03-16T08:04:07.441Z (UTC+0
    //  console.log(moment().tz("Asia/Shanghai").format()); // 2021-03-16T16:04:07+08:00 (UTC+8)
    let curDate = moment().tz("Asia/Shanghai").format();
    //let curDate =new Date().toISOString()
    //1,
    let dataCVS = 'order_list_'+curDate+'.xlsx'
    //2，
    let alldata = [];
    //let row = ['status','receiver']; //
    let row=['_id' , 'orderDate', 'region','address','receiver','phone'
    ,'cloth_id','cloth_notes','num','cloth_price','totalPrice','status']
    alldata.push(row);
    console.log('excel before loop:'+alldata)
    for (let key in userdata) {
      
      for (let i in userdata[key]['product']){
        let arr = [];
        arr.push(userdata[key]._id);
        arr.push(userdata[key].orderDate);
        arr.push(userdata[key]['address'].region[0]+'-'
        +userdata[key]['address'].region[1]+'-'
        +userdata[key]['address'].region[2]);
        arr.push(userdata[key]['address'].address);
        arr.push(userdata[key]['address'].receiver);
        arr.push(userdata[key]['address'].phone);

        arr.push(userdata[key]['product'][i].cloth_id);
        arr.push(userdata[key]['product'][i].cloth_notes);
        arr.push(userdata[key]['product'][i].cloth_price);
        arr.push(userdata[key]['product'][i].num);
        arr.push(userdata[key].totalPrice);
        arr.push(userdata[key].status);
        alldata.push(arr)
      }
    }
    //console.log('excel has data:'+alldata)
    //3，
    var buffer = await xlsx.build([{
      name: "mySheetName",
      data: alldata
    }]);
    console.log('buffer have data : '+buffer);
    //4，
    return await cloud.uploadFile({
      cloudPath: dataCVS,
      fileContent: buffer, //
      
    })

  } catch (e) {
    console.error(e)
    return e
  }
}

调整代码后的新报错 #

将fileContent: buffer改为fileContent: excelbuffer后出现以下报错：

at Function.DelayedStream.create (cloudfunctions\excel\node_modules\delayed-stream\lib\delayed_stream.js:33:10)
at FormData.CombinedStream.append (cloudfunctions\excel\node_modules\combined-stream\lib\combined_stream.js:45:37)
at FormData.append (cloudfunctions\excel\node_modules\form-data\lib\form_data.js:74:3)
at appendFormValue (cloudfunctions\excel\node_modules\request\request.js:326:21)
at Request.init (cloudfunctions\excel\node_modules\request\request.js:337:11)
at new Request (cloudfunctions\excel\node_modules\request\request.js:127:8)
at Object.request [as default] (cloudfunctions\excel\node_modules\request\index.js:53:10)
at body (cloudfunctions\excel\node_modules@cloudbase\node-sdk\lib\storage\index.js:41:26)
at new Promise ()
at Object.uploadFile (cloudfunctions\excel\node_modules@cloudbase\node-sdk\lib\storage\index.js:40:22)

解决方案 #

1. 移除xlsx.build的await关键字 #

node-xlsx的build方法是同步函数，不需要用await修饰。原代码中错误的await会把同步返回的Buffer包装为Promise，导致内部处理异常。修改为：

var buffer = xlsx.build([{
  name: "mySheetName",
  data: alldata
}]);

2. 修正fileContent变量名 #

调整代码时错误替换了变量名，excelbuffer未定义，需改回原变量buffer：

return await cloud.uploadFile({
  cloudPath: dataCVS,
  fileContent: buffer
})

3. 回退node-xlsx兼容版本 #

npm audit fix可能升级了node-xlsx到不兼容版本，查看package-lock.json中的历史版本，指定版本重新安装：

npm install node-xlsx@<原版本号> --save

例如原版本为0.17.0，则执行npm install node-xlsx@0.17.0 --save

4. 校验输入数据结构 #

在循环前添加userdata格式校验，避免空数据导致alldata生成异常：

if (!userdata || typeof userdata !== 'object' || Object.keys(userdata).length === 0) {
  throw new Error('userdata格式错误或为空');
}

内容的提问来源于stack exchange，提问作者Erik Johnsson