PHPExcelService.php 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267
  1. <?php
  2. // +----------------------------------------------------------------------
  3. // | CRMEB [ CRMEB赋能开发者,助力企业发展 ]
  4. // +----------------------------------------------------------------------
  5. // | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
  6. // +----------------------------------------------------------------------
  7. // | Licensed CRMEB并不是自由软件,未经许可不能去掉CRMEB相关版权
  8. // +----------------------------------------------------------------------
  9. // | Author: CRMEB Team <admin@crmeb.com>
  10. // +----------------------------------------------------------------------
  11. namespace service;
  12. use PHPExcel_IOFactory;
  13. use PHPExcel;
  14. use service\JsonService as Json;
  15. use think\Request;
  16. class PHPExcelService
  17. {
  18. //PHPExcel实例化对象
  19. private static $PHPExcel = null;
  20. //表头计数
  21. protected static $count;
  22. //表头占行数
  23. protected static $topNumber = 3;
  24. //表能占据表行的字母对应self::$cellkey
  25. protected static $cells;
  26. //表头数据
  27. protected static $data = [];
  28. //文件名
  29. protected static $title = '订单导出';
  30. //行宽
  31. protected static $where = 20;
  32. //行高
  33. protected static $height = 50;
  34. //表行名
  35. private static $cellKey = array(
  36. 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
  37. 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
  38. 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
  39. 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
  40. );
  41. //设置style
  42. private static $styleArray = array(
  43. 'borders' => array(
  44. 'allborders' => array(
  45. // PHPExcel_Style_Border里面有很多属性,想要其他的自己去看
  46. // 'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
  47. // 'style' => \PHPExcel_Style_Border::BORDER_DOUBLE,//双重的
  48. // 'style' => \PHPExcel_Style_Border::BORDER_HAIR,//虚线
  49. // 'style' => \PHPExcel_Style_Border::BORDER_MEDIUM,//实粗线
  50. // 'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,//虚粗线
  51. // 'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,//点虚粗线
  52. 'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
  53. //'color' => array('argb' => 'FFFF0000'),
  54. ),
  55. ),
  56. 'font' => [
  57. 'bold' => true
  58. ],
  59. 'alignment' => [
  60. 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  61. 'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
  62. ]
  63. );
  64. /**
  65. *初始化PHPExcel类
  66. * @param $data array()
  67. * @param $fun function()
  68. * return
  69. */
  70. private static function initialize($data, $fun)
  71. {
  72. // vendor("PHPExcel.PHPExcel.PHPExcel");
  73. self::$PHPExcel = new PHPExcel();
  74. if ($fun !== null && is_callable($fun)) {
  75. self::$styleArray = $fun();
  76. }
  77. if (!is_array($data)) exit(Json::fail('data 为数组'));
  78. self::$data = $data;
  79. }
  80. /**
  81. *设置字体格式
  82. * @param $title string 必选
  83. * return string
  84. */
  85. public static function setUtf8($title)
  86. {
  87. return iconv('utf-8', 'gb2312', $title);
  88. }
  89. /**
  90. *
  91. * execl数据导出
  92. * @param $list 需要导出的数据 格式和以前的可是一样
  93. * @param $list 也可以为匿名函数 匿名函数参数有 $sheet PHPExcel->getActiveSheet(),self::$topNumber 从第几行设置,$cellkey 行号为数组,self::$cells现在设置的最大行号
  94. *
  95. * 特殊处理:合并单元格需要先对数据进行处理
  96. */
  97. public function setExcelContent($list = null)
  98. {
  99. $sheet = self::$PHPExcel->getActiveSheet();
  100. foreach (self::$data as $key => $val) {
  101. $row = self::$cellKey[$key];
  102. $sheet->getColumnDimension($row)->setWidth(isset($val['w']) ? $val['w'] : self::$where);
  103. $sheet->setCellValue($row . self::$topNumber, isset($val['name']) ? $val['name'] : $val);
  104. }
  105. $cellkey = array_slice(self::$cellKey, 0, self::$count);
  106. if ($list !== null && is_array($list)) {
  107. foreach ($cellkey as $k => $v) {
  108. foreach ($list as $key => $val) {
  109. if (isset($val[$k]) && !is_array($val[$k])) {
  110. $sheet->setCellValue($v . (self::$topNumber + 1 + $key), $val[$k]);
  111. } else if (isset($val[$k]) && is_array($val[$k])) {
  112. $str = '';
  113. foreach ($val[$k] as $value) {
  114. $str .= $value . chr(10);
  115. }
  116. $sheet->setCellValue($v . (self::$topNumber + 1 + $key), $str);
  117. }
  118. }
  119. }
  120. $sheet->getDefaultRowDimension()->setRowHeight(self::$height);
  121. //设置边框
  122. $sheet->getStyle('A1:' . self::$cells . (count($list) + self::$topNumber))->applyFromArray(self::$styleArray);
  123. //设置自动换行
  124. $sheet->getStyle('A4:' . self::$cells . (count($list) + self::$topNumber))->getAlignment()->setWrapText(true);
  125. } else if ($list !== null && is_callable($list)) {
  126. $list($sheet, self::$topNumber, $cellkey, self::$cells)->applyFromArray(self::$styleArray);
  127. }
  128. return $this;
  129. }
  130. /**
  131. * 保存表格数据,并下载
  132. * @param
  133. * @return
  134. */
  135. public function ExcelSave()
  136. {
  137. $objWriter = \PHPExcel_IOFactory::createWriter(self::$PHPExcel, 'Excel2007');
  138. $filename = self::$title . '--' . time() . '.xlsx';
  139. ob_end_clean();
  140. header('Content-Type: application/vnd.ms-excel');
  141. header('Content-Type: application/octet-stream');
  142. header('Content-Disposition: attachment; filename="' . $filename . '"');
  143. header('Cache-Control: max-age=0');
  144. $objWriter->save('php://output');
  145. exit;
  146. }
  147. /**
  148. * 设置头部信息
  149. * @param $data array
  150. * @param $fun function() 主要设置边框的粗细
  151. * @return $this
  152. */
  153. public static function setExcelHeader($data, $fun = null)
  154. {
  155. self::initialize($data, $fun);
  156. if (self::$count = count(self::$data)) {
  157. if (self::$count > count(self::$cellKey)) {
  158. return Json::fail('表头长度过长');
  159. }
  160. self::$cells = self::$cellKey[self::$count - 1];
  161. } else {
  162. return Json::fail('data 参数二不能为空');
  163. }
  164. return new self;
  165. }
  166. /**
  167. * 设置标题
  168. * @param $title string || array ['title'=>'','name'=>'','info'=>[]]
  169. * @param $Name string
  170. * @param $info string || array;
  171. * @param $funName function($style,$A,$A2) 自定义设置头部样式
  172. * @return $this
  173. */
  174. public function setExcelTile($title = '', $Name = '', $info = [], $funName = null)
  175. {
  176. //设置参数
  177. if (is_array($title)) {
  178. if (isset($title['title'])) $title = $title['title'];
  179. if (isset($title['name'])) $Name = $title['name'];
  180. if (isset($title['info'])) $info = $title['info'];
  181. }
  182. if (empty($title))
  183. $title = self::$title;
  184. else
  185. self::$title = $title;
  186. if (empty($Name)) $Name = time();
  187. //设置Excel属性
  188. self::$PHPExcel->getProperties()
  189. ->setCreator("Neo")
  190. ->setLastModifiedBy("Neo")
  191. ->setTitle(self::setUtf8($title))
  192. ->setSubject($Name)
  193. ->setDescription("")
  194. ->setKeywords($Name)
  195. ->setCategory("");
  196. self::$PHPExcel->getActiveSheet()->setCellValue('A1', $title);
  197. //文字居中
  198. self::$PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  199. self::$PHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  200. self::$PHPExcel->setActiveSheetIndex(0);
  201. self::$PHPExcel->getActiveSheet()->setTitle($Name);
  202. self::$PHPExcel->getActiveSheet()->setCellValue('A2', self::setCellInfo($info));
  203. //合并表头单元格
  204. self::$PHPExcel->getActiveSheet()->mergeCells('A1:' . self::$cells . '1');
  205. self::$PHPExcel->getActiveSheet()->mergeCells('A2:' . self::$cells . '2');
  206. self::$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
  207. self::$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
  208. //设置表头行高
  209. if ($funName !== null && is_callable($funName)) {
  210. $fontstyle = self::$PHPExcel->getActiveSheet();
  211. $funName($fontstyle, 'A1', 'A2');
  212. } else {
  213. //设置表头字体
  214. self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
  215. self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
  216. self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  217. self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
  218. self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
  219. }
  220. self::$PHPExcel->getActiveSheet()->getStyle('A3:' . self::$cells . '3')->getFont()->setBold(true);
  221. return $this;
  222. }
  223. /**
  224. * 设置第二行标题内容
  225. * @param $info array (['name'=>'','site'=>'','phone'=>123] || ['我是表名','我是地址','我是手机号码'] ) || string 自定义
  226. * @return string
  227. */
  228. private static function setCellInfo($info)
  229. {
  230. $content = ['操作者:', '导出日期:' . date('Y-m-d', time()), '地址:', '电话:'];
  231. if (is_array($info) && !empty($info)) {
  232. if (isset($info['name'])) {
  233. $content[0] .= $info['name'];
  234. } else {
  235. $content[0] .= isset($info[0]) ? $info[0] : '';
  236. }
  237. if (isset($info['site'])) {
  238. $content[2] .= $info['site'];
  239. } else {
  240. $content[2] .= isset($info[1]) ? $info[1] : '';
  241. }
  242. if (isset($info['phone'])) {
  243. $content[3] .= $info['phone'];
  244. } else {
  245. $content[3] .= isset($info[2]) ? $info[2] : '';
  246. }
  247. return implode(' ', $content);
  248. } else if (is_string($info)) {
  249. return empty($info) ? implode(' ', $content) : $info;
  250. }
  251. }
  252. }