' . "\n"; /** * The package. * * @var ZipWriter */ private ZipWriter $zip; /** * Rows held back to size the columns. * * @var array> */ private array $sample = []; /** * Sheet XML not yet handed to the ZIP stream. * * @var string */ private string $buffer = ''; /** * Rows written so far (1-based row number of the last one). * * @var int */ private int $rows = 0; /** * Whether the sheet has started streaming. * * @var bool */ private bool $started = false; /** * Whether close() ran. * * @var bool */ private bool $closed = false; /** * Sheet tab name, valid for Excel. * * @var string */ private string $sheet; /** * Constructor. * * @param resource $stream Writable stream (php://output, php://temp, ...). * @param array $types Column type by index: self::TEXT (default), NUMBER, MONEY or DATE. * @param int $decimals Decimals shown in money cells. * @param string $sheet Sheet tab name. * @param bool $compress Deflate when zlib is available. */ public function __construct( $stream, private readonly array $types = [], private readonly int $decimals = 2, string $sheet = 'Sheet1', bool $compress = true ) { $this->zip = new ZipWriter( $stream, $compress ); $this->sheet = self::sheet_name( $sheet ); } /** * Write one row. The first row is the header. * * @param array $row Cells. * @throws \LogicException After close(). */ public function row( array $row ): void { if ( $this->closed ) { throw new \LogicException( 'The XLSX file is already closed.' ); } $cells = array_map( static fn( $v ) => null === $v || false === $v ? '' : (string) $v, array_values( $row ) ); if ( ! $this->started ) { $this->sample[] = $cells; if ( count( $this->sample ) >= self::SAMPLE ) { $this->start(); } return; } $this->buffer .= $this->row_xml( $cells ); if ( strlen( $this->buffer ) >= self::BUFFER ) { $this->drain(); } } /** * Push what is written so far towards the browser. */ public function flush(): void { if ( $this->started && ! $this->closed ) { $this->drain(); $this->zip->flush(); } } /** * Finish the sheet and the package. */ public function close(): void { if ( $this->closed ) { return; } if ( ! $this->started ) { $this->start(); } $this->buffer .= ''; $this->drain(); $this->zip->end(); $this->zip->finish(); $this->closed = true; } /** * Write the fixed parts, open the sheet and replay the sample. */ private function start(): void { $this->started = true; $this->zip->add( '[Content_Types].xml', $this->content_types() ); $this->zip->add( '_rels/.rels', self::XML . '' ); $this->zip->add( 'xl/workbook.xml', $this->workbook() ); $this->zip->add( 'xl/_rels/workbook.xml.rels', self::XML . '' ); $this->zip->add( 'xl/styles.xml', $this->styles() ); $this->zip->begin( self::SHEET ); $xml = self::XML . '' . '' . ''; $widths = $this->widths(); if ( $widths ) { $xml .= ''; foreach ( $widths as $i => $width ) { $xml .= sprintf( '', $i + 1, $width ); } $xml .= ''; } $this->buffer = $xml . ''; foreach ( $this->sample as $cells ) { $this->buffer .= $this->row_xml( $cells ); } $this->sample = []; $this->drain(); } /** * Hand the XML buffer to the ZIP stream. */ private function drain(): void { if ( '' !== $this->buffer ) { $this->zip->write( $this->buffer ); $this->buffer = ''; } } /** * One . * * @param array $cells Cells. */ private function row_xml( array $cells ): string { $r = ++$this->rows; $header = 1 === $r; $xml = ''; foreach ( $cells as $i => $value ) { if ( '' === $value ) { continue; } $ref = self::column( $i ) . $r; $type = $header ? self::TEXT : ( $this->types[ $i ] ?? self::TEXT ); if ( self::NUMBER === $type || self::MONEY === $type ) { $number = self::number( $value ); if ( null !== $number ) { $style = self::MONEY === $type ? ' s="' . self::STYLE_MONEY . '"' : ''; $xml .= '' . $number . ''; continue; } } elseif ( self::DATE === $type ) { $date = self::serial( $value ); if ( null !== $date ) { $style = str_contains( $value, ':' ) ? self::STYLE_DATETIME : self::STYLE_DATE; $xml .= '' . $date . ''; continue; } } $text = self::text( $value ); $space = trim( $text ) !== $text || str_contains( $text, "\n" ) ? ' xml:space="preserve"' : ''; $style = $header ? ' s="' . self::STYLE_HEADER . '"' : ''; $xml .= '' . htmlspecialchars( $text, ENT_XML1 | ENT_QUOTES, 'UTF-8' ) . ''; } return $xml . ''; } /** * Column widths in characters, from the header and the sampled rows. * * @return array */ private function widths(): array { $widths = []; foreach ( $this->sample as $r => $cells ) { foreach ( $cells as $i => $value ) { $type = 0 === $r ? self::TEXT : ( $this->types[ $i ] ?? self::TEXT ); $len = self::length( $value ); if ( self::MONEY === $type && null !== self::number( $value ) ) { // Room for thousands separators. $len += intdiv( max( 0, strcspn( ltrim( $value, '-' ), '.,' ) - 1 ), 3 ); } elseif ( self::DATE === $type && null !== self::serial( $value ) ) { $len = str_contains( $value, ':' ) ? 16 : 10; } if ( 0 === $r ) { // Bold header text is wider. $len = (int) ceil( $len * 1.1 ); } $widths[ $i ] = max( $widths[ $i ] ?? 0, $len ); } } $out = []; $max = $widths ? max( array_keys( $widths ) ) : -1; for ( $i = 0; $i <= $max; $i++ ) { $out[ $i ] = min( 60, max( 8, ( $widths[ $i ] ?? 0 ) + 2 ) ); } return $out; } /** * A plain decimal number Excel can store exactly, normalised to a dot, or * null to keep the value as text (e.g. "007", "Guest", 16+ digit IDs). * * @param string $value Cell. */ public static function number( string $value ): ?string { if ( 1 !== preg_match( '/^-?(?:0|[1-9]\d*)(?:[.,]\d+)?$/', $value ) ) { return null; } if ( strlen( (string) preg_replace( '/\D/', '', $value ) ) > 15 ) { return null; } return str_replace( ',', '.', $value ); } /** * Excel date serial for "YYYY-MM-DD" or "YYYY-MM-DD HH:MM[:SS]", or null * to keep the value as text. * * @param string $value Cell. */ public static function serial( string $value ): ?string { if ( 1 !== preg_match( '/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?)?$/', $value, $m ) ) { return null; } $year = (int) $m[1]; $month = (int) $m[2]; $day = (int) $m[3]; $hour = (int) ( $m[4] ?? 0 ); $minute = (int) ( $m[5] ?? 0 ); $second = (int) ( $m[6] ?? 0 ); // From 1901: no Excel 1900 leap-year bug to work around. if ( $year < 1901 || ! checkdate( $month, $day, $year ) || $hour > 23 || $minute > 59 || $second > 59 ) { return null; } $days = intdiv( (int) gmmktime( 0, 0, 0, $month, $day, $year ), 86400 ) + 25569; $secs = $hour * 3600 + $minute * 60 + $second; if ( 0 === $secs ) { return (string) $days; } return rtrim( rtrim( sprintf( '%.10F', $days + $secs / 86400 ), '0' ), '.' ); } /** * Valid UTF-8, only characters XML 1.0 allows, within Excel's cell limit. * * @param string $value Cell. */ private static function text( string $value ): string { if ( 1 !== preg_match( '//u', $value ) ) { // Invalid UTF-8: replace broken sequences with U+FFFD. $value = htmlspecialchars_decode( htmlspecialchars( $value, ENT_NOQUOTES | ENT_SUBSTITUTE, 'UTF-8' ), ENT_NOQUOTES ); } $value = (string) preg_replace( '/[^\x{9}\x{A}\x{D}\x{20}-\x{D7FF}\x{E000}-\x{FFFD}\x{10000}-\x{10FFFF}]/u', '', $value ); $value = str_replace( "\r\n", "\n", $value ); if ( strlen( $value ) > self::MAX_CHARS && 1 === preg_match( '/^.{0,' . self::MAX_CHARS . '}/su', $value, $m ) ) { $value = $m[0]; } return $value; } /** * Characters, not bytes. * * @param string $value Cell. */ private static function length( string $value ): int { $count = preg_match_all( '/./su', $value ); return false === $count ? strlen( $value ) : $count; } /** * Zero-based index → column letters: 0 → A, 25 → Z, 26 → AA. * * @param int $index Column index. */ public static function column( int $index ): string { $out = ''; for ( $n = $index + 1; $n > 0; $n = intdiv( $n - 1, 26 ) ) { $out = chr( 65 + ( $n - 1 ) % 26 ) . $out; } return $out; } /** * Excel refuses tab names over 31 characters or with []:*?/\. * * @param string $name Requested name. */ private static function sheet_name( string $name ): string { $name = trim( (string) preg_replace( '/[\[\]:*?\/\\\\\x00-\x1F]/u', ' ', self::text( $name ) ), " '" ); if ( 1 === preg_match( '/^.{1,31}/u', $name, $m ) ) { return rtrim( $m[0], " '" ); } return 'Sheet1'; } /** * [Content_Types].xml. */ private function content_types(): string { return self::XML . '' . '' . '' . '' . '' . '' . ''; } /** * Workbook with one sheet. */ private function workbook(): string { return self::XML . '' . '' . '' . ''; } /** * Styles: default, bold header, money, date-time, date. */ private function styles(): string { $decimals = max( 0, min( 8, $this->decimals ) ); $money = '#,##0' . ( $decimals > 0 ? '.' . str_repeat( '0', $decimals ) : '' ); return self::XML . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . '' . ''; } }