Skip to content

Instantly share code, notes, and snippets.

@iso2022jp
Created December 4, 2020 03:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save iso2022jp/aa68a1c698b6de8676a5f3792e149337 to your computer and use it in GitHub Desktop.
Save iso2022jp/aa68a1c698b6de8676a5f3792e149337 to your computer and use it in GitHub Desktop.
ZipArchive 依存の単一シート Excel 出力
<?php
declare(strict_types=1);
namespace Damn\IO;
use ZipArchive;
class ExcelWriter
{
private $zip;
private $sheetXm;
private $strings;
public function __construct(string $filename)
{
$zip = new ZipArchive();
$this->zip = $zip;
$this->strings = [];
$this->sheetXm = '';
$zip->open($filename, ZipArchive::CREATE | ZipArchive::OVERWRITE);
$this->writePrologue($zip);
}
public function __destruct()
{
$this->writeEpilogue($this->zip, $this->sheetXm, $this->strings);
$this->zip->close();
}
public function write(/* var_args or array */)
{
$fields = func_get_args();
if (count($fields) == 1 && is_array($fields[0])) {
$fields = $fields[0];
}
$xm = '<row>';
foreach ($fields as $value) {
if (is_int($value)) {
# native integer
$xm .= '<c t="n"><v>' . $value . '</v></c>';
} else {
# string
if ($value == '') {
$xm .= '<c></c>';
} else {
$value = (string)$value;
if (isset($this->strings[$value])) {
$index = $this->strings[$value];
} else {
$index = count($this->strings);
$this->strings[$value] = $index;
}
if (strpos($value, "\n") == false) {
$xm .= '<c t="s"><v>' . $index . '</v></c>';
} else {
# wordwrap
$xm .= '<c t="s" s="1"><v>' . $index . '</v></c>';
}
}
}
}
$xm .= '</row>';
$this->sheetXm .= $xm;
}
private function writePrologue(ZipArchive $zip)
{
$zip->addFromString('[Content_Types].xml', <<<'XML'
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/book.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/sheet.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/strings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>
XML
);
$zip->addFromString('_rels/.rels', <<<'XML'
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="book.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"/>
</Relationships>
XML
);
$zip->addFromString('book.xml', <<<'XML'
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets>
<sheet name="Sheet" sheetId="1" r:id="rId1"/>
</sheets>
</workbook>
XML
);
$zip->addFromString('_rels/book.xml.rels', <<<'XML'
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="sheet.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/>
<Relationship Id="rId2" Target="strings.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"/>
<Relationship Id="rId3" Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"/>
</Relationships>
XML
);
# Style 1: wordwrap
$zip->addFromString('styles.xml', <<<'XML'
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1">
<font />
</fonts>
<fills count="1">
<fill />
</fills>
<borders count="1">
<border />
</borders>
<cellStyleXfs count="1">
<xf />
</cellStyleXfs
><cellXfs count="2">
<xf />
<xf><alignment wrapText="true"/></xf>
</cellXfs>
</styleSheet>
XML
);
}
private function writeEpilogue(ZipArchive $zip, string $sheetXm, array $strings)
{
$zip->addFromString('sheet.xml', <<<"XML"
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetData>
$sheetXm
</sheetData>
</worksheet>
XML
);
$stringXm = '';
foreach ($strings as $text => $index) {
$stringXm .= '<si><t>' . strtr(htmlspecialchars((string)$text, ENT_NOQUOTES), [
' ' => '&#x20;',
"\t" => '&#x9;',
"\n" => '&#xA;',
"\v" => '&#xA;',
// "\f" => '&#xC;',
"\r" => '&#xD;',
// "\e" => '&#x1B;',
]) . '</t></si>';
}
$unique = count($strings);
$zip->addFromString('strings.xml', <<<"XML"
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" uniqueCount="$unique">
$stringXm
</sst>
XML
);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment