1 <?php 2 /* 3 分页，分栏，分行 4 $title excel标题 5 $excelsheet sheet标题 6 $field 7 $data 生成excel的数据 8 */ 9 class PHPExcelExport 10 { 11 function __construct() 12 { 13 ; 14 } 15 16 private static $objPHPExcel = null; 17 private static $FIELD2SHEETCOLUMN = array(); //导出字段与excel列对照数组 18 private static $objPhpSheet = null; 19 20 /* 21 *创建excel生成表 22 *@pram ($title=>标题 23 $field => 列名称 24 $data => 数据 25 $sheetindex => 第几个工作表) 26 * 27 */ 28 public static function getexportsheet($title,$field,$data,$sheetindex=0) 29 { 30 set_time_limit(0); 31 self::getphpexcel_instance();//引入phpExcel.php 并且实例化 32 self::field2sheetcolumn($firstcolumid=1,$field); 33 self::createSheet(0,$title);//创建excel表格 34 self::setfirstRow(++$sheetindex,$field); //生成首行字段说明 35 self::setdataRow($data,++$sheetindex);//生成主体内容 36 self::save_export($title);//生成文件 37 } 38 39 public static function getcolumnexport($title,$field,$data,$sheetindex=0,$columntitle) 40 { 41 self::getphpexcel_instance(); 42 $cloumn_length = count($field); 43 $firstcolumid=1; 44 $k = 0; 45 foreach($data as $key=> $val) 46 { 47 $tmp_sheetindex = $sheetindex; 48 $firstcolumid = $firstcolumid + $cloumn_length * $k; 49 field2sheetcolumn($firstcolumid=1,$field); 50 self::createSheet(0,$title);//创建excel表格 51 self::setfirstRow(++$tmp_sheetindex,$field); //生成首行 52 self::setdataRow($val,++$tmp_sheetindex);//生成主体内容 53 $k++; 54 } 55 self::save_export($title);//生成文件 56 } 57 58 59 public static function getpageexport($title,$field,$data,$sheetindex=0,$pagetitle) 60 { 61 self::getphpexcel_instance(); 62 self::field2sheetcolumn($firstcolumid=1,$field); 63 $cloumn_length = count($field); 64 foreach($data as $key=> $val) 65 { 66 $tmp_sheetindex = $sheetindex; 67 self::createSheet(0,$pagetitle[$key]);//创建excel表格 68 self::setfirstRow(++$tmp_sheetindex,$field); //生成首行 69 self::setdataRow($val,++$tmp_sheetindex); //生成主体内容 70 } 71 self::save_export($title);//生成文件 72 } 73 74 public static function getcolumnpageexport() 75 { 76 } 77 78 private static function getphpexcel_instance() 79 { 80 if(self::$objPHPExcel == null) 81 { 82 require_once 'phpexcel/PHPExcel.php'; 83 require_once 'phpexcel/PHPExcel/Writer/Excel5.php'; 84 self::$objPHPExcel = new PHPExcel(); 85 } 86 return self::$objPHPExcel; 87 } 88 89 public static function createSheet($sheetindex,$title) 90 { 91 self::getphpexcel_instance(); 92 self::$objPHPExcel->createSheet(); 93 self::$objPHPExcel->setActiveSheetIndex($sheetindex);//设置当前的sheet 94 self::$objPhpSheet = self::$objPHPExcel->getActiveSheet(); 95 self::$objPhpSheet->setTitle($title);//设置当前活动sheet的名称 96 } 97 98 /** 99 *function @merge cells 100 * @param int $columnfrom Start with column number 101 * @param int $columnto end with column number 102 *@param int $linefrom Start with line number 103 * @param int $lineto end with line number 104 * @param string $celval After the merger of cell'value 105 */ 106 107 public static function mergecel($columnfrom,$columnto,$linefrom,$lineto,$celval) 108 { 109 self::getphpexcel_instance(); 110 $columnfrom = self::changeid2abc($columnfrom); 111 $columnto = self::changeid2abc($to); 112 $objPhpSheet->setCellValue($columnfrom.$linefrom, $celval); 113 $objPhpSheet->mergeCells($linefrom . $linefrom . ":" . $columnto . $lineto); 114 } 115 116 /** 117 * static save excel 118 * 119 * @param string $title excel title 120 * @param bool $isoutput2brower excel output way (The browser or is saved to the server) 121 * @param string $path excel is saved to the server's path 122 */ 123 124 public static function save_export($title , $isoutput2brower=true , $path='') 125 { 126 $objWriter = new PHPExcel_Writer_Excel5(self::$objPHPExcel); 127 $outputFileName = $title."(" . date('Y-m-d') . ").xls"; 128 if($isoutput2brower) { 129 header("Content-Type: application/force-download"); 130 header("Content-Type: application/octet-stream"); 131 header("Content-Type: application/download"); 132 header('Content-Disposition:inline;filename="' . iconv('UTF-8', 'GBK', $outputFileName).'"'); 133 header('Content-Type: application/vnd.ms-excel'); 134 header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 135 header("Pragma: no-cache"); 136 $objWriter->save('php://output'); 137 } else { 138 $objWriter->save($path.$outputFileName); 139 } 140 } 141 142 /** 143 *function Setting field line 144 *@param int $k line number 145 *@param array $field field array 146 */ 147 public static function setfirstRow($k,$field) 148 { 149 self::getphpexcel_instance(); 150 foreach($field as $key=> $val) { 151 self::$objPhpSheet->setCellValue(self::$FIELD2SHEETCOLUMN[$key] . $k, $val); 152 } 153 } 154 155 public static function setdataRow($data,$dataindex) 156 { 157 self::getphpexcel_instance(); 158 foreach($data as $key=> $val) { 159 foreach($val as $k => $v) { 160 if(isset(self::$FIELD2SHEETCOLUMN[$k])) { 161 self::$objPhpSheet->setCellValue(self::$FIELD2SHEETCOLUMN[$k] . $dataindex, ' '.$v); 162 } 163 } 164 $dataindex++; 165 } 166 } 167 168 /** 169 *将需要导出的字段转化为excel列ABCD 170 *@para $firstcolumid=>起始列 默认从第一列开始 171 */ 172 public static function field2sheetcolumn($firstcolumid=1,$field) 173 { 174 foreach($field as $key=> $val) 175 { 176 self::$FIELD2SHEETCOLUMN[$key] = PHPExcelExport::changeid2abc($firstcolumid++); 177 } 178 } 179 180 /** 181 * @desc阿拉伯数字转化为excel单元格 182 */ 183 public static function changeid2abc($n) 184 { 185 $n = $n >= 1 && $n <= 255 ? strval($n) : ''; 186 if(empty($n)){return false;} 187 $s = $n > 26 ? chr(intval(($n - 1)/26)+64) : ''; 188 $g = chr((($n - 1) % 26) + 65); 189 return $s.$g; 190 } 191 192 //推广回收报表 193 public function makeExcel($title,$field2name,$fieldArr,$data) 194 { 195 self::getphpexcel_instance(); 196 self::createSheet(0,$title);//创建excel表格 197 self::getphpexcel_instance(); 198 self::$objPhpSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置对齐方式 199 200 $line = array('1' => 0 , '2' => '0'); 201 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc("1")."1",$title);//设置单元格内容 202 self::$objPhpSheet->mergeCells("A1:U1" );//合并单元格 203 foreach ($fieldArr as $_key => $_field) { 204 if (is_array($_field)) { 205 $line[1] += 1; 206 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[1])."2",$field2name[$_key]); 207 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($line[1])."2" . ":" .PHPExcelExport::changeid2abc($line[1]+count($_field)-1)."2" ); 208 $num = 0; 209 foreach ($_field as $_key2 => $_val) { 210 if ($num >0) { 211 $line[1] += 1; 212 } 213 $num ++; 214 $line[2] += 1; 215 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[2])."3",$field2name[$_key.'_'.$_val]); 216 } 217 } else { 218 $line[1] += 1; 219 $line[2] += 1; 220 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($line[1])."2" . ":" .PHPExcelExport::changeid2abc($line[1])."3" ); 221 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[1])."2",$field2name[$_field]); 222 } 223 } 224 $except = array('week1','week2','week3','week4', 'week5', 'week6'); 225 $line = array('1' => 3 , '2' => '0'); 226 if ($data) { 227 foreach ($data as $key=>$item) { 228 $line[1] += 1; 229 $line[2] = 0; 230 foreach ($field2name as $k=>$_item) { 231 if (!in_array($k,$except)) { 232 $line[2] += 1; 233 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[2]).$line[1],$item[$k]); 234 } 235 } 236 } 237 } 238 self::save_export($title);//生成文件 239 } 240 241 //广告分析报表 2016/5/23 242 public function adAnalysisExcel ($title, $fieldName, $fieldArr, $valName, $data, $flag) 243 { 244 self::getphpexcel_instance(); 245 self::createSheet(0,$title); 246 $c1 = 0; $c2=0; 247 //短期评价 ps:没有30天ROI 248 if ($flag == 0 ) { 249 foreach ($fieldArr as $key => $val) { 250 if (is_array($val)) { 251 $c1++; 252 $num1 = count($val); 253 $num2 = count($val, COUNT_RECURSIVE); 254 if ($num1 == $num2) { 255 $num = $num2; 256 } else { 257 $num = $num2-$num1; 258 } 259 if (isset($c3) && $c3 == 20) { 260 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c3) . '1' . ':' . PHPExcelExport::changeid2abc(23) . '1'); 261 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c3) . '1','广告使用情况'); 262 } else { 263 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c1) . '1' . ':' . PHPExcelExport::changeid2abc($num+$c2) . '1'); 264 foreach ($fieldName as $_key => $_val) { 265 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c1) . '1',$fieldName[$key]); 266 } 267 268 } 269 $tip = 0; 270 foreach ($val as $k => $v) { 271 $tip2 = 0; 272 if (is_array($v)) { 273 $c2++;$c3 = $c2; //16 274 //三层的第二层的合并 275 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c2) . '2' . ':' . PHPExcelExport::changeid2abc($c2+1) . '2'); 276 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c2) . '2', $fieldName[$k]); 277 foreach ($v as $x => $y) { 278 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c3) . '3',$fieldName[$y]); 279 if ($tip2 >0) { 280 $c1 += 1; 281 $c2++; 282 } 283 $c3++; 284 $tip2++; 285 } 286 } else { 287 if ($tip >0) { 288 $c1 += 1; 289 } 290 $tip++; 291 $c2++; 292 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c2) . '2' . ':' . PHPExcelExport::changeid2abc($c2) . '3'); 293 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c2) . '2',$fieldName[$v]); 294 } 295 } 296 } else { 297 $c1++; 298 $c2++; 299 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c1) . '1' . ':' . PHPExcelExport::changeid2abc($c1) . '3'); 300 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c1) . '1', '广告名称'); 301 } 302 } 303 $line = array('1' => '3', '2' => '0'); 304 305 if ($data) { 306 foreach ($data as $key=>$item) { 307 $line[1] += 1; 308 $line[2] = 0; 309 foreach ($valName as $k=>$_item) { 310 $line[2] += 1; 311 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[2]).$line[1],$item[$k]); 312 } 313 } 314 } 315 self::save_export($title);//生成文件 316 } 317 //长期评价 ps:有30天ROI这项 318 if ($flag == 1) { 319 foreach ($fieldArr as $key => $val) { 320 if (is_array($val)) { 321 $c1++; 322 $num1 = count($val); 323 $num2 = count($val, COUNT_RECURSIVE); 324 if ($num1 == $num2) { 325 $num = $num2; 326 } else { 327 $num = $num2-$num1; 328 } 329 if (isset($c3) && $c3 == 21) { 330 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c3) . '1' . ':' . PHPExcelExport::changeid2abc(24) . '1'); 331 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c3) . '1','广告使用情况'); 332 } else { 333 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c1) . '1' . ':' . PHPExcelExport::changeid2abc($num+$c2) . '1'); 334 foreach ($fieldName as $_key => $_val) { 335 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c1) . '1',$fieldName[$key]); 336 } 337 338 } 339 $tip = 0; 340 foreach ($val as $k => $v) { 341 $tip2 = 0; 342 if (is_array($v)) { 343 $c2++;$c3 = $c2; //16 344 //三层的第二层的合并 345 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c2) . '2' . ':' . PHPExcelExport::changeid2abc($c2+1) . '2'); 346 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c2) . '2', $fieldName[$k]); 347 foreach ($v as $x => $y) { 348 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c3) . '3',$fieldName[$y]); 349 if ($tip2 >0) { 350 $c1 += 1; 351 $c2++; 352 } 353 $c3++; 354 $tip2++; 355 } 356 } else { 357 if ($tip >0) { 358 $c1 += 1; 359 } 360 $tip++; 361 $c2++; 362 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c2) . '2' . ':' . PHPExcelExport::changeid2abc($c2) . '3'); 363 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c2) . '2',$fieldName[$v]); 364 } 365 } 366 } else { 367 $c1++; 368 $c2++; 369 self::$objPhpSheet->mergeCells(PHPExcelExport::changeid2abc($c1) . '1' . ':' . PHPExcelExport::changeid2abc($c1) . '3'); 370 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($c1) . '1', '广告名称'); 371 } 372 } 373 $line = array('1' => '3', '2' => '0'); 374 375 if ($data) { 376 foreach ($data as $key=>$item) { 377 $line[1] += 1; 378 $line[2] = 0; 379 foreach ($valName as $k=>$_item) { 380 $line[2] += 1; 381 self::$objPhpSheet->setCellValue(PHPExcelExport::changeid2abc($line[2]).$line[1],$item[$k]); 382 } 383 } 384 } 385 self::save_export($title);//生成文件 386 } 387 } 388 }