POI解析Excel

我要解析如附件中的Excel 格式代码并生成xml文件,具体格式见附件图片

package readexceltoxml;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

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;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args)throws Exception {
		// TODO Auto-generated method stub
		String zh_big_postion = null;
		String en_big_postion = null;
		String filepath = "d:/a.xls";
		List<String> zh_value = new ArrayList<String> ();    //中文值
		List<String> en_value = new ArrayList<String> ();    //英文值
		boolean b = false;
		
		//String last
		
		InputStream is = new FileInputStream(filepath);
		HSSFWorkbook book = new HSSFWorkbook(is);
		HSSFSheet sheet = book.getSheet("职位类别");
		int rows = sheet.getPhysicalNumberOfRows();//获得总行数
		
		
		
		for(int i=0;i<rows;i++){//循环每行
			
			HSSFRow row = sheet.getRow(i);
			
			HSSFCell A = row.getCell(0);
			HSSFCell B = row.getCell(1);
			HSSFCell C = row.getCell(2);
			HSSFCell D = row.getCell(3);
			
			String a_value = A.getStringCellValue();
			String b_value = B.getStringCellValue();
			String c_value = C.getStringCellValue();
			String d_value = D.getStringCellValue();
			if(!"".equals(a_value)){
				zh_big_postion = a_value;
			}
			if(!"".equals(b_value)){
				en_big_postion = b_value;
			}
			if(i==0){
				zh_value.add(c_value);
				en_value.add(d_value);
			}else if(i!=0&&"".equals(a_value)){
				zh_value.add(c_value);
				en_value.add(d_value);
			}
			
				HSSFRow roww = sheet.getRow(i+1);
				if(roww!=null){              //如果下一行第一列有值就是true
					HSSFCell AA = roww.getCell(0);
					String aa_value = AA.getStringCellValue();
					if(!"".equals(aa_value))
						b = true;
				}
				
			
			
			
			if(b||i==rows-1){//如果为true或者是最后一行就写入到xml里
				Document document = generXML(zh_big_postion, en_big_postion,
						zh_value, en_value);
				
				writeHtml("d:/position.xml",document);
				
				
				zh_big_postion = null;
				en_big_postion = null;
				zh_value.clear();
				en_value.clear();
				b = false;
			}
		}

		is.close();
		System.out.println("end");
	}

	/**
	 * 生成xml文件
	 * @param zh_big_postion
	 * @param en_big_postion
	 * @param zh_value
	 * @param en_value
	 * @return
	 * @throws DocumentException
	 */
	private static Document generXML(String zh_big_postion,
			String en_big_postion, List<String> zh_value, List<String> en_value)
			throws DocumentException {
		SAXReader reader = new SAXReader();
		Document document = reader.read(new File("d:/position.xml"));
		Element root = document.getRootElement(); //获得根节点
		
		Element position_zh = DocumentHelper.createElement("position-zh");
		Element position_en = DocumentHelper.createElement("position-en");
		
		Element name_zh = DocumentHelper.createElement("name");
		name_zh.addText(zh_big_postion);
		Element name_en = DocumentHelper.createElement("name");
		name_en.addText(en_big_postion);
		
		position_zh.add(name_zh);
		position_en.add(name_en);
		
		Element position_small_zh = DocumentHelper.createElement("position-small");
		Element position_small_en = DocumentHelper.createElement("position-small");
		
		for(String value:zh_value){
			Element name = DocumentHelper.createElement("name");
			name.addText(value);
			position_small_zh.add(name);
		}
		position_zh.add(position_small_zh);
		
		for(String value:en_value){
			Element name = DocumentHelper.createElement("name");
			name.addText(value);
			position_small_en.add(name);
		}
		position_en.add(position_small_en);
		
		root.add(position_zh);
		root.add(position_en);
		return document;
	}

	/**
	 * 写到xml文件里
	 * @param path
	 * @param d
	 */
	private static void writeHtml(String path, Document d) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(path);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
        OutputFormat of=new OutputFormat("",true);   
        of.setEncoding("UTF-8");

        XMLWriter xw = null;
		try {
			xw = new XMLWriter(fos, of);
			 xw.write( d );    
		     xw.close();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(fos!=null){
				try {
					fos.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(xw!=null){
				try {
					xw.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
}

生成的xml文件如附件中