解析xls到tinf

whyclxw

2024-10-17 aee5d69235093cd1cf46ec685812985703e01541

解析xls到tinf

 src/main/java/com/whyc/controller/AnaysisController.java

@@ -4,10 +4,14 @@
import com.whyc.util.AnalysisUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.ParseException;


@RequestMapping("anaysis")
@@ -29,4 +33,18 @@
    public Response getAnaysisXls(@RequestParam int battGroupId, @RequestParam int testRecordCount){
        return service.getAnaysisXls(battGroupId,testRecordCount);
    }

    @ApiOperation("解析xls文件到放电数据中")
    @PostMapping("anaysisXlsToTdata")
    public Response anaysisXlsToTdata(@RequestParam int battGroupId,@RequestParam MultipartFile multipartFile) throws IOException, InvalidFormatException, ParseException {
        Response<Object> response = new Response<>();
        String name=multipartFile.getOriginalFilename();
        assert name != null;
        if(!name.substring(name.length()-4).equals(".xls") && !name.substring(name.length()-5).equals(".xlsx")){
            response.set(1,false,"文件解析错误:上传格式非excel格式");
        }else{
            response =  service.anaysisXlsToTdata(battGroupId,multipartFile.getInputStream());
        }
        return response;
    }
}

 src/main/java/com/whyc/pojo/XlsToTdata.java

New file
@@ -0,0 +1,25 @@
package com.whyc.pojo;

import lombok.Data;

import java.util.List;

@Data
public class XlsToTdata {


    private String recordTime;

    private int testTimelong;

    private Float groupVol;

    private Float groupCurr;

    private Float testCap;

    private List<Float> monVols;

    private int recordNum;

}

 src/main/java/com/whyc/service/AnaysiService.java

@@ -2,10 +2,7 @@

import com.whyc.dto.FileDirPath;
import com.whyc.dto.Response;
import com.whyc.pojo.AnaylsisId;
import com.whyc.pojo.AnaysisData;
import com.whyc.pojo.BatttestdataId;
import com.whyc.pojo.UserInf;
import com.whyc.pojo.*;
import com.whyc.util.ActionUtil;
import com.whyc.util.AnalysisUtil;
import org.apache.poi.hssf.usermodel.*;
@@ -13,12 +10,14 @@
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
@@ -29,6 +28,10 @@
public class AnaysiService {
    @Autowired(required = false)
    private SubTablePageInfoService subService;

    @Autowired(required = false)
    private BatttestdataInfService tinfService;


    //预估数据
    public String createAnaysisXls(int battGroupId, int testRecordCount) {
@@ -277,4 +280,76 @@
        List<AnaylsisId> list=subService.getAnaysisXls(battGroupId,testRecordCount);
        return new Response().setII(1,list!=null,list,"查询预估数据");
    }
    //解析xls文件到放电数据中
    public Response<Object> anaysisXlsToTdata(int battGroupId,InputStream inputStream) throws IOException, InvalidFormatException, ParseException {
        Workbook workbook = null;
        workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        //取第一个sheet表
        Sheet sheet = workbook.getSheetAt(2);
        int lastRowNum = sheet.getLastRowNum();
        Row rowTemp = sheet.getRow(0);
        short lastCellNum = rowTemp.getLastCellNum();
        DataFormatter dataFormatter = new DataFormatter(Locale.US);
        List<XlsToTdata> list=new ArrayList<>();
        for (int i=1;i<lastRowNum;i++){
            XlsToTdata tData=new XlsToTdata();
            Row row = sheet.getRow(i);
            Cell cell0 = row.getCell(0);
            String formattedValue = dataFormatter.formatCellValue(cell0);
            tData.setRecordTime(ActionUtil.parseDurationDirectly(formattedValue));

            tData.setTestTimelong(ActionUtil.convertToSeconds(formattedValue));

            Cell cell1 = row.getCell(1);
            cell1.setCellType(Cell.CELL_TYPE_NUMERIC);
            tData.setGroupVol((float) cell1.getNumericCellValue());

            Cell cell2 = row.getCell(2);
            cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
            tData.setGroupCurr((float) cell2.getNumericCellValue());

            Cell cell3 = row.getCell(3);
            cell3.setCellType(Cell.CELL_TYPE_NUMERIC);
            tData.setTestCap((float) cell3.getNumericCellValue());

            List<Float> monVols=new ArrayList<>();
            for (int j=4;j<lastCellNum;j++){
                Cell cell = row.getCell(j);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                monVols.add((float) cell.getNumericCellValue());
            }
            tData.setMonVols(monVols);
            list.add(tData);
        }

        //1.获取当前最大的放电信息testRecordCount
        BatttestdataInf tinf=tinfService.getMaxRecord(battGroupId);
        int testRecordCount=0;
        if(tinf!=null) {
            testRecordCount = tinf.getTestRecordCount();
        }


        //3.将tinf补齐
        tinf=new BatttestdataInf();
        tinf.setBattGroupId(battGroupId);
        tinf.setDataNew((byte) 1);
        tinf.setDataAvailable((byte) 1);
        tinf.setTestType(3);
        tinf.setRecordTimeInterval(10);
        tinf.setTestRecordCount(testRecordCount+1);
        tinf.setTestRecordCountEx(testRecordCount+2);
        tinf.setRecordNum(list.size()+1);
        tinf.setTestStarttime(new Date());
        tinf.setRecordTime(new Date());
        tinf.setTestStarttype(3);
        tinfService.insertTinf(tinf);

        //2.将数据插入电池组数据
        subService.insertAnaysisXlsToTdata(battGroupId,testRecordCount+1,list);
        return new Response<>().set(1);
    }


}

 src/main/java/com/whyc/service/BatttestdataInfService.java

