php中如何使用的phpspreadsheet插件读取Execl

2021-06-13 本文已影响0人似朝朝我心

声明：首先php版本必须要到7.2或以上，其次必须安装了composer工具，因为这是一个自动处理依赖包的安装工具，相当node的npm或者说是linux里面的yum，因为这是安装phpspreadsheet插件的必须环境，phpspreadsheet这个插件就是让我们用来读取Execl表格当中的数据的。

其次是要开启php.ini当中的两个扩展：

extension = php_openssl.dll
extension = php_fileinfo.dll

我们可以到getcomposer.org的官网搜一下phpspreadsheet这个插件，下面有一个安装要求：提示你php版本必须在7.2以上。

如果你已经搭建好了基本环境，那么就可以在项目当中，通过cmd创建：

composer require phpoffice/phpspreadsheet

然后你会看到项目中就会多出一个vendor目录，代表你的phpspreadsheet插件成功安装。

如何读取Execl表格当中的数据呢？

首先准备一张Execl表格。

phpspreadsheet插件提供两种资源引入：execl文件的读取操作和写入操作。

首先得引入这个文件

读取一个execl文件所需的资源

#引入资源
require("./vendor/autoload.php");//相对于自己的路径出发
use PhpOffice\PhpSpreadsheet\IOFactory;

新建一个execl文件并写入数据所需的资源

#引入资源
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\Spreadsheet;

起步：创建一个spreadsheet对象（分情形）

spreadsheet对象是PHPSpreadsheet组件的核心类，借助这个spreadsheet对象可以让我们完成对execl文件的读取、创建、修改等各类操作。

如果我们项目当中已经有了一个execl文件，我们只需要这样来创建一个spreadsheet对象，通过一个已经存在的的文件创建spreadsheet对象的方法。

#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取文件的保存路径和文件名
$fileName = './student.xlsx';
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);
#打印spreadsheet对象查看
var_dump($spreadSheet);

-------------------------------------------------------------------------------------
其中创建spreadsheet对象还可以简写成下面这种形式，效果是一样的：
$spreadSheet = IOFactory::load($fileName);

打印spreadsheet对象查看

如果文件资源来自于表单，也就是说该execl文件是用户通过上传提交过来的，则使用如下方案进行创建spreadsheet对象。

<?php
#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取表单文件域上传的fileName信息
$fileName = $_FILES["filen_ame"]["tmp_name"]
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = IOFactory::load($fileName);
#打印spreadsheet对象查看
var_dump($spreadSheet);
?>

<!-- html代码 -->
<form action="#" method="post" enctype="multipart/form-data">
    <input type="file" name="file_name">
    <input type="submit" value="上传">
</form>

创建一个空的execl文件

<?php
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\spreadSheet;
$spreadSheet = new SpreadSheet();
var_dump($spreadSheet);

创建工作表worksheet对象
在创建完spreadSheet对象后，可以根据需要访问execl文件中任何一张工作表。

读取execl文件中的第一张表

$workSheet = $spreadSheet -> getSheet(0)

通过spreadSheet对象调用其下的getSheet(0)方法，括号内的参数0代表第一张表的序号，此外还可以通过工作的表面名来获取工作表worksheet对象。

和工作表worksheet对象相关的常用方法：

方法名称	功能说明	调用案例
getSheetCount	获取execl文件文件中的工作表数量，统计一下一共有多少张工作表，返回一个整数	`$spreadSheet -> getSheetCount()`
getSheetNames	获取execl文件中的所有的工作表名，返回的是一个数组	`$spreadSheet -> getSheetNames()`
getSheet	以序号的形式获取execl文件当中的工作表，0代表是第一张工作表对象，返回一个工作表对象	`$spreadSheet -> getSheet ()`
getSheetByName	以表名获取工作表（只能获取一张工作表的名字），返回一个工作表对象	`$spreadSheet -> getSheetByName ('Sheet1')`
setTitle	设置或修改execl文件中工作表的名字	`$spreadSheet -> setTitle('Copy of Worksheet 1')`

