php导出excel封装类
安装包
composer require phpoffice/phpexcel
<?php /** * 公共的导出类@author:zhaoyaei * $column :导出的第一行标题 * $result :需要导出的结果集 * $path :导出文件的保存路径 * $sheet_num :每个工作薄的最大行数(可选) * @return :保存的地址 */ class Export{ public function __construct(){ //初始化类,引入相关类文件 include "PHPExcel.php"; include "PHPExcel/IOFactory.php"; } //导出设置 public function createxcel($column,$result,$path,$sheet_num = null){ //检查数据的合法性 if(empty($column) || empty($path) || $path == "" || empty($result)){ return false; } //如果数据较大,则分薄导出 if(empty($sheet_num) || $sheet_num <= 0){ $sheet_num = 2000; } $path = $this->check_encod("GBK",$path); //创建PHPExcel实例 $objPHPExcel = new \PHPExcel(); //总数据行数和数据列数 $arr_num = count($result); $field_count = count($column); //工作薄数目,工作薄数量太多,可以调节煤业导出的数据条数减少工作薄数目 $get_num = ceil($arr_num / $sheet_num); if($get_num > 20){ return false; } //生成列信息 $ary = 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"); for($i = 1;$i <= $field_count; $i++){ $ary_info[$i] = $ary[$i/27].$ary[$i%27]; $fieldwidth[$i-1] = 20; //设置列宽默认是20 } //循环每个工作薄 for($sheet = 0;$sheet < $get_num; $sheet++){ //创建工作薄,设置起始工作薄 $objPHPExcel->createSheet($sheet); $Sheet = $objPHPExcel->setActiveSheetIndex($sheet); //设置工作薄样式 $i = 1; foreach ($column as $key => $value){ //设置第一行的值 $Sheet->setCellValue($ary_info[$i] .'1', $value); //设置第一行加粗 $objPHPExcel->getActiveSheet()->getStyle($ary_info[$i] .'1')->getFont()->setBold(true); //设置行宽 $objPHPExcel->getActiveSheet()->getColumnDimension($ary_info[$i] )->setWidth($fieldwidth[$i-1]); $i = $i + 1; } //每个工作薄开始的行数 $hang_num = 2; //本工作薄结束行数开始为0 $end_num = 0; //start_num本工作薄开始行数 $start_num = $sheet*$sheet_num; //计算出每次导出的开始行数和结束行数(多工作薄导出) if($arr_num > ($start_num + $sheet_num)){ $end_num = $start_num + $sheet_num; }else{ $end_num = $arr_num; } //循环行数 for($i = $start_num; $i < $end_num; $i++){ //循环列数 for($j = 1;$j <= $field_count; $j++){ //写入数据 $Sheet->setCellValue($ary_info[$j].($hang_num)," ".$result[$i][$j]); } $hang_num++; } //设置sheet的名称 $objPHPExcel->getActiveSheet($sheet)->setTitle('sheet'.$sheet,$sheet); //设置sheet的起始位置 $objPHPExcel->setActiveSheetIndex($sheet); } //通过PHPExcel_IOFactory的写函数将上面数据写出来 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //设置时区 date_default_timezone_set("Asia/Shanghai"); //保存并返回保存路径 $objWriter->save($path); return $this->check_encod("utf-8",$path); } public function check_encod($encod,$string){ //判断字符编码 $encode = mb_detect_encoding($string, array("ASCII","UTF-8","GB2312","GBK","BIG5")); if($encode != $encod){ $string = iconv($encode, $encod, $string); } return $string; } } ?>