<?php

require_once 'lib/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
/**
  * @method excel 操作
  * @author soul
  * @copyright 2017/6/15
  */
class OthExcel
{
	/**
	  * @method 读取excel
	  * @param  $file 文件
	  * @param  $sheetArr array 默认第一个sheet   
	  * @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.'不存在');
		try
		{
			$fileType = PHPExcel_IOFactory::identify($file);
			$objReader = PHPExcel_IOFactory::createReader($fileType);
			$objPHPExcel = $objReader->load($file);
		} catch(Exception $e) 
		{
			return LibFc::ReturnData(false, '加载文件发生错误'.$file.','.$e->getMessage());
		}
		$arr = [];
		
		//默认读取第一个sheet
		$sheet = $objPHPExcel->getSheet($sheet);
		$rowCount = $sheet->getHighestRow();
		$colCount = $sheet->getHighestColumn();
		$colHeadIndex = [];
		for ($row = $startRow; $row <= $rowCount; $row++)
		{
			$rowData = $sheet->rangeToArray('A'.$row.':'.$colCount.$row, NULL, TRUE, FALSE);
			$rowData = array_pop($rowData);
			if(!empty($colHeader))
			{
				//获取固定头对应的顺序
				if(empty($colHeadIndex))
				{
					//长度大到小排序
					if(!$mustMatch) rsort($colHeader);
					foreach($rowData as $x=>$xv)
					{
						$xv = trim($xv);
						foreach($colHeader as $ck=>$cname)
						{
							if(($mustMatch && $xv == $cname) || (!$mustMatch && stripos($xv, $cname) === 0))
							{
								$colHeadIndex[$x] = $cname;
								unset($colHeader[$xv]);
								break;
							}
						}
					}
					continue;
				}
				else
				{
					$tempArr = [];
					foreach($rowData as $kk=>$vv)
					{
						if(isset($colHeadIndex[$kk]))
						{
							$tempArr[$colHeadIndex[$kk]] = $vv;
						}
					}
					$rowData = $tempArr;
				}
			}
			$arr[$row] = $rowData;
		}
		return LibFc::ReturnData(true, $arr);
	}

	/**
	 * 写入Execl数据
	 *
	 * @param array $aValue 数据
	 * @example 
	 *         存在$aValue['fields']为有序输出, 输出$aValue['fields']键对值
	 *         不存在$aValue['fields']为无序输出, 直接输出$aValue
	 * @throws 开启debug模式会导致生成的Execl文件乱码
	 * @return Response
	 */
	public function writer($aValue, $execl = 'Excel5', $filename = '')
	{
		if ( empty($aValue) ) {
			return LibFc::ReturnData(false, '数据不能为空~');
		}

		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setTitle('export')->setDescription('none');
		$objPHPExcel->setActiveSheetIndex(0);

		$row = 1;
		if (isset($aValue['fields']) && !empty($aValue['fields'])) 
		{
			# 存在标题头, 输出标题头内容
			$aFields = $aValue['fields'];
			unset($aValue['fields']);

			$col = 0;
			foreach ($aFields as $key => $value) {
				$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $value);
				$col = $col + 1;
			}
			
			$row = $row + 1;
			foreach ($aValue as $key => $value) 
			{
				$col = 0;
				foreach ($aFields as $keyFields => $valueFields) 
				{
					if (!isset($value[$keyFields])) {
						$value[$keyFields] = '';
					}
				
					$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $value[$keyFields]);
					if(stripos($value[$keyFields], "\n"))
					{
						$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getAlignment()->setWrapText(true);
					}
					$col = $col + 1;
				}
				$row = $row + 1;
			}
		} else
		{
			# 不存在标题头, 直接输出所有内柔
			foreach ($aValue as $key => $value) 
			{
				$col = 0;
				foreach ($value as $kk => $vv) 
				{
					$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $vv);
					if(stripos($vv, "\n"))
					{
						$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getAlignment()->setWrapText(true);
					}
					$col = $col + 1;
				}
				$row = $row + 1;
			}
		}

		# 下载Execl文件
		$execl = empty($execl) ? 'Excel5': $execl;
		$this->download($objPHPExcel, $filename, $execl);
	}


	/**
	 * 下载生成的Execl数据
	 *
	 * @param object $object PHPExecl 对象
	 * @param string $filename 下载文件名称
	 * @param string $execl Execl版本
	 * @return Response
	 */
	public function download($object, $filename = '', $execl = 'Excel5')
	{
		# 文件名称
		if ( empty($filename) ) {
			$filename = sprintf('%d.xls', time());
		}
		$execl = empty($execl) ? 'Excel5': $execl;

		$objWrite = PHPExcel_IOFactory::createWriter($object, $execl);
		// Sending headers to force the user to download the file
		ob_clean();
		header('Content-Type: application/vnd.ms-execl');
		header('Content-Disposition: attachment;filename="'.$filename.'"');
		header('Cache-Control: max-age=0');
		$objWrite->save('php://output');
	}
}
?>