java导出excel(写入到excel后再下载)示例

package com.tyhb.server.core.db; import java.beans.PropertyDescriptor; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Vector; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @SuppressWarnings("unchecked") public class Dao<T> { final private static int BEAN = 1; final private static int BEAN_LIST = 2; final private static int MAP = 3; final private static int MAP_LIST = 4; final private static int ARRAY = 5; final private static int ARRAY_LIST = 6; // private static Hashtable prebuildSql = new Hashtable(); private static Vector<SysSqlLog> sqlLogVector; private static int logSqlCount = 10; private ConnectionManager connectionManager; private final Logger logger = LoggerFactory.getLogger(getClass()); public Dao() { connectionManager = ConnectionManager .getInstance(ConnectionManager.DB_MYSQL); } public Dao(int dbType) { connectionManager = ConnectionManager.getInstance(dbType); } /* *//** * 查询出结果，保存在XML中 */ /* * public String getXml(String sql) { Object[] result = (Object[]) * query(ARRAY, sql, null, null); Clob clob = (Clob) result[0]; return * clobToString(clob); } * * public String getXml(String sql, Object param1) { Object[] result = * (Object[]) query(ARRAY, sql, new Object[] { param1 }, null); Clob clob = * (Clob) result[0]; return clobToString(clob); } * * public String getXml(String sql, Object param1, Object param2) { Object[] * result = (Object[]) query(ARRAY, sql, new Object[] { param1, param2 }, * null); Clob clob = (Clob) result[0]; return clobToString(clob); } * * public String getXml(String sql, Object param1, Object param2, Object * param3) { Object[] result = (Object[]) query(ARRAY, sql, new Object[] { * param1, param2, param3 }, null); Clob clob = (Clob) result[0]; return * clobToString(clob); } */ /** * 查询出一条记录，保存在Object[]中 @ */ public Object[] getArray(String sql) { return (Object[]) query(ARRAY, sql, null, null); } public Object[] getArray(String sql, Object param1) { return (Object[]) query(ARRAY, sql, new Object[] { param1 }, null); } public Object[] getArray(String sql, Object param1, Object param2) { return (Object[]) query(ARRAY, sql, new Object[] { param1, param2 }, null); } public Object[] getArray(String sql, Object param1, Object param2, Object param3) { return (Object[]) query(ARRAY, sql, new Object[] { param1, param2, param3 }, null); } /** * 查询出一组存在于Map中的对象，专用于投影查询 @ */ public List<Map<String, Object>> getMapList(String sql) { return (List) query(MAP_LIST, sql, null, null); } public List<Map<String, Object>> getMapList(String sql, Object param1) { return (List) query(MAP_LIST, sql, new Object[] { param1 }, null); } public List<Map<String, Object>> getMapList(String sql, Object param1, Object param2) { return (List) query(MAP_LIST, sql, new Object[] { param1, param2 }, null); } public List<Map<String, Object>> getMapList(String sql, Object param1, Object param2, Object param3) { return (List) query(MAP_LIST, sql, new Object[] { param1, param2, param3 }, null); } /** * 查询出一条记录，保存在Map中 @ */ public Map<String, Object> getMap(String sql) { return (Map) query(MAP, sql, null, null); } public Map<String, Object> getMap(String sql, Object param1) { return (Map) query(MAP, sql, new Object[] { param1 }, null); } public Map<String, Object> getMap(String sql, Object param1, Object param2) { return (Map) query(MAP, sql, new Object[] { param1, param2 }, null); } public Map<String, Object> getMap(String sql, Object param1, Object param2, Object param3) { return (Map) query(MAP, sql, new Object[] { param1, param2, param3 }, null); } /** * 根据类名以及id查询出一个对像 @ */ private Object getBean(Object pojo, Integer id) { StringBuffer sql = new StringBuffer(); sql.append("select * from "); sql.append(getTableName(pojo)); sql.append(" where id = ?"); return getBean(pojo.getClass(), sql.toString(), id); } private Object buildField(Object[] array) { if (array != null && array.length > 0) { return array[0]; } else { return null; } } public Object getField(String sql) { return buildField((Object[]) query(ARRAY, sql, null, null)); } public Object getField(String sql, Object param1) { return buildField((Object[]) query(ARRAY, sql, new Object[] { param1 }, null)); } public Object getField(String sql, Object param1, Object param2) { return buildField((Object[]) query(ARRAY, sql, new Object[] { param1, param2 }, null)); } public Object getField(String sql, Object param1, Object param2, Object param3) { return buildField((Object[]) query(ARRAY, sql, new Object[] { param1, param2, param3 }, null)); } private List<Object> buildFieldList(List<Object[]> arrayList) { if (arrayList != null && arrayList.size() > 0) { List fieldList = new ArrayList(); for (Object[] array : arrayList) { fieldList.add(array[0]); } return fieldList; } else { return null; } } public List<Object> getFieldList(String sql) { List<Object[]> arrayList = (List<Object[]>) query(ARRAY_LIST, sql, null, null); return buildFieldList(arrayList); } public List<Object> getFieldList(String sql, Object param1) { List<Object[]> arrayList = (List<Object[]>) query(ARRAY_LIST, sql, new Object[] { param1 }, null); return buildFieldList(arrayList); } public List<Object> getFieldList(String sql, Object param1, Object param2) { List<Object[]> arrayList = (List<Object[]>) query(ARRAY_LIST, sql, new Object[] { param1, param2 }, null); return buildFieldList(arrayList); } public List<Object> getFieldList(String sql, Object param1, Object param2, Object param3) { List<Object[]> arrayList = (List<Object[]>) query(ARRAY_LIST, sql, new Object[] { param1, param2, param3 }, null); return buildFieldList(arrayList); } /** * 查询出一个pojo对象 @ */ public T getBean(String sql) { return (T) query(BEAN, sql, null, getClassDefinition(sql)); } public T getBean(String sql, Object param1) { return (T) query(BEAN, sql, new Object[] { param1 }, getClassDefinition(sql)); } public T getBean(String sql, Object param1, Object param2) { return (T) query(BEAN, sql, new Object[] { param1, param2 }, getClassDefinition(sql)); } public T getBean(String sql, Object param1, Object param2, Object param3) { return (T) query(BEAN, sql, new Object[] { param1, param2, param3 }, getClassDefinition(sql)); } /** ***************************************************************************** */ /** * 查询出一个投影对象 @ */ public Object getBean(Class c, String sql) { return query(BEAN, sql, null, c); } public