phpexcel 导出excel表格，csv文件，将xls，csv转数组，保存至服务器

php 7.0
composer require phpoffice/phpspreadsheet:1.8.2
可以直接使用mysql的select语句 直接导出文件

<?php

/**
 *  +----------------------------------------------------------------------
 *  | ThinkPHP [ WE CAN DO IT JUST THINK ]
 *  +----------------------------------------------------------------------
 *  | Copyright (c) 2020 ahai574 All rights reserved.
 *  +----------------------------------------------------------------------
 *  | Licensed ( ++++ahai574++++ )
 *  +----------------------------------------------------------------------
 *  | Author: 阿海 <764882431@qq.com>
 *  +----------------------------------------------------------------------
 *  处理导入excel 导入csv  导出xls xlsx csv
 */

namespace app\common\library;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

class PHPExcelLib
{
    /**
     * 文件保存名称 不用写后缀 ，默认会使用下载驱动作为后缀
     */
    private $fileName = 'excel';

    /**
     * 下载文件的驱动类
     *  如Xls,Xlsx
     */
    private $downloadClass = 'Xlsx';

    /**
     * 是下载还是保存至本地  默认是下载文件
     */
    private $isDownload = true;

    /**
     * 保存至服务器的路径
     */
    private $filePath = "";

    /**
     * 保存至服务器的路径+文件名称 -- 这个不需要设置 ---自动使用 $filePath+$fileName+时间
     */
    private $saveFilePath = "";

    /**
     * excel表的数据 --- 模拟数据格式 是mysql查询的二维数组
     * [
     *  ['id' => 1, 'name' => '阿海', 'age' => 'unknow', 'gender' => 'male'],
     *  ['id' => 2, 'name' => '小鬼', 'age' => '18', 'gender' => 'male']
     * ]
     */
    private $excelData = [];

    public function __construct($config = [])
    {
        isset($config['fileName']) && $this->fileName =  mb_convert_encoding($config['fileName'], 'UTF-8', 'UTF-8,GBK,GB2312,BIG5');
        isset($config['downloadClass']) && $this->downloadClass = $config['downloadClass'];
        isset($config['excelData']) && $this->excelData = $config['excelData'];
        isset($config['isDownload']) && $this->isDownload = (bool) $config['isDownload'];
        $this->filePath = isset($config['filePath']) ? $config['filePath'] : "runtime/uploads/files/" . date("Y-m-d");
        //文件名去除后缀
        if (strripos($this->fileName, ".") !== false) {
            $this->fileName = substr($this->fileName, 0, strripos($this->fileName, "."));
        }
        //如果是保存至本地 则设置保存的文件路径及名称，同样由于可能存在同名称 所以给加了一个随机数给这个文件名称,一般够用
        $this->saveFilePath = !($this->isDownload) ? $this->filePath . "/" . $this->fileName . "_" . time() . rand(0, 1000) . "." . strtolower($this->downloadClass) : '';
    }

    /**
     * 下载时的header头 
     */
    private function header()
    {
        if ($this->isDownload) {
            // Redirect output to a client’s web browser (Xlsx)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $this->fileName . "." . strtolower($this->downloadClass) . '"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
        } else {
            // 确保文件没有缓存,在ios上可能会出现问题
            header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
            header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
            header("Cache-Control: no-store, no-cache, must-revalidate");
            header("Cache-Control: post-check=0, pre-check=0", false);
            header("Pragma: no-cache");
        }
    }

    /**
     * 设置数据源，可以用于被下载的文件  -- 以mysql select() 的查询结果（二维数组，默认会把索引作为标题行）即可
     * @param Array $data
     */
    public function SetExcelData($data)
    {
        if (!is_array($data)) {
            throw new \Exception('数据格式必须是二维数组');
        }
        $this->excelData = $data;
        return $this;
    }

    /**
     * 下载文件支持： xls xlsx csv
     */
    public function createServer()
    {
        $this->header();
        //设置允许的请求时间
        @set_time_limit(5 * 60);
        $spreadsheet = new Spreadsheet();
        // Set document properties
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        $spreadsheet->setActiveSheetIndex(0);
        // Add some data
        foreach ($this->excelData as $key => $val) {
            $i = 0;
            //首次 第一行放数组的索引名称
            if ($key == 0) {
                foreach ($val as $k => $v) {

                    //总共可以有52列
                    if ($i <= 25) {
                        $spreadsheet->setActiveSheetIndex(0)->setCellValue(chr(65 + $i) . ($key + 1), $k);
                    } else if ($i > 25 && $i <= 51) {
                        $spreadsheet->setActiveSheetIndex(0)->setCellValue(chr(65) . chr(39 + $i) . ($key + 1), $k);
                    } else {
                        throw new \Exception("数据列比较长了，请在此处继续扩展");
                    }
                    $i++;
                }
            }
            $i = 0;
            //从excel第二行开始放数据
            foreach ($val as $k => $v) {

                //总共可以有52列
                if ($i <= 25) {
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue(chr(65 + $i) . ($key + 2), $v);
                } else if ($i > 25 && $i <= 51) {
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue(chr(65) . chr(39 + $i) . ($key + 2), $v);
                } else {
                    throw new \Exception("数据列比较长了，请在此处继续扩展");
                }
                $i++;
            }
        }
        $writer = IOFactory::createWriter($spreadsheet, ucfirst($this->downloadClass));
        if ($this->isDownload) {
            $writer->save('php://output');
            exit;
        } else {
            $writer->save($this->saveFilePath);
            //返回文件路径
            return $this->saveFilePath;
        }
    }

    /**
     * .xls文件 .csv文件 .tsv数据转数组
     * @param string $filePath  相对路径 如 runtime/example1.csv
     * @return Array
     */
    public function fileToArray($filePath)
    {
        if (!file_exists($filePath)) {
            throw new \Exception("文件不存在,请检查文件路径");
        }
        $spreadsheet = IOFactory::load($filePath);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        return $sheetData;
    }
}