公用函数:/**
* 数据表导出excel
*
* @author rainfer
*
* @param string $table,不含前缀表名,必须
* @param string $file,保存的excel文件名,默认表名为文件名
* @param string $fields,需要导出的字段名,默认全部,以半角逗号隔开
* @param string $field_titles,需要导出的字段标题,需与$field一一对应,为空则表示直接以字段名为标题,以半角逗号隔开
* @param stting $tag,筛选条件 以字符串方式传入,例：limit:0,8;order:post_date desc,listorder desc;where:id>0;
* limit:数据条数,可以指定从第几条开始,如3,8(表示共调用8条,从第3条开始)
* order:排序方式，如：post_date desc
* where:查询条件，字符串形式，和sql语句一样
*/
function export2excel($table,$file='',$fields='',$field_titles='',$tag=''){
//处理传递的参数
if(stripos($table,c('db_prefix'))==0){
//含前缀的表,去除表前缀
$table=substr($table,strlen(c('db_prefix')));
}
$file=empty($file)?c('db_prefix').$table:$file;
$fieldsall=m($table)->getdbfields();
$field_titles=empty($field_titles)?array():explode(,,$field_titles);
if(empty($fields)){
$fields=$fieldsall;
//成员数不一致,则取字段名为标题
if(count($fields)!=count($field_titles)){
$field_titles=$fields;
}
}else{
$fields=explode(,,$fields);
$rst=array();
$rsttitle=array();
$title_y_n=(count($fields)==count($field_titles))?true:false;
foreach($fields as $k=>$v){
if(in_array($v,$fieldsall)){
$rst[]=$v;
//一一对应则取指定标题,否则取字段名
$rsttitle[]=$title_y_n?$field_titles[$k]:$v;
}
}
$fields=$rst;
$field_titles=$rsttitle;
}
//处理tag标签
$tag=param2array($tag);
$limit = !empty($tag['limit']) ? $tag['limit'] : '';
$order = !empty($tag['order']) ? $tag['order'] : '';
$where=array();
if (!empty($tag['where'])) {
$where['_string'] = $tag['where'];
}
//处理数据
$data= m($table)->field(join(,,$fields))->where($where)->order($order)->limit($limit)->select();
import(org.util.phpexcel);
error_reporting(e_all);
date_default_timezone_set('europe/london');
$objphpexcel = new \phpexcel();
import(org.util.phpexcel.reader.excel5);
/*设置excel的属性*/
$objphpexcel->getproperties()->setcreator(rainfer)//创建人
->setlastmodifiedby(rainfer)//最后修改人
->setkeywords(excel)//关键字
->setcategory(result file);//种类
//第一行数据
$objphpexcel->setactivesheetindex(0);
$active = $objphpexcel->getactivesheet();
foreach($field_titles as $i=>$name){
$ck = num2alpha($i++) . '1';
$active->setcellvalue($ck, $name);
}
//填充数据
foreach($data as $k => $v){
$k=$k+1;
$num=$k+1;//数据从第二行开始录入
$objphpexcel->setactivesheetindex(0);
foreach($fields as $i=>$name){
$ck = num2alpha($i++) . $num;
$active->setcellvalue($ck, $v[$name]);
}
}
$objphpexcel->getactivesheet()->settitle($table);
$objphpexcel->setactivesheetindex(0);
header('content-type: application/vnd.ms-excel');
header('content-disposition: attachment;filename='.$file.'.xls');
header('cache-control: max-age=0');
$objwriter = \phpexcel_iofactory::createwriter($objphpexcel, 'excel5');
$objwriter->save('php://output');
exit;
}用到的2个函数:/**
* 生成参数列表,以数组形式返回
* @author rainfer
*/
function param2array($tag = ''){
$param = array();
$array = explode(';',$tag);
foreach ($array as $v){
$v=trim($v);
if(!empty($v)){
list($key,$val) = explode(':',$v);
$param[trim($key)] = trim($val);
}
}
return $param;
}
/**
* 数字到字母列
* @author rainfer
*/
function num2alpha($intnum, $islower = false)
{
$num26 = base_convert($intnum, 10, 26);
$addcode = $islower ? 49 : 17;
$result = '';
for ($i = 0; $i $code = ord($num26{$i});
if ($code $result .= chr($code + $addcode);
} else {
$result .= chr($code + $addcode - 39);
}
}
return $result;
}至于phpexcel请自行下载
实例参考yfcmf:http://www.topthink.com/topic/23584.html
ad：真正免费，域名+虚机+企业邮箱=0元