PhpSpreadsheet如何读取excel文件

PhpSpreadsheet如何读取excel文件

一、总结

一句话总结：万能的百度，直接搜代码就好，绝对有，毕竟github上面4000+的关注，说明很多人用了这个，使用照着demo倒是异常简单

 

 

二、使用PhpSpreadsheet将Excel导入到MySQL数据库

日常开发中，我们经常遇到这样的场景，需要将一个Excel表格数据如客户信息、学生成绩表导入到系统数据库中，然后在系统中进行进一步操作，如给导入的客户群发短信，统计学生成绩排名。PHP导入Excel避免了人工录入信息的麻烦和出错，提高效率。

下载源码

本文以导入学生成绩表为例，给大家讲解使用PhpSpreadsheet将Excel导入的MySQL数据库。

准备

首先我们需要准备一张MySQL表，表名t_student，表结构如下：

CREATE TABLE `t_student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT '姓名',
  `chinese` int(6) NOT NULL DEFAULT '0' COMMENT '语文',
  `maths` int(6) NOT NULL DEFAULT '0' COMMENT '数学',
  `english` int(6) NOT NULL DEFAULT '0' COMMENT '外语',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这是一张学生成绩表，用来存储学生的姓名和语数外三门课程的成绩。

接着，我们准备excel表格文件，我特意制作了一个Excel文件students.xlsx：

 

然后将Excel文件放置程序应用目录下。当然，实际应用中，我们一般通过web上传到服务器指定目录下，然后再进行导入数据库操作。本文web上传这块就不涉及了，感兴趣的同学可以参考本站文章：Dropzone.js实现文件拖拽上传，功能强大的文件上传组件-WebUploader。

最后，你还需要安装好PhpSpreadsheet，在前一节文章中有介绍，非常简单：使用PhpSpreadsheet读取和写入Excel

导入Excel

准备工作做好后，我们来开始导入。

思路很简单：使用PhpSpreadsheet读取Excel表格中的有用信息，然后组装成sql语句，最后批量插入到MySQL表中。我把代码贴出来。

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语句。

我们使用批量插入MySQL语句，当然你也可以逐条插入，但是效率没有批量插入高。

最后执行导入代码，你会发现数据表里有数据了：

MariaDB [demo]> select * from t_student;
+----+-----------+---------+-------+---------+
| id | name      | chinese | maths | english |
+----+-----------+---------+-------+---------+
| 13 | 王二小    |      82 |    78 |      65 |
| 14 | 李万豪    |      68 |    87 |      79 |
| 15 | 张三丰    |      89 |    90 |      98 |
| 16 | 王老五    |      68 |    81 |      72 |
+----+-----------+---------+-------+---------+
4 rows in set (0.00 sec)

 

 

参考：使用PhpSpreadsheet将Excel导入到MySQL数据库_Helloweba
https://www.helloweba.net/php/562.html

 

三、PhpSpreadsheet 读取excel文件

phpexcel不再维护,使用PhpSpreadsheet

官方地址
github

安装

composer require phpoffice/phpspreadsheet

例子

class Vcard
{
    public function index()
    {
        $file_name = "1";
        $uploadwork    = "C:\Users\Administrator\Desktop\城市\二线城市（30个）27575条\\";
        $uploadfile    = $uploadwork.$file_name.'.xlsx';
        $reader        = \PHPExcel_IOFactory::createReader('excel2007'); //设置以Excel5格式(Excel97-2003工作簿)
        $PHPExcel      = $reader->load($uploadfile); // 载入excel文件
        $sheet         = $PHPExcel->getSheet(0); // 读取第一個工作表
        $highestRow    = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数
        $data          = [];
        for ($row = 2; $row <= $highestRow; $row++) //行号从1开始
        {
            for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
            {
                if (empty($sheet->getCell($column . $row)->getValue()) == false) {
                    if (empty($data[$row]) == false) {
                        $str = $sheet->getCell($column . $row)->getValue();
                        if (strlen($str)>11) {
                            $pieces = explode("|", $str);
                            $data[$row]['mobile'] = $pieces[0];
                        }else{
                            $data[$row]['mobile'] = $str;
                        }
                    } else {
                        $data[$row]['name'] = $sheet->getCell($column . $row)->getValue();
                    }
                }
            }
        }
        // dump($data);
        $myfile = fopen($uploadwork.$file_name.".vcf", "w") or die("Unable to open file!");
        foreach ($data as $key => $value) {
            $all_str = $this->make_vcard($value['name'], $value['mobile']);
            fwrite($myfile, $all_str);
        }
        fclose($myfile);
    }
}

 

参考：PhpSpreadsheet 读取excel文件 - HD2killers的博客 - CSDN博客
https://blog.csdn.net/HD2killers/article/details/80826981

 

 

 

四、自己phpSpreadsheet读写excel实例代码（亲测可用）

write函数功能：测试write内容到excel

read函数功能：将用户数据从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;


    }
}