php"> public function export ( ) { $header = [ [ 'title' => '一级表头1' , 'children' => [ [ 'title' => '二级表头1' , ] , [ 'title' => '二级表头2' , ] , [ 'title' => '二级表头3' , ] , ] ] , [ 'title' => '一级表头2' ] , [ 'title' => '一级表头3' , 'children' => [ [ 'title' => '二级表头1' , 'children' => [ [ 'title' => '三级表头1' , ] , [ 'title' => '三级表头2' , ] , ] ] , [ 'title' => '二级表头2' , ] , [ 'title' => '二级表头3' , 'children' => [ [ 'title' => '三级表头1' , 'children' => [ [ 'title' => '四级表头1' , 'children' => [ [ 'title' => '五级表头1' ] , [ 'title' => '五级表头2' ] ] ] , [ 'title' => '四级表头2' ] ] ] , [ 'title' => '三级表头2' , ] , ] ] ] ] , [ 'title' => '一级表头4' , ] , [ 'title' => '一级表头5' , ] , ] ; $data = [ ] ; for ( $i = 0 ; $i < 100 ; $i ++ ) { $data [ ] = [ '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , '这是第' . $i . '行测试' , ] ; } $fileName = '很厉害的文件导出类' ; $xlsWriterServer = new MultiFloorXlsWriterService ( ) ; $xlsWriterServer -> setFileName ( $fileName , '这是Sheet1别名' ) ; $xlsWriterServer -> setHeader ( $header , true ) ; $xlsWriterServer -> setData ( $data ) ; $xlsWriterServer -> addSheet ( '这是Sheet2别名' ) ; $xlsWriterServer -> setHeader ( $header ) ; $xlsWriterServer -> setData ( $data ) ; $filePath = $xlsWriterServer -> output ( ) ; $xlsWriterServer -> excel Download( $filePath ) ; } < ? php namespace App\ Services ; use Vtiful\ Kernel\ Excel ; class MultiFloorXlsWriterService
{ private $defaultWidth = 16 ; private $exportType = '.xlsx' ; private $maxHeight = 1 ; private $fileName = null ; private $xlsObj ; private $fileObject ; private $format ; public function __construct ( ) { $path = base_path ( ) . '/public/uploads/excel ' ; $config = [ 'path' => $path ] ; $this -> xlsObj = ( new \ Vtiful\ Kernel\ Excel( $config ) ) ; } public function setFileName ( string $fileName = '' , string $sheetName = 'Sheet1' ) { $fileName = empty ( $fileName ) ? ( string ) time ( ) : $fileName ; $fileName .= $this -> exportType ; $this -> fileName = $fileName ; $this -> fileObject = $this -> xlsObj -> fileName ( $fileName , $sheetName ) ; $this -> format = ( new \ Vtiful\ Kernel\ Format( $this -> fileObject -> getHandle ( ) ) ) ; } public function setHeader ( array $header , bool $filter = false ) { if ( empty ( $header ) ) { throw new \ Exception( '表头数据不能为空' ) ; } if ( is_null ( $this -> fileName ) ) { self :: setFileName ( time ( ) ) ; } $colManage = self :: setHeaderNeedManage ( $header ) ; $colManage = self :: completeColMerge ( $colManage ) ; self :: queryMergeColumn ( $colManage , $filter ) ; } public function setData ( array $data ) { foreach ( $data as $row => $datum ) { foreach ( $datum as $column => $value ) { $this -> fileObject -> insertText ( $row + $this -> maxHeight , $column , $value ) ; } } } public function addSheet ( string $sheetName ) { $this -> fileObject -> addSheet ( $sheetName ) ; } public function output ( ) { return $this -> fileObject -> output ( ) ; } public function excel Download( string $filePath ) { $fileName = $this -> fileName ; $userBrowser = $_SERVER [ 'HTTP_USER_AGENT' ] ; if ( preg_match ( '/MSIE/i' , $userBrowser ) ) { $fileName = urlencode ( $fileName ) ; } else { $fileName = iconv ( 'UTF-8' , 'GBK//IGNORE' , $fileName ) ; } header ( "Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) ; header ( 'Content-Disposition: attachment;filename="' . $fileName . '"' ) ; header ( 'Content-Length: ' . filesize ( $filePath ) ) ; header ( 'Content-Transfer-Encoding: binary' ) ; header ( 'Cache-Control: must-revalidate' ) ; header ( 'Cache-Control: max-age=0' ) ; header ( 'Pragma: public' ) ; if ( ob_get_contents ( ) ) { ob_clean ( ) ; } flush ( ) ; if ( copy ( $filePath , 'php ://output' ) === false ) { throw new \ Exception( $filePath . '地址出问题了' ) ; } @unlink ( $filePath ) ; exit ( ) ; } private function setHeaderNeedManage ( array $header , int $col = 1 , int & $cursor = 0 , array & $colManage = [ ] , $parent = null , array $parentList = [ ] ) { foreach ( $header as $head ) { if ( empty ( $head [ 'title' ] ) ) { throw new \ Exception( '表头数据格式有误' ) ; } if ( is_null ( $parent ) ) { $parentList = [ ] ; $col = 1 ; } else { foreach ( $colManage as $value ) { if ( $value [ 'parent' ] == $parent ) { $parentList = $value [ 'parentList' ] ; $col = $value [ 'height' ] ; break ; } } } $column = $this -> getColumn ( $cursor ) . $col ; $colManage [ $column ] = [ 'title' => $head [ 'title' ] , 'cursor' => $cursor , 'cursorEnd' => $cursor , 'height' => $col , 'width' => $this -> defaultWidth , 'mergeStart' => $column , 'hMergeEnd' => $column , 'zMergeEnd' => $column , 'parent' => $parent , 'parentList' => $parentList , ] ; if ( isset ( $head [ 'children' ] ) && ! empty ( $head [ 'children' ] ) && is_array ( $head [ 'children' ] ) ) { $col += 1 ; $parentList [ ] = $column ; $this -> setHeaderNeedManage ( $head [ 'children' ] , $col , $cursor , $colManage , $column , $parentList ) ; } else { $cursor += 1 ; } } return $colManage ; } private function completeColMerge ( array $colManage ) { $this -> maxHeight = max ( array_column ( $colManage , 'height' ) ) ; $parentManage = array_column ( $colManage , 'parent' ) ; foreach ( $colManage as $index => $value ) { if ( ! is_null ( $value [ 'parent' ] ) && ! empty ( $value [ 'parentList' ] ) ) { foreach ( $value [ 'parentList' ] as $parent ) { $colManage [ $parent ] [ 'hMergeEnd' ] = self :: getColumn ( $value [ 'cursor' ] ) . $colManage [ $parent ] [ 'height' ] ; $colManage [ $parent ] [ 'cursorEnd' ] = $value [ 'cursor' ] ; } } $checkChildren = array_search ( $index , $parentManage ) ; if ( $value [ 'height' ] < $this -> maxHeight && ! $checkChildren ) { $colManage [ $index ] [ 'zMergeEnd' ] = self :: getColumn ( $value [ 'cursor' ] ) . $this -> maxHeight ; } } return $colManage ; } private function queryMergeColumn ( array $colManage , bool $filter ) { foreach ( $colManage as $value ) { $this -> fileObject -> mergeCells ( "{ $value [ 'mergeStart' ] } :{ $value [ 'zMergeEnd' ] } " , $value [ 'title' ] ) ; $this -> fileObject -> mergeCells ( "{ $value [ 'mergeStart' ] } :{ $value [ 'hMergeEnd' ] } " , $value [ 'title' ] ) ; if ( $value [ 'cursor' ] != $value [ 'cursorEnd' ] ) { $value [ 'width' ] = ( $value [ 'cursorEnd' ] - $value [ 'cursor' ] + 1 ) * $this -> defaultWidth ; } $toColumnStart = self :: getColumn ( $value [ 'cursor' ] ) ; $toColumnEnd = self :: getColumn ( $value [ 'cursorEnd' ] ) ; $this -> fileObject -> setColumn ( "{ $toColumnStart } :{ $toColumnEnd } " , $value [ 'width' ] ) ; } if ( $filter ) { $filterEndColumn = self :: getColumn ( end ( $colManage ) [ 'cursorEnd' ] ) . $this -> maxHeight ; $this -> fileObject -> autoFilter ( "A1:{ $filterEndColumn } " ) ; } } private function getColumn ( int $num ) { return Excel :: stringFromColumnIndex ( $num ) ; }
}