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;}