ExcelPoi 写入HDFS

---

场景： 从本地Excel文件导入HDFS进行计算

/**
 * @author DHing
 */
public class ExportPoiToHDFS {

	public static void exportPoiToHDFS(String server, String dbname, String username, String passwd,String build) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + server + "/" + dbname, username, passwd);
			pst = conn.prepareStatement("SELECT p.poi_no as poi_no," + "p.id_build as build_id," + "p.floor as floor," + "p.name_chinese as cn_name," + "AsText(p.the_geom) as geom " + "FROM poi_basic_2 p " + "where p.the_geom is not NULL and p.poi_no >= 0 and p.floor is not null and p.floor != '' and id_build='"+build+"'");
			rs = pst.executeQuery();
			if (rs == null) {
				return;
			}
			int i = 0;
			Configuration conf = new Configuration();
			FileSystem fs = FileSystem.get(conf);
			StringBuffer sb = new StringBuffer();
			while (rs.next()) {
				try {
					i++;
					int poino = rs.getInt(1);
					String buildid = rs.getString(2);
					String floorid = CommonUtils.parseFloorToId(rs.getString(3));
					String cnname = rs.getString(4);
					String geom = rs.getString(5);
					if (buildid == null || buildid.length() < 18) {
						continue;
					}
					if (i % 10 == 0) {
						System.out.println("Load : " + i);
					}
//				
					sb.append(buildid+"\t"+floorid+"\t"+poino+"\t"+cnname+"\t"+geom+"\n");
					
					
				} catch (Exception e) {
					System.out.println(rs.getInt(1) + "--" + rs.getString(2) + "--" + rs.getString(3));
				}
				ByteArrayInputStream bis = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
				FSDataOutputStream fds = fs.create(new Path("/warehouse/meta/poi/"));
				IOUtils.copyBytes(bis, fds, 4096, true);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * 上传文件到hdfs
	 * 
	 * @throws IOException
	 */
	public static void uploadDataToHDFS(File file, String remotedir) throws IOException {
		if (file == null || file.length()==0) {
			return;
		}
		FileSystem fs = FileSystem.get(new Configuration());
		Path remoteFilePath = new Path(remotedir + File.separator + file.getName());
		FSDataOutputStream fos = fs.create(remoteFilePath, true);
		FileInputStream fis = new FileInputStream(file);
		
		IOUtils.copyBytes(fis, fos, 4096, true);
	}
	public static void combinPortalDateAndUpload(String localdir) throws Exception{
		File dir = new File(localdir);
		FileOutputStream fos = null;
		OutputStreamWriter osw = null;
		BufferedWriter bw = null;
			for(File builddir : dir.listFiles()){
				if(builddir.getName().endsWith(".por")){
					continue;
				}
				try {
					fos = new FileOutputStream(localdir+File.separator+builddir.getName()+".por",true);
					osw = new OutputStreamWriter(fos,"UTF-8");
					bw = new BufferedWriter(osw);
					for(File file : builddir.listFiles()){
						CsvReader reader = new CsvReader(file.getAbsolutePath(),',');
						reader.readHeaders();
						while(reader.readRecord()){
							String line = reader.getRawRecord()+"\n";
							bw.append(line);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally{
					if(bw!=null){
						bw.flush();
						bw.close();
					}
					if(osw!=null)
						osw.close();
					if(fos!=null)
						fos.close();
				}
				
				
				
			}
		
		
	}
	public static void combinSellFiles(String localdir,String dstname) throws IOException{
		FileOutputStream fos = null;
		OutputStreamWriter osw = null;
		BufferedWriter bw = null;
		try {
			fos = new FileOutputStream(localdir+File.separator+dstname,true);
			osw = new OutputStreamWriter(fos,"UTF-8");
			bw = new BufferedWriter(osw);
			
			File dir = new File(localdir);
			for(File datadir : dir.listFiles()){
				if(datadir.getName().endsWith(".all")){
					continue;
				}
				FileInputStream fis = null;
				InputStreamReader isr = null;
				BufferedReader br = null;
				try {
					for(File sellFile : datadir.listFiles()){
						if(!sellFile.getName().endsWith(".sal")){
							continue;
						}
						System.out.println(sellFile);
						fis = new FileInputStream(sellFile);
						isr = new InputStreamReader(fis,"UTF-8");
						br = new BufferedReader(isr);
						String line = null; 
						while((line=br.readLine())!=null){
							line = line+"\n";
							bw.append(line);
						}
						
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally{
					if(br!=null)
						br.close();
					if(isr!=null)
						isr.close();
					if(fis!=null)
						fis.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			if(bw!=null){
				bw.flush();
				bw.close();
			}
			if(osw!=null)
				osw.close();
			if(fos!=null)
				fos.close();
		}
		
		
	}
	public static void combinMemberFiles(String localdir,String dstname) throws Exception{
		FileOutputStream fos = null;
		OutputStreamWriter osw = null;
		BufferedWriter bw = null;
		try {
			File dir = new File(localdir);
			fos = new FileOutputStream(localdir+File.separator+dstname,true);
			osw = new OutputStreamWriter(fos,"UTF-8");
			bw = new BufferedWriter(osw);
			
			for(File memFile : dir.listFiles()){
				if(!memFile.getName().endsWith(".memb")){
					continue;
				}
				System.out.println(memFile.getName());
				FileInputStream fis = null;
				InputStreamReader isr = null;
				BufferedReader br = null;
				try {
					fis = new FileInputStream(memFile);
					isr = new InputStreamReader(fis,"UTF-8");
					br = new BufferedReader(isr);
					String line = null;
					while((line=br.readLine())!=null){
						line = line+"\t"+memFile.getName().replaceAll(".memb", "")+"\n";
						bw.append(line);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally{
					if(br!=null)
						br.close();
					if(isr!=null)
						isr.close();
					if(fis!=null)
						fis.close();
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally{
			if(bw!=null){
				bw.flush();
				bw.close();
			}
			if(osw!=null)
				osw.close();
			if(fos!=null)
				fos.close();
		}
		
		
	}
	
	public static void main(String[] args) throws Exception {
		uploadDataToHDFS(new File("D:\\data\\longfox\\portal\\861400010030300001.ori"),"/xx/xx");
	}

}

---