<?php 
#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取文件的保存路径和文件名
$fileName = './student.xlsx';
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);

#统计工作表的数量
$workSheet = $spreadSheet -> getSheetCount();
echo '当前execl文件中，共有'.$workSheet.'张工作表！';
echo '<hr/>';

#获取文件中的所有的工作表名
$workSheet = $spreadSheet -> getSheetNames();
print_r($workSheet);
echo '<hr/>';

#获取文件中的第一张工作表对象
$workSheet = $spreadSheet -> getSheet(0);
// print_r($workSheet);
echo '<hr/>';

#以工作表的名字获取文件中的工作表对象
$workSheet = $spreadSheet -> getSheetByName('Sheet1');
 // print_r($workSheet);
echo '<hr/>';
?>

创建好一个工作表对象后，我们就可以根据单元格的位置坐标来访问到指定单元格，继而读取或者设置单元格的值了

phpspreadsheet插件提供了使用列序号+行序号来定位到你想要获取到的单元格，其表达方式一般分为两种，比如我们想访问“何乐嘉”这个值的话，我们只需要这样表达：B2或者[2,2],其中B2这种取值的方式是最为常见的，而[2,2]这种取值的方式在循环时调用单元格更加方便。

与单元格相关的常用方法：

方法名称	功能说明	调用案例
getCell	获取指定单元格对象	`$workSheet -> getCell ('A2')`
getValue	获取指定单元格里面的数据，可以返回字符串、数字、布尔、公式等类型数据	`$workSheet -> getCell ('A2') -> getValue()`
getCellByColumnAndRow	获取指定单元格对象,功能一样，表达方式不一样	`$workSheet -> getCellByColumnAndRow (2,1)`
setCellValue	给单元格设置值,它是工作表对象的方法	`$workSheet -> setCellValue ('A3',TRUE)`
setValue	给单元格设置值,它是单元格对象的方法	`$workSheet -> getCell ('A2') -> setValue('言真清')`
getHighestRow	获取execl文件当中最大行的序号（统计一共有多少行），返回的值是数字	`$workSheet -> getHighestRow()`
getHighestColumn	获取execl文件当中最大行的列号（单元格有值的最末尾那一列的列号，即获取截止的列号）	`$workSheet -> getHighestColumn()`

案例代码：

参照图：

<?php
#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取文件的保存路径和文件名
$fileName = './student.xlsx';
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);

#获取execl文件中的第一张工作表对象
$workSheet = $spreadSheet -> getSheet(0);

#读取单元格B3的值
$value =  $workSheet -> getCell('B3') -> getValue();
echo '单元格B3的值为：'.$value;
echo '<hr/>';

#使用变量标记单元格坐标
$id = 3;
$value =  $workSheet -> getCell('B'.$id) -> getValue();
echo '单元格B3的值为：'.$value;
echo '<hr/>';

#给单元格设置新值，只是一个缓存状态，并不会真实修改单元格的值，要真实去修改必须借助数据库
$value =  $workSheet -> getCell('B4') -> setValue('大白');
echo '设置单元格B4的值为：'.$value;
echo '<hr/>';

#获取最大的行号（所有行），用于遍历取值
$rows = $workSheet -> getHighestRow();
echo '最大行号到第:'.$rows.'行';
echo '<hr/>';
#读取第二列所有单元格的值
for($i=2;$i<$rows;$i++){//从第二行开始
    $rowCell[] = $workSheet -> getCell('B'.$i) -> getValue();
}
echo '第二列单元格的所有值：';
var_dump($rowCell);
echo '<hr/>';

