欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-16 00:01:31
...

之前做过几个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部分就自己去完善吧。^^