生成Excel

@RequestMapping("/fao/ga_manage/stat/exportAbroadPlanExpectExcel.do")
 public void exportAbroadPlanExpectExcel(HttpServletRequest request, HttpServletResponse response) {
  String strSql = request.getParameter("strSql");
  String staName = request.getParameter("staName");
  String[] colsWidth = request.getParameterValues("colsWidth");
  String templateFileName = request.getParameter("templateFileName");
  ExportExcelHelper.exportAbroadPlanExpectExcel(strSql,staName,colsWidth,templateFileName,staName, response);
 }

 

/**
  * 生成特殊表头报表
  * @param data :显示的数据
  * @param staName：报表名
  * @param colsWidth：每列宽度
  * @param templateFileName：报表模板名
  * @param savaFileName：保存路径
  */
 public static void exportAbroadPlanExpectExcel(String strSql,String staName,String colsWidthValue[],String templateFileName,String savaFileName,HttpServletResponse response){
  List<StatReport> reportList = StatReportHelper.getReportList(strSql);
  
  List<List<String>> strData=new ArrayList();
  if(reportList != null && reportList.size()>0){
   int row = reportList.size();
   int col= 14;
   for(int i=0;i<row;i++){
     StatReport report=reportList.get(i);
     List<String> tempList=new ArrayList();
     for(int j=0;j<col;j++){
       tempList.add(report.get(j));
     }
     strData.add(tempList);
   }
  }else{
    for(int i=0;i<1;i++){
      List<String> tempList=new ArrayList();
      for(int j=0;j<14;j++){
         tempList.add("0");
      }
      strData.add(tempList);
    }
  }
  
  int rows = strData.size();
  int cols = colsWidthValue.length;
  String data[][] = new String[rows][cols];
  
  for(int i=0;i<strData.size();i++){
   int teamTotal = 0;
   int memberTotal = 0;
   //团组数(横向)合计
   teamTotal = Integer.parseInt(strData.get(i).get(1)) + Integer.parseInt(strData.get(i).get(3));
    //出访人次数(横向)合计
    memberTotal = Integer.parseInt(strData.get(i).get(2)) + Integer.parseInt(strData.get(i).get(4));
    int index = i+1;
    data[i][0]=String.valueOf(index);
    for(int j=0;j<16;j++){
       if(j==5){
        data[i][j+1]=String.valueOf(teamTotal);
     }else if(j==6){
       data[i][j+1]=String.valueOf(memberTotal);
     }else if(j>6){
       data[i][j+1]=String.valueOf(strData.get(i).get(j-2));
     }else{
       data[i][j+1]=String.valueOf(strData.get(i).get(j));
     }
     }
  }
  
  short[] colsWidth = new short[colsWidthValue.length];
  for(int i= 0;i<colsWidthValue.length;i++){
   colsWidth[i] = Short.parseShort(colsWidthValue[i]);
  }
  String reportPath = Config.get("report.store_path");
  if ( !reportPath.endsWith("/") && !reportPath.endsWith("//") ) {
   reportPath += "/";
  }
  String reportFullFilename = reportPath + templateFileName;
  FileInputStream is = null;
  try {
   is = new FileInputStream(reportFullFilename);
  } catch (FileNotFoundException e1) {
   e1.printStackTrace();
  }  
  
  
  OutputStream out = null;
  try { 
   // 设置 response.setContentType 编码
   response.setContentType("application/octet-stream; charset=GBK");   
   String newfilename = new String(savaFileName.getBytes("GBK"), "ISO-8859-1");
   // 设置导出 excel 的下载标题
   response.setHeader("Content-Disposition", "attachment; filename="+ newfilename + ".xls");
   // 输出字节流
   out = response.getOutputStream();
   
  
   // 得到工作薄  
         HSSFWorkbook workbook = null;
   try {
    workbook = new HSSFWorkbook(is);
   } catch (IOException e1) {
    e1.printStackTrace();
  
    }
   
         // 得到第一张工作表  
         HSSFSheet sheet = workbook.getSheetAt(0); 
        
         workbook.setSheetName(0,staName,HSSFWorkbook.ENCODING_UTF_16);//支持中文
        
         // 用于格式化单元格的数据
         HSSFDataFormat format = workbook.createDataFormat();
        
         // 设置cellHeading字体
         HSSFFont font = workbook.createFont();
         font.setFontHeightInPoints((short) 25); //字体高度
         font.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
         font.setFontName("宋体"); //字体
         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
        
         // 设置cellHeading字体
         HSSFFont font3 = workbook.createFont();
         font3.setFontHeightInPoints((short) 18); //字体高度
         font3.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
         font3.setFontName("黑体"); //字体
         font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
        
      // 设置cellData字体
         HSSFFont font2 = workbook.createFont();
         font2.setFontHeightInPoints((short) 20); //字体高度
         font2.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
         font2.setFontName("黑体"); //字体
         font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //宽度
        
         // 设置表头cellHeading样式
         HSSFCellStyle cellHeading = workbook.createCellStyle();
         cellHeading.setFont(font);
         cellHeading.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
         cellHeading.setWrapText(true);
        
         // 设置表头cellHeading样式
         HSSFCellStyle cellSubHeading = workbook.createCellStyle();
         cellSubHeading.setFont(font3);
         cellSubHeading.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
         cellSubHeading.setWrapText(true);
        
         // 设置数据cellData样式
         HSSFCellStyle cellData = workbook.createCellStyle();
         cellData.setFont(font2);
         cellData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         cellData.setBorderBottom((short)1);
         cellData.setBorderLeft((short)1);
         cellData.setBorderRight((short)1);
         cellData.setBorderTop((short)1);
        
         // 创建新行(row),并将单元格(cell)放入其中. 行号从0开始计算.
        int intRow=3;
         sheet.addMergedRegion(new Region(0, (short) 0, 0, (short)(colsWidth.length-1)));  
         HSSFRow row = sheet.createRow((short)intRow);
 //      row.setHeight((short)900);   //设置每行高度
 //      // 创建单元格
         HSSFCell cell = row.createCell((short)(colsWidth.length + 1));
        
         for (short i = 0; i < data.length; i++) {
          intRow++;
          row = sheet.createRow(intRow);
          row.setHeight((short)630);   //设置每行高度
          for(short j=0;j<data[0].length;j++){
           cell = row.createCell(j);
           cell.setCellStyle(cellData);
           cell.setEncoding(HSSFCell.ENCODING_UTF_16);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellValue(data[i][j]);
          }
         }
        
         for(int i=0;i<colsWidth.length;i++){
          //调整单元格宽度
          sheet.setAutobreaks(true);
          sheet.setColumnWidth((short)i,colsWidth[i]);
         }
        
         try {
             FileOutputStream fileOut = new FileOutputStream(savaFileName);
             workbook.write(fileOut);
             fileOut.close();
         } catch (Exception e) {
             System.out.println(e.toString());
         }
        
         // 输出到文件
         workbook.write(out);
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    // 注意关闭顺序
    out.flush();
    out.close();
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }   
 }