之前做过几个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" οnclick="exportExcel();" value="结果导出Excel"/>
jsp部分就自己去完善吧。^^