使用PHPExcel弄的一个导出类

namespace tool;

/**
 * 导出excel类
 * Class ExcelExport
 * @package tool
 */
class ExcelExport
{
    protected $objPHPExcel = null;
    //首行标题信息,例如：$title = ['ID', '名称', '价格', '数量'];
    protected $title = [];
    //数据列的键值,例如：$keysMap = ['id', 'name', 'price', 'number'];
    protected $keysMap = [];
    //总记录数
    protected $totalRows = 0;

    public function __construct($title = [], $keysMap = [])
    {
        include_once 'extend/phpexcel/PHPExcel.php';
        $this->title = $title;
        $this->keysMap = $keysMap;
        $this->objPHPExcel = new \PHPExcel();
    }

    /*
     * 获取对象
     */
    public function getPhpExcelObject() {
        return $this->objPHPExcel;
    }

    /*
     * 设置首行数据
     */
    public function setFirstLineData() {
        $objPhpExcel = $this->objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $this->objPHPExcel->getActiveSheet();
        for ($i = 0; $i < count($this->title); $i++) {
            $nowColumn = \PHPExcel_Cell::stringFromColumnIndex($i) . '1';
            //设置头信息水平垂直居中，加粗
            $objActSheet->getStyle($nowColumn)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objActSheet->getStyle($nowColumn)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objActSheet->getStyle($nowColumn)->getFont()->setBold(true);

            $objPhpExcel->setCellValue($nowColumn, $this->title[$i]);
        }
    }

    /**
     * 下载导出模板的规范，可设置下拉列表选项
     * Created by Ares
     * User: Ares
     * Date: 2020/6/30 9:54
     * @param array $data,如[['value' => 1, 'is_select' => false, 'options' => [], 'width' => 25],['value' => '', 'is_select' => true, 'options' => [10,20,30], 'width' => 15]]
     * 说明：
     * 1，$data是要导出模板的实例填充数据
     * 2,value为要填充的数据，当is_select为false时填充，为true时则表示此处是下拉列表，不需要填充数据
     * 3,options为下拉列表的数据
     * 4,设置单元格的列宽，如若缺省则使用默认值
     */
    public function setDownLoadTemplet($data = []) {
        if (!empty($data)) {
            //设置当前的sheet索引，用于后续的内容操作。
            //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0
            $objPhpExcel = $this->objPHPExcel->setActiveSheetIndex(0);
            $objActSheet = $this->objPHPExcel->getActiveSheet();

            $count = count($data) + 10; //找出总列数，后续下拉列表用到，放到（当前列数+10）列以后
            $i = 0;
            foreach ($data as $key => $item) {
                //当前列的字符串表示，如当前第一列，则是'A',第二列，则是'B'
                $nowColumn = \PHPExcel_Cell::stringFromColumnIndex($i);

                if (!$item['is_select']) {
                    //不是下拉列表
                    $objPhpExcel->setCellValue($nowColumn . 2, $item['value']);
                } else {
                    //是下拉列表
                    $strList = implode(',', $item['options']);
                    $strLength = strlen($strList);
                    if ($strLength >= 255) {
                        //下拉数据值长度超过255则分解到一列空闲的单元格中
                        //此处表示用于设置要存放下拉列表值的列
                        $count++;
                        $tmpColumn = \PHPExcel_Cell::stringFromColumnIndex($count);

                        $strArr = explode(',', $strList);
                        foreach ($strArr as $k => $d) {
                            $c = $tmpColumn . ($k + 1);
                            $objPhpExcel->setCellValue($c,$d);
                        }
                        $endCell = $c;
                        //隐藏填充数据的列
                        $objPhpExcel->getColumnDimension($tmpColumn)->setVisible(false);
                    }

                    //设置每一需要有下拉列表的单元格
                    $objValidation = $objPhpExcel->getCell($nowColumn . 2)->getDataValidation();
                    $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                        ->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        ->setAllowBlank(true)
                        ->setShowInputMessage(true)
                        ->setShowErrorMessage(true)
                        ->setShowDropDown(true)
                        ->setErrorTitle('输入的值有误')
                        ->setError('您输入的值不在下拉框列表内.')
                        ->setPromptTitle('下拉选择框')
                        ->setPrompt('');

                    if ($strLength < 255) {
                        $objValidation->setFormula1('"' . $strList . '"');
                    } else {
                        $objValidation->setFormula1($tmpColumn."1:".$endCell);
                    }

                }

                //设置下拉列表单元格的列宽
                if (isset($item['width']) && !empty($item['width'])) {
                    $objActSheet->getColumnDimension($nowColumn)->setWidth($item['width']);
                }

                $i++;
            }
        }

    }

    /*
     * 设置主体数据
     * $row表示当前行，一般第一行用于设置标题信息
     * $data = [
     *    ['id' => 1, 'name' => '商品1', 'price' => 12.00, 'number' => 10],
     *    ['id' => 2, 'name' => '商品1', 'price' => 13.00, 'number' => 12],
     * ];
     */
    public function setMainData($data, $nowRow = 2) {
        if (!empty($data)) {
            $objPhpExcel = $this->objPHPExcel->setActiveSheetIndex(0);
            $this->totalRows += count($data);
            foreach ($data as $key => $val) {
                $number = count($val);
                for ($i = 0; $i < $number; $i++) {
                    $objPhpExcel->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($i) . $nowRow, $val[$this->keysMap[$i]]);
                }
                $nowRow++;
            }
        }

    }

    /*
     * 获取总记录数
     */
    public function getTotalRows() {
        return $this->totalRows;
    }


    /*
     * 直接导出
     */
    public function export(\PHPExcel $objPHPExcel, $filename) {
        $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");

        //解决IE下载html
        $filename = $filename . '.xlsx';

        //解决IE下乱码问题
        $userBrowser = $_SERVER['HTTP_USER_AGENT'];
        if (preg_match('/MSIE/i', $userBrowser) || preg_match('/Trident\/7.0/', $userBrowser)) {
            $filename = urlencode($filename);
        }
        $filename = iconv('UTF-8', 'GBK//IGNORE', $filename);

        header('Pragma:public');
        header('Content-Type:application/x-msexecl;name="' . $filename . '"');
        header('Content-Disposition:inline;filename="' . $filename . '"');

        $PHPWriter->save("php://output");
        exit;
    }


    /*
     * 压缩导出
     */
    public function makeZip($fileNameArray = [], $title = '导出', $savePath) {
        //进行多个文件压缩
        $zip = new \ZipArchive();
        $filename = $savePath . $title . ".zip";
        $filename = iconv('utf-8','gbk//ignore',$filename);
        $zip->open($filename, \ZipArchive::CREATE);   //打开压缩包

        //向压缩包中添加文件
        foreach ($fileNameArray as $file) {
            $zip->addFile($file, basename($file));
        }

        $zip->close();  //关闭压缩包
        foreach ($fileNameArray as $file) {
            unlink($file); //删除临时文件
        }

        //输出压缩文件提供下载
        header("Cache-Control: max-age=0");
        header("Content-Description: File Transfer");
        header('Content-disposition: attachment; filename=' . $title . '.zip'); // 文件名
        header("Content-Type: application/zip"); // zip格式的
        header("Content-Transfer-Encoding: binary"); //
        header('Content-Length: '.filesize($filename)); //
        ob_clean();
        flush();
        readfile($filename);//输出文件;
        unlink($filename);
    }
}

后续如果增加了新功能的话再来修改！