// $data = array(
// 'note'=>'根据被考核人的上级、同级、下级的实际人数统计',
// 'title'=>array('一级1','一级2','一级3','一级4'),
// 'data'=>array(
// array(
// 'mid'=>1,
// 'companyname'=>'1',
// 'position'=>'1',
// 'name'=>'1',
// 'details'=>array(
// array(
// 'name'=>'上级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'上级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'同级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'同级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'下级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'下级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// ),
// ),
// array(
// 'mid'=>2,
// 'companyname'=>'2',
// 'position'=>'2',
// 'name'=>'2',
// 'details'=>array(
// array(
// 'name'=>'上级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'上级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'同级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'同级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'下级1',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// ),
// array(
// 'name'=>'下级2',
// 'score1'=>'11',
// 'score2'=>'22',
// 'score3'=>'33',
// 'score4'=>'44',
// )
// )
// ),
// )
// );
require 'vendor/PHPExcel/PHPExcel.php';
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator('hebang');//作者
//$objPHPExcel->getProperties()->setLastModifiedBy('Maarten Balliauw');//最后修改人
$objPHPExcel->getProperties()->setTitle('ExcelGroupReport');//标题
$objPHPExcel->getProperties()->setSubject('ExcelGroupReport');//题目
$objPHPExcel->getProperties()->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.');//描述
$objPHPExcel->getProperties()->setKeywords('office 2007 openxml php');//关键字
$objPHPExcel->getProperties()->setCategory('Test result file');//种类
$objPHPExcel->createSheet(0);
$objPHPExcel->setActiveSheetIndex(0);
$currentSheet = $objPHPExcel->getActiveSheet();//当前sheet
$allRow = $currentSheet->getHighestRow(); //获取Excel中信息的行数
$allColumn = $currentSheet->getHighestColumn();//获取Excel的列数
//\PHPExcel_Cell::columnIndexFromString($allColumn);//从字符串获取索引,AA转化成27
//\PHPExcel_Cell::stringFromColumnIndex($pColumnIndex);//从索引转化成字符串//
$currentSheet->setTitle('ExcelGroupReport');
$title_array_header = array('工号','现单位','职务','姓名','评价人');
$title_array_footer = array();// array('备注');
$title_array = array_merge($title_array_header,$data['title'],$title_array_footer);
//set title value
//$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
//$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
//$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);//合并单元格
//$objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);//分离单元格
$currentRow = 1;
foreach($title_array as $key=>$value)
{
$index = $key;
$currentColumn = \PHPExcel_Cell::stringFromColumnIndex($index);
$currentSheet->setCellValue($currentColumn.$currentRow, $value);
if($value=='评价人')
{
//评价人备注
$comment = $currentSheet->getComment($currentColumn.$currentRow);
$comment->setAuthor('PHPExcel');
$objCommentRichText = $comment->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$comment->getText()->createTextRun("\r\n");
$comment->getText()->createTextRun($data['note']);
}
$currentRow = 2;
foreach($data['data'] as $adata)
{
if(isset($endRow)){
$currentRow = $endRow+1;
}
$currentIndex = 0;
$startRow = $currentRow;
$num = count($adata['details']);
$endRow = $startRow+$num-1;
foreach($adata as $key=>$value){
if(!is_array($value)){
$currentColumn = \PHPExcel_Cell::stringFromColumnIndex($currentIndex);
$currentSheet->setCellValue($currentColumn.$startRow,$value);
$currentSheet->mergeCells($currentColumn.$startRow.':'.$currentColumn.$endRow);
}
else
{
$detailRow = $startRow;
foreach($value as $akey=>$avalue)
{
//上级1
$detailIndex = $currentIndex;
foreach($avalue as $aavalue){
$detailColumn = \PHPExcel_Cell::stringFromColumnIndex($detailIndex);
$currentSheet->setCellValue($detailColumn.$detailRow,$aavalue);
$detailIndex++;
}
$detailRow++;
}
}
$currentIndex ++;
}
//备注
// $currentIndex = $currentIndex+1;
// $currentColumn = \PHPExcel_Cell::stringFromColumnIndex($currentIndex);
// $objPHPExcel->getActiveSheet()->mergeCells($currentColumn.$startRow.':'.$currentColumn.$endRow);
// $currentSheet->setCellValue($currentColumn.$startRow,$data['note']);
$currentRow++;
}
//数据填充完毕
//返回$objPHPExcel
//$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);//不支持注释
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);//2007格式
header('Pragma: public');
header('Expires: 0');
header('Cache-Control:must-revalidate, post-check=0, pre-check=0');
header('Content-Type:application/force-download');
header('Content-Type:application/vnd.ms-execl');
header('Content-Type:application/octet-stream');
header('Content-Type:application/download');
header("Content-Disposition:attachment;filename=groupReport.xls");//输出名字
header('Content-Transfer-Encoding:binary');
$objWriter->save('php://output');
exit;