PHPExcel探索

来源：互联网发布：大学生网络借贷论文编辑：程序博客网时间：2024/05/01 10:10

PHPExcel导出到Excel

前提，准备工作

1、PHP版本5.3以上
2、官网下载稳定版本的PHPExcel
官网地址：http://phpexcel.codeplex.com/
以下均以PHPExcel_1.8.0 稳定版为学习版本

插曲：当我用在官网下载的1.8.0版本练习时，发现与PHP7不能兼容，经Goole后发现要下载Github上的最新版本，附地址：https://github.com/PHPOffice/PHPExcel

这里写图片描述
上面图示是下载的好的PHPExcel，解压后，拷贝Classes文件夹到项目中，并更名为PHPExcel。以后需要使用的时候，直接实例化PHPExcel.php中的PHPExcel即可使用

wamp启动失败处理方法

控制面板\系统和安全\管理工具\事件查看器\windows日志\应用程序

可以到上面的路径中去查看是否报错信息，或者在apache中查看错误日志

简单的使用PHPExcel写入Excel文件

步骤解析：
这里写图片描述

<?php    $dir = dirname(__FILE__);    require $dir.'/PHPExcel/PHPExcel.php';    $objPHPExcel = new PHPExcel();    $objSheet = $objPHPExcel -> getActiveSheet();//获得当前活动sheet的操作对象    $objSheet -> setTitle('demo');//给当前活动sheet设置名称    //==第一种插入数据方式/*  $objSheet -> setCellValue('A1','姓名') -> setCellValue('B1','分数');//给当前活动sheet填充数据    $objSheet -> setCellValue('A2','张三') -> setCellValue('B2','188200787878'); */    //==第二种插入数据方式    $array = array(        array(),        array('','姓名','分数'),        array('','李四','89'),        array('','王五','30')    );    $objSheet -> fromArray($array);//直接加载数据块填充数据，这种做法，当数据量大的时候，不可取    //=============    $objWrite = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//指定生成Excel的格式    $objWrite -> save($dir.'/demo.xls');//指定生成的文件名?>

使用PHPExcel输出数据库数据并导出到Excel

sql文件：

