Excel导入导出_浮叶蓝空的博客

> 此功能适用于ThinkPHP项目。
>此功能依赖于phpspreadsheet：`composer require phpoffice/phpspreadsheet`

```php
<?php

namespace app\common\controller;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Exception;

/**
 * Excel识别和创建
 * Trait Excel
 * description: 此功能依赖于phpspreadsheet，安装：composer require phpoffice/phpspreadsheet
 * @author fuyelk <fuyelk@fuyelk.com>
 */
trait Excel
{
    /**
     * 解析导入的文件，支持csv、xls、xlsx
     * @param array $column 字段映射
     * @param string $filePath 文件路径（缺省则检查post文件）
     * example:
     * [
     *      '姓名' => 'username',
     *      '性别' => 'gender',
     *      '年龄' => 'years',
     *      '手机号' => 'mobile'
     * ]
     */
    protected function parseExcel($column = [], $filePath = '')
    {
        if (empty($column) || !is_array($column)) {
            die('请设置字段对应规则');
        }
        // 上传单文件
        if (empty($filePath)) {
            if (!$this->request->isPost() || !($file = request()->file())) {
                die('未上传任何文件');
            }
            $file = array_shift($file);
            $filename = '';
            $file_root = dirname(dirname(dirname(__DIR__))) . '/public/uploads/files/';
            // 移动到框架应用根目录/public/uploads/files 目录下
            if ($file) {
                $info = $file->move($file_root);
                if ($info) {
                    $filename = $info->getSaveName();
                } else {
                    // 上传失败获取错误信息
                    die($file->getError());
                }
            }
            $filePath = str_replace('\\', '/', $file_root . $filename);
            if (!is_file($filePath)) {
                die('文件不存在');
            }
        }

//实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            die('未知的文件类型');
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);
            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }
        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                die('未知的数据类型');
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    if (is_object($val)) $val = $val->__toString();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($column[$k]) && $k !== '') {
                        $row[$column[$k]] = $v;
                    }
                }
                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            die($exception->getMessage());
        }
        return $insert;
    }

/**
     * 导出Excel
     * @param string $sheetName sheet名
     * @param array $column 字段名，仅保存该值中包含的字段
     * example: ['姓名', '性别', '年龄', '手机号']
     * @param array $data 数据
     */
    protected function exportExcel($sheetName = '', $column = [], $data = [])
    {
        $sheetName = empty($sheetName) ? 'sheet1' : $sheetName;
        if (empty($column)) die('字段名不能为空');

$spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

//设置sheet的名字
        $spreadsheet->getActiveSheet()->setTitle($sheetName);

$col = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
        $temp_col = $col;

//设置第一行小标题
        foreach ($column as $col_name) {
            $sheet->setCellValue(array_shift($temp_col) . 1, $col_name);
        }

// 创建更多数据
        $row = 2;
        foreach ($data as $line => $value) {
            $temp_col = $col;
            foreach ($column as $col_name) {
                $sheet->setCellValue(array_shift($temp_col) . $row, array_shift($value));
            }
            $row++;
        }

$file_name = date('Y-m-d', time()) . rand(1000, 9999);
        $file_name = $file_name . ".xlsx";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}
```

字符转Unicode编码

字符串转GBK编码

0人点赞