一,Excel导入:
1,composer 安装phpExcel:
composer require phpoffice/phpexcel (好像只有Classes里面的文件有用,其他可删除)
2,前端代码
- <form method="post" action="{:url('student/savestudentImport')}" class="form-signin" enctype="multipart/form-data" >
- <input name="excel" type="file" class="form-control">
- <button class="btn btn-lg btn-primary btn-block">导入</button>
- </form>
3,控制器
- public function savestudentImport(){
- //import('phpexcel.PHPExcel', EXTEND_PATH);//方法二
- vendor("PHPExcel.PHPExcel"); //方法一
- $objPHPExcel = new \PHPExcel();
- //获取表单上传文件
- $file = request()->file('excel');
- $info = $file->validate(['size'=>15678,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
- if($info){
- $exclePath = $info->getSaveName(); //获取文件名
- $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath; //上传文件的地址
- $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
- $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8
- echo "<pre>";
- $excel_array=$obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
- array_shift($excel_array); //删除第一个数组(标题);
- $data = [];
- $i=0;
- foreach($excel_array as $k=>$v) {
- $data[$k]['title'] = $v[0]; //数据表字段赋值
- $i++;
- }
- $success=Db::name('t_station')->insertAll($data); //批量插入数据
- $error=$i-$success;
- echo "总{$i}条,成功{$success}条,失败{$error}条。";
- }else{
- // 上传失败获取错误信息
- echo $file->getError();
- }
- }
二,Excel导出
- error_reporting(E_ALL);
- ini_set('display_errors', TRUE);
- ini_set('display_startup_errors', TRUE);
-
- vendor("PHPExcel.PHPExcel");
- //创建对象
- $excel = new PHPExcel();
- //Excel表格式,这里简略写了8列
- $letter = array('A','B','C','D','E','F','F','G');
- //表头数组
- $tableheader = array('学号','姓名','性别','年龄','班级');
- //填充表头信息
- for($i = 0;$i < count($tableheader);$i++) {
- $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
- }
- //表格数组
- $data = array(
- array('1','小王','男','20','100'),
- array('2','小李','男','20','101'),
- array('3','小张','女','20','102'),
- array('4','小赵','女','20','103')
- );
- //填充表格信息
- for ($i = 2;$i <= count($data) + 1;$i++) {
- $j = 0;
- foreach ($data[$i - 2] as $key=>$value) {
- $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
- $j++;
- }
- }
- //创建Excel输入对象
- $write = new PHPExcel_Writer_Excel5($excel);
- header("Pragma: public");
- header("Expires: 0");
- header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
- header("Content-Type:application/force-download");
- header("Content-Type:application/vnd.ms-execl");
- header("Content-Type:application/octet-stream");
- header("Content-Type:application/download");;
- header('Content-Disposition:attachment;filename="testdata.xls"');
- header("Content-Transfer-Encoding:binary");
- $write->save('php://output');