/*Navicat MySQL Data TransferSource Server         : 127.0.0.1Source Server Version : 50714Source Host           : 127.0.0.1:3306Source Database       : phpexcelTarget Server Type    : MYSQLTarget Server Version : 50714File Encoding         : 65001Date: 2016-11-09 00:27:01*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `username` varchar(30) NOT NULL DEFAULT '' COMMENT '姓名',  `score` decimal(3,1) unsigned NOT NULL DEFAULT '0.0' COMMENT '分数',  `class` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '班级',  `grade` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '班级',  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', '刘册', '67.0', '1', '1');INSERT INTO `user` VALUES ('2', '菲菲', '87.0', '2', '3');INSERT INTO `user` VALUES ('3', '刘莉莉', '56.0', '3', '2');INSERT INTO `user` VALUES ('4', '刘丽华', '99.0', '2', '1');INSERT INTO `user` VALUES ('5', '曹莉莉', '45.0', '1', '2');INSERT INTO `user` VALUES ('6', '范冰冰', '89.0', '3', '3');INSERT INTO `user` VALUES ('7', '你大爷', '68.0', '1', '3');

export.php

<?php    $dir = dirname(__FILE__);    require $dir.'/db.php';    require $dir.'/PHPExcel/PHPExcel.php';    /**    *   $phpexcel=array(            'host'=>"127.0.0.1",            "username"=>"root",            "password"=>"",            "database"=>"phpexcel",            "charset"=>"utf8"        );    */    $db = new db($phpexcel);//$phpexcel为数据库配置文件    $objPHPExcel = new PHPExcel();    //以下操作目的：    //将user表中的数据，按照年级创建sheet（现在有3个年级）    //    for($i=1;$i <= 3 ; $i++)    {        //实例化PHPExcel时，已经自动创建了第一张sheet了，所以这里从第二张sheet开始创建        if($i > 1)        {            $objPHPExcel ->createSheet();//创建新的内置表        }        //sheet表的索引默认从0开始，所以这里是 $i - 1        $objPHPExcel -> setActiveSheetIndex($i-1);//把新创建的sheet设定为当前活动sheet        $objSheet = $objPHPExcel -> getActiveSheet();//获取当前活动sheet        $objSheet -> setTitle($i.'年级');//给当前活动sheet起个名称        //$sql="select username,score,class from user where grade=".$grade." order by score desc";        $data = $db -> getDataByGrade($i);//数据库查询每个年级的学生数据        $objSheet ->setCellValue('A1','姓名') -> setCellValue('B1','分数') -> setCellValue('C1','班级');//填充数据        $j = 2;        foreach($data as $key => $val)        {            $objSheet -> setCellValue('A'.$j,$val['username']) -> setCellValue('B'.$j,$val['score']) ->setCellValue('C'.$j,$val['class'].'班');            $j++;        }    }    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//指定生成的Excel的格式，这里是Excel2003    //输出方式一：保存到指定目录    //$objWriter -> save($dir.'/export_1.xls');//保存文件    //输出方式二：输出到浏览器    //在下载的E:\wamp\wamp64\www\phpexecl\PHPExcel-1.8\Examples目录下有很多学习案例，可以参考    browser_export('Excel5','output_Excel2003.xls');    //browser_export('Excel2007','output_Excel2007');    $objWriter -> save('php://output');//这句不写，表格是不会有内容的    function browser_export($type,$filename)    {        if($type == 'Excel5')        {            header('Content-Type: application/vnd.ms-excel');//以Excel2003格式输出        }elseif($type == 'Excel2007'){            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//以Excel2007格式输出        }        header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器输出文件的名称        header('Cache-Control: max-age=0');     //禁用浏览器缓存    }   ?>

导出的结果如下图：
这里写图片描述

用PHPExcel合并单元格、文字居中、设置字体、背景颜色、边框、单元格内换行、格式控制（电话号码等长字符不以科学计算的方式显示）

style.php

<?php     error_reporting(0);    ini_set('display_errors', false);    $dir=dirname(__FILE__);//查找当前脚本所在路径    require $dir."/db.php";//引入mysql操作类文件    require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel    $db=new db($phpexcel);//实例化db类 连接数据库    $objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel    $objSheet=$objPHPExcel->getActiveSheet();//获得当前活动单元格    //开始本节课代码编写    $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中    $objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");//设置默认字体大小和格式    $objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);//设置第二行字体大小和加粗    $objSheet->getStyle("A3:Z3")->getFont()->setSize(16)->setBold(true);//设置第三行字体大小和加粗    $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高    $objSheet->getRowDimension(2)->setRowHeight(50);//设置第二行行高    $objSheet->getRowDimension(3)->setRowHeight(40);//设置第三行行高    $gradeInfo=$db->getAllGrade();//查询所有的年级    $index=0;    foreach($gradeInfo as $g_k=>$g_v){        $gradeIndex=getCells($index*2);//获取年级信息所在列        $objSheet->setCellValue($gradeIndex."2","高".$g_v['grade']);        $classInfo=$db->getClassByGrade($g_v['grade']);//查询每个年级所有的班级        foreach($classInfo as $c_k=>$c_v){            $nameIndex=getCells($index*2);//获得每个班级学生姓名所在列位置            $scoreIndex=getCells($index*2+1);//获得每个班级学生分数所在列位置            $objSheet->mergeCells($nameIndex."3:".$scoreIndex."3");//合并每个班级的单元格            $objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充班级背景颜色            $classBorder=getBorderStyle("445cc1");//获取班级边框样式代码            $objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->applyFromArray($classBorder);//设置每个班级的边框            $info=$db->getDataByClassGrade($c_v['class'],$g_v['grade']);//查询每个班级的学生信息            $objSheet->setCellValue($nameIndex."3",$c_v['class']."班");//填充班级信息                $objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//设置文字自动换行            $objSheet->setCellValue($nameIndex."4","姓名\n换行")->setCellValue($scoreIndex."4","分数");             $objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式            $j=5;//从第五行开始填充姓名和分数            foreach($info as $key=>$val){                $objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValue($scoreIndex.$j,$val['score']."21312321321321321321");//填充学生信息                //$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValueExplicit($scoreIndex.$j,$val['score']."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充学生信息                $j++;            }            $index++;        }        $endGradeIndex=getCells($index*2-1);//获得每个年级的终止单元格        $objSheet->mergeCells($gradeIndex."2:".$endGradeIndex."2");//合并每个年级的单元格        $objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('c1b644');//填充年级背景颜色        $gradeBorder=getBorderStyle("c144b1");//获取年级边框样式代码        $objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->applyFromArray($gradeBorder);//设置每个年级的边框    }    $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件    //$objWriter->save($dir."/export_1.xls");//保存文件    browser_export('Excel5','browser_excel03.xls');//输出到浏览器    $objWriter->save("php://output");    function browser_export($type,$filename){        if($type=="Excel5"){                header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件        }else{                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件        }        header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称        header('Cache-Control: max-age=0');//禁止缓存    }/****根据下标获得单元格所在列位置**/    function getCells($index){        $arr=range('A','Z');        //$arr=array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,....Z);        return $arr[$index];    }/****获取边框样式代码**/    function getBorderStyle($color){            $styleArray = array(                'borders' => array(                    'outline' => array(                        'style' => PHPExcel_Style_Border::BORDER_THICK,                        'color' => array('rgb' => $color),                    ),                ),            );            return $styleArray;    }?>

