TP5使用phpoffice/phpexcel包操作excel（导出）

深藏阁楼爱情的钟 2021-09-28 14:44 354阅读 0赞

# [安装composer（window版本）][composer_window] #

# [安装composer（MAC版本）][composer_MAC] #

# [安装composer（Linux版本）][composer_Linux] #

[在PhpStorm配置][PhpStorm]

## 导出excel ##

1、使用composer安装phpoffice/phpexcel包

## [phpexcel资源][phpexcel] ##

下载好之后，解压，再\\php-excel\\vendor\\phpoffice目录下，不建议使用，因为这是通过composer下载的 ，对其他（比如：composer.json等，安装包也有这个，一套的）的文件有所关联

composer require phpoffice/phpexcel

2、公共代码（我放到API位置了，各自根据自己代码结构编写）

<?php
    /**
     * 操作excel
     * 导入、导出
     * Created by PhpStorm.
     * Date: 2019/5/13
     * Time: 16:12
     */
    namespace app\api\controller;
    
    use think\Controller;
    use think\Request;
    
    class Excel extends Controller
    {
        /**
         * 导出
         * @param string $fileName
         * @param array $headArr
         * @param array $data
         * @throws \PHPExcel_Exception
         * @throws \PHPExcel_Reader_Exception
         * @throws \PHPExcel_Writer_Exception
         */
        function excelExport($fileName = '', $headArr = [], $data = []) {
    
            $fileName .= "-" . date("YmdHi", Request::instance()->time()) . ".xls";
    
            $objPHPExcel = new \PHPExcel();
    
            $objPHPExcel->getProperties();
    
            $key = ord("A"); // 设置表头
    
            foreach ($headArr as $v) {
    
                $colum = chr($key);
    
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
    
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
    
                $key += 1;
    
            }
    
            $column = 2;
    
            $objActSheet = $objPHPExcel->getActiveSheet();
    
            foreach ($data as $key => $rows) { // 行写入
    
                $span = ord("A");
    
                foreach ($rows as $keyName => $value) { // 列写入
    
                    $objActSheet->setCellValue(chr($span) . $column, $value);
    
                    $span++;
    
                }
    
                $column++;
    
            }
    
            $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
    
            $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
    
            header('Content-Type: application/vnd.ms-excel');
    
            header("Content-Disposition: attachment;filename=$fileName");
    
            header('Cache-Control: max-age=0');
    
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    
            $objWriter->save('php://output'); // 文件通过浏览器下载
    
            exit();
    
        }
    }

3、控制层，处理数据调用

<?php
    /**
     * 会员设置控制器
     * Created by PhpStorm.
     * Date: 2019/4/25
     * Time: 9:45
     */
    
    namespace app\admin\controller;
    
    use app\api\controller\Excel;
    use app\common\controller\Manage;
    use think\Request;
    
    class MemberShip extends Manage
    {
    
        private $memberShipM;
    
        /**
         * 预加载model
         * Category constructor.
         * @param Request|null $request
         */
        public function __construct(Request $request = null)
        {
            parent::__construct($request);
            $this->memberShipM = new \app\common\model\MemberShip();
        }
    
     
    
        /**
         * 导出会员列表
         */
        public function exportMember()
        {
            $excel = new Excel();
            $name='会员';
            $header=['序号','会员名称','价格','购买人数'];
            $newdata = [];
            $data=$this->memberShipM->getList();
            foreach ($data as $key=>$value){
                $newdata[$key]['id'] = $value['id'];
                $newdata[$key]['name'] = $value['name'];
                $newdata[$key]['price'] = $value['price'];
                $newdata[$key]['buy_num'] = $value['buy_num'];
            }
    
            $excel->excelExport($name,$header,$newdata);
        }
    
    }

5、导出成功

[composer_window]: https://blog.csdn.net/weixin_40354683/article/details/87193701
[composer_MAC]: https://blog.csdn.net/weixin_40354683/article/details/87457152
[composer_Linux]: https://blog.csdn.net/weixin_40354683/article/details/87196248
[PhpStorm]: https://blog.csdn.net/weixin_40354683/article/details/90177684
[phpexcel]: https://download.csdn.net/download/weixin_40354683/11176156