Gemeinschaft
Lernen
Tools-Bibliothek
KI-Tools
Freizeit
suchen
Deutsch
Heim > php教程 > PHP源码 > 基于phpexcel的php导出excel类，附注释和调用方法

基于phpexcel的php导出excel类，附注释和调用方法

WBOY
Freigeben： 2016-06-08 17:21:03
Original
1234 Leute haben es durchsucht

以前我们写过一个 HP通过链式操作将数据输出excel（csv）格式，最近项目又要导出excel，由于项目中要导出excel的地方太多，因此写个类便于管理使用。

<script>ec(2);</script>

以前我们写过一个PHP通过链式操作将数据输出excel（csv）格式，http://www.111cn.net/phper/php-cy/72796.htm，现在我们来学习写一个php基于phpexcel制作的导出excel类

 代码如下 复制代码
/*
*@使用方法。
*引入类库。
*$excel = news excelC();
*$excel->fileName = '文件名称';//设置文件名称，默认为时间戳
*$excel->format = '2007';//文件类型，默认为2007，其他为excel5
*$record = array(
    'delKey'=>array('id','addTime','status'),//如果数据$data中有不需要显示的列，可以在此说明。删除不需要导出的键值
    'sort'          =>array(
                    'keyName'=>array('subjectName','flag'),//按keyName列排序，如果不存在则不排序。
                    //'reorder'=>'DESC',//排序方式，DESC为倒序，ASC为正序。如果keyName存在则排序keyName，如果不存在则按数组的键名排序，如果reorder不存在则不排序
                ),//排序   如果sort存在则排序，否则不排序，如果keyName存在则按设置排序，如果不存在则按字符排序，如果reorder不存在或为空或为DES则正序，等于DESC为倒序。
    'excelStyle'    =>array(
                        'setName'=>'Arial',//字体样式
                        'setSize'=>'12',//字体大小
                    ),//表格全局样式
    'title'         =>array('tableName'=>'学科列表','center'=>true,'direction'=>'right','merge'=>'2','setSize'=>'30'),//标题,center垂直,direction为合并方向。right,left,up,down。 merge为合并几个单元格，setSize为字体大小
    'data'          =>array(
                        array('tableName'=>'科目名称','width'=>'30','setName'=>'宋体','setSize'=>'20','background'=>'red','textColor'=>'white','bold'=>true,'underline'=>true,'borderColor'=>'cyan','center'=>true,),
                        array('tableName'=>'学科','width'=>'50','center'=>true),//颜色表是：black,white,red,green,blue,yellow,magenta,cyan
                    ),//表名称 tableName为名称，width为表格宽度，setName为字体样式，background为背景颜色,textColor为字体颜色，bold为加粗,underline为下划线，borderColor为边框颜色.
    'merge'         =>array(
                        //'flag'=>array('keyword'=>'初','direction'=>'right','merge'=>'2'),merge的键值为需要处理数据数组的键值，keyword为如果存在此关键字才执行其他样式操作，如果keyword不存在则执行所有键值为flag的单元格。
                        'all'=>array('width'=>'30','setName'=>'宋体','setSize'=>'20','background'=>'red','textColor'=>'white','bold'=>true,'underline'=>true,'borderColor'=>'cyan','center'=>true,),
                    ),//
);//导出配置
 
 
*$excel->export($record,$data);//$record为导出配置，$data为数据库的数据,$data可以为数组，也可以为对象。
*
*
*
*/
$address = dirname(dirname(__FILE__)).'/PHPExcel';
include $address.'/PHPExcel.class.php';
include $address.'/PHPExcel/Writer/Excel2007.php';
include $address.'/PHPExcel/Writer/Excel5.php';
include $address.'/PHPExcel/IOFactory.php';
 
/****************************
*生成excel文档。
*/
 
class excelC {
    
    public $format = '2007';//转换格式，默认为2007版本，其他版本，请输入不是2007的数字
    
    public $fileName;//文件名称默认为时间戳。
    
    
    private $objExcel;
    
    private $letters;
    
