Exporter.php 12.6 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 39 40 41
    /**
     * 以字节流的形式下载
     */
    CONST DOWNLOAD_TYPE_STREAM = 1;

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

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

    CONST EXPORTER_TYPE_CSV = 1;
    CONST EXPORTER_TYPE_XLS = 2;
    CONST EXPORTER_TYPE_XLSX = 3;
42 43 44 45
    /**
     * @var ConfigInterface
     */
    protected $config;
王源's avatar
王源 committed
46
    private $rootPath;
zhaopeng343's avatar
zhaopeng343 committed
47
    private $beginRowIndex = 1;
梁俊杰's avatar
梁俊杰 committed
48 49 50 51 52 53 54 55 56 57
    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
58 59 60 61
     * @param int $export_type 类型
     * @param string $tempFilePath 模板文件地址
     * @param string $name 名称
     * @param int $sheetIndex
王源's avatar
王源 committed
62
     * @throws PhpSpreadsheetException
王源's avatar
王源 committed
63
     * @throws \Exception
梁俊杰's avatar
梁俊杰 committed
64
     */
王源's avatar
王源 committed
65
    public function __construct(int $export_type, $tempFilePath = "", $name = "export_data", $sheetIndex = 0)
梁俊杰's avatar
梁俊杰 committed
66
    {
王源's avatar
王源 committed
67
        $this->config = container(ConfigInterface::class);
68
        $this->rootPath = $this->config->get('server.settings.document_root', BASE_PATH . '/public');
王源's avatar
王源 committed
69
        if ($tempFilePath) {
70
            $tempFilePath = $this->rootPath . "/" . $tempFilePath;
王源's avatar
王源 committed
71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
            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
94 95
        }
        $this->name = $name;
王源's avatar
王源 committed
96
        $this->sheet = $reader->getSheet($sheetIndex);
梁俊杰's avatar
梁俊杰 committed
97 98 99 100 101 102
        $this->reader = $reader;
        $this->sheetIndex = $sheetIndex;
    }

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

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

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

    /**
     * 重置排序
     */
    private function resetRowIndex()
    {
        $this->beginRowIndex = 1;
梁俊杰's avatar
梁俊杰 committed
157
        return $this;
梁俊杰's avatar
梁俊杰 committed
158 159 160 161 162 163 164 165 166 167 168
    }

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

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

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

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

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

        if (count($keys)) {
梁俊杰's avatar
梁俊杰 committed
225 226 227 228 229 230 231
            foreach ($data as $k => $v) {
                $data[$k] = $this->getKeyValue($v, $keys);
            }
        }

        $this->sheet->fromArray($data, null, $this->beginColumnChar . $this->beginRowIndex);
        //美化样式
王源's avatar
王源 committed
232
        $this->applyStyle($this->beginColumnChar . $this->beginRowIndex . ":" . $this->sheet->getHighestColumn() . ($this->beginRowIndex + count($data) - 1));
梁俊杰's avatar
梁俊杰 committed
233
        $this->beginRowIndex += count($data);
梁俊杰's avatar
梁俊杰 committed
234
        return $this;
梁俊杰's avatar
梁俊杰 committed
235 236
    }

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

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

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

王源's avatar
王源 committed
299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315
    /**
     * 设置列宽
     * @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
316
    /**
王源's avatar
王源 committed
317
     * 下载导出的文件
梁俊杰's avatar
梁俊杰 committed
318 319
     * @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
320 321
     * @return mixed | void
     * @throws \Exception
梁俊杰's avatar
梁俊杰 committed
322 323 324 325 326 327 328
     */
    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
329
        $this->reader->setActiveSheetIndex(0);
梁俊杰's avatar
梁俊杰 committed
330 331 332 333
        $objWriter = IOFactory::createWriter($this->reader, $this->fileType);
        switch ($downloadType) {
            case self::DOWNLOAD_TYPE_STREAM:
            case self::DOWNLOAD_TYPE_SAVE_AND_DOWNLOAD:
334
                $filename .= "." . strtolower($this->fileType);
梁俊杰's avatar
梁俊杰 committed
335 336 337 338 339 340 341 342 343 344 345 346 347 348 349
                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
350
                    $f = $this->rootPath . "/export";
梁俊杰's avatar
梁俊杰 committed
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371
                    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
372
                $filePath = $this->rootPath . "/export/";
fuyunnan's avatar
fuyunnan committed
373
                !is_dir($filePath) && mkdir($filePath, 0777, true);
374 375 376
                $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
377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394
                break;
            default:
                throw new \Exception('不支持此种下载类型');
                break;
        }
    }

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

    /**
     * 返回原生的PhpOffice Reader 手动处理数据
王源's avatar
王源 committed
395
     * @return null|Spreadsheet
梁俊杰's avatar
梁俊杰 committed
396 397 398 399 400
     */
    public function getReader()
    {
        return $this->reader;
    }
王源's avatar
王源 committed
401

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