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个sheetfor ($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