dongye1934 2016-03-23 21:08
浏览 33
已采纳

PHP附加到Excel文件

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;
}
  • 写回答

1条回答 默认 最新

  • dpzlz08480 2016-03-24 06:36
    关注

    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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 爬取豆瓣电影相关处理
  • ¥15 手机淘宝抓清除消息接口
  • ¥15 C#无selenium
  • ¥15 LD衰减计算的结果过大
  • ¥15 用机器学习方法帮助保险公司预测哪些是欺诈行为
  • ¥15 计算300m以内的LD衰减
  • ¥15 数据爬取,python
  • ¥15 怎么看 cst中一个面的功率分布图,请说明详细步骤。类似下图
  • ¥15 为什么我的pycharm无法用pyqt6的QtWebEngine
  • ¥15 FOR循环语句显示查询超过300S错误怎么办