PHPExcel 表格导出数据

PHPExcel开发文档

https://www.kancloud.cn/chunyu/php_basic_knowledge/1041088

1、控制器-平台订单-导出

  //功能：订单查询导出-导出
    //功能：平台订单-导出
    public function orderTLoad()
    {
        set_time_limit(0);
        //@ini_set('memory_limit','512M');
        $shopId = request()->param('shopId/d') ? request()->param('shopId/d') : 0;
        if ($shopId <= 0) errorMsg(400, '请选择店铺！');
        $isSync = request()->param('isSync/d') ? request()->param('isSync/d') : 0;
        $istatus = request()->param('istatus/d') ? request()->param('istatus/d') : 0;
        $datetime = request()->param('datetime/s') ? request()->param('datetime/s') : '';
        if (!$datetime) errorMsg(400, '请选择日期！');
        $where['o.companyId'] = $this->companyId;
        $where['o.deleted'] = 0;
        if ($shopId > 0) {
            $where['o.shopId'] = $shopId;
        }
        if ($isSync == 1 || $isSync == 2) {
            $where['o.isSync'] = $isSync - 1;
        }
        if ($istatus > 0) {
            switch ($istatus) {
                case 1:
                    $where['o.logisticsId'] = ['>', 0];
                    break;
                case 2:
                    $where['o.logisticsId'] = 0;
                    break;
                case 3:
                    $where['o.isBackLogis'] = 2;
                    break;
                case 4:
                    $where['o.isBackLogis'] = 3;
                    break;
                case 5:
                    $where['o.logisticsId'] = ['>', 0];
                    $where['o.isBackLogis'] = 0;
                    $where['s.isGrant'] = 1;
                    break;
                case 6:
                    $where['o.status'] = 2;
                    break;
            }
        }
        $owhere = '';
        if ($datetime) {
            $owhere = "o.createTime>'{$datetime} 00:00:00' and o.createTime<='{$datetime} 23:59:59'";
        }

        $list = OrderThird::alias('o')->join('shop s', 's.shopId=o.shopId', 'left')->join('plat_cate pc', 'pc.platCateId=s.platCateId', 'left')->join('logistics l', 'l.logisticsId=o.logisticsId', 'left')->join('order_third_goods og', 'og.torderId=o.torderId', 'left')->where($where)->where($owhere)->field('o.order_id,o.consignee,o.address,o.buyerRegNo,o.buyerName,o.mobile,o.buyerIdNumber,o.goodsValue,o.acturalPaid,o.discount,o.freight,o.taxTotal,o.insuredFee,o.logisticsCode,o.addTime,o.createTime,o.remark,s.shopName,pc.platCateName,l.logisticsName,og.sku_id,og.sku_name,og.price,og.qty')->order('o.torderId desc')->select();
        if (!$list) errorMsg(400, '没有符合条件的订单！');

        //导入表格文件
        vendor("phpexcel.PHPExcel");
        //实例化表格
        $objPHPExcel = new \PHPExcel();
        //生成表头
        $objPHPExcel->getProperties()
            ->setCreator("jason")
            ->setLastModifiedBy("jason")
            ->setTitle("orders")
            ->setSubject("Office 2007 csv")
            ->setDescription("orders")
            ->setKeywords("orders")
            ->setCategory("orders");
        // 设置行高
        $objPHPExcel->setActiveSheetIndex(0)->getDefaultRowDimension()->setRowHeight(24);
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A1", "编号")
            ->setCellValue("B1", "所属平台")
            ->setCellValue("C1", "所属店铺")
            ->setCellValue("D1", "订单编号")
            ->setCellValue("E1", "商品总金额")
            ->setCellValue("F1", "优惠金额")
            ->setCellValue("G1", "保费")
            ->setCellValue("H1", "运杂费")
            ->setCellValue("I1", "税费")
            ->setCellValue("J1", "支付金额")
            ->setCellValue("K1", "购买人昵称")
            ->setCellValue("L1", "购买人姓名")
            ->setCellValue("M1", "购买人身份证")
            ->setCellValue("N1", "收件人姓名")
            ->setCellValue("O1", "收货人电话")
            ->setCellValue("P1", "收货地址")
            ->setCellValue("Q1", "快递公司")
            ->setCellValue("R1", "快递单号")
            ->setCellValue("S1", "商品编码")
            ->setCellValue("T1", "商品名称")
            ->setCellValue("U1", "商品数量")
            ->setCellValue("V1", "商品单价")
            ->setCellValue("W1", "下单时间")
            ->setCellValue("X1", "创建时间")
            ->setCellValue("Y1", "备注");
        foreach ($list as $key => $value) {
            $i = $key + 2;
            $j = $key + 1;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueExplicit("A" . $i, $j)
                ->setCellValueExplicit("B" . $i, $value['platCateName'])
                ->setCellValueExplicit("C" . $i, $value['shopName'])
                ->setCellValueExplicit("D" . $i, $value['order_id'])
                ->setCellValueExplicit("E" . $i, $value['goodsValue'])
                ->setCellValueExplicit("F" . $i, $value['discount'])
                ->setCellValueExplicit("G" . $i, $value['insuredFee'])
                ->setCellValueExplicit("H" . $i, $value['freight'])
                ->setCellValueExplicit("I" . $i, $value['insuredFee'])
                ->setCellValueExplicit("J" . $i, $value['taxTotal'])
                ->setCellValueExplicit("K" . $i, $value['acturalPaid'])
                ->setCellValueExplicit("L" . $i, $value['buyerRegNo'])
                ->setCellValueExplicit("M" . $i, $value['buyerName'])
                ->setCellValueExplicit("N" . $i, $value['buyerIdNumber'])
                ->setCellValueExplicit("O" . $i, $value['consignee'])
                ->setCellValueExplicit("P" . $i, $value['mobile'])
                ->setCellValueExplicit("Q" . $i, $value['address'])
                ->setCellValueExplicit("R" . $i, $value['logisticsName'])
                ->setCellValueExplicit("S" . $i, $value['logisticsCode'])
                ->setCellValueExplicit("T" . $i, $value['sku_id'])
                ->setCellValueExplicit("U" . $i, $value['sku_name'])
                ->setCellValueExplicit("V" . $i, $value['qty'])
                ->setCellValueExplicit("W" . $i, $value['addTime'])
                ->setCellValueExplicit("X" . $i, $value['createTime'])
                ->setCellValueExplicit("Y" . $i, $value['remark']);
        }
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('A')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('E')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('F')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('G')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('H')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('I')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('J')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('K')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('L')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('M')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('N')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('O')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('P')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('Q')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('R')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('S')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('T')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('U')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('V')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('W')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('X')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getColumnDimension('Y')->setAutoSize(true);
        $objPHPExcel->getActiveSheet(0)->getStyle('A1:Y1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet(0)->getStyle('A1:Y1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet(0)->getStyle('A1:Y1')->getFill()->getStartColor()->setARGB('FF00FF00');
        $objActSheet = $objPHPExcel->getActiveSheet(0);
        $objActSheet->setTitle("订单详情");
        $path = APP_PUBLIC . '/download/' . date('Ymd');
        if (!is_dir($path)) {
            mkdir($path, '0755');
        }
        $filename = date('YmdHis') . str_pad(rand(0, 999), 3, '0', STR_PAD_LEFT) . '.csv';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
        $objWriter->save($path . '/' . $filename);
        $path = request()->domain() . '/public/download/' . date('Ymd') . '/' . $filename;
        //$path='http://www.localhost.com/Public/'.$filename;
        errorMsg(200, 'success', $path);
    }