think PHP导入导出excel

本地环境

think PHP5，PhpOffice/PhpSpreadsheet，composer
PHP版本7.4，这个插件的最低版本要求7.2
配置PhpSpreadsheet
官网：https://phpspreadsheet.readthedocs.io/en/stable/

composer require phpoffice/phpspreadsheet

数据库与excel表

导入文件

思路：首先读取excel文件数据，将他存放在缓存中，然后按照数据库的插入指令，插入数据

测试

public function upexceltest()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Welcome to Helloweba.');
        $writer = new Xlsx($spreadsheet);
        $path = ROOT_PATH.'public'.DS.'uploads'.DS.'excel';
        $writer->save($path.'/'.'hello.xlsx');//默认保存文件到public
        dump($writer);

    }

这段代码会产生一个excel文件

存缓存

新建phpspreadsheet实例

use think\Db;
use think\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

获取数据，移动获取到的excel文件到本地目录

//正式使用excel上传
    //读取excel数据然后将数据组装成sql语句，传递进mysql中
    //第一步，读取excel信息，将数据中需要的预处理提取出来
    //第二部，将预处理数据进行封装成sql数据，使用模型传入数据库
    public function upexcel(Request $request)
    {
        // 判断请求类型是否为POST
        if ($request->isPost()) {
            // 获取上传的文件信息
            $file = $request->file('file');
            if (empty($file)) {
                $this->error('请选择要上传的文件！');
            }
            // 移动到框架应用根目录/public/uploads/ 目录下
            $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads'.'/excel');
            if ($info) {
                // 获取文件路径
                $filePath = $info->getSaveName();

开始实例化导入

try {
                    // 使用PHPSpreadsheet读取Excel文件
                    $reader = IOFactory::createReader('Xlsx');
                    $reader->setReadDataOnly(true);
                    $spreadsheet = $reader->load(ROOT_PATH . 'public' . DS . 'uploads'.'/excel' . DS . $filePath);
                    $worksheet = $spreadsheet->getActiveSheet();//获取excel中的活动表
                    $highestRow = $worksheet->getHighestRow(); // 总行数
                    $highestColumn = $worksheet->getHighestColumn(); // 总列数
                    $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 转换为数字索引
                    $lines = $highestRow - 1; // 减1是因为Excel的行号从1开始，实际数据行应从第2行开始计算
                    if ($lines <= 0) {
                        $this->error('Excel表格中没有数据！');
                    } else {

将数据提取出来成数组

$rowData = []; // 初始化二维数组，在外部循环之前定义

                        for ($row = 2; $row <= $highestRow; ++$row) {
                            // 为每一行初始化一个新的数据数组
                            $currentRowData = [];

                            for ($col = 1; $col <= $highestColumnIndex; ++$col) {
                                // 使用较新的 getCell() 方法，但首先确认您的PhpSpreadsheet版本
                                $cellAddress = Coordinate::stringFromColumnIndex($col) . $row; // 转换列索引为列字母
                                $cell = $worksheet->getCell($cellAddress);
                                $currentRowData[] = $cell->getValue(); // 将当前单元格的值添加到该行数据中
                            }
                            // 将当前行的所有数据作为一个数组添加到 rowData
                            $rowData[] = $currentRowData;
                        }
                        // 现在，$rowData 是一个二维数组，每个内部数组代表一行的数据
//                        return json_encode($rowData); // 打印出来检查结果
                        //将二维数组转换为键值对的数据，姓名:mmm,语文：23
//                        foreach($rowData as $key=>$value){
//                            foreach ($rowData[])
//                        }

将数组提取出来组合成sql需要的数组批量导入数据库

$newData = [];
                        foreach($rowData as $row){
                            $newRow = ['name' => $row[0], 'chinese' => $row[1],'math'=>$row[2],'english'=>$row[3]];
                            $newData[] = $newRow;
                        }
                        $data = model('student')->insertAll($newData);
                        if ($data){
                            return json_encode(['code'=>200,'msg'=>'Excel数据导入成功！']);
                        }
                    }
                } catch (\Exception $e) {
                    return "导入失败";
                };
            }
        }
    }

