phpexcel导出功能
web开发的时候，经常会遇到需要将数据库中某些表单导出到excel文件中的问题。我在运用yii框架做web开发过程中，发现有个应用为phpexcel，该应用能够实现excel文件的导入导出，刚好能够满足需要的要求。一下是自己应用 phpexcel 做数据导出的记录。
1、首先下载该应用，github链接为：https://github.com/phpoffice/phpexcel
2、在 protected/extensions 路径下建立 phpexcel 目录，将下载的 phpexcel 文件解压。
3、将解压后的classes目录下所有内容，拷贝放到protected/extensions/phpexcel 目录下
4、在控制器文件 xxx.controller.php 中,先引入相关文件
getproperties()->setcreator(maarten balliauw) ->setlastmodifiedby(maarten balliauw) ->settitle(office 2007 xlsx test document) ->setsubject(office 2007 xlsx test document) ->setdescription(test document for office 2007 xlsx, generated using php classes.) ->setkeywords(office 2007 openxml php) ->setcategory(test result file); //$objphpexcel->getactivesheet()->mergecells('a1:g1'); //$objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //$objphpexcel->getactivesheet()->getcolumndimension('c')->setautosize(true); //$objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(20); //$objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(20); $subjects = subject::model()->findall(headteacher=:name, array(:name => yii::app()->session['name'])); //找出相关课程组 foreach ($subjects as $item1) { $criteria = new cdbcriteria; // 创建cdbcriteria对象 $criteria->addcondition(suid = :id); $criteria->params[':id'] = $item1->id; $criteria->select = '*'; //按照返回参数搜索选题信息 if ($option == 1) { $criteria->order = 'cid'; //$criteria -> limit = 3; $b = selectcourse::model()->findall($criteria); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a1', '选题情况') ->setcellvalue('a2', '学生id') ->setcellvalue('b2', '学生姓名') ->setcellvalue('c2', '课程名称') ->setcellvalue('d2', '题目名称') ->setcellvalue('e2', '选题时间'); $count = 2; foreach ($b as $item2) { $count += 1; $l1 = a . $count; $l2 = b . $count; $l3 = c . $count; $l4 = d . $count; $l5 = e . $count; $objphpexcel->setactivesheetindex(0) ->setcellvalue($l1, $item2->sid) ->setcellvalue($l2, student::model()->find(array('condition' => 'id=' . $item2->sid,))->name) ->setcellvalue($l3, $item1->name) ->setcellvalue($l4, course::model()->find(array('condition' => 'cid=' . $item2->cid,))->cname) ->setcellvalue($l5, $item2->apply_time); } } if ($option == 2) { $criteria->order = 'sid'; //$criteria -> limit = 3; $b = selectcourse::model()->findall($criteria); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a1', '选题情况') ->setcellvalue('a2', '学生id') ->setcellvalue('b2', '学生姓名') ->setcellvalue('c2', '课程名称') ->setcellvalue('d2', '题目名称') ->setcellvalue('e2', '选题时间'); $count = 2; foreach ($b as $item2) { $count += 1; $l1 = a . $count; $l2 = b . $count; $l3 = c . $count; $l4 = d . $count; $l5 = e . $count; $objphpexcel->setactivesheetindex(0) ->setcellvalue($l1, $item2->sid) ->setcellvalue($l2, student::model()->find(array('condition' => 'id=' . $item2->sid,))->name) ->setcellvalue($l3, $item1->name) ->setcellvalue($l4, course::model()->find(array('condition' => 'cid=' . $item2->cid,))->cname) ->setcellvalue($l5, $item2->apply_time); } } } // rename sheet $objphpexcel->getactivesheet()->settitle('学生选题信息'); // set active sheet index to the first sheet, so excel opens this as the first sheet $objphpexcel->setactivesheetindex(0); // redirect output to a client’s web browser (excel5) header('content-type: application/vnd.ms-excel'); header('content-disposition: attachment;filename=学生选题信息.xls'); header('cache-control: max-age=0'); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); $objwriter->save('php://output'); }
6、不要忘了在用户访问控制里面添加动作download
public function accessrules(){array('allow', // allow admin user to perform 'admin' and 'delete' actions 'actions' => array('download'), 'roles' => array('...'), ),}
7、在对应的view文件中，编写链接
$option)); ?>
8、完成将数据以excel文件导出。
以上是自己对phpexcel的一个应用，因为是初次接触，用的也不熟练，难免有一些不当，望大家指出，共同进步！