<?php
function formatExcelToArrayBank($file_url = ""){
	if (! file_exists ( $file_url )) {
		return array ();
	}
	
	Doo::loadClass ( 'PHPExcel1.7.9/PHPExcel' );
	
	$PHPExcel = new PHPExcel ();
	$PHPReader = new PHPExcel_Reader_Excel2007 ();
	
	if (! $PHPReader->canRead ( $file_url )) {
		$PHPReader = new PHPExcel_Reader_Excel5 ();
		if (! $PHPReader->canRead ( $file_url )) {
	
			echo 'no Excel';
			return;
		}
	}
	
	$PHPExcel = $PHPReader->load ( $file_url );
	/* * 读取excel文件中的第一个工作表 */
	$currentSheet = $PHPExcel->getSheet ( 0 );
	/* * 取得最大的列号 */
	$allColumn = $currentSheet->getHighestColumn ();
	/* * 取得一共有多少行 */
	$allRow = $currentSheet->getHighestRow ();
	$excel_array = array ();
	
	for($currentRow = 2; $currentRow <= $allRow; $currentRow ++) {
		$excel_column = array ();
		// 后期改进
		if ($allColumn == 'AM')
			$allColumn = 'Z';
	
			/* * 从第A列开始输出 */
			for($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn ++) {
				$val = $currentSheet->getCellByColumnAndRow ( ord ( $currentColumn ) - 65, $currentRow )->getValue ();
				if ($val instanceof PHPExcel_RichText)
					$val = $val->__toString ();
				
					if ($currentColumn == 'E') {
						$excel_column ['receivablesPrice'] = $val;
					} elseif ($currentColumn == 'B')
					$excel_column ['receivablesMessage'] = $val;
					elseif ($currentColumn == 'D') {
						$excel_column ['receivablesBank'] = $val;
					} elseif ($currentColumn == 'A') {
						$excel_column ['receivablesDate'] = excelTime ( $val );
					} 
			}
			if (! empty ( $excel_column ['receivablesPrice'] ) && ! empty ( $excel_column ['receivablesMessage'] ) && ! empty ( $excel_column ['receivablesBank'] )&& ! empty ( $excel_column ['receivablesDate'] ))
				array_push ( $excel_array, $excel_column );
	}
	return $excel_array;
}

function excelTime($date, $time = false) {
	if (function_exists ( 'GregorianToJD' )) {
		if (is_numeric ( $date )) {
			$jd = GregorianToJD ( 1, 1, 1970 );
			$gregorian = JDToGregorian ( $jd + intval ( $date ) - 25569 );
			$date = explode ( '/', $gregorian );
			$date_str = str_pad ( $date [2], 4, '0', STR_PAD_LEFT ) . "-" . str_pad ( $date [0], 2, '0', STR_PAD_LEFT ) . "-" . str_pad ( $date [1], 2, '0', STR_PAD_LEFT ) . ($time ? " 00:00:00" : '');
			return $date_str;
		}
	} else {
		$date = $date > 25568 ? $date + 1 : 25569;
		/* There was a bug if Converting date before 1-1-1970 (tstamp 0) */
		$ofs = (70 * 365 + 17 + 2) * 86400;
		$date = date ( "Y-m-d", ($date * 86400) - $ofs ) . ($time ? " 00:00:00" : '');
	}
	return $date;
}

 

?>