完整代码

//正式使用excel上传
    //读取excel数据然后将数据组装成sql语句，传递进mysql中
    //第一步，读取excel信息，将数据中需要的预处理提取出来
    //第二部，将预处理数据进行封装成sql数据，使用模型传入数据库
    public function upexcel(Request $request)
    {
        // 判断请求类型是否为POST
        if ($request->isPost()) {
            // 获取上传的文件信息
            $file = $request->file('file');
            if (empty($file)) {
                $this->error('请选择要上传的文件！');
            }
            // 移动到框架应用根目录/public/uploads/ 目录下
            $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads'.'/excel');
            if ($info) {
                // 获取文件路径
                $filePath = $info->getSaveName();

                try {
                    // 使用PHPSpreadsheet读取Excel文件
                    $reader = IOFactory::createReader('Xlsx');
                    $reader->setReadDataOnly(true);
                    $spreadsheet = $reader->load(ROOT_PATH . 'public' . DS . 'uploads'.'/excel' . DS . $filePath);
                    $worksheet = $spreadsheet->getActiveSheet();//获取excel中的活动表
                    $highestRow = $worksheet->getHighestRow(); // 总行数
                    $highestColumn = $worksheet->getHighestColumn(); // 总列数
                    $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 转换为数字索引
                    $lines = $highestRow - 1; // 减1是因为Excel的行号从1开始，实际数据行应从第2行开始计算
                    if ($lines <= 0) {
                        $this->error('Excel表格中没有数据！');
                    } else {
                        // 这里可以添加读取每行数据的逻辑，例如：
                        $rowData = []; // 初始化二维数组，在外部循环之前定义

                        for ($row = 2; $row <= $highestRow; ++$row) {
                            // 为每一行初始化一个新的数据数组
                            $currentRowData = [];

                            for ($col = 1; $col <= $highestColumnIndex; ++$col) {
                                // 使用较新的 getCell() 方法，但首先确认您的PhpSpreadsheet版本
                                $cellAddress = Coordinate::stringFromColumnIndex($col) . $row; // 转换列索引为列字母
                                $cell = $worksheet->getCell($cellAddress);
                                $currentRowData[] = $cell->getValue(); // 将当前单元格的值添加到该行数据中
                            }
                            // 将当前行的所有数据作为一个数组添加到 rowData
                            $rowData[] = $currentRowData;
                        }
                        // 现在，$rowData 是一个二维数组，每个内部数组代表一行的数据
//                        return json_encode($rowData); // 打印出来检查结果
                        //将二维数组转换为键值对的数据，姓名:mmm,语文：23
//                        foreach($rowData as $key=>$value){
//                            foreach ($rowData[])
//                        }
                        $newData = [];
                        foreach($rowData as $row){
                            $newRow = ['name' => $row[0], 'chinese' => $row[1],'math'=>$row[2],'english'=>$row[3]];
                            $newData[] = $newRow;
                        }
                        $data = model('student')->insertAll($newData);
                        if ($data){
                            return json_encode(['code'=>200,'msg'=>'Excel数据导入成功！']);
                        }
                    }
                } catch (\Exception $e) {
                    return "导入失败";
                };
            }
        }
    }

导出数据

从数据库导出数据
思路：通过sql语句查询数据，转化成二维数组，二维数组之后按照foreach循环将数据按照要求传入excel表

实例化excel表格

//导出数据，第一步，导出数据库，第二部通过数据库导出成excel数据
    public function downexcel()
    {

        //设置表数据
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('学生成绩表');
        //将其存入excel表
        //表头
        设置单元格内容
        $worksheet->setCellValue('A1',  '学生成绩表');
        $worksheet->setCellValue('A2','姓名');
        $worksheet->setCellValue('B2',  '语文');
        $worksheet->setCellValue('C2',  '数学');
        $worksheet->setCellValue('D2', '外语');
        $worksheet->setCellValue('E2',  '总分');
        //合并单元格
        $worksheet->mergeCells('A1:E1');

        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
        $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);

