用PHPExcel导出导入Excel
thinkPHP5.0框架
查询数据库调用Excel方法
public function exportlist(){ $orderModel = new OrderModel(); if($start_time = $this->request->param('start_time','')){ $orderModel->where(function($query) use($start_time) { $query->where('created_at','>=',$start_time); }); } if($end_time = $this->request->param('end_time','')){ $orderModel->where(function($query) use($end_time) { $query->where('created_at','<=',$end_time); }); } $expTitle = '详细营销单'.date('Y-m-d');
//数据库的字段, '名字', '宽度 $expCellName = [ ['rec_code','推荐人',15], ['pro_code','产品',15], ['price','金额',8], ['sell_code','营销人',15], ['created_at','日期',20], ]; $expTableData = $orderModel->order('id','desc')->select()->toArray(); if($expTableData){ $this->exportListExcel($expTitle,$expCellName,$expTableData); } }
Excel导出方法
public function exportListExcel($expTitle,$expCellName,$expTableData){ $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称 $fileName = $xlsTitle; $cellNum = count($expCellName); $dataNum = count($expTableData); Loader::import('PHPExcel.Classes.PHPExcel'); Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5'); $objPHPExcel = new \PHPExcel(); // 居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal('center'); $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical('center'); $cellName = array('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'); // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格 // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s')); for($i=0;$i<$cellNum;$i++){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]); $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]); } // Miscellaneous glyphs, UTF-8 for($i=0;$i<$dataNum;$i++){ for($j=0;$j<$cellNum;$j++){ $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]); } } header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"'); header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
PHP导入Excel
public function excel(){ if($this->request->isPost()){ if (!empty($_FILES)) { if(!in_array(cmf_get_file_extension($_FILES['file']['name']),['xlsx','xls'])){ $this->error('请上传Excel格式的文件！', ''); } $uploader = new Upload(); $uploader->setFileType('file'); $result = $uploader->upload(); if ($result) { Loader::import('PHPExcel.Classes.PHPExcel'); Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5'); $filename = ROOT_PATH .'public'. $result['preview_url']; //文件路径 $extension = cmf_get_file_extension($result['name']); //文件扩展名 if ($extension == 'xlsx') { $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load($filename, $encode = 'utf-8'); //加载文件内容,编码utf-8 } else if ($extension == 'xls') { $objReader = \PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load($filename, $encode = 'utf-8'); //加载文件内容,编码utf-8 } else { $this->error('请上传Excel格式的文件！', ''); } //$excel_array = $objPHPExcel->getsheet(0)->toArray(); //转换为数组格式 $sheet =$objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow();//取得总行数 //$highestColumn =$sheet->getHighestColumn(); //取得总列数 for($i=2;$i<=$highestRow;$i++){ if($value = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue()){ if(!Db::name('sell')->where('sell_code',$value)->find()){ $data[] = ['sell_code'=>$value]; } } } if (Db::name('sell')->insertAll ($data)) { $this->success('导入数据成功！', url('AdminSell/index')); } else { $this->error('导入数据失败!', ''); } } else { $this->error($uploader->getError(), ''); } } } }
还有一种比较简单,快捷的方法, 不需要因为额外的文件或者composer, 就是直接输出excel文件.
只需要一个方法(适合菜鸟使用)
/** * Author 963087326@qq.com * 导出excel * @param array $data 导出数据 * @param array $a excel表头 * @param string $b 导出的文件名 * @return file */ function excel($data,$a,$b=null){ header("Content-type:application/octet-stream"); header("Accept-Ranges:bytes"); header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=".$b.".xls"); header("Pragma: no-cache"); header("Expires: 0"); //$a=['第一列','第二列','第三列']; foreach ($a as $k => $v) { $a[$k]=iconv("UTF-8", "GB2312//IGNORE",$v); } //输出表头 echo implode("\t",$a).PHP_EOL; foreach ($data as $key => $value) { $arr = array(); foreach ($value as $kk => $vv) { if(empty($vv) || $vv=='无' ){ $vv='--'; } $j=iconv("UTF-8", "GB2312//IGNORE",$vv); $arr[]= trim($j); } //输出数据 echo implode("\t",$arr)."\n"; } //$b=[1,2,3]; }
