PhpSpreadsheet导入导出

PhpSpreadsheet

使用前，使用composer引入PhpSpreadsheet

composer require phpoffice/phpspreadsheet

方法：

1. 导入转数组
2. 导出为文件
3. 导入文件存入数据库

<?php
/**
 * Created by PhpStorm.
 * User: WuNai
 * Date: 2019/4/2/002
 * Time: 16:57
 */

namespace common\helpers;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class FileProcess
{
    /**
     * 文件 转 Array
     * @param string $filePath 文件路径
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function toArray($filePath)
    {

        $spreadsheet = IOFactory::load($filePath);// 载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数

        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

        $data = [];
        for ($row = 2; $row <= $highestRow; ++$row) { // 从第二行开始
            $row_data = [];
            for ($column = 1; $column <= $highestColumnIndex; $column++) {
                $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
            }
            $data[] = $row_data;
        }

        return $data;
    }

    /**
     * Array 转 文件
     * @param array $title 标题 格式['title1', 'title2']
     * @param array $data 导出数据 ['A1' => '***', 'B2' => '***']
     * @param string $fileName 导出文件名称
     * @param array $options 配置 [ 'fileType' => 'xls',    导出文件后缀 默认 xls
     *                             'savePath' => '/web'    自定义保存地址
     *                           ]
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function toFile($title, $data, $fileName, $options = [])
    {

        /** 设置转义格式 */
        if (isset($options['fileType']) && !in_array($options['fileType'], ['xls', 'xlsx', 'ods', 'csv', 'html', 'tcpdf', 'dompdf', 'mpdf'])) {
            return false;
        }

        $spreadsheet = new Spreadsheet();

        $worksheet = $spreadsheet->getActiveSheet();
        //设置标题名称
        $worksheet->setTitle($fileName);

        foreach ($title as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        $row = 2; //第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }

        /** 设置文件后缀名 */
        if (!isset($options['fileType'])) {
            $fileName = $fileName . '.xls';
        } else {
            $fileName = $fileName . '.' . $options['fileType'];
        }

        /** 设置文件路径 */
        if (!isset($options['savePath'])) {
            $savePath = \Yii::$app->basePath . '/web/' . $fileName;
        } else {
            $savePath = $options['savePath'] . $fileName;
        }

        /** @var string $writerType 转义成PhpSpreadsheet能识别的后缀 */
        $writerType = ucfirst($options['fileType']);

        $writer = IOFactory::createWriter($spreadsheet, $writerType);

        $writer->save($savePath);

        /* 释放内存 */
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();

        return true;
    }

    /**
     * 导入文件插入数据库
     * @param string $tableName 表名
     * @param array $filed 字段名 [ 'create_id', 'create_time' ]
     * @param string $filePath 文件路径
     * @return bool|int
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \yii\db\Exception
     */
    public static function toSql($tableName, $filed, $filePath)
    {
        $params = self::toArray($filePath);

        $result = \Yii::$app->db->createCommand()->batchInsert($tableName, $filed, $params)->execute();

        return $result;
    }
}