Создание больших файлов Excel из данных MySQL с помощью PHP из корпоративных приложений

Для такого большого объема данных я бы не рекомендовал такие инструменты, как PHPExcel или ApachePOI (для Java) из-за их требований к памяти. Недавно я боролся с подобной задачей, и я нашел удобный (но, может быть, немного неудобный) способ вводить данные в электронные таблицы. Создание или обновление электронных таблиц 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;           
    }
}
?>   

Список альтернатив для PHPExcel, которые я стараюсь обновлять, находится здесь

Если вам нужна чистая скорость/производительность памяти выше всего, что может предложить PHPExcel, то единственный, который я действительно рекомендую, это расширение-оболочка Ильи для libXL, так как библиотека по-прежнему активно поддерживается.

Вы можете экспортировать в формате CSV, Excel справится с этим. Если у вас возникли проблемы с записью файла, вы всегда можете зациклить результаты (разбиение на страницы) и добавить их в файл CSV.

Попробуйте впоследствии преобразовать с помощью PHPExcel в формат .xsl или .odf, в противном случае оставьте его в CSV.

Пробовали ли вы старый Pear Excel (он же Spreadsheet_Excel_Writer: http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected)?

Обсуждение Checkuout относительно Pear и PHPExcel:
http://phpexcel.codeplex.com/discussions/240688

Попробуйте OfficeWriter. Недавно мы специально улучшили производительность больших наборов данных для финансовой компании из списка Fortune 500. Он делает гораздо больше с форматом файла, чем вам конкретно нужно (диаграммы и все, что у вас есть), но API довольно прост в использовании, и с оценкой вы можете быстро получить POC. Отказ от ответственности - я на инженерах, которые построили последнюю версию.

Еще один недостаток для вас, ребята, заключается в том, что это .NET.

А если просто распечатать таблицу?

<?php
header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=abc.xls");  //File name extension was wrong
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);

echo "<table><tr><td>Test</td><td>Test2</td></table>";