PHP附加到Excel文件 [英] PHP Append to Excel file

问题描述

尝试从PHP将数据附加到Excel文件时遇到了一些麻烦.我正在使用PHPExcel，代码如下.

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.

有人有主意吗?

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;
}

推荐答案

在添加数据之前，您正在阅读整个电子表格.这意味着PHPExcel将所有数据加载到内存中.如果您没有太多数据，但是对于较大的电子表格则停止工作，则效果很好. 由于您的程序只有有限的可用内存，因此整个数据集无法容纳在内存中，因此您的程序将崩溃.

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

或者，您可以看看如何使用Spout进行此操作. Spout是专门为解决这些内存不足错误而构建的.您可以在此处找到文档: https://github.com /box/spout/wiki/向现有电子表格添加数据

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

这篇关于PHP附加到Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！