使用PHP从企业应用程序生成来自MySQL数据的大型Excel文件 [英] Generating large Excel files from MySQL data with PHP from corporate applications

问题描述

我们正在开发和维护几个系统，需要以最终用户的形式将Excel格式的报表导出。这些报告是从MySQL数据库中收集而来的，这些数据库通过一些微小的处理，通常会产生大约40000行的数据，其中10-15列，我们期望数据量的稳定增长。

We're developing and maintaining a couple of systems, which need to export reports in Excel format to the end user. The reports are gathered from a MySQL database with some trivial processing and usually result in ~40000 rows of data with 10-15 columns, we're expecting the amount of data to grow steadily.

目前，我们使用PHPExcel作为Excel代，但它不再适用于我们了。在5000行之后，内存消耗和加载时间变得无法解决，无法通过无限增加PHP对内存使用和脚本执行时间的最大限制来解决。数据的处理尽可能精简，整个问题是PHPExcel是一个记忆猪。 CSV生成会更轻，但不幸的是，由于用户需求，我们需要从我们的服务导出Excel（和Excel）。这是由于格式化要求等，所以CSV不是一个选项。

At the moment we're using PHPExcel for the Excel generation, but it's not working for us anymore. After we go above 5000 rows, the memory consumption and loading times become untolerable, and can't be solved by indefinitely increasing PHP's maximum limits for memory usage and script execution times. Processing of the data is as lean as possible, and the entire problem is with PHPExcel being a memory hog. CSV generation would be lighter, but unfortunately we're required to export Excel (and Excel alone) from our services due to user demands. This is due to formatting requirements etc., so CSV isn't an option.

任何想法/建议第三方应用程序/模块/服务/什么是生成大擅长无论是商业许可证，只要符合我们的需求，都可以将其集成到现有的PHP应用程序中并完成其工作。我们的服务通常运行在linux / php / mysql，我们可以做任何我们需要做的服务器。

Any ideas/recommendations for a third party application/module/service/what ever for generating large excels? Doesn't matter if it's a commercial licence, as long as it fits our needs, can be integrated to existing PHP applications and does its job. Our services are generally running on linux/php/mysql and we can do just about whatever we need to do with the servers.

谢谢！

推荐答案

对于这么大量的数据不推荐像PHPExcel或ApachePOI（for Java）这样的工具，因为它们的内存要求。我最近一直在努力处理类似的任务，我发现方便（但可能有点费心）将数据注入电子表格。可以实现服务器生成或更新Excel电子表格，从而实现简单的XML编辑。我有XLSX电子表格坐在服务器上，每当从dB收集数据时，我使用php解压缩它。然后我访问保存需要注入的工作表的内容的特定XML文件，并手动插入数据。之后，我压缩电子表格文件夹，以便将其作为常规XLSX文件分发。整个过程相当快速可靠。显然，与XLSX / Open XML文件的内部组织有关的几个问题和故障（例如，Excel倾向于将所有字符串存储在单独的表中，并在工作表中使用对此表的引用）。但是当注入数字和字符串数据时，并不是那么难。如果有人有兴趣，我可以提供一些代码。

For such a large amount of data I would not recommend tools like PHPExcel or ApachePOI (for Java) because of their memory requirements. I have struggled with similar task recently and I have found convenient (but maybe little bit fiddly) way to inject data into spreadsheets. Serverside generation or updating of Excel spreadsheets can be achieved thus simple XML editing. I have XLSX spreadsheet sitting on the server and every time data is gathered from dB, I unzip it using php. Then I access specific XML files that are holding contents of worksheets that need to be injected and insert data manually. Afterwards, I compress spreadsheet folder in order to distribute it as an regular XLSX file. Whole process is quite fast and reliable. Obviously, there are few issues and glitches related to inner organisation of XLSX/Open XML file (e. g. Excel tend to store all strings in separate table and use references to this table in worksheets). But when injecting only data like numbers and strings, it is not that hard. If anyone is interested, I can provide some code.

好的，这里是示例代码。我试图评论它的作用，但是可以随时要求进一步解释。

Okay, here goes sample code for this. I have tried to comment what it does, but feel free to ask for further explanation.

