fastadmin PhpSpreadsheet插件导出

最新推荐文章于 2024-07-04 17:36:36 发布

@M@

最新推荐文章于 2024-07-04 17:36:36 发布

阅读量775

点赞数 1

分类专栏： fastadmin 文章标签： PhpSpreadsheet FastAdmin Excel导出数据库查询样式设置

本文链接：https://blog.csdn.net/weixin_43383417/article/details/125934686

版权

fastadmin 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

fastadmin自带导出功能无法满足产品需求，故用PhpSpreadsheet重写了，废话不说直接贴代码：

public function billexport()
    {
        //当前是否为关联查询
        $this->relationSearch = false;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        $condition['shop_id'] = $this->user['shop_id'];
        $condition['status'] = array('in', '0,1');
        $list = $this->model
            ->where($condition)
            ->order('id', 'desc')
            ->field(['id', 'payment_days', 'createtime', 'stall_name', 'turnover', 'split_account_proportion', 'preassigned_account', 'channel_charge', 'service_charge'])
            ->select();
        $listArr = collection($list)->toArray();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $filename = '当前月账单导出.Xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        //设置表头
        $spreadsheet->getActiveSheet()->mergeCells('A1:P1');
        $sheet->setCellValue('A1', '导出时间：' . date("Y-m-d H:i:s", time()) . ',请在表格水费、电费、罚款、罚款原因、其他费用、其他费用备注下方填入数据，其他内容请勿更改，填写后确认无误再上传此文件。');
        //设置 红色文字
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setName('Arial')->setSize(13);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $sheet->setCellValue('A2', 'ID');
        $sheet->setCellValue('B2', '账期');
        $sheet->setCellValue('C2', '出账时间');
        $sheet->setCellValue('D2', '档口名称');
        $sheet->setCellValue('E2', '营业额');
        $sheet->setCellValue('F2', '分账比例');
        $sheet->setCellValue('G2', '预分账金额');
        $sheet->setCellValue('H2', '扣除通道费');
        $sheet->setCellValue('I2', '扣除分账手续费');
        $sheet->setCellValue('J2', '水费');
        $sheet->setCellValue('K2', '电费');
        $sheet->setCellValue('L2', '罚款');
        $sheet->setCellValue('M2', '罚款原因');
        $sheet->setCellValue('N2', '其他费用');
        $sheet->setCellValue('O2', '其他费用备注');
        $sheet->setCellValue('P2', '应结算金额');

        //改变此处设置的高度数值
        $sheet->getRowDimension('1')->setRowHeight(25);
        //改变此处设置的长度数值
        $sheet->getColumnDimension('A')->setWidth(15);
        $sheet->getColumnDimension('B')->setWidth(35);
        $sheet->getColumnDimension('C')->setWidth(30);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getColumnDimension('L')->setWidth(15);
        $sheet->getColumnDimension('M')->setWidth(15);
        $sheet->getColumnDimension('N')->setWidth(15);
        $sheet->getColumnDimension('O')->setWidth(15);
        $sheet->getColumnDimension('P')->setWidth(15);
        //输出表格
        $str = '无账单信息';
        foreach ($listArr as $key => $row) {

            $count = count($listArr);
            $i = $key + 3; //表格是从2开始的
            if ($count >= 1) {
                //查询 水费 电费  罚款  其他费用
                $shuifei = Db::table("ims_j_hmoney_saas_stall_additional_charge")->where(['stall_bill_id' => $row['id'], 'type' => 1])->find();
                if (!$shuifei) {
                    $row['shuifei'] = 0;
                } else {
                    $row['shuifei'] = $shuifei['value'];
                }
                $dianfei = Db::table("ims_j_hmoney_saas_stall_additional_charge")->where(['stall_bill_id' => $row['id'], 'type' => 2])->find();
                if (!$dianfei) {
                    $row['dianfei'] = 0;
                } else {
                    $row['dianfei'] = $dianfei['value'];
                }
                $fakuan = Db::table("ims_j_hmoney_saas_stall_additional_charge")->where(['stall_bill_id' => $row['id'], 'type' => 3])->find();
                if (!$fakuan) {
                    $row['fakuan'] = 0;
                    $row['fakuan_remark'] = 0;
                } else {
                    $row['fakuan'] = $fakuan['value'];
                    $row['fakuan_remark'] = $fakuan['remark'];
                }
                $qita = Db::table("ims_j_hmoney_saas_stall_additional_charge")->where(['stall_bill_id' => $row['id'], 'type' => 4])->find();
                if (!$qita) {
                    $row['qita'] = 0;
                    $row['qita_remark'] = "";
                } else {
                    $row['qita'] = $qita['value'];
                    $row['qita_remark'] = $qita['remark'];
                }

                $sheet->setCellValue('A' . $i, $row['id']);
                $sheet->setCellValue('B' . $i, $row['payment_days']);
                $sheet->setCellValue('C' . $i, date("Y-m-d H:i:s", $row['createtime']));
                $sheet->setCellValue('D' . $i, $row['stall_name']);
                $sheet->setCellValue('E' . $i, $row['turnover']);
                $sheet->setCellValue('F' . $i, $row['split_account_proportion']);
                $sheet->setCellValue('G' . $i, $row['preassigned_account']);
                $sheet->setCellValue('H' . $i, $row['channel_charge']);
                $sheet->setCellValue('I' . $i, $row['service_charge']);
                $sheet->setCellValue('J' . $i, $row['shuifei']);
                $sheet->setCellValue('K' . $i, $row['dianfei']);
                $sheet->setCellValue('L' . $i, $row['fakuan']);
                $sheet->setCellValue('M' . $i, $row['fakuan_remark']);
                $sheet->setCellValue('N' . $i, $row['qita']);
                $sheet->setCellValue('O' . $i, $row['qita_remark']);
                $sheet->setCellValue('P' . $i, $row['qita_remark']);
                $sheet->setCellValue('P' . $i, "=G" . $i . "-J" . $i . "-K" . $i . "-L" . $i . "-N" . $i);
            } else {
                $sheet->setCellValue('A' . $i, $str);
                $sheet->setCellValue('B' . $i, $str);
                $sheet->setCellValue('C' . $i, $str);
                $sheet->setCellValue('D' . $i, $str);
                $sheet->setCellValue('E' . $i, $str);
                $sheet->setCellValue('F' . $i, $str);
                $sheet->setCellValue('G' . $i, $str);
                $sheet->setCellValue('H' . $i, $str);
                $sheet->setCellValue('I' . $i, $str);
                $sheet->setCellValue('J' . $i, $str);
                $sheet->setCellValue('K' . $i, $str);
                $sheet->setCellValue('L' . $i, $str);
                $sheet->setCellValue('M' . $i, $str);
                $sheet->setCellValue('N' . $i, $str);
                $sheet->setCellValue('O' . $i, $str);
                $sheet->setCellValue('P' . $i, $str);
            }
        }
        $writer->save('php://output');