thinkphp5.0数据导出excel表格

<?phpnamespace Admin\admin\model;use think\Model;class MarkModel extends Model{ //导出预约信息
public function outExcel1(){ $del=@implode(",",$_POST["del"]); if($del!=""){ $art = db('messga'); $where['id'] = array('in',$del); $rs = $art->where($where)->order('id desc')->select(); foreach ($rs as $v){
$excdata[]=array( 'id'=>$v["id"], 'names'=>$v["names"], 'phone'=>$v["phone"], 'email'=>$v["email"], 'cid'=>$v["cid"], 'companynames'=>$v["companynames"], 'tel'=>$v["tel"], 'num'=>$v["num"], 'p_names'=>$v["p_names"], 'dates'=>date('Y-m-d H:i:s',$v["dates"]) );
} }else{ $art = db('message'); $rs = $art->order('id desc')->select(); foreach($rs as $v){
$excdata[]=array( 'id'=>$v["id"], 'names'=>$v["names"], 'phone'=>$v["phone"], 'email'=>$v["email"], 'cid'=>$v["cid"], 'companynames'=>$v["companynames"], 'tel'=>$v["tel"], 'num'=>$v["num"], 'p_names'=>$v["p_names"], 'dates'=>date('Y-m-d H:i:s',$v["dates"]) );
} //echo "<script>alert ('请选中要导出的数据'); history.go(-1);</script>"; }//数组信息根据自己项目而定
$expCellName=array( array('id','序号'), array('names','姓名'), array('phone','电话'), array('email','邮箱'), array('companynames','公司名称'), array('num','人数'), array('cid','公开课'), array('p_names','职务'), array('tel','座机'), array('dates','提交日期') ); $this->exportExcel('留言信息',$expCellName,$excdata); exit; } /** * 导出操作 * 参数1：$expTitle :导出excel的标题 * 参数2：$expCellName:导出到表格中需要显示每一列的标题 如下： * $expCellName=array( * 参数三：$expTableData : 需要导出的表中数据 * * */ public function exportExcel($expTitle='', $expCellName, $expTableData){//引入PHPExcell类
import('PHPExcel.Classes.PHPExcel'); import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称 $fileName = $expTitle.date('_YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定 $cellNum = count($expCellName); $dataNum = count($expTableData); vendor("phpoffice.phpexcel.Classes.PHPExcel"); $objPHPExcel = new \PHPExcel(); $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'); // 设置水平垂直居中 $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置excel表格 从 A1 到 AB1 这一行的字体加粗 $objPHPExcel->getActiveSheet()->getStyle('A1:AB1')->getFont()->setBold(true); // 设置某一行的高度 1.2.3 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20); //J 、 K列为文本 (输入什么就是什么，不会随着excel系统格式变化) // $objPHPExcel->getActiveSheet()->getStyle('J')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); // $objPHPExcel->getActiveSheet()->getStyle('K')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //设置某一列的宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(45); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(105); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20); // 设置某一行的高度 1.2.3 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20); // 隐藏某一列 //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setVisible(false); //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false); //$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false); //$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false); //合并单元格 // $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]); } // 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; }}