Java excel导出支持2007以上版本

一.创建工作薄
XSSFWorkbook wb= new XSSFWorkbook();

 二.创建sheet页（如果需要同时导出多个sheet也内容,则如下）

   XSSFSheet sheet1 = createSheet(wb, "数据层");
   XSSFSheet sheet2 = createSheet(wb, "播放层");
   
 2.1      创建createSheet（） 作用：设置sheet页标题，文本内容格式等等

  public XSSFSheet createSheet(XSSFWorkbook wb,String sheetName){
      wb.createSheet(sheetName);
     //设置sheet页文本居中
     sheet.setHorizontallyCenter(true);
     //是否设置自适应界面（有的内容会挤到一张纸上）
      sheet.setFitToPage(false);
      //导出excel，打开excel后会自动执行公式
       sheet.setForceFormulaRecalculation(true);
     //设置打印方式
     XSSFPrintSetup ps = sheet.getPrintSetup();
     ps.setLandscape(true); // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
     ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小
     return sheet; }
 3.// 设置字体的样式
     XSSFFont    font =  wb.cteateFont();
   font.setFontName("宋体");
   //白底黑字
    font.setColor(IndexedColors.WHITE.getIndex());
    //通过数据库查询得到一个数据集   list
    
         genExcel(wb,sheet,list);  （ 主要作用：用来定义样式，得到结果）
  OutputStream os=null;
  HttpServletResponse response=null;
        try{
         response=ServletActionContext.getResponse();
         os=response.getOutputStream();
         response.reset();
         //设置响应流
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
         String filename="java"+new date(new SimpleFormat("yyyy-MM-dd"))+"xlsx";
         filename=URLEncoder.encode(filename,"utf-8");
         response.setHeader("content-disposition","filename=" + fileName);
         wb.write(os);
         catch (Exception e) {
}finally{
    try {
        os.flush();
        os.close();
    } catch (Exception e2) {
    }
        }
    }
public  void genExcel(XSSFWorkbook workbook, XSSFSheet sheet,List list) {
      XSSFCellStyle headStyle = haveHeadStyle(workbook); //创建表头样式
     XSSFCellStyle contextStyle = haveNormalStyle(workbook);//创建文本样式
     XSSFCellStyle footStyle = haveRemarkStyle(workbook);//创建备注样式
     XSSFCellStyle pinkStyle = haveExceptionStyle(workbook);//创建异常样式
     //设置标题位置   合并单元格 new CellRangeAddress
      CellRangeAddress cra = new CellRangeAddress(
            list.size() + 1, //first row
            list.size() + 2, //last row
            0,
            column_title.length-1
    );
    sheet.addMergedRegion(cra);
     // 使用RegionUtil类为合并后的单元格添加边框    为整个表格添加边框
    //下边框
    RegionUtil.setBorderBottom(6, cra, sheet, workbook);
   //左边框
    RegionUtil.setBorderLeft(6, cra, sheet, workbook);
    //上边框
    RegionUtil.setBorderTop(6, cra, sheet, workbook);
    //右边框
    RegionUtil.setBorderRight(6, cra, sheet, workbook);
//循环表头内容到表格中
    XSSFRow row    =     sheet.createRow(0);
    XSSFCell cell=null;
    for(int i=0;i<title.length;i++){
         cell =  row.createCell(i);
       cell.setCellValue(new XSSFRichTextString(column_title[i]));
       cell.setCellStyle(headStyle);
    }
    //循环读取数据行
    for(int i=0;i<list.size();i++){
     row= sheet.createRow(i+1);
       Map map= list.get(i);
       for(int j=0;j<column_value.length;j++){
          XSSFCell cell1=row.createCell(j);      
      cell1.setCellValue((String)map.get(column_value[j]));
    cell1.setCellStyle(contextStyle);
 }    
    }
    //让表格数据小于-10 的标记为红色
    for(int i=1;i<sheet.getLastRowNum();i++){
           XSSFRow row1=  sheet.getRow(i);
      int cells= row1.getPhysicalNumberOfCells();
      for(int k=0;k<cells.length;k++){
          String value=  row1.getCell(k).getStringCellValue();
        try{
          if(Float.parseFloat(cellValue)<-10 && !"".equals(cellValue) && cellValue != null && !cellValue.equals(null)){
         row1.getCell(k).setCellStyle(pinkStyle);
          }else{
            row1.getCell(k).setCellStyle(contextStyle);
          }
        } catch (Exception e){
                      e.printStackTrace();
                  }
      }
    }  
    }
    //创建表头样式
     public XSSFCellStyle haveHeadStyle(XSSFWorkbook workbook) {
       XSSFCellStyle style= workbook.createCellStyle();
       //设置字体颜色
       XSSFFont font= workbook.createFont();
     font.setColor(HSSFColor.WHITE.index);
     style.setFont(font);
     style= sheetStyle(style);
     return style;
  }
    private XSSFCellStyle sheetStyle(XSSFCellStyle style){
     style.setAlignment(HorizontalAlignment.CENTER);//文本水平居中显示
    style.setVerticalAlignment(VerticalAlignment.CENTER);//文本竖直居中显示
    style.setWrapText(true);//文本自动换行

    //生成Excel表单，需要给文本添加边框样式和颜色
    style.setBorderBottom(BorderStyle.THIN);//设置文本边框
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderTop(BorderStyle.THIN);

    return style;
}
//创建文本样式
  public XSSFCellStyle haveNormalStyle(XSSFWorkbook workbook) {
    XSSFCellStyle style = workbook.createCellStyle();
    style = sheetStyle(style);
    return style;
}
  //设置备注样式
public XSSFCellStyle haveRemarkStyle(XSSFWorkbook workbook) {
    XSSFCellStyle style = workbook.createCellStyle();
    style = sheetStyle(style);
    style.setAlignment(HorizontalAlignment.LEFT);
    //设置标题的边框
    //标题居中 ，设置标题文字样式
    XSSFFont titleFont = workbook.createFont();
    titleFont.setBold(true);//加粗
    titleFont.setFontHeight((short)10);//文字尺寸
    titleFont.setFontHeightInPoints((short)10);
    style.setFont(titleFont);
    return style;
}
//红色字体的样式
public XSSFCellStyle haveExceptionStyle(XSSFWorkbook workbook) {
    XSSFCellStyle style = workbook.createCellStyle();
    style = sheetStyle(style);
    //设置字体颜色
    XSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
     style.setFillBackgroundColor(HSSFColor.ROSE.index);
    style.setFillForegroundColor(HSSFColor.ROSE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.ROSE.index);
    return style;
}