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:
Output:
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:
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).
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);
*/
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
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($filename, PHPExcel $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($csvStream, 0, $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($stream, 3) !== (chr(239) . chr(187) . chr(191))) {
fseek($stream, 0); // 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).
Kommentar