#获取截止列号,即获取所有列（一般用于遍历取值）
$columns = $workSheet -> getHighestColumn();
echo '截止列号到第:'.$columns.'列';
echo '<hr/>';
#读取第一行所有单元格的值
/* 因为列号是大写字母(字符串)，所以不能用
<=来作为for循环的第二个表达式的判断符合,但这样有格bug不能拿到最后一列，而且现在7.1版本后还报错了 */
// for($col = "A";$col != $columns;$col++){//从A列开始到末列
//  $colCell[] = $workSheet -> getCellByColumnAndRow($col,1) -> getValue();
// }
for($col = 1;$col <= 9;$col++){//从A列开始到末列
    $colCell[] = $workSheet -> getCellByColumnAndRow($col,1) -> getValue();
}
echo '第一行单元格的所有值：';
var_dump($colCell);
echo '<hr/>';
?>

将execl表格的数据通过预处理语句插入数据库

其次准备搭建数据库：

school.sql

create database school;
use school;
create table student
(
    stu_id int(10) unsigned primary key not null,
    stu_name varchar(20) not null,
    stu_class varchar(30)
);

create table score
(
   sc_id int auto_increment primary key not null,
   foreign key(stu_id) references student(stu_id)
   on delete cascade
   on update cascade,
   stu_id int(10) unsigned not null,
   sc_normal float(3,1),
   sc_lab float(3,1),
   sc_midterm float(3,1),
   sc_final float(3,1),
   sc_overall float(3,1) not null
);


提示：如果增添了这个on delete cascade,代表因约束不能删除table，

读入学生表信息：

<?php
#创建数据库连接对象
$mysqli = @new mysqli('localhost','root','12345678','school');
if($mysqli -> connect_error) {
    die('连接数据库失败：'.$mysqli -> connect_error);
}

#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取文件的保存路径和文件名
$fileName = './student.xlsx';
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);

#获取execl文件中的第一张工作表对象
$workSheet = $spreadSheet -> getSheet(0);

#获取最大的行号（所有行），用于遍历取值
$rows = $workSheet -> getHighestRow();

#sql语句
$sql = "insert into student(stu_id,stu_name,stu_class) 
        values(?,?,?)";

#判断一下预处理失败的情况
if(!($mysqli_stmt = $mysqli -> prepare($sql))){
    die('预处理失败');
}

#获取A、B、C列单元格的值：分别对应数据库的学号、姓名、班级
for($i=2;$i<$rows;$i++){//从第二行开始
    $stuId = $workSheet -> getCell('A'.$i) -> getValue();
    $stuName = $workSheet -> getCell('B'.$i) -> getValue();
    $stuClass = $workSheet -> getCell('C'.$i) -> getValue();
    #绑定参数
    $mysqli_stmt -> bind_param('iss',$stuId,$stuName,$stuClass);
    #执行预处理语句
    $mysqli_stmt -> execute();
    if ($mysqli_stmt -> affected_rows == 1) {
        #数据增加成功
        $count ++;#统计一下读入个数
    }
}
if($count > 0){
    #写入成功
    echo <<< end
    <script>
        alert("成功录入{$count}个学生的信息");
        location = "./index.php";
    </script>
end;
}else {
    #写入失败
    echo <<< end
    <script>
        alert("写入失败");
        location = "./index.php";
    </script>
end;
}
?>

读入成绩表信息：
其中总评成绩要经过计算：总评成绩 = 平时成绩的50% + 期末成绩的50%

<?php
#创建数据库连接对象
$mysqli = @new mysqli('localhost','root','12345678','school');
if($mysqli -> connect_error) {
    die('连接数据库失败：'.$mysqli -> connect_error);
}

#引入资源和使用读取资源操作
require("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\IOFactory;
#获取文件的保存路径和文件名
$fileName = './student.xlsx';
#从已经存在的文件中创建spreadsheet对象
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);

#获取execl文件中的第一张工作表对象
$workSheet = $spreadSheet -> getSheet(0);

#获取最大的行号（所有行），用于遍历取值
$rows = $workSheet -> getHighestRow();

#sql语句
$sql = "insert into score
        (stu_id,sc_normal,sc_lab,sc_midterm,sc_final,sc_overall) 
        values(?,?,?,?,?,?)";

