导出修改

lxw

2023-02-10 7fcee987e20eb710a70eed363964e7412421f707

导出修改

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

@@ -5,6 +5,7 @@
import com.whyc.fbo.FboDataHeadStop;
import com.whyc.fbo.FboDataInf;
import com.whyc.pojo.Response;
import com.whyc.service.ExcelExportService;
import com.whyc.service.FboDataInfService;
import com.whyc.util.*;
import io.swagger.annotations.Api;
@@ -31,7 +32,10 @@
@RequestMapping("test")
public class TestController {
    @Autowired
    private FboDataInfService service;
    private FboDataInfService fboService;

    @Autowired
    private ExcelExportService exportService;

    @GetMapping("/hello")
    public Response hello(){
@@ -44,7 +48,7 @@
        String suffix=filePath.substring(filePath.lastIndexOf(".")+1).toLowerCase(Locale.ROOT);
        Response response=new Response();
        switch (suffix){
            case "fbx":FboDataInf fboDataInf = service.readFboFile(filePath);
            case "fbx":FboDataInf fboDataInf = fboService.readFboFile(filePath);
                       response.set(1,fboDataInf,filePath);
                       break;
            case "bres":response.set(1,filePath);
@@ -63,215 +67,20 @@

    @PostMapping("/export")
    @ApiOperation(value = "文件导出")
    public void export(HttpServletRequest req, HttpServletResponse resp){

        String curr_echart = req.getParameter("curr_echart");        //电池电流折线图
        String groupVol_echart = req.getParameter("groupVol_echart");        //组端电压折线图
        String cap_echart = req.getParameter("cap_echart");            //测试容量折线图
        //String actualCap_echart = req.getParameter("actualCap_echart");            //实际容量折线图
        String vol_echart = req.getParameter("vol_echart");            //单体电压折线图
        String tempEnvi_echart = req.getParameter("tempEnvi_echart");//环境温度折线图
        String humiEnvi_echart = req.getParameter("humiEnvi_echart");//环境湿度折线图


        String filePath = req.getParameter("filePath");
        FboDataInf  fboDataInf=service.readFboFile(filePath);
//        if (fboDataInf==null){
//            fboDataInf = new FboDataInf();
//            if (filePath==null || "".equals(filePath)){
//                filePath = fileUrl;
//            }
//            fboDataInf.readFboFile(filePath);
//        }
        //从文件中获取数据
        List<FboData> list = fboDataInf.fboData;
        //图片base64后的数据
        List<byte[]> bytes = new ArrayList<>();
        try {
            if (ServletUtils.isNotNull(curr_echart)) {
                String[] url = curr_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(groupVol_echart)) {
                String[] url = groupVol_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(cap_echart)) {
                String[] url = cap_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            //if (ServletUtils.isNotNull(actualCap_echart)) {
            //    String[] url = actualCap_echart.split(",");
            //    bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            //}
            if (ServletUtils.isNotNull(vol_echart)) {
                String[] url = vol_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(tempEnvi_echart)) {
                String[] url = tempEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(humiEnvi_echart)) {
                String[] url = humiEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "FBX-"+nowFormat;
        //HSSFWorkbook wb = new HSSFWorkbook();
        //HSSFSheet sheet = wb.createSheet("数据总表");
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("数据总表");
        //图片元素
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        ////字体格式-加粗
        //HSSFCellStyle cellStyle = wb.createCellStyle();
        //HSSFFont font = wb.createFont();
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //cellStyle.setFont(font);

        int rowNumSheet = 0;
        FboDataHeadStart start = fboDataInf.fboDataStart;
        FboDataHeadStop stop = fboDataInf.fboDataStop;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池组名称："+start.battNameStr);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("电池品牌："+start.battBrandStr);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);

        sheet.getRow(rowNumSheet).createCell(0).setCellValue("安装日期："+start.batt_date_str);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("单体数量："+start.batt_mon_num);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称容量："+start.batt_std_cap+"AH");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("测试开始时间："+formartDate(start.testStartTime,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("测试时长："+stop.TestTimeLong.hms());
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("单体电压："+start.batt_mon_vol+"V");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("正极绝缘电阻："+list.get(list.size()-1).PosInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("负极绝缘电阻："+list.get(list.size()-1).NegInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称内阻："+start.batt_std_res+"mΩ");
        if(start.DataType==0xFD){
            rowNumSheet++;
            sheet.createRow(rowNumSheet);
            sheet.getRow(rowNumSheet).createCell(0).setCellValue("预估实际容量："+list.get(list.size()-1).actualCap+"AH");
        }
        rowNumSheet+=4;
        //插入图片
        int picNum = 0;
        String[] picName = getPicName(start.DataType);
        if (picName.length==bytes.size()){
            for(int i=0;i<picName.length;i++){
                sheet.createRow(rowNumSheet-1);
                sheet.getRow(rowNumSheet-1).createCell(0).setCellValue(picName[i]);
                //rowNum++;
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNumSheet, (short) 10, rowNumSheet+27);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                patriarch.createPicture(anchor,wb.addPicture(bytes.get(picNum),XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                picNum ++;
                rowNumSheet+=27;
            }
        }

        XSSFSheet sheet1 = wb.createSheet("数据");
        //新建行

        //抬头
        int rowNum = 0;
        sheet1.createRow(rowNum).createCell(0).setCellValue("测试数据");
        rowNum++;




        //属性栏
        String[] rowName = getRowName(start.DataType);
        XSSFRow row = sheet1.createRow(rowNum);
        for (int i=0;i<rowName.length;i++){
            row.createCell(i).setCellValue(rowName[i]);
        }
        for(int i=0;i<list.get(0).SingleVol.length;i++){
            row.createCell(rowName.length+i).setCellValue("#"+Integer.valueOf(i+1));
        }
        rowNum++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            FboData fbo=list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbo.m_TestTime.hms());
            sheet1.getRow(rowNum).createCell(1).setCellValue(fbo.SumVoltage);
            sheet1.getRow(rowNum).createCell(2).setCellValue(fbo.SumCurrent);
            sheet1.getRow(rowNum).createCell(3).setCellValue(fbo.Temp_Envi);
            sheet1.getRow(rowNum).createCell(4).setCellValue(fbo.Humi_Envi);
            if (start.DataType!=0xFB){
                sheet1.getRow(rowNum).createCell(5).setCellValue(fbo.testCap);
            }
            for (int k = 0;k<list.get(0).SingleVol.length;k++){
                sheet1.getRow(rowNum).createCell(rowName.length+k).setCellValue(fbo.SingleVol[k]);
            }
            rowNum++;
        }

        rowNum++;

        try {
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
                    + new String(excelName.getBytes("UTF-8"), "ISO8859-1")
                    + ".xlsx");
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    public String[] getRowName(int dateType){
        if (dateType==0xFB){
            String[] rowName = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度"};
            return rowName;
        }else{
            String[] rowName1 = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度","测试容量(AH)"};
            return rowName1;
    public void export(HttpServletRequest req, HttpServletResponse resp, @RequestParam String suffix){
        switch (suffix){
            case "fbx":exportService.exportFbx(req,resp);
                break;
            case "bres":
                break;
            case "alm":
                break;
            case "mcp":
                break;
            case "mch":
                break;
        }
    }

    public String[] getPicName(int dateType){
        if (dateType==0xFB){
            String[] picName1 = new String[]{
                    "电池电流折线图","组端电压折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
            };
            return picName1;
        }else{
            String[] picName = new String[]{
                    "电池电流折线图","组端电压折线图","测试容量折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
            };
            return picName;
        }
    }


    /**
     * 将日期格式转换成指定的字符串格式
     * @param date    日期
     * @param str    字符串的格式
     * @return
     */
    public static String formartDate(Date date, String str){
        return new SimpleDateFormat(str).format(date);
    }


}


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

New file
@@ -0,0 +1,237 @@
package com.whyc.service;

import com.whyc.fbo.FboData;
import com.whyc.fbo.FboDataHeadStart;
import com.whyc.fbo.FboDataHeadStop;
import com.whyc.fbo.FboDataInf;
import com.whyc.util.ServletUtils;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Service
public class ExcelExportService {
    @Autowired
    FboDataInfService fboService;

    //导出fbx
    public void exportFbx(HttpServletRequest req, HttpServletResponse resp){

        String curr_echart = req.getParameter("curr_echart");        //电池电流折线图
        String groupVol_echart = req.getParameter("groupVol_echart");        //组端电压折线图
        String cap_echart = req.getParameter("cap_echart");            //测试容量折线图
        //String actualCap_echart = req.getParameter("actualCap_echart");            //实际容量折线图
        String vol_echart = req.getParameter("vol_echart");            //单体电压折线图
        String tempEnvi_echart = req.getParameter("tempEnvi_echart");//环境温度折线图
        String humiEnvi_echart = req.getParameter("humiEnvi_echart");//环境湿度折线图


        String filePath = req.getParameter("filePath");
        FboDataInf fboDataInf=fboService.readFboFile(filePath);
//        if (fboDataInf==null){
//            fboDataInf = new FboDataInf();
//            if (filePath==null || "".equals(filePath)){
//                filePath = fileUrl;
//            }
//            fboDataInf.readFboFile(filePath);
//        }
        //从文件中获取数据
        List<FboData> list = fboDataInf.fboData;
        //图片base64后的数据
        List<byte[]> bytes = new ArrayList<>();
        try {
            if (ServletUtils.isNotNull(curr_echart)) {
                String[] url = curr_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(groupVol_echart)) {
                String[] url = groupVol_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(cap_echart)) {
                String[] url = cap_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            //if (ServletUtils.isNotNull(actualCap_echart)) {
            //    String[] url = actualCap_echart.split(",");
            //    bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            //}
            if (ServletUtils.isNotNull(vol_echart)) {
                String[] url = vol_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(tempEnvi_echart)) {
                String[] url = tempEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(humiEnvi_echart)) {
                String[] url = humiEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "FBX-"+nowFormat;
        //HSSFWorkbook wb = new HSSFWorkbook();
        //HSSFSheet sheet = wb.createSheet("数据总表");
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("数据总表");
        //图片元素
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        ////字体格式-加粗
        //HSSFCellStyle cellStyle = wb.createCellStyle();
        //HSSFFont font = wb.createFont();
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //cellStyle.setFont(font);

        int rowNumSheet = 0;
        FboDataHeadStart start = fboDataInf.fboDataStart;
        FboDataHeadStop stop = fboDataInf.fboDataStop;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池组名称："+start.battNameStr);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("电池品牌："+start.battBrandStr);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);

        sheet.getRow(rowNumSheet).createCell(0).setCellValue("安装日期："+start.batt_date_str);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("单体数量："+start.batt_mon_num);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称容量："+start.batt_std_cap+"AH");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("测试开始时间："+formartDate(start.testStartTime,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("测试时长："+stop.TestTimeLong.hms());
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("单体电压："+start.batt_mon_vol+"V");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("正极绝缘电阻："+list.get(list.size()-1).PosInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("负极绝缘电阻："+list.get(list.size()-1).NegInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称内阻："+start.batt_std_res+"mΩ");
        if(start.DataType==0xFD){
            rowNumSheet++;
            sheet.createRow(rowNumSheet);
            sheet.getRow(rowNumSheet).createCell(0).setCellValue("预估实际容量："+list.get(list.size()-1).actualCap+"AH");
        }
        rowNumSheet+=4;
        //插入图片
        int picNum = 0;
        String[] picName = getPicName(start.DataType);
        if (picName.length==bytes.size()){
            for(int i=0;i<picName.length;i++){
                sheet.createRow(rowNumSheet-1);
                sheet.getRow(rowNumSheet-1).createCell(0).setCellValue(picName[i]);
                //rowNum++;
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNumSheet, (short) 10, rowNumSheet+27);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                patriarch.createPicture(anchor,wb.addPicture(bytes.get(picNum),XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                picNum ++;
                rowNumSheet+=27;
            }
        }

        XSSFSheet sheet1 = wb.createSheet("数据");
        //新建行

        //抬头
        int rowNum = 0;
        sheet1.createRow(rowNum).createCell(0).setCellValue("测试数据");
        rowNum++;




        //属性栏
        String[] rowName = getRowName(start.DataType);
        XSSFRow row = sheet1.createRow(rowNum);
        for (int i=0;i<rowName.length;i++){
            row.createCell(i).setCellValue(rowName[i]);
        }
        for(int i=0;i<list.get(0).SingleVol.length;i++){
            row.createCell(rowName.length+i).setCellValue("#"+Integer.valueOf(i+1));
        }
        rowNum++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            FboData fbo=list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbo.m_TestTime.hms());
            sheet1.getRow(rowNum).createCell(1).setCellValue(fbo.SumVoltage);
            sheet1.getRow(rowNum).createCell(2).setCellValue(fbo.SumCurrent);
            sheet1.getRow(rowNum).createCell(3).setCellValue(fbo.Temp_Envi);
            sheet1.getRow(rowNum).createCell(4).setCellValue(fbo.Humi_Envi);
            if (start.DataType!=0xFB){
                sheet1.getRow(rowNum).createCell(5).setCellValue(fbo.testCap);
            }
            for (int k = 0;k<list.get(0).SingleVol.length;k++){
                sheet1.getRow(rowNum).createCell(rowName.length+k).setCellValue(fbo.SingleVol[k]);
            }
            rowNum++;
        }

        rowNum++;

        try {
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
                    + new String(excelName.getBytes("UTF-8"), "ISO8859-1")
                    + ".xlsx");
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public String[] getRowName(int dateType){
        if (dateType==0xFB){
            String[] rowName = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度"};
            return rowName;
        }else{
            String[] rowName1 = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度","测试容量(AH)"};
            return rowName1;
        }
    }

    public String[] getPicName(int dateType){
        if (dateType==0xFB){
            String[] picName1 = new String[]{
                    "电池电流折线图","组端电压折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
            };
            return picName1;
        }else{
            String[] picName = new String[]{
                    "电池电流折线图","组端电压折线图","测试容量折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
            };
            return picName;
        }
    }


    /**
     * 将日期格式转换成指定的字符串格式
     * @param date    日期
     * @param str    字符串的格式
     * @return
     */
    public static String formartDate(Date date, String str){
        return new SimpleDateFormat(str).format(date);
    }

}