第一百零一次闯荡江湖的无名游侠 - 2017年10月
下载PHPExcel；地址https://github.com/PHPOffice/PHPExcel代码如下 <?php namespace app\index\controller; use think\Controller; use think\Request; use think\Loader; use think\Db; class Index extends Controller { public function index() { return $this->fetch('/index'); } //上传EXECL文件并用表格显示在网页上 public function up(Request $request) { Loader::import('PHPExcel.Classes.PHPExcel');//加载PHPExcel //PHPExcel放在目录extend下，PHPExcel.Classes.PHPExcel就相当于 //PHPExcel/Classes/PHPExcel; Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5'); $file = $request->file('file');//获取表单上传文件 $info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads');//上传验证后缀名,以及上传之后移动的地址 if ($info) { $file_name = $info->getRealPath(); //获取文件地址 if($info->getExtension()=='xls') { $objReader = \PHPExcel_IOFactory::createReader('Excel5');//支持xls后缀 }else { $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//支持xlsx后缀 } $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8 $objReader->setReadDataOnly(true); $objWorksheet = $objPHPExcel->getActiveSheet();//获得当前活动状态的工作表，返回工作表对象 echo ' ' . "\n"; foreach ($objWorksheet->getRowIterator() as $row) { echo ' ' . "\n"; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); foreach ($cellIterator as $cell) { echo ' ' . $cell->getValue() . ' ' . "\n";//得到值 } echo ' ' . "\n"; } echo ' ' . "\n"; //删除上传的文件 //unlink($file_name); die; //表格数据插入数据库 $excel_array = $objPHPExcel->getsheet(0)->toArray(); //转换为数组格式 //array_shift($excel_array); //删除第一个数组(标题); $city = []; foreach($excel_array as $k=>$v) { $city[$k]['row1'] = $v[0]; $city[$k]['row2'] = $v[1]; $city[$k]['row3'] = $v[2]; $city[$k]['row4'] = $v[3]; $city[$k]['row5'] = $v[4]; } Db::table('execl')->insertAll($city); //批量插入数据 } else { echo $file->getError(); } } //显示数据库表格信息 public function table(){ $data=Db::table('execl')->select(); //dump($data);die; $this->assign('data',$data); return $this->fetch('/table'); } //数据库导出到excel表文件，xls格式 public function dotable(){ $data=Db::table('execl')->select(); Loader::import('PHPExcel.Classes.PHPExcel');//加载PHPExcel Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5'); //对数据进行检验 if(empty($data)||!is_array($data)){ die("data must be a array"); } $date=time(); $fileName="{$date}.xls"; //创建PHPExcel对象，注意，不能少了 $objPHPExcel=new \PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->setCellValue('A1','ID'); $objPHPExcel->getActiveSheet()->setCellValue('B1','姓名'); $objPHPExcel->getActiveSheet()->setCellValue('C1','款号'); $objPHPExcel->getActiveSheet()->setCellValue('D1','件数'); $objPHPExcel->getActiveSheet()->setCellValue('E1','单价'); $i = 2; foreach($data as $r){ $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$r['row1']); $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$r['row2']); $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$r['row3']); $objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$r['row4']); $objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$r['row5']); $i++; } $objPHPExcel->getActiveSheet()->setTitle('表格'); $fileName = iconv("utf-8", "gb2312", $fileName); //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件 header("Content-Disposition: attachment;filename=\"$fileName\"");//告诉浏览器将输出文件的名称(文件下载) header('Cache-Control: max-age=0');//禁止缓存 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output');//告诉浏览器下载 } }
解决方案：增加一个隐藏的表单域，包含一个一次性token，将会在个token储存在用户会话中。 <?php session_start(); $_SESSION['token']=md5(uniqid(mt_rand(),true)); ?> <input type="hidden" name="token" value="<?php echo $_SESSION['token']; ?>"> 名称: 数量: 接收到一个表示表单提交的请求时，检查token，确保它们是匹配的：<?php session_start(); if((!isset($_SESSION['token'])) || ($_POST['token'] != $_SESSION['token'])){ //提示输入密码 }else{ //继续 } ?>