PHP实现读写Excel文件的经典Composer包phpoffice/phpspreadsheet使用详记

实例应用（整理自互联网和自有代码）

批量读取Excel数据导入到数据库

演示一：
Xlsx文件如下：

代码：

require 'vendor/autoload.php';
include('conn.php'); //连接数据库

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('students.xlsx'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

$lines = $highestRow - 2; 
if ($lines <= 0) {
    exit('Excel表格中没有数据');
}

$sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES ";

for ($row = 3; $row <= $highestRow; ++$row) {
    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
    $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文
    $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学
    $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语

    $sql .= "('$name','$chinese','$maths','$english'),";
}
$sql = rtrim($sql, ","); //去掉最后一个,号
try {
    $db->query($sql);
    echo 'OK';
} catch (Exception $e) {
    echo $e->getMessage();
}

其中$worksheet->getCellByColumnAndRow($col, $row)->getValue()可以获取表格中任意单元格数据内容，$col表示单元格所在的列，以数字表示，A列表示第一列，$row表示所在的行。
我们只需要第三行以后的数据，因此直接从第三行开始循环遍历，获取成绩，组装成SQL语句。
数据结果：

演示二：
程序自创建Excel + 批量生成账户

代码：

<?php
namespace app\admin\controller\test\phpspreadsheet;

use app\admin\controller\Base;

use PhpOffice\PhpSpreadsheet\Reader\Xls\MD5;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

class Index extends Base
{
    public function write()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');

        $writer = new Xlsx($spreadsheet);
        $writer->save('d://hello.xlsx');
        //dump('2222222222');die;
        //return view();
    }

    public function read(){
        $file_name = "D";
        $uploadwork    = "d://";
        $uploadfile    = $uploadwork.$file_name.'.xlsx';
        $reader        = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); //设置以Excel5格式(Excel97-2003工作簿)
        $PHPExcel      = $reader->load($uploadfile); // 载入excel文件
        $sheet         = $PHPExcel->getSheet(0); // 读取第一個工作表
        $highestRow    = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数
        $data          = [];
        for ($row = 4; $row <= $highestRow; $row++) //行号从1开始
        {
            for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
            {
                if (empty($sheet->getCell($column . $row)->getValue()) == false) {
                    $data[$row][$column]=$sheet->getCell($column . $row)->getValue();
                }
            }
            if(!isset($data[$row]['B'])) unset($data[$row]);
        }

        dump($data);

        //班級和文件信息
        $fileAndClass=[
          'A'=>17,
          'B'=>18,
          'C'=>19,
          'D'=>20,
        ];

        //組裝用戶信息
        $userArr=null;
        foreach ($data as $key=>$val){
            $perUser=null;
            $perUser['u_picture']='/static/student/img/a4.png';
            $perUser['u_gender']=0;
            $perUser['u_name']=$val['D'].'-'.$val['E'];
            $perUser['u_username']=$val['G'].'_'.strtolower($val['B']);
            $perUser['u_password']=md5($perUser['u_username']);
            $perUser['u_class']=$fileAndClass[$file_name];

            //如果是女性，更改性別和默認圖片
            if(strtolower($val['F'])=='f'){
                $perUser['u_picture']='/static/student/img/a2.png';
                $perUser['u_gender']=1;
            }
            $userArr[]=$perUser;
        }
        $ans=db('user')->insertAll($userArr);
        dump($ans);
        dump($userArr);die;


    }
}

这个示例来源于：https://www.cnblogs.com/Renyi-Fan/p/9744090.html