TP Excel

 // 导出甘特图 
 public function gantExport($project, $gant)
 {
     if (empty($gant) || !is_array($gant) || count($gant) < 1) {
         $gant = [];
     }
     $x = 7;
     $y = 6;

     // 求出顶级的时间
     $map = [];
     foreach ($gant as $k => $v) {
         if ($v['pCid']) {
             $map['pcid'] = $v['pCid'];
             $map['pid'] = $v['id'];
             $service = M('Project_service')->alias('ps')
                 ->field('max(ps.petime) as endTime,min(ps.pstime) as startTime')
                 ->where($map)
                 ->find();
             $data = M('Project_service')->alias('ps')
                 ->field('sum(progress) as progress,COUNT(*) as sum')
                 ->where($map)
                 ->find();
             $sum = $data['sum'] * 100;
             $result = $data['progress'] / $sum;
             $v['pComp'] = round($result * 100);

             $v['pStart'] = date("m/d/Y", $service['startTime']);
             $v['pEnd'] = date("m/d/Y", $service['endTime']);

             $gant[$k] = $v;
         }
     }
     $map = [];
     foreach ($gant as $k => $v) {
         if ($v['pMid']) {
             $map['ps.mid'] = $v['pMid'];
             $map['pid'] = $v['id'];
             $service = M('Project_service')->alias('ps')
                 ->field('max(ps.petime) as endTime,min(ps.pstime) as startTime')
                 ->where($map)
                 ->find();
             $data = M('Project_service')->alias('ps')
                 ->field('sum(progress) as progress,COUNT(*) as sum')
                 ->where($map)
                 ->find();
             $sum = $data['sum'] * 100;
             $result = $data['progress'] / $sum;
             $v['pComp'] = round($result * 100);

             $v['pStart'] = date("m/d/Y", $service['startTime']);
             $v['pEnd'] = date("m/d/Y", $service['endTime']);
             $gant[$k] = $v;
         }
     }

     $pEndMax = 0;  // 最大结束时间
     foreach ($gant as $k => $v) {
         if ($v['pEnd'] > $pEndMax) {
             $pEndMax = $v['pEnd'];
         }
     }

     $pStartMin = $pEndMax;
     foreach ($gant as $k => $v) {
         if ($v['pStart'] > 0 && $v['pStart'] < $pStartMin) {
             $pStartMin = $v['pStart'];
         }
     }
     $pEndMaxWeek = date('w', strtotime($pEndMax));
     $pStartMinWeek = date('w', strtotime($pStartMin));
     $endWeek = 6 - $pEndMaxWeek;
     $pEndMax = date('m/d/Y', strtotime("$pEndMax +$endWeek day"));

     $pStartMin = date('m/d/Y', strtotime("$pStartMin -$pStartMinWeek day"));


     $min = date('d', strtotime($pStartMin));
     $differDate = floor((strtotime($pEndMax) - strtotime($pStartMin)) / 86400); // 相差多少天


     //  计算出1级标题
     foreach ($gant as $k => $v) {
         $v['pParent'] > 0 ? $v['pGrade'] = 1 : $v['pGrade'] = 0;
         $gant[$k] = $v;
     }
     //  计算出2级标题
     foreach ($gant as $k => $v) {
         ($v['pParent'] > 1 && $v['pGrade'] == 1) and $v['pGrade'] = 2;
         $gant[$k] = $v;
     }

     foreach ($gant as $k => $v) {
         $startDays = floor((strtotime($v['pStart']) - strtotime($pStartMin)) / 86400); // 相差多少天
         $endDays = floor((strtotime($v['pEnd']) - strtotime($pStartMin)) / 86400); // 相差多少天
         $startBit = $y + $startDays; // 起始位
         $endBit = $y + $endDays;   // 结束位
         $v['startBit'] = $startBit;
         $v['endBit'] = $endBit;
         $gant[$k] = $v;
     }
     foreach ($gant as $key => $val) {
         $gantDays = floor((strtotime($val['pEnd']) - strtotime($val['pStart'])) / 86400); // 相差多少天
         $name = $val['pName'];
         $val['pGrade'] == 1 and $name = "    " . $val['pName'];
         $val['pGrade'] == 2 and $name = "        " . $val['pName'];
         $arr['0'] = $name;   //任务
         $arr['1'] = $val['pRes'];    // 指派给
         $arr['2'] = $gantDays + 1;    // 时长
         $arr['3'] = $val['pComp'] . "%";       // 进度
         $arr['4'] = $val['pStart'];  // 开始
         $arr['5'] = $val['pEnd'];    // 结束
         $datas[] = $arr;
     }
     $data = $datas;
     import("IOFactory", "excel/toexcel/PHPExcel", ".php");
     import("Excel5", "excel/toexcel/PHPExcel/Reader", ".php");
     import("PHPExcel", "excel/toexcel", ".php");

     $today = strtotime(date('Y-m-d H:i:s'));   //本地时间
     $filename = $project['name'].'-甘特图-' . date('YmdHis');
     $filename .= ".xls";
     header("Content-type: application/vnd.ms-excel; charset=utf-8");
     header("Content-Disposition: attachment; filename=$filename.xls");

     $excel = new \PHPExcel();

     $excelAssistant = new \Vendor\ExcelAssistant;

     $letter = $excelAssistant->GetExcelTit($differDate + 10);

     //表头数组
     $tableheader = ['任务', '指派给', '时长', '进度', '开始时间', '结束时间', "$min"];

     $tableheadertop = [];
     // 添加的表头 : 日期
     for ($i = 0; $i <= $differDate; $i++) {
         $d = $pStartMin;
         $w = strftime("%U", strtotime("$d"));
         $tableheadertop[$w][] = $pStartMin;
         $nextDay = date('m/d/Y', strtotime("$d +1 day"));
         $tableheader[$x] = date('d', strtotime("$nextDay"));;
         $excel->setActiveSheetIndex(0);
         $excel->getActiveSheet()->getDefaultColumnDimension("$letter[$x]4")->setWidth(3);
         $pStartMin = $nextDay;
         $x++;

     }

     $excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
     $excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
     $excel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
     $excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
     $excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
     $excel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

     // 给Excel添加背景色
     foreach ($gant as $k => $v) {
         $k = $k + 5;
         $startBit = $v['startBit'];
         $endBit = $v['endBit'];

         if($v['pGrade'] == 1||$v['pGrade'] == 0){
   $color = '000000';
}else{
   $color = $v['pColor'];
}
         for ($i = $startBit; $i <= $endBit; $i++) {
             $pCellCoordinate = "$letter[$i]$k";
             $excel->getActiveSheet(0)->getStyle($pCellCoordinate)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
             $excel->getActiveSheet()->getStyle($pCellCoordinate)->getFill()->getStartColor()->setRGB($color);
             $styleThinBlackBorderOutline = array(
                 'borders' => array(
                     'outline' => array(
                         'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式
                         'color' => array('rgb' => 'ffffff'), //设置border颜色
                     ),
                 ),);
             $excel->getActiveSheet()->getStyle($pCellCoordinate)->applyFromArray($styleThinBlackBorderOutline);
             if ($i < $endBit) {
                 $x = $i + 1;
                 $pCellCoordinateNext = "$letter[$x]$k";
                 $merge = "$pCellCoordinate" . ':' . "$pCellCoordinateNext";
                 $excel->getActiveSheet()->mergeCells($merge);      //合并
             }


         }
     }
     // 周期
     $z = 6;

     foreach ($tableheadertop as $k => $v) {
         $note = date('m/d', strtotime("$v[0]")) . ' - ' . $v[6];
         $excel->getActiveSheet()->setCellValue("$letter[$z]3", "$note");
         $z += 7;
     }

     for ($j = 0; $j <= $differDate / 7; $j++) {
         $x = ($j + 1) * 7 - 1;
         $y = (($j + 1) * 7 + 6) - 1;
         $start = "$letter[$x]3";
         $end = "$letter[$y]3";
         $merge = $start . ":" . $end;
         $excel->getActiveSheet()->mergeCells($merge);
         $excel->getActiveSheet()->getStyle($merge)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $styleThinBlackBorderOutline = array(
             'borders' => array(
                 'outline' => array(
                     'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式
                     'color' => array('rgb' => '333333'), //设置border颜色
                 ),
             ),);
         $excel->getActiveSheet()->getStyle($merge)->applyFromArray($styleThinBlackBorderOutline);
     }

     // 添加表头前面固定的
     for ($i = 0; $i < count($tableheader); $i++) {

         $excel->getActiveSheet()->setCellValue("$letter[$i]4", "$tableheader[$i]");
     }

     // 添加任务和项目经理
     if($project['stime']){
       $project['stime']=date('Y-m-d',$project['stime']);
     }else{
       $project['stime']="";
     }
     
     for ($i = 1; $i <= 2; $i++) {
         if ($i == 1) $note = $project['name'];
         if ($i == 2) $note = "项目经理:" . $project['pm'] . "      实际开始时间:" . $project['stime'];
         $j = 0;
         $excel->getActiveSheet()->setTitle('甘特图');
         $excel->getActiveSheet()->setCellValue("$letter[$j]$i", " " . "$note");
         $x = $i + 6;
         $pCellCoordinateNext = "$letter[$x]$i";
         $merge = "$letter[$j]$i" . ':' . "$pCellCoordinateNext";   //合并
     }


     $excel->getActiveSheet()->mergeCells("A1:IV1");
     $excel->getActiveSheet()->mergeCells("A2:IV2");
     $excel->getActiveSheet()->getRowDimension("1")->setRowHeight(30);
     $excel->getActiveSheet()->getRowDimension("2")->setRowHeight(30);
     $excel->getActiveSheet()->getStyle('A1:IV1')->getFont()->setSize(15);
     $excel->getActiveSheet()->getStyle('A2:IV2')->getFont()->setSize(10);
     $excel->getActiveSheet()->getStyle('A1:IV1')->applyFromArray(
         array(
             'font' => array(
                 'bold' => true
             ),
             'alignment' => array(
                 'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
             )
         )
     );
     $excel->getActiveSheet()->getStyle('A2:AA2')->applyFromArray(
         array(
             'font' => array(
                 'bold' => true
             ),
             'alignment' => array(
                 'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
             )
         )
     );


     //表格数组
     //填充表格信息
     for ($i = 5; $i <= count($data) + 4; $i++) {
         $j = 0;
         if ($i <= count($data) + 4) {
             foreach ($data[$i - 5] as $key => $value) {
                 $excel->getActiveSheet()->setTitle('甘特图');
                 $excel->getActiveSheet()->setCellValue("$letter[$j]$i", " " . "$value");
                 $j++;
             }
         }

     }


     //创建Excel输入对象
     $write = new \PHPExcel_Writer_Excel5($excel);
     header("Pragma: public");
     header("Expires: 0");
     header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
     header("Content-Type:application/force-download");
     header("Content-Type:application/vnd.ms-execl");
     header("Content-Type:application/octet-stream");
     header("Content-Type:application/download");;
     header('Content-Disposition:attachment;filename=' . $filename . '');
     header("Content-Transfer-Encoding:binary");
     $write->save('php://output');
 }