PHPExcel生成图形报表

<?php     error_reporting(0);    ini_set('display_errors', false);    $dir=dirname(__FILE__);//查找当前脚本所在路径    require $dir."/db.php";//引入mysql操作类文件    require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel    $db=new db($phpexcel);//实例化db类 连接数据库    $objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel    $objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet    /**本节课程代码编写开始**/    $array=array(            array("","一班","二班","三班"),            array("不及格",20,30,40),            array("良好",30,50,55),            array("优秀",15,17,20)    );//准备数据    $objSheet->fromArray($array);//直接加载数组填充进单元格内    //开始图表代码编写    $labels=array(        new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$B$1',null,1),//一班        new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$1',null,1),//二班        new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$D$1',null,1),//三班    );//先取得绘制图表的标签    $xLabels=array(        new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2:$A$4',null,3)//取得图表X轴的刻度    );    $datas=array(        new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$B$2:$B$4',null,3),//取一班的数据        new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$2:$C$4',null,3),//取二班的数据        new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$D$2:$D$4',null,3)//取三班的数据    );//取得绘图所需的数据    $series=array(        new PHPExcel_Chart_DataSeries(            PHPExcel_Chart_DataSeries::TYPE_LINECHART,            PHPExcel_Chart_DataSeries::GROUPING_STANDARD,            range(0,count($labels)-1),            $labels,            $xLabels,            $datas        )    );//根据取得的东西做出一个图表的框架    $layout=new PHPExcel_Chart_Layout();    $layout->setShowVal(true);    $areas=new PHPExcel_Chart_PlotArea($layout,$series);    $legend=new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);    $title=new PHPExcel_Chart_Title("高一学生成绩分布");    $ytitle=new PHPExcel_Chart_Title("value(人数)");    $chart=new PHPExcel_Chart(        'line_chart',        $title,        $legend,        $areas,        true,        false,        null,        $ytitle    );//生成一个图标    $chart->setTopLeftPosition("A7")->setBottomRightPosition("K25");//给定图表所在表格中的位置    $objSheet->addChart($chart);//将chart添加到表格中    $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件    $objWriter->setIncludeCharts(true);    //$objWriter->save($dir."/export_1.xls");//保存文件    browser_export('Excel2007','browser_chart.xlsx');//输出到浏览器    $objWriter->save("php://output");    function browser_export($type,$filename){        if($type=="Excel5"){                header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件        }else{                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件        }        header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称        header('Cache-Control: max-age=0');//禁止缓存    }?>

