PHPExcel使用
PHPExcel 中文使用手册详解--转载
PhpSpreadsheet使用
使用PhpSpreadsheet将数据导出为Excel文件
谈一谈php://input和php://output0
PHPExecl 实例
private function downExcel($excel=2003)
{
$this->autoLayout = false;
$this->autoRender = false;
set_time_limit(600);
$query = array();
if($this->Session->check('MonthlyTarget_AREAREQUEST_QUERY'))
{
$query = $this->Session->read('MonthlyTarget_AREAREQUEST_QUERY');
}
$this->loadModel('Dept');
$this->loadModel('DeclarationForm');
$conditions = $this->_performanceAreaFilter($query);
$count = $this->MonthlyTarget->find('count',array('conditions'=>$conditions,'order'=>'MonthlyTarget.user_id'));
if(!$count){
$this->Session->setFlash('导出失败:未找到符合条件的内容或数据为空','default',array('class'=>'albox errorbox'));
$this->redirect(array('controller'=>'MonthlyTargets','action'=>'Performance'));
}
//引入相关文件
App::uses('PHPExcel','Vender/PHPExcel');
App::uses('IOFactory','Vender/PHPExcel/PHPExcel');
App::uses('Excel5','Vender/PHPExcel/PHPExcel/Writer'); //2003版excel
App::uses('Excel2007','Vender/PHPExcel/PHPExcel/Writer'); //2007版excel
//生成excel表格
$resultPHPExcel = new PHPExcel();
$sheet = $resultPHPExcel->getActiveSheet();
//生成表头数据及格式
$columnArr = range('A','L');//与表头字段相对应 //列字母A-V
$titlesArr = array('年份','月份','区域','签约金额','到账金额','业绩金额','目标','签单完成率(%)','到账完成率(%)','到账人均单','业绩人均单','出单率(%)'); //表头
$keysArr = array('表头字段'); //表头对应字段名
//生成查询数据
$sheetDataMaxNum = 20000; //设置项 单个sheet保存数据最大值
$dataStartRow = 1; //设置项 第几行之后开始导入数据
$limit = 5000; //设置项 每次查询5000条数据
$allPages = ceil($count/$limit); //总页数
//统计行初始化
$totalSignMoney = 0;//签约金额
$totalRealSignMoney=0;//签约实际金额
$totalIntoPrice = 0;//到账金额
$totalPerformance = 0;//业绩金额
$totalDeptTarget = 0;//目标金额
for($i=1,$sheetDataNum=0; $i<=$allPages; $i++){
//查询结果数组 数据量很大时 占用内存会很大 比如几万条 十几万条 所以做分批处理 及时释放内存
$firstDeptIds = $this->_getFirstDeptIds();
$area_depts = $this->Dept->find('list',array('conditions'=>array('id'=>$firstDeptIds),'fields'=>array('id','name'),'limit'=>$limit));
$item = array();
foreach($area_depts as $k=>$v){
//区域业绩列表数据处理
//统计行数据
$num = 1;
$totalSignMoney += $item['MonthlyTarget']['total_sign_money']; //区域累积签约金额
$totalRealSignMoney += $item['MonthlyTarget']['total_real_sign_money']; //区域累积签约实际金额
$totalIntoPrice += $item['MonthlyTarget']['total_into_price']; //区域累积到账金额
$totalPerformance += $item['MonthlyTarget']['total_performance_money'];//区域累积业绩金额
$totalDeptTarget += $item['MonthlyTarget']['dept_target'];//目标金额
$deptSignMoneyFinishPercent = $totalDeptTarget>0 ? intval($totalSignMoney/$totalDeptTarget*100).'%':0;//签单完成率
$deptIntoPriceFinishPercent = $totalDeptTarget>0 ? intval($totalIntoPrice/$totalDeptTarget*100).'%':0;//到账完成率
$sheetDataNum++;
$rownum = $sheetDataNum + $dataStartRow; //考虑excel2003最大65536行的限制(可以创建新的sheet,使用多个)
//所有的单元格居中显示
$sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
foreach($columnArr as $key=>$colLetter){
$fields = explode('.',$keysArr[$key]);//分割字段名
$val = $item[$fields[0]][$fields[1]];//对应字段内容值
if($sheetDataNum > $sheetDataMaxNum){ //一个sheet最大保存20000条数据
$sheet = $resultPHPExcel->createSheet(); //创建新的sheet
$sheetDataNum = 1; $rownum = $sheetDataNum + $dataStartRow;
}
//单元格赋值输出
$sheet->setCellValue($colLetter.$rownum,h($val));
}
}
//统计行合并单元格并赋值
$sheet->setCellValue('A'.intval($rownum+$num), '总计');
$sheet->setCellValue('J'.intval($rownum+$num), '--');
$sheet->setCellValue('C'.intval($rownum+$num), '--');
$sheet->setCellValue('D'.intval($rownum+$num), $totalRealSignMoney);
$sheet->setCellValue('E'.intval($rownum+$num), $totalIntoPrice);
$sheet->setCellValue('F'.intval($rownum+$num), $totalPerformance);
$sheet->setCellValue('G'.intval($rownum+$num), $totalDeptTarget);
$sheet->setCellValue('H'.intval($rownum+$num), $deptSignMoneyFinishPercent);
$sheet->setCellValue('I'.intval($rownum+$num), $deptIntoPriceFinishPercent);
$sheet->mergeCells( 'A'.intval($rownum+$num).':'.'B'.intval($rownum+$num));
$sheet->mergeCells( 'J'.intval($rownum+$num).':'.'L'.intval($rownum+$num));
unset($item);
//运行内存跳转,防止内存溢出(最高调整到1.125G)
$memoryUsage = round(memory_get_usage(true)/1024/1024,3);
$memoryLimit = ini_get('memory_limit');
if($memoryLimit<=1024 && $memoryUsage/$memoryLimit > 0.9){ //当前内存使用超过限定值90% 并且限制值<1G 则限定值+128M
ini_set('memory_limit',($memoryLimit+128).'M');
}
}
//循环设置所有sheet的标题和表头等
$allSheets = $resultPHPExcel->getAllSheets();
foreach($allSheets as $index=>$oneSheet){
$lastCol = $columnArr[count($columnArr)-1];
$oneSheet->freezePaneByColumnAndRow(4,1); //冻结4列2行
$oneSheet->getStyle('A1:'.$lastCol.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //对齐方式
foreach($columnArr as $k=>$c){
$oneSheet->setCellValue($c.'1',$titlesArr[$k]); //第二行设置为标头
}
}
unset($allSheets);
//导出文件
$suffix = 'xls';
$className = 'PHPExcel_Writer_Excel5';
if($excel == 2007){
$suffix = 'xlsx';
$className = 'PHPExcel_Writer_Excel2007';
}
$outputFileName ='区域业绩列表'.date('Y-m-d H-i-s').'.'.$suffix;
$xlsWriter = new $className($resultPHPExcel);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:attachment;filename="'.$outputFileName.'"');
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$xlsWriter->save( "php://output" );
exit;
}
