123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485 |
- <?php
- namespace excel;
- class MultiHeadExcel
- {
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- protected $max_width;
- public function __construct()
- {
- require_once(__DIR__.'/PHPExcel.php');
- }
-
- public function changehead($head=[],&$heads=[],$x=0,$y=0)
- {
- if(empty($heads[$y])){
- $heads[$y] = [];
- }
- $w = 0;
- $h = $y;
- foreach ($head as $v){
- if(!empty($v['children'])){
- $heads[$y][$x] = $v['name'];
- if($h == $y){
- $h++;
- }
- if(empty($heads[$y])){
- $heads[$h] = [];
- }
- $res = $this->changehead($v['children'],$heads,$x,$h);
- $x+=$res[3];
- }else{
- $heads[$y][$x] = $v;
- $x++;
- }
- $w++;
- }
- $this->max_width = $this->max_width>$x?$this->max_width:$x;
- return [$heads,$x,$y,$w,$h];
- }
-
- public function mergehead($head,$lenth,$letter,$row=1)
- {
- $check = [];$widthmerge = [];
- $check_ = [];$heightmerge = [];
- foreach ($head as $k=>$v) {
- $check_[$k] = [];
- for ($i = 0; $i < $lenth; $i++) {
- $check_[$k][$i] = empty($v[$i])?0:$v[$i];
- if (empty($v[$i])){
-
- if($i>0){
- if(empty($widthmerge[$k])){
- $widthmerge[$k] = [];
- }
- if(!empty($check[$i])){
- goto a;
- }else{
- $l = count($widthmerge[$k]);
- if($l>0){
- $end = $widthmerge[$k][$l-1];
- }else{
- $end = [];
- }
- if(empty($end[0])){
- $widthmerge[$k][] = [$i-1];
- }else if(empty($end[1])){
- if(($i-$end[0])==3){
- $widthmerge[$k][$l-1][1] = $i-2;
- $widthmerge[$k][] = [$i-1];
- }else{
- $widthmerge[$k][$l-1][1] = $i;
- }
- }else if(($i-$end[1])==1){
- $widthmerge[$k][$l-1][1] = $i;
- }else{
- $widthmerge[$k][] = [$i-1];
- }
- $check_[$k][$i] = $check_[$k][$i-1];
- }
- }
-
- a:
- if($k>0){
- if(empty($check_[$k][$i])){
- if(empty($heightmerge[$i])){
- $heightmerge[$i] = [$letter[$i] . ($row-1),$letter[$i] . $row];
- }else{
- $heightmerge[$i][1] = $letter[$i] . $row;
- }
- }
- }
- }else{
- $check[$i] = true;
- }
- }
- $row++;
- }
- return [$widthmerge,$heightmerge];
- }
-
- public function getA_ZZ()
- {
- $letter = range('A', 'Z');
- $x = $letter;
- $y = $letter;
- foreach ($x as $xv){
- foreach ($y as $yv){
- $letter[] = $xv.$yv;
- }
- }
- return $letter;
- }
-
- public function is_image_string($str)
- {
- if(strstr($str,'jpg')!=false
- || strstr($str,'jpeg')!=false
- || strstr($str,'png')!=false
- || strstr($str,'gif')!=false
- || strstr($str,'bmp')!=false){
- if(is_file(public_path().$str)){
- return true;
- }
- }
- return false;
- }
-
- public function export_help($xlsData=[],$head=[],$save_path='',$title='')
- {
-
- $letter = $this->getA_ZZ();
-
- $objExcel = new \PHPExcel();
-
- $objActSheet = $objExcel->getActiveSheet();
-
- $heads = $this->changehead($head);
-
- $head = $heads[0];
-
- $lenth = $this->max_width;
-
- if(empty($title)){
- $row = 1;
- }else{
-
- $styleArray = array(
- 'font' => array(
- 'bold' => true,
- 'color' => array('rgb' => 'FFFFFF'),
- 'size' => '20'
- ),
- 'fill' => array(
- 'type'=>\PHPExcel_Style_Fill::FILL_SOLID,
- 'color' => array('rgb' => '0070C0')
- ));
- $objActSheet->setCellValue("A1", ''."$title");
- $objActSheet->mergeCells('A1:'.$letter[($lenth-1)].'1');
- $objActSheet->getStyle( 'A1:'.$letter[($lenth-1)].'1')->applyFromArray($styleArray);
- $row = 2;
- }
-
- $merges = $this->mergehead($head,$lenth,$letter,$row);
-
-
- $heightmerge = $merges[1];
- foreach ($heightmerge as $v){
- $objActSheet->mergeCells($v[0].':'.$v[1]);
- }
-
- $widthmerge = $merges[0];
- for ($i = 0; $i < count($head); $i++) {
- if(!empty($widthmerge[$i])){
- foreach ($widthmerge[$i] as $v){
- if(empty($v[1])){
- $v[1] = $v[0]+1;
- }
- $objActSheet->mergeCells($letter[$v[0]].($i+$row).':'.$letter[$v[1]].($i+$row));
- }
- }
- }
-
- foreach ($head as $k=>$v) {
- for ($i = 0; $i < $lenth; $i++) {
- if (!empty($v[$i])){
- $objActSheet->setCellValue($letter[$i] . $row, ''."$v[$i]");
- }
- }
- $row++;
- }
- $row--;
-
- $objActSheet->getRowDimension($row)->setRowHeight(25);
-
- foreach ($xlsData as $k => $v) {
- $row ++;
- $i=0;
- $v = array_values($v);
-
-
- foreach($v as $jv){
- $image_slice = [];
- if(is_array($jv)){
- $image_slice = $jv;
- }
- if(is_string($jv) && strstr($jv,',')){
- $image_slice = explode(',',$jv);
- }
- if(empty($image_slice)){
- if($this->is_image_string($jv)){
- $objDrawing = new \PHPExcel_Worksheet_Drawing();
- $objDrawing->setPath(public_path().$jv);
- $objDrawing->setWidthAndHeight(30,30);
- $objDrawing->setCoordinates($letter[$i].$row);
- $objDrawing->setWorksheet($objActSheet);
- $i++;
- continue;
- }
- }else{
- $image_slice_export = [];
- foreach ($image_slice as $isv){
- if($this->is_image_string($isv)){
- $image_slice_export[] = $isv;
- }
- }
- if(!empty($image_slice_export)){
- foreach ($image_slice_export as $k=>$ise){
- $objDrawing = new \PHPExcel_Worksheet_Drawing();
- $objDrawing->setPath(public_path().$ise);
- $objDrawing->setOffsetX(35*$k);
- $objDrawing->setOffsetY(5);
- $objDrawing->setWidthAndHeight(30,30);
- $objDrawing->setCoordinates($letter[$i].$row);
- $objDrawing->setWorksheet($objActSheet);
- }
- $i++;
- continue;
- }
- }
-
- $objActSheet->setCellValue($letter[$i].$row, ' '."$v[$i]");
- $i++;
- }
-
- $objActSheet->getRowDimension($row)->setRowHeight(25);
- }
- $end=$letter[$lenth].$row;
-
- $objActSheet->getStyle('A1:'.$end)->getAlignment()
- ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
-
- $objActSheet->getStyle('A1:'.$end)->getAlignment()
- ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
-
- $width = 20;
- for ($i = 0; $i < $lenth; $i++) {
- $objActSheet->getColumnDimension($letter[$i])->setWidth($width);
- }
-
- $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
- $objWriter->save($save_path);
- }
-
-
- public function exportBook($bookData,$save_path)
- {
-
- $letter = $this->getA_ZZ();
-
- $objExcel = new \PHPExcel();
- foreach($bookData as $sheet=>$xlsData){
- $objExcel->setActiveSheetIndex($sheet);
- $objActSheet = $objExcel->getActiveSheet();
-
- $heads = $this->changehead($head);
-
- $head = $heads[0];
-
- $lenth = $this->max_width;
-
- if(empty($title)){
- $row = 1;
- }else{
-
- $styleArray = array(
- 'font' => array(
- 'bold' => true,
- 'color' => array('rgb' => 'FFFFFF'),
- 'size' => '20'
- ),
- 'fill' => array(
- 'type'=>\PHPExcel_Style_Fill::FILL_SOLID,
- 'color' => array('rgb' => '0070C0')
- ));
- $objActSheet->setCellValue("A1", ''."$title");
- $objActSheet->mergeCells('A1:'.$letter[($lenth-1)].'1');
- $objActSheet->getStyle( 'A1:'.$letter[($lenth-1)].'1')->applyFromArray($styleArray);
- $objActSheet->setTitle($title);
- $row = 2;
- }
-
- $merges = $this->mergehead($head,$lenth,$letter,$row);
-
-
- $heightmerge = $merges[1];
- foreach ($heightmerge as $v){
- $objActSheet->mergeCells($v[0].':'.$v[1]);
- }
-
- $widthmerge = $merges[0];
- for ($i = 0; $i < count($head); $i++) {
- if(!empty($widthmerge[$i])){
- foreach ($widthmerge[$i] as $v){
- if(empty($v[1])){
- $v[1] = $v[0]+1;
- }
- $objActSheet->mergeCells($letter[$v[0]].($i+$row).':'.$letter[$v[1]].($i+$row));
- }
- }
- }
-
- foreach ($head as $k=>$v) {
- for ($i = 0; $i < $lenth; $i++) {
- if (!empty($v[$i])){
- $objActSheet->setCellValue($letter[$i] . $row, ''."$v[$i]");
- }
- }
- $row++;
- }
- $row--;
-
- $objActSheet->getRowDimension($row)->setRowHeight(25);
-
- foreach ($xlsData as $k => $v) {
- $row ++;
- $i=0;
- $v = array_values($v);
-
-
- foreach($v as $jv){
- $image_slice = [];
- if(is_array($jv)){
- $image_slice = $jv;
- }
- if(is_string($jv) && strstr($jv,',')){
- $image_slice = explode(',',$jv);
- }
- if(empty($image_slice)){
- if($this->is_image_string($jv)){
- $objDrawing = new \PHPExcel_Worksheet_Drawing();
- $objDrawing->setPath(public_path().$jv);
- $objDrawing->setWidthAndHeight(30,30);
- $objDrawing->setCoordinates($letter[$i].$row);
- $objDrawing->setWorksheet($objActSheet);
- $i++;
- continue;
- }
- }else{
- $image_slice_export = [];
- foreach ($image_slice as $isv){
- if($this->is_image_string($isv)){
- $image_slice_export[] = $isv;
- }
- }
- if(!empty($image_slice_export)){
- foreach ($image_slice_export as $k=>$ise){
- $objDrawing = new \PHPExcel_Worksheet_Drawing();
- $objDrawing->setPath(public_path().$ise);
- $objDrawing->setOffsetX(35*$k);
- $objDrawing->setOffsetY(5);
- $objDrawing->setWidthAndHeight(30,30);
- $objDrawing->setCoordinates($letter[$i].$row);
- $objDrawing->setWorksheet($objActSheet);
- }
- $i++;
- continue;
- }
- }
-
- $objActSheet->setCellValue($letter[$i].$row, ' '."$v[$i]");
- $i++;
- }
-
- $objActSheet->getRowDimension($row)->setRowHeight(25);
- }
- $end=$letter[$lenth].$row;
-
- $objActSheet->getStyle('A1:'.$end)->getAlignment()
- ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
-
- $objActSheet->getStyle('A1:'.$end)->getAlignment()
- ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
-
- $width = 20;
- for ($i = 0; $i < $lenth; $i++) {
- $objActSheet->getColumnDimension($letter[$i])->setWidth($width);
- }
- }
-
- $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
- $objWriter->save($save_path);
- }
- }
|