phpExcel數據內存溢出解決辦法

2024-05-04 21:48:08

字體：大中小

來源：轉載

供稿：網友

云平臺需要進行excel數據批量導入,使用的是phpExcel工具,小曲同學的代碼都寫好后,小數據量下測試都沒有問題,可是一到正式環境下,數據超過千條,一行十列為一條數據,就報內存超出.

先看實例,代碼如下:

<?php

require_once 'PHPExcel.php';

require_once 'PHPExcel/Writer/Excel5.php';

require_once("..includemysqlconn.php");

$sdate=$_POST["sdate"];//接受傳遞過來的生成時間段

$edate=$_POST["edate"];

//$sdate='2009-01-01';

//$edate='2009-04-01';

$cancel_time=date("YmdHis");



$data=new MysqlConn();

$data->connect();

$sql="select * from employee_addminus where (oper_time between '$sdate' and '$edate') and isCanceled=0";



// 創建一個處理對象實例

$objExcel = new PHPExcel();



// 創建文件格式寫入對象實例, uncomment

$objWriter = new PHPExcel_Writer_Excel5($objExcel);



//設置文檔基本屬性

$objProps = $objExcel->getProperties();

$objProps->setCreator("章貢區醫療保險局");

$objProps->setLastModifiedBy("章貢區醫療保險局");

$objProps->setTitle("章貢區醫療保險局職工月增減變動報表");

$objProps->setSubject("章貢區醫療保險局職工月增減變動報表");

$objProps->setDescription("章貢區醫療保險局職工月增減變動報表");

$objProps->setKeywords("章貢區醫療保險局職工月增減變動報表");

$objProps->setCategory("變動報表");



//*************************************

//設置當前的sheet索引，用于后續的內容操作。

//一般只有在使用多個sheet的時候才需要顯示調用。

//缺省情況下，PHPExcel會自動創建第一個sheet被設置SheetIndex=0

$objExcel->setActiveSheetIndex(0);

$objActSheet = $objExcel->getActiveSheet();



//設置當前活動sheet的名稱

$objActSheet->setTitle('月增減變動報表');



//*************************************

//

//設置寬度，這個值和EXCEL里的不同，不知道是什么單位，略小于EXCEL中的寬度

$objActSheet->getColumnDimension('A')->setWidth(20);

$objActSheet->getColumnDimension('B')->setWidth(10);

$objActSheet->getColumnDimension('C')->setWidth(6);

$objActSheet->getColumnDimension('D')->setWidth(20);

$objActSheet->getColumnDimension('E')->setWidth(12);

$objActSheet->getColumnDimension('F')->setWidth(10);

$objActSheet->getColumnDimension('G')->setWidth(20);

$objActSheet->getColumnDimension('H')->setWidth(18);

$objActSheet->getColumnDimension('I')->setWidth(12);

$objActSheet->getColumnDimension('J')->setWidth(8);

$objActSheet->getColumnDimension('K')->setWidth(8);

$objActSheet->getColumnDimension('L')->setWidth(12);

$objActSheet->getColumnDimension('M')->setWidth(10);

$objActSheet->getColumnDimension('N')->setWidth(10);



$objActSheet->getRowDimension(1)->setRowHeight(30);

$objActSheet->getRowDimension(2)->setRowHeight(27);

$objActSheet->getRowDimension(3)->setRowHeight(16);



//設置單元格的值

$objActSheet->setCellValue('A1', '章貢區醫療保險局職工月增減變動報表');

//合并單元格

$objActSheet->mergeCells('A1:N1');

//設置樣式

$objStyleA1 = $objActSheet->getStyle('A1');

$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objFontA1 = $objStyleA1->getFont();

$objFontA1->setName('宋體');

$objFontA1->setSize(18);

$objFontA1->setBold(true);



//設置居中對齊

$objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('E2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('F2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('G2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('H2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('J2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('K2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('L2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('N2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



$objActSheet->setCellValue('A2', '現所在單位');

$objActSheet->setCellValue('B2', '姓名');

$objActSheet->setCellValue('C2', '性別');

$objActSheet->setCellValue('D2', '身份證號碼');

$objActSheet->setCellValue('E2', '參保時間');

$objActSheet->setCellValue('F2', '增減原因');

$objActSheet->setCellValue('G2', '原所在單位');

$objActSheet->setCellValue('H2', '增減時間');

$objActSheet->setCellValue('I2', '退休時間');

$objActSheet->setCellValue('J2', '原工資');

$objActSheet->setCellValue('K2', '現工資');

$objActSheet->setCellValue('L2', '定點醫院');

$objActSheet->setCellValue('M2', '操作人');

$objActSheet->setCellValue('N2', '備注');



//設置邊框

$objActSheet->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('C2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('D2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('E2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('F2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('G2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('H2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('I2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('J2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('K2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('L2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('M2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('N2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );



$query=$data->query($sql);

$i=1;

//從數據庫取值循環輸出

while($result=mysql_fetch_row($query)){

$personName=$result[1];

$idcard=$result[2];

$old_company=$result[3];

$new_company=$result[4];

$sex=$result[5];

$start_time=$result[6];

$reason=$result[7];

$retire_time=$result[8];

$old_wages=$result[9];

$new_wages=$result[10];

$hospital=$result[11];

$remarks=$result[12];

$operator=$result[13];

$oper_time=$result[14];



    $n=$i+2;



    $objActSheet->getStyle('B'.$n)->getNumberFormat()->setFormatCode('@');

    $objActSheet->getStyle('E'.$n)->getNumberFormat()->setFormatCode('@');



    $objActSheet->getRowDimension($n)->setRowHeight(16);



    $objActSheet->getStyle('A'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('A'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('B'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('C'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('C'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('D'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('D'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('E'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('E'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('F'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('F'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('G'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('G'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('H'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('H'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('I'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('I'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('J'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('J'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('K'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('K'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

        $objActSheet->getStyle('L'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('L'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('M'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('M'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

            $objActSheet->getStyle('N'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

    $objActSheet->getStyle('N'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );



    $xb="男";

    if($sex==1){

        $xb="女";

    }



    $objActSheet->setCellValue('A'.$n, $new_company);

    $objActSheet->setCellValue('B'.$n, $personName);

    $objActSheet->setCellValue('C'.$n, $xb);

    $objActSheet->setCellValue('D'.$n, ' '.$idcard.' ');

    $objActSheet->setCellValue('E'.$n, $start_time);

    $objActSheet->setCellValue('F'.$n, $reason);

    $objActSheet->setCellValue('G'.$n, $old_company);

    $objActSheet->setCellValue('H'.$n, $oper_time);

    $objActSheet->setCellValue('I'.$n, $retire_time);

    $objActSheet->setCellValue('J'.$n, $old_wages);

    $objActSheet->setCellValue('K'.$n, $new_wages);

    $objActSheet->setCellValue('L'.$n, $hospital);

    $objActSheet->setCellValue('M'.$n, $operator);

    $objActSheet->setCellValue('N'.$n, $remarks);

    $i++;

}

//*************************************

//輸出內容

//



$outputFileName = "tables/".$cancel_time."addminus.xls";

//到文件

$objWriter->save($outputFileName);

//下面這個輸出我是有個頁面用Ajax接收返回的信息

echo("<a href="tables/".$cancel_time."addminus.xls" mce_href="tables/".$cancel_time."addminus.xls" target='_blank'>點擊下載電子表</a>");

?>

小數據量沒問題,但是大數據量時出現數據導入內存溢出經過查詢之后,找到了解決辦法.

版本：1.7.6,在不進行特殊設置的情況下,phpExcel將讀取的單元格信息保存在內存中,我們可以通過如下代碼:

PHPExcel_Settings::setCacheStorageMethod();

來設置不同的緩存方式,已達到降低內存消耗的目的.

1、將單元格數據序列化后保存在內存中,代碼如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

2、將單元格序列化后再進行Gzip壓縮,然后保存在內存中,代碼如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

3、緩存在臨時的磁盤文件中,速度可能會慢一些,代碼如下:

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

4、保存在php://temp,代碼如下:

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

5、保存在memcache中,代碼如下:

PHPExcel_CachedObjectStorageFactory::cache_to_memcache

舉例,第4種方式,代碼如下:

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;

$cacheSettings = array( ' memoryCacheSize '  => '8MB'

                      );

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

第5種,代碼如下:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;

$cacheSettings = array( 'memcacheServer'  => 'localhost',

                       'memcachePort'    => 11211,

                      'cacheTime'       => 600

                     );

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);