主页 思维拓展 PHPExcel上万条数据量优化导出方法实例
admin
发表于2013-08-13 13:33:29    只看楼主 楼主
头衔:  精益求精
注册时间: 2012-10-07
用户组: 日常管理组
发帖数:  502
金币数:  187
短消息
<?php
error_reporting(E_ALL);
set_include_path(get_include_path() . PATH_SEPARATOR . 'classes/');//設置路徑
require('includes/application_top.php');
include 'PHPExcel/PHPExcel.php';
include 'PHPExcel/PHPExcel/IOFactory.php';
//$objPHPExcel = new PHPExcel();
?>
<!DOCTYPE html>
<html>
	<head>
	<style>
	#log{
  position:fixed;
  margin:auto;
  left:0; right:0; top:0; bottom:0;
  width:600px; height:200px;
  background:#d6cff8;
  line-height:200px;
  text-align:center;
  font-size:18px;
  font-weight:bold;
  font-family:Arial;
  color:#3b8080;
}
	</style>	
	</head>
  <body>
     <div id="log">
     <?php
     if(@$_GET['p']=='' || @$_GET['p']==null){
     echo 'Generating 500 records from 0';
    }else{
    	 echo 'Generating 500 records from '.$_GET['p'];
    }
     ?>	
     </div>
<?php
if(@$_GET['p']=='' || @$_GET['p']==null){
	if(file_exists("./export/All_orders.xls")){
		unlink("./export/All_orders.xls");
	}
	file_put_contents("./export/All_orders.xls","");
}

$objReader = PHPExcel_IOFactory::createReader('Excel5');/*Excel5 for 2003 excel2007 for 2007*/
$objPHPExcel = PHPExcel_IOFactory::load("./export/All_orders.xls");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->freezePane('A2');
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
    array('fill' => array(
                'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
                'color'	=> array('argb' => 'FFCCFFCC')
            ),
          'borders' => array(
                'bottom'=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
                'right'	=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
                )
         ));
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle2->applyFromArray(
    array('fill' => array(
                'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
                'color'	=> array('argb' => 'fff4f4f4')
            )
         ));
if(@$_GET['p']=='' || @$_GET['p']==null){
	$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:H1");   
	
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
	 
	$objPHPExcel->getActiveSheet()->setTitle('All Orders Info');//設置當前工作表的名稱

	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1,'First Name');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1,'Last Name');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1,'Payment Status');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1,'Nationality');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1,'Country');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1,'Course');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1,'Order Date');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 1,'Attendance Year');
}
						
            $sheet = $objPHPExcel->getSheet(0);
            $AllRow = $sheet->getHighestRow(); // 取得总行数
$sqltmp="select c.customers_id,c.customers_firstname,c.customers_lastname,o.orders_status,o.orders_id,o.customers_id,o.date_purchased,o.customers_country,o.customers_state from orders o,customers c where c.customers_id=o.customers_id;";
$querytmp=mysql_query($sqltmp);
$numall=mysql_num_rows($querytmp); 
$payment_status=array(
'',
'Pending',
'Deposit Paid',
'Outstanding Balance Due',
'Full Amount Paid',
'Cancelled',
'Bad Debt',
'Pending - Scholarship',
'Deferred',
'Confirmed',
'Deposit Paid 2011',
'Full Amount Paid - Early Departure'
);
$sql="select c.customers_id,c.customers_firstname,c.customers_lastname,o.orders_status,o.orders_id,o.customers_id,o.date_purchased,o.customers_country,o.customers_state from orders o,customers c where c.customers_id=o.customers_id  order by o.orders_id desc limit ".(empty($_GET['p'])?0:$_GET['p']).",500";
$query=mysql_query($sql);
$num=$AllRow;
if($num>=$numall){
	echo '<script>document.getElementById("log").innerHTML="Generation has been completed,<br>Click <a href=\"./export/All_orders.xls\">here</a> to download";</script>';
	exit;}
while($arr=mysql_fetch_array($query)){
	$num++;
	if($num%2==1){
		$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "A".$num.":H".$num);  
	}
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num,$arr['customers_firstname']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num,$arr['customers_lastname']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num,$payment_status[$arr['orders_status']]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num,$arr['customers_country']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num,$arr['customers_state']);
	
	$o_course_name_query = tep_db_query("select course_id, course_name from admin_courses where course_status = '1' order by course_id asc");
	$o_course_name_array=array();
	while($o_course_name_Row = tep_db_fetch_array($o_course_name_query)){
		$o_course_name_array[] = array("id" =>$o_course_name_Row['course_id'] , "text" => $o_course_name_Row['course_name']);
	}
	
	$sql2="select orders_id,o_course_name from orders_extra_info_course where orders_id=".$arr['orders_id'];
	$coursename='';
	$query2=mysql_query($sql2);
	while($arr2=mysql_fetch_array($query2)){
		foreach($o_course_name_array as $v){
			if($v['id']==$arr2['o_course_name']){
				$coursename .= $v['text']."\r\n";
				break;
			}
		}
	}
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num,$coursename);
	$objPHPExcel->getActiveSheet()->getStyle("F".$number)->getAlignment()->setWrapText(true);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num,$arr['date_purchased']);
	
	$sql3="select orders_id,o_attendaceyear from orders_extra_info where orders_id=".$arr['orders_id'];
	$arr3=mysql_fetch_array(mysql_query($sql3));
	
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num,$arr3['o_attendaceyear']);
}
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save ("./export/All_orders.xls");
	echo '<script>window.location.href="./all_orders.php?p='.$num.'";</script>';

?>
  </body>
</html>
引用回复  把握生命里的每一分钟,没有人能随随便便成功~^_^
 
