利用POI进行Excel的导出

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

需求：将用户的违约金信息导出为excel表格格式

步骤

1. 数据库中增加按钮的值（注意上级编号要和页面隐藏域中的相等）

DZ内容（页面加载时根据SJBH查询数据库内容，读取DZ字段信息并加载样式及方法）

<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-remove'" onclick="getWyj();">违约金查询</a>

页面加载后菜单栏出现“违约金查询”按钮

2. 创建js方法

sf_wyjcx.js

3. 点击菜单栏的“违约金查询”首先加载用户所有的违约金信息

后台方法如下：

UserPayAction.java

// 查询违约金
public String getWyj(){
jmjs.setYhbh(jmjs.getYhbh());
List list=userPayService.getWyjlist(jmjs);
jmjs.setList(list);
return "addwyj";
}

UserPayDaoImpl.java

public List getWyjlist(SfJmjsT vo){
List list = new ArrayList();
StringBuilder sql = new StringBuilder();
List result = null;
sql.append("select a.*,b.*,c.* from (select cnq,sfmj,dj,ysje,sfje,qfje,decode(sfhjwyj,'否',SF_ZNJ_F_CFD('居民',yhbh,?,cnq),znjys)znj," +
"SF_ZNJ_F_TS(yhbh,?,cnq)ts from sf_jmjs_t where zf=0 and qfje>0 and yhbh=? )a," +
"(select sum(qfje) zqf,sum(decode(sfhjwyj,'否',SF_ZNJ_F_CFD('居民',yhbh,?,cnq),znjys))zznj from sf_jmjs_t " +
"where zf=0 and qfje>0 and yhbh=?)b,(select yhbm,yhkh,yhmc,lxdh,fgs,qy,rlz,xq,dz from sf_jmyh_t where zf=0 and yhbh=?)c");
Date da=new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String dateString = formatter.format(da);
DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
Date date=null;
try {
date = format1.parse(dateString);
} catch (ParseException e) {
e.printStackTrace();
}
list.add(date);
list.add(date);
list.add(vo.getYhbh());
list.add(date);
list.add(vo.getYhbh());
list.add(vo.getYhbh());
try {
result = this.executeSqlList(sql.toString(), list.toArray(), SfJmjsT.class);
} catch (BDXCException e) {
e.printStackTrace();
logger.error(BDXCUtil.getExceptionString(e));
}
return result;
}

返回的数据在页面进行遍历

sf_wyjcx.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib uri="/struts-tags" prefix="s" %>
<%@ taglib prefix="C" uri="http://java.sun.com/jsp/jstl/core"%>
<script type="text/javascript" src="js/common/validate.js"></script>
<script type="text/javascript">
</script>
<form id="getWyjForm" method="post" action="">
<s:token></s:token>
<div>
<table>
<tbody>
<fieldset class="fieldsetCss" style="width:95%;">
<legend class="legendCss" >违约金信息</legend>
<table id="getWyj" border="1">
<tr>
<td align="center">采暖期</td>
<td align="center">用户编码</td>
<td align="center">用户名称</td>
<td align="center">联系电话</td>
<td align="center">地址</td>
<td align="center">收费面积</td>
<td align="center">单价</td>
<td align="center">应收金额</td>
<td align="center">实收金额</td>
<td align="center">欠费金额</td>
<td align="center">违约金</td>
<td align="center">违约金天数</td>
</tr>

<s:iterator value="jmjs.list" var="b">
<tr>
<td class="contentCss" align="center">${b.cnq }</td>
<td class="contentCss" align="center">${b.yhbm }</td>
<td class="contentCss" align="center">${b.yhmc }</td>
<td class="contentCss" align="center">${b.lxdh }</td>
<td class="contentCss" align="center">${b.dz }</td>
<td class="contentCss" align="center">${b.sfmj }</td>
<td class="contentCss" align="center">${b.dj }</td>
<td class="contentCss" align="center">${b.ysje }</td>
<td class="contentCss" align="center">${b.sfje }</td>
<td class="contentCss" align="center">${b.qfje }</td>
<td class="contentCss" align="center">${b.znj }</td>
<td class="contentCss" align="center">${b.ts }</td>
</tr>
</s:iterator> <tr>
<td class="contentCss" align="center">合计：</td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center"></td>
<td class="contentCss" align="center">${b.zqf }</td>
<td class="contentCss" align="center">${b.zznj }</td>
<td class="contentCss" align="center"></td>
</tr>
</table>
</fieldset>
</tbody>
</table>
</div>
</form>

页面显示效果

