<?php
ini_set('memory_limit', '4096M');
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
  * @method PhpSpreadsheet 操作  https://github.com/PHPOffice/PhpSpreadsheet
  * @author soul
  * @copyright 2017/6/15
  */
class OthSExcel
{
	
	/**
	  * @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)) return LibFc::ReturnData(false, '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) {
			return LibFc::ReturnData(false, '加载文件发生错误'.$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 LibFc::ReturnData(true, $arr);
	}


	/**
	 * 写入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)) return LibFc::ReturnData(false, '数据不能为空');
		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++;
				}
			}
			$sheetArr[$sheetIndex] = [];
		}
		unset($sheetArr);
		if($save)
		{
			if(!is_dir($filename)) return LibFc::ReturnData(false, '提供的EXCEL存储路径有误');
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save($filename);
			$arr = ['file'=>$filename];
			return LibFc::ReturnData(true, $arr);
		}
		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');
		}
	}


	/**
	 * 写入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 writeBig($dataArr, $filename = '', $save = false)
	{
		if(empty($dataArr)) return LibFc::ReturnData(false, '数据不能为空');
		if(empty($filename)) $filename = date('YmdHis').'.Xlsx';
		$fileType = stripos($filename, '.xlsx')? 'Xlsx': 'Xls';
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();
		$fields = [];
		if (isset($dataArr['fields']) && !empty($dataArr['fields'])) 
		{
			$fields = $dataArr['fields'];
			unset($dataArr['fields']);
		}
		
		$row = 1;
		if(!empty($fields)){
			$col = 'A';
			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 ($dataArr as $v) {
				$rowData = [];
				foreach ($fields as $filed => $fname){
					$rowData[] = isset($v[$filed])? $v[$filed]: '';
				}
				$sheet->fromArray($rowData, null, 'A' . $row);
				$row++;
			}
		}else{
			foreach ($dataArr as $rowData){
				$sheet->fromArray($rowData, null, 'A' . $row);
				$row++;
			}
		}
		
		unset($sheetArr);
		if($save)
		{
			if(!is_dir($filename)) return LibFc::ReturnData(false, '提供的EXCEL存储路径有误');
			$writer = IOFactory::createWriter($spreadsheet, $fileType);
			$writer->save($filename);
			$arr = ['file'=>$filename];
			return LibFc::ReturnData(true, $arr);
		}
		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 = 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']);
		}
	}
}
?>