phpexcel的上传和下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
	//处理上传的文件数据
	public function doUpload()
	{
		
		$data = input();
		$file = request()->file('excel');
		//dump($data);
		
		//考试批次
		$batch = $data['batch'];
		
		//班级
		$class = $data['class'];
		
		//科目
		$subject = $data['subject'];
		
		$info =  $file->validate(['size'=>1048576,'ext'=>'xlsx,xls'])->move('/public/upload/excel');
		if($info){
			//再batch表创建对应批次
			$db = Db::table('batch')->insert(['batch'=>$batch]);
			
			//加载指定文件
			$fileName = '/public/upload/excel/'.$info->getSaveName();

			$phpExcelObj = \PHPExcel_IOFactory::load($fileName);
			$sheet = array();
			$sheet[] = $phpExcelObj->getSheet(0)->toArray();
			
			$name = '';
			//dump($sheet[0]);
			//把表格数据遍历为数组
			foreach($sheet[0] as $key=>$value){
				
				if($key!=0){
					foreach($value as $k=>$val){
						if($k == 0){
							$name = $val;
						}
						if($k == 1){
							$score = $val;
						}
						
					}
					
					//上传到数据库
					GradeModel::create(['name'=>$name,'score'=>$score,'batch'=>$batch,'class'=>$class,'subject'=>$subject]);
					//echo $name.$score.$batch.$class.'<br>';
				}
				
			}
			$this->view->assign('class',$class);
			$this->view->assign('batch',$batch);
			$this->view->assign('subject',$subject);
			return $this->view->fetch();

		}else{
			// 上传失败获取错误信息
			//echo $file->getError();
			return ['status'=>0,'msg'=>'上传失败'];
		}	
	}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
	//下载Excel模板
	public function product()
	{
		$excel = new \PHPExcel();
		

		//2.指定当前的sheet
		$objSheet = $excel->setActiveSheetIndex(0);
		
		//写入值
		$objSheet->setCellValue('A1', '姓名');
		$objSheet->setCellValue('B1', '分数');


		//4.保存文件
		$objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
		//告诉浏览器以xlsx文件输出
		
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		//Content-Disposition 消息头指示输出内容该以何种形式展示， attachment：消息体应该被下载到本地
		//filename的值预填为下载后的文件名
		
		header('Content-Disposition: attachment;filename="01simple.xlsx"');
		//设置缓存存储的最大周期，超过这个时间缓存被认为过期(单位秒),
		//max-age=0 即不缓存
		
		header('Cache-Control: max-age=0');
		
		$objWriter->save('php://output');
	}