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 stubString 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 lastInputStream 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){ //如果下一行第一列有值就是trueHSSFCell 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 blocke.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 blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{if(fos!=null){try {fos.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(xw!=null){try {xw.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}}生成的xml文件如附件中
页:
[1]