POI处理Excel表格导出

@Service
public class OrgnizationExportService{

     @Autowired
     private ObjectMapper mapper;

     //  活动报名列表 【表头的定义】

     private static final String []  TITLE_ACTIVITY_SIGNE = {"","","","",""};

     private static final String [] TITLE_VOLNTEER_INFO = {"","","","",""};


     //  生成活动报名信息表
     public void generateActivitySignUpRecordsXls(Activity activity ,OutPutstream outputstream){
           XSSFWorkbook wb = generateActivitySignUpRecordsXlsCore(activity);
           wb.write(outputstream);
           wb.close();  
     }

    //  生成志愿者信息报表
     public void generateVolunteerXls(List<OrgnazitionVolunteerData> fullVolunteers,String explain,OutPutstream output ){
        XSSFWorkbook wb = generateVolunteerXlsCore(fullVolunteers,explain);
        wb.write(output);
        wb.close();
     }
   
    public XSSFWorkbook generateVolunteerXlsCore(List<OrgnazitionVolunteerData> fullVolunteers,String explain){

        XSSFWorkbook wb = new XSSFWorkbook();

        Map<String ,CellStyle> styles = createActivitySignUpStyles(wb);

        Sheet sheet = wb.crateSheet("志愿者信息");//  sheet 名称
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPag(true);
        shert.setHorizontallyCenter(true);  // 设置水平居中
        PrintSetUp printSetpup = sheet.getPrintSetUp();//  设置打印设置
        pritSetup.setLandscape(true);  //  设置横向打印和纵向打印 true【横向】 false【纵向】

        Row titleRow;
        Cell titleCell ,titleValueCell;
        CellRageAddress mergeedCell;
        
        titleRow = sheet.createRow(0); //  创建第一行
        titleCell = titleRow.setCellValue(0);
        titleCell.setCellValue("导出时间");
        titleCell.setCellStyle(styles.get("cell_title_bg"));

        titleValueCell = titleRow.createRow(1);   //  初始化第二个单元格
        titleValueCell.setCellValue(new Date());  // 设置导出时间

        titleValueCell.setCellStyle(styles.get("cell_title_normal_date"));  //  设置单元格的风格
        mergeedCell = new CellRangeAddress(0,0,1,5);  //  合并单元格 1 到5列
        setBorderOnMergeCell(mergedCell,sheet,wb);
        sheet.addMergedRegion(mergedCell);

        // 排序方式解释  再次创建一个新的单元格
        titleCell = titleRow.createCell(6);//  设置排序方式的解释
        titleCell.setCellValue("排序方式");
        titleCell.setCellStyle(styles.get("cell_title_bg"));
        
        // 从第七个单元格合并到第十个
        titleValueCell = titleRow.createCell(7);
        titleValueCell.setCellValue(explain);
        titleValueCell.setCellStyle()style.get("cell_title_normal"));
        mergedCell = new CellRangeAddress(0,0,7,10);  // 0 0 表示行不用合并 。 合并单元格 7 -10列
        setBorderOnMergeCell(mergedCell,sheet,wb);
        sheet.addMergedRegion(mergedCell);


