我们在项目时,经常需要去查数据库中的信息,导出excel。laravel提供了一个插件。
它有个github地址:https://github.com/Maatwebsite/Laravel-Excel
- 首先,我们先用composer下载:
composer require "maatwebsite/excel:~2.1.0"
- 然后,需要注册 一个服务提供者,在config/app.php到 providers数组中
- 然后,还是这个文件,在aliases数组中注册门面。
一般导出表格,表头分一维和多维如:
现在我们已经把插件下好了,做一个DEMO:
一维表头:
<?php
namespace App\Http\Controllers\ExportController;
use App\Http\Controllers\Controller;
use App\Model\ModelDatabase;
use config\SearchMessageConfig;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class ExcelController extends Controller
{
public function exportPatentExcel(Request $request){
$patent_cell_datas = SearchMessageConfig::PATENT_CELL_DATAS;//表头配置
$pa_id_datas = explode(',',$request->pa_id_datas);
$patent_table_name = SearchMessageConfig::PATENT_TABLE;
$patent_id_field = SearchMessageConfig::PATENT;
$patent_datas = ModelDatabase::selectExportExcelDatas($patent_table_name,$patent_id_field,$pa_id_datas);
foreach ($patent_datas as $datas){
++$this->serial_number;//序号
$patent_cell_datas[] = [
$this->serial_number,
$datas->patent_person,
$datas->first_inventor,
$datas->pa_all_author,
SearchMessageConfig::PA_TYPE_DATAS[$datas->pa_type],
$datas->pa_name,
SearchMessageConfig::PA_IMPLE_SITU_DATAS[$datas->pa_imple_situ],
$datas->author_num,
$datas->author_cert_num,
date($this->date_formate,$datas->author_notic_day/1000),
$datas->pa_integral,
$datas->pa_remarks
];
}
Excel::create('专利信息数据',function ($excel) use ($patent_cell_datas){
$excel->sheet('专利',function ($sheet) use ($patent_cell_datas){
$sheet->rows($patent_cell_datas);
});
})->export('xlsx');
return responseTojson(0,'导出成功');
}
}
上面的patent_cell_datas 实际上是下面的表头:
const PATENT_CELL_DATAS = [
['序号','专利权人','第一发明人','全部发明人','专利类型','专利名称','实施情况','授权编号或申请号','授权证书编号',
'授权公告日或受理日期','积分','备注']
];
多维表头:
<?php
namespace App\Http\Controllers\ExportController;
use App\Http\Controllers\Controller;
use App\Model\ModelDatabase;
use config\SearchMessageConfig;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class ExcelController extends Controller
{
//导出表格序号
private $serial_number = 0;
//设置老师所在部门单元格,累加器
private $count_table_list = 0;
private $date_formate = 'Y年-m月-d日';
//导出老师信息EXCEL表格
public function exportTeacherExcel(Request $request){
$teacher_information = [[],[],[],[],[],[]];
$department_count = [];
$teacher_cell_datas = SearchMessageConfig::TEACHER_CELL_DATAS;
$tea_id_datas = explode(',',$request->tea_id_datas);
$teacher_table_name = SearchMessageConfig::TEACHER_TABLE;
$teacher_id_field = SearchMessageConfig::TEACHER_ID;
$teacher_datas = ModelDatabase::selectExportExcelDatas($teacher_table_name,$teacher_id_field,$tea_id_datas);
foreach ($teacher_datas as $information){
switch ($information->teacher_department){
case 0:
array_push($teacher_information[0],$information);
break;
case 1:
array_push($teacher_information[1],$information);
break;
case 2:
array_push($teacher_information[2],$information);
break;
case 3:
array_push($teacher_information[3],$information);
break;
case 4:
array_push($teacher_information[4],$information);
break;
case 5:
array_push($teacher_information[5],$information);
break;
}
}
for ($i = 0; $i < count($teacher_information); $i++){
$count = count($teacher_information[$i]); //统计每个部门的老师个数是否大于1
if($count > 0){
array_push($department_count,$count);
for($j = 0; $j < count($teacher_information[$i]); $j++){
++$this->serial_number; //序号
$teacher_cell_datas[] = [
$this->serial_number,
SearchMessageConfig::TEACHER_DEPARTMENT[$teacher_information[$i][$j]->teacher_department],
$teacher_information[$i][$j]->name,
$teacher_information[$i][$j]->office_phone,
$teacher_information[$i][$j]->home_phone,
$teacher_information[$i][$j]->phone,
$teacher_information[$i][$j]->number,
];
}
}
}
$new_teacher_cell_datas = [$department_count,$teacher_cell_datas];
//设置导出表格的样式
Excel::create('老师信息',function ($excel) use ($new_teacher_cell_datas){
$excel->sheet('生命科技学院教工信息表',function ($sheet) use ($new_teacher_cell_datas){
$sheet->rows($new_teacher_cell_datas[1]);
for($i = 0; $i < 40;$i++){
if($i < 27 || ($i > 34 && $i < 38)){
//前26个表格单元格格式,合并每一列的前两行
$table_list = intToChr($i);
$sheet->mergeCells($table_list.'1:'.$table_list.'2');
}elseif ($i == 27){
//设置老师的第一学历和最高学历单元格格式
$sheet->mergeCells(intToChr($i).'1:'.intToChr($i + 3).'1');
$sheet->mergeCells(intToChr($i + 4).'1:'.intToChr($i + 7).'1');
$i += 7; //跳格
}elseif($i == 38){
//设置老师的硕(博)导的单元格格式
$sheet->mergeCells(intToChr($i).'1:'.intToChr($i + 1).'1');
}
}
//设置老师所在部门单元格格式
for($k = 0; $k < count($new_teacher_cell_datas[0]); $k++){
if($k != 0){
$sheet->mergeCells('B'.$this->count_table_list.':B'.($this->count_table_list + $new_teacher_cell_datas[0][$k] - 1));
}else{
$sheet->mergeCells('B3:B'.($new_teacher_cell_datas[0][$k] + 2));
$new_teacher_cell_datas[0][$k] = $new_teacher_cell_datas[0][$k] + 3;//第一次比较特殊,需要 + 3
}
$this->count_table_list += $new_teacher_cell_datas[0][$k]; //从下一行开始,设置单元格格式
}
});
})->export('xlsx');
return responseTojson(0,'导出成功');
}
}
上面的TEACHER_CELL_DATAS 实际上是下面的多维表头:中间有很多空的东西,主要是为了占位,让他形成二维的。
const TEACHER_CELL_DATAS = [
['序号','分组','姓名','办公电话','住宅电话','手机','编号','性别','民族','出生年月','政治面貌','籍贯',
'行政职务','任职时间','职务级别','专业技术职务','职称','评审通过时间','聘任时间','系列',
'所在单位','所属教研室和实验室','来校工作时间','原工作单位','教师资格证书编号','身份证号','老师毕业院校','第一学历','','','',
'最高学历学位','','','','现从事专业','所属学科','任教课程','硕(博)导'],
['','','','','','','','','','','','','','','','','','','','','','','','','','','','学历/学位','毕业院校','所学专业',
'毕业时间','学历/学位','毕业院校','所学专业','毕业时间','','','',
'授予单位','获得时间']
];
还有再导出前,把数据做一下分组,主要是为了实现,导出的老师是刚好分好组的,按照部门顺序导出。如:
上面的intToChr(),这个函数是专门为了转化excel序列号写的一个函数:
//导出EXCEL表格,单元格设置,数字转excel字母
function intToChr($index, $start = 65) {
$str = '';
if (floor($index / 26) > 0) {
$str .= intToChr(floor($index / 26)-1);
}
return $str . chr($index % 26 + $start);
}