package com.persagy.excel; import cn.hutool.core.thread.ThreadUtil; import cn.hutool.core.util.ObjectUtil; import cn.hutool.core.util.StrUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.fastjson.JSON; import com.persagy.common.ErrorCodeConstants; import com.persagy.common.R; import com.persagy.communication.entity.Packet; import com.persagy.communication.util.IClientManager; import com.persagy.parser.ParserEntity; import com.persagy.parser.RecordData; import com.persagy.util.DateUtil; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.formula.functions.T; import org.springframework.web.multipart.MultipartFile; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.concurrent.atomic.AtomicLong; import java.util.function.Consumer; /** * Excel 工具类 * * @author */ @Slf4j public class ExcelUtils { public static AtomicLong totalCount = new AtomicLong(0); public static IClientManager ClientManager; public static final Consumer consumeIot = (t) -> { String buildding = t.getBuildding(); List> dataList = t.getDataList(); List descriptionList = t.getDescriptionList(); List meterList = t.getMeterList(); List funcidList = t.getFuncidList(); Boolean dynamic = t.getDynamic(); try { ParserEntity parserEntity = ParserEntity.builder().building(buildding).gateway("0").datatype("report").packageId("0").dataList(new ArrayList<>()).build(); for (List item : dataList) { Date time = DateUtil.parseDate(item.get(0)); for (int i = 1; i < item.size(); i++) { String value = item.get(i); String meter = meterList.get(i); String funcid = funcidList.get(i); if (StrUtil.isAllNotEmpty(value, meter, funcid)) { RecordData recordData = RecordData.builder().meter(meter).value(value).funcid(funcid).time(time).build(); parserEntity.dataList.add(recordData); } } } log.info("保存数据:{}", JSON.toJSONString(parserEntity)); if (ObjectUtil.isNotEmpty(parserEntity.dataList)) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < parserEntity.dataList.size(); i++) { RecordData recordData = parserEntity.dataList.get(i); if (sb.length() > 0) { sb.append("&"); } sb.append(parserEntity.building).append(";").append(parserEntity.gateway).append(";report;").append(DateUtil.formatDate(recordData.time)) .append(";").append(i).append(";").append(recordData.meter).append(";").append("1").append(";" + recordData.funcid + ";" + recordData.value); if(sb.length()>1500){ ClientManager.AppendToSend(new Packet(sb.toString())); sb.setLength(0); } } if(sb.length()>0){ ClientManager.AppendToSend(new Packet(sb.toString())); sb.setLength(0); } } } catch (Exception e) { log.error(e.getMessage(), e); } }; public static List read(MultipartFile file, Class head) throws IOException { return EasyExcel.read(file.getInputStream(), head, null) .autoCloseStream(false) // 不要自动关闭,交给 Servlet 自己处理 .doReadAllSync(); } /** * 异步读取excel * * @param filePath 文件觉得路径 * @param sheetNo 从0开始 * @param head Annotate the class for configuration information. * @param headRowNumber Count the number of added heads when read sheet. * 0 - This Sheet has no head ,since the first row are the data * 1 - This Sheet has one row head , this is the default * 2 - This Sheet has two row head ,since the third row is the data * @param excelListener * @return 读取的数据 */ public static R read(String filePath, MultipartFile simpleFile, InputStream simpleFileInputStream, Integer sheetNo, Class head, Integer headRowNumber, ExcelListener excelListener) { if (!ObjectUtil.isAllEmpty(simpleFile, simpleFileInputStream)) { //异步 try { if (ObjectUtil.isNotEmpty(simpleFile)) { simpleFileInputStream = simpleFile.getInputStream(); } log.info("开始时间:[{}],结束时间[{}],全部信息[{}]", excelListener.getStartTime(), excelListener.getEndTime(), JSON.toJSONString(excelListener)); EasyExcel.read(simpleFileInputStream, head, excelListener).sheet(sheetNo).autoTrim(true).headRowNumber(headRowNumber).doRead(); } catch (Exception e) { log.error("读取异常:{}", filePath, e); return R.fail(ErrorCodeConstants.USER_ERROR_A0001.getCode(), e.getMessage()); } log.info("读到的数据信息:[{}]", JSON.toJSONString(excelListener)); return R.success(excelListener); } if (StrUtil.isNotBlank(filePath)) { try (InputStream fileStream = new FileInputStream(filePath);) { //异步 EasyExcel.read(fileStream, head, excelListener).sheet(sheetNo).autoTrim(true).headRowNumber(headRowNumber).doRead(); //同步 // return EasyExcel.read(fileStream).sheet(1).doReadSync(); return R.success(excelListener); } catch (Exception e) { log.error("读取异常:{}", filePath, e); return R.fail(ErrorCodeConstants.USER_ERROR_A0001.getCode(), e.getMessage()); } } return R.fail(ErrorCodeConstants.USER_ERROR_A0001.getCode(), "文件为空"); } }