模板导出和标准化修改

whyclxw

6 天以前 10613f0fe047752f23d83a567d46dca609b04447

模板导出和标准化修改

 pom.xml

@@ -148,6 +148,21 @@
            <artifactId>poi-ooxml</artifactId>
            <version>3.10.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!--<dependency>
            <groupId>org.springframework.plugin</groupId>

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

@@ -1,5 +1,6 @@
package com.whyc.controller;

import com.whyc.dto.Real.ExportTinfDataDto;
import com.whyc.dto.Statistic.MonStic;
import com.whyc.service.ExportService;
import io.swagger.annotations.Api;
@@ -9,6 +10,7 @@

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

@RestController
@@ -23,4 +25,10 @@
    public void exportBattTinfStatistic(HttpServletRequest req, HttpServletResponse resp ,@RequestBody MonStic stic) throws ParseException {
        service.exportBattTinfStatistic(req,resp,stic);
    }

    @PostMapping("exportTinfDataByTestRecordCount")
    @ApiOperation(value = "历史测试数据导出")
    public void exportTinfDataByTestRecordCount(HttpServletRequest req, HttpServletResponse resp ,@RequestBody ExportTinfDataDto dto) throws ParseException, IOException {
        service.exportTinfDataByTestRecordCount(req,resp,dto);
    }
}

 src/main/java/com/whyc/dto/Real/ExportTinfDataDto.java

New file
@@ -0,0 +1,12 @@
package com.whyc.dto.Real;

import lombok.Data;

@Data
public class ExportTinfDataDto {
    private Integer battgroupId;
    private Integer testRecordCount;
    private String  picGroupVol;
    private String  picBattVol;
    private String  picGroupCurr;
}

 src/main/java/com/whyc/pojo/db_pwrdev_alarm/PwrdevAlarmParamStand.java

@@ -42,7 +42,7 @@
    private Integer paramType;

    @ApiModelProperty(value = "标准参数对应数据名称")
    private Integer paramName;
    private String paramName;

    @ApiModelProperty(value = "基准值")
    private Float basisVal;

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

@@ -1005,4 +1005,12 @@
        wrapper.last(" limit 1");
        return mapper.selectOne(wrapper);
    }
    //历史测试数据导出
    public BatttestdataInf exportTinfDataByTestRecordCount(Integer battgroupId, Integer testRecordCount) {
        QueryWrapper wrapper =new QueryWrapper();
        wrapper.eq("battgroup_id",battgroupId);
        wrapper.eq("test_record_count",testRecordCount);
        wrapper.last(" limit 1");
        return mapper.selectOne(wrapper);
    }
}

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

@@ -1,17 +1,28 @@
package com.whyc.service;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.whyc.dto.Real.ExportTinfDataDto;
import com.whyc.dto.Response;
import com.whyc.dto.Statistic.MonStic;
import com.whyc.dto.Statistic.SticMonRes;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_batt_testdata.BatttestdataId;
import com.whyc.pojo.db_batt_testdata.BatttestdataInf;
import com.whyc.pojo.db_user.User;
import com.whyc.util.ActionUtil;
import com.whyc.util.ServletUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@@ -20,16 +31,19 @@
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.*;

@Service
public class ExportService {

    @Autowired
    private BatttestdataInfService battTinfService;

    @Autowired
    private SubTablePageInfoService subTablePageInfoService;

    @Resource
    private CommonMapper commonMapper;


    //单节数量统计导出（1.2.14）
@@ -169,4 +183,50 @@
            rowNum = rowNum+rowNumAdd+5;
        }
    }
    //历史测试数据导出
    public void exportTinfDataByTestRecordCount(HttpServletRequest req, HttpServletResponse resp, ExportTinfDataDto dto) throws IOException {
       Map<String,Object> map=new HashMap<>();
        //历史测试数据导出
        BatttestdataInf tinf = battTinfService.exportTinfDataByTestRecordCount(dto.getBattgroupId(), dto.getTestRecordCount());
        String tableName ="db_batt_testdata.tb_batttestdata_"+dto.getBattgroupId();
        //获取具体的放电数据
        List<BatttestdataId> dataList= subTablePageInfoService.exportTinfDataByTestRecordCount(dto.getBattgroupId(),dto.getTestRecordCount(),tableName);
        map.put("dataList",dataList);
        BASE64Decoder decoder = new BASE64Decoder();
        if(!dto.getPicGroupVol().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupVol().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picGroupVol",volImage);
        }
        if(!dto.getPicBattVol().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicBattVol().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picBattVol",volImage);
        }
        if(!dto.getPicGroupCurr().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupCurr().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picGroupCurr",volImage);
        }
        //获取导出模板地址
        ClassPathResource classPathResource = new ClassPathResource("excel_templates/batt_mon_static_template.xlsx");
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
        try {
            String fileName = "历史测试数据表.xls";
            resp.setContentType("application/vnd.ms-excel");
            resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            resp.flushBuffer();
            wb.write(resp.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

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

@@ -950,4 +950,40 @@
        });
        return list;
    }
    //获取具体的放电数据
    public List<BatttestdataId> exportTinfDataByTestRecordCount(Integer battgroupId, Integer testRecordCount, String tableName) {
        String sql="select distinct * from "+tableName+" "+
                " where test_record_count="+testRecordCount;
        sql+=" order by record_time asc";
        List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BatttestdataId> list=new ArrayList<>();
                while (rs.next()){
                    BatttestdataId data=new BatttestdataId();
                    data.setNum(rs.getInt("num"));
                    data.setBattgroupId(rs.getInt("battgroup_id"));
                    data.setTestRecordCount(rs.getInt("test_record_count"));
                    data.setTestType(rs.getInt("test_type"));
                    data.setRecordNum(rs.getInt("record_num"));
                    data.setDataNew(rs.getInt("data_new"));
                    data.setDataAvailable(rs.getInt("data_available"));
                    data.setTestTimelong(rs.getInt("test_timelong"));
                    data.setTestCurr(rs.getFloat("test_curr"));
                    data.setTestCap(rs.getFloat("test_cap"));
                    data.setMonNum(rs.getInt("mon_num"));
                    data.setMonVol(rs.getFloat("mon_vol"));
                    data.setMonTmp(rs.getFloat("mon_tmp"));
                    data.setMonRes(rs.getFloat("mon_res"));
                    data.setTestStarttime(rs.getTimestamp("test_starttime"));
                    data.setRecordTime(rs.getTimestamp("record_time"));
                    data.setGroupVol(rs.getFloat("group_vol"));
                    data.setOnlineVol(rs.getFloat("online_vol"));
                    list.add(data);
                }
                return list;
            }
        });
        return list;
    }
}

 src/main/resources/excel_templates/batt_mon_static_template.xlsx

Binary files differ