//引入工具类
require_once 'PHPExcel.php';
//HandleExcel.class.php 文件
class HandleExcel extends PHPExcel{ private $file;
private $fileType; private $objProperty; //文档属性对象
private $objReader;
private $objWriter; private $property = array(); public function __construct($property = array()){
parent::__construct();
if($property){
$this->property = $property;
$this->setProperty($this->property);
}
} /*
* 设置文档属性
* $property = array('title'=>'标题', 'creator' => '作者');
*/
public function setProperty($property = array()){
$this->objProperty = $this->getProperties();
if(!empty($property['creator']))$this->objProperty->setCreator($property['creator']);
if(!empty($property['title'])) $this->objProperty->setTitle($property['title']);
if(!empty($property['subject']))$this->objProperty->setSubject($property['subject']);
if(!empty($property['laster']))$this->objProperty->setLastModifiedBy($property['laster']);
if(!empty($property['description']))$this->objProperty->setDescription($property['description']);
if(!empty($property['keyword']))$this->objProperty->setKeywords($property['keyword']);
if(!empty($property['category']))$this->objProperty->setCategory($property['category']);
} /*
* 添加数据
* $data = array( 'name'=>'tom', 'age'=>'13');
* $dataCnf = array('name=>'a1', 'age'=>'b2');
* */
public function addData($data, $field = array(), $index = ''){
$objAdd = ($index)? $this->setActiveSheetIndex($index) : $this->getActiveSheet(); //根据data自动创建位置数据对应位置.
if($field){
$data = array_merge( array(0 => $field), $data);
$excelRowCnf = 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');
$excelLine = 1;
foreach($data as $key => $row){
$excelRow = 0;
foreach($field as $fieldName => $text){
$data['new'][$key][$excelRowCnf[$excelRow].$excelLine] = $row[$fieldName];
$excelRow++;
}
unset($data[$key]);
$excelLine++;
}
$data = $data['new'];
} //添加
foreach($data as $row){
foreach($row as $place => $val){
if(empty($place) || empty($val)) continue;
$objAdd->setCellValue($place, $val);
}
} } //生成文件
public function saveFile($savePath, $output = false, $type = 'Excel5'){
$this->objWriter = PHPExcel_IOFactory::createWriter($this, $type);
if($output){
$savePath = iconv ('utf-8', 'gb2312', $savePath);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$savePath.'"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$this->objWriter->save('php://output');
exit;
}
$this->objWriter->save($savePath);
} /* -------------------------------------- 读取文件 ---------------------------- */ //设置读对象
private function setReader($file, $type = null){
$this->file = $file;
if($type){
$this->fileType = $type;
$this->objReader = PHPExcel_IOFactory::createReader($type)->load($file);
}else{
$this->fileType = PHPExcel_IOFactory::identify($file);
$this->objReader = PHPExcel_IOFactory::load($file);
}
} //加载文件
public function loadFile($file, $type = null){
$this->setReader($file, $type);
$this->sheetData = $this->objReader->getActiveSheet()->toArray(null,true,true,true);
} //返回需要的数据
public function dataFormat($meed, $start = 1, $end = null){
foreach($this->sheetData as $line => $row){
if($start && $line < $start) continue;
if($end && $line > $end) break;
foreach($row as $key => $val){
if(array_key_exists($key, $meed)){
$data[$line][$meed[$key]] = $val;
}
}
}
return array_merge($data);
} //工作表信息
public function sheetInfo($file = null){
($file)? null : $file = $this->file;
$info = $this->objReader->listWorksheetInfo($file);
return $info;
} }
set_time_limit(0);
//使用如下:
require_once 'HandleExcel.class.php';
$myExcel = new HandleExcel();
$fieldCnf = array('title'=>'名称','type'=>'类型', 'content'=>'反馈内容', 'name'=>'反馈人', 'reg_mail'=>'联系邮箱','from_link'=>'相关链接', 'addtime'=>'反馈时间'); /*说明一下, $record 是查询得到的二维数组数据
array(
array('title' => '这是标题1', 'type'=>1, 'content'=>'内容文字' ... ),
array('title' => '这是标题2', 'type'=>3, 'content'=>'内容文字' ... )
...
)
*/
$myExcel->addData($record, $fieldCnf);
// ->saveFile(文件名, 是否输出到浏览器? )
$myExcel->saveFile('用户反馈数据.xls', true);
最后, 得到的excel文件显示如下: