fasadmin导出数（利用PhpSpreadsheet和自制模板的服务端导出到Excel方法）-肥猫博客

1.以fastadmin自带的test表为例；
2.所有操作都是针对test做了CURD后形成的文件view之test.html、控制器test.php、test.js文件修改。

具体操作如下：

一、建立模板目录

建立以下目录，并要保证有足够写入权限（用everyone能写入的权限）。

application\admin\controller\muban

将做好的Excel模板（后缀名要是.xls）放入到muban（模板）目录中。本例中随便做了一个。如下图所示（文件名test.xls）：

Excel模板.png

二、在控制器test.php文件顶端添加以下引用

use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

三、在控制器test.php中

添加下面的export方法

    public function export()
    {
        if ($this->request->isPost()) {
            set_time_limit(0);
            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $op = $this->request->post('op');
            $columns = $this->request->post('columns');

            //$excel = new PHPExcel();
            $spreadsheet = new Spreadsheet();

            $spreadsheet->getProperties()
                ->setCreator("FastAdmin")
                ->setLastModifiedBy("FastAdmin")
                ->setTitle("标题")
                ->setSubject("Subject");
            $spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
            $spreadsheet->getDefaultStyle()->getFont()->setSize(12);

            $worksheet = $spreadsheet->setActiveSheetIndex(0);
            $whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
            $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            
            $line = 1;

            //设置过滤方法
            $this->request->filter(['strip_tags']);

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($whereIds)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($whereIds)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);
            
            $first = array_keys($list[0]);
            foreach ($first as $index => $item) {
                $worksheet->setCellValueByColumnAndRow($index, 1, __($item));
            }
            
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(__DIR__ . '/muban/test.xls');  //读取模板
            $worksheet = $spreadsheet->getActiveSheet();     //指向激活的工作表
            $worksheet->setTitle('模板测试标题');

            for($i=0;$i<$total;++$i){ 
                //向模板表中写入数据
                $worksheet->setCellValue('A1', '模板测试内容');   //送入A1的内容
                $worksheet->getCell('B2')->setValue($result['rows'][$i]['week']);    //星期
                $worksheet->getCell('d2')->setValue($result['rows'][$i]['genderdata']);  //性别
                $worksheet->getCell('f2')->setValue($result['rows'][$i]['hobbydata']);  //爱好
                $worksheet->getCell('b3')->setValue($result['rows'][$i]['title']);  //标题
                $worksheet->getCell('b4')->setValue($result['rows'][$i]['content']);  //内容
                
                
                $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
                //下载文档
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'. date('Y-m-d') . $result['rows'][$i]['admin_id'] .'_test'.'.xlsx"');
                header('Cache-Control: max-age=0');
                $writer = new Xlsx($spreadsheet);
                $writer->save('php://output');
            }        

            return;
        }
    }

四、在view之test.html中添加“导出按钮”

注：该按钮暂时屏蔽了“导出本页X条”、“导出全页”两项功能，只保留了导出选中的一行（因为一个既成的模板中只存放一条列表数据）。

                       <a href="javascript:;" class="btn btn-success btn-export {:$auth->check('test/export')?'':'hide'}" title="{:__('Export')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('Export')}</a>

五、修改test.js

找到

            // 为表格绑定事件
            Table.api.bindevent(table);

复制以下代码放到该行上面：

            //*************************** 自定义export开始
            var submitForm = function (ids, layero) {
                var options = table.bootstrapTable('getOptions');
                console.log(options);
                var columns = [];
                $.each(options.columns[0], function (i, j) {
                    if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
                        columns.push(j.field);
                    }
                });
                var search = options.queryParams({});
                $("input[name=search]", layero).val(options.searchText);
                $("input[name=ids]", layero).val(ids);
                $("input[name=filter]", layero).val(search.filter);
                $("input[name=op]", layero).val(search.op);
                $("input[name=columns]", layero).val(columns.join(','));
                $("form", layero).submit();
            };
            $(document).on("click", ".btn-export", function () {
                var ids = Table.api.selectedids(table);
                var page = table.bootstrapTable('getData');
                var all = table.bootstrapTable('getOptions').totalRows;
                console.log(ids, page, all);
                Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl("test/export") + "' method='post' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='search'><input type='hidden' name='columns'></form>", {
                    title: '导出数据',
                    //btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
                    btn: ["选中项(" + ids.length + "条)"],
                    success: function (layero, index) {
                        $(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
                    }
                    , yes: function (index, layero) {
                        submitForm(ids.join(","), layero);
                        return false;
                    }
                    ,
                    btn2: function (index, layero) {
                        var ids = [];
                        $.each(page, function (i, j) {
                            ids.push(j.id);
                        });
                        submitForm(ids.join(","), layero);
                        return false;
                    }
                    ,
                    btn3: function (index, layero) {
                        submitForm("all", layero);
                        return false;
                    }
                })
            });
            //*************************** 自定义export结束
            
            // 为表格绑定事件
            Table.api.bindevent(table);