java连接数据库获取数据并生成excel报表

package com.cignacmc.REPE159.main;

import java.io.File;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

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

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.SystemUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;

public class PolicyHolder
{
    private final static Log log = LogFactory.getLog(MainEntry.class);
   
    private static String ROOT_PATH = "E:/REPETool/HolderProfile";
   
    private static WritableWorkbook workbook;
   
    private static WritableSheet sheet;
   
    public static void main(String[] args)
    {
        init(args[0]);
        doMain();
        end();
    }
   
    @SuppressWarnings("unchecked")
    private static void doMain()
    {
        Session _session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction _transaction = _session.beginTransaction();
       
        String lastBizDate_Sql= "SELECT fld0003 FROM ordumcad";
        SQLQuery bizDateQuery = (SQLQuery)_session.createSQLQuery(lastBizDate_Sql).setResultTransformer(Transformers.TO_LIST);
        bizDateQuery.addScalar("fld0003",Hibernate.STRING); //列名, 数据类型
       
        List list = bizDateQuery.list();
        List list2 = (List)list.get(0);
        String lastBizDate = (String)list2.get(0);
       
        log.info("get last business date = " + lastBizDate);
       
        String hsql= "SELECT distinct cast(po.fld0002 as varchar(10)) as PolNo, " +
        " cast(na.fld0004 as varchar(20)) as FirstName, " +
        " cast(na.fld0003 as varchar(20)) as LastName, " +
       " cast(pm.PM_AGNT_REF as varchar(10)) as TMRNo, " +
       " cast(pm.PM_AGNT_REF as varchar(10)) as AgentRef, " +
       " CASE " +
       "        when po.fld0069='T' and po.pocii2='1' then cast(na.fld0041 as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='2' then cast(na.NAPAC2  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='3' then cast(na.MAPAC3  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='4' then cast(na.NAPAC4  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='5' then cast(na.NAPAC5  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='6' then cast(na.NAPAC6  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='7' then cast(na.NAPAC7  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='8' then cast(na.NAPAC8  as varchar(21)) " +
       "        when po.fld0069='T' and po.pocii2='9' then cast(na.NAPAC9  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='1' then cast(na.FLD0101 as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='2' then cast(na.NACRN2  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='3' then cast(na.NACRN3  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='4' then cast(na.NACRN4  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='5' then cast(na.NACRN5  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='6' then cast(na.NACRN6  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='7' then cast(na.NACRN7  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='8' then cast(na.NACRN8  as varchar(21)) " +
       "        when po.fld0069='K' and po.pocii2='9' then cast(na.NACRN9  as varchar(21)) " +
       " END as Account, " +
       " cast(na.fld0034 as varchar(20)) as UserID, " +
       " cast(na.fld0029 as varchar(1)) as Sex,  " +
       " '成功下单' as CallResult , " +
       " cast(na.namobp as varchar(20)) as MobilePhone , " +
       " cast(na.fld0046 as varchar(20)) as HomePhone , " +
       " cast(na.FLD0045 as varchar(20)) as OffPhone,  " +
       " cast(trim(na.fld0011) as varchar(140)) as Address1, "  +
       " cast(na.fld0017 as varchar(20)) as ZipCode,   " +
       " ' ' as FamAdress,  " +
       " ' ' as FamZip, " +
       " ' ' as relName, " +
       " ' ' as relation, " +
       " ' ' as relTelNo, " +
       " cast(po.fld0006 as varchar(3)) as reg_year, " +
       " cast(po.fld0007 as varchar(2)) as reg_month, " +
       " cast(po.fld0008 as varchar(2)) as reg_day, " +
       " cast(pm.PM_ACTUAL_DELIVER + 18000000 as varchar(8)) as actDeliDate,  " +
       "  cast(trim(na.fld0012) as varchar(140)) as Address2" +
        " FROM ordupo po, orduna na, ordupm pm " +
        " WHERE po.fld0002 = pm.pm_policy " +
        " and pm.pm_trailer = 0  " +
        " and po.fld0021 = na.fld0002 " +
        " and left(po.fld0185,5) = 'CEBCC'   " +
        " and pm.PM_DELIVERY_FLAG = 'Y'  " +
        " and left(pm.pm_campaign_no, 5) in ('5ECG3', '5EDG3')  " +
        " and po.fld0003 in ('1','4') " +
        " and pm.PMLUPDDATE =:lastBizDate " ;
       
        Query _SqlQuery = _session.createSQLQuery(hsql);
        _SqlQuery.setParameter("lastBizDate",  lastBizDate);
       
        ScrollableResults _result = _SqlQuery.scroll(ScrollMode.SCROLL_SENSITIVE);
       
        List _list = new LinkedList();
        int i=0;
        while (_result.next())
        {
           
            List _rowList = new LinkedList();
            String FirstName =(String) _result.get(1);
            String fName = FirstName.replaceAll("(^[ |　]*|[ |　]*$)","");
            String LastName =(String) _result.get(2);
            String lName = LastName.replaceAll("(^[ |　]*|[ |　]*$)","");
            _rowList.add(_result.get(0));
            _rowList.add(fName + lName);
            _rowList.add(_result.get(3));
            _rowList.add(_result.get(4));
            _rowList.add(_result.get(5));
            _rowList.add(_result.get(6));
            _rowList.add(_result.get(7));
            _rowList.add(_result.get(8));
            _rowList.add(_result.get(9));
            _rowList.add(_result.get(10));
            _rowList.add(_result.get(11));
            String address = (String)_result.get(12)+_result.get(23);
            _rowList.add(address.replaceAll(" ", ""));
            _rowList.add(_result.get(13));
            _rowList.add(_result.get(14));
            _rowList.add(_result.get(15));
            _rowList.add(_result.get(16));
            _rowList.add(_result.get(17));
            _rowList.add(_result.get(18));
            _rowList.add(convertYMD2Date((String)_result.get(19), (String)_result.get(20), (String)_result.get(21) ));
            _rowList.add(_result.get(22));
           
            _list.add(_rowList);
           
        }
        _transaction.commit();
        _list = fillHeadList(_list);
        generateExcel(_list);
       
    }
   
