PHP操作Excel

php操作Excel其实就是用了一个PHPExcel类，这个类提供了很多方法，支持生成Excel文件，支持生成报表等等。

首先我们得去官网下载这个PHPExcel类。

百度PHPExcel，搜索结果最上面显示的那个就是PHPExcel的官网（http://phpexcel.codeplex.com/），点击进入，官网首页右侧有一个download按钮，点击下载即可，下载完后解压，找到里面的Classes目录，把这个目录重命名为PHPExcel并且复制放到服务器www根目录下，打开这个目录，里面会有PHPExcel.php以及PHPExcel文件夹，其中PHPExcel.php这个文件就是我们日后要调用的生成Excel的关键文件。注意里面的PHPExcel文件夹不能删，因为PHPExcel.php也依赖了其中的某些重要文件。

现在我们就可以开始使用php操作excel了。

一、入门

用过Excel的伙伴们都知道，要生成一个Excel文件，首先得新建一个excel表格，然后创建sheet(内置表)，然后向sheet里填充数据，最后保存文件。那么同理，用php来生成一个excel也是相同的步骤。

首先得实例化一个PHPExcel类，这就相当于新建一个表格，然后使用该类的createSheet()方法创建sheet，用setActiveSheetIndex()方法设置某个sheet为活动状态，通过该sheet的下标设置。用getActiveSheet()方法获得当前处于活动状态的sheet，然后用该sheet的setCellValue()方法填充数据。最后使用PHPExcel_IOFactory类的save()方法保存文件。如下图：

具体使用：

在PHPExcel.php的同级目录下创建demo.php，

<?phprequire './PHPExcel.php';$PHPExcel=new PHPExcel();   //实例化一个PHPExcel类，相当于在桌面上创建一个表格$sheet=$PHPExcel->getActiveSheet();  //获得当前获得sheet的操作对象$sheet->setTitle('demo');   //设置名称$sheet->setCellValue("A1","姓名")->setCellValue('B1',"分数");   //填充数据$sheet->setCellValue("A2","张三")->setCellValue('B2',"90");   //填充数据$sheet->setCellValue("A3","李四")->setCellValue('B3',"80");   //填充数据$writer=PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');   //按照指定格式生成Excel文件$writer->save('./demo.xlsx');   // 保存到指定目录下

运行后，在当前目录下就会生成一个excel文件。

代码解释：使用sheet的setTitle()方法设置sheet的名称，使用setCellValue()方法填充数据。这里的A1就是对应于excel的第A列第1行，同理B2就是第B列第2行。如下图。

调用PHPExcel_IOFactory的静态方法createWriter生成excel文件，第一个参数是PHPExcel资源句柄，第二个的参数是excel的格式，返回一个文件写入句柄$writer，最后调用$writer的save方法保存到指定路径，参数是路径。

二、获取数据库中的数据并且导出到excel里

首先创建一个数据库，假定创建phpexcel数据库，再创建一张user表，表中有id，username，score，class，grade等5个字段，并且插入以下记录，

假定现在的业务需求是制作excel表，该表有3张sheet，每个sheet对应一个年级，里面放该年级学生的信息。

那么我们可以先在PHPExcel.php的同级目录下分别创建dbconfig.php（存储数据库的配置），DB.class.php（从数据获取数据），test.php（测试），

dbconfig.php：

<?phpreturn array(    'host'=>'127.0.0.1',    'user'=>'root',    'password'=>'',    'database'=>'phpexcel',    'charset'=>'utf8');

DB.class.php：

<?phpclass DB{    private  $conn=null;    /*连接数据库*/    public function __construct($config){        $this->conn=@mysql_connect($config['host'],$config['user'],$config['password']);        mysql_select_db($config['database'],$this->conn);        mysql_query("set names ".$config['charset'],$this->conn);    }    /*返回结果二维数组*/    public function getResult($sql)    {        $result=mysql_query($sql,$this->conn);        $res=array();        while ($row=mysql_fetch_assoc($result)){            $res[]=$row;        }        return $res;    }    /*根据年级查找学生信息*/    public function getDataByGrade($grade){        $sql="select username,score,class,grade from user where grade=".$grade." order by score desc";        $res=$this->getResult($sql);        return $res;    }}

test.php:

<?phprequire_once './DB.class.php';require_once "./PHPExcel.php";$config=include './dbconfig.php';$DB=new DB($config); //实例化DB类，连接数据库$excel=new PHPExcel();for($i=1;$i<=3;$i++){   //三个年级，故需要3张sheet    if($i>1){   //因为有一个默认的内置表        $excel->createSheet();  //创建新的两张内置表    }    $excel->setActiveSheetIndex($i-1);  //把新创建的sheet设置为当前活动sheet    $sheet=$excel->getActiveSheet();   //获得当前活动的sheet    $sheet->setTitle($i.'年级');   //设置标题    $data=$DB->getDataByGrade($i);  //根据年级查找数据    $sheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");//填充第一行的数据    for($j=0;$j<count($data);$j++){        $sheet->setCellValue("A".($j+2),$data[$j]['username'])->setCellValue("B".($j+2),$data[$j]['score'])->setCellValue("C".($j+2),$data[$j]['class']);//填充数据    }}$writer=PHPExcel_IOFactory::createWriter($excel,'Excel2007');$writer->save('./demo.xlsx');

