[PHP知识库]thinkphp导出到excel，合并拆分单元格！

导出到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为4
            foreach ($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 2021-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为4
            foreach ($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'
        );//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
    }

业务核心：先合并第一二行做出对应的表头，再将数据合并，数据合并相同的合并不相同的则不合并！

大神们有更好的意见请留言，合理博主定采纳，互相学习互相成长，博主也为小白一枚，各位可以互相交流。

全部代码贴上，虽然草寮，业务逻辑实用，转载请通知作者，否则违者必究！