PHPExcel之生成xlsx并下载（一）

PHPExcel提供了一个强大的操作表格的php类库，之前都是copy使用，简单的使用，对于复杂的表格的处理还是难以上手。

一，针对PHPExcel是封闭好的类开，一般有一个基本的类需要包含或者导入到当前的业务代码中，这个类就是PHPExcel.php，针对这个类，大家只需要关注构造函数那堆初始化各种操作对象。

public function __construct()
    {
        $this->uniqueID = uniqid();
        $this->calculationEngine = new PHPExcel_Calculation($this);
 
        //sheet操作类
        $this->workSheetCollection = array();
        $this->workSheetCollection[] = new PHPExcel_Worksheet($this);
        $this->activeSheetIndex = 0;
 
        //表格属性操作类
        $this->properties = new PHPExcel_DocumentProperties();
 
        // Create document security
        $this->security = new PHPExcel_DocumentSecurity();
 
        // Set named ranges
        $this->namedRanges = array();
 
        // Create the cellXf supervisor
        $this->cellXfSupervisor = new PHPExcel_Style(true);
        $this->cellXfSupervisor->bindParent($this);
 
        // Create the default style
        $this->addCellXf(new PHPExcel_Style);
        $this->addCellStyleXf(new PHPExcel_Style);
    }

　　常用的的基本就是上面2个，一个属性设置类，一堆的set，相对应的一堆的get方法。一个是sheet操作类，这个是主要的内容操作处理类。

二，下面一个简单的例子

<?php
/**
 * PHPExcel应用之一 写xlxs文件 并提供下载
**/
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel.php';
$objExcel = new PHPExcel();
 
//xlsx相关属性操作方法  关联类 PHPExcel/Classes/PHPExcel/DocumnentProperties.php
$objExcel->getProperties()->setCreator("author")                  //设置作者
                            ->setLastModifiedBy("last save author")  //最后一次保存者
                            ->setTitle("title")                      //标题
                            ->setSubject("subject")                  //主题
                            ->setDescription("remark")               //备注
                            ->setKeywords("mark")                    //标记
                            ->setCategory("category");               //类别
                             
                             
//对sheet cell进行操作
$objExcel->setActiveSheetIndex(0)                //会返回worksheet对象 关联类 PHPExcel/Classes/PHPExcel/Worksheet.php
            ->setCellValue('A1', 'A1')
            ->setCellValue('B1', 'B1')
            ->setCellValue('C1', 'C1')
            ->setCellValue('D1', 'D1');
             
$objExcel->setActiveSheetIndex(0)                //会返回worksheet对象 关联类 PHPExcel/Classes/PHPExcel/Worksheet.php
            ->setCellValue('A2', 'A2')
            ->setCellValue('B2', 'B2')
            ->setCellValue('C2', 'C2')
            ->setCellValue('D2', 'D2');
             
             
//设置sheet标题
$objExcel->getActiveSheet()->setTitle('the first sheet');
 
//设置为第一个sheet为活动状态
$objExcel->setActiveSheetIndex(0);
 
//保存
$filename = "test.xlsx";
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
$objWriter->save($filename);
 
//如果生成并提供下载
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename);
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
     
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
$objWriter->save('php://output');

针对表格的各种属性，大家可以在生成的xlsx上右键属性，详情里看到

三，业务中一般是从DB或者其它为数据类源，例子如下只用数据那一块即可

 1 //数据类源为业务DB select id,name,age,address from info
 2 //模拟数据
 3 $data = array(
 4     array("id"=>1,"name"=>"张一","age"=>1,"address"=>"广东1"),
 5     array("id"=>2,"name"=>"张二","age"=>2,"address"=>"广东2"),
 6     array("id"=>3,"name"=>"张三","age"=>3,"address"=>"广东3"),
 7     array("id"=>4,"name"=>"张四","age"=>4,"address"=>"广东4"),
 8     array("id"=>5,"name"=>"张五","age"=>5,"address"=>"广东5"),
 9 );
10 
11 //设置标题行
12 $objExcel->setActiveSheetIndex(0)
13             ->setCellValue('A1','ID')
14             ->setCellValue('B1','姓名')
15             ->setCellValue('C1','年龄')
16             ->setCellValue('D1','住址');
17 
18 //内容循环操作即可 从第二行开始
19 $j = 2;
20 foreach($data as $key=>$value){
21     $objExcel->setActiveSheetIndex(0)                
22             ->setCellValue('A'.$j, $value['id'])
23             ->setCellValue('B'.$j, $value['name'])
24             ->setCellValue('C'.$j, $value['age'])
25             ->setCellValue('D'.$j, $value['address']);
26             
27     $j++;
28 }

生生成的效果图如下：

四，假设要对写入的某一列加一些效果，可以用PHPExcel的富文本类PHPExcel_RichText.php 可以对cell时行加粗，倾斜，颜色的处理

 1 foreach($data as $key=>$value){
 2     //设置某列为富文本 加粗 倾斜 颜色
 3     $objRichText = new PHPExcel_RichText();
 4     $objPayable = $objRichText->createTextRun($value['address']);
 5     $objPayable->getFont()->setBold(true);
 6     $objPayable->getFont()->setItalic(true);
 7     $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
 8     
 9     $objExcel->setActiveSheetIndex(0)                
10             ->setCellValue('A'.$j, $value['id'])
11             ->setCellValue('B'.$j, $value['name'])
12             ->setCellValue('C'.$j, $value['age'])
13             ->setCellValue('D'.$j, $objRichText);
14             
15     $j++;
16 }