运行test.php，即可在当前目录下获得从数据库中导出的excel。

三、将excel输出到浏览器以便下载。

只需把test.php的最后两行换一下即可，如下：

<?phprequire_once './DB.class.php';require_once "./PHPExcel.php";$config=include './dbconfig.php';$DB=new DB($config); //实例化DB类，连接数据库$excel=new PHPExcel();for($i=1;$i<=3;$i++){   //三个年级，故需要3张sheet    if($i>1){   //因为有一个默认的内置表        $excel->createSheet();  //创建新的两张内置表    }    $excel->setActiveSheetIndex($i-1);  //把新创建的sheet设置为当前活动sheet    $sheet=$excel->getActiveSheet();   //获得当前活动的sheet    $sheet->setTitle($i.'年级');   //设置标题    $data=$DB->getDataByGrade($i);  //根据年级查找数据    $sheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");//填充第一行的数据    for($j=0;$j<count($data);$j++){        $sheet->setCellValue("A".($j+2),$data[$j]['username'])->setCellValue("B".($j+2),$data[$j]['score'])->setCellValue("C".($j+2),$data[$j]['class']);//填充数据    }}<pre name="code" class="php">       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');//禁止缓存}

四、为Excel设置样式

完善DB.class.php，

<?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;}}?>

然后test.php稍作修改即可。

<?php $dir=dirname(__FILE__);//查找当前脚本所在路径require $dir."/db.php";//引入mysql操作类文件require './PHPExcel.php';//引入PHPExcel        $config=include './dbconfig.php';        $db=new DB($config);$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","姓名")->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']);//填充学生信息//$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."/demo.xls");//保存文件      /*根据下标获得单元格所在列位置*/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;}?>

生成的excel效果如下：

解析如下：

1.设置单元格居中：

$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

2.设置默认字体大小和格式：

$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");

3.设置第二行字体大小和加粗

$objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);

4.设置默认行高

$objSheet->getDefaultRowDimension()->setRowHeight(30);

5.设置第二行行高

$objSheet->getRowDimension(2)->setRowHeight(50);

6.合并单元格

$objSheet->mergeCells("A3:B3");//A3和B3合并

7.填充背景颜色

$objSheet->getStyle("A3:B3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//

8.设置边框

$gradeBorder=getBorderStyle("c144b1");//获取年级边框样式代码$objSheet->getStyle("A3:B3")->applyFromArray($gradeBorder);function getBorderStyle($color){   $styleArray = array(      'borders' => array('outline' => array(   'style' => PHPExcel_Style_Border::BORDER_THICK,   'color' => array('rgb' => $color),),      ), );return $styleArray;}

9.超长数字显示，不显示科学计数

$objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValueExplicit($scoreIndex.$j,$val['score']."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充学生信息

四、php操作Excel生成图形报表

<?php require "../PHPExcel/PHPExcel.php";//引入PHPExcel$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("./chart.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');//禁止缓存}?>

效果截图：

五、获取excel里的数据

首先得创建一张excel，名称为reader.xls，3张内置sheet，如下：

1.把所有的数据暴力地放到一个数组里面。

<?php header("Content-Type:text/html;charset=utf-8");require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件$filename="./reader.xls";$objPHPExcel=PHPExcel_IOFactory::load($filename);//加载文件$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheetfor($i=0;$i<$sheetCount;$i++){      $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据全部放入到数组中      print_r($data);}?>

2.逐行逐列读取数据

<?php header("Content-Type:text/html;charset=utf-8");require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件$filename="./reader.xls";$objPHPExcel=PHPExcel_IOFactory::load($filename);//加载文件foreach($objPHPExcel->getWorksheetIterator() as $sheet){ //循环取sheet     foreach($sheet->getRowIterator() as $row){//逐行处理if($row->getRowIndex()<2)  {  continue; }     //从第二行开始读取foreach($row->getCellIterator() as $cell){//逐列读取$data=$cell->getValue();//获取单元格数据echo $data." ";}echo '<br/>';     }     echo '<br/>';}?>

2.部分加载

例如：只读取二年级和三年级的数据

<?php header("Content-Type:text/html;charset=utf-8");require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件$filename="./reader.xls";$fileType=PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用$objReader=PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象$sheetName=array("2年级","3年级");$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet$objPHPExcel=$objReader->load($filename);//部分加载文件foreach($objPHPExcel->getWorksheetIterator() as $sheet){//循环取sheetforeach($sheet->getRowIterator() as $row){//逐行处理if($row->getRowIndex()<2)  {  continue;  }foreach($row->getCellIterator() as $cell){//逐列读取    $data=$cell->getValue();//获取单元格数据    echo $data." ";}echo '<br/>';}echo '<br/>';}

代码下载：https://github.com/lensh/PHP-Excel