hssf改写为xssf

whycxzp

2022-04-01 efdfc21cd5478c93e2dfb8dc696ec2eaa1c3b70b

 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) {