package com.moral.api.service.impl;
|
|
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.hssf.usermodel.HSSFRow;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Collections;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
import java.util.stream.Collectors;
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONObject;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.moral.api.entity.HistoryDaily;
|
import com.moral.api.entity.TbExcel;
|
import com.moral.api.mapper.ExcelMapper;
|
import com.moral.api.mapper.HistoryDailyMapper;
|
import com.moral.api.pojo.bo.ExcelBO;
|
|
import com.moral.api.pojo.vo.excel.ExcelVo;
|
import com.moral.api.service.ExcelService;
|
import com.moral.api.utils.ExcelUtils;
|
|
import com.moral.util.DateUtils;
|
|
|
@Service
|
@Slf4j
|
public class ExcelServiceImpl implements ExcelService {
|
|
@Autowired
|
private ExcelMapper excelMapper;
|
|
@Autowired
|
private HistoryDailyMapper historyDailyMapper;
|
|
/**
|
* 高新区导入
|
* @param files
|
* @param params
|
* @return
|
* @throws IOException
|
*/
|
@Override
|
public ExcelBO importTemplate(List<MultipartFile> files, Map<String, Object> params) throws IOException {
|
Date date2 = new Date();
|
String time = (String) params.get("time");
|
String code = (String) params.get("code");
|
// String time1 = (String) params.get("date");
|
Date date = DateUtils.getDate(time, "yyyy-MM-dd");
|
// Date date1 = DateUtils.getDate(time1, "yyyy-MM-dd");
|
ExcelBO excelBO = new ExcelBO();
|
QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
|
wrapper.eq("time",date).eq("code",code);
|
TbExcel excel = excelMapper.selectOne(wrapper);
|
if (excel==null){
|
TbExcel excel1 = new TbExcel();
|
HashMap<String, Object> map = new HashMap<>();
|
MultipartFile file1 = files.get(0);
|
Map<String, Object> map1 = getMap1(file1);
|
map.put("c1",map1);
|
MultipartFile file2 = files.get(1);
|
Map<String, Object> map2 = getMap2(file2);
|
map.put("c2",map2);
|
MultipartFile file3 = files.get(2);
|
Map<String, Object> map3 = getMap3(file3);
|
map.put("c3",map3);
|
MultipartFile file4 = files.get(3);
|
Map<String, Object> map4 = getMap4(file4);
|
map.put("c4",map4);
|
MultipartFile file5 = files.get(4);
|
Map<String, Object> map5 = getMap5(file5);
|
map.put("c5",map5);
|
if (files.size()>5){
|
MultipartFile file6 = files.get(5);
|
Map<String, Object> map6 = getMap6(file6);
|
map.put("c6",map6);
|
}
|
String s = JSON.toJSONString(map);
|
excel1.setValue(s);
|
excel1.setTime(date);
|
excel1.setCode(code);
|
excel1.setDate(date2);
|
excelMapper.insert(excel1);
|
excelBO.setId(excel1.getId());
|
excelBO.setTime(excel1.getTime());
|
excelBO.setDate(date2);
|
|
}else {
|
HashMap<String, Object> map = new HashMap<>();
|
MultipartFile file1 = files.get(0);
|
Map<String, Object> map1 = getMap1(file1);
|
map.put("c1",map1);
|
MultipartFile file2 = files.get(1);
|
Map<String, Object> map2 = getMap2(file2);
|
map.put("c2",map2);
|
MultipartFile file3 = files.get(2);
|
Map<String, Object> map3 = getMap3(file3);
|
map.put("c3",map3);
|
MultipartFile file4 = files.get(3);
|
Map<String, Object> map4 = getMap4(file4);
|
map.put("c4",map4);
|
MultipartFile file5 = files.get(4);
|
Map<String, Object> map5 = getMap5(file5);
|
map.put("c5",map5);
|
if (files.size()>5){
|
MultipartFile file6 = files.get(5);
|
Map<String, Object> map6 = getMap6(file6);
|
map.put("c6",map6);
|
}
|
String s = JSON.toJSONString(map);
|
excel.setValue(s);
|
excelMapper.updateById(excel);
|
excelBO.setId(excel.getId());
|
excelBO.setTime(excel.getTime());
|
excelBO.setDate(date2);
|
}
|
return excelBO;
|
}
|
|
/**
|
* 高新区导出
|
* @param id
|
* @return
|
*/
|
@Override
|
public ExcelVo export(Integer id) {
|
HashMap<String, Object> map1 = new HashMap<>();
|
ArrayList<Map<String,Object>> list1 = new ArrayList<>();
|
ArrayList<Map<String,Object>> list2 = new ArrayList<>();
|
ArrayList<Map<String,Object>> list3 = new ArrayList<>();
|
ArrayList<Map<String,Object>> list4 = new ArrayList<>();
|
ArrayList<Map<String,Object>> list5= new ArrayList<>();
|
ExcelVo excelVo = new ExcelVo();
|
//类型转换
|
TbExcel excel = excelMapper.selectById(id);
|
String value = excel.getValue();
|
|
JSONObject jsonObject = JSON.parseObject(value);
|
Map map4 = JSON.parseObject(value, Map.class);
|
Set sets = map4.keySet();
|
for (Object set : sets) {
|
if (set.equals("c1")){
|
Object o = map4.get(set);
|
String s = JSON.toJSONString(o);
|
Map map5 = JSON.parseObject(s, Map.class);
|
Set set1s = map5.keySet();
|
for (Object set1 : set1s) {
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
list1.add(map6);
|
}
|
}
|
if (set.equals("c2")){
|
Object o = map4.get(set);
|
String s = JSON.toJSONString(o);
|
Map map5 = JSON.parseObject(s, Map.class);
|
Set set1s = map5.keySet();
|
for (Object set1 : set1s) {
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
list2.add(map6);
|
}
|
}
|
if (set.equals("c3")){
|
Object o = map4.get(set);
|
String s = JSON.toJSONString(o);
|
Map map5 = JSON.parseObject(s, Map.class);
|
Set set1s = map5.keySet();
|
for (Object set1 : set1s) {
|
if (set1.equals("高新区")){
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
map6.put("place","国控站");
|
list3.add(map6);
|
}else {
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
map6.put("place",set1.toString().substring(3));
|
list3.add(map6);
|
}
|
}
|
}
|
if (set.equals("c4")){
|
Object o = map4.get(set);
|
String s = JSON.toJSONString(o);
|
Map map5 = JSON.parseObject(s, Map.class);
|
Set set1s = map5.keySet();
|
for (Object set1 : set1s) {
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
String o2 = String.valueOf(map6.get("PM2_5m"));
|
|
String place = String.valueOf(map6.get("place"));
|
String o3m = String.valueOf(map6.get("O3m"));
|
String aqIm = String.valueOf(map6.get("AQIm")) ;
|
String o2s =String.valueOf(map6.get("PM2_5y"));
|
String O3ms = String.valueOf(map6.get("O3y")) ;
|
String aqIms = String.valueOf(map6.get("AQIy"));
|
String result = o2+"、"+o3m+"、"+aqIm+"、"+o2s+"、"+O3ms+"、"+aqIms;
|
map1.put(place,result);
|
list4.add(map6);
|
}
|
}
|
if (set.equals("c5")){
|
Object o = map4.get(set);
|
String s = JSON.toJSONString(o);
|
Map map5 = JSON.parseObject(s, Map.class);
|
Set set1s = map5.keySet();
|
for (Object set1 : set1s) {
|
Object o1 = map5.get(set1);
|
String s1 = JSON.toJSONString(o1);
|
Map map6 = JSON.parseObject(s1, Map.class);
|
list5.add(map6);
|
}
|
}
|
}
|
|
//第四个文件
|
String c4 = jsonObject.getString("c4");
|
JSONObject jsonObject3 = JSON.parseObject(c4);
|
String gxq = jsonObject3.getString("高新区");
|
JSONObject jsonObject4 = JSON.parseObject(gxq);
|
|
String PM25m = jsonObject4.getString("PM2_5m");
|
String PM25ms = jsonObject4.getString("PM2_5ms");
|
String code1 = getString(PM25ms);
|
String O3m = jsonObject4.getString("O3m");
|
String O3ms = jsonObject4.getString("O3ms");
|
String code2 = getString(O3ms);
|
String AQIm = jsonObject4.getString("AQIm");
|
String AQIms = jsonObject4.getString("AQIms");
|
String code3 = getString(AQIms);
|
String PM25y = jsonObject4.getString("PM2_5y");
|
String PM25ys = jsonObject4.getString("PM2_5ys");
|
String code4 = getString(PM25ys);
|
String O3y = jsonObject4.getString("O3y");
|
String O3ys = jsonObject4.getString("O3ys");
|
String code5 = getString(O3ys);
|
String AQIy = jsonObject4.getString("AQIy");
|
String AQIys = jsonObject4.getString("AQIys");
|
String code6 = getString(AQIys);
|
|
StringBuilder sb1 = new StringBuilder();
|
StringBuilder sb2 = new StringBuilder();
|
StringBuilder sb3 = new StringBuilder();
|
StringBuilder sb4 = new StringBuilder();
|
StringBuilder sb5 = new StringBuilder();
|
StringBuilder sb6 = new StringBuilder();
|
Set<String> trims = map1.keySet();
|
for (String trim : trims) {
|
String o = (String) map1.get(trim);
|
String[] split = o.split("、");
|
if (Double.parseDouble(split[0])>Double.parseDouble(PM25m)){
|
sb1.append(trim.substring(3)+"、");
|
}
|
if (Double.parseDouble(split[1])>Double.parseDouble(O3m)){
|
sb2.append(trim.substring(3)+"、");
|
}
|
if (Double.parseDouble(split[2])<Double.parseDouble(AQIm)){
|
sb3.append(trim.substring(3)+"、");
|
}
|
if (Double.parseDouble(split[3])>Double.parseDouble(PM25y)){
|
sb4.append(trim.substring(3)+"、");
|
}
|
if (Double.parseDouble(split[4])>Double.parseDouble(O3y)){
|
sb5.append(trim.substring(3)+"、");
|
}
|
if (Double.parseDouble(split[5])<Double.parseDouble(AQIy)){
|
sb6.append(trim.substring(3)+"、");
|
}
|
}
|
String builder1 = getBuilder(sb1);
|
String builder2 = getBuilder(sb2);
|
String builder3 = getBuilder(sb3);
|
String builder4 = getBuilder(sb4);
|
String builder5 = getBuilder(sb5);
|
String builder6 = getBuilder(sb6);
|
excelVo.setName1(builder1);
|
excelVo.setName2(builder2);
|
excelVo.setName3(builder3);
|
excelVo.setName4(builder4);
|
excelVo.setName5(builder5);
|
excelVo.setName6(builder6);
|
|
//第五个文件
|
String c5 = jsonObject.getString("c5");
|
JSONObject jsonObject5 = JSON.parseObject(c5);
|
String cxq = jsonObject5.getString("彩香");
|
JSONObject jsonObject6 = JSON.parseObject(cxq);
|
String cxp = jsonObject6.getString("PM2_5y");
|
double CX = 0.0;
|
if (!cxp.equals("--")){
|
CX = Double.parseDouble(cxp);
|
}
|
double aDouble = 0.0;
|
if (!PM25y.equals("--")){
|
aDouble = Double.parseDouble(PM25y);
|
}
|
double v = aDouble - CX;
|
if (v>=0){
|
excelVo.setDiff1("高于");
|
excelVo.setCode1(String.valueOf(new BigDecimal(v).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
|
}else {
|
excelVo.setDiff1("低于");
|
excelVo.setCode1(String.valueOf(new BigDecimal(Math.abs(v)).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
|
}
|
String xcq = jsonObject5.getString("相城区");
|
JSONObject jsonObject7 = JSON.parseObject(xcq);
|
String xcp = jsonObject7.getString("PM2_5y");
|
double XCP = 0.0;
|
if (!xcp.equals("--")){
|
XCP = Double.parseDouble(xcp);
|
}
|
double v1 = aDouble - XCP;
|
if (v1>=0){
|
excelVo.setDiff2("高于");
|
excelVo.setCode2(String.valueOf(new BigDecimal(v1).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
|
}else {
|
excelVo.setDiff2("低于");
|
excelVo.setCode2(String.valueOf(new BigDecimal(Math.abs(v1)).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
|
|
}
|
|
|
|
//第一个文件
|
String c1 = jsonObject.getString("c1");
|
JSONObject jsonObject1 = JSON.parseObject(c1);
|
//高新区
|
String cx = jsonObject1.getString("高新区");
|
JSONObject jsonObject2 = JSON.parseObject(cx);
|
String reTime = jsonObject2.getString("日期");
|
Date reDate = DateUtils.getDate(reTime, "yyyy-MM-dd");
|
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日");
|
String format = simpleDateFormat.format(reDate);
|
String substring = format.substring(5);
|
String CO = jsonObject2.getString("CO");
|
String NO2 = jsonObject2.getString("NO2");
|
String SO2 = jsonObject2.getString("SO2");
|
String PM10 = jsonObject2.getString("PM10");
|
String PM25 = jsonObject2.getString("PM2_5");
|
String s1 = jsonObject2.getString("SU");
|
String O8 = jsonObject2.getString("O3_8H");
|
String s3 = jsonObject2.getString("空气质量类别");
|
String s4 = jsonObject2.getString("空气质量级别");
|
int count = 0;
|
if (Double.parseDouble(PM10)<150){
|
count++;
|
}
|
if (Double.parseDouble(PM25)<75){
|
count++;
|
}
|
if (Double.parseDouble(SO2)<150){
|
count++;
|
}
|
if (Double.parseDouble(NO2)<80){
|
count++;
|
}
|
if (Double.parseDouble(CO)<4){
|
count++;
|
}
|
if (!O8.equals("--")) {
|
if (Double.parseDouble(O8) < 160) {
|
count++;
|
}
|
}
|
|
|
|
// String res = substring+"，我区空气质量为"+s3+s4+"，首要污染物为："+s1+"。从空气6因子看，" +
|
// count+"项达标：PM10为"+PM10+"ug/m³（标准值150μg/m³);PM2.5为"+PM25+"μg/m³(标准值75μg/m³);" +
|
// "SO2为"+SO2+"μg/m³(标准值150μg/m³);NO2为"+NO2+"μg/m³(标准值80μg/m³);CO为"+CO+"mg/m³" +
|
// "(标准值4mg/m³);O3-8H为"+O8+"μg/m³(标准值160μg/m³）。";
|
// String month ="我区月度（9月1日-13日）PM2.5浓度为"+PM25m+"微克/立方米，同比"+code1+"%。" +
|
// "臭氧8小时滑动平均值90百分位数浓度为"+O3m+"微克/立方米，同比"+code2+"%。" +
|
// "空气质量优良天数比例为"+AQIm+"%，同比"+code3+"%。";
|
// String years= "今年以来（01月1日-"+substring+"），我区PM2.5浓度为"+PM25y+"微克/立方米，同比"+code4+"%。" +
|
// "臭氧8小时滑动平均值90百分位数浓度为"+O3y+"微克/立方米，同比"+code5+"%。" +
|
// "空气质量优良天数比例为"+AQIy+"%，同比"+code6+"%。";
|
|
excelVo.setQuality(s4+s3);
|
excelVo.setSu(s1);
|
excelVo.setCount(count);
|
excelVo.setPM10(PM10);
|
excelVo.setPM25(PM25);
|
excelVo.setSO2(SO2);
|
excelVo.setNO2(NO2);
|
excelVo.setCO(CO);
|
excelVo.setO3_8H(O8);
|
excelVo.setPM25m(PM25m);
|
excelVo.setPM25ms(code1);
|
excelVo.setO3m(O3m);
|
excelVo.setO3ms(code2);
|
excelVo.setAQIm(AQIm);
|
excelVo.setAQIms(code3);
|
excelVo.setPM25y(PM25y);
|
excelVo.setPM25ys(code4);
|
excelVo.setO3y(O3y);
|
excelVo.setO3ys(code5);
|
excelVo.setAQIy(AQIy);
|
excelVo.setAQIys(code6);
|
excelVo.setTime1(format);
|
excelVo.setTime(substring);
|
|
excelVo.setList1(list1);
|
excelVo.setList2(list2);
|
excelVo.setList3(list3);
|
excelVo.setList4(list4);
|
excelVo.setList5(list5);
|
|
return excelVo;
|
}
|
|
/**
|
* 查询
|
* @param startTime
|
* @param code
|
* @return
|
*/
|
@Override
|
public List<ExcelBO> excelSelect(String startTime, String code,String endTime) {
|
ArrayList<ExcelBO> excelBOS = new ArrayList<>();
|
QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
|
wrapper.between("time",startTime,endTime);
|
wrapper.eq("code",code);
|
List<TbExcel> tbExcels = excelMapper.selectList(wrapper);
|
if (tbExcels==null){
|
return null;
|
}
|
for (TbExcel tbExcel : tbExcels) {
|
ExcelBO excelBO = new ExcelBO();
|
BeanUtils.copyProperties(tbExcel,excelBO);
|
excelBOS.add(excelBO);
|
}
|
|
return excelBOS;
|
}
|
|
/**
|
* 天数据补充
|
* @param files
|
* @return
|
*/
|
@Override
|
public void rexcelImport(List<MultipartFile> files) throws IOException {
|
MultipartFile file = files.get(0);
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
// HashMap<String, Object> rsMap = new HashMap<>();
|
ArrayList<Map<String, Object>> list = new ArrayList<>();
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 1; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
// if (lastCellNum < 10) {
|
// continue;
|
// }
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
map.put("mac", objects[0]);
|
map.put("time", objects[1]);
|
// pm2.5
|
if (!objects[2].toString().equals("-")){
|
map.put("a34004", objects[2]);
|
}
|
|
// pm10
|
if (!objects[3].toString().equals("-")){
|
map.put("a34002", objects[3]);
|
}
|
// 二氧化硫
|
if (!objects[4].toString().equals("-")){
|
map.put("a21026", objects[4]);
|
}
|
// 二氧化氮
|
if (!objects[5].toString().equals("-")){
|
map.put("a21004", objects[5]);
|
}
|
// co
|
if (!objects[6].toString().equals("-")){
|
map.put("a21005", objects[6]);
|
}
|
// o3
|
if (!objects[7].toString().equals("-")){
|
map.put("a05024", objects[7]);
|
}
|
|
// 温度
|
if (!objects[8].toString().equals("-")){
|
map.put("a01001", objects[8]);
|
}
|
// 湿度
|
if (!objects[9].toString().equals("-")){
|
map.put("a01002", objects[9]);
|
}
|
// 风速
|
if (!objects[10].toString().equals("-")){
|
map.put("a01007", objects[10]);
|
}
|
// 风向
|
if (!objects[11].toString().equals("-")){
|
map.put("a01008", objects[11]);
|
}
|
|
// 气压
|
if (!objects[12].toString().equals("-")){
|
map.put("a01006", objects[12]);
|
}
|
// 光照强度
|
if (!objects[13].toString().equals("-")){
|
map.put("a00e12", objects[13]);
|
}
|
// tvoc
|
if (!objects[14].toString().equals("-")){
|
map.put("a99054", objects[14]);
|
}
|
|
// map.put("颗粒物0.3", objects[15]);
|
// map.put("颗粒物2.5", objects[16]);
|
// map.put("硫化氢", objects[17]);
|
// map.put("氨气", objects[18]);
|
// map.put("苯", objects[19]);
|
// map.put("甲苯", objects[20]);
|
// map.put("二甲苯", objects[21]);
|
// map.put("非甲烷总烃", objects[22]);
|
list.add(map);
|
}
|
// ArrayList<HistoryDaily> rsList = new ArrayList<>();
|
for (Map<String, Object> map : list) {
|
HistoryDaily historyDaily = new HistoryDaily();
|
String time = map.remove("time").toString();
|
Date date = DateUtils.dateStringToDate(time);
|
String mac = map.remove("mac").toString();
|
String value = JSONObject.toJSONString(map);
|
historyDaily.setMac(mac);
|
historyDaily.setTime(date);
|
historyDaily.setValue(value);
|
// log.info(historyDaily.getValue());
|
historyDailyMapper.insert(historyDaily);
|
// rsList.add(historyDaily);
|
}
|
}
|
|
|
private String getBuilder(StringBuilder sb) {
|
if (sb.length() > 0 && sb.charAt(sb.length() - 1) == '、') {
|
sb.deleteCharAt(sb.length() -1 );
|
}else {
|
sb.append("无");
|
}
|
String s = sb.toString();
|
return s;
|
}
|
|
private String getString(String value) {
|
String code =null;
|
double aDouble = 0.0;
|
if (!value.equals("--")){
|
aDouble = Double.parseDouble(value);
|
}
|
|
if (aDouble>=0){
|
code = "上升"+ value;
|
}else {
|
String s = value.substring(1);
|
code = "下降"+ s;
|
}
|
return code;
|
}
|
|
|
private Map<String, Object> getMap4(MultipartFile file) throws IOException {
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HashMap<String, Object> rsMap = new HashMap<>();
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 5; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
if (lastCellNum < 10) {
|
continue;
|
}
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
map.put("place", objects[0]);
|
map.put("PM2_5d", objects[1]);
|
map.put("PM2_5m", objects[2]);
|
map.put("PM2_5y", objects[3]);
|
map.put("PM2_5r", objects[4]);
|
map.put("PM2_5ms", objects[5]);
|
map.put("PM2_5ys", objects[6]);
|
map.put("PM2_5rs", objects[7]);
|
map.put("AQId", objects[8]);
|
map.put("AQIm", objects[9]);
|
map.put("AQIy", objects[10]);
|
map.put("AQIr", objects[11]);
|
map.put("AQIms", objects[12]);
|
map.put("AQIys", objects[13]);
|
map.put("AQIrs", objects[14]);
|
map.put("O3d", objects[15]);
|
map.put("O3m", objects[16]);
|
map.put("O3y", objects[17]);
|
map.put("O3r", objects[18]);
|
map.put("O3ms", objects[19]);
|
map.put("O3ys", objects[20]);
|
map.put("O3rs", objects[21]);
|
rsMap.put(objects[0].toString(), map);
|
}
|
return rsMap;
|
}
|
private Map<String, Object> getMap1(MultipartFile file) throws IOException {
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HashMap<String, Object> rsMap = new HashMap<>();
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 3; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
if (lastCellNum < 10) {
|
continue;
|
}
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
map.put("place", objects[0]);
|
map.put("日期", objects[1]);
|
map.put("SO2", objects[2]);
|
map.put("SO2分指数", objects[3]);
|
map.put("NO2", objects[4]);
|
map.put("NO2分指数", objects[5]);
|
map.put("PM10", objects[6]);
|
map.put("PM10分指数", objects[7]);
|
map.put("CO", objects[8]);
|
map.put("CO分指数", objects[9]);
|
map.put("O3_1h", objects[10]);
|
map.put("O3一小时分指数", objects[11]);
|
map.put("O3_8H", objects[12]);
|
map.put("O3八小时分指数", objects[13]);
|
map.put("PM2_5", objects[14]);
|
map.put("PM2_5分指数", objects[15]);
|
map.put("AQI", objects[16]);
|
map.put("SU", objects[17]);
|
map.put("空气质量级别", objects[18]);
|
map.put("空气质量类别", objects[19]);
|
|
rsMap.put(objects[0].toString(), map);
|
}
|
return rsMap;
|
}
|
|
|
private Map<String, Object> getMap2(MultipartFile file) throws IOException {
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HashMap<String, Object> rsMap = new HashMap<>();
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 3; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
if (lastCellNum < 10) {
|
continue;
|
}
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
if (objects[0].toString().equals("高新区")){
|
map.put("place", "国控站");
|
}else {
|
map.put("place",objects[0].toString().substring(3));
|
}
|
map.put("日期", objects[1]);
|
map.put("SO2", objects[2]);
|
map.put("SO2分指数", objects[3]);
|
map.put("NO2", objects[4]);
|
map.put("NO2分指数", objects[5]);
|
map.put("PM10", objects[6]);
|
map.put("PM10分指数", objects[7]);
|
map.put("CO", objects[8]);
|
map.put("CO分指数", objects[9]);
|
map.put("O3_1h", objects[10]);
|
map.put("O3一小时分指数", objects[11]);
|
map.put("O3_8H", objects[12]);
|
map.put("O3八小时分指数", objects[13]);
|
map.put("PM2_5", objects[14]);
|
map.put("PM2_5分指数", objects[15]);
|
map.put("AQI", objects[16]);
|
map.put("SU", objects[17]);
|
map.put("空气质量级别", objects[18]);
|
map.put("空气质量类别", objects[19]);
|
|
rsMap.put(objects[0].toString(), map);
|
}
|
return rsMap;
|
}
|
|
private Map<String, Object> getMap3(MultipartFile file) throws IOException {
|
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HashMap<String, List<Double>> rsMap = new HashMap<>();
|
HashMap<String, Map<String, List<Double>>> maps = new HashMap<>();
|
HashMap<String, List<Double>> map1 = new HashMap<>();
|
HashMap<String, List<Double>> map2 = new HashMap<>();
|
|
|
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 3; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
if (lastCellNum < 10) {
|
continue;
|
}
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
|
|
//CO
|
double CO=0.0;
|
if (!(objects[8].toString()).equals("--")){
|
CO = Double.parseDouble(objects[8].toString());
|
}
|
|
//o3
|
double O3=0.0;
|
if (!(objects[12].toString()).equals("--")){
|
O3 = Double.parseDouble(objects[12].toString());
|
}
|
|
|
|
|
|
if (map1.get(objects[0].toString())==null){
|
ArrayList<Double> doubles1 = new ArrayList<>();
|
ArrayList<Double> doubles2 = new ArrayList<>();
|
doubles1.add(CO);
|
doubles2.add(O3);
|
map1.put(objects[0].toString(),doubles1);
|
map2.put(objects[0].toString(),doubles2);
|
}else {
|
List<Double> doubles = map1.get(objects[0].toString());
|
doubles.add(CO);
|
List<Double> lists = map2.get(objects[0].toString());
|
lists.add(O3);
|
}
|
|
if (maps.get(objects[0].toString())==null){
|
ArrayList<Double> list1 = new ArrayList<>();
|
ArrayList<Double> list2 = new ArrayList<>();
|
ArrayList<Double> list4 = new ArrayList<>();
|
ArrayList<Double> list3 = new ArrayList<>();
|
HashMap<String, List<Double>> ListHashMap = new HashMap<>();
|
if (!(objects[2].toString()).equals("--")){
|
double SO21 = Double.parseDouble(objects[2].toString());
|
list1.add(SO21);
|
}
|
if (!(objects[4].toString()).equals("--")){
|
double NO21 = Double.parseDouble(objects[4].toString());
|
list2.add(NO21);
|
}
|
if (!(objects[6].toString()).equals("--")){
|
double PM101 = Double.parseDouble(objects[6].toString());
|
list3.add(PM101);
|
}
|
if (!(objects[14].toString()).equals("--")){
|
double PM2_51 = Double.parseDouble(objects[14].toString());
|
list4.add(PM2_51);
|
}
|
|
ListHashMap.put("SO2",list1);
|
ListHashMap.put("NO2",list2);
|
ListHashMap.put("PM10",list3);
|
ListHashMap.put("PM2_5",list4);
|
maps.put(objects[0].toString(),ListHashMap);
|
|
}else {
|
Map<String, List<Double>> stringListMap = maps.get(objects[0].toString());
|
List<Double> SO2List = stringListMap.get("SO2");
|
if (!(objects[2].toString()).equals("--")){
|
double SO21 = Double.parseDouble(objects[2].toString());
|
SO2List.add(SO21);
|
}
|
|
List<Double> NO2List = stringListMap.get("NO2");
|
if (!(objects[4].toString()).equals("--")){
|
double NO21 = Double.parseDouble(objects[4].toString());
|
NO2List.add(NO21);
|
}
|
|
List<Double> PM10List = stringListMap.get("PM10");
|
if (!(objects[6].toString()).equals("--")){
|
double PM101 = Double.parseDouble(objects[6].toString());
|
PM10List.add(PM101);
|
}
|
|
List<Double> PM2_5List = stringListMap.get("PM2_5");
|
if (!(objects[14].toString()).equals("--")){
|
double PM2_51 = Double.parseDouble(objects[14].toString());
|
PM2_5List.add(PM2_51);
|
}
|
|
}
|
}
|
Set<String> strings2 = maps.keySet();
|
for (String s : strings2) {
|
Map<String, List<Double>> stringListMap = maps.get(s);
|
ArrayList<Double> list = new ArrayList<>();
|
Double so2 = getYz(stringListMap, "SO2");
|
Double no2 = getYz(stringListMap, "NO2");
|
Double pm10 = getYz(stringListMap, "PM10");
|
Double pm2_5 = getYz(stringListMap, "PM2_5");
|
list.add(so2);
|
list.add(no2);
|
list.add(pm10);
|
list.add(pm2_5);
|
rsMap.put(s,list);
|
}
|
|
HashMap<String, List<Double>> ListHashMap = new HashMap<>();
|
Set<String> strings = rsMap.keySet();
|
for (String string : strings) {
|
List<Double> list = getList(rsMap, map1, map2, string);
|
ListHashMap.put(string,list);
|
}
|
//计算aqi和首要污染物
|
HashMap<String, Object> resultMap = new HashMap<>();
|
Set<String> strings1 = ListHashMap.keySet();
|
for (String s : strings1) {
|
HashMap<String, Object> map = new HashMap<>();
|
List<Double> doubles = ListHashMap.get(s);
|
|
ArrayList<Double> list = new ArrayList<>();
|
double co = getCo(doubles.get(0));
|
double so2 = getSo2(doubles.get(1));
|
double no2 = getNo2(doubles.get(2));
|
double pm10 = getPm10(doubles.get(3));
|
double pm2_5 = getPm2_5(doubles.get(4));
|
double o3 = getO3(doubles.get(5));
|
list.add(co);
|
list.add(so2);
|
list.add(no2);
|
list.add(pm10);
|
list.add(pm2_5);
|
list.add(o3);
|
|
Double aqi = Collections.max(list);
|
|
map.put("a21005",doubles.get(0));
|
map.put("a21026",doubles.get(1));
|
map.put("a21004",doubles.get(2));
|
map.put("a34002",doubles.get(3));
|
map.put("a34004",doubles.get(4));
|
map.put("a05024",doubles.get(5));
|
map.put("AQI",aqi);
|
map.put("SU", getSU(aqi,list));
|
resultMap.put(s,map);
|
}
|
return resultMap;
|
}
|
|
|
|
/**
|
* 计算平均值
|
* @param stringListMap
|
*/
|
private Double getYz(Map<String, List<Double>> stringListMap,String code) {
|
List<Double> doubles = stringListMap.get(code);
|
double v = new BigDecimal(doubles.stream().collect(Collectors.averagingDouble(Double::doubleValue))).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
|
return v;
|
}
|
|
/**
|
* 计算月累计参数
|
* @param rsMap
|
* @param map1
|
* @param map2
|
* @param code
|
* @return
|
*/
|
private List<Double> getList(HashMap<String, List<Double>> rsMap, HashMap<String, List<Double>> map1, HashMap<String, List<Double>> map2,String code) {
|
ArrayList<Double> list = new ArrayList<>();
|
List<Double> doubles1 = map1.get(code);
|
double percentiles1 = getPercentiles(doubles1, 0.95);
|
double co = getDouble(percentiles1);
|
list.add(co);
|
List<Double> doubles2 = map2.get(code);
|
double percentiles2 = getPercentiles(doubles2, 0.90);
|
List<Double> doubles3 = rsMap.get(code);
|
for (int i = 0; i < doubles3.size(); i++) {
|
double anInt = getInt(doubles3.get(i));
|
list.add(anInt);
|
}
|
double anInt = getInt(percentiles2);
|
list.add(anInt);
|
return list;
|
}
|
|
private Map<String, Object> getMap5(MultipartFile file) throws IOException {
|
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HashMap<String, Object> rsMap = new HashMap<>();
|
XSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 5; i <= lastRowNum; i++) {
|
XSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
if (lastCellNum < 10) {
|
continue;
|
}
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
map.put("place", objects[0]);
|
map.put("PM2_5d", objects[1]);
|
map.put("PM2_5m", objects[2]);
|
map.put("PM2_5y", objects[3]);
|
map.put("PM2_5r", objects[4]);
|
map.put("PM2_5ms", objects[5]);
|
map.put("PM2_5ys", objects[6]);
|
map.put("PM2_5rs", objects[7]);
|
map.put("AQId", objects[8]);
|
map.put("AQIm", objects[9]);
|
map.put("AQIy", objects[10]);
|
map.put("AQIr", objects[11]);
|
map.put("AQIms", objects[12]);
|
map.put("AQIys", objects[13]);
|
map.put("AQIrs", objects[14]);
|
map.put("O3d", objects[15]);
|
map.put("O3m", objects[16]);
|
map.put("O3y", objects[17]);
|
map.put("O3r", objects[18]);
|
map.put("O3ms", objects[19]);
|
map.put("O3ys", objects[20]);
|
map.put("O3rs", objects[21]);
|
rsMap.put(objects[0].toString(), map);
|
}
|
return rsMap;
|
}
|
|
|
private Map<String, Object> getMap6(MultipartFile file) throws IOException {
|
// XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
|
HSSFWorkbook workbook= new HSSFWorkbook(file.getInputStream());
|
HashMap<String, Object> rsMap = new HashMap<>();
|
// XSSFSheet sheetAt = workbook.getSheetAt(0);
|
HSSFSheet sheetAt = workbook.getSheetAt(0);
|
//一共有多少行
|
int lastRowNum = sheetAt.getLastRowNum();
|
for (int i = 1; i <= lastRowNum; i++) {
|
// XSSFRow row = sheetAt.getRow(i);
|
HSSFRow row = sheetAt.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
short lastCellNum = row.getLastCellNum();
|
|
Object[] objects = new Object[lastCellNum];
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
Object value = ExcelUtils.getValue(cell);
|
objects[j] = value;
|
}
|
HashMap<String, Object> map = new HashMap<>();
|
map.put("企业名称", objects[0]);
|
map.put("设备名称", objects[1]);
|
map.put("合计", objects[2]);
|
map.put("油烟", objects[3]);
|
map.put("颗粒物", objects[4]);
|
map.put("设备离线", objects[5]);
|
|
rsMap.put(objects[0].toString()+i, map);
|
}
|
return rsMap;
|
}
|
|
/**
|
* excel计算公式
|
* @param list
|
* @param percentile
|
* @return
|
*/
|
private static double getPercentiles(List<Double> list, double percentile) {
|
Collections.sort(list);
|
double x = (list.size()-1) * percentile;
|
int i = (int) x;
|
double j = x - i;
|
return (1 - j) * list.get(i) + j * list.get(i+1);
|
}
|
|
|
private static double getDouble(Double code1){
|
double floor1 = Math.floor(code1 * 10);
|
//保留一位小数四舍五入
|
double result = new BigDecimal(code1).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
|
|
|
if (code1*100-floor1*10==5 && (result*10) % 2 !=0){
|
result= result-0.1;
|
}else {
|
result = result-0;
|
}
|
|
return result;
|
}
|
|
|
|
private static double getInt(Double code){
|
//向下取整数
|
double floor = Math.floor(code);
|
//四舍五入
|
double round = Math.round(code);
|
|
|
if (code*10-(floor*10)==5 && round % 2 !=0){
|
round=round-1;
|
}else {
|
round=round-0;
|
}
|
return round;
|
}
|
/**
|
* 计算搜要污染物
|
* @param aqi
|
* @param list
|
* @return
|
*/
|
private String getSU(Double aqi,ArrayList list){
|
String SU=null;
|
if (aqi<=50){
|
SU="-";
|
} else if (aqi==list.get(1)){
|
SU="SO2";
|
}else if (aqi==list.get(0)){
|
SU="CO";
|
}else if (aqi==list.get(2)){
|
SU="NO2";
|
}else if (aqi==list.get(3)){
|
SU="PM10";
|
}else if (aqi==list.get(4)){
|
SU="PM2.5";
|
}else if (aqi==list.get(5)){
|
SU="O3";
|
}
|
return SU;
|
}
|
|
private static double getSo2(double so2){
|
double v = 0.0;
|
if (so2<50){
|
v = 50 * so2 / 50;
|
}else if (so2<150 && so2 >= 50){
|
v = 50 * (so2 - 50) / 100 + 50;
|
}else if (so2<475 && so2 >= 150){
|
v = 50 * (so2 - 150) / 325 + 100;
|
}else if (so2<800 && so2 >= 475){
|
v = 50 * (so2 - 475) / 325 + 150;
|
}else {
|
v = 100 * (so2 - 800) / 800 + 200;
|
}
|
return Math.round(v);
|
}
|
|
private static double getNo2(double no2){
|
double v = 0.0;
|
if (no2<40){
|
v = 50 * no2 / 40;
|
}else if (no2<80 && no2 >= 40){
|
v = 50 * (no2 - 40) / 40 + 50;
|
}else if (no2<180 && no2 >= 80){
|
v = 50 * (no2 - 80) / 100 + 100;
|
}else if (no2<280 && no2 >= 180){
|
v = 50 * (no2 - 180) / 100 + 150;
|
}else if (no2<565 && no2 >= 280){
|
v = 100 * (no2 - 280) / 285 + 200;
|
}else if (no2<750 && no2 >= 565){
|
v = 100 * (no2 - 565) / 185 + 300;
|
}
|
else {
|
v = 100 * (no2 - 750) / 190 + 400;
|
}
|
return Math.round(v);
|
}
|
|
|
private static double getCo(double co){
|
double v = 0.0;
|
if (co<2){
|
v = 50 * co / 2;
|
}else if (co<4 && co >= 2){
|
v = 50 * (co - 2) / 2 + 50;
|
}else if (co<14 && co >= 4){
|
v = 50 * (co - 4) / 10 + 100;
|
}else if (co<24 && co >= 14){
|
v = 50 * (co - 14) / 10 + 150;
|
}else if (co<36 && co >= 24){
|
v = 100 * (co - 24) / 12 + 200;
|
}else if (co<48 && co >= 36){
|
v = 100 * (co - 36) / 12 + 300;
|
}
|
else {
|
v = 100 * (co - 48) / 12 + 400;
|
}
|
return Math.round(v);
|
}
|
|
private static double getPm10(double pm10){
|
double v = 0.0;
|
if (pm10<50){
|
v = 50 * pm10 / 50;
|
}else if (pm10<150 && pm10 >= 50){
|
v = 50 * (pm10 - 50) / 100 + 50;
|
}else if (pm10<250 && pm10 >= 150){
|
v = 50 * (pm10 - 150) / 100 + 100;
|
}else if (pm10<350 && pm10 >= 250){
|
v = 50 * (pm10 - 250) / 100 + 150;
|
}else if (pm10<420 && pm10 >= 350){
|
v = 100 * (pm10 - 350) / 70 + 200;
|
}else if (pm10<500 && pm10 >= 420){
|
v = 100 * (pm10 - 420) / 80 + 300;
|
}
|
else {
|
v = 100 * (pm10 - 500) / 100 + 400;
|
}
|
return Math.round(v);
|
}
|
|
private static double getPm2_5(double pm2_5){
|
double v = 0.0;
|
if (pm2_5<35){
|
v = 50 * pm2_5 / 35;
|
}else if (pm2_5<75 && pm2_5 >= 35){
|
v = 50 * (pm2_5 - 35) / 40 + 50;
|
}else if (pm2_5<115 && pm2_5 >= 75){
|
v = 50 * (pm2_5 - 75) / 40 + 100;
|
}else if (pm2_5<150 && pm2_5 >= 115){
|
v = 50 * (pm2_5 - 115) / 35 + 150;
|
}else if (pm2_5<250 && pm2_5 >= 150){
|
v = 100 * (pm2_5 - 150) / 100 + 200;
|
}else if (pm2_5<350 && pm2_5 >= 250){
|
v = 100 * (pm2_5 - 250) / 100 + 300;
|
}
|
else {
|
v = 100 * (pm2_5 - 350) / 150 + 400;
|
}
|
return Math.round(v);
|
}
|
|
|
private static double getO3(double o3){
|
double v = 0.0;
|
if (o3<100){
|
v = 50 * o3 / 100;
|
}else if (o3<160 && o3 >= 100){
|
v = 50 * (o3 - 100) / 60 + 50;
|
}else if (o3<215 && o3 >= 160){
|
v = 50 * (o3 - 160) / 55 + 100;
|
}else if (o3<265 && o3 >= 215){
|
v = 50 * (o3 - 215) / 50 + 150;
|
}else if (o3<800 && o3 >= 265){
|
v = 100 * (o3 - 265) / 535 + 200;
|
}else if (o3<1000 && o3 >= 800){
|
v = 100 * (o3 - 800) / 200 + 300;
|
}
|
return Math.round(v);
|
}
|
}
|