    public function __construct()
    {
        $this->fileName = time();
        
        $this->fileTitle = '导出数据';
        
        $this->objExcel = new PHPExcel();
        $this->letters = $this->letter();
    }
    
    
    //导出excel的属性
    private function attribute(){
        
        
        $this->objExcel->getProperties()->setCreator("力达行有限公司");//创建人
        
        $this->objExcel->getProperties()->setLastModifiedBy("力达行有限公司");//最后修改人
        
        $this->objExcel->getProperties()->setTitle("导出数据");//标题
        
        $this->objExcel->getProperties()->setSubject("导出数据");//题目
        
        $this->objExcel->getProperties()->setDescription("数据导出");//描述
        
        $this->objExcel->getProperties()->setKeywords("office 导出");//关键字
        
        $this->objExcel->getProperties()->setCategory("excel");//种类
    }
    
    
    
    //设置表(如果只有一个sheet可以忽略该函数，将默认创建。)
    private function sheet(){
        
        $this->objExcel->setActiveSheetIndex(0);//设置当前的表
        
        $this->objExcel->getActiveSheet()->setTitle('excel');//设置表名称。
    }
    
    
    /***************************
    *导出excel
    *@attr $record为表头及样式设置
    *@attr $data为需要导出的数据
    */
    public function export($record=array(),$data=array()){
        if(!$data)return false;
        if(!is_array($record))return false;//表样式及其他设置
        
        //处理获取到的数据
        $data = $this->maniData($record,$data);
        
        //获取整体样式。
        $this->excelData($record,$data);
        
        
        //$this->objExcel->getActiveSheet()->setCellValue('A1', '季度');
        
        $this->down();//导出下载
    }
    
    
    /*
    *处理表格
    */
    private function excelData(&$record,&$data){
        $this->attribute();//设置属性
        $this->sheet();//设置表
        $this->whole($record);//设置整体样式
        $this->tableHeader($record);//设置表格头。
        $this->tableContent($record,$data);//设置表格
        $this->excelTitle($record,2);//设置标题
    }
    
    /*
    *设置表格整体样式
    */
    private function whole(&$record){
        if(!array_key_exists('excelStyle',$record))return false;
        
        $excelStyle = $record['excelStyle'];
        
        $default = $this->objExcel->getDefaultStyle();
        
        if(array_key_exists('setName',$excelStyle))
            $default->getFont()->setName($excelStyle['setName']);//设置字体样式
            
        if(array_key_exists('setSize',$excelStyle))
            $default->getFont()->setSize($excelStyle['setSize']);//设置字体大小
    }
    
    /*
    *设置标题
    */
    private function excelTitle($record,$num){
        $titleL = $this->letters[0];
        if(!array_key_exists('title',$record))return false;
        $this->appOintStyle($titleL ,1,$record['title']);
        
    }
    
    /*
    *设置表格头。
    */
    private function tableHeader($record){
        if(!array_key_exists('data',$record))return false;
        $objExcel = $this->objExcel;
        $letters = $this->letters;
        
        if(!is_array($record['data']))return false;
        
        $i = 0;
        $hang = 2;
        foreach($record['data'] as $k=>$v){
        
            $this->appOintStyle($letters[$i],$hang,$v);
                
            $i++;
        }
        
    }
    
    private function setCellValue($letter,$data){
    
        if(@$data)
            $this->objExcel->getActiveSheet()->setCellValue($letter, $data);//填充值
        
        return $this;
    }
    
    private function getColumnDimension($letter,$data){
    
        if(@$data)
            $this->objExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($data);//设置宽度
            
        return $this;
    }
    
    private function setName($letter,$data){
    
        if(@$data)
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setName($data);//设置字体
            
        return $this;
    }
    
    private function setSize($letter,$data){
    
        if(@$data)
             $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setSize($data);//设置字体大小
            
        return $this;
    }
    
    private function background($letter,$data){
    
        if(@$data){
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFill()->getStartColor()->setARGB($this->backColor($data));
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);//设置背景色样式，无样式将不显示背景色。
        }
            
