138. PHPExcel 操作

官网下载：http://phpexcel.codeplex.com/

---

---

1.demo.php

<?php

$dir = dirname(__FILE__);

require $dir . DIRECTORY_SEPARATOR . 'PHPExcel.php';

$objExcel = new PHPExcel();//实例化一个phpexcel类，等同于创建一个 excel 表格

$objSheet = $objExcel->getActiveSheet();//获取当前活动的 sheet 对象
$objSheet->setTitle('demo'); //给当前活动sheet 设置名称

// 1.填充数据
/*$objSheet->setCellValue('A1','姓名')->setCellValue('B1','分数');//给当前活动sheet填充数据
$objSheet->setCellValue('A2','张三')->setCellValue('B2',50);*/

// 2.填充数据
$arr = array(
    array('姓名','分数'),
    array('李四',60),
    array('王五',70),
);

$objSheet->fromArray($arr);//直接从数组加载数据

//PHPExcel_IOFactory::createWriter($objExcel,'Excel5'); // excel.xls
$objWriter = PHPExcel_IOFactory::createWriter($objExcel,'Excel2007'); // excel.xlss 按照指定格式生成excel
$objWriter->save($dir . '/demo_1.xlsx');

---

2.从数据库取数据

<?php

$dir = dirname(__FILE__);

require $dir . DIRECTORY_SEPARATOR . 'PHPExcel.php';

$objExcel = new PHPExcel();//实例化一个phpexcel类，等同于创建一个 excel 表格

$dsn = "mysql:host=localhost;dbname=test";

$db = new PDO($dsn,'root','123456');

// 按年级分类，3个sheet
for ($i = 1; $i <= 3; $i++) {

    if ($i > 1) {
        $objExcel->createSheet();//创建新的sheet
    }
    $objSheet = $objExcel->setActiveSheetIndex($i-1);
    $objSheet->setTitle($i . '_年级');

    $objSheet->setCellValue('A1','Id')->setCellValue('B1','Username')
        ->setCellValue('C1','score')->setCellValue('D1','Class')->setCellValue('E1','Grade');

    $sql = "select * from user where grade={$i}";
    $resutl = $db->query($sql,PDO::FETCH_ASSOC);
    $arr = $resutl->fetchAll();

    $j = 2;
    foreach ($arr[0] as $val) {
        $objSheet->setCellValue('A'.$j,$val['id'])->setCellValue('B'.$j,$val['username'])
            ->setCellValue('C'.$j,$val['score'])->setCellValue('D'.$j,$val['class'])->setCellValue('E'.$j,$val['grade']);
        $j++;
    }

    $objWriter = PHPExcel_IOFactory::createWriter($objExcel,'Excel2007'); // excel.xlss 按照指定格式生成excel
    $objWriter->save($dir . '/demo_a_'.$i.'.xlsx');
}

---

输出到浏览器：

<?php 
    $dir=dirname(__FILE__);//查找当前脚本所在路径
    require $dir."/db.php";//引入mysql操作类文件
    require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
    $db=new db($phpexcel);//实例化db类 连接数据库
    $objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel
    for($i=1;$i<=3;$i++){
        if($i>1){
            $objPHPExcel->createSheet();//创建新的内置表
        }
        $objPHPExcel->setActiveSheetIndex($i-1);//把新创建的sheet设定为当前活动sheet
        $objSheet=$objPHPExcel->getActiveSheet();//获取当前活动sheet
        $objSheet->setTitle($i."年级");//给当前活动sheet起个名称
        $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文件
    //$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');//禁止缓存
    }

?>

---

---

PHPExcel 读取行

http://blog.csdn.net/helencoder/article/details/48541633

http://blog.csdn.net/ning521513/article/details/55257925