thinkphp3.2中实现phpexcel导出带生成图片示例

时间:2022-08-30 09:11:14

首先下载PHPEXCEL  下载地址:http://phpexcel.codeplex.com/        https://github.com/PHPOffice/PHPExcel

把Classes目录下的文件(PHPExcel.php和PHPExcel文件夹),放到ThinkPHP\Library\Org\Util目录下

PHPExcel.php  改名为 :PHPExcel.class.php

?
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
// 导出exl
 
  public function look_down(){
 
    $id = I('get.id');
 
    $m = M ('offer_goods');
 
    $where['offer_id'] = $id;
 
    $data = $m->field('goods_id,goods_sn,goods_name,barcode,goods_type,price')->select();
 
     
 
    // 导出Exl
 
    import("Org.Util.PHPExcel");
 
    import("Org.Util.PHPExcel.Worksheet.Drawing");
 
    import("Org.Util.PHPExcel.Writer.Excel2007");
 
    $objPHPExcel = new \PHPExcel();
 
     
 
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
 
   
 
    $objActSheet = $objPHPExcel->getActiveSheet();
 
     
 
    // 水平居中(位置很重要,建议在最初始位置)
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
     
 
    $objActSheet->setCellValue('A1', '商品货号');
 
    $objActSheet->setCellValue('B1', '商品名称');
 
    $objActSheet->setCellValue('C1', '商品图');
 
    $objActSheet->setCellValue('D1', '商品条码');
 
    $objActSheet->setCellValue('E1', '商品属性');
 
    $objActSheet->setCellValue('F1', '报价(港币)');
 
    // 设置个表格宽度
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(80);
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
 
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
 
     
 
    // 垂直居中
 
    $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
 
    $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
 
    $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
 
    $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
 
    $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
 
     
 
    foreach($data as $k=>$v){
 
      $k +=2;
 
      $objActSheet->setCellValue('A'.$k, $v['goods_sn']); 
 
      $objActSheet->setCellValue('B'.$k, $v['goods_name']); 
 
         
 
       
 
      $img = M('goods')->where('goods_id = '.$v['goods_id'])->field('goods_thumb')->find();
 
      // 图片生成
 
      $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
 
      $objDrawing[$k]->setPath('./Upload/'.$img['goods_thumb']);
 
      // 设置宽度高度
 
      $objDrawing[$k]->setHeight(80);//照片高度
 
      $objDrawing[$k]->setWidth(80); //照片宽度
 
      /*设置图片要插入的单元格*/
 
      $objDrawing[$k]->setCoordinates('C'.$k);
 
      // 图片偏移距离
 
      $objDrawing[$k]->setOffsetX(12);
 
      $objDrawing[$k]->setOffsetY(12);
 
      $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
 
       
 
      // 表格内容
 
      $objActSheet->setCellValue('D'.$k, $v['barcode']); 
 
      $objActSheet->setCellValue('E'.$k, $v['goods_type']); 
 
      $objActSheet->setCellValue('F'.$k, $v['price']);
 
         
 
      // 表格高度
 
      $objActSheet->getRowDimension($k)->setRowHeight(80);
 
       
 
    }
 
     
 
    $fileName = '报价表';
 
    $date = date("Y-m-d",time());
 
    $fileName .= "_{$date}.xls";
 
    $fileName = iconv("utf-8", "gb2312", $fileName);
 
    //重命名表
 
    // $objPHPExcel->getActiveSheet()->setTitle('test');
 
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
 
    $objPHPExcel->setActiveSheetIndex(0);
 
    header('Content-Type: application/vnd.ms-excel');
 
    header("Content-Disposition: attachment;filename=\"$fileName\"");
 
    header('Cache-Control: max-age=0');
 
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 
    $objWriter->save('php://output'); //文件通过浏览器下载
 
    // END 
 
  }

水平居中,垂直居中,高度 等设置。注意放的位置,如果你放在末尾,那么是下一样生效。放到头部,第一行生效(上面代码是第一行生效,如果放到foreach里面就是下一行生效)。

import介绍。import("Org.Util.PHPExcel.Writer.Excel2007"); 文件位置:Org\Util\PHPExcel\Writer\Excel2007.class.php

图片地址一定要是本地。objDrawing[ objDrawing[k]->setPath('./Upload/'.$img['goods_thumb']); 图片位置:安装目录/Upload/xxx

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://www.cnblogs.com/lhm166/articles/6393989.html