使用POI和DynaBean导出Excel的完整例子 ExcelApacheSQLStrutsBean

之前做过几个POI的Excel处理，但都没有去整理过。俗话说好记性敌不过烂笔头，所以今天做了一个觉得把代码整理一遍贴出来。

LazyDynaBean!

使用到的东西： struts1.2

jdbc

Apache commons-beanutils.jar

Apache commons-lang.jar

POI 3.0

闲话不多说，上代码....

struts的配置文件：

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN" "http://struts.apache.org/dtds/struts-config_1_2.dtd">

<struts-config>
  <data-sources />
  <form-beans >


  </form-beans>
  <global-exceptions />
  <global-forwards >
  	<forward name="logs" path="/logs/logs.jsp"></forward>
  </global-forwards>
  <action-mappings >
    <!-- 日志管理 -->
    <action path="/logsAction" type="logs.LogsAction"></action>
  </action-mappings>
  <message-resources parameter="ApplicationResources" />
</struts-config>

 
Action：

package logs;

import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.commons.lang.StringUtils;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import logs.dao.LogsDao;

/**
 * 日志
 *
 * @author zhongzy
 * @时间 2011-4-13
 */
public class LogsAction extends Action {
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		request.setCharacterEncoding("GBK");
		String method = StringUtils.defaultIfEmpty(request.getParameter("method"), "");
		if ("exportExcel".equals(method)) {
			return exportExcel(mapping, form, request, response);
		}
		// 权限控制
		String menuId = request.getParameter("menuId");
		int pageSize = 10;
		int pageNum = 1;
		int rowCount = 0;

		String pageStr = request.getParameter("pageNum");

		if (pageStr != null) {
			pageNum = Integer.parseInt(pageStr);
		} else {
			pageNum = 1;
		}
		try {
			List<DynaBean> data = LogsDao.selectLogs(request);
			rowCount = LogsDao.getLogsCount(request);
			int pageCount = rowCount % pageSize == 0 ? rowCount / pageSize
					: (rowCount / pageSize + 1);
			request.setAttribute("pageNum", pageNum);
			request.setAttribute("count", rowCount);
			request.setAttribute("countPage", pageCount);
			request.setAttribute("list", data);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return new ActionForward("logs");
	}

	public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		response.setContentType("application/x-download");
		response.setCharacterEncoding("GBK");
		response.addHeader("Content-Disposition", "attachment;filename=Logs.xls");
		List<DynaBean> list = LogsDao.selectLogs(request);
		OutputStream out = response.getOutputStream();
		LogsExcel excel = new LogsExcel();
		excel.generalTableExport("前台登录日志", title(), list, out);
		out.close();
		return null;
	}

	public DynaBean title() {
		DynaBean title = new LazyDynaBean();
		//|角色|用户编号(用户ID)|用户名|昵称(用户名称)|日志时间|主机IP|主机名
		title.set("log_id", "序号");
		title.set("rolename", "角色");
		title.set("userid", "用户编号(用户ID)");
		title.set("username", "用户名");
		title.set("nickname", "昵称(用户名称)");
		title.set("logdate", "登录时间");
		title.set("hostip", "主机IP");
		title.set("hostname", "主机名");
		return title;
	}
}

 
由于是小应用就直接在Action调用Dao了，Dao：

package logs.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author zhongzy
 * @version 1.0
 */
public class LogsDao {
	private static log = LogFactory.getLog(LogsDao .class);

	/**
	 * 查询前台登录日志
	 * @return
	 * @author zhongzy 2011-04-13
	 */
	public static List<DynaBean> selectLogs(HttpServletRequest request) {
		String sql = "	SELECT log_id, rolename, userid, username, nickname, logdate, hostip, hostname \n";
		sql += "	FROM tc_logs	\n";
		sql += "	WHERE	1=1		\n";
		sql += getCondition(request);

		List result = null;
		ResultSet rs = null;
		try {
			System.out.println(sql);
			rs = .....//查询出结果集
			//取数据库的列名
			ResultSetMetaData o_resultSetMetaData = rs.getMetaData();
		    int n_total = o_resultSetMetaData.getColumnCount();
			result = new ArrayList<DynaBean>();
			DynaBean bean = null;
			while (rs.next()) {
				bean = new LazyDynaBean();
				for(int i = 1; i <= n_total; i++) {
					String s_columnName = o_resultSetMetaData.getColumnLabel(i);
					String s_value = rs.getString(s_columnName);
					bean.set(s_columnName, s_value);
				}
				result.add(bean);
			}
		}
		catch(Exception ex) {
			log.info("查询前台登录日志失败！" + ex.getMessage());
			ex.printStackTrace();
		}
		finally {
			if (rs != null) {
				rs.close();
			}
		}
		return result;
	}