@@ -1326,4 +1326,16 @@
        }
        return new Response().setIII(1, true, sCountMap,sCapMap, "能效统计");
    }
    //1.获取当前最大的放电信息testRecordCount
    public BatttestdataInf getMaxRecord(int battGroupId) {
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.eq("battGroupId",battGroupId);
        wrapper.last("limit 1");
        BatttestdataInf tinf=mapper.selectOne(wrapper);
        return tinf;
    }
    //3.将tinf补齐
    public void insertTinf(BatttestdataInf tinf) {
        mapper.insert(tinf);
    }
}

 src/main/java/com/whyc/service/SubTablePageInfoService.java

@@ -3895,4 +3895,58 @@
        }
        sqlExecuteService.makeManualCommit(sql_str);
    }
    //2.将数据插入电池组数据
    public void insertAnaysisXlsToTdata(int battGroupId, int testRecordCount, List<XlsToTdata> list) {
        ArrayList  sql_str=new ArrayList();
        String tableName="db_batt_testdata."+"tb_batttestdata_"+battGroupId;
        //检测表是否存在
        if(list!=null){
            String insertSql="insert into "+tableName+" "
                    + "("
                    + "BattGroupId,"
                    + "test_record_count,"
                    + "record_num,"
                    + "record_time,"
                    + "test_starttime,"
                    + "test_timelong,"
                    + "group_vol,"
                    + "test_curr,"
                    + "test_cap,"
                    + "mon_num,"
                    + "mon_vol,"
                    + "test_type,"
                    + "data_new,"
                    + "data_available"
                    + ") "
                    + " values ";
            for (int i=0;i<list.size();i++) {
                XlsToTdata tdata=list.get(i);
                List monVols=tdata.getMonVols();
                for (int j=0;j<monVols.size();j++) {
                    if(!(i==0&&j==0)){
                        insertSql+=" ,  ";
                    }
                    String volSql="("
                            + battGroupId+","
                            + testRecordCount+","
                            + (i+1)+","
                            + "'"+tdata.getRecordTime()+"',"
                            + "'"+ActionUtil.sdf.format(new Date())+"',"
                            + tdata.getTestTimelong()+","
                            + tdata.getGroupVol()+","
                            + tdata.getGroupCurr()+","
                            + tdata.getTestCap()+","
                            + (j+1)+","
                            + monVols.get(j)+","
                            + 3+","
                            + 1+","
                            + 1+""
                            + ")";//如果对应是字符几个带上单引号
                    insertSql+=volSql;
                }
            }
            sql_str.add(insertSql);
        }
        sqlExecuteService.makeManualCommit(sql_str);
    }
}

 src/main/java/com/whyc/util/ActionUtil.java

@@ -19,6 +19,9 @@
import java.lang.reflect.Type;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Duration;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
@@ -628,8 +631,55 @@
        return matcher.matches();
    }

    /**
     * 将时间字符串（格式HH:mm:ss）转换为秒数
     *
     * @param timeStr 时间字符串
     * @return 转换后的秒数
     */
    public static int convertToSeconds(String timeStr) {
        // 使用冒号(:)分割时间字符串
        String[] parts = timeStr.split(":");

        // 验证时间字符串是否有效（假设总是包含三个部分）
        if (parts.length != 3) {
            throw new IllegalArgumentException("时间字符串格式不正确，应为HH:mm:ss");
        }

        // 解析小时、分钟和秒
        int hours = Integer.parseInt(parts[0]);
        int minutes = Integer.parseInt(parts[1]);
        int seconds = Integer.parseInt(parts[2]);

        // 转换为总秒数
        // 注意：1小时 = 3600秒，1分钟 = 60秒
        int totalSeconds = hours * 3600 + minutes * 60 + seconds;

        return totalSeconds;
    }

    public static String parseDurationDirectly(String timeStr) {
        // 获取当前时间
        LocalDateTime now = LocalDateTime.now();
        String[] parts = timeStr.split(":");
        if (parts.length != 3) {
            throw new IllegalArgumentException("时间长度字符串格式不正确");
        }
        int hours = Integer.parseInt(parts[0]);
        int minutes = Integer.parseInt(parts[1]);
        int seconds = Integer.parseInt(parts[2]);
        Duration timeToAdd = Duration.ofHours(hours).plusMinutes(minutes).plusSeconds(seconds);
        // 将时间长度加到当前时间上
        LocalDateTime updatedTime = now.plus(timeToAdd);
        return  updatedTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    public static void main(String[] args) {
        System.out.println(filterPwd("Aa@123456"));
        //System.out.println(filterPwd("Aa@123456"));

        // 时间长度字符串
        String timeToAddStr = "00:00:34";
        String time =parseDurationDirectly(timeToAddStr);
        System.out.println(time);
    }

}