#判断一下预处理失败的情况
if(!($mysqli_stmt = $mysqli -> prepare($sql))){
    die('预处理失败');
}

#获取A、D、E、F、G、H列单元格的值：分别对应数据库的学号、平时成绩、期中成绩、实验成绩、期末成绩、总评成绩
for($i=2;$i<$rows;$i++){//从第二行开始
    $stu_id = $workSheet -> getCell('A'.$i) -> getValue();
    $sc_normal = $workSheet -> getCell('D'.$i) -> getValue();
    $sc_lab = $workSheet -> getCell('E'.$i) -> getValue();
    $sc_midterm  = $workSheet -> getCell('F'.$i) -> getValue();
    $sc_final = $workSheet -> getCell('G'.$i) -> getValue();    
    $sc_overall = $sc_normal * 0.5 + $sc_final * 0.5; 
    
    #绑定参数
    $mysqli_stmt -> bind_param('iiiiii',$stu_id,$sc_normal,$sc_lab,$sc_midterm,$sc_final,$sc_overall);
    #执行预处理语句
    $mysqli_stmt -> execute();
    if ($mysqli_stmt -> affected_rows == 1) {
        #数据增加成功 
        $count ++;
    }
}
if($count > 0){
    #写入成功
    echo <<< end
    <script>
        alert("成功录入{$count}个学生成绩");
        location = "./index.php";
    </script>
end;
}else {
    #写入失败
    echo <<< end
    <script>
        alert("写入失败");
        location = "./index.php";
    </script>
end;
}
?>

多表查询将插入的数据显示到页面上

<?php
#创建数据库连接对象
$mysqli = @new mysqli('localhost','root','12345678','school');
if($mysqli -> connect_error) {
    die('连接数据库失败：'.$mysqli -> connect_error);
}
#sql语句
$sql = "select student.stu_id,stu_name,stu_class,sc_id,sc_normal,sc_lab,sc_midterm,sc_final,sc_overall   
        from student,score
        where student.stu_id = score.stu_id";
#准备预处理
$mysqli_stmt = $mysqli -> prepare($sql);
#绑定结果
$mysqli_stmt -> bind_result($stu_id,$stu_name,$stu_class,$sc_id,$sc_normal,$sc_lab,$sc_midterm,$sc_final,$sc_overall);
#执行预处理
$mysqli_stmt -> execute();
#获取所有结果集信息
$mysqli_stmt -> store_result();
#获取一条记录
// $mysqli_stmt -> fetch();
// echo $stu_class;
?>

<!DOCTYPE html>
<html lang="zh">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta http-equiv="X-UA-Compatible" content="ie=edge">
        <script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
        <title></title>
    </head>
    <body>
        <div id="app">
        <table border="1">
            <tr>
                <th v-for="(item,index) in titleList" :key="index">{{item}}</th>
            </tr>
<?php
         if($mysqli_stmt -> num_rows == 0){
            echo '没有获取到数据';
         }else {
            while ($mysqli_stmt -> fetch()) {
            echo <<< end
            <tr style="text-align: center;">
                <td>$sc_id</td>
                <td>$stu_id</td>
                <td>$stu_name</td>
                <td>$stu_class</td>
                <td>$sc_normal</td>
                <td>$sc_midterm</td>
                <td>$sc_lab</td>
                <td>$sc_final</td>
                <td>$sc_overall</td>
            </tr>   
end;
         }
    }
?>
        </table>
        </div>

        <script type="text/javascript">
            const app = new Vue({
                data() {
                    return {
                        title: '11',
                        titleList: ['序号','学号', '姓名', '班级', '平时成绩', '期中成绩', '实验成绩', '期末成绩', '总评成绩', ]
                    }
                }
            }).$mount("#app")
        </script>
    </body>
</html>

<?php 
#释放结果集
$mysqli_stmt -> free_result();
#关闭预处理语句
$mysqli_stmt -> close()
?>

php中如何使用的phpspreadsheet插件读取Execl

猜你喜欢

热点阅读