phpexcel導出 自定義資料 分頁資料 設定單元格寬 合并單元格

//導出操作
function outExcel($title,$data){
    $title="商戶清算對帳表".date('Y-m-d H:i:s',time());
    $data=array(
	    array(
		    'name'=>'資料1',
		    'head'=>array(
			    '#商戶厘清對賬',
			    '#賬單号20190417175906',
			    '#起始日期：[2019年4月17日 00:00:00]終止日期：[2019年4月23日 00:00:00]',
			    '#----------------------------------------------------------------------------财務明細清單----------------------------------------------------------------------------'
		    ),
		    'title'=>array('标題1','标題2','标題3','标題4','标題5','标題6','标題7'),
		    'content'=>array(
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
		    ),
		    'floor'=>array(
			    '#--------------------------------------------------------------------------财務明細清單結束--------------------------------------------------------------------------',
			    '#原始訂單合計：56筆，共-6515216元',
			    '#營銷成本合計：56筆，共6515216元',
			    '#導出時間：[2019年4月17日 00:00:00]',
		    )
	    ),
	    array(
		    'name'=>'資料2',
		    'head'=>array(
			    '#商戶厘清對賬',
			    '#賬單号20190417175906',
			    '#起始日期：[2019年4月17日 00:00:00]終止日期：[2019年4月23日 00:00:00]',
			    '#----------------------------------------------------------------------------财務明細清單----------------------------------------------------------------------------'
		    ),
		    'title'=>array('标題1','标題2','标題3','标題4','标題5','标題6','标題7'),
		    'content'=>array(
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
			    array('id'=>'值1','name'=>'值2','title'=>'值3','del'=>'值4','add'=>'值5','rr'=>'值6','hh'=>'值7'),
		    ),
		    'floor'=>array(
			    '#--------------------------------------------------------------------------财務明細清單結束--------------------------------------------------------------------------',
			    '#原始訂單合計：56筆，共-6515216元',
			    '#營銷成本合計：56筆，共6515216元',
			    '#導出時間：[2019年4月17日 00:00:00]',
		    )
	    ),
    );
    $alphabets = 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','AX',
    );
        
    //引入導出phpexcel
    Vendor('PHPExcel.PHPExcel#class');
    Vendor('PHPExcel.PHPExcel.IOFactory');
        
    // Create new PHPExcel object
    $objPHPExcel = new \PHPExcel();
        
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory($title);
        
    //setCellValue($a,$v)指派到單元格  $a:單元格表頭如：A2   $v:單元格值
    //mergeCells($a) 合并單元格    $a:單元格表頭如：A2:Z2
    foreach($data as $k=>$v){
	    $objPHPExcel->createSheet();
	    $objPHPExcel->setActiveSheetIndex($k);
	    foreach($v['head'] as $ka=>$va){
			    $mergeCells="A".($ka+1).":Z".($ka+1);
			    $objPHPExcel->getActiveSheet($k)->mergeCells($mergeCells);
			    $objPHPExcel->setActiveSheetIndex($k)->setCellValue($alphabets[0].($ka + 1), $va);
   		 }
  	    foreach($v['title'] as $kb=>$vb){
			    // $objPHPExcel->getActiveSheet()->getStyle($alphabets[$kb].(count($v['head'])+1))->getFont()->setBold(true);//加粗
			    $objPHPExcel->setActiveSheetIndex($k)->setCellValue($alphabets[$kb].(count($v['head'])+1), $vb);
    	}
	    $res = array_values($v['content']);
	    foreach($res as $kc=>$vc){
			    $countA=(count($v['head'])+2+$kc);
			    $item=array_values($vc);
			    for($i=0;$i<count($item);$i++){
					    $contentAlphabets=$alphabets[$i].$countA;
					    $objPHPExcel->setActiveSheetIndex($k)->setCellValue($contentAlphabets, $item[$i]." ");
    			}
   		}
    	foreach($v['floor'] as $kd=>$vd){
			    $count=(count($v['head'])+count($v['content'])+2+$kd);
			    $mergeCells="A".$count.":Z".$count;
			    $objPHPExcel->getActiveSheet($k)->mergeCells($mergeCells);
			    $floorAlphabets=$alphabets[0].$count;
			    $objPHPExcel->setActiveSheetIndex($k)->setCellValue($floorAlphabets, $vd);
	    }
    	$objPHPExcel->getActiveSheet()->setTitle($v['name']);
    }
    //設定單元格寬度
    foreach($alphabets as $k=>$v){
    		$objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(30);
    }
    
    $objPHPExcel->setActiveSheetIndex(0);
    // exit;
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=$title.xls");
    header('Cache-Control: max-age=0');
        
    // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); 
    
    $objWriter->save('php://output');
}