<?php
namespace model;
ini_set('memory_limit', \mvc::$cfg['export_memory_limit']);
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
/**
  * @method PhpSpreadsheet 操作  https://github.com/PHPOffice/PhpSpreadsheet
  * @author soul
  * @copyright 2017/6/15
  */
class excel
{
	/**
	  * @method 读取excel
	  * @param  $file 文件
	  * @author soul
	  * @copyright 2017/6/15
	  * @return 
	  */
	public function readAll($file)
	{
		//判断文件格式xls  
		if(!file_exists($file)) exitJson('excel文件'.$file.'不存在');
		$arr = [];
		try{
			
			$spreadsheet = IOFactory::load($file);
			$sheetNames = $spreadsheet->getSheetNames();
			foreach($sheetNames as $snum=>$sname)
			{
				$sheet = $spreadsheet->getSheet($snum);
				$data = $sheet->toArray('');
				$header = [
					'height'=>[],
					'width'=>[],
					'style'=>[]
				];
				if(!empty($data))
				{
					$maxcol = count($data[0]);
					$rownum = 1;
					$header['height'][] = ['key'=>$rownum, 'val'=>$sheet->getRowDimension($rownum)->getRowHeight()];
					for($coli = 1; $coli <= $maxcol; $coli++)
					{
						$col = Coordinate::stringFromColumnIndex($coli);
						$header['width'][] = ['key'=>$col, 'val'=>$sheet->getColumnDimension($col)->getWidth()];
						$cellcode = $col.$rownum;
						$header['style'][] = ['key'=>$cellcode, 'val'=>$sheet->getStyle($cellcode)->exportArray()];
					}
				}
				$arr[$snum] = [
					'header'=>$header,
					'name'=>$sname,
					'data'=>$data
				];
			}
		}catch(Exception $e) {
			exitJson('加载文件发生错误'.$file.','.$e->getMessage());
		}
		return $arr;
	}


	/**
	  * @method 读取excel
	  * @param  $file 文件
	  * @param  $sheet int|array 默认第一个  all:所有 
	  * @param  $colHeader 默认为空， 如['name', 'part'....]
	  					   则返回 [['name'=>'刹车片', 'part'=>''...]...]
	  * @author $mustMatch  $colHeader采取完全匹配
	  * @author soul
	  * @copyright 2017/6/15
	  * @return 
		 成功：["status"=>true, "data"=>[['刹车片',''..]...] 或者 [['name'=>'刹车片', 'part'=>'']...] ]
		 失败：["status"=>false, "data"=>'错误原因', 'code'=>110]
	  */
	public function read($file, $sheet = 0, $colHeader = [], $startRow = 1, $mustMatch = true)
	{
		//判断文件格式xls  
		if(!file_exists($file)) exitJson('excel文件'.$file.'不存在');
		$sheetArr = [];
		try{
			$spreadsheet = IOFactory::load($file);
			if($sheet == 'all'){
				$sheetCount = $spreadsheet->getSheetCount();
				for ($i = 0; $i < $sheetCount; $i++){
					$sheetArr[] = $i;
				}
			}else if(is_array($sheet)){
				$sheetArr = $sheet;
				
			}else{
				$sheetArr = [$sheet];
			}
		}catch(Exception $e) {
			exitJson('加载文件发生错误'.$file.','.$e->getMessage());
		}

		$arr = [];
		foreach($sheetArr as $sheetIndex)
		{
			$sheet = $spreadsheet->getSheet($sheetIndex);
			$arr[$sheetIndex] = $sheet->toArray('');
			$colHeadIndex = [];
			$temColHeader = $colHeader;
			foreach($arr[$sheetIndex] as $row=>$rowData)
			{
				if($row < $startRow) unset($arr[$sheetIndex][$row]);
				if(!empty($temColHeader))
				{
					//获取固定头对应的顺序
					if(empty($colHeadIndex))
					{
						//长度大到小排序
						if(!$mustMatch) rsort($temColHeader);
						foreach($rowData as $x=>$xv)
						{
							$xv = trim($xv);
							foreach($temColHeader as $ck=>$cname)
							{
								if(($mustMatch && $xv == $cname) || (!$mustMatch && stripos($xv, $cname) === 0))
								{
									$colHeadIndex[$x] = preg_match('/^[-+]?\d+$/', $ck)? $cname: $ck;
									unset($temColHeader[$xv]);
									break;
								}
							}
						}
						continue;
					}
					else
					{
						$tempArr = [];
						foreach($rowData as $kk=>$vv)
						{
							if(isset($colHeadIndex[$kk]))
							{
								$tempArr[$colHeadIndex[$kk]] = $vv;
							}
						}
						$rowData = $tempArr;
					}
				}
				$arr[$sheetIndex][$row] = $rowData;
			}
		}
		return $arr;
	}

	
	//读取excel，包括图片信息
	public function readAnyType($file)
	{
		//判断文件格式xls  
		if(!file_exists($file)) exitJson('excel文件'.$file.'不存在');
		$data = [];
		try{
			mkdirs($imgpath);
			$spreadsheet = IOFactory::load($file);
			$worksheet = $spreadsheet->getActiveSheet();
			//提取图片
			$imagesMap = [];
			$drawingCollection = $worksheet->getDrawingCollection();
			foreach ($drawingCollection as $drawing) {
				if ($drawing instanceof \PhpOffice\PhpSpreadsheet\Worksheet\Drawing) {
					$coordinates = $drawing->getCoordinates();
					$imginfo = [
						'type'=>'img',
						'ext'=>$drawing->getExtension(),
						'path'=>$drawing->getPath()
					];
					$imagesMap[$coordinates] = $imginfo;
				}
			}

			foreach ($worksheet->getRowIterator() as $row) {
				$rowData = [];
				foreach ($row->getCellIterator() as $cell) {
					$coordinate = $cell->getCoordinate();
					if (isset($imagesMap[$coordinate])) {
						$rowData[] = $imagesMap[$coordinate];
					} else {
						$value = $cell->getValue();
						if ($value instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
							$value = $value->getPlainText(); // 获取纯文本
						}
						$rowData[] = is_null($value) ? '' : $value;
					}
				}
				$data[] = $rowData;
			}
			return $data;
		}catch(Exception $e) {
			exitJson('加载文件发生错误'.$file.','.$e->getMessage());
		}
	}


	/**
	 * 写入Execl数据 如果超出行数自动分工作簿
	 * @param $dataArr array [必填]
	 *         存在 $dataArr['fields']为有序输出, 输出$aValue['fields']键对值
	 *         不存在 $dataArr['fields']为无序输出, 直接输出$aValue
	 		   ['aa', ['val'=>'xxx必填', 'color'=>'FF0000 颜色选填', 'width'=>20 宽度选填] ]
	 * @param $filename string [选填] 如果是保存必填
	 * @param $save bool [选填] 是否保存，默认直接下载
	 * @return Response
	 */
	public function writer($dataArr, $filename = '', $save = false)
	{
		if(empty($dataArr)) exitJson('数据不能为空');
		if(empty($filename)) $filename = date('YmdHis').'.xls';
		$fileType = stripos($filename, '.xlsx')? 'Xlsx': 'Xls';

		//实例化 Spreadsheet 对象
		$spreadsheet = new Spreadsheet();
		
		$fields = [];
		if (isset($dataArr['fields']) && !empty($dataArr['fields'])) 
		{
			$fields = $dataArr['fields'];
			unset($dataArr['fields']);
		}

		$per = 65000;
		$sheetIndex = 0;
		$sheetArr = [];
		if(count($dataArr) <= $per)
		{
			$sheetArr[0] = $dataArr;
		}
		else
		{//超过65000条的, 分工作簿
			$i = 1;
			foreach($dataArr as $k=>$v)
			{
				$sheetIndex = ceil($i/$per)-1;
				$sheetArr[$sheetIndex][$k] = $v;
				$i++;
			}
		}
		unset($dataArr);

		foreach($sheetArr as $sheetIndex=>$drows)
		{
			$workSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Sheet'.($sheetIndex+1));
			$spreadsheet->addSheet($workSheet, $sheetIndex);
			$spreadsheet->setActiveSheetIndex($sheetIndex);
			$sheet = $spreadsheet->getActiveSheet();
			$row = 1;
			$col = 'A';
			if(!empty($fields))
			{
				foreach ($fields as $filed => $val)
				{
					if($row == 1 && is_array($val) && !empty($val['width']))
						$sheet->getColumnDimension($col)->setWidth($val['width']);
					$this->setCell($sheet, $col.$row, $val);
					$col++;
				}
				$row++;
				foreach ($drows as $v) 
				{
					$col = 'A';
					foreach ($fields as $filed => $fname)
					{
						$val = isset($v[$filed])? $v[$filed]: '';
						$this->setCell($sheet, $col.$row, $val);
						$col++;
					}
					$row++;
				}
			}
			else
			{
				foreach ($drows as $v) 
				{
					$col = 'A';
					foreach ($v as $val) 
					{
						if($row == 1 && is_array($val) && !empty($val['width']))
							$sheet->getColumnDimension($col)->setWidth($val['width']);
						$this->setCell($sheet, $col.$row, $val);
						$col++;
					}
					$row++;
				}
			}
		}
		if($save)
		{
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save($filename);
			return ['file'=>$filename];
		}
		else
		{
			ob_clean();
			if($fileType == 'Xlsx')
			{
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
				header('Content-Disposition: attachment;filename="'.$filename.'"');
			}
			else
			{
				header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment;filename="'.$filename.'"');
			}
			header('Cache-Control: max-age=0');
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save('php://output');
		}
	}
	
