导出到excel合并拆解单元格操作（奇怪操作）
接到需求做一个奇怪的产品表格，时间紧任务重，开始演示，目的以下样式
目标确定开始操作！
首先数据格式为图中，我们需要先分析出这应该是一条数据但是需要合并company_list的数据重组数据代码 分割成两条数据
$newlist = [];// 重组数组foreach ($new_arr1 as $k=>$v){$temp = $v;$tempSix = $v['company_list'];$temp['company_list'] = '';foreach ($tempSix as $kk => $vv) {$temp['company_list'] = $vv;$newlist[] = $temp;}}
得到的数据为
然后我们再进行去导出，赋值直接贴代码：
$objActSheetIndex->setCellValue('A1', 'ID')->setCellValue('B1', '订单编号')->setCellValue('C1', '时间')->setCellValue('D1', '客户单位')->setCellValue('E1', '客户联系人')->setCellValue('F1', '合同类型')->setCellValue('G1', '合同主体')->setCellValue('H1', '销售人员')->setCellValue('I1', '合同类容')->setCellValue('I2', '产品名称')->setCellValue('J2', '数量')->setCellValue('K2', '成本单价')->setCellValue('L2', '供应商')->setCellValue('M2', '销售单价')->setCellValue('N2', '备注')->setCellValue('O1', '订单状态')->setCellValue('P1', '成本')->setCellValue('Q1', '合同金额')->setCellValue('R1', '利润');$new_arr1 = array_values($new_arr1);$new_arr2 = array_values($new_arr1);$count = count($newlist); //计算有多少条数据$new_arr1 = $newlist;for ($i = 3; $i <= $count+2; $i++) {$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $new_arr1[$i-3]['id']);$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $new_arr1[$i-3]['order_no']);$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $new_arr1[$i-3]['create_time']);$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $new_arr1[$i-3]['company']);$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $new_arr1[$i-3]['personnel']);$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $new_arr1[$i-3]['contract_name']);$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $new_arr1[$i-3]['contract_subject']);$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $new_arr1[$i-3]['salesman']);$objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $new_arr1[$i-3]['company_list']['goods_name']);$objPHPExcel->getActiveSheet()->setCellValue('j' . $i, $new_arr1[$i-3]['company_list']['goods_num']);$objPHPExcel->getActiveSheet()->setCellValue('k' . $i, $new_arr1[$i-3]['company_list']['goods_cost']);$objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $new_arr1[$i-3]['company_list']['supplier_name']);$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $new_arr1[$i-3]['company_list']['sales_price']);$objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $new_arr1[$i-3]['company_list']['marks']);$objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $new_arr1[$i-3]['status']);$objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $new_arr1[$i-3]['cost']);$objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, $new_arr1[$i-3]['contract_amount']);$objPHPExcel->getActiveSheet()->setCellValue('R' . $i, $new_arr1[$i-3]['profit']);}
正常赋值后导出的数据为两条，然后我们先去处理表头。因为表头有一块是需要做成上下表格的，逻辑是合并第一行和第二行的单元格，但是从i2到n2是不需要合并的
代码为：
$cellkey = array('A','B','C','D','E','F','G','H','O','P','Q','R','S');foreach ($cellkey as $k=>$v){$objPHPExcel->getActiveSheet()->mergeCells($v."1".':'.$v."2");}
合并完成后现在是两条我们需要合并成1条除了I到N剩下的都去合并代码为：
foreach ($new_arr2 as $k => $v) {$countArr[] = count($v['company_list']);}if ($countArr) {$numArr = [];$start = 3;//文档前三行是标题 所以start为4foreach ($countArr as $k => $v) {$end = ($start + $v) - 1;$numArr[] = ['start' => $start,//起始数'end' => $end,//结束数];$start = $end + 1;}}$cellkey = array('A','B','C','D','E','F','G','H','O','P','Q','R','S');$overcellkey = [];//遍历字母foreach ($cellkey as $k => $v) {foreach ($numArr as $kk => $vv) {//拼接坐标$overcellkey[] = ['start' => $v . $vv['start'],//合并开始坐标'end' => $v . $vv['end'],//合并结束坐标];}}if ($overcellkey) {foreach ($overcellkey as $k=>$v){//进行上下单元格合并。$objPHPExcel->getActiveSheet()->mergeCells($v['start'] . ':' . $v['end']);}}
然后进行导出则好，下面贴上完整的代码，由于业务逻辑不一样，所以写法可能有些不同，视业务逻辑而定，写的比较仓促，命名可能不规范，本文章也是在赶时间的时候发布，后续我会调整命名还有进行代码优化，本次为草寮模式，代码为：
/*** 导出到excel* @author Liu Yuanhang* @date -08-28* @param $expTitle 文件标题* @param $expTableData 数据* @throws \PHPExcel_Exception* @throws \PHPExcel_Reader_Exception* @throws \PHPExcel_Writer_Exception*/public function exportExcel_1($expTitle, $expTableData){$fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定vendor('PHPExcel.PHPExcel');$objPHPExcel = new \PHPExcel();/* 设置当前的sheet */$objActSheetIndex = $objPHPExcel->setActiveSheetIndex(0);$objActSheet = $objPHPExcel->getActiveSheet(0);/* sheet标题 */$objActSheet->setTitle($expTitle);$cellkey = array('A','B','C','D','E','F','G','H','O','P','Q','R','S');foreach ($cellkey as $k=>$v){$objPHPExcel->getActiveSheet()->mergeCells($v."1".':'.$v."2");}// 处理导出的数据！$new_arr1 = [];foreach ($expTableData as $ks=>$vs){if(array_key_exists($vs['id'],$new_arr1)){$new_arr1[$vs['id']]['company_list'][] = ['goods_name' => $vs['goods_name'],'goods_num' => $vs['goods_num'],'goods_cost' => $vs['goods_cost'],'supplier_name'=> $vs['supplier_name'],'sales_price' => $vs['sales_price'],'marks' => $vs['marks'],];}else{$new_arr1[$vs['id']]['id'] = $vs['id'];$new_arr1[$vs['id']]['order_no'] = $vs['order_no'];$new_arr1[$vs['id']]['company'] = $vs['company'];$new_arr1[$vs['id']]['personnel'] = $vs['personnel'];$new_arr1[$vs['id']]['contract_id'] = $vs['contract_id'];$new_arr1[$vs['id']]['contract_subject_id'] = $vs['contract_subject_id'];$new_arr1[$vs['id']]['salesman_id'] = $vs['salesman_id'];$new_arr1[$vs['id']]['status'] = $vs['status'];$new_arr1[$vs['id']]['cost'] = $vs['cost'];$new_arr1[$vs['id']]['contract_amount'] = $vs['contract_amount'];$new_arr1[$vs['id']]['profit'] = $vs['profit'];$new_arr1[$vs['id']]['contract_name'] = $vs['contract_name'];$new_arr1[$vs['id']]['salesman'] = $vs['salesman'];$new_arr1[$vs['id']]['contract_subject'] = $vs['contract_subject'];$new_arr1[$vs['id']]['create_time'] = $vs['create_time'];$new_arr1[$vs['id']]['update_time'] = $vs['update_time'];$new_arr1[$vs['id']]['company_list'][] = ['goods_name' => $vs['goods_name'],'goods_num' => $vs['goods_num'],'goods_cost' => $vs['goods_cost'],'supplier_name'=> $vs['supplier_name'],'sales_price' => $vs['sales_price'],'marks' => $vs['marks'],];}}$newlist = [];// 重组数组foreach ($new_arr1 as $k=>$v){$temp = $v;$tempSix = $v['company_list'];$temp['company_list'] = '';foreach ($tempSix as $kk => $vv) {$temp['company_list'] = $vv;$newlist[] = $temp;}}$objActSheetIndex->setCellValue('A1', 'ID')->setCellValue('B1', '订单编号')->setCellValue('C1', '时间')->setCellValue('D1', '客户单位')->setCellValue('E1', '客户联系人')->setCellValue('F1', '合同类型')->setCellValue('G1', '合同主体')->setCellValue('H1', '销售人员')->setCellValue('I1', '合同类容')->setCellValue('I2', '产品名称')->setCellValue('J2', '数量')->setCellValue('K2', '成本单价')->setCellValue('L2', '供应商')->setCellValue('M2', '销售单价')->setCellValue('N2', '备注')->setCellValue('O1', '订单状态')->setCellValue('P1', '成本')->setCellValue('Q1', '合同金额')->setCellValue('R1', '利润');$new_arr1 = array_values($new_arr1);$new_arr2 = array_values($new_arr1);$count = count($newlist); //计算有多少条数据$new_arr1 = $newlist;for ($i = 3; $i <= $count+2; $i++) {$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $new_arr1[$i-3]['id']);$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $new_arr1[$i-3]['order_no']);$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $new_arr1[$i-3]['create_time']);$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $new_arr1[$i-3]['company']);$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $new_arr1[$i-3]['personnel']);$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $new_arr1[$i-3]['contract_name']);$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $new_arr1[$i-3]['contract_subject']);$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $new_arr1[$i-3]['salesman']);$objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $new_arr1[$i-3]['company_list']['goods_name']);$objPHPExcel->getActiveSheet()->setCellValue('j' . $i, $new_arr1[$i-3]['company_list']['goods_num']);$objPHPExcel->getActiveSheet()->setCellValue('k' . $i, $new_arr1[$i-3]['company_list']['goods_cost']);$objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $new_arr1[$i-3]['company_list']['supplier_name']);$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $new_arr1[$i-3]['company_list']['sales_price']);$objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $new_arr1[$i-3]['company_list']['marks']);$objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $new_arr1[$i-3]['status']);$objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $new_arr1[$i-3]['cost']);$objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, $new_arr1[$i-3]['contract_amount']);$objPHPExcel->getActiveSheet()->setCellValue('R' . $i, $new_arr1[$i-3]['profit']);}foreach ($new_arr2 as $k => $v) {$countArr[] = count($v['company_list']);}if ($countArr) {$numArr = [];$start = 3;//文档前三行是标题 所以start为4foreach ($countArr as $k => $v) {$end = ($start + $v) - 1;$numArr[] = ['start' => $start,//起始数'end' => $end,//结束数];$start = $end + 1;}}$cellkey = array('A','B','C','D','E','F','G','H','O','P','Q','R','S');$overcellkey = [];//遍历字母foreach ($cellkey as $k => $v) {foreach ($numArr as $kk => $vv) {//拼接坐标$overcellkey[] = ['start' => $v . $vv['start'],//合并开始坐标'end' => $v . $vv['end'],//合并结束坐标];}}if ($overcellkey) {foreach ($overcellkey as $k=>$v){//进行上下单元格合并。$objPHPExcel->getActiveSheet()->mergeCells($v['start'] . ':' . $v['end']);}}$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);$objPHPExcel->getActiveSheet()->mergeCells("I1:N1");$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"');}ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码header('pragma:public');header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"');header("Content-Disposition:attachment;filename=$fileName.xls");//"xls"参考下一条备注$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//"Excel"生成版本的xlsx，"Excel5"生成版本的xls$objWriter->save('php://output');}
业务核心：先合并第一二行做出对应的表头，再将数据合并，数据合并相同的合并不相同的则不合并！
大神们有更好的意见请留言，合理博主定采纳，互相学习互相成长，博主也为小白一枚，各位可以互相交流。
全部代码贴上，虽然草寮，业务逻辑实用，转载请通知作者，否则违者必究！
