我要解析如附件中的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文件如附件中