Excel.php
4.6 KB
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
<?php
/**
* Created by PhpStorm.
* User: wz
* Date: 2019/3/30
* Time: 14:14
*/
namespace app\admin\model;
class Excel
{
/**
* 循环excel单元格横坐标 最大702列
*/
public function excelAcross($num){
$arr=['A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
$re=[];
$counts=['0'=>0,'1'=>0];
if ($num<=702){
for ($i=0;$i<$num;$i++){
if ($counts[0]==0){
$re[]=$arr[$counts[1]];
}else{
$nums=$counts[0]-1;
$re[]=$arr[$nums].$arr[$counts[1]];
}
if ($counts[1]>=25){
$counts[1]=0;
$counts[0]++;
}else{
$counts[1]++;
}
}
return $re;
}else{
return false;
}
}
//例子
public function cs(){
$title=[['列名','列宽','字段名','1:普通文本 2:图片（必须为本地路径）','图片宽度 非图片可不填']];
$letter=$this->excelAcross(count($title));
$list=[['id'=>1,'mobile'=>2],['id'=>2,'mobile'=>2]];
}
//excel
/**
* @param $letter
* @param $title
* @param $list 数据
* @param $name
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
* @throws \PHPExcel_Writer_Exception
*/
public function excel($letter,$title,$list,$name){
// 导出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();
foreach ($letter as $k=>$v){
// 水平居中（位置很重要，建议在最初始位置）
$objPHPExcel->setActiveSheetIndex(0)->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置首行
$objActSheet->setCellValue($v.'1', $title[$k][0]);
// 设置个表格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth($title[$k][1]);
// 垂直居中
$objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
foreach($list as $k=>$v){
$k +=2;
foreach ($letter as $key=>$val){
if ($title[$key][3]==1){
$objActSheet->setCellValue($val.$k, empty($v[$title[$key][2]])?'':$v[$title[$key][2]]);
}else{
$objPHPExcel->getActiveSheet()->getDefaultRowDimension($k)->setRowHeight(200);
// 图片生成
$objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
$objDrawing[$k]->setPath('./'.$v[$title[$key][2]]);
// 设置宽度高度
//$objDrawing[$k]->setHeight(80);//照片高度
$objDrawing[$k]->setWidth($title[$key][4]); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing[$k]->setCoordinates($val.$k);
// 图片偏移距离
$objDrawing[$k]->setOffsetX(10);
$objDrawing[$k]->setOffsetY(10);
$objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
}
}
}
$fileName = $name;
$ua = $_SERVER['HTTP_USER_AGENT'];
$ua = strtolower($ua);
if(preg_match('/msie/', $ua) || preg_match('/edge/', $ua)) { //判断是否为IE或Edge浏览器
$fileName = str_replace('+', '%20', urlencode($fileName)); //使用urlencode对文件名进行重新编码
}
$date = date("Y-m-d",time());
$fileName .= "_{$date}.xlsx";
$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, 'Excel2007');
$objWriter->save('php://output'); //文件通过浏览器下载
exit();
// END
}
}