微信云开发 将筛选数据导出Excel文件

完整示例代码：generateExcel.js

示例：将销售表里面的数据，根据日期筛选，查询每种支付方式的统计金额,导出Excel
思路：
1、先将需要的数据查询出来，存一个数据列表
2、创建excel表数据结构：表头 + 生成的数据列表 + 表尾，注：表头和数据列表字段一一对应
3、使用组件node-xlsx创建excel文件
4、将文件上传至云存储
5、将云存储返回的fileID生成临时URL返回至小程序，小程序有链接就可以访问查看了
注意：这里的临时还是永久路径是根据云存储的存储权限决定的，如果权限选择的是所有用户可访问，那这个URL就是永久路径

// 云函数入口文件
const cloud = require('wx-server-sdk');

//操作excel组件
const xlsx = require('node-xlsx')

cloud.init({
    env: cloud.DYNAMIC_CURRENT_ENV,

}) // 使用当前云环境

const db = cloud.database({
    // doc单条查询不存在时，不报错
    throwOnNotFound: false,
});

const _ = db.command;


//请求失败返回
function reqFail(msg) {
    return {
        data: 0,
        code: -1,
        message: msg || '操作失败,重试'
    }
}

//请求成功返回体
function reqSuccess(data, msg) {
    return {
        data: data || null,
        code: 200,
        message: msg || '操作成功'
    }
}


// 云函数入口函数
exports.main = async (event, context) => {
    const wxContext = cloud.getWXContext()

    try {

        event.endDate = '2024-05-20';
        //========================1、获取需要的数据源==========================
        //开始日期有值
        let filterDate = {};
        if (event.endDate) {
            //结束日期时间戳
            filterDate.endTime = `${event.endDate} 23:59:59`;
            //开始日期时间戳
            if (event.startDate) {
                filterDate.startTime = `${event.startDate} 00:00:00`;
            } else {
                filterDate.startTime = `${event.endDate.substr(0,8)}01 00:00:00`;
            }
        } else {
            throw new Error("查询日期不能为空")
        }

        //查询交易完成的订单，存在店员不点完成，隔了几天在处理的情况
        let orderInfo = await db.collection("order_list")
            .where({
                orderType: 0, //购物订单
                orderState: 2, //订单为完成
                completeTime: _.gte(filterDate.startTime).and(_.lte(filterDate.endTime)) //店员点击完成时间为节点
            })
            .orderBy("finishTime", "asc")
            .get()
            .catch(err => {
                console.info(err)
                throw new Error("查询订单失败");
            });

        //整理订单数据
        let orderList = orderInfo.data;
        if (orderList.length) {
            let newOrderList = [];
            let wxTotal = 0;
            let memberTotal = 0;
            let discountTotal = 0;

            let scanPayTotal = 0;
            let cashPayTotal = 0;

            orderList.forEach(item => {
                item.newFinishTime = item.completeTime.substr(0, 10); //截取年月日,如2000-01-21

                wxTotal += Number(item.wxPay || 0); //总微信支付
                memberTotal += Number(item.memberPay || 0); //总会员支付
                scanPayTotal += Number(item.scanPay || 0); //总扫码支付
                cashPayTotal += Number(item.cashPay || 0); //总现金支付
                discountTotal += Number(item.discountTotal || 0); //总优惠

                //相同日期的下标
                let newOrderIndex = -1;
                newOrderList.forEach((newItem, newIndex) => {
                    if (newItem.newFinishTime == item.newFinishTime) {
                        newOrderIndex = newIndex;
                    }
                })
                if (newOrderIndex != -1) {
                    let existOrderItem = newOrderList[newOrderIndex];
                    existOrderItem["totalWx"] += Number(item.wxPay || 0);
                    existOrderItem["totalMember"] += Number(item.memberPay || 0);
                    existOrderItem["totalScanPay"] += Number(item.scanPay || 0);
                    existOrderItem["totalCashPay"] += Number(item.cashPay || 0);
                    existOrderItem["totalDiscount"] += Number(item.discountTotal || 0);
                } else {
                    newOrderList.push({
                        newFinishTime: item.newFinishTime,
                        totalWx: Number(item.wxPay || 0),
                        totalMember: Number(item.memberPay || 0),
                        totalScanPay: Number(item.scanPay || 0),
                        totalCashPay: Number(item.cashPay || 0),
                        totalDiscount: Number(item.discountTotal || 0)
                    })
                }
            })
            //==================================================================


            //========================2、组装excel数据==========================
            let allData = []; //excel表所有数据
            //1.定义excel名字
            let excelName = `贴贴本月销售`;
            //2.定义excel表头名
            let rowHead = ['销售日期', '微信', '会员', '扫码付', '现金付', '优惠'];
            allData.push(rowHead);
            //3.取数据源，一一对应
            for (let i = 0; i < newOrderList.length; i++) {
                let rowItem = [];
                let orderItem = newOrderList[i];
                rowItem.push(orderItem.newFinishTime);
                rowItem.push(orderItem.totalWx);
                rowItem.push(orderItem.totalMember);
                rowItem.push(orderItem.totalScanPay);
                rowItem.push(orderItem.totalCashPay);
                rowItem.push(orderItem.totalDiscount);
                allData.push(rowItem);
            }
            //4.存一个汇总
            let rowFooter = [];
            rowFooter.push('总计');
            rowFooter.push(wxTotal);
            rowFooter.push(memberTotal);
            rowFooter.push(scanPayTotal);
            rowFooter.push(cashPayTotal);
            rowFooter.push(discountTotal);
            allData.push(rowFooter);

            //5.生成excel文件，并把数据保存在云存储
            let buffer = await xlsx.build([{
                name: excelName,
                data: allData
            }])
            let fileInfo = await cloud.uploadFile({
                cloudPath: `./excel/${excelName}_${event.endDate}.xlsx`,
                fileContent: buffer
            })

            //6.生成临时路径返回
            console.info(`上传云存储成功的fileID：${fileInfo.fileID}`);
            let tempInfo = await cloud.getTempFileURL({
                fileList: [fileInfo.fileID]
            })
            return reqSuccess(tempInfo.fileList[0].tempFileURL);
        } else {
            return reqSuccess('数据为空');
        }
    } catch (err) {
        return reqFail(err.message)
    }
}