使用 PhpSpreadsheet 导入 Excel 中的信息、导出数据库中的信息到 Excel

关于Excel数据管理工具

Excel是一种优秀的数据管理工具，如果能够发掘出Excel的全部潜力，它的功能与完成的编程语言并无二致。然而，大多数人仅限于以表格的形式使用Excel，这些高手也只能使用SUM函数自动汇总数据。

若是能将CMS系统中的数据导出到Excel，或者将Excel中的数据导入到CMS系统中，我们就可以将两者连接起来进行更加方便的数据管理。今天，我将向大家介绍如何使用 maatwebsite/excel工具在使用Laravel开发的CMS系统中实现数据导入导出的功能。在WordPress中的实现也是类似的，只需对下面的代码稍作调整。

首先，安装 maatwebsite/excel库

只需运行以下代码，就可以让Composer将 maatwebsite/excel安装到你的主题或插件中。

composer require maatwebsite/excel

实现信息导入功能

有些数据可能原本就存在于Excel工作簿中，而逐条手动输入到CMS系统中的工作量较大，且较为琐碎。既然我们可以通过程序导入这些数据，为什么不这么做呢？

导入数据的实现并不难，基本的流程既是读取Excel的数据，再将其逐条写入数据库中。然而，它并不是一种高效的处理方法，我们可以优化使用下面的代码，将数据批量写入数据库中，以提高性能：

try{
$spreadsheet=\PhpOffice\PhpSpreadsheet\IOFactory::load($info_student);

//$cells是包含Excel表格数据的数组
foreach($spreadsheet->getWorksheetIterator()as$cell){
$cells=$cell->toArray();
}

//去掉表头
unset($cells[0]);

foreach($cellsas$cell){

$reg_no=$cell[2];
$password=str_random(8);
//添加或更新数据
$student=StudentRegister::query()->firstOrCreate([
'form_id'=>$id,
'school_id'=>$logged_user->school_id,
'reg_no'=>$reg_no,
]);

//添加对应的数据到报名信息
$student->name=$cell[0];
$student->gender=$cell[1];
$student->ex_school=$cell[3];
$student->save();
}

}catch(\Exception$e){

return Redirect::back()->withErrors('导入学生信息失败。');
}

实现信息导出功能

导出功能的实现同样简单，首先获取需要导出的数据，然后循环把这些数据放入的工作表中，最后写入数据到文件中，提供给用户下载就可以了。

在这里有一个性能上的可用性问题：若数据量非常大，实现时需要注意测试是否会因为执行时间过长而导致导出失败。如果会的话，就需要将数据分批次导出，另外，在Laravel中有相当简单的方法可以实现此功能，相信熟悉Laravel的朋友都知道，在此我们就不再详述了。

$students=StudentRegister::query()
->where('school_id',$logged_user->school_id)
->where('form_id',$id)
->get();

try{

$spreadsheet=new\PhpOffice\PhpSpreadsheet\Spreadsheet();

$worksheet=$spreadsheet->getActiveSheet();

$worksheet->getCell('A1')->setValue('姓名');
$worksheet->getCell('B1')->setValue('学号');
$worksheet->getCell('C1')->setValue('验证码');

$i=2;
foreach($studentsas$student){
$worksheet->getCell('A'.$i)->setValue($student->name);
$worksheet->getCell('B'.$i)->setValue($student->reg_no);
$worksheet->getCell('C'.$i)->setValue($student->password);

$i++;
}

$filename='validate-code-'.date('YmdHis').".xlsx";
$writer=\PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,"Xlsx");

header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="'.$filename.'"');
$writer->save("php://output");

returnredirect('/admin/student_register/'.$id.'/edit')
->with('success',['导出成功']);

}catch(\Exception$e){
returnredirect('/admin/student_register/'.$id.'/edit')
->with('success',$e->getMessage());
}

自动计算列数

因为上面的数据较少，我们可以直接将列数“A1，B1”硬编码在代码中。不过，若有几十个数据列，像上面那样硬编码会导致效率低下、容易出错。我们可以使用一个简单的函数自动获取数据列名称，这时就只需根据列数访问数组值即可获得数据列名称。下面的函数可以获取指定数量的数据列名称为一个数组：

excel_header($num=0)
{
$arr=range('A','Z');

$no=ceil($num/count($arr));

$data=[];

if($no<=1){
for($i=0;$i<$num;$i++){
$data[]=$arr[$i];
}
}else{
for($i=0;$i<count($arr);$i++){
$data[]=$arr[$i];
}
for($i=0;$i<$num-count($arr);$i++){
$list=(($i+count($arr))%count($arr));
$data[]=$arr[ceil(($i+1)/count($arr))-1].$arr[$list];
}
}

return$data;
}

虽然这个代码是从使用Laravel开发的系统中摘抄出来的，但是含有Laravel框架的部分只须根据WordPress函数相应地进行修改，即可在WordPress中使用。

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。