Как сделать экспорт большой таблицы в Excel средствами PHP?

Добрый день, уважаемые пользователи.

Имеется таблица MySQL 70 столбцов, более 150 тыс. записей

Необходимо сделать выборку и выгрузить данные в Excel.
Попробовал PHPExcel - виснет, максимально может разом порядка 1000-1500 записей.

А выборка может содержать 25 тыс., 60 тыс. записей.

Формат CSV не подходит, так как он не поддерживает редактирование формате ячеек.

Как быть подскажите?
  • Вопрос задан
  • 14339 просмотров
Пригласить эксперта
Ответы на вопрос 7
@switlle
Для решения этой задачи писал своё решение. Было просто огромное количество данных.
Перепробовал все что было на тот момент. На решения данной задачи подойдут только SAX решения.
Я думаю вы сможете поправить временные директории под себя и использовать мой код:

<?php

class Switlle_FastExcelWriter {
    private $workSheetHandler = null;
    private $stringsHandler = null;

    private $numRows = 0;
    private $curCel = 0;
    private $numStrings = 0;
    private $dirPath = '';
    private $outputFile = '';
    public function openExcelWriter($cols, $rows, $outputFile)
    {
        $dirPath = '/dev/shm/switlle-excel2007-test';
        @mkdir($dirPath);
        @mkdir($dirPath.'/data');
        @mkdir($dirPath.'/data/xl');
        @mkdir($dirPath.'/data/xl/worksheets');
        $this->dirPath = $dirPath;
        $this->outputFile = $outputFile;
        $this->workSheetHandler = fopen($dirPath.'/data/xl/worksheets/sheet1.xml', 'w+');
        $this->stringsHandler = fopen($dirPath.'/data/xl/sharedStrings.xml', 'w+');

        copy(dirname(__FILE__).'/FastExcelWriter/source-2007.xlsx', $dirPath.'/source.xlsx');

        fwrite($this->workSheetHandler, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?'.
            '><worksheet xml:space="preserve"'.
            ' xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'.
            ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'.
            '<sheetPr><outlinePr summaryBelow="1" summaryRight="1"/></sheetPr>'.
            '<dimension ref="A1:'.chr(64+$cols).$rows.'"/><sheetViews>'.
            '<sheetView tabSelected="1" workbookViewId="0" showGridLines="true" showRowColHeaders="1"/>'.
            '</sheetViews><sheetFormatPr defaultRowHeight="12.75" outlineLevelRow="0" outlineLevelCol="0"/>'.
            '<sheetData>");');
        fwrite($this->stringsHandler, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?'.
            '><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" uniqueCount="'.($rows*$cols).'">');
    }

    public function writeRowStart()
    {
        $this->numRows++;
        fwrite($this->workSheetHandler, '<row r="'.$this->numRows.'" spans="1:11">');
        $this->curCel = 0;
    }

    public function writeNumberCell($value)
    {
        $this->curCel++;
        fwrite($this->workSheetHandler, '<c r="'.chr(64+$this->curCel).$this->numRows.'"><v>'.$value.'</v></c>');
    }
    public function writeStringCell($value)
    {
        $this->curCel++;
        if (!empty($value)) {
            $value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
            $value = preg_replace( '/[\x00-\x13]/', '', $value );
            fwrite($this->stringsHandler, '<si><t>'.$value.'</t></si>');
            fwrite($this->workSheetHandler, '<c r="'.chr(64+$this->curCel).$this->numRows.'" t="s"><v>'.$this->numStrings.'</v></c>');
            $this->numStrings++;
        }
    }

    public function writeRowEnd()
    {
        fwrite($this->workSheetHandler, '</row>');
    }

    public function closeExcelWriter()
    {
        fwrite($this->workSheetHandler, '</sheetData>
<sheetProtection sheet="false" objects="false" scenarios="false" formatCells="false" formatColumns="false" formatRows="false" insertColumns="false" insertRows="false" insertHyperlinks="false" deleteColumns="false" deleteRows="false" selectLockedCells="false" sort="false" autoFilter="false" pivotTables="false" selectUnlockedCells="false"/>
<printOptions gridLines="false" gridLinesSet="true"/>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup paperSize="1" orientation="default" scale="100" fitToHeight="1" fitToWidth="1"/>
<headerFooter differentOddEven="false" differentFirst="false" scaleWithDoc="true" alignWithMargins="true">
<oddHeader/>
<oddFooter/>
<evenHeader/>
<evenFooter/>
<firstHeader/>
<firstFooter/>
</headerFooter>
</worksheet>');
        fwrite($this->stringsHandler, '</sst>');
        fclose($this->workSheetHandler);
        fclose($this->stringsHandler);

        $source = '../source.xlsx';
        $curDir = getcwd();
        chdir($this->dirPath.'/data');
        exec("zip -r $source * -O ../output.zip");
        chdir($curDir);
        rename($this->dirPath.'/output.zip', $this->outputFile);
        Switlle_Path::removeFolder($this->dirPath);
    }
}
Ответ написан
@portfelio
Видятся такие варианты:

1) Найти машину с достаточным количеством оперативной памяти. Писал через PHPExcel большие файлы, ничего не висло.
2) Сначала попробовать писать XLSX через PHPExcel, если не пройдет, то писать XLSX вручную построчно, ибо XLSX это по сути архив с XMLками и стилями.
Ответ написан
@Nikoblood
Я реализовывал подобную задачу. Использовал библиотеку phpExcelReader. Притом, что стояла задача брать произвольные столбцы не по порядку и засовывать в разные таблицы. Могу порекомендовать использовать более мощный компьютер и не грузить сразу всю выбору а циклом разбить выбору на несколько этапов.
Ответ написан
@tushev
Действительно PHPExcel для больших объемов данных не подходит.

Как решение, формируйте эксель данные в формате ODF, можно с расширением XLS. Это открытый XML формат, эксель его понимает. Формировать эти данные можно обычным print-ами, память и ресурсы при этом расходоваться не будут.
Самый простой способ пронять структуру ODF-файла, это сохранять данные из Экселя в формате ODF/XML и посмотреть как они устроены. Потом просто воспроизведите эту структуру на PHP.

Самое тупое но простое решение, сделать выгрузку в формате HTML содержащий одну таблицу, и дать ему расширение xls. Эксель такое переварит при открытии.
Ответ написан
Комментировать
Mandor
@Mandor
А еще Excel умеет подгружать данные из XML-файлов. Причем форматирование и сами данные при этом находятся в разных местах, что может быть удобным. Экспортирование из PHP в XML неограниченной длины тривиально.
Ответ написан
Комментировать
@Vitaly83vvp
На самом деле это не простой вопрос.
Вариант с формированием HTML и сохранением с расширением XLS я раньше практиковал, однако, последние версии MS Excel выдают предупреждение при открытии файла. Это немного нехорошо.
Пробовал разные библиотеки, но они все используют память, что при больших объёмах данных может просто выдать ошибку нехватки памяти.
Если оставить вариант сохранения в формате CSV, то, опять же по опыту, могу сказать, что не все знают чем открывать этот формат (несмотря на то, что он бывает ассоциирован с Excel при установке пакета Office). Сохранение в XLS/XLSX путём echo/print позволяет решить две проблемы из трёх: расходуется меньше памяти и данные сохраняются в формате Excel, но без форматирования.
Тут нет единого решения - всё зависит от конкретных требований.
Ответ написан
Комментировать
@lynnikvadim
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы