hssf改写为xssf

whycxzp

2022-04-01 efdfc21cd5478c93e2dfb8dc696ec2eaa1c3b70b

hssf改写为xssf

 WebRoot/WEB-INF/lib/poi-ooxml-3.10.1.jar

Binary files differ

 src/com/fgkj/servlets/EchartPictureDowloadServlet.java

@@ -34,6 +34,7 @@
import com.fgkj.dao.BattTestData;
import com.google.gson.reflect.TypeToken;

import org.apache.poi.xssf.usermodel.*;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

@@ -897,10 +898,12 @@
        };
        try {
            // 创建一个工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("数据总表");
            //HSSFWorkbook wb = new HSSFWorkbook();
            //更改类型为 新的excel格式,支持更长的横向长度
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet1 = wb.createSheet("数据总表");
            // HSSFRow row = sheet1.createRow(2);
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
            XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
            int rownum = 1;
        
            sheet1.createRow(rownum);
@@ -1027,8 +1030,8 @@
                }
            }
            
            createMonInfoSheet(wb,mon_vol_list ,mon_tmp_list,groupinfo);
            createMonInfoSheet2(wb,splitTime,timeCompareList,monVolCompareList ,monTempCompareList,monVolComparePic,monTempComparePic);
            createMonInfoSheet_XSSF(wb,mon_vol_list ,mon_tmp_list,groupinfo);
            createMonInfoSheet2_XSSF(wb,splitTime,timeCompareList,monVolCompareList ,monTempCompareList,monVolComparePic,monTempComparePic);

            if (qth.getCode()==1) {
                rownum += 2;
@@ -1065,7 +1068,7 @@
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
                    + new String(excelName.getBytes("gb2312"), "ISO8859-1")
                    + ".xls");
                    + ".xlsx");
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
@@ -1149,6 +1152,80 @@
            }        
            
            //System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
        }
    }

    public void createMonInfoSheet_XSSF(XSSFWorkbook wb,String[][] mon_vol_list ,String[][] mon_tmp_list,List<Batttestdata> groupinfo){
        XSSFSheet sheet = wb.createSheet("单体数据表");
        if(mon_vol_list != null && mon_tmp_list != null && groupinfo != null){
            int mon_count = mon_tmp_list.length;        //总单体数量
            int total_col = mon_vol_list.length;        //总记录条数


            List<String> tabTh = new ArrayList<String>(){{
                this.add("时间(HH:MM:SS)");this.add("在线电压(V)");this.add("组端电压(V)");this.add("电流(A)");this.add("测试容量(AH)");
            }};
            for(int i=1;i<=mon_count;i++){
                tabTh.add("单体电压"+i+"(V)");
            }
            for(int i=0;i<=mon_count;i++){
                if(i == 0){
                    tabTh.add("");
                    continue;
                }
                tabTh.add("单体温度"+i+"(℃)");
            }

            String[][] datas = new String[total_col][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).getTest_timelong());
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getOnline_vol(),2)+"";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getGroup_vol(),2)+"";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_curr(),1)+"";
                datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_cap(),1)+"";

                for(int i=0;i<mon_vol_list[j].length;i++){
                    datas[j][currnum++] = mon_vol_list[j][i];
                }
                datas[j][currnum++] = "";
                for(int i=0;i<mon_tmp_list.length;i++){
                    datas[j][currnum++] = mon_tmp_list[i][j];
                }
            }
            //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)total_col/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;
            }
        }
    }

@@ -1326,6 +1403,127 @@
        }*/

    }

    private void createMonInfoSheet2_XSSF(XSSFWorkbook wb,String splitTime, String[] timeCompareList,String[][] monVolCompareList, String[][] monTempCompareList, String monVolComparePic, String monTempComparePic) {

        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 = wb.createSheet("单体电压对比表");
        //图片元素
        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);
        cell.setCellValue("单体电压对比图");
        cell.setCellStyle(cellStyle);

        //插入对比图片
        int picNum = 0;
        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;

        //插入数据
        XSSFRow firstDataRow = sheet.createRow(rowNum++);
        XSSFCell dataCell = firstDataRow.createCell(0);
        dataCell.setCellValue("基准时间:"+splitTime+"分钟");
        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){
                cellTemp.setCellValue("时间(HH:MM:SS)");
            }else{
                cellTemp.setCellValue("单体对比电压"+(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 = wb.createSheet("单体温度对比表");
        //图片元素
        XSSFDrawing patriarch1 = sheet1.createDrawingPatriarch();

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

        //一行一行的填充数据
        int rowNum1 = 0;
        XSSFRow firstRow1 = sheet1.createRow(rowNum1++);
        XSSFCell cell1 = firstRow1.createCell(0);
        cell1.setCellValue("单体温度对比图");
        cell1.setCellStyle(cellStyle);

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

        //插入数据
        XSSFRow firstDataRow1 = sheet1.createRow(rowNum1++);
        XSSFCell dataCell1 = firstDataRow1.createCell(0);
        dataCell1.setCellValue("基准时间:"+splitTime+"分钟");
        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){
                cellTemp.setCellValue("时间(HH:MM:SS)");
            }else{
                cellTemp.setCellValue("单体对比温度"+(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]);
                }
            }
        }

    }
    
    //导出CT机样式报表
    private void ExprotCTReport(HttpServletRequest req, HttpServletResponse resp) {