	/**
	 * 查询日志总数
	 * @param request
	 * @return
	 * @author zhongzy 2011-04-13
	 */
	public static int getLogsCount(HttpServletRequest request) {
		String sql = "	SELECT COUNT(1) AS dataTotal \n";
		sql += "	FROM tc_logs	\n";
		sql += "	WHERE 1=1		\n";
		sql += getCondition(request);
		int result = 0;
		try {
			result  = ...//查询

		}
		catch(Exception ex) {
			log.info("查询前台登录日志总数失败！" + ex.getMessage());
		}
		return result;
	}
	/**
	 * 查询语句条件
	 * @param request
	 * @return
	 * @author zhongzy 2011-04-13
	 */
	protected static String getCondition(HttpServletRequest request) {
		String sql = "";
		String startDate = StringUtils.defaultIfEmpty(request.getParameter("startDate"), "");
		String endDate = StringUtils.defaultIfEmpty(request.getParameter("endDate"), "");
		String rolename = StringUtils.defaultIfEmpty(request.getParameter("rolename"), "");
		String username = StringUtils.defaultIfEmpty(request.getParameter("username"), "");
		String nickname = StringUtils.defaultIfEmpty(request.getParameter("nickname"), "");
		String hostip = StringUtils.defaultIfEmpty(request.getParameter("hostip"), "");
		sql += StringUtils.isEmpty(startDate)== true ? "" : "	AND	logdate>='" + startDate +"'\n";
		sql += StringUtils.isEmpty(endDate)== true ? "" : "	AND	logdate<='" + endDate + "'\n";
		sql += StringUtils.isEmpty(rolename)==true ? "" : "	AND rolename='" + rolename + "'\n" ;
		sql += StringUtils.isEmpty(username)==true ? "" : "	AND username LIKE '%" + username.trim() + "%'\n" ;
		sql += StringUtils.isEmpty(nickname)==true ? "" : "	AND nickname LIKE '%" + nickname.trim() + "%'\n" ;
		sql += StringUtils.isEmpty(hostip)==true ? "" : "	AND hostip LIKE '%" + hostip.trim() + "%'\n" ;

		return sql;
	}
}

 

最后是Excel导出处理类：

package logs;import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaClass;
import org.apache.commons.beanutils.DynaProperty;
import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 简单 Excel 导出类
 * @author zhongzy at 2011-04-13
 *
 */

public class LogsExcel {
	/**
	 * 通用表格导出Excel, 单sheet
	 * @param sheetName		sheet显示文字
	 * @param tableTitle	表格标题
	 * @param tableData		数据
	 * @param out			输出流
	 * @throws IOException
	 */
	public void generalTableExport(String sheetName, DynaBean title, List data, OutputStream out) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);

		/**
		 * 数据区域个列样式
		 */
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 字体用的风格
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
		cellStyle.setWrapText(true); // 字符换行

		HSSFFont cellFont5 = workbook.createFont();
		cellFont5.setFontHeightInPoints((short) 10); // 字号
		cellFont5.setFontName("宋体"); // 设置为宋体
		cellStyle.setFont(cellFont5);

		/**
		 * 表头样式
		 */
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		//字体
		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 12); // 字体高度
		titlefont.setColor(HSSFColor.DARK_GREEN.index); // 字体颜色
		titlefont.setFontName("宋体"); // 字体
		titlefont.setBoldweight(HSSFFont.COLOR_NORMAL); // 字体加粗
		titleStyle.setFont(titlefont);
		/**
		 * 边框
		 */
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short)1);
		titleStyle.setBorderRight((short)1);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);				//样式
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);		//前景色
		titleStyle.setWrapText(false); // 标题栏字符不予换行


		/**
		 * 处理标题栏
		 */
		int row = 1; // 行号
		DynaClass titleDc = title.getDynaClass();				//DynaClass
		DynaProperty[] titledp =  titleDc.getDynaProperties();	//属性
		HSSFRow rowTitle = sheet.createRow(0); // 创建一行
		rowTitle.setHeight((short) 500); // 高

		for (int i=0; i<titledp.length; i++) {
			String key = (String) titledp[i].getName(); 		// 字段
			String value = (String) title.get(key);				//值
			HSSFCell celli = rowTitle.createCell((short) i); // 创建一列
			celli.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置单元格字符
			celli.setCellValue(value);
			celli.setCellStyle(titleStyle);
			sheet.setColumnWidth((short)i, (short)(value.length() * 899));		//设置标题各列的宽度
		}

		/**
		 * 处理数据列表
		 */
		for (Iterator iter = data.iterator(); iter.hasNext();) { // 根据list的大小生成
			// execel
			DynaBean bean = (LazyDynaBean) iter.next();
			HSSFRow rowi = sheet.createRow(row); // 创建一行
			rowi.setHeight((short) 300); // 高
			int cell = 0; // 列号
			DynaClass dc = bean.getDynaClass();
			DynaProperty[] dp =  dc.getDynaProperties();

			for (int i=0; i<dp.length; i++) {
				String key = (String) dp[i].getName(); // 字段值
				String value = (String) bean.get(key);
				HSSFCell celli = rowi.createCell((short) cell); // 创建一列
				celli.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置单元格字符
				celli.setCellValue(value);
				cell++;
			}
			row++;
		}
		workbook.write(out);
		out.flush();
	}
}

 JSP调用：

<c:set var="ctx" value="${pageContext.request.contextPath}"></c:set><script type="text/javascript">  
function exportExcel() {
	    	document.location.href = "${ctx}/logsAction.do?method=exportExcel";
	    }
 </script>

<input name="export" type="button" class="button" onclick="exportExcel();" value="结果导出Excel"/>

jsp部分就自己去完善吧。^^