对于如此大量的数据,我不会因为内存需求而推荐像PHPExcel或ApachePOI(Java)这样的工具.我最近一直在努力完成类似的任务,我发现将数据注入电子表格方便(但可能有点繁琐).可以实现Serverside生成或更新Excel电子表格,从而实现简单的XML编辑.我将XLSX电子表格放在服务器上,每次从dB收集数据时,我都会使用PHP解压缩它.然后我访问特定的XML文件,这些文件包含需要注入的工作表的内容并手动插入数据.之后,我压缩电子表格文件夹,以便将其作为常规XLSX文件进行分发.整个过程非常快速可靠.显然,与XLSX / Open XML文件的内部组织相关的问题和故障很少(例如,Excel倾向于将所有字符串存储在单独的表中并在工作表中使用对该表的引用).但是当只注入数字和字符串等数据时,并不难.如果有人有兴趣,我可以提供一些代码.

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

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