下载 phpspreadsheet
composer require phpoffice/phpspreadsheet
EXCEL导入导出封装类
<?php /** * Created by PhpStorm. * User: HOUDJ * Date: 2020/7/8 * Time: 10:04 */ namespace appcommonussiness; use PhpOfficePhpSpreadsheetSpreadsheet; use PhpOfficePhpSpreadsheetWriterXlsx; use PhpOfficePhpSpreadsheetIOFactory; class Excel { /** * Excel导出 * * @param array $datas 导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号'] * @param string $fileName 导出文件名称 * @param string $format 导出文件类型 * @param array $options 操作选项，例如： * bool print 设置打印格式 * string freezePane 锁定行数，例如表头为第一行，则锁定表头输入A2 * array setARGB 设置背景色，例如['A1', 'C1'] * array setWidth 设置宽度，例如['A' => 30, 'C' => 20] * bool setBorder 设置单元格边框 * array mergeCells 设置合并单元格，例如['A1:J1' => 'A1:J1'] * array formula 设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)'] * array format 设置格式，整列设置，例如['A' => 'General'] * array alignCenter 设置居中样式，例如['A1', 'A2'] * array bold 设置加粗样式，例如['A1', 'A2'] * string savePath 保存路径，设置后则文件保存到服务器，不通过浏览器下载 * @param array $options = [ * [ * 'column' =>'字段名', * 'name' => '列名', * 'width' => '列宽度', * 'height' => '行高', * 'color' => '颜色', * 'size' => '字体大小', * 'font' => '字体', * 'image' => '是否是图片', * 'border' => '边框', * 'dataType' => 'n数字 ', * ] * * ] * @return bool * user: stars<1014916675@qq.com> */ function exportSheelExcel($datas, $options = [], $fileName = '', $format = 'Xlsx',$type=0) { set_time_limit(0); //初始化 $spreadsheet = new Spreadsheet(); // $fileName = iconv('utf-8', 'gb2312', $fileName);//文件名称 //设置标题 $spreadsheet->getActiveSheet()->setTitle($fileName); $filename = $fileName.'_'.date('YmdHis'); $cellNum = count($options); /* 设置默认文字居中 */ $styleArray = [ 'alignment' => [ 'horizontal' => 'left', 'vertical' => 'left', ], ]; $spreadsheet->getDefaultStyle()->applyFromArray($styleArray); /* 设置Excel Sheet */ $spreadsheet->setActiveSheetIndex(0); $cellName = [ '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', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ' ]; $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', $fileName); //设置行高 $spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight(30); $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setName('Arial')->setSize(20);; //设置行高 $spreadsheet->getActiveSheet()->getRowDimension('A1')->setRowHeight(30); //合并单元格 $spreadsheet->getActiveSheet()->mergeCells('A1:'.$cellName[$cellNum - 1].'1'); //默认水平居中 $styleArray = [ 'alignment' => [ 'horizontal' => 'center', 'vertical' => 'center', ], ]; $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray); $color = [ 'Black' => 'FF000000', 'White' => 'FFFFFFFF', 'Red' => 'FFFF0000', 'Red1' => 'FF800000',//COLOR_DARKRED 'Green' => 'FF00FF00', 'Green1' => 'FF008000',//COLOR_DARKGREEN 'Blue' => 'FF0000FF', 'Blue1' => 'FF000080',//COLOR_DARKBLUE 'Yellow' => 'FFFFFF00', 'Yellow1' => 'FF808000',//COLOR_DARKYELLOW ]; //设置excel第2行数据 foreach ($options as $key => $val) { $column = $cellName[$key].'2'; //设置表头 $spreadsheet->setActiveSheetIndex(0) ->setCellValue($column, $val['name']); //设置列宽 if (isset($val['width']) && ! empty($val['width'])) { $spreadsheet->getActiveSheet()->getColumnDimension($cellName[$key])->setWidth($val['width']); } else { $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);//设置默认列宽为 } //设置字体 粗体 $spreadsheet->getActiveSheet()->getStyle($column)->getFont()->setBold(true); //设置行高 if ( ! empty($val['height'])) { $spreadsheet->getActiveSheet()->getRowDimension($column)->setRowHeight($val['height']); //设置默认行高 $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); } //设置颜色 if ( ! empty($val['color']) && isset($color[$val['color']])) { $spreadsheet->getActiveSheet()->getStyle($column)->getFont()->getColor()->setARGB($color[$val['color']]); } else { $spreadsheet->getActiveSheet()->getStyle($column)->getFont()->getColor()->setARGB('FF000000'); } } $yieldData = $this->yieldData($datas); $i = 0; foreach ($yieldData as $val) { for ($j = 0; $j < $cellNum; $j++) { //$spreadsheet->setActiveSheetIndex(0)->setCellValue($cellName[$j].($i+3),' '.$val[$options[$j]['column']].' '); //数据类型 $dataType = isset($options[$j]['dataType']) ? $options[$j]['dataType'] : 's'; switch ($dataType) { case 'n'://数字 $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; case 'str2num': $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; case 'str': $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; case 's': case 'inlineStr': $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; case 'null': $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; case 'f': default: $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($cellName[$j].($i + 3), $val[$options[$j]['column']], $dataType); break; } } $i++; } header('pragma:public'); if ($format == 'Xlsx') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } elseif ($format == 'Xls') { header('Content-Type: application/vnd.ms-excel'); }
// type等于1直接下载 if($type) { $objWriter = new Xlsx($spreadsheet); $objWriter->setPreCalculateFormulas(false); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment;filename=" . $filename . '.' . strtolower($format)); header('Cache-Control: max-age=0');//禁止缓存 header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header("Content-Transfer-Encoding:binary"); header("Expires: 0"); ob_clean(); ob_start(); $objWriter->save('php://output'); /* 释放内存 */ $spreadsheet->disconnectWorksheets(); unset($spreadsheet); ob_end_flush(); return true; }else{ $objWriter = IOFactory::createWriter($spreadsheet, $format); $rootPath=app()->getRootPath(); $savePath='/uploads'.'/exportExcel/'.$filename . '.' . strtolower($format); $a=$rootPath. 'public'.$savePath; $objWriter->save($a); /* 释放内存 */ $spreadsheet->disconnectWorksheets(); unset($spreadsheet); ob_end_flush(); return $savePath; } } function yieldData($data){ foreach ($data as $val){ yield $val; } } //导入 public function importExcel(){ set_time_limit(0); //文件上传导入 // $fileController=new FileController(); $res=self::uploadFileImport(); if($res['code']) { $data = $res['data']; //修正路径 $filename = 'uploads/' . str_replace('\', '/', $data); //进行读取 $spreadsheet = IOFactory::load($filename); $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); array_shift($sheetData); //删除标题; array_shift($sheetData); //删除标题; return $sheetData; }else{ return ''; } } //文件上传,导入文件专用,数据不入库 public function uploadFileImport(){ // 获取表单上传文件 $file = equest()->file('file'); $return = array('status' => 1, 'info' => '上传成功', 'data' => []); // 移动到框架应用根目录/public/uploads/ 目录下 if($file){ $savename = hinkfacadeFilesystem::disk('public')->putFile( 'importExcel', $file); return self::setResAr(1,'上传成功',$savename); } return self::setResAr(0,'上传失败'); } public function setResAr($code=0,$msg='',$data=array()){ return ['code'=>$code,'msg'=>$msg,'data'=>$data]; } }
导入：
/** * Notes:导入 * User: HOUDJ * Date: 2020/7/8 */ public function import(){ $excel = new Excel(); $data=$excel->importExcel();//这里已经获取到了上传文件中的数据行 if(count($data)){ $course=new CourseMode(); $flag=$course->importCourse($data); return returnMsg($flag['code'],$flag['data'],$flag['msg']); }else{ return returnMsg(0,'','导入数据为空'); } }
导出：
/** * Notes:导出 * User: HOUDJ * Date: 2020/7/8 */ public function export(){ $ids=input('param.ids'); $data=array(); $map=[]; if(!empty($ids)){ $idsArr=explode(',',$ids); $map[]=array('c.id','in',$idsArr); } $course=new CourseMode(); $lists =$course->getCourseLists($map, 1, 100000); //总数据 if($lists['total']){ foreach ($lists['data'] as $k=>$v){ $data[$k]['id']=$v['id']; $data[$k]['course_name']=$v['course_name']; $data[$k]['learn_style']=config('app.learn_style.'.$v['learn_style']); $data[$k]['duration']=$v['duration']; $data[$k]['teacher']=$v['teacher']; $data[$k]['cid']=appadminmodelCategory::where('id','=',$v['cid'])->value('name');; $data[$k]['knowledge']=is_array($v['knowledge'])?implode(',',$v['knowledge']):$v['knowledge']; $data[$k]['syllabus']=$v['syllabus']; } $filename = "云学院课程列表".date('YmdHis'); $header = array( array('column' => 'id', 'name' => '课程id', 'width' => 15), array('column' => 'course_name', 'name' => '课程名称', 'width' => 15), array('column' => 'learn_style', 'name' => '学习方式', 'width' => 30), array('column' => 'duration', 'name' => '课程时长', 'width' => 15), array('column' => 'teacher', 'name' => '授课老师', 'width' => 35), array('column' => 'cid', 'name' => '所属分类', 'width' => 15), array('column' => 'knowledge', 'name' => '所属知识体系', 'width' => 15), array('column' => 'syllabus', 'name' => '课程大纲', 'width' => 15), ); $excel=new Excel(); $download_url=$excel->exportSheelExcel($data,$header,$filename);//获取下载链接 if($download_url){ return returnMsg(1,config('app.web_url').$download_url,'导出成功'); }else{ return returnMsg(0,'','导出失败'); } } }