根据条件查询出结果,然后导出Excell报表,代码如下(控制层框架vert.x,持久层框架:beetlsql):
/**
* PC后台用户管理导出报表
* @param context
* @param userExtend
*/
public void exportUserExcell(RoutingContext context, UserExtend userExtend) throws IOException {
// 设置导出文件名
String fileName = "用户信息";
// 根据条件查询用户信息
String registEndTime = userExtend.getRegistEndTime();
if (StrUtil.isNotBlank(registEndTime)){
userExtend.setRegistEndTime(registEndTime+" 23:59:59");
}
String upEndTime = userExtend.getUpEndTime();
if (StrUtil.isNotBlank(upEndTime)){
userExtend.setUpEndTime(upEndTime+" 23:59:59");
}
List<UserExtend> list = sqlManager.select("sysUser.queryManager", UserExtend.class, userExtend);
for (UserExtend extend : list) {
// 处理所在地
String contactProvince = extend.getContactProvince();
String contactCity = extend.getContactCity();
if(StrUtil.isNotBlank(contactProvince)&&StrUtil.isNotBlank(contactCity)){
List<String> toString = AddressUtil.addressCodeToString(contactProvince, contactCity);
extend.setProvinceName(toString.get(0));
extend.setCityName(toString.get(1));
// 设置所在省市
extend.setRealAddress(toString.get(0)+toString.get(1));
}
// 处理推荐人信息
String recommendId = extend.getRecommendId();
if (StrUtil.isNotBlank(recommendId)){
// 根据推荐人信息查询推荐人
SysUser user = new SysUser();
user.setId(recommendId);
List<UserExtend> select = sqlManager.select("sysUser.queryUser", UserExtend.class, user);
UserExtend single = new UserExtend();
if (null !=select && select.size()>0){
single = select.get(0);
}
extend.setRecommendName(single.getName());
extend.setRecommendMobile(single.getMobile());
extend.setRecommendRoleName(single.getRoleName());
extend.setRecommendNickName(single.getNickName());
}
}
List<Map<String,Object>> listMap = createUserExcelRecord(list);
// 定义key
String[] keys = {"nickName","mobile","name","sex","realAddress","createTime","roleName","limitTime","recommendNickName","recommendRoleName","recommendMobile","upTime"};
// 定义列名
String[] columnNames={"昵称","手机","真实姓名","性别","所在地","注册时间","用户等级","会员有效期","推荐人昵称","推荐人等级","推荐人手机号","升级时间"};
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelUtil.createWorkBook(fileName, listMap, keys, columnNames).write(os);
byte[] content = os.toByteArray();
Buffer buffer = Buffer.buffer();
buffer.appendBytes(content);
// 设置response参数,可以打开下载页面
context.response().putHeader("content-type", "application/octet-stream;charset=UTF-8");
context.response().putHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
context.response().putHeader("Pargam", "no-cache");
context.response().putHeader("Cache-Control", "no-cache");
context.response().end(buffer);
}
private List<Map<String,Object>> createUserExcelRecord(List<UserExtend> datalist) {
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
UserExtend bean;
for (int i = 0; i < datalist.size(); i++) {
bean = datalist.get(i);
Map<String, Object> map = new HashMap<String, Object>();
map.put("nickName", bean.getNickName());
map.put("mobile", bean.getMobile());
map.put("name",bean.getName());
String sex = bean.getSex();
if(sex.equals("0")){
map.put("sex","女");
}else if (sex.equals("1")){
map.put("sex","男");
}
map.put("realAddress",bean.getRealAddress());
map.put("roleName",bean.getRoleName());
map.put("recommendNickName",bean.getRecommendNickName());
map.put("recommendRoleName",bean.getRecommendRoleName());
map.put("recommendMobile", bean.getRecommendMobile());
// 注册时间
Date createTime = bean.getCreateTime();
String format = DateUtil.format(createTime, "yyyy/MM/dd HH:mm");
map.put("createTime", format);
// 升级时间
Date upTime = bean.getUpTime();
String up = DateUtil.format(upTime, "yyyy/MM/dd HH:mm");
if(StrUtil.isBlank(up)){
map.put("upTime","未升级");
}else {
map.put("upTime",up);
}
// 会员有效期
Date limitTime = bean.getLimitTime();
String limit = DateUtil.format(limitTime, "yyyy/MM/dd HH:mm");
if (StrUtil.isBlank(limit)){
map.put("limitTime","无");
}else {
map.put("limitTime",limit);
}
listMap.add(map);
}
return listMap;
}
导出报表的工具类:
package org.silk.Util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
/**
* 导出Excel文档工具类
* @author xc
* @date 2018.01.06
*/
public static Workbook createWorkBook(String filename, List<Map<String,Object>> listMap, String[] keys, String[] columnNames){
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet("sheet1");
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for(int i=0;i<keys.length;i++){
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
// 设置列名
for (int i=0;i<columnNames.length;i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
// 设置每行每列的值
for (int i = 0; i < listMap.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow((short) (i+1));
// 在row行上创建一个方格
for(short j=0;j<columnNames.length;j++){
Cell cell = row1.createCell(j);
// cell.setCellValue(listMap.get(i).get(keys[j]) == null?"":listMap.get(i).get(keys[j]).toString());
Object obj = listMap.get(i).get(keys[j];
if (obj == null) {
cell.setCellValue("");
}else{
if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Integer) {
cell.setCellValue((Integer) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
}else {
cell.setCellValue((String) obj);
}
}
cell.setCellStyle(cs2);
}
}
return wb;
}
}