Exporter.php 13.3 KB
Newer Older
梁俊杰's avatar
梁俊杰 committed
1 2 3 4 5 6 7 8 9 10 11
<?php
/**
 * Created by PhpStorm.
 * User: 梁俊杰
 * Date: 2020/5/12
 * Time: 11:37 AM
 * Description:
 */

namespace Meibuyu\Micro\Tools;

王源's avatar
王源 committed
12
use Hyperf\Contract\ConfigInterface;
王源's avatar
王源 committed
13
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
梁俊杰's avatar
梁俊杰 committed
14
use PhpOffice\PhpSpreadsheet\IOFactory;
王源's avatar
王源 committed
15
use PhpOffice\PhpSpreadsheet\Spreadsheet;
梁俊杰's avatar
梁俊杰 committed
16 17 18 19 20 21 22
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class Exporter
{
王源's avatar
王源 committed
23

梁俊杰's avatar
梁俊杰 committed
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
    /**
     * 以字节流的形式下载
     */
    CONST DOWNLOAD_TYPE_STREAM = 1;

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

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

39 40 41 42 43
    /**
     * 保存文件,返回文件绝对路径
     */
    CONST DOWNLOAD_TYPE_RETURN_ABSOLUTE_PATH = 4;

梁俊杰's avatar
梁俊杰 committed
44 45 46
    CONST EXPORTER_TYPE_CSV = 1;
    CONST EXPORTER_TYPE_XLS = 2;
    CONST EXPORTER_TYPE_XLSX = 3;
47 48 49 50
    /**
     * @var ConfigInterface
     */
    protected $config;
王源's avatar
王源 committed
51
    private $rootPath;
zhaopeng343's avatar
zhaopeng343 committed
52
    private $beginRowIndex = 1;
梁俊杰's avatar
梁俊杰 committed
53 54 55 56 57 58 59 60 61 62
    private $fileType = 'Xlsx';
    private $isFromTemplate = false;
    private $sheet = null;
    private $reader = null;
    private $sheetIndex = 0;
    private $name = "";
    private $beginColumnChar = "A";

    /**
     * Exporter constructor.
王源's avatar
王源 committed
63 64 65 66
     * @param int $export_type 类型
     * @param string $tempFilePath 模板文件地址
     * @param string $name 名称
     * @param int $sheetIndex
王源's avatar
王源 committed
67
     * @throws PhpSpreadsheetException
王源's avatar
王源 committed
68
     * @throws \Exception
梁俊杰's avatar
梁俊杰 committed
69
     */
王源's avatar
王源 committed
70
    public function __construct(int $export_type, $tempFilePath = "", $name = "export_data", $sheetIndex = 0)
梁俊杰's avatar
梁俊杰 committed
71
    {
王源's avatar
王源 committed
72
        $this->config = container(ConfigInterface::class);
73
        $this->rootPath = $this->config->get('server.settings.document_root', BASE_PATH . '/public');
王源's avatar
王源 committed
74
        if ($tempFilePath) {
75
            $tempFilePath = $this->rootPath . "/" . $tempFilePath;
王源's avatar
王源 committed
76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
            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();
梁俊杰's avatar
梁俊杰 committed
99 100
        }
        $this->name = $name;
王源's avatar
王源 committed
101
        $this->sheet = $reader->getSheet($sheetIndex);
梁俊杰's avatar
梁俊杰 committed
102 103 104 105 106 107
        $this->reader = $reader;
        $this->sheetIndex = $sheetIndex;
    }

    /**
     * 设置非保护区间,调用此方法全表将自动保护
108 109
     * @param mixed $unprotectRange 可传数组,字符串或者多个参数形式('A1:B1', 'C1:D1')
     * 例如"A1:B1";从A列B列第一行到数据结束行不需要保护
王源's avatar
王源 committed
110
     * @return $this
梁俊杰's avatar
梁俊杰 committed
111
     */
112
    public function setUnprotectRange($unprotectRange = null)
梁俊杰's avatar
梁俊杰 committed
113 114
    {
        $this->sheet->getProtection()->setSheet(true);
115
        $this->sheet->getProtection()->setPassword('micro-tttt');
梁俊杰's avatar
梁俊杰 committed
116
        if ($unprotectRange) {
117 118 119 120
            $unprotectRange = is_array($unprotectRange) ? $unprotectRange : func_get_args();
            foreach ($unprotectRange as $range) {
                $this->sheet->getStyle($range)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
            }
梁俊杰's avatar
梁俊杰 committed
121
        }
梁俊杰's avatar
梁俊杰 committed
122
        return $this;
梁俊杰's avatar
梁俊杰 committed
123 124 125 126 127 128
    }

    /**
     * 往Excel里面新增一张sheet表
     * @param string $name sheet的名字
     * @param bool $activeSheet 是否激货并切换到当前sheet 默认否
王源's avatar
王源 committed
129 130
     * @return $this
     * @throws PhpSpreadsheetException
梁俊杰's avatar
梁俊杰 committed
131 132 133 134 135 136 137 138
     */
    public function addSheet($name, $activeSheet = false)
    {
        $workSheet = new Worksheet(null, $name);
        $this->reader->addSheet($workSheet);
        if ($activeSheet) {
            $this->setSheetByName($name);
        }
梁俊杰's avatar
梁俊杰 committed
139
        return $this;
梁俊杰's avatar
梁俊杰 committed
140 141
    }

王源's avatar
王源 committed
142 143
    /**
     * 根据名字sheet表名激化切换到该表
梁俊杰's avatar
梁俊杰 committed
144
     * @param $name
王源's avatar
王源 committed
145 146
     * @return $this
     * @throws PhpSpreadsheetException
梁俊杰's avatar
梁俊杰 committed
147 148 149 150 151 152
     */
    public function setSheetByName($name)
    {
        $this->sheet = $this->reader->setActiveSheetIndexByName($name);
        $this->sheetIndex = $this->reader->getActiveSheetIndex();
        $this->resetRowIndex();
梁俊杰's avatar
梁俊杰 committed
153
        return $this;
梁俊杰's avatar
梁俊杰 committed
154 155 156 157 158 159 160 161
    }

    /**
     * 重置排序
     */
    private function resetRowIndex()
    {
        $this->beginRowIndex = 1;
梁俊杰's avatar
梁俊杰 committed
162
        return $this;
梁俊杰's avatar
梁俊杰 committed
163 164 165 166 167 168 169 170 171 172 173
    }

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

王源's avatar
王源 committed
174 175
    /**
     * 设置从哪一列开始填充数据
梁俊杰's avatar
梁俊杰 committed
176
     * @param string $char 默认 A列
王源's avatar
王源 committed
177
     * @return Exporter
梁俊杰's avatar
梁俊杰 committed
178 179 180 181
     */
    public function setBeginColumnChar($char = "A")
    {
        $this->beginColumnChar = $char;
梁俊杰's avatar
梁俊杰 committed
182
        return $this;
梁俊杰's avatar
梁俊杰 committed
183 184
    }

王源's avatar
王源 committed
185 186
    /**
     * 设置从哪一行开始填充数据
梁俊杰's avatar
梁俊杰 committed
187
     * @param int $index 默认第一行
王源's avatar
王源 committed
188
     * @return Exporter
梁俊杰's avatar
梁俊杰 committed
189 190 191 192
     */
    public function setBeginRowIndex($index = 1)
    {
        $this->beginRowIndex = $index;
梁俊杰's avatar
梁俊杰 committed
193
        return $this;
梁俊杰's avatar
梁俊杰 committed
194 195 196
    }

    /**
王源's avatar
王源 committed
197
     * 根据sheet索引切换到对应sheet
梁俊杰's avatar
梁俊杰 committed
198
     * @param int $sheetIndex
王源's avatar
王源 committed
199 200
     * @return Exporter
     * @throws PhpSpreadsheetException
梁俊杰's avatar
梁俊杰 committed
201 202 203 204 205 206
     */
    public function setSheetByIndex(int $sheetIndex)
    {
        $this->sheet = $this->reader->setActiveSheetIndex($sheetIndex);
        $this->sheetIndex = $sheetIndex;
        $this->resetRowIndex();
梁俊杰's avatar
梁俊杰 committed
207
        return $this;
梁俊杰's avatar
梁俊杰 committed
208 209 210 211 212 213 214 215
    }

    /**
     * 往表格里填充数据
     * @param array $data 支持一维数组和二位数组,数据顺序与表头顺序相同
     * @param array $keys 如果$data是原生数组支持直接根据$keys
     * 提取数据,可多层提取例如 ['product_name','color.name','creator.user_status.name']
     * 支持默认值,为空时显示默认值 例如['product_name|默认值','color.name|无颜色']
王源's avatar
王源 committed
216 217
     * @return Exporter
     * @throws PhpSpreadsheetException
梁俊杰's avatar
梁俊杰 committed
218
     */
219
    public function append(array $data, $keys = [], $noStyle = false)
梁俊杰's avatar
梁俊杰 committed
220
    {
王源's avatar
王源 committed
221
        // 一维数组转二维
222 223 224 225 226
        foreach ($data as $v) {
            if (!is_array($v)) {
                $data = [$data];
            }
            break;
梁俊杰's avatar
梁俊杰 committed
227
        }
王源's avatar
王源 committed
228 229

        if (count($keys)) {
梁俊杰's avatar
梁俊杰 committed
230 231 232 233 234 235
            foreach ($data as $k => $v) {
                $data[$k] = $this->getKeyValue($v, $keys);
            }
        }

        $this->sheet->fromArray($data, null, $this->beginColumnChar . $this->beginRowIndex);
236 237 238 239
        if (!$noStyle) {
            // 美化样式
            $this->applyStyle($this->beginColumnChar . $this->beginRowIndex . ":" . $this->sheet->getHighestColumn() . ($this->beginRowIndex + count($data) - 1));
        }
梁俊杰's avatar
梁俊杰 committed
240
        $this->beginRowIndex += count($data);
梁俊杰's avatar
梁俊杰 committed
241
        return $this;
梁俊杰's avatar
梁俊杰 committed
242 243
    }

王源's avatar
王源 committed
244 245
    /**
     * 根据key提取数据
梁俊杰's avatar
梁俊杰 committed
246 247 248 249 250 251 252 253 254 255 256 257
     * @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 {
王源's avatar
王源 committed
258
                    $result[] = '';
梁俊杰's avatar
梁俊杰 committed
259 260 261 262 263 264 265 266
                }
            } 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) {
王源's avatar
王源 committed
267
                    if (isset($b[$lv]) && $b[$lv]) {
梁俊杰's avatar
梁俊杰 committed
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285
                        $b = $b[$lv];
                    } else {
                        $b = "";
                    }
                    //提取到最后一个
                    if ($t === $lk) {
                        $result[] = (($b === "") ? $nullValue : $b);
                    }
                }
            }
        }
        return $result;
    }

    /**
     * 给表格加样式
     * @param $pRange
     */
王源's avatar
王源 committed
286
    public function applyStyle($pRange)
梁俊杰's avatar
梁俊杰 committed
287 288 289 290 291
    {
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
王源's avatar
王源 committed
292
                    'color' => ['argb' => '000000'],
梁俊杰's avatar
梁俊杰 committed
293 294 295 296 297 298 299 300 301 302 303 304 305
                ],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'quotePrefix' => true,
        ];

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

