thinkPHP+phpexcel实现excel报表输出功能示例

时间:2022-02-27 20:29:44

本文实例讲述了thinkphp+phpexcel实现excel报表输出功能。分享给大家供大家参考,具体如下:

准备工作:

1.下载phpexcel1.7.6类包;

2.解压至tp框架的thinkphp\vendor目录下,改类包文件夹名为phpexcel176,目录结构如下图;

thinkPHP+phpexcel实现excel报表输出功能示例

编写代码(以一个订单汇总数据为例):

  1. 创建数据库及表;
  2. 创建tp项目,配置项目的数据库连接,这些基本的就不说了;
  3. 在项目的lib\action下创建一个新的类文件exportstatisticsaction.class.php,然后在  index方法中实现excel导出;
  4. 导出方法的步骤:
    ①查询数据
    ②导入phpexcel类库
    ③创建excel对象并设置excel对象的属性
    ④设置excel的行列样式(字体、高宽、颜色、边框、合并等)
    ⑤绘制报表表头
    ⑥将查询数据写入excel
    ⑦设置excel的sheet的名称
    ⑧设置excel报表打开后初始的sheet
    ⑨设置输出的excel的头参数及文件名
    ⑩调用创建excel的方法生成excel文件

代码如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<?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+phpexcel实现excel报表输出功能示例

希望本文所述对大家基于thinkphp框架的php程序设计有所帮助。