phpSpreadsheet使用

导入

$objRead = IOFactory::createReader('Xlsx');
if (!$objRead->canRead($file)) {
    $objRead = IOFactory::createReader('Xls');
    if (!$objRead->canRead($file)) {
        return '只支持导入Excel文件';
    }

}

$excel         = $objRead->load($file);
$sheet         = $excel->getSheet(0); // 读取第一個工作表
$list = $sheet->toArray();
//第一行是标题，所以要去除
array_shift($list);
......($list就是excel中的数据，可以根据需求进行处理)

导出

$spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '封面')
            ->setCellValue('B1', '课程类型')
            ->setCellValue('C1', '呈现类型')
            ->setCellValue('D1', '课程名称')
            ->setCellValue('E1', '计费方式')
            ->setCellValue('F1', '价格')
            ->setCellValue('G1', '购买时间')
            ->setCellValue('H1', '发布人')
            ->setCellValue('I1', '费用总额');

            $i = 2;
            foreach ($res['data']['data'] as $k => $v) {
                //呈现类型
                $v['typeCn'] = CourseModel::TYPE_CN[$v['type']];

                //根据购买方式获取数据
                $data               = $this->getDataByBuyWay($v['way'], $v['oneTimePrice'], $v['useNumberPrice'], $v['purchaseCount']);
                $v['wayCn']         = $data['wayCn'];
                $v['price']         = $data['price'];
                $v['purchaseCount'] = $data['purchaseCount'];

                //购买时间
                $v['createTime'] = explode(',', $v['createTime']);

                $sheet->setCellValue('A' . $i, $v['cover'])
                    ->setCellValue('B' . $i, $v['typeCn'])
                    ->setCellValue('E' . $i, $v['wayCn'])
                    ->setCellValue('F' . $i, $v['price'])
                   ->setCellValue('G' . $i, $v['createTime']);
                    ->setCellValue('C' . $i, $v['categoryName'])
                    ->setCellValue('D' . $i, $v['name'])
                    ->setCellValue('H' . $i, $v['creator'])
                    ->setCellValue('I' . $i, $v['allPrice']);
                $i++;
            }

        $title = date('Y-m-d') . '购买的课程';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . iconv('utf-8', 'gbk', $title) . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save(__DIR__ . '/' . iconv('utf-8', 'gbk', $title) . '.xlsx');//保存在当前目录下
//        $writer->save('php://output');
        exit;

合并单元格导出

        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '封面')
            ->setCellValue('B1', '课程类型')
            ->setCellValue('C1', '呈现类型')
            ->setCellValue('D1', '课程名称')
            ->setCellValue('E1', '计费方式')
            ->setCellValue('F1', '价格')
            ->setCellValue('G1', '购买时间')
            ->setCellValue('H1', '发布人')
            ->setCellValue('I1', '费用总额');

            $i = 2;
            foreach ($res['data']['data'] as $k => $v) {
                //呈现类型
                $v['typeCn'] = CourseModel::TYPE_CN[$v['type']];

                //根据购买方式获取数据
                $data               = $this->getDataByBuyWay($v['way'], $v['oneTimePrice'], $v['useNumberPrice'], $v['purchaseCount']);
                $v['wayCn']         = $data['wayCn'];
                $v['price']         = $data['price'];
                $v['purchaseCount'] = $data['purchaseCount'];

                //购买时间
                $v['createTime'] = explode(',', $v['createTime']);

                if (count($v['wayCn']) > 1) {
                    $end = $i + count($v['wayCn']) - 1;
                    $sheet->mergeCells('A' . $i . ':A' . $end);
                    $sheet->mergeCells('B' . $i . ':B' . $end);
                    $sheet->mergeCells('C' . $i . ':C' . $end);
                    $sheet->mergeCells('D' . $i . ':D' . $end);
                    $sheet->mergeCells('H' . $i . ':H' . $end);
                    $sheet->mergeCells('I' . $i . ':I' . $end);
                }

                $sheet->setCellValue('A' . $i, $v['cover'])
                    ->setCellValue('B' . $i, $v['typeCn'])
                    ->setCellValue('C' . $i, $v['categoryName'])
                    ->setCellValue('D' . $i, $v['name'])
                    ->setCellValue('H' . $i, $v['creator'])
                    ->setCellValue('I' . $i, $v['allPrice']);

                foreach ($v['wayCn'] as $key => $wayCn) {
                    $sheet->setCellValue('E' . $i, $wayCn)
                        ->setCellValue('F' . $i, $v['price'][$key])
                        ->setCellValue('G' . $i, $v['createTime'][$key]);
                    $i++;
                }
            }

        $title = date('Y-m-d') . '购买的课程';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . iconv('utf-8', 'gbk', $title) . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save(__DIR__ . '/' . iconv('utf-8', 'gbk', $title) . '.xlsx');//保存在当前目录下
//        $writer->save('php://output');
        exit;

效果如下：

image.png