PHPexcel表格导出_复杂表头

最新推荐文章于 2023-07-16 15:29:15 发布

zrxc001

最新推荐文章于 2023-07-16 15:29:15 发布

阅读量1.3k

点赞数 1

分类专栏： phpexcel 文章标签： php

本文链接：https://blog.csdn.net/zrxc001/article/details/105695111

版权

phpexcel 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

PHPexcel表格导出_复杂表头

/**
     * excel导出（复杂表头）
     * @param  [type]     $title     [文件名称]
     * $cellName = array(
     *      array('name' => '商家订单号', 'rowspan' => 2),
     *      array('name' => '平台订单号', 'rowspan' => 2 ),
     *      array('name' => '收件人信息', 'colspan' => 5, 'list' => array('收件人姓名', '收件人手机', '收件人座机', '收件人地址', 
     *      '收件人公司')),
     *  );
     * colspan 所占列数
     * rowspan 所占行数
     * @param  [type]     $cellName  [表头]
     * @param  [type]     $expTableData [数据]
     * @param  [type]     $topNumber [表头有几行占用]
     */
    public function exportOrderExcel($title, $cellName, $data, $topNumber = 1) {
        //引入核心文件
        vendor ('Classes/PHPExcel', BASIC_PATH);
        $objPHPExcel = new \PHPExcel();
        //定义配置
        $xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
        $fileName = $title;//文件名称
        $cellKey  = 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'
        );
        // 所有单元格（列）默认宽度
        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
        // 垂直居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 字体
        $objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
        // 表头行高
        for ($i=0; $i < $topNumber; $i++) { 
            $objPHPExcel->getActiveSheet()->getRowDimension($i + 1)->setRowHeight(30);
        }
        // 表头设置
        $num   = 0;
        $index = 0;
        foreach ($cellName as $key => $value) {
            
            if(! $value['colspan']) {
                $objPHPExcel->getActiveSheet()->mergeCells($cellKey[$key+$num]."1:".$cellKey[$key+$num].($value['rowspan']??2));
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$key+$num]."1", $value['name']);
                $objPHPExcel->getActiveSheet()->getStyle($cellKey[$key+$num]."1")->getFont()->setSize(10);
                $objPHPExcel->getActiveSheet()->getStyle($cellKey[$key+$num]."1")->getFont()->setBold(true);//设置是否加粗
            } else{
                $num += $value['colspan'] - 1;
                $index = $num - ($value['colspan'] - 1);
                $objPHPExcel->getActiveSheet()->mergeCells($cellKey[$key + $index]."1:" . $cellKey[$key  + $num]."1");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$key + $index]."1", $value['name']);
                $objPHPExcel->getActiveSheet()->getStyle($cellKey[$key + $index]."1")->getFont()->setSize(10);
                $objPHPExcel->getActiveSheet()->getStyle($cellKey[$key + $index]."1")->getFont()->setBold(true);//设置是否加粗
                foreach ($value['list'] as $k => $v) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$key+ $index + $k]."2", $v);
                }
            }
        }
        // 数据打印
        for($i = 0; $i < count($data); $i ++) {
            for($j=0; $j < count($data[$i]); $j ++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellKey[$j].($i + $topNumber + 1), $data[$i][$j]??'');
            }
        }
        
        // 导出execl
        ob_end_clean();// 防止乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }