php导出excel操作,关于PHP文件Excel导入导出操作

PHPExcel使用

PhpSpreadsheet使用

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;

}