express+mysql+vue,从零搭建一个商城管理系统17--查询订单导出excel

提示：学习express，搭建管理系统

---

前言

需求：主要学习express，所以先写service部分

一、安装exceljs

npm install exceljs --save

二、新建config/exportExcel.js

exportExcel.js

const Excel = require('exceljs');
const exportExcel = async (data)=>{
	let workbook = new Excel.Workbook();
	let worksheet = workbook.addWorksheet(data.userName+'-'+data.orderId);
	worksheet.columns = [
		{ header:'用户名', key:'userName' },
		{ header:'订单号', key:'orderId' },
		{ header:'订单总价', key:'orderTotal' },
		{ header:'商户名称', key:'shopName' },
		{ header:'商品名称', key:'goodsName' },
		{ header:'规格', key:'specs' },
		{ header:'数量', key:'count' },
		{ header:'单个商品总价', key:'goodsTotal' },
		{ header:'折扣', key:'preferential' },
	]
	let rows = [];
	for(let i=0;i<data.goods.length;i++){
		let oneGoods = data.goods[i];
		worksheet.addRow({ 
			userName: data.userName, 
			orderId: data.orderId,  
			orderTotal: data.total, 
			shopName: oneGoods.shopName, 
			goodsName: oneGoods.goodsName, 
			specs:oneGoods.specs, 
			count:oneGoods.count,
			goodsTotal:oneGoods.total, 
			preferential:oneGoods.preferential, 
		});
	}
	// worksheet.addRow(rows);
	// A列 从第2行开始--到data.goods.length+1行合并  因为头部占了一行   所以第二行开始   到n+1行结束
	worksheet.mergeCells('A2:A+'+(data.goods.length+1));
	worksheet.mergeCells('B2:B'+(data.goods.length+1));
	worksheet.mergeCells('C2:C'+(data.goods.length+1));
	//合并后的单元格文字水平垂直居中
	worksheet.getCell('A2').style.alignment = worksheet.getCell('B2').style.alignment = worksheet.getCell('C2').style.alignment = { vertical: 'middle', horizontal: 'center' };
	try{
		await workbook.xlsx.writeFile(data.userName+'-'+data.orderId+'.xlsx');
		return {
			msg:'表格下载成功',
			code:200,
			success:false
		}
	}catch(err){
		return {
			msg:err,
			code:500,
			success:false
		}
	}
}

module.exports = exportExcel;

三、修改dao/order.js

dao/order.js

const { Op } = require("sequelize");
const OrderModel = require('../models/order');
const OrderItemModel = require('../models/orderItem');
const GoodsDao = require('../dao/goods');
const md5 = require("md5");
const secretKey = 'longlongago';

const errFun = (msg,data,code)=>{
    return {
        code:code||500,
        success:false,
        msg:msg||'操作失败',
        data:data
    }
}
const sucFun = (data,msg)=>{
    return {
        code:200,
        success:true,
        msg:msg||'操作成功',
        data,
    }
}


