package cn.gistack.sm.sjztmd.word.util;
|
|
import ch.qos.logback.core.pattern.FormatInfo;
|
import cn.gistack.sm.sjztmd.vo.StationExport;
|
import cn.gistack.sm.sjztmd.word.vo.DzkInfo;
|
import cn.gistack.sm.sjztmd.word.vo.OverDetail;
|
import cn.gistack.sm.sjztmd.word.vo.TotalInfo;
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.support.ExcelTypeEnum;
|
import com.alibaba.excel.util.FileUtils;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.alibaba.excel.write.metadata.fill.FillWrapper;
|
import org.springblade.core.tool.utils.StringUtil;
|
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
/**
|
* excel工具类
|
*/
|
public class ExcelUtil {
|
|
|
public static void generateExcel(HttpServletResponse response, List<TotalInfo> totalList, List<DzkInfo> dzkList, List<OverDetail> overDetailList) throws Exception {
|
|
String tm = new SimpleDateFormat("MM月dd日H时").format(new Date());
|
HashMap<String,String> map = new HashMap<>();
|
map.put("time",tm);
|
|
HashMap<String, Object> info = formatInfo(totalList, dzkList, overDetailList);
|
info.put("time",tm);
|
|
//获取模板(带公式的模板需要文件编辑之后才会显示正常数据)
|
InputStream inputStream = ExcelUtil.class.getClassLoader().getResourceAsStream("excel/xlsx/totalTemplateNoFormula.xlsx");
|
String time = new SimpleDateFormat("yyyy日MM月dd日HH时").format(new Date());
|
String fileName = time+ "报汛统计.xlsx";
|
// String fileName = StringUtil.format("水库超汛限水位情况计表（{}）.xlsx",tm);
|
ExcelWriter excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build();
|
//设置公式生效
|
// excelWriter.writeContext().writeWorkbookHolder().getWorkbook().setForceFormulaRecalculation(true);
|
WriteSheet writeSheet1 = EasyExcel.writerSheet(0,"总表").build();
|
WriteSheet writeSheet2 = EasyExcel.writerSheet(1,"大中库").build();
|
WriteSheet writeSheet3 = EasyExcel.writerSheet(2,"明细").build();
|
|
|
|
// 这个传进去的 map 就是非表格数据，详见我定义的模板中图片位置上方那部分
|
excelWriter.fill(info, writeSheet1);
|
excelWriter.fill(new FillWrapper("data", totalList), writeSheet1);
|
|
excelWriter.fill(map, writeSheet2);
|
excelWriter.fill(new FillWrapper("data", dzkList), writeSheet2);
|
|
excelWriter.fill(map, writeSheet3);
|
excelWriter.fill(new FillWrapper("data", overDetailList), writeSheet3);
|
excelWriter.finish();
|
}
|
|
private static HashMap<String,Object> formatInfo(List<TotalInfo> totalList, List<DzkInfo> dzkList, List<OverDetail> overDetailList) {
|
HashMap<String,Object> info = new HashMap<>();
|
|
int overTotal = 0;
|
int overBig = 0;
|
int overMid = 0;
|
int overSmall = 0;
|
|
int wzTotal = 0;
|
int wzBig = 0;
|
int wzMid = 0;
|
int wzSmall = 0;
|
|
int yzTotal=0;
|
int yzBig = 0;
|
int yzMid = 0;
|
int yzSmall = 0;
|
|
int dangerTotal = 0;
|
int dangerBig = 0;
|
int dangerMid = 0;
|
int dangerSmall = 0;
|
|
int overDangerTotal = 0;
|
int overDangerBig = 0;
|
int overDangerMid = 0;
|
int overDangerSmall = 0;
|
|
//给总览加序号
|
for (int i = 0; i < totalList.size(); i++) {
|
|
TotalInfo totalInfo = totalList.get(i);
|
|
totalInfo.setIndex(String.valueOf(i+1));
|
|
overTotal+=isNull(totalInfo.getCount_all());
|
overBig +=isNull(totalInfo.getBig_all());
|
overMid +=isNull(totalInfo.getMid_all());
|
overSmall +=isNull(totalInfo.getSmall_all());
|
|
wzTotal +=isNull(totalInfo.getWz_count_all());
|
wzBig+=isNull(totalInfo.getWz_big());
|
wzMid+=isNull(totalInfo.getWz_mid());
|
wzSmall+=isNull(totalInfo.getWz_small());
|
|
yzTotal+=isNull(totalInfo.getYz_count_all());
|
yzBig+=isNull(totalInfo.getYz_big());
|
yzMid +=isNull(totalInfo.getYz_mid());
|
yzSmall += isNull(totalInfo.getYz_small());
|
|
dangerTotal +=isNull(totalInfo.getDanger_count_all());
|
dangerBig +=isNull(totalInfo.getDanger_big_all());
|
dangerMid += isNull(totalInfo.getDanger_mid_all());
|
dangerSmall += isNull(totalInfo.getDanger_small_all());
|
|
overDangerTotal +=isNull(totalInfo.getOver_danger_count_all());
|
overDangerBig += isNull(totalInfo.getOver_danger_big_all());
|
overDangerMid += isNull(totalInfo.getOver_danger_mid_all());
|
overDangerSmall += isNull(totalInfo.getOver_danger_small_all());
|
}
|
info.put("overTotal", overTotal);
|
info.put("overBig", overBig);
|
info.put("overMid", overMid);
|
info.put("overSmall", overSmall);
|
|
info.put("wzTotal", wzTotal);
|
info.put("wzBig", wzBig);
|
info.put("wzMid", wzMid);
|
info.put("wzSmall", wzSmall);
|
|
info.put("yzTotal", yzTotal);
|
info.put("yzBig", yzBig);
|
info.put("yzMid", yzMid);
|
info.put("yzSmall", yzSmall);
|
|
info.put("dangerTotal", dangerTotal);
|
info.put("dangerBig", dangerBig);
|
info.put("dangerMid", dangerMid);
|
info.put("dangerSmall", dangerSmall);
|
|
info.put("overDangerTotal", overDangerTotal);
|
info.put("overDangerBig", overDangerBig);
|
info.put("overDangerMid", overDangerMid);
|
info.put("overDangerSmall", overDangerSmall);
|
|
//给大中库加序号、是否病险转换
|
for (int i = 0; i < dzkList.size(); i++) {
|
dzkList.get(i).setIndex(String.valueOf(i+1));
|
if (dzkList.get(i).getIs_danger() != null){
|
if (dzkList.get(i).getIs_danger()){
|
dzkList.get(i).setIs_dangerText("1");
|
}else if (!dzkList.get(i).getIs_danger()){
|
dzkList.get(i).setIs_dangerText("0");
|
}
|
}
|
}
|
|
//给明细是否病险转换
|
for (int i = 0; i < overDetailList.size(); i++) {
|
if (overDetailList.get(i).getIs_danger() != null){
|
if (overDetailList.get(i).getIs_danger()){
|
overDetailList.get(i).setIs_dangerText("1");
|
}else if (!overDetailList.get(i).getIs_danger()){
|
overDetailList.get(i).setIs_dangerText("0");
|
}
|
}
|
}
|
|
return info;
|
}
|
|
/**
|
* 导出文件时为Writer生成OutputStream.
|
*
|
* @param fileName 文件名
|
* @param response response
|
* @return ""
|
*/
|
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
|
try {
|
fileName = URLEncoder.encode(fileName, "UTF-8");
|
response.setContentType("application/vnd.ms-excel");
|
response.setCharacterEncoding("utf8");
|
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
|
return response.getOutputStream();
|
} catch (IOException e) {
|
throw new Exception("导出excel表格失败!", e);
|
}
|
}
|
|
public static Integer isNull(Integer num){
|
if (num == null){
|
return 0;
|
}else {
|
return num;
|
}
|
}
|
|
public static void createStationExcel(HttpServletResponse response,String title, String fileName, List<StationExport> list ,String templatePath) throws Exception {
|
|
HashMap<String,String> map = new HashMap<>();
|
map.put("title",title);
|
|
InputStream inputStream = ExcelUtil.class.getClassLoader().getResourceAsStream(templatePath);
|
ExcelWriter excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build();
|
WriteSheet writeSheet1 = EasyExcel.writerSheet().build();
|
|
// 这个传进去的 map 就是非表格数据，
|
excelWriter.fill(map, writeSheet1);
|
excelWriter.fill(new FillWrapper("data", list), writeSheet1);
|
|
excelWriter.finish();
|
}
|
}
|
