TP5使用phpoffice/phpexcel包操作excel(导出)

来源:csdn博客 分类: 文章浏览史 发布时间:2020-11-27 13:59:36 最后更新:2020-11-27 浏览:134
转载声明:
本文为摘录自“csdn博客”,版权归原作者所有。
温馨提示:
为了更好的体验,请点击原文链接进行浏览
摘录时间:
2020-11-27 13:59:36

安装composer(window版本)

安装composer(MAC版本)

安装composer(Linux版本)

在PhpStorm配置

导出excel

1、使用composer安装phpoffice/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、导出成功

 

php技术微信