记录一下jeecgboot框架excel模板导出使用
1.excel模板内容:
2.模板路径:
3.代码改造:
@RequestMapping(value = "/exportXlsCou")
public ModelAndView downloadCou(CemsHour cemsHour,HttpServletRequest request,HttpServletResponse response) throws Exception{
TemplateExportParams params = new TemplateExportParams(
"D:\\exportTemplate\\CEMS排放量.xls");
//原路径 exportTemplate/CEMS排放量.xls
CemsHour cemsHour2 = new CemsHour();
if(StringUtils.isNotEmpty(cemsHour.getDeviceNum())) {
cemsHour2.setDeviceNum("*"+cemsHour.getDeviceNum()+"*");
}
if(StringUtils.isNotEmpty(cemsHour.getSiteName())) {
cemsHour2.setSiteName("*"+cemsHour.getSiteName()+"*");
}
String dataTime ;
if(cemsHour.getDataTime() == null) {
dataTime = sdf.format(new Date()).substring(0, 10);
System.out.println(dataTime);
}else {
dataTime = sdf.format(cemsHour.getDataTime()).substring(0, 10);
System.out.println(dataTime);
}
// Step.1 组装查询条件
QueryWrapper<CemsHour> queryWrapper = new QueryWrapper<>();
String column = camelToUnderline(request.getParameter("column"));
String order = request.getParameter("order");
if (order.equals("asc")) {
queryWrapper = QueryGenerator.initQueryWrapper(cemsHour2, request.getParameterMap()).orderByAsc(column).likeRight("data_time", dataTime);
} else if (order.equals("desc")) {
queryWrapper = QueryGenerator.initQueryWrapper(cemsHour2, request.getParameterMap()).orderByDesc(column).likeRight("data_time", dataTime);
} else {
queryWrapper = QueryGenerator.initQueryWrapper(cemsHour2, request.getParameterMap()).likeRight("data_time", dataTime);
}
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
// Step.2 获取导出数据
List<CemsHour> pageList = service.list(queryWrapper);
List<CemsHour> exportList = null;
// 过滤选中数据
String selections = request.getParameter("selections");
if (oConvertUtils.isNotEmpty(selections)) {
List<String> selectionList = Arrays.asList(selections.split(","));
exportList = pageList.stream().filter(item -> selectionList.contains(getId(item))).collect(Collectors.toList());
} else {
exportList = pageList;
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("siteName", pageList.get(0).getSiteName());
map.put("userName", sysUser.getUsername());
// map.put("upperMoney", "贰佰万");
// map.put("company", "执笔潜行科技有限公司");
// map.put("bureau", "财政局");
// map.put("person", "JueYue");
// map.put("phone", "1879740****");
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
for (int i = 0; i < exportList.size(); i++) {
Map<String, String> lm = new HashMap<String, String>();
lm.put("num", i + 1 + "");
lm.put("dataTime", sdf2.format(exportList.get(i).getDataTime()));
lm.put("deviceNum", exportList.get(i).getDeviceNum());
lm.put("siteName", exportList.get(i).getSiteName());
lm.put("pmValue", exportList.get(i).getPmValue());
lm.put("pmZsvalue", exportList.get(i).getPmZsvalue());
lm.put("pmCou", exportList.get(i).getPmCou());
lm.put("so2Value", exportList.get(i).getSo2Value());
lm.put("so2Zsvalue", exportList.get(i).getSo2Zsvalue());
lm.put("so2Cou", exportList.get(i).getSo2Cou());
lm.put("noxValue", exportList.get(i).getNoxValue());
lm.put("noxZsvalue", exportList.get(i).getNoxZsvalue());
lm.put("noxCou", exportList.get(i).getNoxCou());
lm.put("wasteGasCou", exportList.get(i).getWasteGasCou());
lm.put("oxygenValue", exportList.get(i).getOxygenValue());
lm.put("flueGasTemperatureValue", exportList.get(i).getFlueGasTemperatureValue());
lm.put("moistureContentValue", exportList.get(i).getMoistureContentValue());
lm.put("gasPressureValue", exportList.get(i).getGasPressureValue());
listMap.add(lm);
}
map.put("maplist", listMap);
String TimeNow = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SS").format(Calendar.getInstance().getTime());
// double hourPmCou = 0;
// double hourSo2Cou = 0;
// double hourNoxCou = 0;
if(dataTime.substring(0, 7).equals(TimeNow.substring(0, 7))) {
String tableName = "cems_hour";
CemsHour min = cemsHourService.hourMin(cemsHour.getDeviceNum(), dataTime);
CemsHour avg = cemsHourService.hourAvg(cemsHour.getDeviceNum(), dataTime);
CemsHour max = cemsHourService.hourMax(cemsHour.getDeviceNum(), dataTime);
CemsHour sum = cemsHourService.hourSum(cemsHour.getDeviceNum(), dataTime);
CemsHour cou = cemsHourService.hourCouTotalOneDay(tableName, dataTime);
if(min == null) {
min = new CemsHour();
}
if(avg == null) {
avg = new CemsHour();
}
if(max == null) {
max = new CemsHour();
}
if(sum == null) {
sum = new CemsHour();
}else {
if(Double.valueOf(sum.getPmCou()) == 0) {
sum.setPmCou("-");
}
if(Double.valueOf(sum.getSo2Cou()) == 0) {
sum.setSo2Cou("-");
}
if(Double.valueOf(sum.getNoxCou()) == 0) {
sum.setNoxCou("-");
}
}
if(cou == null) {
cou = new CemsHour();
}else {
if(isNumber(cou.getPmCou())) {
// hourPmCou = Double.valueOf(cou.getPmCou());
cou.setPmCou(cou.getPmCou());
}else {
// hourPmCou = 0;
cou.setPmCou("0");
}
if(isNumber(cou.getSo2Cou())) {
// hourSo2Cou = Double.valueOf(cou.getSo2Cou());
cou.setSo2Cou(cou.getSo2Cou());
}else {
// hourSo2Cou = 0;
cou.setSo2Cou("0");
}
if(isNumber(cou.getNoxCou())) {
// hourNoxCou = Double.valueOf(cou.getNoxCou());
cou.setNoxCou(cou.getNoxCou());
}else {
// hourNoxCou = 0;
cou.setNoxCou("0");
}
}
map.put("min", min);
map.put("avg", avg);
map.put("max", max);
map.put("sum", sum);
map.put("cou", cou);
map.put("all", Double.valueOf(cou.getPmCou())+Double.valueOf(cou.getSo2Cou())+Double.valueOf(cou.getNoxCou()));
}else {
String tableName = "cems_hour"+dataTime.substring(0, 4)+ Integer.valueOf(dataTime.substring(6, 7)) ;
CemsHour min = cemsHourService.hourMinByDate(tableName,cemsHour.getDeviceNum(), dataTime);
CemsHour avg = cemsHourService.hourAvgByDate(tableName,cemsHour.getDeviceNum(), dataTime);
CemsHour max = cemsHourService.hourMaxByDate(tableName,cemsHour.getDeviceNum(), dataTime);
CemsHour sum = cemsHourService.hourSumByDate(tableName,cemsHour.getDeviceNum(), dataTime);
CemsHour cou = cemsHourService.hourCouTotalOneDay(tableName, dataTime);
if(min == null) {
min = new CemsHour();
}
if(avg == null) {
avg = new CemsHour();
}
if(max == null) {
max = new CemsHour();
}
if(sum == null) {
sum = new CemsHour();
}else {
if(Double.valueOf(sum.getPmCou()) == 0) {
sum.setPmCou("-");
}
if(Double.valueOf(sum.getSo2Cou()) == 0) {
sum.setSo2Cou("-");
}
if(Double.valueOf(sum.getNoxCou()) == 0) {
sum.setNoxCou("-");
}
}
if(cou == null) {
cou = new CemsHour();
}else {
if(isNumber(cou.getPmCou())) {
// hourPmCou = Double.valueOf(cou.getPmCou());
cou.setPmCou(cou.getPmCou());
}else {
// hourPmCou = 0;
cou.setPmCou("0");
}
if(isNumber(cou.getSo2Cou())) {
// hourSo2Cou = Double.valueOf(cou.getSo2Cou());
cou.setSo2Cou(cou.getSo2Cou());
}else {
// hourSo2Cou = 0;
cou.setSo2Cou("0");
}
if(isNumber(cou.getNoxCou())) {
// hourNoxCou = Double.valueOf(cou.getNoxCou());
cou.setNoxCou(cou.getNoxCou());
}else {
// hourNoxCou = 0;
cou.setNoxCou("0");
}
}
map.put("min", min);
map.put("avg", avg);
map.put("max", max);
map.put("sum", sum);
map.put("cou", cou);
map.put("all", Double.valueOf(cou.getPmCou())+Double.valueOf(cou.getSo2Cou())+Double.valueOf(cou.getNoxCou()));
}
// Workbook workbook = ExcelExportUtil.exportExcel(params, map);
// File savefile = new File("D:/home/excel/");
// if (!savefile.exists()) {
// savefile.mkdirs();
// }
// FileOutputStream fos = new FileOutputStream("D:/home/excel/CEMS时均数据.xls");
// workbook.write(fos);
// fos.close();
// Step.3 AutoPoi 导出Excel
ModelAndView mv = new ModelAndView(new JeecgTemplateExcelView());
mv.addObject(TemplateExcelConstants.FILE_NAME, "CEMS排放量数据"); //此处设置的filename无效 ,前端会重更新设置一下
mv.addObject(TemplateExcelConstants.PARAMS, params);//参数
mv.addObject(TemplateExcelConstants.MAP_DATA, map);
return mv;
}
4.效果:
5.打包部署以后发现找不到src下的模板路径,所以把模板放在D盘下了,代码中路径改成D盘下的模板路径,完成!!!