thinkPHP+phpexcel实现excel报表输出功能示例
本文实例讲述了thinkphp+phpexcel实现excel报表输出功能。分享给大家供大家参考,具体如下:
准备工作:
1.下载phpexcel1.7.6类包;
2.解压至tp框架的thinkphp\vendor目录下,改类包文件夹名为phpexcel176,目录结构如下图;
编写代码(以一个订单汇总数据为例):
1. 创建数据库及表;
2. 创建tp项目,配置项目的数据库连接,这些基本的就不说了;
3. 在项目的lib\action下创建一个新的类文件exportstatisticsaction.class.php,然后在 index方法中实现excel导出;
4. 导出方法的步骤:
①查询数据
②导入phpexcel类库
③创建excel对象并设置excel对象的属性
④设置excel的行列样式(字体、高宽、颜色、边框、合并等)
⑤绘制报表表头
⑥将查询数据写入excel
⑦设置excel的sheet的名称
⑧设置excel报表打开后初始的sheet
⑨设置输出的excel的头参数及文件名
⑩调用创建excel的方法生成excel文件
代码如下:
<?php /** * created by lonm.shi. * date: 2012-02-09 * time: 下午4:54 * to change this template use file | settings | file templates. */ class exportstatisticsaction extends action { public function index(){ $model= d("ordersview"); $ordersdata= $model->select(); //查询数据得到$ordersdata二维数组 vendor("phpexcel176.phpexcel"); // create new phpexcel object $objphpexcel = new phpexcel(); // set properties $objphpexcel->getproperties()->setcreator("ctos") ->setlastmodifiedby("ctos") ->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"); //set width $objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(8); $objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(10); $objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(25); $objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(50); $objphpexcel->getactivesheet()->getcolumndimension('f')->setwidth(10); $objphpexcel->getactivesheet()->getcolumndimension('g')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('h')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('i')->setwidth(12); $objphpexcel->getactivesheet()->getcolumndimension('j')->setwidth(30); //设置行高度 $objphpexcel->getactivesheet()->getrowdimension('1')->setrowheight(22); $objphpexcel->getactivesheet()->getrowdimension('2')->setrowheight(20); //set font size bold $objphpexcel->getactivesheet()->getdefaultstyle()->getfont()->setsize(10); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getfont()->setbold(true); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); $objphpexcel->getactivesheet()->getstyle('a2:j2')->getborders()->getallborders()->setborderstyle(phpexcel_style_border::border_thin); //设置水平居中 $objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_left); $objphpexcel->getactivesheet()->getstyle('a')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('b')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('d')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('f')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('g')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('h')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objphpexcel->getactivesheet()->getstyle('i')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //合并cell $objphpexcel->getactivesheet()->mergecells('a1:j1'); // set table header content $objphpexcel->setactivesheetindex(0) ->setcellvalue('a1', '订单数据汇总 时间:'.date('y-m-d h:i:s')) ->setcellvalue('a2', '订单id') ->setcellvalue('b2', '下单人') ->setcellvalue('c2', '客户名称') ->setcellvalue('d2', '下单时间') ->setcellvalue('e2', '需求机型') ->setcellvalue('f2', '需求数量') ->setcellvalue('g2', '需求交期') ->setcellvalue('h2', '确认bom料号') ->setcellvalue('i2', 'pmc确认交期') ->setcellvalue('j2', 'pmc交货备注'); // miscellaneous glyphs, utf-8 for($i=0;$i<count($ordersdata)-1;$i++){ $objphpexcel->getactivesheet(0)->setcellvalue('a'.($i+3), $ordersdata[$i]['id']); $objphpexcel->getactivesheet(0)->setcellvalue('b'.($i+3), $ordersdata[$i]['realname']); $objphpexcel->getactivesheet(0)->setcellvalue('c'.($i+3), $ordersdata[$i]['customer_name']); $objphpexcel->getactivesheet(0)->setcellvalue('d'.($i+3), todate($ordersdata[$i]['create_time'])); //这里调用了common.php的时间戳转换函数 $objphpexcel->getactivesheet(0)->setcellvalue('e'.($i+3), $ordersdata[$i]['require_product']); $objphpexcel->getactivesheet(0)->setcellvalue('f'.($i+3), $ordersdata[$i]['require_count']); $objphpexcel->getactivesheet(0)->setcellvalue('g'.($i+3), $ordersdata[$i]['require_time']); $objphpexcel->getactivesheet(0)->setcellvalue('h'.($i+3), $ordersdata[$i]['product_bom_encoding']); $objphpexcel->getactivesheet(0)->setcellvalue('i'.($i+3), $ordersdata[$i]['delivery_time']); $objphpexcel->getactivesheet(0)->setcellvalue('j'.($i+3), $ordersdata[$i]['delivery_memo']); $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getborders()->getallborders()->setborderstyle(phpexcel_style_border::border_thin); $objphpexcel->getactivesheet()->getrowdimension($i+3)->setrowheight(16); } // sheet命名 $objphpexcel->getactivesheet()->settitle('订单汇总表'); // set active sheet index to the first sheet, so excel opens this as the first sheet $objphpexcel->setactivesheetindex(0); // excel头参数 header('content-type: application/vnd.ms-excel'); header('content-disposition: attachment;filename="订单汇总表('.date('ymd-his').').xls"'); //日期为文件名后缀 header('cache-control: max-age=0'); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); //excel5为xls格式,excel2007为xlsx格式 $objwriter->save('php://output'); } }
5.调用导出方法直接 http://项目/index.php/exportstatistics/index,项目中调用直接__app__/exportstatistics/index,生成的报表是下载方式来保存。phpexcel1.7.6没有发现什么编码问题,速度也很快,注意导出的方法中不能有任何页面输出信息或调试信息,否则导出的excel会提示格式不对。效果如下:
导出报表
更多关于thinkphp相关内容感兴趣的读者可查看本站专题:《thinkphp入门教程》、《thinkphp模板操作技巧总结》、《thinkphp常用方法总结》、《codeigniter入门教程》、《ci(codeigniter)框架进阶教程》、《zend framework框架入门教程》及《php模板技术总结》。
希望本文所述对大家基于thinkphp框架的php程序设计有所帮助。
上一篇: Java 8新特性方法引用详细介绍
下一篇: Java中的static的使用指南