thinkphp6 phpspreadsheet导入+导出xls包含多图
先在本项目里导入
composer require phpoffice/phpspreadsheet
点击查看代码公用类
<?php
#设置你对应的命名空间
namespace app\common\service;
use think\App;
use PHPExcel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Request;
class ExportService
{
// 导出数据
public $data = [];
// 表头
public $arrHeader;
// 导出标题
public $title = "项目汇总";
/**
* 导出Excel方法
*/
public function export()
{
$arr = $this->data;//导出的数据
if(!$arr){
return false;
}
//实例化
$objExcel = new Spreadsheet();
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
//设置文档属性
$objWriter = IOFactory::createWriter($objExcel, "Xls");
// 定义字母表头
$letter = explode(',', "A,B,C,D,E,F,G,H,I,J,K,L,M,N");
//设置表头
$arrHeader = $this->arrHeader;
// 设置标题
$objActSheet->setTitle($this->title);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
foreach($arrHeader as $hk=>$hv){
$objActSheet->setCellValue("$letter[$hk]1", $hv['title']);
foreach ($arr as $k => $v){
$jk = $k;
$k += 2;
if(isset($hv['url'])){
if($v[$hv['field']]){
$objActSheet->setCellValue($letter[$hk] . $k, request()->domain().$v[$hv['field']]);
}
}else if(isset($hv['mulfield'])){
$objActSheet->setCellValue($letter[$hk] . $k, $v[$hv['field']]['title']);
}else if(isset($hv['is_img'])){
if($v[$hv['field']]){
$num = 10;
foreach ($v[$hv['field']] as $k1=>$v1){
if($v1 != ""){
$objDrawing[$k] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$objDrawing[$k]->setName('');
$objDrawing[$k]->setDescription('');
//读取图片路径
$objDrawing[$k]->setPath(request()->domain().$v1);
$objDrawing[$k]->setWidth(80);
$objDrawing[$k]->setHeight(80);
$objDrawing[$k]->setCoordinates($letter[$hk] . $k);
$objDrawing[$k]->setOffsetX($num);
$objDrawing[$k]->setOffsetY(20);
$objDrawing[$k]->setWorksheet($objActSheet);
$objActSheet->getRowDimension($k)->setRowHeight(100);
$num = $num + 70; // 增加每张图之间的间距
}
}
}
}else{
//设置表格内容,这里如果是多链表可以用递归，我文章有个递归用法比如test.a.b.c里的值现在只有test.
if (strpos($hv['field'], '.') !== false) {
// 拆分字段字符串为数组
$fields = explode('.', $hv['field']);
if (isset($v[$fields[0]][$fields[1]])) {
$objActSheet->setCellValue($letter[$hk] . $k, $v[$fields[0]][$fields[1]]);
}
}else{
$objActSheet->setCellValue($letter[$hk] . $k, $v[$hv['field']]);
}
}
//设置表格的宽度
$objActSheet->getColumnDimension($letter[$hk])->setWidth(20);
}
}
$outfile = $this->title . date("Y-m-d",time()) . ".xls";
// 清空输出缓冲区
ob_end_clean();
// 告诉浏览器强制下载
header("Content-Type: application/force-download");
// 二进制文件类型
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
// 设置表名
header('Content-Disposition:inline;filename="' . $outfile . '"');
header("Content-Transfer-Encoding: binary");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
exit();
}//function end
/**
* 导入Excel方法
*/
public function setImportList(){
//获取文件信息
$file = request()->file("file");
//获取文件临时地址->这里可以先保存到服务器或者本地的文件再导入删除文件
$filePath = $file->getPathName();
//测试文件
// $filePath = "./storage/test/测试1662604258.xls";
if (empty($filePath) OR !file_exists($filePath)) {
return json(['status'=>0,"info"=>"临时文件过期或者不存在！"]);
}
//读取xls文件信息
$objReader = IOFactory::createReader('Xls');
if (!$objReader->canRead($filePath)) {
return json(['status'=>0,"info"=>"只支持导入Xls文件！"]);
}
//加载文件
$spreadsheet = IOFactory::load($filePath);
//获得当前活动状态的工作表,返回工作表对象
$sheet = $spreadsheet->getActiveSheet();
//转换数组
$data = $sheet->toArray();
//大于2条才执行，第一条是表头
if(!$data || count($data)<2){
return false;
}
//获取需要导入的字段信息
$arrHeader = $this->arrHeader;
//删除数组中的第一个元素，并返回被删除元素的值。
$keys = array_shift($data);
/**
* 设置文件数组第一个数组作为后面数据的键，然后用键取得自定义的字段
* array_map() 函数将用户自定义函数作用到数组中的每个值上，并返回用户自定义函数作用后的带有新的值的数组。
*/
$result = array_map(function ($values) use ($keys) {
return array_combine($keys, $values);//过合并两个数组来创建一个新数组，其中的一个数组元素为键名，另一个数组元素为键值：
}, $data);
// $key = $data[0];
// $newArray = [];
// unset($data[0]);
// foreach ($data as $kk => $vv) {
// foreach ($vv as $k => $v) {
// $newArray[$kk][$key[$k]] = $v;
// }
// }
$field = [];
foreach ($arrHeader as $hk => $hv){
foreach($result as $k => $v){
if(isset($v[$hv['title']])){
$field[$k][$hv['field']] = $v[$hv['title']];
}
}
}
return $field;
}
}
调用导出导入公用类
<?php
#在你需要的控制器里实例化
namespace app\index\controller;
use app\common\service\ExportService;
class Index
{
/**
* @导出项目总汇
*/
public function Export(){
// 实例化
$exproservice = (new ExportService());
// 设置表头信息 mulfield是否存在多字段比如：status.title ，is_img是否是图片（数组），url是否是链接，补全链接
$arrHeader = [
['field'=>"id","title"=>"No."],
['field'=>"status","title"=>"状态","mulfield"=>true],
['field'=>"title","title"=>"问题"],
['field'=>"pics","title"=>"插图",'is_img'=>true],
['field'=>"video","title"=>"视频","url"=>true],
['field'=>"desc","title"=>"描述"],
['field'=>"create_time","title"=>"Date"],
['field'=>"content.desc","title"=>"内容"],
];
$exproservice->arrHeader = $arrHeader;
//设置数据
$exproservice->data = $this->getExportList($exproservice);
if(!$exproservice->export()){
abort(404, '数据为空');
}
}
/**
* @获取需要导出的数据
* $exproservice 实例化导出对象
*/
public function getExportList($exproservice){
//测试数据（这里就可以查询你需要的数据库，需要对应设置的arrHeader头）
$arr = [
[
'id'=>1,
'status'=>['id'=>'1','title'=>'完成'],
'title'=>'标题1',
'pics'=>['./test/test.png','./test/test1.png'],
'video'=>"./test/test.mp4",
'desc' => "描述描述",
"create_time" => "2022-09-21"
],
];
if(!$arr){
// 设置导出文件标题
$exproservice->title = '导出文件标题';
return $arr;
}
return [];
}
/**
* @导入汇总列表
*/
public function Import(){
// 实例化
$exproservice = (new ExportService());
// 设置表头 title对应你xls文件第一个表头，后面就接着数据
$arrHeader = [
['field'=>"id","title"=>"No."],
['field'=>"status","title"=>"状态","mulfield"=>true],
['field'=>"urgent","title"=>"严重程度","mulfield"=>true],
['field'=>"proposal","title"=>"建议"],
['field'=>"cuatomer","title"=>"客户意见"],
['field'=>"other","title"=>"其他"],
];
//设置所需要导入的字段
$exproservice->arrHeader = $arrHeader;
//读取信息
$data = $exproservice->setImportList();
//到这一步就是添加信息到数据库了
}
}
