1、基于工具类ExcelUtil的exportExcel(String title, String[] headers, Collection<Map<String, Object>> dataset, OutputStream out);
2、通用方法
/**
* 共用的 导出excel方法
* @param fileName excel文件名
* @param headList 第一行内容
* @param fieldDataList 数据块
* @param response
* @throws Exception
*/
private void exportExcel(String fileName, List<String> headList, List<Map<String, Object>> fieldDataList,
HttpServletResponse response) throws Exception {
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition",
"attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8") + ";");
OutputStream out = response.getOutputStream();
ExcelUtil<List<Map<String, Object>>> excelUtil = new ExcelUtil<>();
String[] headers = new String[headList.size()];
headList.toArray(headers);
excelUtil.exportExcel(fileName, headers, fieldDataList, out);
out.close();
}
3、
/**
* 获取2个日期间 的 所有月份 集合 ,比如 ["2018-01", "2018-02", ……]
* @param startDate
* @param endDate
* @return
*/
public static List<String> getMonthBetween(String startDate, String endDate) throws ParseException {
ArrayList<String> result = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");//格式化为年月
Calendar min = Calendar.getInstance();
Calendar max = Calendar.getInstance();
min.setTime(sdf.parse(startDate));
min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);
max.setTime(sdf.parse(endDate));
max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);
Calendar curr = min;
while (curr.before(max)) {
result.add(sdf.format(curr.getTime()));
curr.add(Calendar.MONTH, 1);
}
return result;
}
public static List<String> convertToEnglishMonth(List<String> months) {
List<String> englishMonths = new ArrayList<>();
for(String month : months) {
englishMonths.add(getEnglishMonth(month));
}
return englishMonths;
}
/**
*
* @param month 2018-03 -> Mar-18
* @return
*/
public static String getEnglishMonth(String month) {
String[] yearAndMonth = month.split("-");
StringBuilder sb = new StringBuilder();
switch (yearAndMonth[1]) {
case "01" : {
sb.append("Jan");
break;
}
case "02" : {
sb.append("Feb");
break;
}
case "03" : {
sb.append("Mar");
break;
}
case "04" : {
sb.append("Apr");
break;
}
case "05" : {
sb.append("May");
break;
}
case "06" : {
sb.append("Jun");
break;
}
case "07" : {
sb.append("Jul");
break;
}
case "08" : {
sb.append("Aug");
break;
}
case "09" : {
sb.append("Sep");
break;
}
case "10" : {
sb.append("Oct");
break;
}
case "11" : {
sb.append("Nov");
break;
}
case "12" : {
sb.append("Dec");
break;
}
default: {}
}
sb.append("-").append(yearAndMonth[0].substring(2));
return sb.toString();
}
/**
* 把日期转化为英文,并返回(英文日期)headList
* @param monthList ["2018-04", ……]
* @param firstCellName 第一格内容
* @return [" ", "Apr-18", ……]
* @throws ParseException
*/
private List<String> getHeadEngMonthList(String firstCellName, List<String> monthList, String lastCellName) throws ParseException {
// 头headList
List<String> engMonthList = DateUtil.convertToEnglishMonth(monthList);
List<String> engHeadList = new ArrayList<>();
if(StringUtils.isNotEmpty(firstCellName)) {
engHeadList.add(firstCellName);
}
engHeadList.addAll(engMonthList);
if(StringUtils.isNotEmpty(lastCellName)) {
engHeadList.add(lastCellName);
}
return engHeadList;
}
4、
举例:
基于现有的表结构:这2张表都有user_id、bind_status字段。
需求:当月内,客户既有绑定保单状态,又有未绑定保单状态,则一律算作保单用户。