1. 添加依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>
2.表头(含单元格合并)
public List<List<String>> ExcelHead() {List<List<String>> headList = new ArrayList();headList.add(new ArrayList() {{add("时间");add("参数名\\回路名");}});headList.add(new ArrayList() {{add("时间" }});headList.add(new ArrayList() {{add("时间");add("电流I(A)");}});headList.add(new ArrayList() {{add("环境温度：" + temperature);add("相电压U");}});headList.add(new ArrayList() {{add("环境温度");add("线电压U");}});headList.add(new ArrayList() {{add("环境温度");add("频率(Hz)");}});headList.add(new ArrayList() {{add("环境湿度");add("开关分合(分\\合)");}});headList.add(new ArrayList() {{add("环境湿度");add("总有功功率(kW)");}});headList.add(new ArrayList() {{add("抄表记录单");add("总无功功率(kVar)");}});headList.add(new ArrayList() {{add("抄表记录单");add("总功率因数");}});headList.add(new ArrayList() {{add("抄表记录单");add("正向有功电能(kW.h)");}});headList.add(new ArrayList() {{add("项目");add("正向无功电能(kVar.h)");}});headList.add(new ArrayList() {{add("表单号");add("所属变压器");}});headList.add(new ArrayList() {{add("表单号");add("变压器温度(℃)");}});return headList;}
3. 数据逻辑
List<HashMap> data = statisticmapper.runreport(statisticdto);for (int i = 0; i < data.size(); i++) {String id = data.get(i).get("id").toString();String name = data.get(i).get("equipment_name").toString();if (data.get(i).get("paramid") == null) continue;String paramid = data.get(i).get("paramid").toString();String point_id = data.get(i).get("point_id").toString();String value = data.get(i).get("value").toString();switch (paramid) {case "e2fa092c-6146-11eb-82f0-6c2b59bec1ff":temperature = value + "℃ ";continue;case "70536171-6147-11eb-82f0-6c2b59bec1ff":humidity = value + "%";continue;}if (!devicecheck.contains(id)) {tosave.add(rowdata(name, "A相"));tosave.add(rowdata(name, "B相"));tosave.add(rowdata(name, "C相"));devicecheck.add(id);}if (dic.containsKey(id + point_id)) continue;dic.put(id + point_id, i);int index = devicecheck.indexOf(id) * 3;switch (paramid) {case "069d9c73-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(5, value);tosave.get(index + 1).set(5, value);tosave.get(index + 2).set(5, value);break;case "068d17d6-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(7, value);tosave.get(index + 1).set(7, value);tosave.get(index + 2).set(7, value);break;case "071ce9e3-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(8, value);tosave.get(index + 1).set(8, value);tosave.get(index + 2).set(8, value);break;case "05b23645-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(9, value);tosave.get(index + 1).set(9, value);tosave.get(index + 2).set(9, value);break;case "062ee255-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(10, value);tosave.get(index + 1).set(10, value);tosave.get(index + 2).set(10, value);break;case "06dd3edb-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(11, value);tosave.get(index + 1).set(11, value);tosave.get(index + 2).set(11, value);break;case "061fa440-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(2, value);break;case "06fa867e-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(3, value + "kV");break;case "05952cc2-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index).set(4, "Uab(" + value + "kV)");break;case "06805737-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 1).set(2, value);break;case "05f1ca1b-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 1).set(3, value + "kV");break;case "06ab9dbf-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 1).set(4, "Uab(" + value + "kV)");break;case "0604e966-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 2).set(2, value);break;case "058946a7-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 2).set(3, value + "kV");break;case "06b858c6-6148-11eb-82f0-6c2b59bec1ff":tosave.get(index + 2).set(4, "Uab(" + value + "kV)");break;}}
public List<String> rowdata(String name, String type) {return new ArrayList() {{add(name);add(type);add("-");add("-");add("-");add("-");add("-");add("-");add("-");add("-");add("-");add("-");add("无");add("-");}};}
3.excel
List<List<String>> head = ExcelHead();EasyExcel.write(fileUrl).head(head).sheet(sheetname)//.registerWriteHandler(StyleStrategy())//.registerWriteHandler(new ReportMergeStrategy(devicecheck.size()))//自定义合并 单元格//.registerWriteHandler(new RowWriteHandler()).doWrite(tosave);
4. 结果
