PHPExcel 类

//引入工具类
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文件显示如下:

posted @ 2013-12-21 16:49  心随所遇  阅读(562)  评论(0编辑  收藏  举报