附数据库操作类

db.php

<?php     require dirname(__FILE__)."/dbconfig.php";//引入配置文件    class db{        public $conn=null;        public function __construct($config){//构造方法 实例化类时自动调用                 $this->conn=mysql_connect($config['host'],$config['username'],$config['password']) or die(mysql_error());//连接数据库                mysql_select_db($config['database'],$this->conn) or die(mysql_error());//选择数据库                mysql_query("set names ".$config['charset']) or die(mysql_error());//设定mysql编码        }        /**        **根据传入sql语句 查询mysql结果集        **/        public function getResult($sql){            $resource=mysql_query($sql,$this->conn) or die(mysql_error());//查询sql语句            $res=array();            while(($row=mysql_fetch_assoc($resource))!=false){                $res[]=$row;            }            return $res;        }        /**        ** 根据传入年级数 查询每个年级的学生数据        **/        public function getDataByGrade($grade){            $sql="select username,score,class from user where grade=".$grade." order by score desc";            $res=self::getResult($sql);            return $res;        }        /**        ** 查询所有的年级        **/        public function getAllGrade(){            $sql="select distinct(grade) from user  order by grade asc";            $res=$this->getResult($sql);            return $res;        }        /**        **根据年级数查询所有的班级        **/        public function getClassByGrade($grade){            $sql="select distinct(class) from user where grade=".$grade." order by class asc";            $res=$this->getResult($sql);            return $res;        }        /**        **根据年级数班级数查询学生信息        **/        public function getDataByClassGrade($class,$grade){            $sql="select username,score from user where class=".$class." and grade=".$grade." order by score desc";            $res=$this->getResult($sql);            return $res;        }    }?>

dbconfig.php

<?php     $phpexcel=array(            'host'=>"127.0.0.1",            "username"=>"root",            "password"=>"",            "database"=>"phpexcel",            "charset"=>"utf8"    );?>

PHP导入Excel

这里写图片描述
使用PHPExcel可以选择将Excel全部导入或部分导入

<?php header("Content-Type:text/html;charset=utf-8");$dir=dirname(__FILE__);//找到当前脚本所在路径require $dir."/PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件$filename=$dir."/export_1.xls";//选择要加载的文件$fileType=PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用(Excel5、Excel2007)$objReader=PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象$sheetName=array("2年级","3年级");$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet$objPHPExcel=$objReader->load($filename);//加载文件//==加载Excel文件的所有sheet并读取//==这种加载所有的方式，比较耗内存，如果文件很大，则很容易出问题/**$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheetfor($i=0;$i<$sheetCount;$i++){    $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中    print_r($data);}**///==end====//==部分加载//==这种加载方式比较灵活，可以指定加载某一个sheet的内容foreach($objPHPExcel->getWorksheetIterator() as $sheet){       //循环取sheet        foreach($sheet->getRowIterator() as $row)        {       //逐行处理                if($row->getRowIndex()<2)//从第二行开始读取，第一行一般是title                {                    continue;                }                foreach($row->getCellIterator() as $cell)                {                       //逐列读取                    $data=$cell->getValue();//获取单元格数据                    echo $data." ";                }                echo '<br/>';        }        echo '<br/>';}exit;?>

启用PHPExcel缓存

这里写图片描述

使用PHPExcel可以根据实际情况启用不同的缓存机制。只需要把启用的缓存的代码写在操作之前就可以了。

比较常用和比较好的方式是存放于Memcache里
当脚本结束时，所有的数据都会从memcache清空（忽略缓存时间），不能使用该机制进行持久存储。
以下为启用Memcached的缓存写法：

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;          $cacheSettings = array( 'memcacheServer'=> 'localhost',                              'memcachePort' => 11211,                              'cacheTime' => 600 );           PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

1 0