安装

> composer require phpoffice/phpspreadsheet

excel文件导出

调用方法

use app\exception\ApiException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\file\UploadedFile;

class Excel
{
    //excel文件导出示例
    public function exportExample()
    {
        //需导出的数据,多条数据
        $data = [
            [
                'time' => '时间',
                'order_num' => '付款人数',
                'did' => '付款订单数',
                'total_price' => '付款金额',
                'good_num' => '付款件数'
            ],
            [
                'time' => '时间',
                'order_num' => '付款人数',
                'did' => '付款订单数',
                'total_price' => '付款金额',
                'good_num' => '付款件数'
            ]
        ];
        $xlsName = "测试" . date("YmdHis", time());//文件名称
        $head = ['日期', '付款人数', '付款订单数', '付款金额', '付款件数'];// 表头信息
        //表字段和表头信息一一对应
        $keys = ['time', 'order_num', 'did', 'total_price', 'good_num'];
        $this->export($xlsName, $data, $head, $keys);// 传递参数
    }

封装导出方法

/**
     * 导出Excel
     * @param $name string 导出文件名称
     * @param array $data 导出数据数组
     * @param array $head
     * @param array $keys
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export($name, $data = [], $head = [], $keys = [])
    {
        $count = count($head);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }
        //循环设置单元格：
        foreach ($data as $key => $item) {
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {
                //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                //固定列宽
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20);
            }

        }
        $names = $name;
        //utf-8转unicode格式
        $name = iconv('UTF-8', 'UCS-2BE', $name);
        $len = strlen($name);

        $str = '';

        for ($i = 0; $i < $len - 1; $i = $i + 2) {

            $c = $name[$i];

            $c2 = $name[$i + 1];

            if (ord($c) > 0) {

                $str .= '\u' . base_convert(ord($c), 10, 16) . str_pad(base_convert(ord($c2), 10, 16), 2, 0, STR_PAD_LEFT);

            } else {

                $str .= '\u' . str_pad(base_convert(ord($c2), 10, 16), 4, 0, STR_PAD_LEFT);

            }

        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Data-Type: binary');
        //前端导出数据根据这个unicode格式解析为中文
        header('Data-Filename: ' . $str);
        header('Content-Disposition: attachment;filename="' . $names . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Access-Control-Expose-Headers:Data-Type,Data-Filename');

        //header('Content-Type: application/vnd.ms-excel');
        //header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        //header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
 

excel文件导入

将文件转为数据，方便后续对数组操作，插入数据库等

/**
 * @param UploadedFile $file
 * @return array
 * @throws ApiException
 */
public function parseImportFileToArray(UploadedFile $file)
{
    if ($file->getSize() > 5 * 1024 * 1024) {
        @unlink($file->getRealPath());
        throw new ApiException([400, '文件大小不能超过5M']);
    }

    if (!in_array($extension = $file->extension(), ['csv', 'xls', 'xlsx'])) {
        @unlink($file->getRealPath());
        throw new ApiException([400, '必须为excel表格，且必须为xls格式！']);
    }

    $type = strtolower($file->extension());
    if ($type == 'xlsx') {
        $types = 'Xlsx';
    } elseif ($type == 'xls') {
        $types = 'Xls';
    } elseif ($type == 'csv') {
        $types = 'Csv';
    }

    try {
        $objReader = IOFactory::createReader($types);
        $PHPExcel = $objReader->load($file->getRealPath());
        $currentSheet = $PHPExcel->getSheet(0);  //读取excel文件中的第一个工作表

        //用完就删除 文件
        @unlink($file->getRealPath());
        return $currentSheet->toArray('', true, true, true);

    } catch (\Exception $exception) {
        @unlink($file->getRealPath());
        throw new ApiException([400, $exception->getMessage()]);
    }
}

调用导入方法

public function importQuestion(Request $request)
{
    //获取上传文件信息
    $file = $request->file('file');

    //获取数组
    $excelArray = (new Excel)->parseImportFileToArray($file);

    //第1,2行为注释，去掉。这个看文件实际情况，通常去掉第一行
    $data = array_slice($excelArray,2,count($excelArray) - 2,true); //去掉第1,2行


    $this->checkQuestionExcelData($data);

    //数据导入题库，操作数组
    $this->questionMultipleAdd($data);

    return $this->success([], '导入成功');
}