官方文档：
https://docs.laravel-excel.com/3.1/getting-started/
Excel导入：
composer安装maatwebsite/excel包
composer require maatwebsite/excel创建导入类
php artisan make:import FilesInfoImport打开文件FilesInfoImport，按照以下格式写入
控制器方法
public function filesInfoImport(Request $request)
{
$c = 0;
if ($request->hasFile('file')) {
$file = $request->file('file')->store('/excel');
$tmp_file = 'storage/' . $file;
$data = (new FilesInfoImport())->toArray($tmp_file);
if (!empty($data)) {
$arr0 = array_shift($data[0]);//删除Excel第一行[标题]
$arr = $data[0];
foreach ($arr as $rows) {
$item = FilesInfo::create([
'number' => $rows[0],
'filetype' => $rows[1],
'name' => $rows[2],
//.......所有字段
]);
if ($item) {
$c++;
} else {
exit(json_encode(array('code'=>1, 'msg'=>"部分数据上传失败，已上传".$c."条！")));
}
}
exit(json_encode(array('code'=>1, 'msg'=>'全部提交成功！')));
}
else{
exit(json_encode(array('code'=>0, 'msg'=>'无文件上传！')));
}
}
else
{
exit(json_encode(array('code'=>0, 'msg'=>'请选择上传文件！')));
}
}html(该前端框架使用的layui)
js
$('#import_btn').on('click', function () {
var type = $(this).data('type');
active[type] ? active[type].call(this) : '';
});
upload.render({
elem: '#import_btn'
,url: '/admin/filesInfoImport' //此处配置你自己的上传接口即可
,accept: 'file' //普通文件
,field: "file"
,exts:'xls|xlsx|csv' //允许上传的类型
,before: function(obj){ //obj参数包含的信息，跟 choose回调完全一致，可参见上文。
layui.layer.load();
this.data={
//传给后台的参数
}
}
,done: function(res){
if(res.code==1)
{
layui.layer.closeAll();
return layer.msg(res.msg);
}
else
{
layui.layer.closeAll();
return layer.msg(res.msg);
}
},
error: function(msg){
//请求异常回调
layui.layer.closeAll();
return layer.msg('上传失败,请重新上传');
}
});注意：
如果导入后，无法访问该文件，是访问地址的问题？？
1.需要建立软连接命令：php artisan storage:link
2.如果建立软连接后依旧访问有问题，是因为软连接指定的目录与实际上传的目录不匹配，需要修改上传文件的指定目录
config/filesystems.php下的
'root' => storage_path('app'),改为'root' => storage_path('app/public'),
本地能正常上传，线上服务器上传失败
报错：
ErrorException: touch(): Unable to create file
/data/www/***/***/storage/framework/laravel-excel/laravel-excel-
nTTU18NFIgWFB1tGe6nn8RLlICp8U6aj.xls because Permission denied in file
/data/www/***/***/vendor/maatwebsite/excel/src/Files/LocalTemporaryFile.php on line需要在服务器项目文件夹内运行：sudo chmod -R 777 ***（文件地址）
给storage文件夹设置777权限
Excel导出：
html
js
$('#export_btn').on('click', function () {
window.open("/admin/zdjbExportZf?year=" + $("#year").val()+ '&IDCard='+$(".search_IDCard").val())
});控制器
public function zdjbExportZf(Request $request)
{
ini_set('max_execution_time', 60000);
$key = $request->request->all();
$main = new Zdjb();
$where = [];
//查询条件
if ($key) {
foreach ($key as $k => $v) {
if ($v!=null&&$v!="") {
switch ($k) {
case 'year':
array_push($where, ['year', $v]);
break;
}
}
}
}
//根据条件查询sql数据
$res = $main->where($where)->get()->toArray();
//遍历sql数据
foreach ($res as $k => &$v) {
$v['index'] = $k+1;
//$v['IDCard'] = $v['IDCard'] . ' ';//身份z号加空格，excel不会显示###
//如果直接取字段的表内获取的值，在$field直接写字段名即可，比如以上身份z需要转化加空格，需要再次遍历一次，无特殊条件，无需遍历
}
$head = [
'序号',
'编号',
'单位名称',
'姓名',
'身份z号',
'开户名',
'银行账号',
'开户银行',
'联系电话',
'补偿金额（元）',
'结算日期'
//...
];
$field = [
'index',
'字段名称1',
'字段名称2'
'字段名称3'
'字段名称4'
'字段名称5'
'字段名称6'
'字段名称7'
'字段名称8'
'字段名称9'
'字段名称10'
//...
];//字段名
$body = $res;//内容
$main->daochu('导出表', $head, $field, $body,$time);//调用导出的方法
}调用的导出方法（导出时可设置样式）
public function daochu(string $title, array $head, array $field, array $body, array $time) //字段一一对应
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称--最下角的sheet名称
$worksheet->setTitle('文档');
$worksheet->setCellValueByColumnAndRow(1,1,$title);//传来的表格标题
$worksheet->mergeCellsByColumnAndRow(1,1,11,1);//合并单元格
$worksheet->setCellValueByColumnAndRow(1,2,'统计日期：2022-05-20 至 2022-05-21');
$worksheet->mergeCellsByColumnAndRow(1,2,11,2);//合并统计日期的单元格
$worksheet->setCellValueByColumnAndRow(4,3,'人员信息');
$worksheet->mergeCellsByColumnAndRow(4,3,9,3);
//设置头部格式
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(33.95);
$worksheet->getStyle('A1:K1')->getFont()->setBold(true)->setSize(18);
$spreadsheet->getActiveSheet()->getStyle('A2:K2')->getFont()->setName('Arial')
->setSize(9);
//设置尾部格式
$spreadsheet->getActiveSheet()->getRowDimension(count($body)+5)->setRowHeight(26.25);
$worksheet->setCellValueByColumnAndRow(9,(count($body)+5),'合计:');
$spreadsheet->getActiveSheet()->setCellValue('J'.(count($body)+5), "=SUM(J5:J".(count($body)+4).")");
$worksheet->mergeCellsByColumnAndRow(1,(count($body)+6),11,(count($body)+6));
$worksheet->mergeCellsByColumnAndRow(1,(count($body)+7),11,(count($body)+7));
$total = count($body) + 8;
$worksheet->mergeCellsByColumnAndRow(1,$total,3,$total);
$worksheet->setCellValueByColumnAndRow(1,$total,'初审人');
$worksheet->mergeCellsByColumnAndRow(4,$total,6,$total);
$worksheet->setCellValueByColumnAndRow(4,$total,'复核人');
$worksheet->mergeCellsByColumnAndRow(7,$total,8,$total);
$worksheet->setCellValueByColumnAndRow(7,$total,'XX签字');
$worksheet->mergeCellsByColumnAndRow(9,$total,11,$total);
$worksheet->setCellValueByColumnAndRow(9,$total,'XX签字');
$worksheet->mergeCellsByColumnAndRow(1,$total+1,3,$total+1);
$worksheet->mergeCellsByColumnAndRow(4,$total+1,6,$total+1);
$worksheet->mergeCellsByColumnAndRow(7,$total+1,8,$total+1);
$worksheet->mergeCellsByColumnAndRow(9,$total+1,11,$total+1);
$worksheet->setCellValueByColumnAndRow(1,$total+1,'年 月 日');
$worksheet->setCellValueByColumnAndRow(4,$total+1,'年 月 日');
$worksheet->setCellValueByColumnAndRow(7,$total+1,'年 月 日');
$worksheet->setCellValueByColumnAndRow(9,$total+1,'年 月 日');
$spreadsheet->getActiveSheet()->getRowDimension($total)->setRowHeight(26.25);
$spreadsheet->getActiveSheet()->getRowDimension($total+1)->setRowHeight(81.75);
$spreadsheet->getActiveSheet()->getRowDimension($total+2)->setRowHeight(17.1);
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER //垂直居中
],
];
$worksheet->getStyle('J3')->getAlignment()->setWrapText(true);
$worksheet->getStyle('C')->getAlignment()->setWrapText(true);
$worksheet->getStyle('J')->getAlignment()->setWrapText(true);
$worksheet->getStyle('A1')->applyFromArray($styleArray);
$worksheet->getStyle('A3:K'.($total))->applyFromArray($styleArray);
$styleArray1 = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
],
];
$worksheet->getStyle('A'.($total+1).':K'.($total+2))->applyFromArray($styleArray1);
$worksheet->getStyle('A3:K'.($total+2))->getFont()->setSize(9);
$worksheet->mergeCellsByColumnAndRow(1,$total+2,3,$total+2);
$worksheet->setCellValueByColumnAndRow(1,$total+2,'打印日期: ' . date('Y-m-d'));
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(4.33);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.57);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(25.3);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(7.03);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(16.33);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(7.17);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(18.67);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(14.33);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10.5);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(9.67);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(8.83);
$styleArray2 = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => ' 0xFF000000'],
],
],
];
$worksheet->getStyle('A3:K'.($total-3))->applyFromArray($styleArray2);
$worksheet->getStyle('A'.($total).':K'.($total+1))->applyFromArray($styleArray2);
//表头
//设置单元格内容
foreach ($head as $k => $v) {
$col = $k + 1;
if($k>=3&&$k<=8){
$row = 4;
}else{
$row = 3;
$worksheet->mergeCellsByColumnAndRow($col,3,$col,4);
}
$worksheet->setCellValueByColumnAndRow($col, $row, $v);
}
$len = 5;
$j = 0;
foreach ($body as $k => $v) {
$row = $k + 5;
for ($n = 0; $n < count($field); $n++) {
$col = $n + 1;
$val = $v[$field[$n]];
$worksheet->setCellValueByColumnAndRow($col, $row, $val);
}
$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(25);
}
$filename = "{$title}.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}导出样式如下：
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）