SAX 解析xml并生成EXCEL

1.xml代码

<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://www.sz-customs.gov.cn/WebService/">
       <Table id="Table1" msdata:rowOrder="0">
        <FULL_NAME>广州广发鞋材有限公司</FULL_NAME>
        <TRADE_CO />
        <CASE_ID>533040120040041</CASE_ID>
        <CASE_DATE>2004-10-22T00:00:00+08:00</CASE_DATE>
        <CASE_DEPT>深圳缉私局文锦渡缉私分局查私科</CASE_DEPT>
        <CASE_TYPE>违规</CASE_TYPE>
      </Table>
      <Table id="Table2" msdata:rowOrder="1">
        <FULL_NAME>联能科技（深圳）有限公司</FULL_NAME>
        <TRADE_CO>4403945553</TRADE_CO>
        <CASE_ID>533040120040061</CASE_ID>
        <CASE_DATE>2004-11-02T00:00:00+08:00</CASE_DATE>
        <CASE_DEPT>深圳缉私局文锦渡缉私分局查私科</CASE_DEPT>
        <CASE_TYPE>违规</CASE_TYPE>
      </Table>
      <Table id="Table3" msdata:rowOrder="2">
        <FULL_NAME>肇庆时升运输有限公司</FULL_NAME>
        <TRADE_CO />
        <CASE_ID>533040120040082</CASE_ID>
        <CASE_DATE>2004-10-22T00:00:00+08:00</CASE_DATE>
        <CASE_DEPT>深圳缉私局文锦渡缉私分局查私科</CASE_DEPT>
        <CASE_TYPE>违规</CASE_TYPE>
      </Table>
      <Table id="Table4" msdata:rowOrder="3">
        <FULL_NAME>深圳市国免工贸有限公司</FULL_NAME>
        <TRADE_CO />
        <CASE_ID>533040120040083</CASE_ID>
        <CASE_DATE>2004-10-21T00:00:00+08:00</CASE_DATE>
        <CASE_DEPT>深圳缉私局文锦渡缉私分局查私科</CASE_DEPT>
        <CASE_TYPE>违规</CASE_TYPE>
      </Table>
      <Table id="Table5" msdata:rowOrder="4">
        <FULL_NAME>南山区福光港兴手袋来料加工厂</FULL_NAME>
        <TRADE_CO />
        <CASE_ID>533040120040101</CASE_ID>
        <CASE_DATE>2004-10-27T00:00:00+08:00</CASE_DATE>
        <CASE_DEPT>深圳缉私局文锦渡缉私分局查私科</CASE_DEPT>
        <CASE_TYPE>违规</CASE_TYPE>
      </Table>

</DataSet>

 

java 代码

package com.soft.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import com.soft.model.ChenxinResult;
 
public class SAXDemo extends DefaultHandler{
 
    private List<ChenxinResult> result = null;  
    private ChenxinResult chenxin = null;  
    private String preTag = null;//作用是记录解析时的上一个节点名称  
      
    public List<ChenxinResult> getResults(InputStream  xmlStream) throws Exception{  
        SAXParserFactory factory = SAXParserFactory.newInstance();  
        SAXParser parser = factory.newSAXParser();  
        SAXDemo handler = new SAXDemo();  
        parser.parse(xmlStream, handler);  
        return handler.getResults();  
    }  
      
    public List<ChenxinResult> getResults(){  
        return result;  
    }  
      
    @Override 
    public void startDocument() throws SAXException {  
     result = new ArrayList<ChenxinResult>();  
    }  
 
    @Override 
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {  
        if("Table".equals(qName)){  
         chenxin = new ChenxinResult();  
         chenxin.setId(attributes.getValue(0));   
        }  
        preTag = qName;//将正在解析的节点名称赋给preTag  
    }  
 
    @Override 
    public void endElement(String uri, String localName, String qName)  
            throws SAXException {  
        if("Table".equals(qName)){  
         result.add(chenxin);  
         chenxin = null;  
        }  
        preTag = null;
    }  
      
    @Override 
    public void characters(char[] ch, int start, int length) throws SAXException {  
        if(preTag!=null){  
            String content = new String(ch,start,length); 
            if("FULL_NAME".equals(preTag)){  
               chenxin.setFullname(content);
            }else if("TRADE_CO".equals(preTag)){  
               chenxin.setTradecode(content);
            }else if("CASE_ID".equals(preTag)){
               chenxin.setCaseid(content);
            }else if("CASE_DATE".equals(preTag)){
               chenxin.setCasedate(content);
            }else if("CASE_DEPT".equals(preTag)){
               chenxin.setCasedept(content);
            }else if("CASE_TYPE".equals(preTag)){
               chenxin.setCasetype(content);
            }
        }  
    }
   
