Ankündigung

Einklappen
Keine Ankündigung bisher.

Große CSV in Exceldateien pumpen

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Scriptangebot Große CSV in Exceldateien pumpen

    Hi,

    ich hab ein Skript benötigt, das es mir ermöglicht auch große Datenmengen in Exceldateien zu schreiben. Irgendwie wollte PHPExcel da immer mehr als 2 GB Speicher dafür haben.

    Da ich mir jetzt keinen komplett eigenen Excelwriter schreiben wollte, bedien ich mich einfach bei PHPExcel, schreib die sheet1.xml und sharedStrings.xml darin aber selbst. Dank Streaming braucht das Skript nicht mal 32 MB, vermutlich sogar noch viel weniger (natürlich mehr, wenn man die Beispiel-CSV so mies wie ich generiert).

    Example:
    PHP-Code:
    <?php
    use Slimfast\CsvExcel2007Converter// add backslash <-- forum failure
    error_reporting(-1);
    ini_set('display_errors'1);
    ini_set('memory_limit''32M');
    set_time_limit(-1);
    require 
    __DIR__ '/../vendor/autoload.php';

    Common_Debug::watch('starting');

    $csvFilename __DIR__ '/test.csv';
    $delimiter ';';
    $enclosure '"';
    /*
    $data = array();
    for ($r = 0; $r <= 250000; ++$r) {
        for ($c = 0; $c < 20; ++$c) {
            $data[$r][$c] = $r . '_' . $c;
        }
    }

    Common_Debug::watch('generating data now');



    $handle = fopen($csvFilename, 'w');
    foreach ($data as $row) {
        fputcsv($handle, $row, $delimiter, $enclosure);
    }
    fclose($handle);
    */

    $excelFilename __DIR__ '/test.xlsx';


    Common_Debug::watch('converting to excel');

    $converter = new CsvExcel2007Converter();
    $converter->setCsv($csvFilename$delimiter$enclosure);
    $converter->setExcelFilename($excelFilename);
    $converter->convert();

    Common_Debug::stop('finished');

    /*
    header("Content-type: application/octet-stream", true);
    header('Content-Disposition: attachment; filename="test.xlsx"', true);
    readfile($excelFilename);
    */
    Output:
    Code:
    DEBUG STOP
    DEBUG ARG [1]:
    string(8) "finished"
    
    [01] Q:\Workspace\localhost\src\public\excel.php:46
         Common_Debug::stop("finished"(L=8))
    
    DEBUG MICROTIME
    [01] starting            > 2014-01-08 17:42:15.44414900       0.00000000
                               ...                            +   0.00100000
    [02] converting to excel > 2014-01-08 17:42:15.44514900   =   0.00100000
                               ...                            + 140.70071100
    [03] DEBUG STOP          > 2014-01-08 17:44:36.14586000   = 140.70171100
    
    DEBUG.ENABLED = 1, DEBUG.LOG = 0
    Braucht also knapp 2-3 Minuten um eine 44 MB große CSV-Datei in eine Exceldatei zu pressen (26 MB, entpackt 277 MB). Dauert entsprechend etwas das im Excel aufzumachen, aber damit muss der Anwender leben, wenn er lieber Excel statt CSV haben will.

    Klasse:
    PHP-Code:
    <?php
    namespace Slimfast;

    class 
    CsvExcel2007Converter
    {
        const 
    CSV_DELIMITER_DEFAULT ';';
        const 
    CSV_ENCLOSURE_DEFAULT '"';

        protected 
    $csvFilename;
        protected 
    $csvDelimiter self::CSV_DELIMITER_DEFAULT;
        protected 
    $csvEnclosure self::CSV_ENCLOSURE_DEFAULT;

        protected 
    $excelFilename;
        protected 
    $excelInstance;

        protected 
    $tempDir;

        public function 
    setCsv($filename$delimiter self::CSV_DELIMITER_DEFAULT$enclosure self::CSV_ENCLOSURE_DEFAULT)
        {
            
    $this->setCsvFilename($filename);
            
    $this->setCsvDelimiter($delimiter);
            
    $this->setCsvEnclosure($enclosure);
        }

        public function 
    setCsvFilename($filename)
        {
            if (!
    is_file($filename)) {
                throw new \
    InvalidArgumentException('file not found [file=' $filename ']');
            }
            if (!
    is_readable($filename)) {
                throw new \
    InvalidArgumentException('file not readable [file=' $filename ']');
            }
            
    $this->csvFilename $filename;
        }

        public function 
    getCsvFilename()
        {
            if (
    $this->csvFilename === null) {
                throw new \
    BadMethodCallException('csv filename not set');
            }
            return 
    $this->csvFilename;
        }

        public function 
    setCsvDelimiter($delimiter)
        {
            
    $this->csvDelimiter $delimiter;
        }

        public function 
    getCsvDelimiter()
        {
            return 
    $this->csvDelimiter;
        }

        public function 
    setCsvEnclosure($enclosure)
        {
            
    $this->csvEnclosure $enclosure;
        }

        public function 
    getCsvEnclosure()
        {
            return 
    $this->csvEnclosure;
        }

        public function 
    setExcel($filenamePHPExcel $instance null)
        {
            
    $this->setExcelFilename($filename);
            
    $this->setExcelInstance($instance);
        }

        public function 
    setExcelFilename($filename)
        {
            
    $this->excelFilename $filename;
        }

        public function 
    setExcelInstance(\PHPExcel $excel)
        {
            
    $this->excelInstance $excel;
        }

        public function 
    getExcelFilename()
        {
            if (
    $this->excelFilename === null) {
                throw new \
    BadMethodCallException('excel filename not set');
            }
            return 
    $this->excelFilename;
        }

        public function 
    getExcelInstance()
        {
            if (
    $this->excelInstance === null) {
                
    $this->excelInstance = new \PHPExcel();
            }
            return 
    $this->excelInstance;
        }

        public function 
    setTempDir($dir)
        {
            if (!
    is_dir($dir)) {
                throw new \
    InvalidArgumentException('temporary directory not found [dir=' $dir ']');
            }
            if (!
    is_writable($dir)) {
                throw new \
    InvalidArgumentException('temporary directory not writable [dir=' $dir ']');
            }
            
    $this->tempDir $dir;
        }

        public function 
    getTempDir()
        {
            if (
    $this->tempDir === null) {
                
    $this->tempDir sys_get_temp_dir();
            }
            return 
    $this->tempDir;
        }

        public function 
    convert()
        {
            
    $excel $this->getExcelInstance();
            
    $sheet $excel->getActiveSheet();
            
    $writer = new \PHPExcel_Writer_Excel2007($excel);
            
    $writer->save($this->getExcelFilename());

            
    $zipArchive = new \ZipArchive();
            if (!
    $zipArchive->open($this->getExcelFilename())) {
                throw new \
    RuntimeException('could not extend Excel file');
            }

            list (
    $xmlSheetFilename$xmlSharedStringsFilename) = $this->createXml();

            
    $zipArchive->deleteName('xl/worksheets/sheet1.xml');
            
    $zipArchive->addFile($xmlSheetFilename'xl/worksheets/sheet1.xml');

            
    $zipArchive->deleteName('xl/sharedStrings.xml');
            
    $zipArchive->addFile($xmlSharedStringsFilename'xl/sharedStrings.xml');

            
    $zipArchive->close();
        }

        protected function 
    createXml()
        {
            
    $xmlSheetFilename tempnam($this->getTempDir(), 'xml');
            
    $xmlSheetStream fopen($xmlSheetFilename'w');
            
    fputs($xmlSheetStream$this->createWorksheetXmlHeader());

            
    $xmlSharedStringsFilename tempnam($this->getTempDir(), 'xml');
            
    $xmlSharedStringsStream fopen($xmlSharedStringsFilename'w');
            
    fputs($xmlSharedStringsStream$this->createSharedStringsXmlHeader());

            
    $csvStream fopen($this->getCsvFilename(), 'r');
            
    $this->removeUtf8Bom($csvStream);

            
    $delimiter $this->getCsvDelimiter();
            
    $enclosure $this->getCsvEnclosure();
            
    $rowIndex 0;
            
    $maxColumnsIndex 0;
            
    $index 0;
            while (
    $row fgetcsv($csvStream0$delimiter$enclosure)) {
                ++
    $rowIndex;
                
    $maxColumnIndex count($row);
                
    $maxColumnsIndex max(0$maxColumnIndex);
                
    $xmlSheetRow '<row r="' $rowIndex '" spans="1:' $maxColumnIndex '">';
                foreach (
    $row as $columnIndex => $value) {
                    
    $coordinate = \PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex;
                    
    $xmlSheetRow .= '<c r="' $coordinate '" t="s"><v>' $index '</v></c>';
                    
    $xmlSharedStringsRow '<si><t>' $value '</t></si>';
                    
    fputs($xmlSharedStringsStream$xmlSharedStringsRow);
                    ++
    $index;
                }
                
    $xmlSheetRow .= '</row>' PHP_EOL;
                
    fputs($xmlSheetStream$xmlSheetRow);
            }
            
    fclose($csvStream);

            
    fputs($xmlSheetStream$this->createWorksheetXmlFooter());
            
    fclose($xmlSheetStream);

            
    fputs($xmlSharedStringsStream$this->createSharedStringsXmlFooter());
            
    fclose($xmlSharedStringsStream);

            return array(
    $xmlSheetFilename$xmlSharedStringsFilename);
        }

        protected function 
    createWorksheetXmlHeader()
        {
            
    $xml '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet
        xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
        xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
        <sheetData>'
    ;
            return 
    $xml;
        }

        protected function 
    createWorksheetXmlFooter()
        {
            
    $xml '
        </sheetData>
    </worksheet>'
    ;
            return 
    $xml;
        }

        protected function 
    createSharedStringsXmlHeader()
        {
            
    $xml '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
            return 
    $xml;
        }

        protected function 
    createSharedStringsXmlFooter()
        {
            
    $xml '</sst>';
            return 
    $xml;
        }

        
    /**
         * @desc not tested yet
         */
        
    protected function removeUtf8Bom($stream)
        {
            if (
    ftell($stream) !== 0) {
                return 
    false;
            }
            if (
    fgets($stream3) !== (chr(239) . chr(187) . chr(191))) {
                
    fseek($stream0); // jump back with the pointer
            
    }
            return 
    true;
        }

    }
    ?>

    Wers brauchen kann oder optimieren mag .. nur zu

    Hatte eigentlich vor, die PHPExcel_Worksheet-Klasse so anzupassen, dass sie die Daten nicht alle auf einmal hält, sondern eben dynamisch aus dem CSV streamt, aber irgendwie ist PHPExcel so dämlich geschrieben, dass man praktisch nichts anfassen kann, ohne den Lib-Code anzupassen (nur private, keine Interfaces, hart-kodierte Writer-Klassen).
    "[URL="http://www.youtube.com/watch?v=yMAa_t9k2VA&feature=youtu.be&t=25s"]Mein Name ist Lohse, ich kaufe hier ein.[/URL]"

  • #2
    Den Usern zuliebe mach bitte aus deinen PHP-Tags, Code-Tags. Sonst zerbröselts deine Namespace-Definitionen.
    [URL="https://gitter.im/php-de/chat?utm_source=share-link&utm_medium=link&utm_campaign=share-link"]PHP.de Gitter.im Chat[/URL] - [URL="https://raindrop.io/user/32178"]Meine öffentlichen Bookmarks[/URL] ← Ich habe dir geholfen ? [B][URL="https://www.amazon.de/gp/wishlist/348FHGUZWTNL0"]Beschenk mich[/URL][/B].

    Kommentar


    • #3
      Ich seh es immer gerne, wenn du hier so Sachen postest, Chriz. Finde „was Leute so tun“/Threads-die-keine-Fragen-sind immer spannend. (Auch wenn man das Forum damit vermutlich ganz gut zuballern könnte, wenn das jeder machen würde. Hm…)

      Eine explizite Angabe einer Lizenz würde ich mir noch wünschen. (MIT, CC-0, ….) Ne, halt, die müsste wohl zu der Lizenz von PHPExcel_Writer_Excel2007 (nicht nachgesehen) kompatibel sein, weil du die Klasse linkst. </Lizenzgetrolle>

      PS: Mutiger Top-Level-Namespace.

      Kommentar

      Lädt...
      X