PHP附加到Excel文件

Question

I'm having a little bit of trouble trying to append data to an Excel file from PHP. I'm using PHPExcel and the code is below.

The problem is: When the files were small enough it was working great. But then the files started to get bigger and I started to get the Allowed memory size exhausted error. So, i though that instead of writing the file from scratch everytime I'd get only the new data and append to the file. But it's not writing to the file for some reason.

Does anyone have an idea?

function get_subscriptions($process, $filename){      
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');
    //define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

    /** Include PHPExcel */
    require_once dirname(__FILE__) . '/PHPExcel.php';
    require_once dirname(__FILE__) . '/PHPExcel/IOFactory.php';

    // Create new PHPExcel object
    //$objPHPExcel = new PHPExcel();

    $objPHPExcel = PHPExcel_IOFactory::load(dirname(__FILE__) . "/".$filename);
    $objPHPExcel->setActiveSheetIndex(0);
    $objSheet = $objPHPExcel->getActiveSheet();
    $last_line = $objSheet->getHighestRow()+1;
    $last_id = $objSheet->getCellByColumnAndRow(0, $last_line)->getCalculatedValue();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("FHGV")
        ->setLastModifiedBy("FHGV")
        ->setTitle("Report")
        ->setSubject("PSS Report")

    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'ID')
        ->setCellValue('B1', 'Date')
        ->setCellValue('C1', 'Name')
        ->setCellValue('D1', 'CPF')
        ->setCellValue('E1', 'RG')
        ->setCellValue('F1', 'ZIP')
        ->setCellValue('G1', 'Address')
        ->setCellValue('H1', 'No')
        ->setCellValue('I1', 'Reference')
        ->setCellValue('J1', 'City')
        ->setCellValue('K1', 'State')
        ->setCellValue('L1', 'Other')
        ->setCellValue('M1', 'Email')
        ->setCellValue('N1', 'Phone')
        ->setCellValue('O1', 'Cell Phone')
        ->setCellValue('P1', 'Birth Date')
        ->setCellValue('Q1', 'Company')
        ->setCellValue('R1', 'Position')

    global $wpdb;
    $array = $wpdb->get_results("SELECT id, date_added as 'date',
        (select value from wp_iphorm_form_entry_data dados where element_id = 1 and entry_id = id) as 'name'
        ...
    FROM wp_iphorm_form_entries subscription
    WHERE subscription.post_title = \"".$process."\" AND id>".$last_id);

    $row = $last_line+1; $col = 0;
    foreach($array as $data){
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->id);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->date);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->name);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->cpf);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->rg);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->zip);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->address);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->no);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->reference);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->city);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->state);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->others);
        $objSheet->setCellValueByColumnAndRow($col++, $row, strip_tags($data->email));
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->phone);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->cell_phone);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->birth_date);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->company);
        $objSheet->setCellValueByColumnAndRow($col++, $row, $data->position);  
        $col=0;$row++;
    }

    for($col = 'A'; $col !== 'S'; $col++) {
        $objPHPExcel->getActiveSheet()
            ->getColumnDimension($col)
            ->setAutoSize(true);
    }

    // Rename worksheet
    $objSheet->setTitle('Subscriptions');

    // Save Excel 2007 file
    $callStartTime = microtime(true);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    // Renaming the file and saving it
    $objWriter->save(str_replace('functions.php', $filename , __FILE__));
    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;
}

Answer 1

Before appending data, you are reading the entire spreadsheet. This means PHPExcel loads all the data in memory. It works well if you don't have too much data but stops working for larger spreadsheets. Because you only have a limited amount of memory available for your program, the entire data set does not fit in memory and therefore your program crashes.

You should take a look at cell caching: https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/04-Configuration-Settings.md#cell-caching

Alternatively, you can take a look at how to do this with Spout. Spout was built specifically to solve these out of memory errors. You can find documentation here: https://github.com/box/spout/wiki/Add-data-to-an-existing-spreadsheet