        return $this;
    }
    
    private function textColor($letter,$data){
    
        if(@$data){
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->getColor()->setARGB($data);//字体颜色
        }
            
        return $this;
    }
    
    private function setBold($letter,$data){
    
        if(@$data){
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setBold(true);//加粗
        }
            
        return $this;
    }
    
    private function setUnderline($letter,$data){
    
        if(@$data){
            $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);//下划线
        }
            
        return $this;
    }
    
    private function border($letter,$data){
    
        if(@$data){
            $styleThinBlackBorderOutline = array(
                           'borders' => array (
                                 'outline' => array (
                                       'style' => PHPExcel_Style_Border::BORDER_THIN, //设置border样式
                                       'color' => array ('argb' => $data),          //设置border颜色
                                ),
                          ),
                    );
            $this->objExcel->getActiveSheet()->getStyle($letter)->applyFromArray($styleThinBlackBorderOutline);
        }
            
        return $this;
    }
    
    /*
    *合并
    */
    private function mergeCells($letters,$hang,$direction,$merge){
        
        $merge = $merge-1;
        
        if($merge > 0 && $direction){
        
            //print_r($this->letters);
            $l = array_flip($this->letters);
            $ln = $l[$letters];
            
            switch ($direction)
            {
                case 'left':
                    $signal = $this->letters[($ln-$merge)].$hang.':'.$letters.$hang;
                break;
                case 'right':
                    $signal = $letters.$hang.':'.$this->letters[($ln+$merge)].$hang;
                break;
                case 'up':
                    $signal = $letters.($hang-$merge).':'.$letters.$hang;
                break;
                case 'down':
                    $signal = $letters.$hang.':'.$letters.($hang+$merge);
                break;
                default:
                    $signal = '';
            }
            
            if($signal){
                $this->objExcel->getActiveSheet()->mergeCells($signal);
            }
            
        }
        
        return $this;
    }
    
    /*
    *垂直居中
    */
    private function setVertical($letter,$data){
        
        if($data){
            $this->objExcel->getActiveSheet()->getStyle($letter)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $this->objExcel->getActiveSheet()->getStyle($letter)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        return $this;
        
    }
    
    
    
    
    
    
    
    
    
    /*
    *设置颜色
    */
    private function backColor($color){
        
        $array = array(
            'black' => 'FF000000',   //  System Colour #1 - Black
            'white' => 'FFFFFFFF',   //  System Colour #2 - White
            'red'   => 'FFFF0000',   //  System Colour #3 - Red
            'green' => 'FF00FF00',   //  System Colour #4 - Green
            'blue'  => 'FF0000FF',   //  System Colour #5 - Blue
            'yellow'    => 'FFFFFF00',   //  System Colour #6 - Yellow
            'magenta'   => 'FFFF00FF',   //  System Colour #7- Magenta
            'cyan'  => 'FF00FFFF',   //  System Colour #8- Cyan
        );
        if(array_key_exists($color,$array)){
            return $array[$color];
        } else {
            return false;
        }
                    
    }
    /*
    *设置表
    */
    private function tableContent(&$record,&$data){
        $objExcel = $this->objExcel;
        $letters = $this->letters;
        
        if(array_key_exists('merge',$record))
            $merge = $record['merge'];
        else
            $merge = '';
                    
        $hang = 2;
        foreach($data as $k=>$v){
            $i=0;
            $hang++;
            
            foreach($v as $kk=>$vv){
                $this->setCellValue($letters[$i].$hang, $vv);//设置内容
                $this->Appoint($kk,$vv,$letters[$i],$hang,$merge);
                $i++;
            }
            
        }
    }
    
    /*
    *设置表指定样式
    */
    private function Appoint($kk,$vv,$letters,$hang,$merge){
        if(!$merge)return false;
        if(array_key_exists($kk,$merge)){
            $v = $merge[$kk];
            if(array_key_exists('keyword',$v)){
            
                if(strpos($vv,$v['keyword']) > -1){
                    $this->appOintStyle($letters,$hang,$v);
                }
                
            } else {
                $this->appOintStyle($letters,$hang,$v);
            }
        } else if(array_key_exists('all',$merge)){
            $v = $merge['all'];
            if(array_key_exists('keyword',$v)){
            
                if(strpos($vv,$v['keyword']) > -1){
                    $this->appOintStyle($letters,$hang,$v);
                }
                
            } else {
                $this->appOintStyle($letters,$hang,$v);
            }
        }
    }
    
    /*
    *终极样式
    */
    private function appOintStyle($letters,$hang,$v){
    
        $this
                ->setCellValue($letters.$hang,@$v['tableName'])
                ->getColumnDimension($letters,@$v['width'])
                ->setName($letters.$hang,@$v['setName'])
                ->setSize($letters.$hang,@$v['setSize'])
                ->background($letters.$hang,@$v['background'])
                ->textColor($letters.$hang,$this->backColor(@$v['textColor']))
                ->setBold($letters.$hang,@$v['bold'])
                ->setUnderline($letters.$hang,@$v['underline'])
                ->border($letters.$hang,$this->backColor(@$v['borderColor']))
                ->mergeCells($letters,$hang,@$v['direction'],@$v['merge'])
                ->setVertical($letters.$hang,@$v['center']);
    }
    
    /*
    *应为字母列表
    */
    public function letter(){
        return array('A','B','C','D','F','G','H','I','G','K','L','M','N','O','P','Q','R','S','T','U','V','W','H','Y','Z');
    }
    
    /****************************
    *处理数据，排序及删除字段
    */
    private function maniData($record,$data){
        if(!$data)return false;
        if(!is_array($record))return false;//表样式及其他设置
    
        $data = $this->objectToArray($data);//对象转数组
        
        $delKey = (array_key_exists('delKey',$record))?$record['delKey']:'';//是否删除关键字
        $sort = (array_key_exists('sort',$record))?$record['sort']:'';//是否排序
        $data = $this->delSort($data,$delKey,$sort);
        return $data;
    }
    
    /****************************
    *对象转数组
    */
    private function objectToArray($data){
        if(!$data)return false;
        $data = (array)$data;
        
        foreach($data as $k=>$v){
            if(is_object($v) || is_array($v)){
                $data[$k] = (array)$this->objectToArray($v);
            }
        }
        return $data;
    }
    
    /****************************
    *删除键值，并排序
    */
    private function delSort($data,$delKey='',$sort=''){
    
        if(!$data)return false;
        
        $array = array();
        foreach($data as $k=>$v){
        
            //删除数据中的某个键值
            $delData = $this->delData($v,$delKey);
            //按设定键值排序
            $sortData = $this->sortData($delData,$sort);
            $array[$k] = $sortData;
        }
        
        return $array;
        
    }
    
    /****************************
    *删除键值
    */
    public function delData($data,&$delKey){
        if($delKey){
            foreach($delKey as $delVal){
                if(array_key_exists($delVal,$data))//判断键值是否存在
                    unset($data[$delVal]);//清除键名。
            }
        }
        return $data;
    }
    
    /****************************
    *键值排序
    */
    public function sortData($data,&$sort){
        $array = array();
        if($sort){
            if(array_key_exists('keyName',$sort)){
                $keyName = $sort['keyName'];
                if(array_key_exists('reorder',$sort)){
                    if($sort['reorder'] == 'DESC'){
                        krsort($keyName);
                    } else if($sort['reorder'] == 'ASC'){
                        ksort($keyName);
                    }
                }
                foreach($keyName as $vn){
                    $array[$vn] = (array_key_exists($vn,$data))?$data[$vn]:'';
                }
            } else {
                if(array_key_exists('reorder',$sort)){
                    if($sort['reorder'] == 'DESC'){
                        krsort($data);
                    } else if($sort['reorder'] == 'ASC'){
                        ksort($data);
                    }
                    $array = $data;
                }
            }
            
        }
        return $array;
    }
    
    
    
    //导出下载
    private function down(){
        
        if($this->format == '2007'):
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $excel = 'Excel2007';
        else:
            header('Content-Type: application/vnd.ms-excel');
            $excel = 'Excel5';
        endif;
        
        header("Content-Disposition: attachment; filename="$this->fileName"");
        header('Cache-Control: max-age=0');
        
        $objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, $excel);
 
        $objWriter->save('php://output');
        
    }
}
Verwandte Etiketten：
array data gt nbsp this
Quelle：php.cn
Vorheriger Artikel：php模拟登陆的2种实现方法 Nächster Artikel：PHP中排列组合及性能对比
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
function_exists() kann die benutzerdefinierte Funktion nicht ermitteln Funktionstest () {Verwendung der Verwendung durch -Durch -Durch -Durch -Durch -Durch -Durc...
Aus 2024-04-29 11:01:01
0
2
1429
Im übergeordneten Fenster erfolgt keine Ausgabe document.onclick = function(){ window.opener.document.write('Ich bin die Ausgabe des unter...
Aus 2024-04-18 23:52:34
0
1
1284
Automatische PX-Konvertierung in REM-Fehler  <style>html {  font-size: calc(100vw / 3.75);      }body {    Schriftgröße: 0,16rem...
Aus 2024-04-16 09:34:16
0
0
4687
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage