超级简单报表导出方案，如何用XML Publisher在eclipse中生成excel,pdf,html

原创文章，严禁转载

作者：蓝色雨，  qq:89062647      邮箱:xiao7788@126.com

参考资料：http://soft.zdnet.com.cn/software_zone/2007/1007/539154.shtml

官方介绍：
http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html

XML Publisher官方下载:
http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

  XML Publisher为oracle一款企业级报表解决软件

  此方案做法为，自己写sql语句和查询语句，然后可以指定生成的l输出格式，包括excel,pdf,html。不需要完整的安装XML Publisher软件，只需要在eclipse程序中简单调用几个jar包就能生成。

  第一步写sql语句，然后生成xml文件

  第二步制作rtf格式的word文件作为模板

第一步写sql语句，然后生成xml文件：

数据库连接类：

package neil.study.xmlPublish.db;


import java.io.PrintStream;
import java.sql.*;
import java.util.Enumeration;
import java.util.Hashtable;

// Referenced classes of package parim.net.db:
//            DaoDB, SystemVariable

public class MDBOper
{

  

    //lai
    public Connection getConnection()
        throws Exception
    {
    	 Connection conn = null;

    	
    	String url = "jdbc:oracle:thin:@192.0.0.0:1521:sid";

    	String user = "name";

    	String password = "password";
    	
    	
    	String jdbcDriverClass="oracle.jdbc.driver.OracleDriver";
    	
	
		Class.forName(jdbcDriverClass).newInstance();
		conn = java.sql.DriverManager.getConnection(url, user, password);
		
	
		return conn;
    }

    
    public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection con = null;
		try {

			con = new MDBOper().getConnection();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from org_user");
			System.out.println("begin");
			while (rs.next()) {
				System.out.println(rs.getInt("ID"));

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

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(con!=null){
				  con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}
}

产生xml文件类:

package neil.study.xmlPublish.GeneateXML;



import java.io.FileOutputStream;
import java.util.ArrayList;
import oracle.xml.parser.v2.XMLDocument;
import org.w3c.dom.Element;

public class GenerateXMLfile
{

    private XMLDocument doc;
    private Element eleRoot;
    private String columnNames[];

    public GenerateXMLfile()
    {
        doc = new XMLDocument();
    }

    public void CreateXML(String filePathName, ArrayList dataList)
    {
        addFileHead();
        columnNames = (String[])dataList.get(0);
        for(int i = 1; i < dataList.size(); i++)
            addFileBody((String[])dataList.get(i));

        try
        {
            FileOutputStream fos = new FileOutputStream(filePathName);
            doc.print(fos, "UTF-8");
            fos.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

    private void addFileBody(String dataList[])
    {
        try
        {
            Element ROW = doc.createElement("ROW");
            eleRoot.appendChild(ROW);
            for(int i = 0; i < dataList.length; i++)
                appElement(ROW, columnNames[i], dataList[i]);

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private void appElement(Element ele, String eleName, String value)
    {
        try
        {
            Element eleChild = doc.createElement(eleName);
            ele.appendChild(eleChild);
            eleChild.appendChild(doc.createTextNode(value));
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private void addFileHead()
    {
        try
        {
            eleRoot = doc.createElement("ROWSET");
            doc.appendChild(eleRoot);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private void appendChild(Element parent, String childName)
    {
        Element child = doc.createElement(childName);
        parent.appendChild(child);
    }

    public static void main(String args1[])
    {
    }
}

主类：

package neil.study.xmlPublish;



import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;

import oracle.xdo.XDOException;
import oracle.xdo.template.FOProcessor;
import oracle.xdo.template.RTFProcessor;

import neil.study.xmlPublish.GeneateXML.GenerateXMLfile;
import neil.study.xmlPublish.db.MDBOper;


public class XMLPublisher
{

    public XMLPublisher()
    {
    }

    public ArrayList getData(String sql)
    {
        MDBOper mdb;
        Connection conn;
        ArrayList dataList;
        mdb = null;
        conn = null;
        dataList = null;
        try
        {
            mdb = new MDBOper();
            conn = mdb.getConnection();
            dataList = new ArrayList();
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            String columnNames[] = new String[numberOfColumns];
            for(int i = 0; i < numberOfColumns; i++){
                columnNames[i] = rsmd.getColumnName(i + 1);
            }

            dataList.add(columnNames);
            
            
            
            
            String columnValues[];
            for(; rs.next(); dataList.add(columnValues))
            {
                columnValues = new String[numberOfColumns];
                for(int i = 0; i < numberOfColumns; i++)
                    columnValues[i] = rs.getString(i + 1) != null ? rs.getString(i + 1).trim() : "";

            }

            rs.close();
            ps.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace(System.out);

        }
        finally{
    
            if(conn != null)
            {
                try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                conn = null;
            }

        }
       
        return dataList;
    }

    public boolean executeDisplay(String filePath, String fileName, String type, String sql, String parameters[])
    {
        boolean isTrue = true;
        try
        {
            ArrayList dataList = getData(sql);
            if(dataList != null && dataList.size() > 0)
            {
                GenerateXMLfile generateXMLfile = new GenerateXMLfile();
                generateXMLfile.CreateXML(filePath + fileName + ".xml", dataList);
                buildPublisher(filePath, fileName, type);
            } else
            {
                isTrue = false;
            }
        }
        catch(Exception ex)
        {
            isTrue = false;
            ex.printStackTrace(System.out);
        }
        return isTrue;
    }

    public void buildPublisher(String filePath, String fileName, String type)
    {
        try
        {
            XMLPublisher xMLPublisher = new XMLPublisher();
            FOProcessor processor = new FOProcessor();
            RTFProcessor rtfProcessor = new RTFProcessor(filePath + fileName + ".rtf");
            rtfProcessor.setOutput(filePath + fileName + ".xsl");
            rtfProcessor.process();
            processor.setLocale("UTF-8");
            processor.setData(filePath + fileName + ".xml");
            processor.setTemplate(filePath + fileName + ".xsl");
            if(type != null && type.equals("xls"))
            {
                processor.setOutput(filePath + fileName + ".xls");
                processor.setOutputFormat((byte)4);
            } else
            if(type != null && type.equals("pdf"))
            {
                processor.setOutput(filePath + fileName + ".pdf");
                processor.setOutputFormat((byte)1);
            } else
            {
                processor.setOutput(filePath + fileName + ".html");
                processor.setOutputFormat((byte)3);
            }
            processor.generate();
        }
        catch(XDOException xe)
        {
            xe.printStackTrace(System.out);
        }
        catch(IOException ie)
        {
            ie.printStackTrace(System.out);
        }
        catch(Exception ex)
        {
            ex.printStackTrace(System.out);
        }
    }

    public static void main(String args[])
    {
        XMLPublisher xp = new XMLPublisher();
        String sql = "SELECT ROWNUM NUM,ID,FULL_NAME,NICK_NAME FROM ORG_USER";
        xp.executeDisplay("c:/temp/", "classinfo", "pdf", sql, null);
    }
}

上面包括main入口方法，可以根据

SELECT ROWNUM NUM,ID,FULL_NAME,NICK_NAME FROM ORG_USER

语句生成相应格式的文件这里指定为 pdf，excel为xls，也可以为html