<?php
/** 
 * Class for serverside spreadsheet data injecting
 * Reqs: unzip.php, zip.php (containing any utility functions able to unzip files & zip folders)
 *
 * Author: Poborak
 */
class DataInjector
{    
    //spreadsheet file, we inject data into this one
    const SPREADSHEET_FILE="datafile.xlsx";   
    // specific worksheet into which data are being injected    
    const SPREADSHEET_WORKSHEET_FILE="/xl/worksheets/sheet7.xml"; 
    //working directory, spreadsheet is extracted here
    const WSPACE_DIR="Wspace";
    // query for obtaining data from DB
    const STORE_QUERY = "SELECT * FROM stores ORDER BY store_number ASC"; 

    private $dbConn;
    private $storesData;

    /**
     * @param   mysqli  $dbConn
     */
    function __construct(mysqli $dbConn) {   
        $this->dbConn = $dbConn;
    }

    /**
     * Main method for whole injection process
     * First data are gathered from DB and spreadsheet is decompressed to workspace.
     * Then injection takes place and spreadsheet is ready to be rebuilt again by zipping.
     *
     * @return   boolean    Informace o úspěchu
     */     
    public function injectData() {

        if (!$this->getStoresInfoFromDB()) return false;        
        if (!$this->explodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;                      
        if (!$this->injectDataToSpreadsheet(self::WSPACE_SUBDIR.self::SPREADSHEET_WORKSHEET_FILE)) return false;            
        if (!$this->implodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;
        return true;
    }

    /**
     * Decompress spreadsheet file to folder
     *
     * @param   string  $spreadsheet
     * @param   string  $targetFolder
     *
     * @return   boolean    success/fail 
     */   
    private function explodeSpreadsheet($spreadsheet, $targetFolder) {
        return unzip($spreadsheet,$targetFolder);
    }

    /**
     * Compress source folder to spreadsheet file
     *
     * @param   string  $spreadsheet    
     * @param   string  $sourceFolder
     *
     * @return   boolean    success/fail 
     */   
    private function implodeSpreadsheet($spreadsheet, $sourceFolder) {
        return zip($sourceFolder,$spreadsheet);
    }

    /**
     * Loads data from DB to member variable $storesDetails (as array)
     *
     * @return   boolean    success/fail 
     */ 
    private function getStoresInfoFromDb() {
        unset($this->storesData);       

        if ($stmt = $this->dbConn->prepare(self::STORE_QUERY)) {
            $stmt->execute();
            $stmt->bind_result($store_number, $store_regional_manager, $store_manager, $store_city, $store_address);
            while ($stmt->fetch()) {
                $this->storesData[trim($store_number)] = array(trim($store_regional_manager),trim($store_manager),trim($store_address),trim($store_city));
            }           
            $stmt->close();
        }   
        return true;        
    }

    /**
     * Injects data from member variable $storesDetails to spreadsheet $ws
     *
     * @param   string  $ws target worksheet
     *
     * @return   boolean    success/fail
     */ 
    private function injectDataToSpreadsheet($ws) {
         $worksheet = file_get_contents($ws);    
         if ($worksheet === false or empty($this->storesData) return false;

         $xml = simplexml_load_string($worksheet);  
         if (!$xml) return false;

        // Loop through $storesDetails array containing rows of data
        foreach ($this->storesData as $std){

            // For each row of data create new row in excel worksheet
            $newRow = $xml->sheetData->addChild('row'); 

            // Loop through columns values in rowdata
            foreach ($std as $cbd){                      
                // Save each column value into next column in worksheets row 
                 foreach ($this->storesData as $cbd){
                    $newCell = $newRow->addChild('c'); 
                    $newCell->addAttribute('t', "inlineStr");
                    $newIs = $newCell->addChild('is');
                    // text has to be saved as utf-8 (otherwise the spreadsheet file become corrupted)
                    if (!mb_check_encoding($cbd, 'utf-8')) $cbd = iconv("cp1250","utf-8",$cbd); 
                    $newT = $newIs->addChild('t',$cbd);                     
                }
             }
         }

         // Save xml data back to worksheet file
         if (file_put_contents($ws, $xml->asXML()) !== false) return true;           
    }
}
?>   

这篇关于使用PHP从企业应用程序生成来自MySQL数据的大型Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！