        //  设置报表的标题
        Row headerRow = sheet.createRow(1);
        for(int i = 0;i<TITLE_VOLNTEER_INFO.length;i++){
             Cell cell = headerRow.createCell(i);
             cell.setCellValue(TITLE_VOLNTEER_INFO[i]);
             cell.setCellStyle(styles.get("header"))
        }
        //  冻结前两行  概要信息以及表单头部
         sheet.creatRreezePane(0,2);
        //  开始填充信息
         Row row;
         Cell cell;
         OrgnazationVolunteerData record;
         int rownum =2;  //  默认从第二行开始填充  数据量为fullVolunteers的大小
         for(int i = 0;i< fullVolunteers.size();i++;rownum++){
            row = sheet.createRow(rownum);
            record = fullVolunteers.get(i);
            if(record == null){
                 continue;
            }
            Volunteer volunteer = record.getVolunteer();
            String wechatName = "";
            wechatName = new String(volunteer.getName(),"utf-8"});
            for(int j =0;j< TITLE_VOLNTEER_INFO.length;j++){
             cell = row.createCell(j);
             String styleName = "cell_normal";
             switch(j){
              //{"序号", "姓名", "性别", "微信名", "手机号", "所在学校/单位", "出生年月", "报名次数", "签到次数", "第一次报名", "最近一次报名"}
                case 0:
                   cell.setCellValue(i+1);
                   break;
                case 1:
                    cell.setCellValue(volunteer.getRealName());
                    break;
                case 2:
                    cell.setCellValue(record.getGenner()==1 ? "男":"女");
                    break;
                case 3:
                     cell.setCellValue(volunteer.get(wechatName));
                     break;
                 case 4:
                     cell.setCellValue(volunteer.getCellPhone());
                     break;
                 case 5:
                      cell.setCellValue(volunteer.get);   
                      break;    
   
                case 6:
                  // 出生年月
                      cell.setCellValue(record.getDob());
                       break;
                 case 7:
                 // 报名次数
                      cell.setCellValue(record.getSignUpCount());
                        break;
                 case 8:
                // 签到次数
                      cell.setCellValue(record.getCheckinCount());
                        break;
                 case 9:
                // 第一次报名时间
                       cell.setCellValue(record.getFirstSignUp());
                       styleName = "cell_normal_date";
                        break;
                case 10:
                // 最近一次报名时间
                       cell.setCellValue(record.getLastSignUp());
                       styleName = "cell_normal_date";
                       break;                                 
                     }
                cell.setCellStyle(styles.get(styleName));     
            }
         }
         //setColumnWidth参数的单位是1/256个字符宽度
         //setHeight参数的单位是1/20个点
           sheet.setColumnWidth(0,256*8);
           sheet.setColumnWidth(1,256*12);
           sheet.setColumnWidth(2,256*4);
           sheet.setColumnWidth(3,256*20);
           sheet.setColumnWidth(4,256*12);
           sheet.setColumnWidth(5,256*36);
           sheet.setColumnWidth(6,256*12);
           sheet.setColumnWidth(7,256*8);
           sheet.setColumnWidth(8,256*8);
           sheet.setColumnWidth(9,256*24);
           sheet.setColumnWith(10,256*24)
          
           return wb;
    }
// 数据填充好之后需要的是style样式的填充
    private Map<String ,CellStyle> createActivitySignUpStyles(XSSFWorkbook wb){
    	Map<String,CellStyle> styles = new HashMap<>();
    	DataFormat df = wb.createDataFormat();
       
        //  设置主题颜色
        byte[] rgb = new byte[3];
        rgb[0]= (byte)51;
        rgb[1] = (byte) 209;
        rgb[2] = (byte)161;
        XSSFColor mycolor = new XSSFColor(rgb); 

        //  设置字体
        Font headerFont = wb.crateFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        
        Font blodFont = wb.creatFont();
        blodFont.setBold(true);
        //  设置样式
        XSFFCellStyle style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(mycolor);//  设置背景颜色
        style.setFillPattern(CellStyle.SOLD_FOREGROUND);
        style.setFont(headerFont); //  设置字体
        styles.put("header",style);   //  put 存储添加
        
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true); //  自动换行 
        style.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
        sytles.put("cell_normal_date",style);
         
        //***********************************************************************
        // CellStyle style=wb.createCellStyle();
        // style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        // style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        // style.setWrapText(true);//自动换行
        // style.setIndention((short)5);//缩进
        // style.setRotation((short)60);//文本旋转，这里的取值是从-90到90，而不是0-180度。
        // cell.setCellStyle(style);

        // //单元格合并
        // CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5);//行头行尾列头列尾
        // sheet.addMergedRegion(region);
        //***********************************************************************

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);  // 左对齐
        style.setFont(boldFont);   //  粗体
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());  //  灰色字体
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);     // 图案样式
        styles.put("cell_title_bg", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(true);
        styles.put("cell_title_normal", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(true);
        style.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm:ss"));
        styles.put("cell_title_normal_date", style);
  
        return styles;

  }
    
    private XSSFCellStyle createBorderedStyle(XSSFWorkbook wb){

    	short black = IndexedColors.BLACK.getIndex();  // 获取黑颜色索引
    	XSSFCellStyle style = wb.createCellStyle();
    	style.setBordRight(CellStyle.BORDER_THIN); //  设置右边线条为细线条
    	style.setRightBorderColor(black);   // 设置右边边框为黑色
    	style.setBorderLeft(CellStyle.BORDER_THIN);
    	style.setLeftBorderColor(black);
    	sytle.setBottomBorderColor(black);
    	style.setBorderBottom(CellStyle.BORDER_THIN);
    	sytle.setBorderTop(CellStyle.BORDER_THIN);
    	style.setTopBorderColor(black);

    	return sytle;
    }
   //  对合并单元格的样式设置
 private void setBorderOnMergedCell(CellRangeAddress mergedCell, Sheet sheet, Workbook workbook) {
    RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, mergedCell, sheet, workbook);
    RegionUtil.setBorderRight(CellStyle.BORDER_THIN, mergedCell, sheet, workbook);
    RegionUtil.setBorderTop(CellStyle.BORDER_THIN, mergedCell, sheet, workbook);
    RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, mergedCell, sheet, workbook);
  }
}