const OrderDao = {
    //添加商品到订单
    addGoodsToOrder:async(data)=>{
        const goods = data.goods;
        //查看库存数量
        let inventoryNotEnough = [];
        const resultGoods = await GoodsDao.queryGoodsInventory(data)||[];
        resultGoods.forEach(item=>{
            goods.forEach(dItem=>{
                if(item.goodsId == dItem.goodsId && item.inventory<dItem.count)inventoryNotEnough.push(item);
            })
        })
        if(inventoryNotEnough.length>0)return errFun('以下商品库存不足',inventoryNotEnough);
        //生成orderId
        const orderId = md5(goods.userId + secretKey + new Date().getTime());
        //订单总价
        let orderTotal = 0;
        //商品total错误
        let wrongTotalGoodsData = [];
        //订单里存入商品信息
        let jsonGoods = [];
        //orderItem存入信息
        let orderItemGoods = [];
        goods.forEach(item=>{
            let preferential = item.preferential||[];
            if(preferential.length>0){
                preferential.forEach(pItem=>{
                    let nowTotal = parseFloat((item.goodsTotal||(item.price*item.count)).toFixed(2));//Math.round((item.goodsTotal||(item.price*item.count))*100)/100;
                    if(pItem.operation == 'x'){
                        item.goodsTotal = parseFloat((nowTotal*pItem.value).toFixed(2));//Math.round((nowTotal*pItem.value)*100)/100;
                    }else if(pItem.operation == '-'){
                        item.goodsTotal = parseFloat((nowTotal-pItem.value).toFixed(2));//Math.round((nowTotal-pItem.value)*100)/100;
                    }
                })
            }
            //保留2位小数，向上取整
            item.goodsTotal = parseFloat(item.goodsTotal.toFixed(2));//Math.round(item.goodsTotal*100)/100;
            orderTotal = parseFloat((orderTotal+item.goodsTotal).toFixed(2));
            if(item.goodsTotal != item.total)wrongTotalGoodsData.push(item);
            jsonGoods.push({
                goodsId:item.goodsId,
                goodsName:item.goodsName,
                price:item.price,
                count:item.count
            })
            orderItemGoods.push({
                orderId,
                userId:data.userId,
                userName:data.userName,
                shopId:item.shopId,
                shopName:item.shopName,
                goodsId:item.goodsId,
                goodsName:item.goodsName,
                price:item.price,
                count:item.count,
                specs:item.specs,
                preferential:JSON.stringify(item.preferential),
                total:item.total,
            })
        });
        // orderTotal = orderTotal/100;
        if(wrongTotalGoodsData.length > 0)return errFun('以下商品总价计算错误！！',wrongTotalGoodsData);
        if(orderTotal != data.total)return errFun('订单总价计算错误！！',{orderTotal,...data});
        try{
            let json = JSON.stringify(jsonGoods);
            const orderResult = await OrderModel.create({
                orderId,
                userId:data.userId,
                userName:data.userName,
                total:data.total,
                goods:json,
            });
            if(!orderResult)return errFun('下单失败！！');
            try{
                const orderItemResult = await OrderItemModel.bulkCreate(orderItemGoods);
                if(!orderItemResult)return errFun('子订单下单失败！！');
                //修改下单商品库存   单纯调用  结果不影响下单成功
                GoodsDao.changeOrderGoodsInventory(resultGoods,data);
                return sucFun({
                    id: orderResult.id,
                    orderId: orderResult.orderId,
                    userId: orderResult.userId,
                    userName: orderResult.userName,
                    total: orderResult.total,
                    goods:orderItemResult
                },'下单成功');
            }catch(oiErr){
                return errFun(err);
            }
        }catch(oErr){
            return errFun('下单失败！！',oErr);
        }
    },
    //通过orderId获取订单数据
    queryOrderByOrderId:async(data)=>{
        try{
            const orderData = await OrderModel.findOne({where:{orderId:data.orderId}});
            try{
                const orderItemData = await OrderItemModel.findAll({where:{orderId:data.orderId}});
                console.log(orderItemData)
                if(orderData&&orderItemData){
                    return sucFun({
                        id: orderData.id,
                        orderId: orderData.orderId,
                        userId: orderData.userId,
                        userName: orderData.userName,
                        total: orderData.total,
                        goods:orderItemData
                    },'订单查询成功');
                }
            }catch(err){
                return errFun('订单查询失败！！',oErr);
            }
        }catch(err){
            return errFun('订单查询失败！！',oErr);
        }
    },
    //通过商品ID查询商品信息
    queryGoodsByUserIdFromOrder:async(data)=>{
        //根据goodsId查询goods
        const goodsData = await OrderModel.findAll({where:{userId:data.userId}});
        let shopIds = [];
        let resultData = [];
        if(goodsData&&goodsData.length>0){
            goodsData.forEach(item=>{if(shopIds.indexOf(item.shopId)==-1)shopIds.push(item.shopId)});
            goodsData.forEach(item=>{
                let index = shopIds.indexOf(item.shopId);
                let goodsOne = {
                    userId: item.userId,
                    shopId: item.shopId,
                    shopName: item.shopName,
                    goodsId: item.goodsId,
                    goodsName: item.goodsName,
                    count: item.count,
                    price: item.price,
                    specs: item.specs
                }
                if(!resultData[index]){
                    resultData[index] = {
                        shopId:item.shopId,
                        shopName:item.shopName,
                        goods:[ goodsOne ]
                    }
                }else{
                    resultData[index].goods.push(goodsOne);
                }
            });
        }
        if(!resultData||await resultData.length<1)errFun('订单空空如也');
        return sucFun(resultData,'查询成功');
    },
}

module.exports = OrderDao;

四、修改routes/order.js

routes/order.js

const OrderDao = require('../dao/order');
const logistics = require('../config/logistics');
const exportExcel = require('../config/exportExcel');
const OrderRoutes = (router)=>{
    router.post('/order/addGoodsToOrder',async (req,res)=>{
        const result = await OrderDao.addGoodsToOrder(req.body);
        res.json(result);
    });
    router.get('/order/queryGoodsByUserIdFromOrder',async (req,res)=>{
        const result = await OrderDao.queryGoodsByUserIdFromOrder(req.query);
        res.json(result);
    });
    router.post('/order/queryOrderByOrderId',async (req,res)=>{
        const result = await OrderDao.queryOrderByOrderId(req.body);
        res.json(result);
    });
    router.post('/order/queryOrderLogistics',async (req,res)=>{
        const result = await logistics.query(req.body);
        res.json(result);
    });
    router.post('/order/exportExcel',async (req,res)=>{
        const orderResult = await OrderDao.queryOrderByOrderId(req.body);
        let result = {code:500,success:false,msg:'导出excel失败'};
        if(orderResult.success){
            result = await exportExcel(orderResult.data);
        }else{
            result = orderResult;
        }
        res.json(result);
    });
}
module.exports = OrderRoutes;

五、添加商品到购物车

url:http://localhost:1990/order/exportExcel
params:{
   "orderId": "420cdcbd6ef3075f41c8613ff8bcf013"
}

总结

踩坑路漫漫长@~@