王源's avatar
王源 committed
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322
    /**
     * 设置列宽
     * @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);
            }
        }
    }

梁俊杰's avatar
梁俊杰 committed
323
    /**
王源's avatar
王源 committed
324
     * 下载导出的文件
梁俊杰's avatar
梁俊杰 committed
325 326
     * @param int $downloadType 下载形式 支持 Exporter::DOWNLOAD_TYPE_STREAM Exporter::DOWNLOAD_TYPE_RETURN_FILE_PATH Exporter::DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD
     * @param string $filename 下载的文件名
王源's avatar
王源 committed
327 328
     * @return mixed | void
     * @throws \Exception
梁俊杰's avatar
梁俊杰 committed
329 330 331 332 333 334 335
     */
    public function download(int $downloadType, $filename = "")
    {
        set_time_limit(0);
        if (!$filename) {
            $filename = $this->name ? $this->name : rand(1, 9999999) . time() . rand(1, 9999999);
        }
王源's avatar
王源 committed
336
        $this->reader->setActiveSheetIndex(0);
梁俊杰's avatar
梁俊杰 committed
337 338 339 340
        $objWriter = IOFactory::createWriter($this->reader, $this->fileType);
        switch ($downloadType) {
            case self::DOWNLOAD_TYPE_STREAM:
            case self::DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD:
341
                $filename .= "." . strtolower($this->fileType);
梁俊杰's avatar
梁俊杰 committed
342 343 344 345 346 347 348 349 350 351 352 353 354 355 356
                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 {
王源's avatar
王源 committed
357
                    $f = $this->rootPath . "/export";
梁俊杰's avatar
梁俊杰 committed
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378
                    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:
王源's avatar
王源 committed
379
                $filePath = $this->rootPath . "/export/";
fuyunnan's avatar
fuyunnan committed
380
                !is_dir($filePath) && mkdir($filePath, 0777, true);
381 382 383
                $fileReadPath = $filePath . $filename . '-' . date('YmdHis') . "." . strtolower($this->fileType);
                $objWriter->save($fileReadPath);
                return $this->config->get('app_domain') . str_replace($this->rootPath, '', $fileReadPath);
梁俊杰's avatar
梁俊杰 committed
384
                break;
385 386 387
            case self::DOWNLOAD_TYPE_RETURN_ABSOLUTE_PATH:
                $filePath = $this->rootPath . "/export/";
                !is_dir($filePath) && mkdir($filePath, 0777, true);
jiangkebao's avatar
jiangkebao committed
388 389
                $date = date('YmdHis');
                $fileReadPath = $filePath . $filename . '-' . $date . "." . strtolower($this->fileType);
390
                $objWriter->save($fileReadPath);
391
                return config('server.settings.document_root') . "/export/" . $filename . '-' . $date . "." .
392 393
                    strtolower($this->fileType);
                break;
梁俊杰's avatar
梁俊杰 committed
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
            default:
                throw new \Exception('不支持此种下载类型');
                break;
        }
    }

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

    /**
     * 返回原生的PhpOffice Reader 手动处理数据
王源's avatar
王源 committed
411
     * @return null|Spreadsheet
梁俊杰's avatar
梁俊杰 committed
412 413 414 415 416
     */
    public function getReader()
    {
        return $this->reader;
    }
王源's avatar
王源 committed
417

梁俊杰's avatar
梁俊杰 committed
418
}