    @SuppressWarnings("unchecked")
    public static void generateExcel(List _list)
    {
        String _excelName = "HP" + DateFormatUtils.format(new Date(), "yyyy.MM.dd HHmmss") + ".xls";
        try
        {
            File filePath = new File(ROOT_PATH);
            if(!filePath.exists()) {
                filePath.mkdir();
            }
            String fileName =  ROOT_PATH + SystemUtils.FILE_SEPARATOR + _excelName;
            workbook = Workbook.createWorkbook(new File(fileName));
            sheet = workbook.createSheet("Report", 0);
            fillTable(_list, true);
            workbook.write();
        }
        catch (Exception e)
        {
            log.error(e);
        }
        finally
        {
            try
            {
                workbook.close();
            }
            catch (Exception e2)
            {
                log.error(e2);
            }
           
        }
    }
   
    private static void init(String arg0)
    {
        if(null != arg0 && "" != arg0) {
            ROOT_PATH= arg0;
        }
       
    }
   
    private static void end()
    {
        HibernateUtil.shutdown();
        log.info("System exit************************");
        System.exit(0);
    }
   
    @SuppressWarnings("unchecked")
    private static List fillHeadList(List _dataList)
    {
        List _headList = new LinkedList();
        _headList.add("PolNo");
        _headList.add("UserName");
        _headList.add("TMRNo");
        _headList.add("AgentRef");
        _headList.add("Account");
        _headList.add("UserID");
        _headList.add("Sex");
        _headList.add("CallResult");
        _headList.add("MobilePhone");
        _headList.add("HomePhone");
        _headList.add("OffPhone");
        _headList.add("Address");
        _headList.add("ZipCode");
        _headList.add("FamAdress");
        _headList.add("FamZip");
        _headList.add("RelName");
        _headList.add("Relation");
        _headList.add("RelTelNo");
        _headList.add("RegDate");
        _headList.add("ActDeliDate");
       
        List _list = new LinkedList();
        _list.add(_headList);
        _list.addAll(_dataList);
       
        return _list;
    }
   
    @SuppressWarnings("unchecked")
    public static void fillTable(List dataList, boolean firstRowHead) throws Exception
    {
        if (dataList != null)
        {
            for (int i = 0; i < dataList.size(); i++)
            {
                if (i == 0 && firstRowHead)
                {
                    addRow((List) dataList.get(i), firstRowHead, i);
                }
                else
                {
                    addRow((List) dataList.get(i), false, i);
                }
            }
        }
    }
   
    @SuppressWarnings("unchecked")
    protected static void addRow(List dataList, boolean isHead, int rowIndex) throws Exception
    {
        Label cell = null;
       
        int colIndex = 0;
        String value = null;
        String tempValue = null;
        for (Iterator it = dataList.iterator(); it.hasNext();)
        {   Object obj = it.next();
            tempValue = obj.toString();
            value = (null == tempValue) ? "" : tempValue;
           
            if (isHead)
            {
                WritableFont wf = new WritableFont(WritableFont.TAHOMA, 11, WritableFont.BOLD, false);
                WritableCellFormat wcfF = new WritableCellFormat(wf);
                cell = new Label(colIndex++, rowIndex, value, wcfF);
            }
            else
            {
                cell = new Label(colIndex++, rowIndex, value);
            }
           
            sheet.addCell(cell);
        }
       
    }
   
   
    private static String convertYMD2Date(String _year, String _month, String _day) {
       
        StringBuffer regDate = new StringBuffer();
        regDate.append(_year);
        regDate.append(parseDate(_month));
        regDate.append(parseDate(_day));
       
        int decimalDate = Integer.parseInt(regDate.toString());       
        int realDate = decimalDate + 18000000;
       
        return String.valueOf(realDate);
       
    }
   
    private static String parseDate(String _original) {
       
        if(null != _original && "" != _original) {
            int strLen = _original.length();
            if(strLen == 1) {
                return "0"+_original;
            }       
            return _original;
        }
        return "";
    }
   
   
   
}