//把要导出的数据相同的分组组成一个三维数组$expTableData //准备的导出数据$res = array();foreach($expTableData as &$val){$res[$val]['分组key'][] = $val;}
//计算单元格合并的长度
foreach (array_values($res) as $k=>$v) {if ($k == 0) {$num1 = 3;$num2 = $num1+count($v)-1;}else {$num1 = $num2+1;$num2 = $num1+count($v)-1;}echo $num1.'-'.$num2;$objActSheet->mergeCells('A'.$num1.':A'.$num2);//合并单元格$objActSheet->mergeCells('B'.$num1.':B'.$num2);//合并单元格$objActSheet->mergeCells('C'.$num1.':C'.$num2);//合并单元格$objActSheet->mergeCells('D'.$num1.':D'.$num2);//合并单元格}
完整代码
public function exportExcel_1($expTitle, $expCellName, $expTableData, $expColor = [], $fixed = 0, $title = null, $othTitle = []){// $expTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称$fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定$cellNum = count($expCellName);$dataNum = count($expTableData);vendor('PHPExcel.PHPExcel');$objPHPExcel = new \PHPExcel();$cellName = array('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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');// echo $cellNum . '==' . $dataNum;die;/* 设置当前的sheet */$objActSheetIndex = $objPHPExcel->setActiveSheetIndex(0);$objActSheet = $objPHPExcel->getActiveSheet(0);/* sheet标题 */$objActSheet->setTitle($expTitle);$ascii = 65;$cv = '';if ($title) {$objActSheetIndex->setCellValue('A1', $expTitle);} else {$objActSheetIndex->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));}$i = 2;if ($othTitle) {foreach($othTitle as $val) {$ascii = 65;foreach ($val as $k=>$one) {$objActSheet->setCellValue($cv.chr($ascii). $i, $one);if ($k == 1) {$objActSheet->mergeCells($cv.chr($ascii).$i.':'.$cellName[$cellNum-1]. $i);}$ascii++;if($ascii == 91){$ascii = 65;$cv .= chr(strlen($cv)+65);}}$i++;}}$ascii = 65;foreach($expCellName as $key => $field){$objActSheet->setCellValue($cv.chr($ascii). $i, $field[1]);$ascii++;if($ascii == 91){$ascii = 65;$cv .= chr(strlen($cv)+65);}}$ascii = 65;$cv = '';$i ++;$width = [];$res = array();foreach($expTableData as &$val){$res[$val['xzbIds']][]= $val;foreach($expCellName as $field){// if ($fixed) {//$tmpLen = mb_strlen($field[1]);//if ($tmpLen > 20 ) {var_dump($field);die;// // $objActSheet->getColumnDimension($cv.chr($ascii))->setAutoSize(true);// $objActSheet->getColumnDimension($cv.chr($ascii))->setWidth(mb_strlen($field[1]));// if (isset($width[$cv.chr($ascii)])) {// $width[$cv.chr($ascii)] = $width[$cv.chr($ascii)] > $tmpLen ? $width[$cv.chr($ascii)] : $tmpLen;// } else {// $width[$cv.chr($ascii)] = $tmpLen;// }//} else {// $objActSheet->getColumnDimension($cv.chr($ascii))->setWidth('15');//}// } else {//$objActSheet->getColumnDimension($cv.chr($ascii))->setWidth('15');// }$objActSheet->getColumnDimension($cv.chr($ascii))->setWidth('15');if(isset($field[2])) {switch ($field[2]) {//防止使用科学计数法，在数据前加空格case 'longNumber':$objActSheet->setCellValue($cv.chr($ascii).$i, ' '.$val[$field[0]]);break;case 'datetime':$objActSheet->setCellValue($cv.chr($ascii).$i, date('Y-m-d',$val[$field[0]]));break;default:$objActSheet->setCellValue($cv.chr($ascii).$i, $val[$field[0]]);break;}} else {$objActSheet->setCellValue($cv.chr($ascii).$i, $val[$field[0]]);}$ascii++;if($ascii == 91){$ascii = 65;$cv .= chr(strlen($cv)+65);}}$ascii = 65;$cv = '';$i++;}$num2 = 0;foreach (array_values($res) as $k=>$v) {if ($k == 0) {$num1 = 3;$num2 = $num1+count($v)-1;}else {$num1 = $num2+1;$num2 = $num1+count($v)-1;}echo $num1.'-'.$num2;$objActSheet->mergeCells('A'.$num1.':A'.$num2);//合并单元格$objActSheet->mergeCells('B'.$num1.':B'.$num2);//合并单元格$objActSheet->mergeCells('C'.$num1.':C'.$num2);//合并单元格$objActSheet->mergeCells('D'.$num1.':D'.$num2);//合并单元格}/* 生成到浏览器，提供下载 */ob_end_clean(); //清空缓存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-excel;charset=utf-8');header("Content-Type:application/octet-stream");//header("Content-Type:application/download");$user_agent = $_SERVER['HTTP_USER_AGENT'];if(preg_match("/MSIE/", $user_agent) || preg_match("/Trident/", $user_agent)){$fileName = str_replace('+','%20',urlencode($fileName));header('content-disposition:attachment; filename="'.$fileName.'.xlsx"');}else if(preg_match("/Firefox/", $user_agent)){header("content-disposition:attachment; filename*=\"utf8''".$fileName.'.xlsx"');}else{header('content-disposition:attachment; filename="'.$fileName.'.xlsx"');}//header("Content-Transfer-Encoding:binary");//处理表格颜色if ($expColor) {foreach ($expColor as $one) {$tmp = $one['letters'][0] . $one['num'][0] . ':' . $one['letters'][1] . $one['num'][1];$tmpColor = $one['color'];$objPHPExcel->getActiveSheet()->getStyle($tmp)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle($tmp)->getFill()->getStartColor()->setARGB($tmpColor);}}//冻结表头if ($fixed) {// $objPHPExcel->getActiveSheet()->getStyle()->getFont()->setSize(10);//固定表头 $fixed 代表需要固定的行 第一行为2 第二行为3$objPHPExcel->getActiveSheet()->freezePane('A' . $fixed);$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$styleThinBlackBorderOutline = array('borders' => array('allborders' => array( //设置全部边框'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick),),);$h = 'A2:'. $cellName[$cellNum - 1]. (2 + $dataNum + + count($othTitle));$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);$objPHPExcel->getActiveSheet()->getStyle($h)->applyFromArray($styleThinBlackBorderOutline);//设置字体大小$objPHPExcel->getActiveSheet()->getStyle($h)->getFont()->setSize(10);$objPHPExcel->getActiveSheet()->getStyle('A2:' . $cellName[$cellNum - 1] . 2)->getFont()->setSize(11);$objPHPExcel->getActiveSheet()->getStyle($h)->getFont()->setBold(true);//表头设置$objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFont()->setSize(22);$objPHPExcel->getActiveSheet()->getStyle('A1:'. $cellName[$cellNum - 1] . '1')->applyFromArray($styleThinBlackBorderOutline);//设置行高$objPHPExcel-> getActiveSheet()-> getRowDimension('1')-> setRowHeight(30);//设置字体加粗$objPHPExcel->getActiveSheet()->getStyle()->getFont()->setBold(true);//设置字体// $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setName('Candara' );//自适应宽度// $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);}$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel');$objWriter->save('php://output');exit;}
