php导出execl

public function down(){ $data= $this->db->select('appname, gameintro, qqinfo, submitTime')->order_by('submitTime desc')->get('tb_qplibmanage_feedback')->result(); $result = array(); foreach($data as $val){ array_push($result, array( 'qqinfo' => $val->qqinfo, 'appname' => $val->appname, 'gameintro' => $val->gameintro, 'submitTime' => $val->submitTime )); } $headArr = array( '用户QQ', '游戏名称', '游戏简介', '日期' ); $this->load->library('Tools'); $this->tools->getExcel('qp_liuyan', $headArr, $result); }<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');class Tools {/* * 生成下载excel文件 * $filename="生成的excel名称"; * $headArr=array("表头","名称"); * $data 要显示的数据 * array(array('username'=>1,'pwd'=>2),array(...)..); * 调用方法： * $this->getExcel($filename,$headArr,$data); * * @author v_zengwang */ public function getExcel($fileName, $headArr, $data) { date_default_timezone_set('Asia/Shanghai'); //对数据进行检验 if (empty($data) || !is_array($data)) { die("data must be a array"); } //检查文件名 if (empty($fileName)) { exit; } $date = date("Y_m_d", time()); $fileName .= "_{$date}.xls"; //导入文件 $CI =& get_instance(); $CI->load->library('PHPExcel');$CI->load->library('PHPExcel/IOFactory'); //创建PHPExcel对象 $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties(); //设置表头 $key = ord("A"); foreach ($headArr as $v) { $colum = chr($key); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); $key += 1; } $column = 2; $objActSheet = $objPHPExcel->getActiveSheet(); foreach ($data as $key => $rows) { //行写入 $span = ord("A"); foreach ($rows as $keyName => $value) { // 列写入 $j = chr($span); $objActSheet->setCellValue($j . $column, $value); $span++; } $column++; } $fileName = iconv("utf-8", "gb2312", $fileName); //重命名表 // $objPHPExcel->getActiveSheet()->setTitle('test'); //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); ob_end_clean(); //清除缓冲区,避免乱码,那些年被坑过的乱码 header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=\"$fileName\""); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); //文件通过浏览器下载 exit; } /** * 导入execl文件获取表中数据 * @param [file] $file [excel文件存放路径] * @return [array] [返回表中数据] */ public function execExcel($file) { date_default_timezone_set('Asia/Shanghai'); //检查文件 if (empty($file)) { exit; } //导入文件 $CI =& get_instance(); $CI->load->library('PHPExcel'); $CI->load->library('PHPExcel/IOFactory'); // $CI->load->library('PHPExcel/Reader/Excel5'); // 创建对象 $objPHPExcel = new IOFactory(); $readerType = $objPHPExcel::identify($file); $objReader = $objPHPExcel::createReader($readerType); // 读文件 $objPHPExcel = $objReader->load($file); $objWorksheet = $objPHPExcel->getActiveSheet(0); // 少量数据可用，数据多的话该方法出问题 /* $data = $objWorksheet->toArray(null,true,true,true); return $data;*/ // 总行数 $highestRow = $objWorksheet->getHighestRow(); // 总列数 $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = range('A', $highestColumn); $data = array(); // 从第二行开始，第一行一般是表头 for ($row = 2; $row <= $highestRow; $row++) { $array = array(); foreach ($highestColumnIndex as $value) { $address = $value . $row; $array[] = $objWorksheet->getCell($address)->getFormattedValue(); } array_push($data, $array); } return $data; } /** * 替换字符串里面的特殊字符 * @param [type] $str [description] * @return [type] [description] */ public function strreplace($str) { if ($str == null) { return ''; } $strNew = str_replace('&', '&', $str); $strNew = str_replace(' ', ' ', $str); $strNew = str_replace('<', '<', $str); $strNew = str_replace('>', '>', $str); $strNew = str_replace('\'', '&apos;', $str); $strNew = str_replace('"', '"', $str); return $strNew; } /** * 根据给定的参数数组处理对象数组中对应的值里面的特殊字符 * @param [Array] $arr [参数数组] * @param [Object] $obj [对象数组] * @return [Object] [返回处理后的对应数组] */ public function obj_str_replace($arr, $obj) { $len = count($arr); for ($i=0; $i < $len; $i++) { $temp = $arr[$i]; if (is_array($obj)) { $obj[$temp] = $this->strreplace($obj[$temp]); } elseif (is_object($obj)) { $obj->$temp = $this->strreplace($obj->$temp); } } return $obj; } /** * 显示分页数据 * @return [array] 返回分页数据 * @author v_zengwang */ public function pager() { $params = func_get_args(); if (count($params) < 2) { return false; } else { $CI =& get_instance(); $CI->load->library('pagination'); if (isset($params[2])) { $config['base_url'] = $params[2]; } else { $config['base_url'] = $_SERVER['PHP_SELF'].'?'; } $config['page_query_string'] = true; $config['enable_query_strings'] = true; $config['use_page_numbers'] = TRUE; $config['query_string_segment'] = 'page'; $config['total_rows'] = $params[1]; $config['per_page'] = $params[0]; $config['full_tag_open'] = '

• '; $config['full_tag_close'] = '

'; $config['prev_link'] = '上一页'; $config['prev_tag_open'] = '

• '; $config['prev_tag_close'] = ''; $config['next_link'] = '下一页'; $config['next_tag_open'] = '
• '; $config['next_tag_close'] = ''; $config['cur_tag_open'] = '
• '; $config['cur_tag_close'] = ''; $config['num_tag_open'] = '
• '; $config['num_tag_close'] = ''; $config['first_link'] = '首页'; $config['first_tag_open'] = '
• '; $config['first_tag_close'] = ''; $config['last_link'] = '尾页'; $config['last_tag_open'] = '
• '; $config['last_tag_close'] = ''; $CI->pagination->initialize($config); return $CI->pagination->create_links(); } } /** * 文件上传帮助类 * @param [string] $path [文件上传路径] * @param [string] $allowed_types [允许上传的文件类型] * @param [string] $filename [文件名] * @param [string] $name [上传文件的name值] * @param [string] $imgurl [文件访问路径] * @return [Array] [返回上传结果] */ public function fileupload($path,$allowed_types,$filename,$name,$imgurl) { $CI =& get_instance(); $CI->load->library('upload'); if (!is_dir($path)) { mkdir($path, 0777); } $config['upload_path'] = $path; $config['allowed_types'] = $allowed_types; $config['file_size'] = 1024; // $config['max_width'] = '123'; // $config['max_height'] = '212'; $config['overwrite'] = TRUE; $config['file_name'] = $filename; $CI->upload->initialize($config); $error = ''; $filevalue = ''; if($_FILES[$name]['name'] !== "") { if($CI->upload->do_upload($name)) { $upload_pic_data = $CI->upload->data(); $filevalue = $imgurl.$upload_pic_data['file_name']; } else { $error = $CI->upload->display_errors('

  ','

  '); } } $res['error'] = $error; $res['filevalue'] = $filevalue; return $res; } /** * 给定模版和数据返回渲染后的模版数据 * @param [string] $tpl_path [模版路径] * @param [array] $data [模版数据] * @return [string] [返回渲染后的模版数据] */ public function get_template_data($tpl_path, $data) { extract($data); //开启缓冲区 ob_start(); // If the PHP installation does not support short tags we'll // do a little string replacement, changing the short tags // to standard PHP echo statements. if ((bool) @ini_get('short_open_tag') === FALSE AND config_item('rewrite_short_tags') == TRUE) { echo eval('?>'.preg_replace("/;*\s*\?>/", "; ?>", str_replace('<?=', '<?php echo ', file_get_contents($tpl_path)))); } else { include($tpl_path); // include() vs include_once() allows for multiple views with the same name } // 拿渲染后的模版数据 $buffer = ob_get_contents(); //清除缓冲区内容，并关闭缓冲区 @ob_end_clean(); // 返回数据 return $buffer; } /** * 递归创建给定的目录文件夹 * @param [string] $dir [description] * @return [bool] [返回创建结果] */ public function create_folders($dir) { return is_dir($dir) or ($this->create_folders(dirname($dir)) and mkdir($dir, 0777)); } //将字符串以单个汉字切成数组 ，返回数组。 public function myStrtoArray($str,$charset){ $charsets["utf-8"] = $charsets["utf8"] = "/[\x01-\x7f]|[\xc2-\xdf][\x80-\xbf]|[\xe0-\xef][\x80-\xbf]{2}|[\xf0-\xff][\x80-\xbf]{3}/"; $charsets["gb2312"] = "/[\x01-\x7f]|[\xb0-\xf7][\xa0-\xfe]/"; $charsets["gbk"] = "/[\x01-\x7f]|[\x81-\xfe][\x40-\xfe]/"; $charsets["big5"] = "/[\x01-\x7f]|[\x81-\xfe]([\x40-\x7e]|\xa1-\xfe])/"; preg_match_all($charsets[$charset], $str, $matches); #按照指定编码将字符串切分成一个数组 return $matches[0]; } /** * 计算中文字符的长度,返回长度(int); */ public function myMbStrlen($str, $charset='utf-8'){ $matches = $this->myStrtoArray($str,$charset); return count($matches); } /** * 中文字串的截取,返回截取后的字符串(string)。 */ public function myMbSubstr($str,$start,$length,$charset='utf-8'){ $matches = $this->myStrtoArray($str,$charset); $substr = implode("", array_slice($matches, $start, $length)); #获取数组的子数组,并将子数组的元素连接起来3 return $substr; } /** * 调用通用的脚步移动到cdn,如果cdn上没有的目录，必须手动创建 * @param [string] $path_in20 [在20上的目录或文件] * @param [string] $path_in117 [对应cdn上的目录或文件] */ public function moveCDN($path_in20, $path_in117){ $handle = popen("./param_publish.sh $path_in20 $path_in117 ", 'r'); $read = fread($handle, 20960); pclose($handle); } /** * 生成js数据文件 * @param [type] $data [数据] * @param [type] $param [生成的变量名称] * @param [type] $file [生成的文件全路径包含文件名称(可以是绝对或相对路径)] * @param [type] $json_option [json_encode时的option(默认JSON_UNESCAPED_UNICODE) -1:代表不传] * @param [type] $is_copy [是否备份] */ public function gen_data($data, $param, $file, $json_option=JSON_UNESCAPED_UNICODE, $is_copy=0){ date_default_timezone_set('Asia/Shanghai'); if($json_option == -1){ $str = json_encode($data); }else{ $str = json_encode($data, $json_option); } if(empty($data)){ return; } $date = date("Y_m_d_H_i_s", time()); $path_parts = pathinfo($file); $file_name = $path_parts['dirname'] .'/'.$path_parts['filename'].'_'.$date.'.js'; $buff_temp_arr_copy = array(); if (file_exists($file)) { // 备份数据 if($is_copy){ copy($file, $file_name); } $buff_temp_arr_copy = file($file); $count = count($buff_temp_arr_copy); for($i=0; $i<$count; $i++){ $patten = "/^var\s*$param\s*=\s*/U"; if(trim($buff_temp_arr_copy[$i]) == ''){ unset($buff_temp_arr_copy[$i]); }else if(preg_match($patten, $buff_temp_arr_copy[$i], $matches)){ unset($buff_temp_arr_copy[$i]); } } } $buff_temp_arr_copy[] = 'var '.$param.' = '.$str.';'; $output_data = implode(PHP_EOL, $buff_temp_arr_copy).PHP_EOL; $handle = fopen($file, 'wb'); fwrite($handle, $output_data); fclose($handle); } //写配置文件 public function put_ini_file($file, $array){ $str=""; foreach ($array as $k => $v){ if (is_array($v)){ $str.="[$k]".PHP_EOL; $str.= $this->put_ini_file("",$v); $str.=PHP_EOL; }else $str.="$k=$v".PHP_EOL; } if($file) return file_put_contents($file,$str); else return $str; } /** * 生成json格式返回值 * @param [integer] $result [执行状态] * @param [string] $resultstr [返回的数据] * @param [string] $msg [提示消息] * @return [json] [返回json格式字符串] */ public function json_res($result, $resultstr, $msg) { $res = array( 'result' => $result, 'resultstr' => $resultstr, 'msg' => $msg ); return json_encode($res); } /** * 写日志 * @param string $file [文件名称包含文件后缀, 不包含目录, 日志文件会存在项目文件夹下的logs目录下, xxx.log] * @param string $message [信息] */ public function write_log($file='', $message=''){ date_default_timezone_set('Asia/Shanghai'); $message = preg_replace('/\s+/', ' ', $message); $file = dirname(__FILE__).'/../logs/'.$file; $last_editor = empty($_COOKIE['__OAName'])?'unknow':$_COOKIE['__OAName']; $handle = fopen($file, 'aw') or die("open $file error！"); $time = date('Y-m-d H:i:s'); $message = $time.'×'.$last_editor.'×'.$message.PHP_EOL; if(false === fwrite($handle, $message) ){ die('write log error!'); } } /** * 读日志 * @param string $file [文件名称包含文件后缀， 项目文件夹下的logs目录下， xxx.log] * @param string $starttime [时间范围] * @param string $endtime [时间范围] */ public function read_log($file, $starttime='', $endtime=''){ date_default_timezone_set('Asia/Shanghai'); header("Content-type:text/html;charset=utf-8"); $file = dirname(__FILE__).'/../logs/'.$file; if(!file_exists($file)){ die('log file not exists!'); } $file = file($file); if($starttime != ''){ $ser_starttime = strtotime($starttime); }else{ $ser_starttime = 0; } if($endtime != ''){ $ser_endtime = strtotime($endtime); }else{ $ser_endtime = time(); } $result = array(); for($i=0; $i= $ser_starttime && strtotime($info[0]) <= $ser_endtime ){ $str = $info[0].' '.$info[1].' '.$info[2].'
  
  '; $result[] = $str; } } echo implode('', $result); }}