查询数据库数据，并按要求写入数据库

$db = Db::query('select id,name,chinese,math,english from fa_student');
     $len = count($db);
     $j = 0;
     for($i=0;$i<$len;$i++){
         $j = $i+3;//从第三行开始
         $data = $db[$i];
         $worksheet->setCellValue('A'.$j,$data['name']);//A3,第一行
         $worksheet->setCellValue('B'.$j,$data['chinese']);//A3,第一行
         $worksheet->setCellValue('C'.$j,$data['math']);//A3,第一行
         $worksheet->setCellValue('D'.$j,$data['english']);//A3,第一行
         $worksheet->setCellValue('E'.$j,$data['chinese'] + $data['math'] + $data['english']);//
     }

设置excel格的式

 //设置外边框
      $styleArrayBody = [
          'borders' => [
              'allBorders' => [
                  'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                  'color' => ['argb' => '666666'],
              ],
          ],
          'alignment' => [
              'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
          ],
      ];
      $total_rows = $len + 2;
//添加所有边框/居中
      $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);

打印数据

       //保存数据并使用浏览器打印出来
//        $filename = '成绩表.xlsx';
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        header('Content-Disposition: attachment;filename="'.$filename.'"');
//        header('Cache-Control: max-age=0');
//        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        //这行代码会将生成的Excel文件内容直接输出到HTTP响应体中，这意味着它将作为下载的形式提供给客户端（比如浏览器），或者如果是在命令行脚本中运行，则会输出到标准输出流。
//        $writer->save('php://output');//如果不需要浏览器打开就保存excel文件的话，请注释掉这行代码

        //保存数据直接保存为excel，好像没啥区别
        $filename = '成绩表.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

完整代码

    //导出数据，第一步，导出数据库，第二部通过数据库导出成excel数据
    public function downexcel()
    {

        //设置表数据
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('学生成绩表');
        //将其存入excel表
        //表头
        设置单元格内容
        $worksheet->setCellValue('A1',  '学生成绩表');
        $worksheet->setCellValue('A2','姓名');
        $worksheet->setCellValue('B2',  '语文');
        $worksheet->setCellValue('C2',  '数学');
        $worksheet->setCellValue('D2', '外语');
        $worksheet->setCellValue('E2',  '总分');
        //合并单元格
        $worksheet->mergeCells('A1:E1');

        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
        $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);
        $db = Db::query('select id,name,chinese,math,english from fa_student');
        $len = count($db);
        $j = 0;
        for($i=0;$i<$len;$i++){
            $j = $i+3;//从第三行开始
            $data = $db[$i];
            $worksheet->setCellValue('A'.$j,$data['name']);//A3,第一行
            $worksheet->setCellValue('B'.$j,$data['chinese']);//A3,第一行
            $worksheet->setCellValue('C'.$j,$data['math']);//A3,第一行
            $worksheet->setCellValue('D'.$j,$data['english']);//A3,第一行
            $worksheet->setCellValue('E'.$j,$data['chinese'] + $data['math'] + $data['english']);//
        }

        //设置外边框
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $total_rows = $len + 2;
//添加所有边框/居中
        $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);

        //保存数据并使用浏览器打印出来
//        $filename = '成绩表.xlsx';
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        header('Content-Disposition: attachment;filename="'.$filename.'"');
//        header('Cache-Control: max-age=0');
//        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        //这行代码会将生成的Excel文件内容直接输出到HTTP响应体中，这意味着它将作为下载的形式提供给客户端（比如浏览器），或者如果是在命令行脚本中运行，则会输出到标准输出流。
//        $writer->save('php://output');//如果不需要浏览器打开就保存excel文件的话，请注释掉这行代码

        //保存数据直接保存为excel，好像没啥区别
        $filename = '成绩表.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

原作者连接
https://blog.csdn.net/ice_mocha/article/details/116460057
更详细