admin
发表于2013-08-13 13:34:27 1楼
头衔:  精益求精
注册时间: 2012-10-07
用户组: 日常管理组
发帖数:  502
金币数:  187
短消息
相当经典,相当强悍,特留作纪念
引用回复  把握生命里的每一分钟,没有人能随随便便成功~^_^
 
admin
发表于2013-08-13 16:55:32 2楼
头衔:  精益求精
注册时间: 2012-10-07
用户组: 日常管理组
发帖数:  502
金币数:  187
短消息
效果图
引用回复  把握生命里的每一分钟,没有人能随随便便成功~^_^
 
admin
发表于2013-08-20 16:58:27 3楼
头衔:  精益求精
注册时间: 2012-10-07
用户组: 日常管理组
发帖数:  502
金币数:  187
短消息
代码精简一下
<?php
error_reporting(E_ALL);
set_include_path(get_include_path() . PATH_SEPARATOR . 'classes/');//个人觉得用处不大,照搬以前的代码,就放着了
include 'PHPExcel/PHPExcel.php';
include 'PHPExcel/PHPExcel/IOFactory.php';
?>
<!--  页面上的提示,比如提示正在生成 -->
<!DOCTYPE html>
<html>
<head>
	<style>
	  /* div垂直居中样式 */
		#log{position:fixed;margin:auto;left:0; right:0; top:0; bottom:0;width:600px; height:200px;background:#d6cff8;line-height:200px;text-align:center;font-size:18px;font-weight:bold;font-family:Arial;color:#3b8080;}
	</style>	
</head>
<body>
     <div id="log">
     <?php
     if(@$_GET['p']=='' || @$_GET['p']==null){
     //p 就是代表生成的序号从哪里开始,类似分页一样的
     echo 'Generating 500 records from 0';
    }else{
    	 echo 'Generating 500 records from '.$_GET['p'];
    } ?>	
     </div>
<?php
/* --start 
	首次判断,p等于空的时候,如果存在xls文件,就删除,重新创建新的,
	主要作用是可以多次,反复执行这个程序。	*/
if(@$_GET['p']=='' || @$_GET['p']==null){
    if(file_exists("./export/All_orders.xls")){
        unlink("./export/All_orders.xls");
    }
    file_put_contents("./export/All_orders.xls","");
}
/* --end  */

$objReader = PHPExcel_IOFactory::createReader('Excel5');/*Excel5 for 2003 excel2007 for 2007*/
// 这个 $objReader 好像没用到,我没深究。

$objPHPExcel = PHPExcel_IOFactory::load("./export/All_orders.xls"); //读取这个xls文件
$objPHPExcel->setActiveSheetIndex(0); //设置第一张表为当前活动表
$objPHPExcel->getActiveSheet()->freezePane('A2'); //设置第一行固定,不随滚动条滚动

/*  start
		设置PHPExcel的样式,$sharedStyle1用于第一行的标题,这个颜色是绿色
		*/
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
    array('fill' => array(
                'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
                'color'	=> array('argb' => 'FFCCFFCC')
            ),
          'borders' => array(
                'bottom'=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
                'right'	=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
                )
         ));
/* end */

/*  start
		设置PHPExcel的样式,$sharedStyle2用于单元行的设置,灰色
		*/
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle2->applyFromArray(
    array('fill' => array(
                'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
                'color'	=> array('argb' => 'fff4f4f4')
            )
         ));
/* end */

if(@$_GET['p']=='' || @$_GET['p']==null){
    $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:H1");   //赋给第一行样式
    
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);  //设置每个单元格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
     
    $objPHPExcel->getActiveSheet()->setTitle('All Orders Info');//设置工作表名称

    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1,'First Name');//设置第一行单元格内容
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1,'Last Name');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1,'Payment Status');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1,'Nationality');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1,'Country');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1,'Course');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1,'Order Date');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 1,'Attendance Year');
}
                        
            $sheet = $objPHPExcel->getSheet(0);  //得到第一张表的内容,一般的excel初始是3个sheet的
            $AllRow = $sheet->getHighestRow(); // 取得总行数
/*  start  */            
$sqltmp="select * from test"; 
$querytmp=mysql_query($sqltmp);
$numall=mysql_num_rows($querytmp);  //得到数据表里需要导出的总的行数
/* end */

$sql="select * from test limit ".(empty($_GET['p'])?0:$_GET['p']).",5"; //获取从p开始的5条记录
$query=mysql_query($sql);
$num=$AllRow; //得到当前excel里的总行数
if($num>=$numall){
	//判断,当excel里的总行数大于等于数据表里的总行数时,出现下载地址,并退出程序
    echo '<script>document.getElementById("log").innerHTML="Generation has been completed,<br>Click <a href=\"./export/All_orders.xls\">here</a> to download";</script>';
    exit;}
    
while($arr=mysql_fetch_array($query)){
    $num++; //从总行数的下一行开始操作
    if($num%2==1){
        $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "A".$num.":H".$num);  //赋值给单元格样式,这样好看点,比如单数行白色,偶数行灰色
    }
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num,$arr['a']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num,$arr['b']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num,$arr['c']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num,$arr['d']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num,$arr['e']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num,$arr['f']);
    $objPHPExcel->getActiveSheet()->getStyle("F".$number)->getAlignment()->setWrapText(true); //设置单元格的格式,如果有\r\n,就换行显示
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num,$arr['g']);   
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num,$arr['h']);
}
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save ("./export/All_orders.xls"); //数据保存到到excel中
    echo '<script>window.location.href="./all_orders.php?p='.$num.'";</script>'; //循环操作
?>
  </body>
</html>
引用回复  把握生命里的每一分钟,没有人能随随便便成功~^_^

帖子已被锁定,已经无法回帖。

Powered BY YouYaX
个人自主开发论坛,从2010年10月份开发至今!

操作管理