    public void testSAX(){  
     try{
      SAXDemo demo=new SAXDemo();
      InputStream input = new FileInputStream("D:/company.xml");  
         List<ChenxinResult> demoList=demo.getResults(input);
        
   // 打开文件
   WritableWorkbook book1 = Workbook.createWorkbook(new File("D:/user3.xls "));
   // 生成名为“第一页”的工作表，参数0表示这是第一页
   WritableSheet sheet = book1.createSheet(" EXIMUSER ", 0);
   // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
   Label label = new Label(0, 0, "编号");
   Label label1 = new Label(1, 0, "公司企业");
   Label label2 = new Label(2, 0, "用户编号");
   Label label3 = new Label(3, 0, "注册号");
   Label label4 = new Label(4, 0, "时间");
   Label label5 = new Label(5, 0, "部门");
   Label label6 = new Label(6, 0, "行为");
   
   // 将定义好的单元格添加到工作表中
   sheet.addCell(label);
   sheet.setColumnView(0,10);   
   sheet.addCell(label1);
   sheet.setColumnView(1,35);   
   sheet.addCell(label2);
   sheet.setColumnView(2,20);
   sheet.addCell(label3);
   sheet.setColumnView(3,20);
   sheet.addCell(label4);
   sheet.setColumnView(4,30);
   sheet.addCell(label5);
   sheet.setColumnView(5,30);
   sheet.addCell(label6);
   sheet.setColumnView(6,10);
   
   for(int i=0;i<demoList.size();i++){
    int column=0;
    int width=0;
    ChenxinResult result=(ChenxinResult)demoList.get(i);
    
    Label label8 = new Label(column++,i+1,result.getId());
    sheet.addCell(label8);
    sheet.setColumnView(width,10);
    
    Label label9 = new Label(column++,i+1,result.getFullname());
    sheet.addCell(label9);
    sheet.setColumnView(width+=1,35);
    
    Label label10 = new Label(column++,i+1,result.getTradecode());
    sheet.addCell(label10);
    sheet.setColumnView(width+=1,20);
    
    Label label11 = new Label(column++,i+1,result.getCaseid());
    sheet.addCell(label11);
    sheet.setColumnView(width+=1,20);
    
    Label label12 = new Label(column++,i+1,result.getCasedate());
    sheet.addCell(label12);
    sheet.setColumnView(width+=1,30);
    
    Label label13 = new Label(column++,i+1,result.getCasedept());
    sheet.addCell(label13);
    sheet.setColumnView(width+=1,30);
    
    Label label14 = new Label(column++,i+1,result.getCasetype());
    sheet.addCell(label14);
    sheet.setColumnView(width+=1,10); 
   }
   book1.write();
   book1.close();
     }catch(Exception ce){
      ce.printStackTrace();
     }
    }
   
    public static void main(String [] args){
     SAXDemo demo=new SAXDemo();
     demo.testSAX();
    }
      
} 

注意这里需要用到jxl.jar自己到网上去下载。

 

 

以下是 ChenxinResult主要是用来组装数据

public class ChenxinResult {
 
 private String id;
 private String fullname;
 private String tradecode;
 private String caseid;
 private String casedate;
 private String casedept;
 private String casetype;
 
 public String getId() {
  return id;
 }
 public void setId(String id) {
  this.id = id;
 }
 public String getFullname() {
  return fullname;
 }
 public void setFullname(String fullname) {
  this.fullname = fullname;
 }
 public String getTradecode() {
  return tradecode;
 }
 public void setTradecode(String tradecode) {
  this.tradecode = tradecode;
 }
 public String getCaseid() {
  return caseid;
 }
 public void setCaseid(String caseid) {
  this.caseid = caseid;
 }
 public String getCasedate() {
  return casedate;
 }
 public void setCasedate(String casedate) {
  this.casedate = casedate;
 }
 public String getCasedept() {
  return casedept;
 }
 public void setCasedept(String casedept) {
  this.casedept = casedept;
 }
 public String getCasetype() {
  return casetype;
 }
 public void setCasetype(String casetype) {
  this.casetype = casetype;
 }

}

 

 

以下代码是java生成xml

public void BuildXMLDoc() throws IOException, JDOMException {
  // 创建根节点 list;
        Element root = new Element("list");
       // 根节点添加到文档中；
        Document Doc = new Document(root);
       // 此处 for 循环可替换成 遍历 数据库表的结果集操作;
       for (int i = 0; i < 5; i++) {
           //创建节点 user;
           Element elements = new Element("user");
           //给 user 节点添加属性 id;
           elements.setAttribute("id", "" + i);
           //给 user 节点添加子节点并赋值；
           //new Element("name")中的 "name" 替换成表中相应字段，setText("xuehui")中 "xuehui 替换成表中记录值；
           elements.addContent(new Element("name").setText("xuehui"));
           elements.addContent(new Element("age").setText("28"));
           elements.addContent(new Element("sex").setText("Male"));
           //给父节点list添加user子节点;
           root.addContent(elements);
       }
       Format format = Format.getPrettyFormat();
       XMLOutputter XMLOut = new XMLOutputter(format);
       XMLOut.output(Doc, new FileOutputStream("D:/user.xml"));
    }

需要用到Jdom.jar可以网上下载