通用PHPExcel导出函数代码
- WBOYOriginal
- 2016-06-07 11:34:311183browse
通用PHPExcel导出excel函数代码
公用函数:/**<br>
* 数据表导出excel<br>
*<br>
* @author rainfer <br>
*<br>
* @param string $table,不含前缀表名,必须<br>
* @param string $file,保存的excel文件名,默认表名为文件名<br>
* @param string $fields,需要导出的字段名,默认全部,以半角逗号隔开<br>
* @param string $field_titles,需要导出的字段标题,需与$field一一对应,为空则表示直接以字段名为标题,以半角逗号隔开<br>
* @param stting $tag,筛选条件 以字符串方式传入,例："limit:0,8;order:post_date desc,listorder desc;where:id>0;"<br>
* limit:数据条数,可以指定从第几条开始,如3,8(表示共调用8条,从第3条开始)<br>
* order:排序方式，如：post_date desc<br>
* where:查询条件，字符串形式，和sql语句一样<br>
*/<br>
function export2excel($table,$file='',$fields='',$field_titles='',$tag=''){<br>
//处理传递的参数<br>
if(stripos($table,C('DB_PREFIX'))==0){<br>
//含前缀的表,去除表前缀<br>
$table=substr($table,strlen(C('DB_PREFIX')));<br>
}<br>
$file=empty($file)?C('DB_PREFIX').$table:$file;<br>
$fieldsall=M($table)->getDbFields();<br>
$field_titles=empty($field_titles)?array():explode(",",$field_titles);<br>
if(empty($fields)){<br>
$fields=$fieldsall;<br>
//成员数不一致,则取字段名为标题<br>
if(count($fields)!=count($field_titles)){<br>
$field_titles=$fields;<br>
}<br>
}else{<br>
$fields=explode(",",$fields);<br>
$rst=array();<br>
$rsttitle=array();<br>
$title_y_n=(count($fields)==count($field_titles))?true:false;<br>
foreach($fields as $k=>$v){<br>
if(in_array($v,$fieldsall)){<br>
$rst[]=$v;<br>
//一一对应则取指定标题,否则取字段名<br>
$rsttitle[]=$title_y_n?$field_titles[$k]:$v;<br>
}<br>
}<br>
$fields=$rst;<br>
$field_titles=$rsttitle;<br>
}<br>
//处理tag标签<br>
$tag=param2array($tag);<br>
$limit = !empty($tag['limit']) ? $tag['limit'] : '';<br>
$order = !empty($tag['order']) ? $tag['order'] : '';<br>
$where=array();<br>
if (!empty($tag['where'])) {<br>
$where['_string'] = $tag['where'];<br>
}<br>
//处理数据<br>
$data= M($table)->field(join(",",$fields))->where($where)->order($order)->limit($limit)->select();<br>
import("Org.Util.PHPExcel");<br>
error_reporting(E_ALL);<br>
date_default_timezone_set('Europe/London');<br>
$objPHPExcel = new \PHPExcel();<br>
import("Org.Util.PHPExcel.Reader.Excel5");<br>
/*设置excel的属性*/<br>
$objPHPExcel->getProperties()->setCreator("rainfer")//创建人<br>
->setLastModifiedBy("rainfer")//最后修改人<br>
->setKeywords("excel")//关键字<br>
->setCategory("result file");//种类<br>
<br>
//第一行数据<br>
$objPHPExcel->setActiveSheetIndex(0);<br>
$active = $objPHPExcel->getActiveSheet();<br>
foreach($field_titles as $i=>$name){<br>
$ck = num2alpha($i++) . '1';<br>
$active->setCellValue($ck, $name);<br>
}<br>
//填充数据<br>
foreach($data as $k => $v){<br>
$k=$k+1;<br>
$num=$k+1;//数据从第二行开始录入<br>
$objPHPExcel->setActiveSheetIndex(0);<br>
foreach($fields as $i=>$name){<br>
$ck = num2alpha($i++) . $num;<br>
$active->setCellValue($ck, $v[$name]);<br>
}<br>
}<br>
$objPHPExcel->getActiveSheet()->setTitle($table);<br>
$objPHPExcel->setActiveSheetIndex(0);<br>
header('Content-Type: application/vnd.ms-excel');<br>
header('Content-Disposition: attachment;filename="'.$file.'.xls"');<br>
header('Cache-Control: max-age=0');<br>
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');<br>
$objWriter->save('php://output');<br>
exit;<br>
}用到的2个函数:/**<br>
* 生成参数列表,以数组形式返回<br>
* @author rainfer <br>
*/<br>
function param2array($tag = ''){<br>
$param = array();<br>
$array = explode(';',$tag);<br>
foreach ($array as $v){<br>
$v=trim($v);<br>
if(!empty($v)){<br>
list($key,$val) = explode(':',$v);<br>
$param[trim($key)] = trim($val);<br>
}<br>
}<br>
return $param;<br>
}<br>
/**<br>
* 数字到字母列<br>
* @author rainfer <br>
*/<br>
function num2alpha($intNum, $isLower = false)<br>
{<br>
$num26 = base_convert($intNum, 10, 26);<br>
$addcode = $isLower ? 49 : 17;<br>
$result = '';<br>
for ($i = 0; $i
$code = ord($num26{$i});<br>
if ($code
$result .= chr($code + $addcode);<br>
} else {<br>
$result .= chr($code + $addcode - 39);<br>
}<br>
}<br>
return $result;<br>
}至于PHPExcel请自行下载
实例参考YFCMF:http://www.topthink.com/topic/23584.html
AD：真正免费，域名+虚机+企业邮箱=0元