thinkphp6使用PhpOffice导入导出excel1.导入2.导出

75 阅读 0 评论 50 点赞

我是靠谱客的博主魁梧冥王星，最近开发中收集的这篇文章主要介绍thinkphp6使用PhpOffice导入导出excel1.导入2.导出，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

项目后台使用Thinkphp6+layuiAdmin，经常用到导入导出excel的功能，记录一下。

1.导入

html模板部分


//执行实例
var uploadInst = upload.render({
elem: '#put' //绑定元素
//将excel上传接口到服务器拿到地址 /public/excel/32424324.xls
,url: '{:url("Base/uploadFile")}?type=excel'
,accept:'file'
,done: function(res){
layer.msg('上传中，请稍等', {icon: 6, time: 1000}, function () {
if(res.code==200) {
path = res.data.path;
//window.location.href = "{:url('two.Sch/fromExcel')}?filePath=" + path;//注释掉直接跳转形式，使用下面的json走接口形式
$.post('{:url("two.Sch/fromExcel")}', {path:path}, function (r) {
if (r.code == 200) {
layer.msg(r.msg, {icon: 6, time: 1000}, function () {
parent.location.reload();
})
} else {
layer.msg(r.msg)
}
}, 'json');
}else{
layer.msg(res.msg)
}
})
//上传完毕回调
}
,error: function(){
//请求异常回调
}
});

代码部分

1.上传文件

 public function uploadFile(Request $request)
{
$type = $request->param('type','others');
$file = $request->file("file");
if($type=='excel'){
if($file->extension()!='xlsx'){
output_error('请使用模板导入');
}
}
$path = "/public/storage/";
$save_name = thinkfacadeFilesystem::disk('public')->putFile('file/'.$type, $file);
return output_data(["path" => $path . $save_name]);
}

2.导入文件


public function fromExcel(){
//output_success('保存成功');
$path = $this->request->param('path','public/excel/导入模板.xlsx');
$base=[
['category_name','类型'],
['name','店铺名'],
['business_license','营业执照'],
['management_id','经营许可证'],
];
$data = $this->baseFromExcel($base,$path,['flag'=>'序号','mustField'=>'A']);//
SchModel::saveAll($data);
output_success('导入成功');
}

3.excel解析类


/**
* @param $filePath
* @return array
* @date 2022/3/2 11:29
* @author
* @desc 导入excel之读取excel
* params['mustField']行的某列为空则跳过
* params['flag']开始工作的行（excel没有大标题，则该参数没用）
*/
public function baseFromExcel($base,$path,$params=[]){
$abc=[
'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'
];
$baseNew=[];
foreach($base as $k => $v){
$baseNew[$abc[$k]]=$v;
}
$base=$baseNew;
$path = ltrim($path,'/');
$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load($path);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
$data=[];
//array_pop($sheetData);//去掉最后一个空的
//$flag=0;
foreach($sheetData as $k => $v){
//if(!$flag) {
//if ($v['A'] == $params['flag']) {
//$flag = 1;
//continue;
//}
//}
//if(!$flag) continue;
if(empty($v[$params['mustField']])) continue;
$temp=[];
foreach($v as $k2 => $v2){
if(!isset($base[$k2][0])) continue;
$temp[$base[$k2][0]]=$v2;
}
$data[]=$temp;
}
return $data;
}

2.导出

1.导出文件


public function toExcel()
{
$post = $this->request->param();
//通过筛选条件拿到一个二维数组，格式[{"id":1,"name":"哈哈"},{"id":2,"name":"嘻嘻"}](json格式只是为演示)
$data = $this->getList($post, []);
$base = [
['category_name','类型'],
['name','店铺名'],
['business_license','营业执照'],
['management_id','经营许可证'],
['sanitation_permit','卫生许可证号'],
['address','经营地址'],
['sheet','归属路'],
];
foreach($data['data'] as &$v){
if($v['status']==1) $v['update_time']='';
}
$this->excelBase($base, $data['data']);
}

2.excel解析类


/**
* @param $header
* @param $body
* @param $controller
* @throws PhpOfficePhpSpreadsheetWriterException
* @date 2022/6/7 11:23
* @author xuke
* @desc 导出excel
*/
public function excelBase($header, $body, $controller, $params = [])
{
$abc = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ'
];
$spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
// 设置个表格宽度
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(30);
$sheet->mergeCells('A1:' . $abc[count($header) - 1] . '1');
$sheet->setCellValue('A' . '1', $title)->getStyle('A1')->getFont()->setBold(true)->setSize(15);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
//
//参数名
$order = 0;
$j = [];
foreach ($header as $v) {
$sheet->setCellValue($abc[$order] . '2', $v)->getStyle($abc[$order] . '2')->getFont()->setBold(true)->setSize(15);;
//if(strstr($v,'手机')){//增加宽度防止变16进制
$spreadsheet->getActiveSheet()->getColumnDimension($abc[$order])->setWidth(16);
//}
$j[] = [$abc[$order] . '1', $v];
$order++;
}
//主体数据
$orderMain = 3;
if (isset($body['list'])) $body = $body['list'];
foreach ($body as $value) {
$order = 0;
foreach ($header as $k => $v) {
if (!strstr($k, '#')) {
$valueResult = $value[$k];
} else {//数组
$temp = explode('#', $k);
$valueResult = $value[$temp[0]][$temp[1]] ?? '';
}
$sheet->setCellValue($abc[$order] . $orderMain, $valueResult);
$j[] = [$abc[$order] . $orderMain, $valueResult];
$order++;
}
$orderMain++;
}
# Xlsx类 将电子表格保存到文件
$writer = new PhpOfficePhpSpreadsheetWriterXlsx($spreadsheet);
$filename = '文件名' . '列表' . time() . '.xlsx';
$path = '/public/excel/' . $filename;
$writer->save(root_path() . $path);
//保存到download表
$//data = [
//'filename' => $filename,
//'username' => $this->request->user['name'],
//'times' => 0,
//'type' => $controller,
//'path' => $path,
//'source' => $source,
//];
//DownloadLogModel::create($data);
//
output_success('', ['path' => $path]);
}