Exporter.php

<?php
/**
 * Created by PhpStorm.
 * User: 梁俊杰
 * Date: 2020/5/12
 * Time: 11:37 AM
 * Description:
 */

namespace Meibuyu\Micro\Tools;

use Hyperf\Contract\ConfigInterface;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class Exporter
{

    /**
     * 以字节流的形式下载
     */
    CONST DOWNLOAD_TYPE_STREAM = 1;

    /**
     * 保存文件，并返回文件的路径
     */
    CONST DOWNLOAD_TYPE_RETURN_FILE_PATH = 2;

    /**
     * 保存文件，直接输出下载
     */
    CONST DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD = 3;

    /**
     * 保存文件，返回文件绝对路径
     */
    CONST DOWNLOAD_TYPE_RETURN_ABSOLUTE_PATH = 4;

    CONST EXPORTER_TYPE_CSV = 1;
    CONST EXPORTER_TYPE_XLS = 2;
    CONST EXPORTER_TYPE_XLSX = 3;
    /**
     * @var ConfigInterface
     */
    protected $config;
    private $rootPath;
    private $beginRowIndex = 1;
    private $fileType = 'Xlsx';
    private $isFromTemplate = false;
    private $sheet = null;
    private $reader = null;
    private $sheetIndex = 0;
    private $name = "";
    private $beginColumnChar = "A";

    /**
     * Exporter constructor.
     * @param int $export_type 类型
     * @param string $tempFilePath 模板文件地址
     * @param string $name 名称
     * @param int $sheetIndex
     * @throws PhpSpreadsheetException
     * @throws \Exception
     */
    public function __construct(int $export_type, $tempFilePath = "", $name = "export_data", $sheetIndex = 0)
    {
        $this->config = container(ConfigInterface::class);
        $this->rootPath = $this->config->get('server.settings.document_root', BASE_PATH . '/public');
        if ($tempFilePath) {
            $tempFilePath = $this->rootPath . "/" . $tempFilePath;
            if (file_exists($tempFilePath)) {
                $this->fileType = ucfirst(strtolower(pathinfo($tempFilePath, PATHINFO_EXTENSION)));
                $reader = IOFactory::createReader($this->fileType)->load($tempFilePath);
                $this->isFromTemplate = true;
            } else {
                throw new \Exception("模板文件不存在");
            }
        } else {
            switch ($export_type) {
                case self::EXPORTER_TYPE_CSV:
                    $this->fileType = "Csv";
                    break;
                case self::EXPORTER_TYPE_XLS:
                    $this->fileType = "Xls";
                    break;
                case self::EXPORTER_TYPE_XLSX:
                    $this->fileType = "Xlsx";
                    break;
                default:
                    throw new \Exception("类型不支持。");
                    break;
            }
            $reader = new Spreadsheet();
        }
        $this->name = $name;
        $this->sheet = $reader->getSheet($sheetIndex);
        $this->reader = $reader;
        $this->sheetIndex = $sheetIndex;
    }

    /**
     * 设置非保护区间，调用此方法全表将自动保护
     * @param mixed $unprotectRange 可传数组,字符串或者多个参数形式('A1:B1', 'C1:D1')
     * 例如"A1:B1";从A列B列第一行到数据结束行不需要保护
     * @return $this
     */
    public function setUnprotectRange($unprotectRange = null)
    {
        $this->sheet->getProtection()->setSheet(true);
        $this->sheet->getProtection()->setPassword('micro-tttt');
        if ($unprotectRange) {
            $unprotectRange = is_array($unprotectRange) ? $unprotectRange : func_get_args();
            foreach ($unprotectRange as $range) {
                $this->sheet->getStyle($range)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
            }
        }
        return $this;
    }

    /**
     * 往Excel里面新增一张sheet表
     * @param string $name sheet的名字
     * @param bool $activeSheet 是否激货并切换到当前sheet 默认否
     * @return $this
     * @throws PhpSpreadsheetException
     */
    public function addSheet($name, $activeSheet = false)
    {
        $workSheet = new Worksheet(null, $name);
        $this->reader->addSheet($workSheet);
        if ($activeSheet) {
            $this->setSheetByName($name);
        }
        return $this;
    }

    /**
     * 根据名字sheet表名激化切换到该表
     * @param $name
     * @return $this
     * @throws PhpSpreadsheetException
     */
    public function setSheetByName($name)
    {
        $this->sheet = $this->reader->setActiveSheetIndexByName($name);
        $this->sheetIndex = $this->reader->getActiveSheetIndex();
        $this->resetRowIndex();
        return $this;
    }

    /**
     * 重置排序
     */
    private function resetRowIndex()
    {
        $this->beginRowIndex = 1;
        return $this;
    }

    /**
     * 获取当前数据填充到哪一行了 或者最高行
     * @return int
     */
    public function getCurrentRowIndex()
    {
        return $this->sheet->getHighestRow();
    }

    /**
     * 设置从哪一列开始填充数据
     * @param string $char 默认 A列
     * @return Exporter
     */
    public function setBeginColumnChar($char = "A")
    {
        $this->beginColumnChar = $char;
        return $this;
    }

    /**
     * 设置从哪一行开始填充数据
     * @param int $index 默认第一行
     * @return Exporter
     */
    public function setBeginRowIndex($index = 1)
    {
        $this->beginRowIndex = $index;
        return $this;
    }

    /**
     * 根据sheet索引切换到对应sheet
     * @param int $sheetIndex
     * @return Exporter
     * @throws PhpSpreadsheetException
     */
    public function setSheetByIndex(int $sheetIndex)
    {
        $this->sheet = $this->reader->setActiveSheetIndex($sheetIndex);
        $this->sheetIndex = $sheetIndex;
        $this->resetRowIndex();
        return $this;
    }

    /**
     * 往表格里填充数据
     * @param array $data 支持一维数组和二位数组，数据顺序与表头顺序相同
     * @param array $keys 如果$data是原生数组支持直接根据$keys
     * 提取数据，可多层提取例如 ['product_name','color.name','creator.user_status.name']
     * 支持默认值，为空时显示默认值 例如['product_name|默认值','color.name|无颜色']
     * @return Exporter
     * @throws PhpSpreadsheetException
     */
    public function append(array $data, $keys = [])
    {
        // 一维数组转二维
        foreach ($data as $v) {
            if (!is_array($v)) {
                $data = [$data];
            }
            break;
        }

        if (count($keys)) {
            foreach ($data as $k => $v) {
                $data[$k] = $this->getKeyValue($v, $keys);
            }
        }

        $this->sheet->fromArray($data, null, $this->beginColumnChar . $this->beginRowIndex);
        //美化样式
        $this->applyStyle($this->beginColumnChar . $this->beginRowIndex . ":" . $this->sheet->getHighestColumn() . ($this->beginRowIndex + count($data) - 1));
        $this->beginRowIndex += count($data);
        return $this;
    }

    /**
     * 根据key提取数据
     * @param array $data
     * @param array $keys
     * @return array
     */
    private function getKeyValue(array $data, array $keys)
    {
        $result = [];
        foreach ($keys as $k => $v) {
            if (strpos($v, ".") === false) {
                if (isset($data[$v])) {
                    $result[] = $data[$v];
                } else {
                    $result[] = '';
                }
            } else {
                $separate = explode("|", $v);
                $nullValue = isset($separate[1]) ? $separate[1] : "";
                $list = explode(".", $separate[0]);
                $t = count($list) - 1;
                $b = $data;
                foreach ($list as $lk => $lv) {
                    if (isset($b[$lv]) && $b[$lv]) {
                        $b = $b[$lv];
                    } else {
                        $b = "";
                    }
                    //提取到最后一个
                    if ($t === $lk) {
                        $result[] = (($b === "") ? $nullValue : $b);
                    }
                }
            }
        }
        return $result;
    }

    /**
     * 给表格加样式
     * @param $pRange
     */
    public function applyStyle($pRange)
    {
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'quotePrefix' => true,
        ];

        $this->sheet->getStyle($pRange)->applyFromArray($styleArray);
    }

    /**
     * 设置列宽
     * @param int $width 不传为自动宽度
     */
    public function setColumnWidth(int $width = null)
    {
        $end = $this->sheet->getHighestColumn();
        $end++;
        for ($i = 'A'; $i !== $end; $i++) {
            if ($width) {
                $this->sheet->getColumnDimension($i)->setWidth($width);
            } else {
                $this->sheet->getColumnDimension($i)->setAutoSize(true);
            }
        }
    }

    /**
     * 下载导出的文件
     * @param int $downloadType 下载形式 支持 Exporter::DOWNLOAD_TYPE_STREAM Exporter::DOWNLOAD_TYPE_RETURN_FILE_PATH Exporter::DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD
     * @param string $filename 下载的文件名
     * @return mixed | void
     * @throws \Exception
     */
    public function download(int $downloadType, $filename = "")
    {
        set_time_limit(0);
        if (!$filename) {
            $filename = $this->name ? $this->name : rand(1, 9999999) . time() . rand(1, 9999999);
        }
        $this->reader->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($this->reader, $this->fileType);
        switch ($downloadType) {
            case self::DOWNLOAD_TYPE_STREAM:
            case self::DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD:
                $filename .= "." . strtolower($this->fileType);
                if ($this->fileType == 'Xlsx') {
                    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                } elseif ($this->fileType == 'Xls') {
                    header('Content-Type: application/vnd.ms-excel');
                } else {
                    header("Content-type:text/csv;");
                }
                header("Content-Disposition: attachment;filename="
                    . $filename);
                header('Cache-Control: max-age=0');
                ob_clean();

                if ($downloadType == self::DOWNLOAD_TYPE_STREAM) {
                    $objWriter->save('php://output');
                } else {
                    $f = $this->rootPath . "/export";
                    if (!file_exists($f)) {
                        mkdir($f, 0777, true);
                    }
                    $f .= "/" . $filename;
                    $objWriter->save($f);
                    header('Content-length:' . filesize($f));
                    $fp = fopen($f, 'r');
                    fseek($fp, 0);
                    ob_start();
                    while (!feof($fp) && connection_status() == 0) {
                        $chunk_size = 1024 * 1024 * 2; // 2MB
                        echo fread($fp, $chunk_size);
                        ob_flush();
                        flush();
                    }
                    ob_end_clean();
                    fclose($fp);
                    @unlink($f);
                }
                break;
            case self::DOWNLOAD_TYPE_RETURN_FILE_PATH:
                $filePath = $this->rootPath . "/export/";
                !is_dir($filePath) && mkdir($filePath, 0777, true);
                $fileReadPath = $filePath . $filename . '-' . date('YmdHis') . "." . strtolower($this->fileType);
                $objWriter->save($fileReadPath);
                return $this->config->get('app_domain') . str_replace($this->rootPath, '', $fileReadPath);
                break;
            case self::DOWNLOAD_TYPE_RETURN_ABSOLUTE_PATH:
                $filePath = $this->rootPath . "/export/";
                !is_dir($filePath) && mkdir($filePath, 0777, true);
                $date = date('YmdHis');
                $fileReadPath = $filePath . $filename . '-' . $date . "." . strtolower($this->fileType);
                $objWriter->save($fileReadPath);
                return config('server.settings.document_root') . "/export/" . $filename . '-' .$date . "." .
                    strtolower($this->fileType);
                break;
            default:
                throw new \Exception('不支持此种下载类型');
                break;
        }
    }

    /**
     * 返回当前激化的sheet表
     * @return null|Worksheet
     */
    public function getSheet()
    {
        return $this->sheet;
    }

    /**
     * 返回原生的PhpOffice Reader 手动处理数据
     * @return null|Spreadsheet
     */
    public function getReader()
    {
        return $this->reader;
    }

}