4. 导出

UserPayAction.java

/**
* 导出违约金信息
*/
public String getWyjExcel(){
String yhbh = this.getRequest().getParameter("yhbh");
wyjExcelIs = userPayService.getWyjExcelIs(context,yhbh);
return "wyjExcel";
} /**
* 违约金导出表格名称*/
public String getWyjExcelName() {
String wyjExcelName = null;
try {
wyjExcelName = new String("违约金信息.xls".getBytes(),
"ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
logger.error(BDXCUtil.getExceptionString(e));
}
return wyjExcelName;
} public InputStream getWyjExcelIs() {
return wyjExcelIs;
} public void setWyjExcelIs(InputStream wyjExcelIs) {
this.wyjExcelIs = wyjExcelIs;
}

struts.xml

<result name="wyjExcel" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="inputName">wyjExcelIs</param>
<param name="contentDisposition">
attachment;fileName = "${wyjExcelName}"
</param>
<param name="bufferSize">1024</param>
</result>

UserPayServiceImpl.java

/**
* 导出违约金信息
*
* @return 违约金信息文件的输入流
*/
public InputStream getWyjExcelIs(BDXCPageContext context, String yhbh) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
HSSFWorkbook workbook = new HSSFWorkbook();
//头标题样式
HSSFCellStyle style1 = workbook.createCellStyle(); // 样式对象
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
HSSFFont f1 = workbook.createFont(); f1.setFontName("宋体");
f1.setFontHeightInPoints((short) 20);//字号
f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
style1.setFont(f1);
//表头样式
HSSFCellStyle style2 = workbook.createCellStyle(); // 样式对象
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
style2.setWrapText(true);
style2.setBorderBottom((short) 1);//下边框
style2.setBorderLeft((short) 1);//左边框
style2.setBorderRight((short) 1);//右边框
style2.setBorderTop((short) 1);//上边框 HSSFFont f2 = workbook.createFont();
f2.setFontName("宋体");
f2.setFontHeightInPoints((short) 12);//字号
f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
style2.setFont(f2);
//表体样式
HSSFCellStyle style3 = workbook.createCellStyle(); // 样式对象
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
style3.setWrapText(true);
style3.setBorderBottom((short) 1);//下边框
style3.setBorderLeft((short) 1);//左边框
style3.setBorderRight((short) 1);//右边框
style3.setBorderTop((short) 1);//上边框 HSSFFont f3 = workbook.createFont();
f3.setFontName("宋体");
f3.setFontHeightInPoints((short) 10);//字号
style3.setFont(f3); //添加工作表
HSSFSheet sheet = workbook.createSheet("违约金信息");
//设置默认行高
sheet.setDefaultRowHeightInPoints(25);
//设置工作表列宽
for (short j = 0; j < 12; j++) {
if(j==4){
sheet.setColumnWidth(j, (short) (20 * 256));
}else if(j==11){
sheet.setColumnWidth(j, (short) (16 * 256));
}else{
sheet.setColumnWidth(j, (short) (12 * 256));
}
}
//添加表头信息
List<String> headList = new ArrayList<String>();
headList.add("采暖期");
headList.add("用户编码");
headList.add("用户名称");
headList.add("联系电话");
headList.add("地址");
headList.add("收费面积");
headList.add("单价");
headList.add("应收金额");
headList.add("实收金额");
headList.add("欠费金额");
headList.add("违约金");
headList.add("违约金天数");
//第一行头标题
HSSFRow nRow1 =sheet.createRow(0);
nRow1.setHeight((short) (30 * 20));
HSSFCell ncell1 = nRow1.createCell((short) 0);
ncell1.setCellValue("违约金信息");
ncell1.setCellStyle(style1);
ncell1.setCellType(HSSFCell.CELL_TYPE_STRING);
sheet.addMergedRegion(new Region(0,(short)0,0,(short)11));
//第二行表头
HSSFRow nRow2 =sheet.createRow(1);
nRow2.setHeight((short) (25 * 20));
for (short k = 0; k < 12; k++) { HSSFCell cell = nRow2.createCell(k);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style2);
cell.setCellValue(headList.get(k));
}
//表体
SfJmjsT jmjs = new SfJmjsT();
jmjs.setYhbh(Long.parseLong(yhbh));
List list=getWyjlist(jmjs);
for (int i = 2; i < list.size()+2; i++) {
SfJmjsT js = (SfJmjsT) list.get(i-2);
HSSFRow Row =sheet.createRow(i);
Row.setHeight((short) (25 * 20));
//采暖期
HSSFCell xcell = Row.createCell((short)0);
xcell.setCellType(HSSFCell.CELL_TYPE_STRING);
xcell.setCellStyle(style3);
xcell.setCellValue(js.getCnq());
//用户编码
HSSFCell xcel2 = Row.createCell((short)1);
xcel2.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel2.setCellStyle(style3);
xcel2.setCellValue(js.getYhbm());
//用户名称
HSSFCell xcel3 = Row.createCell((short)2);
xcel3.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel3.setCellStyle(style3);
xcel3.setCellValue(js.getYhmc());
//联系电话
HSSFCell xcel4 = Row.createCell((short)3);
xcel4.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel4.setCellStyle(style3);
xcel4.setCellValue(js.getLxdh());
//地址
HSSFCell xcel5 = Row.createCell((short)4);
xcel5.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel5.setCellStyle(style3);
xcel5.setCellValue(js.getDz());
//收费面积
HSSFCell xcel6 = Row.createCell((short)5);
xcel6.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel6.setCellStyle(style3);
xcel6.setCellValue(js.getSfmj());
//单价
HSSFCell xcel7 = Row.createCell((short)6);
xcel7.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel7.setCellStyle(style3);
xcel7.setCellValue(js.getDj());
//应收金额
HSSFCell xcel8 = Row.createCell((short)7);
xcel8.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel8.setCellStyle(style3);
xcel8.setCellValue(js.getYsje());
//实收金额
HSSFCell xcel9 = Row.createCell((short)8);
xcel9.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel9.setCellStyle(style3);
xcel9.setCellValue(js.getSfje());
//欠费金额
HSSFCell xcel10 = Row.createCell((short)9);
xcel10.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel10.setCellStyle(style3);
xcel10.setCellValue(js.getQfje());
//违约金
HSSFCell xcel11 = Row.createCell((short)10);
xcel11.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel11.setCellStyle(style3);
xcel11.setCellValue(js.getZnj());
//违约金天数
HSSFCell xcel12 = Row.createCell((short)11);
xcel12.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel12.setCellStyle(style3);
xcel12.setCellValue(js.getTs());
}
//表尾合计
SfJmjsT hjJs = (SfJmjsT) list.get(0);
HSSFRow lastRow =sheet.createRow(list.size()+2);
lastRow.setHeight((short) (25 * 20));
//采暖期
HSSFCell xcell = lastRow.createCell((short)0);
xcell.setCellType(HSSFCell.CELL_TYPE_STRING);
xcell.setCellStyle(style2);
xcell.setCellValue("合计：");
//用户编码
HSSFCell xcel2 = lastRow.createCell((short)1);
xcel2.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel2.setCellStyle(style2);
xcel2.setCellValue("");
//用户名称
HSSFCell xcel3 = lastRow.createCell((short)2);
xcel3.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel3.setCellStyle(style2);
xcel3.setCellValue("");
//联系电话
HSSFCell xcel4 = lastRow.createCell((short)3);
xcel4.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel4.setCellStyle(style2);
xcel4.setCellValue("");
//地址
HSSFCell xcel5 = lastRow.createCell((short)4);
xcel5.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel5.setCellStyle(style2);
xcel5.setCellValue("");
//收费面积
HSSFCell xcel6 = lastRow.createCell((short)5);
xcel6.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel6.setCellStyle(style2);
xcel6.setCellValue("");
//单价
HSSFCell xcel7 = lastRow.createCell((short)6);
xcel7.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel7.setCellStyle(style2);
xcel7.setCellValue("");
//应收金额
HSSFCell xcel8 = lastRow.createCell((short)7);
xcel8.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel8.setCellStyle(style2);
xcel8.setCellValue("");
//实收金额
HSSFCell xcel9 = lastRow.createCell((short)8);
xcel9.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel9.setCellStyle(style2);
xcel9.setCellValue("");
//欠费金额
HSSFCell xcel10 = lastRow.createCell((short)9);
xcel10.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel10.setCellStyle(style2);
xcel10.setCellValue(hjJs.getZqf());
//违约金
HSSFCell xcel11 = lastRow.createCell((short)10);
xcel11.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
xcel11.setCellStyle(style2);
xcel11.setCellValue(hjJs.getZznj());
//违约金天数
HSSFCell xcel12 = lastRow.createCell((short)11);
xcel12.setCellType(HSSFCell.CELL_TYPE_STRING);
xcel12.setCellStyle(style2);
xcel12.setCellValue(""); workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return new ByteArrayInputStream(out.toByteArray());
}