PHPExcel导入数据

跟csv导入是相似的，只需要获取上传的文件，并解析出来数据，再依次插入数据库。下面是示例代码，略作修改就可以直接用了。

//导入
	public function import(){
		import ( 'ORG.Excel.PHPExcel' );
		import ( 'ORG.Net.UploadFile' );
		import ( 'ORG.PHPExcel.PHPExcel' );
		require './Public/PHPExcel/PHPExcel.php';
		$upload = new \UploadFile (); // 实例化上传类
		$upload->allowExts = array (
				'xls',
				'xlsx'
		); // 设置附件上传类型
		$upload->savePath = './Public/Uploads/'; // 设置附件上传目录
		if (! $upload->upload ()) { // 上传错误提示错误信息
			$msg=$upload->getErrorMsg();
			exit("{success:false,msg:'$msg'}");
		} else { // 上传成功
			// 取得成功上传的文件信息
			$info = $upload->getUploadFileInfo ();
			$filePath = $info [0] ['savepath'] . $info [0] ['savename'];
			// 创建对象
			$PHPExcel = new \PHPExcel ();
			/**
			 * 默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
			*/
			$PHPReader = new \PHPExcel_Reader_Excel2007 ();
			if (! $PHPReader->canRead ( $filePath )) {
	
				$PHPReader = new \PHPExcel_Reader_Excel5 ();
				if (! $PHPReader->canRead ( $filePath )) {
					exit ( "{success:false,msg:'不能读取Excel'}" );
					return;
				}
			}
			$PHPExcel = $PHPReader->load ( $filePath );
	
			/**
			 * 读取excel文件中的第一个工作表
			*/
			$currentSheet = $PHPExcel->getSheet ( 0 );
			/**
			 * 取得最大的列号
			*/
			$allColumn = $currentSheet->getHighestColumn ();
			/**
			 * 取得一共有多少行
			*/
			$allRow = $currentSheet->getHighestRow ();
			$sqll = "insert into item (`id`,`childid`,`itemname`,`childname`,`mainimplement`) values"; // 插入信息初始化
			/**
			 * 从第二行开始输出，因为excel表中第一行为列名
			 */
			// 循环查看信用代码是否存在，格式是否正确
			$db = M ();
			$tmp = '';
			for($currentRow = 2; $currentRow <= $allRow; $currentRow ++) {
				if ($currentSheet->getCellByColumnAndRow ( ord ( 'C' ) - 65, $currentRow )->getValue ()) {
					$data1 = $currentSheet->getCellByColumnAndRow ( ord ( 'A' ) - 65, $currentRow )->getValue ();
					$data2 = $currentSheet->getCellByColumnAndRow ( ord ( 'B' ) - 65, $currentRow )->getValue ();
					$data3 = $this->trimall($currentSheet->getCellByColumnAndRow ( ord ( 'C' ) - 65, $currentRow )->getValue ());
					$data4 = $this->trimall($currentSheet->getCellByColumnAndRow ( ord ( 'D' ) - 65, $currentRow )->getValue ());
					$data5 = $currentSheet->getCellByColumnAndRow ( ord ( 'E' ) - 65, $currentRow )->getValue ();
					
					$sqll .= "('{$data1}','{$data2}','{$data3}','{$data4}','{$data5}')";
					if ($currentRow <= $allRow - 1) {
						$sqll = $sqll . ',';
					}
				}
				// 数据未通过检测，连接字符串
			}
			$sqll=rtrim($sqll,',');
			$db->startTrans ();
			try {
				$db->query ( $sqll );
				$db->commit ();
				exit ( "{success:true,msg:'导入成功'}" );
			} catch ( \Exception $e ) {
				$db->rollBack ();
				exit ( "{success:true,msg:'$e'}" );
			}
		}
	}