123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138 |
- 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<ExcelData> consumeIot = (t) -> {
- String buildding = t.getBuildding();
- List<List<String>> dataList = t.getDataList();
- List<String> descriptionList = t.getDescriptionList();
- List<String> meterList = t.getMeterList();
- List<String> 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<String> 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 <T> List<T> read(MultipartFile file, Class<T> 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<ExcelListener> read(String filePath, MultipartFile simpleFile, InputStream simpleFileInputStream, Integer sheetNo, Class<T> head, Integer headRowNumber, ExcelListener<T> 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(), "文件为空");
- }
- }
|