@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);
}
}
![Java小案例——随机数猜测随机数猜测[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)