	/**
	 * 设置单元格内容
	 * @param $val string|array [必填] 'xxx' | ['val'=>'xxx必填', 'color'=>'FF0000 颜色选填'] 
	 * @return Response
	 */
	private function setCell($sheet, $cell, $val)
	{
		$text = is_array($val) && !empty($val['val'])? $val['val']: $val;
		$sheet->setCellValue($cell, $text);
		if(stripos($text, "\n")) $sheet->getStyle($cell)->getAlignment()->setWrapText(true);
		if(is_array($val))
		{
			if(!empty($val['color'])) $sheet->getStyle($cell)->getFont()->getColor()->setRGB($val['color']);
		}
	}


	/**
	 * 写入Execl数据 如果超出行数自动分工作簿
	 * @param $dataArr array [必填]
	 *         存在 $dataArr['fields']为有序输出, 输出$aValue['fields']键对值
	 *         不存在 $dataArr['fields']为无序输出, 直接输出$aValue
	 		   ['aa', ['val'=>'xxx必填', 'color'=>'FF0000 颜色选填', 'width'=>20 宽度选填] ]
	 * @param $filename string [选填] 如果是保存必填
	 * @param $save bool [选填] 是否保存，默认直接下载
	 * @return Response
	 */
	public function writerAny($sheets, $filename = '', $save = false)
	{
		if(empty($filename)) $filename = date('YmdHis').'.xls';
		$fileType = stripos($filename, '.xlsx')? 'Xlsx': 'Xls';

		//实例化 Spreadsheet 对象
		$spreadsheet = new Spreadsheet();
		foreach($sheets as $sheetIndex=>$sh)
		{
			$workSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Sheet'.($sheetIndex+1));
			$spreadsheet->addSheet($workSheet, $sheetIndex);
			$spreadsheet->setActiveSheetIndex($sheetIndex);
			if(!empty($sh['name'])) 
				$workSheet->setTitle($sh['name']);
			$sheet = $spreadsheet->getActiveSheet();
			
			$row = 1;
			$col = 'A';
			$drows = $sh['data'];
			foreach ($drows as $v) 
			{
				$col = 'A';
				foreach ($v as $val) 
				{
					$cell = $col.$row;
					$isimg = false;
					if(is_array($val)){
						if(!empty($val['img'])){
							$isimg = true;
							$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
							$drawing->setPath($val['img']);
							$drawing->setCoordinates($cell);
							$drawing->setWorksheet($spreadsheet->getActiveSheet());
						}else{
							$sheet->setCellValueExplicit($cell, $val['name'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
							if(stripos($val['name'], "\n")) $sheet->getStyle($cell)->getAlignment()->setWrapText(true);
						}
					}else{
						$sheet->setCellValueExplicit($cell, $val, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
						if(stripos($val, "\n")) $sheet->getStyle($cell)->getAlignment()->setWrapText(true);
					}
					if($row == 1 || (!empty($sh['header']['bgrow']) && in_array($row, $sh['header']['bgrow']))){
						$sheet->getStyle($cell)->getFont()->setBold(true);
						$sheet->getStyle($cell)->getFont()->getColor()->setRGB('FFFFFF');
						$sheet->getStyle($cell)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('002F5F');
					}
					$col++;
				}
				$row++;
			}
			if(!empty($sh['header']))
			{
				$header = $sh['header'];
				if(!empty($header['style']))
				{
					foreach($header['style'] as $hrow)
					{
						$sheet->getStyle($hrow['key'])->applyFromArray($hrow['val']);
					}
				}
				if(!empty($header['width']))
				{
					foreach($header['width'] as $hrow)
					{
						if($hrow['val'] > 0){
							if(preg_match('/\d/', $hrow['key'])) $hrow['key'] = Coordinate::stringFromColumnIndex($hrow['key']);
							$sheet->getColumnDimension($hrow['key'])->setWidth($hrow['val']);
						}
					}
				}

				if(!empty($header['height']))
				{
					foreach($header['height'] as $hrow)
					{
						if($hrow['val'] > 0) $sheet->getRowDimension($hrow['key'])->setRowHeight($hrow['val']);
					}
				}
			}
		}
		$spreadsheet->setActiveSheetIndex(0);
		if($save)
		{
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save($filename);
			return ['file'=>$filename];
		}
		else
		{
			ob_clean();
			if($fileType == 'Xlsx')
			{
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
				header('Content-Disposition: attachment;filename="'.$filename.'"');
			}
			else
			{
				header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment;filename="'.$filename.'"');
			}
			header('Cache-Control: max-age=0');
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save('php://output');
		}
	}
}
?>