@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=)