初步分析美国科研项目申报项目

 

将XML转换为新格式的XML、excel、和SQlserver数据

 

package hander;

/**
 * @author ZTX * @function：读取XML中字段到excel中
 * @Configuration：将文件放在 Xml\data下，或者data改为您的数据文件夹置于 Xml\下
 * @time：2013年11月12日19:37:45--2013年11月12日21:48:22--2013年11月13日17:28:45--2013年11月13日21:00:45
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.dom4j.*;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

import org.apache.poi.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Read { 
	public static String inputXmlDir;//要处理的xml文件的文件夹
	public static String data="2006";//要处理的xml文件的文件夹名称默认是data
	public static boolean head=false;//表头是否写了
	public static String pathPrefix="";//文件路径前缀
	
	public static long  recordNum=0;//记录批量
	public static void main(String args[]){
		PreparedStatement ppst = null ;
		ConnectSQLserver mssq=new ConnectSQLserver(true);//连sqlserver
		//建表
		String reCreWord=
				"create table Project"+
				"     "+
				"(                        "+
				"AwardTitle varchar(200) null, "+
				"Time varchar(1000) null, "+
				"Organization varchar(200) null, "+
				"ProgramOfficer varchar(100) null, "+
				"AbstractNarration varchar(8000) null, "+
				"AwardID varchar(10) null, "+
				"PrincipalInvestigator varchar(60) null, "+
				"EmailAddress varchar(40) null, "+
				"Name varchar(80) null, "+
				"CountryName varchar(60) null, "+
				"StateName varchar(60) null, "+
				"CityName varchar(60) null, "+
				"ProgramReference varchar(500) null, "+
				")                        ";
		//插入
		String inSQL="insert into Project(" +
				"AwardTitle,Time,Organization," +
				"ProgramOfficer,AbstractNarration," +
				"AwardID,PrincipalInvestigator,EmailAddress," +
				"Name,CountryName,StateName," +
				"CityName,ProgramReference)values(?,?,?,?,?,?,?,?,?,?,?,?,?);";

		try {
			 mssq.sta.execute(reCreWord);
			System.out.println("操作新表："+inSQL);
			ppst=mssq.conn.prepareStatement(inSQL);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
//		try {
//			//mssq.conn.setAutoCommit(false);
//		} catch (SQLException e1) {
//			e1.printStackTrace();
//		}
		
		long start,end=0;
		//创建空表
		Document creDocument = DocumentHelper.createDocument();    
		//添加空字段
		//项目标题
		Element root= creDocument.addElement("root");
		Element element[ ] = new Element[13];
		element[0] =  root.addElement("AwardTitle"); 
		element[1] = root.addElement("Time"); 
		element[2]=  root.addElement("Organization"); 
		element[3] =  root.addElement("ProgramOfficer"); 
		element[4]=  root.addElement("AbstractNarration"); 
		element[5]=  root.addElement("AwardID"); 
		element[6]=  root.addElement("PrincipalInvestigator"); 
		element[7]=  root.addElement("EmailAddress"); 
		element[8] = root.addElement("Name"); 
		element[9]=  root.addElement("CountryName"); 
		element[10] = root.addElement("StateName"); 
		element[11]=  root.addElement("CityName"); 
		element[12]=  root.addElement("ProgramReference");
		
        SAXReader saxReader = new SAXReader();   
		//得到待处理的文件夹
			//前缀
        File getPrefix = new File("");// 空文件夹 用来得到 文件路径前缀
        pathPrefix=getPrefix.getAbsolutePath();//得到 文件路径前缀
        	//组合
        inputXmlDir=File.separator + data;
        inputXmlDir=pathPrefix+inputXmlDir;//文件夹绝对路径
        System.out.println(inputXmlDir);
        File file = new File(data);
        //遍历文件夹下的每个文件
	    if(file.isDirectory()){ 
	        File[] files = file.listFiles();
	        start=System.currentTimeMillis();
	        Document document;
	        for(int i = 0; i < files.length; i++){
	        	 System.out.println("第"+(i+1)+"篇");
	            try {
	            	document = saxReader.read(inputXmlDir+File.separator + files[i].getName());
					//拿出一个xml去处理
	        			try {
							String record[]=Handle( document,creDocument ,element);
							//System.out.println("record[0]:"+record[0].toString());
 							for( int k=0;k<record.length;k++ ){
 								 ppst.setString(k+1, record[k]);
 								//System.out.println(k);
 								// System.out.println(record[k]);
 							}
							ppst.execute();
	        			} catch (SQLException e) {
							e.printStackTrace();
						}
	            } catch (DocumentException e) {
	            	e.printStackTrace();
	            }
	        }
	        end=System.currentTimeMillis()-start;
	        System.out.println("所用时间："+end/1000+"s");
	     }
	    //输出xml结果文件
	    try{
	        XMLWriter output = new XMLWriter(
	            new FileWriter( new File(inputXmlDir+File.separator+"result.xml") ));
	            output.write( creDocument );
	            output.close();
	    }
	    catch(IOException e){
	    	System.out.println(e.getMessage());
	    }
	}
 
	//处理一个xml 
	public static String[] Handle(Document document, Document creDocument, Element[] element) throws SQLException{
		String [] record=new String [13];
		
		//解析
		Element root=document.getRootElement();    // 获取根节点
        Iterator Award = root.elementIterator("Award"); //根节点#Award
        // 遍历Award节点:
        while (Award.hasNext()) {
            Element AwardEle = (Element) Award.next();
            //项目标题
            String AwardTitle = AwardEle.elementTextTrim("AwardTitle"); //  Award#AwardTitle 
              //添加到xml中
            Element Title=element[0].addElement("Title");//AwardTitle的子标签
            Title.setText(AwardTitle);
            record[0]=AwardTitle;
            //项目时间
            String Time="";
            String AwardEffectiveDate=AwardEle.elementTextTrim("AwardEffectiveDate"); //  Award#AwardEffectiveDate 
            String AwardExpirationDate=AwardEle.elementTextTrim("AwardExpirationDate"); //  Award#AwardExpirationDate 
            Time=AwardEffectiveDate +"-"+AwardExpirationDate;
            
            Element time=element[1].addElement("time"); 
            time.setText(Time);
            record[1]=Time;
            //组织
            String org="";
            Iterator Organization = AwardEle.elementIterator("Organization");//Award#Organization
            	// 遍历Organization节点:
            while (Organization.hasNext()) {
                Element OrganizationEle = (Element) Organization.next();
                //组织合成
                org= getChild(OrganizationEle,"Division","LongName")+">"+getChild(OrganizationEle,"Directorate","LongName");
            }
            
            Element organization=element[2].addElement("organization"); 
            organization.setText(org);
            record[2]=org;
           //项目主任
           String  proOffice="";
           proOffice=getChild(AwardEle,"ProgramOfficer","SignBlockName");
           
           Element officers=element[3].addElement("officers"); 
           officers.setText(proOffice);
           record[3]=proOffice;
           //摘要叙述
           String  abs="";
           abs=getChild(AwardEle,"","AbstractNarration");
           
           Element abstracts=element[4].addElement("abstracts"); 
           abstracts.setText(abs);
           record[4]=abs;
           //项目编号
           String  Id="";
           Id=getChild(AwardEle,"","AwardID");
           
           Element id=element[5].addElement("id"); 
           id.setText(Id);
           record[5]=Id;
           //主要研究者
           String  Inv="";
           Inv=getChild(AwardEle,"Investigator","FirstName")+" "+getChild(AwardEle,"Investigator","LastName");
           if(getChild(AwardEle,"Investigator","RoleCode").equals("Principal Investigator")){
               Element investigator=element[6].addElement("investigator"); 
               investigator.setText(Inv);
               record[6]=Inv;
           }
           //主要研究者邮箱
           String  Em="";
           Em=getChild(AwardEle,"Investigator","EmailAddress");
           if(getChild(AwardEle,"Investigator","RoleCode").equals("Principal Investigator")){
        	   Element email=element[7].addElement("email"); 
               email.setText(Em);
               record[7]=Em;
           }
           //机构名称
           String  Name="";
           Name=getChild(AwardEle,"Institution","Name");
           
           Element name=element[8].addElement("name"); 
           name.setText(Name);
           record[8]=Name;
           //机构国家
           String  Nation="";
           Nation=getChild(AwardEle,"Institution","CountryName");
          
           Element nation=element[9].addElement("nation"); 
           nation.setText(Nation);
           record[9]=Nation;
           //机构州
           String  State="";
           State=getChild(AwardEle,"Institution","StateName");
          
           Element state=element[10].addElement("state"); 
           state.setText(State);
           record[10]=State;
           //机构城市
           String  City="";
           City=getChild(AwardEle,"Institution","CityName");
          
           Element city=element[11].addElement("city"); 
           city.setText(City);
           record[11]=City;
           //研究主题
           String  Ref="";
          //(2)子节点的子元素
   		   Iterator iters = AwardEle.elementIterator("ProgramReference");
   		  //遍历mom父节点
   		   while (iters.hasNext()){
   		       Element itemEle = (Element) iters.next();
   		    Ref+=itemEle.elementTextTrim("Text")+" ;"; // 拿到head下的子节点script下的字节点username的值
   		   }
   		 Element reference=element[12].addElement("reference"); 
   		 reference.setText(Ref);
   		record[12]=Ref;
        } 
       // ppst.addBatch();
       // ppst.executeBatch();
       // ppst.execute();
		recordNum++;
        if(!head){//写表头
        	String heads[]=new String [13];
        	heads[0]="项目标题";
        	heads[1]="项目时间";
        	heads[2]="项目主任";
        	heads[3]="组织";
        	heads[4]="摘要叙述";
        	heads[5]="项目编号";
        	heads[6]="主要研究者";
        	heads[7]="主要研究者邮箱";
        	heads[8]="机构名称";
        	heads[9]="机构国家";
        	heads[10]="机构州";
        	heads[11]="机构城市";
        	heads[12]="研究主题";
        	//writeResult2Excel(heads);
        	head=true;
        }
       
        //writeResult2Excel(record);//数据量大就没用
        return record;
	}
	//写入excel一行
	public static void writeResult2Excel(String [] record) {
		String DB=pathPrefix+File.separator+"data/results.xls";	
		HSSFWorkbook wb = getDBwb(DB);//据已有的DB 建立新HSSFWorkbook对象  
		HSSFSheet sheet = wb.getSheet("sheet1");//得到新的sheet对象	
		int rowNum=sheet.getLastRowNum();//获得总行数
		int coloumNum=13;//总列数
		 HSSFRow rowTo =null;
		 HSSFCell cellTo =null;
			 rowTo = sheet.createRow(rowNum+1);
			 for(int j=0;j<coloumNum;j++){//列循环
				 cellTo=rowTo.createCell(j);
				 cellTo.setCellValue(record[j]);
			 } 
		//要把结果写到文件中去
		writeBack2Excel(DB,wb);
	}//writeBack
	public static HSSFWorkbook getDBwb(String getDB) {
		HSSFWorkbook workbook=null;
		try {
			  FileInputStream fis=new FileInputStream(getDB);
		       workbook=new HSSFWorkbook(fis); 
		} catch (IOException e) {
			e.printStackTrace();
		}
		return workbook;
	}
	public static void writeBack2Excel(String DBurl,HSSFWorkbook wb) {
		 FileOutputStream fileOut;
		try {
			fileOut = new FileOutputStream(DBurl);		
			wb.write(fileOut);//把Workbook对象输出到文件中  
			fileOut.flush();              
			fileOut.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  
	}
	private static String getChild(Element AwardEle,String mom,String kid) {
		String target="";
		//(1)直接输出的是根节点的子元素
		if(mom.equals("")){
		    target= AwardEle.elementTextTrim(kid); 
		}
		//(2)子节点的子元素
		Iterator iters = AwardEle.elementIterator(mom);
		//遍历mom父节点
		   while (iters.hasNext()){
		       Element itemEle = (Element) iters.next();
		       target= itemEle.elementTextTrim(kid); // 拿到head下的子节点script下的字节点username的值
		   }
		return target;
	}
}