POI+EL表达式Excel导出功能
在做项⽬的过程中，⽤Excel来导⼊导出数据是很常见的事情，这次负责的项⽬就有这么⼀个功能，⽤户想要的需求就是根据不同的查询条件来实现不同的导出效果，已便于读取与保存。
界⾯的效果如下：
在界⾯上可以看到有很多的查询条件，实现的⽬标就是我点击搜索出现什么信息，那么你就必须得导出什么信息，其实蕴含的意思就是前期⽅向，搜索和导出Excel实现的功能是⼀样的。那么在项⽬中我们是如何应⽤的呢？
⾸先也是分层开发，从页⾯-Controller-Service-Dao层，其实思路很简单，我们主要就是通过El表达式，
通过Form窗体提交的⽅式将其赋值到导出Excel的按钮中，代码如下：
JSP层页⾯显⽰部分代码：
<label>订单号</label>
<input type="text" name="<span >search_LIKE_orderNo</span>" class="input-medium" maxlength="50"
value="<span >${param.search_LIKE_orderNo}</span>">
<label>客户姓名</label>
<input type="text" name="search_LIKE_member.nick" class="input-medium"
maxlength="20"
value="${nick}">
<label>⼿机号</label>
<input type="text" name="search_bile" class="input-medium" maxlength="11"
value="${mobile}">
（只选择了三个简单的控件来表⽰）
Controller层代码：
通过分页查询的⽅式，将JSP页⾯的关于search_⽅式的值触发显⽰：
@RequestMapping(value = "exportExcel")
public void exportExcel( //分页查询的⼀些参数
@RequestParam(value = "sortOrder", defaultValue = "desc") String sortOrder,
@RequestParam(value = "sortName", defaultValue = "createDate") String sortName,
@RequestParam(value = "page", defaultValue = "1") int page,
@RequestParam(value = "pagesize", defaultValue = "50") int pageSize,
@RequestParam(value = "messageTip", defaultValue = "") String messageTip,
HttpServletRequest request, HttpServletResponse response,
Model model) throws UnsupportedEncodingException {  //抛异常处理
response.setContentType("application/vnd.ms-excel");
String dateFile = DateUtil.formatDate(new Date());
//导出Excel名称显⽰
String codedFileName = new String(URLDecoder.decode("交易明细列表-", "UTF-8").getBytes("gb2312"), "ISO8859-1") + dateFile;
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
// 产⽣⼯作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
//产⽣⼯作表对象
//产⽣⼯作表对象
HSSFSheet sheet = ateSheet();
<span > Map<String, Object> searchParams = ParametersStartingWith(request, "search_");    //获取jsp页⾯search_的⼀些触        //对于时间的判断
String createDate = (String) ("EQ_createDate");
if (createDate != null) {
searchParams.put("GTE_createDate", DateUtil.im() + " 00:00:00"));
searchParams.put("LTE_createDate", DateUtil.im() + " 23:59:59"));
}
//获取分页查询的列表
Page<BankTransfer> transfers = bankTransferService.list(searchParams, page, Integer.MAX_VALU
E, sortName, sortOrder);
model.addAttribute("transfers", transfers);
int count = 0;
if (transfers != null && Content() != null && Content().size() > 0) {
//列名
List<BankTransfer> transferList = Content();
HSSFRow row0 = ateRow(0);//创建⼀⾏
HSSFCell cell0 = ateCell(0);//创建⼀列
cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
cell0.setCellValue("序号");
HSSFCell cell1 = ateCell(1);//创建⼀列
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setCellValue("交易单号");
HSSFCell cell2 = ateCell(2);//创建⼀列
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setCellValue("客户姓名");
……
HSSFCell cell10 = ateCell(10);//创建⼀列
cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
cell10.setCellValue("交易备注");
<span > </span>    //赋值处理
for (BankTransfer transfer : transferList) {
String orderNo = OrderNo();//交易单号
/*String nick = Member().getNick();//客户姓名
String mobile = Member().getMobile();//客户⼿机号*/
Double money;
money = Money();//交易⾦额
/*对money进⾏⼀下为空判断*/
if (money == null) {
money = 0.0D;
}
Date time = CreateDate();//交易时间
String payType = PayType();  //交易类型
/
*2：账单缴费，3：商城,  4：转账  5：退款*/
String type = "";
if (payType.equals("2")) {
type = "账单缴费";
} else if (payType.equals("3")) {
type = "商城";
} else if (payType.equals("4")) {
type = "转账";
} else if (payType.equals("5")) {
type = "退款";
}
String statee = Status();//交易状态
/*将数字转换成对应的汉字来显⽰到Excel中*/
String states = "";
if (statee.equals("0")) {
states = "转账中";
states = "转账中";
} else if (statee.equals("1")) {
states = "成功";
} else if (statee.equals("2")) {
states = "失败";
} else if (statee.equals("3")) {
states = "交易单号不存在";
}
String description = Description();//交易备注
String nick = "";
String mobile = "";
List<BankTransfer> TransferListItems = bankTransferService.Member().getId());                if (TransferListItems != null && TransferListItems.size() > 0) {
for (BankTransfer TransferListItem : TransferListItems) {
Member bankTransfer = Member();//客户信息
nick = Nick();//客户姓名
mobile = Mobile();//客户⼿机号
}
}
String shopname = "";
List<BankTransfer> bankTransfers = bankTransferService.ShopId());
if (bankTransfers != null && bankTransfers.size() > 0) {
for (BankTransfer bankTransfer : bankTransfers) {
if (StringUtils.ShopId())) {
Shop bankTransferShop = shopService.ShopId());
shopname = Name();    //获取商家名称
}
}
}
String subdistrictname = "";
List<BankTransfer> bankTransfers1 = bankTransferService.SubdistrictId());                if (bankTransfers1 != null && bankTransfers1.size() > 0) {
for (BankTransfer bankTransfer : bankTransfers1) {
if (StringUtils.SubdistrictId())) {
Subdistrict bankTransferSubdistrict = subdistrictService.SubdistrictId());
subdistrictname = Name();
el表达式获取值
}
}
}
count++;//序号
HSSFRow row = ateRow(count);//创建⼀⾏
HSSFCell cell00 = ateCell(0);//创建⼀列
cell00.setCellType(HSSFCell.CELL_TYPE_STRING);
cell00.setCellValue("" + count);
HSSFCell cell01 = ateCell(1);//创建⼀列
cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
cell01.setCellValue(orderNo);
…………
HSSFCell cell11 = ateCell(10);//创建⼀列
cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
cell11.setCellValue(description);
}
}
OutputStream os = null;
try {
os = OutputStream();
workbook.write(os);
workbook.write(os);
} catch (Exception e) {
<("", e);
} finally {      //进⾏判断
try {
if (os != null) {
os.flush();
os.close();
}
} catch (IOException e) {
<("", e);
}
}
}
对于Service，只来显⽰⼀个List，分页查询的⽅法，其他的都是简单的⼀些传值，调⽤
/
分页list⽅法
public Page<BankTransfer> list(Map<String, Object> searchParams, int page, int pageSize, String sortName, String sortOrder) {
PageRequest pageRequest = buildPageRequest(page, pageSize, sortName, sortOrder);
Specification<BankTransfer> spec = buildSpecification(searchParams);
return bankTransferDao.findAll(spec, pageRequest);
}
/**
* 创建分页请求.
*/
private PageRequest buildPageRequest(int pageNumber, int pagzSize, String sortName, String sortOrder) {
Sort sort = null;
if (StringUtils.isBlank(sortOrder)) {
sortOrder = "desc";
}
if (StringUtils.isBlank(sortName)) {
sortName = "id";
}
if (StringUtils.equalsIgnoreCase(sortOrder, "desc")) {
sort = new Sort(Sort.Direction.DESC, sortName);
} else {
sort = new Sort(Sort.Direction.ASC, sortName);
}
return new PageRequest(pageNumber - 1, pagzSize, sort);
}
/**
* 创建动态查询条件组合.
*/
private Specification<BankTransfer> buildSpecification(Map<String, Object> searchParams) {
Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
Specification<BankTransfer> spec = DynamicSpecifications.bySearchFilter(filters.values(), BankTransfer.class);
return spec;
}
这样主要代码就完⼯了，其实实现的⽅式很简单，通过EL 表达式的关键字段，⽐如search_EQ,search_LIKE等相等或模糊查询来实现想要的功能。这样我们就完成了我们想要的功能。不管是点击搜索按钮，还是导出Excel按钮，其实实现的效果是⼀样的，只是数值存放的位置发⽣了⼩⼩的变化。
通过El表达式，${},EQ,LIKE等直接⽅便了我们代码的开发，提⾼了开发效率，值得好好总结。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。