curSheet = -1;
$this->defaultFont = 'Calibri';
$this->sheets = array(
array(
'name' => 'Sheet1',
'rows' => array(),
'hyperlinks' => array(),
'mergecells' => array(),
),
);
$this->SI = array(); // sharedStrings index
$this->SI_KEYS = array(); // & keys
$this->F = array( self::F_NORMAL ); // fonts
$this->F_KEYS = array( 0 ); // & keys
$this->XF = array( array( self::N_NORMAL, self::F_NORMAL, self::A_DEFAULT ) ); // styles
$this->XF_KEYS = array( 'N0F0A0' => 0 ); // & keys
$this->template = array(
'_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}{MERGECELLS}{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 ) {
return ( new static() )->addSheet( $rows, $sheetName );
}
public function addSheet( array $rows, $name = null ) {
$this->curSheet++;
if ( $name === null ) { // autogenerated sheet names
$name = 'Sheet' . ( $this->curSheet + 1 );
} else {
$name = mb_substr( $name, 0, 31 );
$names = array();
foreach ( $this->sheets as $sh ) {
$names[ mb_strtoupper( $sh['name'] ) ] = 1;
}
for ( $i = 0; $i < 100; $i++ ) {
$postfix = ' (' . $i . ')';
$new_name = ( $i === 0 ) ? $name : $name . $postfix;
if ( mb_strlen( $new_name ) > 31 ) {
$new_name = mb_substr( $name, 0, 31 - mb_strlen( $postfix ) ) . $postfix;
}
$NEW_NAME = mb_strtoupper( $new_name );
if ( ! isset( $names[ $NEW_NAME ] ) ) {
$name = $new_name;
break;
}
}
}
$this->sheets[ $this->curSheet ] = array(
'name' => $name,
'hyperlinks' => array(),
'mergecells' => array(),
);
if ( isset( $rows[0] ) && is_array( $rows[0] ) ) {
$this->sheets[ $this->curSheet ]['rows'] = $rows;
} else {
$this->sheets[ $this->curSheet ]['rows'] = array();
}
return $this;
}
public function __toString() {
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://memory is an in-memory stream not supported by WP_Filesystem.
$fh = fopen( 'php://memory', 'wb' );
if ( ! $fh ) {
return '';
}
if ( ! $this->_write( $fh ) ) {
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
fclose( $fh );
return '';
}
$size = ftell( $fh );
fseek( $fh, 0 );
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fread
return (string) fread( $fh, $size );
}
public function saveAs( $filename ) {
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- Writing binary ZIP data; WP_Filesystem does not support binary write streams.
$fh = fopen( $filename, 'wb' );
if ( ! $fh ) {
return false;
}
if ( ! $this->_write( $fh ) ) {
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
fclose( $fh );
return false;
}
// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
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( array( '{CNT}', '{STRINGS}' ), array( $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 = array();
$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 = array( '' );
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 = array( '' );
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;
}
$cname = $this->num2name( $CUR_COL ) . $CUR_ROW;
if ( $v === null || $v === '' ) {
$row .= '';
continue;
}
$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'][] = array(
'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'][] = array(
'ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ),
'R' => $cname,
'H' => $m[1],
'L' => '',
);
$F = self::F_HYPERLINK; // mailto hyperlink
}
$v = wp_strip_all_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 = ( (int) $m[1] === 0 ) ? self::N_TIME : 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'][] = array(
'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'][] = array(
'ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ),
'R' => $cname,
'H' => 'mailto:' . $v,
'L' => '',
);
$F = self::F_HYPERLINK; // Hyperlink
}
if ( ( $N === self::N_DATE || $N === self::N_DATETIME ) && $cv < 0 ) {
$cv = null;
$N = 0;
}
}
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[] = array( $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( $COL ) ) . $CUR_ROW;
} else {
$ROWS[] = '0
';
$REF = 'A1:A1';
}
$MERGECELLS = array();
if ( count( $this->sheets[ $idx ]['mergecells'] ) ) {
$MERGECELLS[] = '';
$MERGECELLS[] = '';
foreach ( $this->sheets[ $idx ]['mergecells'] as $m ) {
$MERGECELLS[] = '';
}
$MERGECELLS[] = '';
}
$HYPERLINKS = array();
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(
array( '{REF}', '{COLS}', '{ROWS}', '{MERGECELLS}', '{HYPERLINKS}' ),
array( $REF, implode( "\r\n", $COLS ), implode( "\r\n", $ROWS ), implode( "\r\n", $MERGECELLS ), 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 ( (int) $year === 0 ) {
return $excelTime;
}
// self::CALENDAR_WINDOWS_1900
$excel1900isLeapYear = true;
if ( ( $year === 1900 ) && ( $month <= 2 ) ) {
$excel1900isLeapYear = false; }
$myExcelBaseDate = 2415020;
// Julian base date Adjustment
if ( $month > 2 ) {
$month -= 3;
} else {
$month += 9;
--$year;
}
$century = substr( $year, 0, 2 );
$decade = substr( $year, 2, 2 );
// Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
$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 mergeCells( $range ) {
$this->sheets[ $this->curSheet ]['mergecells'][] = $range;
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( array( '&', '<', '>', "\x00", "\x03", "\x0B" ), array( '&', '<', '>', '', '', '' ), $str );
}
}