SAX 解析xml并生成EXCEL_JAVA

您所在的位置：程序员俱乐部 > 编程开发 > JAVA > SAX 解析xml并生成EXCEL

SAX 解析xml并生成EXCEL

2011/12/20 10:53:41 taiwei.peng http://taiwei-peng.iteye.com 我要评论(0)

摘要：1.xml代码<?xmlversion="1.0"encoding="utf-8"?><DataSetxmlns="http://www.sz-customs.gov.cn/WebService/"><Tableid="Table1"msdata:rowOrder="0"><FULL_NAME>广州广发鞋材有限公司</FULL_NAME><TRADE_CO/><CASE_ID>
标签：excel XML 解析

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"));
??? }