PHPEXCEL 合并单元格--导出

 调用

$list = DB::table('agent.dbo.retained')->orderByDesc('mydate')->limit(1000)->get()->map(function ($value) {
                return (array)$value;
            })->toArray();
            $title = ['日期', '活跃', '新增', '充值金额', '玩过游戏玩家', '新增里玩过游戏的玩家', '玩过TP玩家', '新增里玩过TP的玩家', '5局TP活跃玩家', '10局TP活跃玩家', '20局TP活跃玩家', '50局TP活跃玩家',
                '100局TP活跃玩家', '总局数', '人均局数', '总税收', '人均税收', '玩过TP玩家', '新增里玩过TP的玩家', '5局TP活跃玩家',
                '10局TP活跃玩家', '20局TP活跃玩家', '50局TP活跃玩家', '100局TP活跃玩家', '总局数', '人均局数', '总税收', '人均税收',
                '玩过RM玩家', '新增里玩过RM的玩家', '玩过RM2人玩家', '玩过RM5人玩家', '5局RM活跃玩家', '10局RM活跃玩家', '20局RM活跃玩家', '50局RM活跃玩家', '总局数', '人均局数', '总税收', '人均税收', '玩过RM玩家',
                '新增里玩过RM的玩家', '玩过RM2人玩家', '玩过RM5人玩家', '3局RM活跃玩家', '5局RM活跃玩家', '10局RM活跃玩家', '总局数', '人均局数', '总税收', '人均税收'];
            $mergeCells = ['B1:F1', 'G1:Q1', 'R1:AB1', 'AC1:AN1', 'AO1:AY1'];
            $mergeCellsValue = ['B1' => '总体数据', 'G1' => '所有玩家teenpatti玩家的数据', 'R1' => '所有玩家teenpatti付费玩家的数据', 'AC1' => '所有RUMMY玩家的数据', 'AO1' => '所有玩家RUMMY付费玩家的数据'];
            $name = '玩家留存付费';
            PHPExcelHelper::downExcel($name, $title, $list, $mergeCells, $mergeCellsValue);

助手类 

/**
     * @param string $name excel名
     * @param array $title 标题
     * @param array $data 数据
     * @param array $mergeCells 要合并的单元格
     * @param array $mergeCellsValue 合并单元格的标题和序列号
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public static function downExcel($name, $title, $data, $mergeCells, $mergeCellsValue)
    {
        // 创建对象
        $objPHPExcel = new \Maatwebsite\Excel\Classes\PHPExcel();

        // 显示错误信息
        error_reporting(E_ALL);

        // 设置水平居中
        if (!empty($mergeCells)) {
            foreach ($mergeCells as $val) {
                $objPHPExcel->getActiveSheet()->getStyle($val)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }
        }

        // 设置行高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);    //第一行行高

        //设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

        // 合并
        if (!empty($mergeCells)) {
            foreach ($mergeCells as $val) {
                $objPHPExcel->getActiveSheet()->mergeCells($val);
            }
        }

        // 合并单元格的标题
        if (!empty($mergeCellsValue)) {
            foreach ($mergeCellsValue as $key => $val) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($key, $val);
            }
        }

        $serial_number = self::serial_number($title);
        // 第二列 初始行2
        $row = 2;
        foreach ($title as $key => $value) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($serial_number[$key] . $row, $value);
        }

        // 填充数据 初始化3
        $row = 3;
        foreach ($data as $value) {
            if (!empty($value)) {
                $value = array_values($value);
                foreach ($value as $key => $val) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($serial_number[$key] . $row, $val);
                }
                $row++;
            }
        }

        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle($name);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        // 设置输出
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$name.'.xls');
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

 生成序列号

// 生成序列号
    protected static function serial_number($title)
    {
        // 初始值
        $loop = 0;
        $charnum = 65;
        $serial_number = [];
        for (; $loop < count($title); $loop++) {
            $quotient = intval($loop / 26);
            $remainder = $loop % 26;
            $f = $quotient > 0 ? chr($charnum + $quotient - 1) : '';
            $s = $remainder >= 0 ? chr($charnum + $remainder) : '';
            $serial_number[] = $f . $s;
        }
        return $serial_number;
    }