hssf转xssf

whycxzp

2023-05-09 837170b49ccd9c4eb5cc52472a0c273f7524721e

hssf转xssf

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

@@ -10,6 +10,7 @@
import com.whyc.util.ServletUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.system.ApplicationHome;
import org.springframework.core.env.Environment;
@@ -572,7 +573,8 @@
            e.printStackTrace();
        }
        if(bytes.size()>0){
            createExcel(bytes, echarttitle,qth, datas, bmd,mon_vol_list,mon_tmp_list,groupinfo,splitTime,timeCompareList,monVolCompareList,monTempCompareList,monVolComparePic,monTempComparePic, resp, req);
            //createExcel(bytes, echarttitle,qth, datas, bmd,mon_vol_list,mon_tmp_list,groupinfo,splitTime,timeCompareList,monVolCompareList,monTempCompareList,monVolComparePic,monTempComparePic, resp, req);
            createExcel2007(bytes, echarttitle,qth, datas, bmd,mon_vol_list,mon_tmp_list,groupinfo,splitTime,timeCompareList,monVolCompareList,monTempCompareList,monVolComparePic,monTempComparePic, resp, req);
        }
    }
    /**
@@ -1546,6 +1548,262 @@
        }
    }

    //生成历史数据中的报表
    private void createExcel2007(List<byte[]> bytes,Title title,Qth qth,String[][] datas,BattMaintDealarm bmd,String[][] mon_vol_list ,String[][] mon_tmp_list,List<BatttestdataId> groupinfo,String splitTime,String[] timeCompareList,String[][] monVolCompareList,String[][] monTempCompareList,String monVolComparePic, String monTempComparePic,HttpServletResponse resp,
                             HttpServletRequest req) {
        //根据当前的语言环境设置不同的日志记录
        String lang = ActionUtil.getLang();
        String[] arrTh = null;
        String[] picName = null;
        if (lang.contains("zh")) {
            arrTh = new String[]{
                    "单体编号", "起始单体电压(V)", "截止单体电压(V)", "实际容量(AH)", "剩余容量(AH)", "实际容量百分比(%)", "单体内阻(mΩ)", "起始单体温度(℃)", "终止单体温度(℃)"
            };
            picName = new String[]{
                    "组端电压曲线", "电池电流曲线", "终止单体电压", "终止单体温度", "单体内阻", "单体均衡电流", "单体电压", "单体实际容量", "单体剩余容量", "单体实际容量百分比", "单体电压曲线", "单体温度曲线", "单体剩余容量曲线"
            };
        } else {
            arrTh = new String[]{
                    "Unit number", "Starting unit voltage(V)", "Cutoff cell voltage(V)", "Actual Capacity(AH)", "remaining capacity(AH)", "Actual capacity percentage(%)", "Individual internal resistance(mΩ)", "Starting monomer temperature(℃)", "Termination monomer temperature(℃)"
            };
            picName = new String[]{
                    "Group terminal voltage curve", "Battery current curve", "Termination unit voltage", "Termination monomer temperature", "Individual internal resistance", "Individual balanced current", "Individual voltage", "Actual capacity of individual units", "Remaining capacity of single unit", "Actual capacity percentage of individual units", "Individual voltage curve", "Individual temperature curve", "Individual residual capacity curve"
            };
        }

        try {
            // 创建一个工作薄
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet1 = null;
            if (lang.contains("zh")) {
                sheet1 = wb.createSheet("数据总表");
            } else {
                sheet1 = wb.createSheet("Data Summary Table");
            }
            // HSSFRow row = sheet1.createRow(2);
            XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
            int rownum = 1;
            //钱煜要求电流计算出来（测试容量*3600/测试时长）
            String testCurr = bmd.getSdata().getTestCurr().toString();
            if (bmd.getSdata().getTestTimelong() != 0) {
                float curr = (bmd.getSdata().getTestCap() * 3600) / bmd.getSdata().getTestTimelong();
                testCurr = new Formatter().format("%.1f", curr).toString();
            }
            if (lang.contains("zh")) {
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
                sheet1.getRow(rownum).createCell(2).setCellValue(bmd.getBinf().getStationName() + "-" + bmd.getBinf().getBattGroupName());
                rownum++;

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("电池品牌：" + bmd.getBinf().getBattProducer());
                sheet1.getRow(rownum).createCell(2).setCellValue("安装日期：" + formartDate(bmd.getBinf().getBattInUseDate(), "yyyy-MM-dd"));
                sheet1.getRow(rownum).createCell(3).setCellValue("单体数量：" + bmd.getBinf().getMonCount());
                sheet1.getRow(rownum).createCell(4).setCellValue("单体电压：" + bmd.getBinf().getMonVolStd() + "V");
                sheet1.getRow(rownum).createCell(5).setCellValue("单体标称容量：" + formartDouble(bmd.getBinf().getMonCapStd(), 0) + "AH");
                sheet1.getRow(rownum).createCell(6).setCellValue("单体标称内阻：" + formartDouble(bmd.getBinf().getMonResStd(), 3) + "mΩ");
                sheet1.getRow(rownum).createCell(7).setCellValue("单体标称电导：" + formartDouble(bmd.getBinf().getMonSerStd(), 0));

                rownum++;
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("测试类型：" + BattTestData.battState(bmd.getSdata().getTestType()));
                sheet1.getRow(rownum).createCell(2).setCellValue("测试时间：" + formartDate(bmd.getSdata().getTestStarttime(), "yyyy-MM-dd HH:mm:ss"));
                //sheet1.getRow(rownum).createCell(3).setCellValue("测试电流：" + bmd.getSdata().getTestCurr() + "A");
                sheet1.getRow(rownum).createCell(3).setCellValue("测试电流：" + testCurr + "A");
                sheet1.getRow(rownum).createCell(4).setCellValue("测试时长：" + formatTestLong(bmd.getSdata().getTestTimelong()));
                sheet1.getRow(rownum).createCell(5).setCellValue("结束时间：" + formartDate(new Date(bmd.getSdata().getTestStarttime().getTime() + bmd.getSdata().getTestTimelong() * 1000), "yyyy-MM-dd HH:mm:ss"));
                sheet1.getRow(rownum).createCell(6).setCellValue("测试容量：" + formartDouble(bmd.getSdata().getTestCap(), 0) + "AH");
                //stationIp存放的终止原因
                sheet1.getRow(rownum).createCell(7).setCellValue("终止原因：" + (bmd.getSdata().getTestType() == 2 ? "" : bmd.getBinf().getStationIP()));

                rownum++;
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("报告日期：");
                sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
                rownum += 4;

                int picnum = 0;

                for (int i = 0; i < picName.length; i++) {
                    sheet1.createRow(rownum - 1);
                    sheet1.getRow(rownum - 1).createCell(1).setCellValue(picName[i]);
                    //rownum++;
                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255, (short) 1, rownum, (short) 10, rowNumAdd * (picnum + 1));
                    anchor.setAnchorType(3);
                    patriarch.createPicture(anchor, wb.addPicture(bytes.get(picnum), XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                    picnum++;
                    rownum += rowNumAdd;
                }

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("最高单体电压");
                sheet1.getRow(rownum).createCell(2).setCellValue("最低单体电压");
                sheet1.getRow(rownum).createCell(3).setCellValue("平均单体电压");
                sheet1.getRow(rownum).createCell(4).setCellValue("落后单体电压值");
                sheet1.getRow(rownum).createCell(5).setCellValue("落后单体数量");
                sheet1.getRow(rownum).createCell(6).setCellValue("落后单体数量百分比");
                rownum++;

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue(title.getMax());
                sheet1.getRow(rownum).createCell(2).setCellValue(title.getMin());
                sheet1.getRow(rownum).createCell(3).setCellValue(title.getAvg());
                sheet1.getRow(rownum).createCell(4).setCellValue(title.getClow());
                sheet1.getRow(rownum).createCell(5).setCellValue(title.getLc());
                sheet1.getRow(rownum).createCell(6).setCellValue(title.getLp() + "%");
                rownum += 2;
            } else {
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("Battery pack name");
                sheet1.getRow(rownum).createCell(2).setCellValue(bmd.getBinf().getStationName() + "-" + bmd.getBinf().getBattGroupName());
                rownum++;

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("Battery brand：" + bmd.getBinf().getBattProducer());
                sheet1.getRow(rownum).createCell(2).setCellValue("install date：" + formartDate(bmd.getBinf().getBattInUseDate(), "yyyy-MM-dd"));
                sheet1.getRow(rownum).createCell(3).setCellValue("Number of monomers：" + bmd.getBinf().getMonCount());
                sheet1.getRow(rownum).createCell(4).setCellValue("Individual voltage：" + bmd.getBinf().getMonVolStd() + "V");
                sheet1.getRow(rownum).createCell(5).setCellValue("Individual nominal capacity：" + formartDouble(bmd.getBinf().getMonCapStd(), 0) + "AH");
                sheet1.getRow(rownum).createCell(6).setCellValue("Individual nominal internal resistance：" + formartDouble(bmd.getBinf().getMonResStd(), 3) + "mΩ");
                sheet1.getRow(rownum).createCell(7).setCellValue("Individual nominal conductivity：" + formartDouble(bmd.getBinf().getMonSerStd(), 0));

                rownum++;
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("Test Type：" + BattTestData.battState(bmd.getSdata().getTestType()));
                sheet1.getRow(rownum).createCell(2).setCellValue("Test Time：" + formartDate(bmd.getSdata().getTestStarttime(), "yyyy-MM-dd HH:mm:ss"));
                sheet1.getRow(rownum).createCell(3).setCellValue("Test Curr：" + testCurr + "A");
                sheet1.getRow(rownum).createCell(4).setCellValue("Test TimeLong：" + formatTestLong(bmd.getSdata().getTestTimelong()));
                sheet1.getRow(rownum).createCell(5).setCellValue("Stop Time：" + formartDate(new Date(bmd.getSdata().getTestStarttime().getTime() + bmd.getSdata().getTestTimelong() * 1000), "yyyy-MM-dd HH:mm:ss"));
                sheet1.getRow(rownum).createCell(6).setCellValue("Test Cap：" + formartDouble(bmd.getSdata().getTestCap(), 0) + "AH");
                //stationIp存放的终止原因
                sheet1.getRow(rownum).createCell(7).setCellValue("Stop Reason：" + (bmd.getSdata().getTestType() == 2 ? "" : bmd.getBinf().getStationIP()));

                rownum++;
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("Report Date：");
                sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
                rownum += 4;

                int picnum = 0;

                for (int i = 0; i < picName.length; i++) {
                    sheet1.createRow(rownum - 1);
                    sheet1.getRow(rownum - 1).createCell(1).setCellValue(picName[i]);
                    //rownum++;
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255, (short) 1, rownum, (short) 10, rowNumAdd * (picnum + 1));
                    anchor.setAnchorType(3);
                    patriarch.createPicture(anchor, wb.addPicture(bytes.get(picnum), HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                    picnum++;
                    rownum += rowNumAdd;
                }

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("Maximum individual voltage");
                sheet1.getRow(rownum).createCell(2).setCellValue("Minimum unit voltage");
                sheet1.getRow(rownum).createCell(3).setCellValue("Average individual voltage");
                sheet1.getRow(rownum).createCell(4).setCellValue("Backward unit voltage value");
                sheet1.getRow(rownum).createCell(5).setCellValue("Number of lagging units");
                sheet1.getRow(rownum).createCell(6).setCellValue("Percentage of lagging units");
                rownum++;

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue(title.getMax());
                sheet1.getRow(rownum).createCell(2).setCellValue(title.getMin());
                sheet1.getRow(rownum).createCell(3).setCellValue(title.getAvg());
                sheet1.getRow(rownum).createCell(4).setCellValue(title.getClow());
                sheet1.getRow(rownum).createCell(5).setCellValue(title.getLc());
                sheet1.getRow(rownum).createCell(6).setCellValue(title.getLp() + "%");
                rownum += 2;
            }
            sheet1.createRow(rownum);
            for (int i = 0; i < arrTh.length; i++) {
                sheet1.getRow(rownum).createCell(i + 1).setCellValue(arrTh[i]);
            }
            rownum++;

            if (datas != null && datas.length > 0) {
                for (int i = 0; i < datas[0].length; i++) {
                    sheet1.createRow(rownum);
                    for (int j = 0; j < datas.length; j++) {
                        sheet1.getRow(rownum).createCell(j + 1).setCellValue(datas[j][i]);
                    }
                    rownum++;
                }
            }
            //单体数
            int monNum = mon_tmp_list.length;
            //导出的总笔数
            int exportNum = groupinfo.size();
            //单体电压数据表(flag(0:单体电压,1:温度))
            //createMonInfoSheet(wb, mon_vol_list, groupinfo, monNum, exportNum, 0);
            createMonInfoSheet2007(wb, mon_vol_list, groupinfo, monNum, exportNum, 0);
            //单体温度数据表
            //createMonInfoSheet(wb, mon_tmp_list, groupinfo, monNum, exportNum, 1);
            createMonInfoSheet2007(wb, mon_tmp_list, groupinfo, monNum, exportNum, 1);
            //单体电压对比表-单体温度对比表
            //createMonInfoSheet2(bytes, wb, splitTime, timeCompareList, monVolCompareList, monTempCompareList, monVolComparePic, monTempComparePic);
            createMonInfoSheet2_2007(bytes, wb, splitTime, timeCompareList, monVolCompareList, monTempCompareList, monVolComparePic, monTempComparePic);

            if (qth.getCode() == 1) {
                rownum += 2;
                //锅底    驼峰    Qg    Qt    Qh    电池性能
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue("锅底");
                sheet1.getRow(rownum).createCell(2).setCellValue("驼峰");
                sheet1.getRow(rownum).createCell(3).setCellValue("Qg");
                sheet1.getRow(rownum).createCell(4).setCellValue("Qt");
                sheet1.getRow(rownum).createCell(5).setCellValue("Qh");
                sheet1.getRow(rownum).createCell(6).setCellValue("电池性能");
                rownum++;
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue(qth.getLowTime());
                sheet1.getRow(rownum).createCell(2).setCellValue(qth.getHighTime());
                rownum++;

                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue(qth.getLow() + "V");
                sheet1.getRow(rownum).createCell(2).setCellValue(qth.getHigh() + "V");
                sheet1.getRow(rownum).createCell(3).setCellValue(qth.getQg() + "%");
                sheet1.getRow(rownum).createCell(4).setCellValue(qth.getQt() + "%");
                sheet1.getRow(rownum).createCell(5).setCellValue(qth.getQh() + "%");
                sheet1.getRow(rownum).createCell(6).setCellValue(qth.getTitle());
            }
            //取出电池组信息构建excel文件的名称
            String excelName = "";
            Battinf binf = binfservice.getdevIdByBattGroupId(bmd.getBinf().getBattGroupId());
            Long dev_id = binf.getFBSDeviceId();//获取设备id判断文件名
            if (dev_id != null) {
                if (dev_id / 100000 == DeviceTypeDTO.DEVICE8059 || dev_id / 100000 == DeviceTypeDTO.DEVICE8060) {
                    //StationName9放文本信息用于8059、8060设备加文件名信息
                    excelName = binfservice.serchExcelNameA059(bmd.getBinf().getBattGroupId(), bmd.getBinf().getStationName9());
                } else {
                    excelName = binfservice.serchExcelName(bmd.getBinf().getBattGroupId());
                    //excel文件名
                    String testTime = new SimpleDateFormat("yyyy-MM-dd").format(bmd.getSdata().getTestStarttime());
                    excelName = excelName + "-" + testTime;
                }
            } else {
                excelName = binfservice.serchExcelName(bmd.getBinf().getBattGroupId());
                //excel文件名
                String testTime = new SimpleDateFormat("yyyy-MM-dd").format(bmd.getSdata().getTestStarttime());
                excelName = excelName + "-" + testTime;
            }

            String regEx = "[\n`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。， 、？]";
            excelName = excelName.replace(regEx, "");
            // 转码防止乱码
            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();
        }
    }
    ///生成Excel报表
    private void createExcel(HSSFWorkbook wb,HttpServletResponse resp){
        try {
@@ -1865,6 +2123,129 @@
        }
    }

    public void createMonInfoSheet2007(XSSFWorkbook wb, String[][] datalist, List<BatttestdataId> groupinfo, int monNum, int exportNum, int flag) {
        String lang = ActionUtil.getLang();
        XSSFSheet sheet = null;
        if (lang.contains("zh")) {
            if (flag == 0) {
                sheet = wb.createSheet("单体电压表");
            } else {
                sheet = wb.createSheet("单体温度表");
            }
        } else {
            if (flag == 0) {
                sheet = wb.createSheet("Voltage Data Table");
            } else {
                sheet = wb.createSheet("Temperature Data Table");
            }
        }
        if (datalist != null && groupinfo != null) {
           /* int mon_count = mon_tmp_list.length;        //总单体数量
            int total_col = mon_vol_list.length;        //总记录条数*/
            List<String> tabTh = null;
            if (lang.contains("zh")) {
                tabTh = new ArrayList<String>() {{
                    this.add("时间(HH:MM:SS)");
                    this.add("在线电压(V)");
                    this.add("组端电压(V)");
                    this.add("电流(A)");
                    this.add("测试容量(AH)");
                }};
            } else {
                tabTh = new ArrayList<String>() {{
                    this.add("Time(HH:MM:SS)");
                    this.add("On-line Voltage(V)");
                    this.add("Group terminal voltage(V)");
                    this.add("Current(A)");
                    this.add("Test capacity(AH)");
                }};
            }
            for (int i = 1; i <= monNum; i++) {
                if (lang.contains("zh")) {
                    if (flag == 0) {
                        tabTh.add("单体电压" + i + "(V)");
                    } else {
                        tabTh.add("单体温度" + i + "(℃)");
                    }
                } else {
                    if (flag == 0) {
                        tabTh.add("Individual voltage" + i + "(V)");
                    } else {
                        tabTh.add("Monomer temperature" + i + "(V)");
                    }
                }

            }
            /*for (int i = 0; i <= monNum; i++) {
                if (i == 0) {
                    tabTh.add("");
                    continue;
                }
                if (lang.contains("zh")) {
                    tabTh.add("单体温度" + i + "(℃)");
                } else {
                    tabTh.add("Monomer temperature" + i + "(V)");
                }

            }*/

            String[][] datas = new String[exportNum][tabTh.size()];

            //System.out.println("datas.length"+datas.length+"\t datas[0].length:"+datas[0].length+"==mon_vol_list.length:"+mon_vol_list.length+"\tmon_vol_list[0].length"+mon_vol_list[0].length+"\tmon_tmp:"+mon_tmp_list[0].length);


            for (int j = 0; j < datas.length; j++) {
                int currnum = 0;
                datas[j][currnum++] = formatTestLong(groupinfo.get(j).getTestTimelong());
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getOnlineVol(), 2) + "";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getGroupVol(), 2) + "";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getTestCurr(), 1) + "";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getTestCap(), 1) + "";

                if (flag == 0) {
                    for (int i = 0; i < monNum; i++) {
                        datas[j][currnum++] = datalist[j][i];
                    }
                } else {
                    for (int i = 0; i < monNum; i++) {
                        datas[j][currnum++] = datalist[i][j];
                    }
                }
                //datas[j][currnum++] = "";
            }
            //System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);

            int maxcol = 254;            //设置最大列数

            int countReLine = (int) Math.ceil((double) exportNum / maxcol);
            int totalrow = countReLine * (tabTh.size() + 1);                    //excel总行数
            for (int i = 0; i < totalrow; i++) {
                sheet.createRow(i);
            }
            //System.out.println("countReLine:"+countReLine);

            int currRow = 0;            //当前行
            int arr_index = 0;            //当前列
            for (int i = 0; i < countReLine; i++) {
                for (int col = 0; col <= maxcol && col <= datas.length && arr_index < datas.length; col++) {
                    if (col == 0) {
                        for (int k = 0; k < tabTh.size(); k++) {
                            sheet.getRow(k + currRow).createCell(col).setCellValue(tabTh.get(k));
                        }
                    } else {
                        for (int k = 0; k < datas[arr_index].length; k++) {
                            sheet.getRow(k + currRow).createCell(col).setCellValue(datas[arr_index][k]);
                        }
                        arr_index++;
                    }
                }
                currRow += tabTh.size() + 1;
            }

            //System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
        }
    }

    private void createMonInfoSheet2(List<byte[]> bytes,HSSFWorkbook wb,String splitTime, String[] timeCompareList,String[][] monVolCompareList, String[][] monTempCompareList, String monVolComparePic, String monTempComparePic) {
        String lang = ActionUtil.getLang();
        bytes=new ArrayList<byte[]>();
@@ -2083,4 +2464,223 @@
        }*/

    }

    private void createMonInfoSheet2_2007(List<byte[]> bytes,XSSFWorkbook wb,String splitTime, String[] timeCompareList,String[][] monVolCompareList, String[][] monTempCompareList, String monVolComparePic, String monTempComparePic) {
        String lang = ActionUtil.getLang();
        bytes=new ArrayList<byte[]>();
        //处理图片
        try {
            if (ServletUtils.isNotNull(monVolComparePic)) {
                String[] url = monVolComparePic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(monTempComparePic)) {
                String[] url = monTempComparePic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = null;
        if (lang.contains("zh")) {
            sheet = wb.createSheet("单体电压对比表");
        } else {
            sheet = wb.createSheet("Single unit voltage comparison table");
        }

        //图片元素
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();

        //字体格式-加粗
        XSSFCellStyle cellStyle = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);

        //一行一行的填充数据
        int rowNum = 0;
        XSSFRow firstRow = sheet.createRow(rowNum++);
        XSSFCell cell = firstRow.createCell(0);
        if (lang.contains("zh")) {
            cell.setCellValue("单体电压对比图");
        } else {
            cell.setCellValue("Comparison diagram of individual voltage");
        }
        cell.setCellStyle(cellStyle);

        //插入对比图片
        int picNum = 0;
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255, (short) 0, rowNum, (short) 10, rowNum + 25);
        anchor.setAnchorType(3);
        patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
        picNum++;
        rowNum += 25;

        //插入数据
        XSSFRow firstDataRow = sheet.createRow(rowNum++);
        XSSFCell dataCell = firstDataRow.createCell(0);
        if (lang.contains("zh")) {
            dataCell.setCellValue("基准时间:" + splitTime + "分钟");
        } else {
            dataCell.setCellValue("reference time:" + splitTime + "minute");
        }
        dataCell.setCellStyle(cellStyle);
        //数据行抬头
        XSSFRow rowDataTitle = sheet.createRow(rowNum++);
        for (int i = 0; i < monVolCompareList.length + 1; i++) {
            XSSFCell cellTemp = rowDataTitle.createCell(i);
            if (i == 0) {
                if (lang.contains("zh")) {
                    cellTemp.setCellValue("时间(HH:MM:SS)");
                } else {
                    cellTemp.setCellValue("Time(HH:MM:SS)");
                }
            } else {
                if (lang.contains("zh")) {
                    cellTemp.setCellValue("单体对比电压" + (i) + "(V)");
                } else {
                    cellTemp.setCellValue("Single body comparison voltage" + (i) + "(V)");
                }
            }
            cellTemp.setCellStyle(cellStyle);
        }
        //数据行[1,1,1],[2,2,2]
        for (int i = 0; i < monVolCompareList[0].length; i++) {
            XSSFRow row = sheet.createRow(rowNum++);
            for (int j = 0; j < monVolCompareList.length + 1; j++) {
                row.createCell(0).setCellValue(timeCompareList[i]);
                if (j != 0) {
                    row.createCell(j).setCellValue(monVolCompareList[j - 1][i]);
                }
            }
        }

        //第二个sheet
        XSSFSheet sheet1 = null;
        if (lang.contains("zh")) {
            sheet1 = wb.createSheet("单体温度对比表");
        } else {
            sheet1 = wb.createSheet("Single body temperature comparison table");
        }
        //图片元素
        XSSFDrawing patriarch1 = sheet1.createDrawingPatriarch();

        //字体格式-加粗
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        XSSFFont font1 = wb.createFont();
        font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        cellStyle1.setFont(font1);

        //一行一行的填充数据
        int rowNum1 = 0;
        XSSFRow firstRow1 = sheet1.createRow(rowNum1++);
        XSSFCell cell1 = firstRow1.createCell(0);
        if (lang.contains("zh")) {
            cell1.setCellValue("单体温度对比图");
        } else {
            cell1.setCellValue("Comparison diagram of individual temperature");
        }
        cell1.setCellStyle(cellStyle);

        //插入对比图片
        XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 512, 255, (short) 0, rowNum1, (short) 10, rowNum1 + 25);
        anchor1.setAnchorType(3);
        patriarch1.createPicture(anchor1, wb.addPicture(bytes.get(picNum), XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
        rowNum1 += 25;

        //插入数据
        XSSFRow firstDataRow1 = sheet1.createRow(rowNum1++);
        XSSFCell dataCell1 = firstDataRow1.createCell(0);
        if (lang.contains("zh")) {
            dataCell1.setCellValue("基准时间:" + splitTime + "分钟");
        } else {
            dataCell1.setCellValue("reference time:" + splitTime + "minute");
        }

        dataCell1.setCellStyle(cellStyle1);
        //数据行抬头
        XSSFRow rowDataTitle1 = sheet1.createRow(rowNum1++);
        for (int i = 0; i < monTempCompareList.length + 1; i++) {
            XSSFCell cellTemp = rowDataTitle1.createCell(i);
            if (i == 0) {
                if (lang.contains("zh")) {
                    cellTemp.setCellValue("时间(HH:MM:SS)");
                } else {
                    cellTemp.setCellValue("Time(HH:MM:SS)");
                }
            } else {
                if (lang.contains("zh")) {
                    cellTemp.setCellValue("单体对比温度" + (i) + "(℃)");
                } else {
                    cellTemp.setCellValue("Single body comparison temperature" + (i) + "(℃)");
                }
            }
            cellTemp.setCellStyle(cellStyle1);
        }
        //数据行[1,1,1],[2,2,2]
        for (int i = 0; i < monTempCompareList[0].length; i++) {
            XSSFRow row = sheet1.createRow(rowNum1++);
            for (int j = 0; j < monTempCompareList.length + 1; j++) {
                row.createCell(0).setCellValue(timeCompareList[i]);
                if (j != 0) {
                    row.createCell(j).setCellValue(monTempCompareList[j - 1][i]);
                }
            }
        }

        /*//基准时间
        HSSFRow row = sheet.createRow(rowNum++);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("基准时间:"+splitTime+"分钟");
        cell.setCellStyle(cellStyle);
        //时间行
        HSSFRow rowTitle = sheet.createRow(rowNum++);
        HSSFCell cellTitle = rowTitle.createCell(0);
        cellTitle.setCellValue("时间(HH:MM:SS)");
        cellTitle.setCellStyle(cellStyle);
        for (int i = 1; i < timeCompareList.length+1; i++) {
            rowTitle.createCell(i).setCellValue(timeCompareList[i-1]);
        }
        //单体电压对比行
        for (int i = 0; i < monVolCompareList.length; i++) { //行数
            HSSFRow rowTemp = sheet.createRow(rowNum++);
            HSSFCell firstCell = rowTemp.createCell(0);
            firstCell.setCellValue("单体对比电压"+(i+1)+"(V)");
            firstCell.setCellStyle(cellStyle);
            for (int j = 1; j < monVolCompareList[i].length+1; j++) { //每行列数
                rowTemp.createCell(j).setCellValue(monVolCompareList[i][j-1]);
            }
        }

        rowNum++; //空格一行

        //单体温度对比行
        for (int i = 0; i < monTempCompareList.length; i++) { //行数
            HSSFRow rowTemp = sheet.createRow(rowNum++);
            HSSFCell firstCell = rowTemp.createCell(0);
            firstCell.setCellValue("单体对比温度"+(i+1)+"(℃)");
            firstCell.setCellStyle(cellStyle);
            for (int j = 1; j < monTempCompareList[i].length+1; j++) { //每行列数
                rowTemp.createCell(j).setCellValue(monTempCompareList[i][j-1]);
            }
        }

        rowNum++; //空格一行

        //插入图片
        int picNum = 0;
        String[] picName = new String[]{
                "单体电压对比图","单体温度对比图"
        };
        for(int i=0;i<picName.length;i++){
            sheet.createRow(rowNum);
            sheet.getRow(rowNum).createCell(0).setCellValue(picName[i]);
            //rowNum++;
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNum, (short) 10, rowNum+25);
            anchor.setAnchorType(3);
            patriarch.createPicture(anchor,wb.addPicture(bytes.get(picNum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
            picNum ++;rowNum+=25;
        }*/

    }
}