curSheet = -1;
$this->defaultFont = 'Calibri';
$this->sheets = [['name' => 'Sheet1', 'rows' => [], 'hyperlinks' => []]];
$this->SI = []; // sharedStrings index
$this->SI_KEYS = []; // & keys
$this->F = [self::F_NORMAL]; // fonts
$this->F_KEYS = [0]; // & keys
$this->XF = [[self::N_NORMAL, self::F_NORMAL, self::A_DEFAULT]]; // styles
$this->XF_KEYS = ['N0F0A0' => 0]; // & keys
$this->template = [
'_rels/.rels' => '
',
'docProps/app.xml' => '
0
' . __CLASS__ . '',
'docProps/core.xml' => '
{DATE}
en-US
{DATE}
1
',
'xl/_rels/workbook.xml.rels' => '
{SHEETS}',
'xl/worksheets/sheet1.xml' => '
{COLS}{ROWS}{HYPERLINKS}',
'xl/worksheets/_rels/sheet1.xml.rels' => '
{HYPERLINKS}',
'xl/sharedStrings.xml' => '
{STRINGS}',
'xl/styles.xml' => '
{FONTS}
{XF}
',
'xl/workbook.xml' => '
{SHEETS}
',
'[Content_Types].xml' => '
{TYPES}
',
];
//
// 0100
1200
// Простой шаблонБудем делать генератор
}
public static function fromArray(array $rows, $sheetName = null)
{
$xlsx = new static();
return $xlsx->addSheet($rows, $sheetName);
}
public function addSheet(array $rows, $name = null)
{
$this->curSheet++;
if ($name === null) { // autogenerated sheet names
$name = 'Sheet' . ($this->curSheet + 1);
} else {
$names = [];
foreach ($this->sheets as $sh) {
$names[mb_strtoupper($sh['name'])] = 1;
}
for ($i = 0; $i < 100; $i++) {
$new_name = ($i === 0) ? $name : $name . ' (' . $i . ')';
$NEW_NAME = mb_strtoupper($new_name);
if (!isset($names[$NEW_NAME])) {
$name = $new_name;
break;
}
}
}
$this->sheets[$this->curSheet] = ['name' => $name, 'hyperlinks' => []];
if (is_array($rows) && isset($rows[0]) && is_array($rows[0])) {
$this->sheets[$this->curSheet]['rows'] = $rows;
} else {
$this->sheets[$this->curSheet]['rows'] = [];
}
return $this;
}
public function __toString()
{
$fh = fopen('php://memory', 'wb');
if (!$fh) {
return '';
}
if (!$this->_write($fh)) {
fclose($fh);
return '';
}
$size = ftell($fh);
fseek($fh, 0);
return (string) fread($fh, $size);
}
public function saveAs($filename)
{
$fh = fopen($filename, 'wb');
if (!$fh) {
return false;
}
if (!$this->_write($fh)) {
fclose($fh);
return false;
}
fclose($fh);
return true;
}
public function download()
{
return $this->downloadAs(gmdate('YmdHi') . '.xlsx');
}
public function downloadAs($filename)
{
$fh = fopen('php://memory', 'wb');
if (!$fh) {
return false;
}
if (!$this->_write($fh)) {
fclose($fh);
return false;
}
$size = ftell($fh);
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s \G\M\T', time()));
header('Content-Length: ' . $size);
while (ob_get_level()) {
ob_end_clean();
}
fseek($fh, 0);
fpassthru($fh);
fclose($fh);
return true;
}
protected function _write($fh)
{
$dirSignatureE = "\x50\x4b\x05\x06"; // end of central dir signature
$zipComments = 'Generated by ' . __CLASS__ . ' PHP class, thanks sergey.shuchkin@gmail.com';
if (!$fh) {
return false;
}
$cdrec = ''; // central directory content
$entries = 0; // number of zipped files
$cnt_sheets = count($this->sheets);
foreach ($this->template as $cfilename => $template) {
if ($cfilename === 'xl/_rels/workbook.xml.rels') {
$s = '';
for ($i = 0; $i < $cnt_sheets; $i++) {
$s .= '\n";
}
$s .= '';
$template = str_replace('{SHEETS}', $s, $template);
$this->_writeEntry($fh, $cdrec, $cfilename, $template);
$entries++;
} elseif ($cfilename === 'xl/workbook.xml') {
$s = '';
foreach ($this->sheets as $k => $v) {
$s .= '';
}
$template = str_replace('{SHEETS}', $s, $template);
$this->_writeEntry($fh, $cdrec, $cfilename, $template);
$entries++;
} elseif ($cfilename === 'docProps/core.xml') {
$template = str_replace('{DATE}', gmdate('Y-m-d\TH:i:s\Z'), $template);
$this->_writeEntry($fh, $cdrec, $cfilename, $template);
$entries++;
} elseif ($cfilename === 'xl/sharedStrings.xml') {
if (!count($this->SI)) {
$this->SI[] = 'No Data';
}
$si_cnt = count($this->SI);
$si = '' . implode("\r\n", $this->SI) . '';
$template = str_replace(['{CNT}', '{STRINGS}'], [$si_cnt, $si], $template);
$this->_writeEntry($fh, $cdrec, $cfilename, $template);
$entries++;
} elseif ($cfilename === 'xl/worksheets/sheet1.xml') {
foreach ($this->sheets as $k => $v) {
$filename = 'xl/worksheets/sheet' . ($k + 1) . '.xml';
$xml = $this->_sheetToXML($k, $template);
$this->_writeEntry($fh, $cdrec, $filename, $xml);
$entries++;
}
$xml = null;
} elseif ($cfilename === 'xl/worksheets/_rels/sheet1.xml.rels') {
foreach ($this->sheets as $k => $v) {
if (count($v['hyperlinks'])) {
$RH = [];
$filename = 'xl/worksheets/_rels/sheet' . ($k + 1) . '.xml.rels';
foreach ($v['hyperlinks'] as $h) {
$RH[] = '';
}
$xml = str_replace('{HYPERLINKS}', implode("\r\n", $RH), $template);
$this->_writeEntry($fh, $cdrec, $filename, $xml);
$entries++;
}
}
$xml = null;
} elseif ($cfilename === '[Content_Types].xml') {
$TYPES = [''];
foreach ($this->sheets as $k => $v) {
$TYPES[] = '';
if (count($v['hyperlinks'])) {
$TYPES[] = '';
}
}
$template = str_replace('{TYPES}', implode("\r\n", $TYPES), $template);
$this->_writeEntry($fh, $cdrec, $cfilename, $template);
$entries++;
} elseif ($cfilename === 'xl/styles.xml') {
$FONTS = [''];
foreach ($this->F as $f) {
$FONTS[] = ''
. ($this->defaultFontSize ? '' : '')
. ($f & self::F_BOLD ? '' : '')
. ($f & self::F_ITALIC ? '' : '')
. ($f & self::F_UNDERLINE ? '' : '')
. ($f & self::F_STRIKE ? '';
$CUR_COL = 0;
foreach ($r as $v) {
$CUR_COL++;
if (!isset($COL[$CUR_COL])) {
$COL[$CUR_COL] = 0;
}
if ($v === null || $v === '') {
continue;
}
$cname = $this->num2name($CUR_COL) . $CUR_ROW;
$ct = $cv = null;
$N = $F = $A = 0;
if (is_string($v)) {
if ($v[0] === "\0") { // RAW value as string
$v = substr($v, 1);
$vl = mb_strlen($v);
} else {
if (strpos($v, '<') !== false) { // tags?
if (strpos($v, '') !== false) {
$F += self::F_BOLD;
}
if (strpos($v, '') !== false) {
$F += self::F_ITALIC;
}
if (strpos($v, '') !== false) {
$F += self::F_UNDERLINE;
}
if (strpos($v, '') !== false) {
$F += self::F_STRIKE;
}
if (strpos($v, '') !== false) {
$A += self::A_LEFT;
}
if (strpos($v, '') !== false) {
$A += self::A_CENTER;
}
if (strpos($v, '') !== false) {
$A += self::A_RIGHT;
}
if (preg_match('/(.*?)<\/a>/i', $v, $m)) {
$h = explode('#', $m[1]);
$this->sheets[$idx]['hyperlinks'][] = ['ID' => 'rId' . (count($this->sheets[$idx]['hyperlinks']) + 1), 'R' => $cname, 'H' => $h[0], 'L' => isset($h[1]) ? $h[1] : ''];
$F = self::F_HYPERLINK; // Hyperlink
}
if (preg_match('/(.*?)<\/a>/i', $v, $m)) {
$this->sheets[$idx]['hyperlinks'][] = ['ID' => 'rId' . (count($this->sheets[$idx]['hyperlinks']) + 1), 'R' => $cname, 'H' => $m[1], 'L' => ''];
$F = self::F_HYPERLINK; // mailto hyperlink
}
$v = strip_tags($v);
} // tags
$vl = mb_strlen($v);
if ($v === '0' || preg_match('/^[-+]?[1-9]\d{0,14}$/', $v)) { // Integer as General
$cv = ltrim($v, '+');
if ($vl > 10) {
$N = self::N_INT; // [1] 0
}
} elseif (preg_match('/^[-+]?(0|[1-9]\d*)\.(\d+)$/', $v, $m)) {
$cv = ltrim($v, '+');
if (strlen($m[2]) < 3) {
$N = self::N_DEC;
}
} elseif (preg_match('/^([-+]?\d+)%$/', $v, $m)) {
$cv = round($m[1] / 100, 2);
$N = self::N_PERCENT_INT; // [9] 0%
} elseif (preg_match('/^([-+]?\d+\.\d+)%$/', $v, $m)) {
$cv = round($m[1] / 100, 4);
$N = self::N_PRECENT_DEC; // [10] 0.00%
} elseif (preg_match('/^(\d\d\d\d)-(\d\d)-(\d\d)$/', $v, $m)) {
$cv = $this->date2excel($m[1], $m[2], $m[3]);
$N = self::N_DATE; // [14] mm-dd-yy
} elseif (preg_match('/^(\d\d)\/(\d\d)\/(\d\d\d\d)$/', $v, $m)) {
$cv = $this->date2excel($m[3], $m[2], $m[1]);
$N = self::N_DATE; // [14] mm-dd-yy
} elseif (preg_match('/^(\d\d):(\d\d):(\d\d)$/', $v, $m)) {
$cv = $this->date2excel(0, 0, 0, $m[1], $m[2], $m[3]);
$N = self::N_TIME; // time
} elseif (preg_match('/^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m)) {
$cv = $this->date2excel($m[1], $m[2], $m[3], $m[4], $m[5], $m[6]);
$N = self::N_DATETIME; // [22] m/d/yy h:mm
} elseif (preg_match('/^(\d\d)\/(\d\d)\/(\d\d\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m)) {
$cv = $this->date2excel($m[3], $m[2], $m[1], $m[4], $m[5], $m[6]);
$N = self::N_DATETIME; // [22] m/d/yy h:mm
} elseif (preg_match('/^[0-9+-.]+$/', $v)) { // Long ?
$A = self::A_RIGHT;
} elseif (preg_match('/^https?:\/\/\S+$/i', $v)) {
$h = explode('#', $v);
$this->sheets[$idx]['hyperlinks'][] = ['ID' => 'rId' . (count($this->sheets[$idx]['hyperlinks']) + 1), 'R' => $cname, 'H' => $h[0], 'L' => isset($h[1]) ? $h[1] : ''];
$F = self::F_HYPERLINK; // Hyperlink
} elseif (preg_match("/^[a-zA-Z0-9_\.\-]+@([a-zA-Z0-9][a-zA-Z0-9\-]*\.)+[a-zA-Z]{2,}$/", $v)) {
$this->sheets[$idx]['hyperlinks'][] = ['ID' => 'rId' . (count($this->sheets[$idx]['hyperlinks']) + 1), 'R' => $cname, 'H' => 'mailto:' . $v, 'L' => ''];
$F = self::F_HYPERLINK; // Hyperlink
}
}
if (!$cv) {
$v = $this->esc($v);
if (mb_strlen($v) > 160) {
$ct = 'inlineStr';
$cv = $v;
} else {
$ct = 's'; // shared string
$cv = false;
$skey = '~' . $v;
if (isset($this->SI_KEYS[$skey])) {
$cv = $this->SI_KEYS[$skey];
}
if ($cv === false) {
$this->SI[] = $v;
$cv = count($this->SI) - 1;
$this->SI_KEYS[$skey] = $cv;
}
}
}
} elseif (is_int($v)) {
$vl = mb_strlen((string) $v);
$cv = $v;
} elseif (is_float($v)) {
$vl = mb_strlen((string) $v);
$cv = $v;
} elseif ($v instanceof DateTime) {
$vl = 16;
$cv = $this->date2excel($v->format('Y'), $v->format('m'), $v->format('d'), $v->format('H'), $v->format('i'), $v->format('s'));
$N = self::N_DATETIME; // [22] m/d/yy h:mm
} else {
continue;
}
$COL[$CUR_COL] = max($vl, $COL[$CUR_COL]);
$cs = 0;
if ($N + $F + $A > 0) {
if (isset($this->F_KEYS[$F])) {
$cf = $this->F_KEYS[$F];
} else {
$cf = count($this->F);
$this->F_KEYS[$F] = $cf;
$this->F[] = $F;
}
$NFA = 'N' . $N . 'F' . $cf . 'A' . $A;
if (isset($this->XF_KEYS[$NFA])) {
$cs = $this->XF_KEYS[$NFA];
}
if ($cs === 0) {
$cs = count($this->XF);
$this->XF_KEYS[$NFA] = $cs;
$this->XF[] = [$N, $cf, $A];
}
}
$row .= ''
. ($ct === 'inlineStr' ? '' . $cv . '' : '' . $cv . '') . "\r\n";
}
$ROWS[] = $row . "
\r\n";
}
foreach ($COL as $k => $max) {
$COLS[] = '';
}
$COLS[] = '';
$REF = 'A1:' . $this->num2name(count($COLS)) . $CUR_ROW;
} else {
$ROWS[] = '0
';
$REF = 'A1:A1';
}
$HYPERLINKS = [];
if (count($this->sheets[$idx]['hyperlinks'])) {
$HYPERLINKS[] = '';
foreach ($this->sheets[$idx]['hyperlinks'] as $h) {
$HYPERLINKS[] = '';
}
$HYPERLINKS[] = '';
}
//restore locale
setlocale(LC_NUMERIC, $_loc);
return str_replace(
['{REF}', '{COLS}', '{ROWS}', '{HYPERLINKS}'],
[$REF, implode("\r\n", $COLS), implode("\r\n", $ROWS), implode("\r\n", $HYPERLINKS)],
$template
);
}
public function num2name($num)
{
$numeric = ($num - 1) % 26;
$letter = chr(65 + $numeric);
$num2 = (int) (($num - 1) / 26);
if ($num2 > 0) {
return $this->num2name($num2) . $letter;
}
return $letter;
}
public function date2excel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
{
$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
if ($year === 0) {
return $excelTime;
}
// self::CALENDAR_WINDOWS_1900
$excel1900isLeapYear = True;
if (((int)$year === 1900) && ($month <= 2)) {
$excel1900isLeapYear = False;
}
$myExcelBaseDate = 2415020;
// Julian base date Adjustment
if ($month > 2) {
$month -= 3;
} else {
$month += 9;
--$year;
}
// Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
$century = substr($year, 0, 2);
$decade = substr($year, 2, 2);
$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myExcelBaseDate + $excel1900isLeapYear;
return (float) $excelDate + $excelTime;
}
public function setDefaultFont($name)
{
$this->defaultFont = $name;
return $this;
}
public function setDefaultFontSize($size)
{
$this->defaultFontSize = $size;
return $this;
}
public function esc($str)
{
// XML UTF-8: #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
// but we use fast version
return str_replace(['&', '<', '>', "\x00", "\x03", "\x0B"], ['&', '<', '>', '', '', ''], $str);
}
}