1.准备
2.想达到的效果
3.分析
要写出表格,首先我们需要先生成一个表格,这里我定义了一个sheet类.用来装一个sheet的全部数据。
表格包含了主题(theme)、表头(title)、内容(contents)、表格说明(explan),表格名字(name);
主题由部门和表格名称组成,所以可以使用Map来装,我这里使用的是: private Map<String, String> theme;
可以看出内容又多个类型部分构成,每个类型可有多个小项;
所以,内容里又可分为两类:1-3为一类,使用 private List<Map<String,List>> contents;
Map中的key,对应表格类型,list对应相应的数据。
(注:这里最好不加最外层的List,小编加上是因为当时思考时想多了,后来发现可以不加,但是又不想去改代码了。)
@Data
public class PlanAndComplete {
//类型
private String type;
//序号
private int id;
//计划事项
private String planThing;
//计划目标
private String planIdeal;
//认可方式
private String approvalMethod;
//开始时间
private String startDate;
//完成时间
private String endDate;
//需配合部门和事项
private String coDept;
//权重
private String weight;
//自评完成率
private String selfEval;
//复核完成率
private String review;
//考评审定完成率
private String ExamApproval;
}
4-7为一类;使用 private List<Other.> others;("."需去掉)
Other类中需要两个属性:name,content 分别对应表格中的类型和该类型对应的内容。
写表格
处理数据
将从数据库中查询出的数据处理,并放入准备好的sheet类中。
写数据
准备一个方法,传入sheet,和想生成表格的地址。
想要表格达到自己想要的样子,需要对列宽,行高进行设置。
//设置列宽,方法setColumnWidth()第二个参数为什么这么设置,自己百度吧。
private static XSSFSheet setSheetColWidth(XSSFSheet sheet) {
// TODO Auto-generated method stub
//第一个参数代表列id(从0开始),第2个参数代表宽度值
//类型列
sheet.setColumnWidth(0, 25*256);
sheet.setColumnWidth(1, 5*256);
sheet.setColumnWidth(2,6*256);
sheet.setColumnWidth(3,6*256);
sheet.setColumnWidth(4,6*256);
sheet.setColumnWidth(5,15*256);
sheet.setColumnWidth(6,6*256);
sheet.setColumnWidth(7,6*256);
sheet.setColumnWidth(8, 10*256);
sheet.setColumnWidth(9, 10*256);
sheet.setColumnWidth(10, 9*256);
sheet.setColumnWidth(11, 9*256);
sheet.setColumnWidth(12, 8*256);
sheet.setColumnWidth(13, 10*256);
sheet.setColumnWidth(14, 10*256);
sheet.setColumnWidth(15, 15*256);
return sheet;
}
//设置每个单元格的样式
public static XSSFCellStyle setCellStyle(XSSFCellStyle style){
//设置居中
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置边框
style.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
style.setWrapText(true);
return style;
}
public static String writePlanSheet(PlanAndCompleteSheet sheet, String path) throws IOException {
// TODO Auto-generated method stub
FileOutputStream fileOutputStream = new FileOutputStream(new File(path));
// 创建工作簿对象
XSSFWorkbook wb = new XSSFWorkbook();
// 创建sheet工作表对象
XSSFSheet wbSheet = wb.createSheet(sheet.getName());
// 设置单元格样式
XSSFCellStyle style = wb.createCellStyle();
style=setCellStyle(style);
//设置表格列宽度
wbSheet=setSheetColWidth(wbSheet);
//设置表头字体
XSSFFont fontTheme=wb.createFont();
fontTheme.setFontName("黑体");
fontTheme.setBold(true);
fontTheme.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
fontTheme.setFontHeightInPoints((short)12);
//title字体
XSSFFont titleFont=wb.createFont();
titleFont.setFontName("仿宋_GB2312");
titleFont.setBold(true);
titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontHeightInPoints((short)12);
//类型字体
XSSFFont typeFont=wb.createFont();
typeFont.setFontName("宋体");
typeFont.setBold(true);
typeFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
typeFont.setFontHeightInPoints((short)12);
//设置内容字体
XSSFFont contentFont=wb.createFont();
contentFont.setFontName("宋体");
contentFont.setFontHeightInPoints((short)10);
// 设置名称
Row row = wbSheet.createRow(0);
row.setHeight((short)(400));
Cell cell = row.createCell(0);
style.setFont(fontTheme);
cell.setCellStyle(style);
cell.setCellValue(sheet.getTheme().get("dept") + sheet.getTheme().get("excelName"));
// 设置主题单元格合并
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 15);
wbSheet.addMergedRegion(region);
// 写入title
wbSheet=writePlanTitle(sheet,wbSheet,style,titleFont);
int rowNum=2;
// 写入内容
wbSheet=writePlanContent(sheet,wbSheet,style,rowNum,contentFont);
// 写入结尾
// 结尾开始行号
int endStartRow = ExcelTools.getContentRows(sheet) + 2;
XSSFCellStyle explainStyle=wb.createCellStyle();
explainStyle.setWrapText(true);
explainStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
explainStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
explainStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
wbSheet=writePlanEndContent(sheet, wbSheet, style, explainStyle,endStartRow,typeFont,contentFont);
//输出表格
wb.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
return "ok";
}
这几个设置字体可忽略。
//获取1-3总内容条数
public static int getContentRows(PlanAndCompleteSheet sheet) {
Map<String, List<PlanAndComplete>> maps = sheet.getContents().get(0);
Set<String> key = maps.keySet();
int count = 0;
for (String k : key) {
count += maps.get(k).size();
}
return count;
}
//获取指定类型的内容
public static List<PlanAndComplete> getContentList(PlanAndCompleteSheet sheet, String type) {
Map<String, List<PlanAndComplete>> map = sheet.getContents().get(0);
return map.get(type);
}
//写title
public static XSSFSheet writePlanTitle(PlanAndCompleteSheet sheet ,XSSFSheet wbSheet,XSSFCellStyle style,XSSFFont font){
Row row = wbSheet.createRow(1);
row.setHeight((short)500);
int titleNum = 0;
for (int i = 0; i < 16; i++) {
Cell cell = row.createCell(i);
style.setFont(font);
cell.setCellStyle(style);
cell.setCellValue(sheet.getTitle().get(titleNum++));
switch (i) {
case 2:
CellRangeAddress regionT = new CellRangeAddress(1, 1, i, i + 2);
wbSheet.addMergedRegion(regionT);
row.createCell(3).setCellStyle(style);
row.createCell(4).setCellStyle(style);
row.createCell(5).setCellStyle(style);
i = i + 2;
break;
case 6:
CellRangeAddress regionT1 = new CellRangeAddress(1, 1, i, i + 1);
wbSheet.addMergedRegion(regionT1);
row.createCell(7).setCellStyle(style);
row.createCell(8).setCellStyle(style);
row.createCell(9).setCellStyle(style);
i = i + 1;
break;
case 10:
row.createCell(11).setCellStyle(style);
CellRangeAddress regionT2 = new CellRangeAddress(1, 1, i, i + 1);
wbSheet.addMergedRegion(regionT2);
i = i + 1;
break;
default:
break;
}
}
return wbSheet;
}
//写上部分内容
public static XSSFSheet writePlanContent(PlanAndCompleteSheet sheet ,XSSFSheet wbSheet,XSSFCellStyle style,int rowNum,XSSFFont font){
// 写入内容
int id = 1;
Row row=null;
Set<String> keys = sheet.getContents().get(0).keySet();
List<PlanAndComplete> list = null;
for (String key : keys) {
// 当前类型的内容集
list = sheet.getContents().get(0).get(key);
// 每个类型的条数
int nowListCount = sheet.getContents().get(0).get(key).size();
if (nowListCount > 1) {
//合并单元格(起始行,起始列,结束行,结束列)
CellRangeAddress region2 = new CellRangeAddress(rowNum, rowNum + nowListCount - 1, 0, 0);
wbSheet.addMergedRegion(region2);
}
PlanAndComplete planAC = null;
// 每条的内容
for (int n = 0; n < list.size(); n++) {
planAC = list.get(n);
row = wbSheet.createRow(rowNum);
// 填写内容
for (int k = 0; k < 16; k++) {
CellRangeAddress region1 = null;
Cell cell = row.createCell(k);
style.setFont(font);
cell.setCellStyle(style);
switch (k) {
case 0: // 第一列 类型列涉及合并单元格
cell.setCellValue(planAC.getType());
break;
case 1:
cell.setCellValue(id++);
break;
case 2:
region1 = new CellRangeAddress(rowNum, rowNum, k, k + 2);
k = k + 2;
wbSheet.addMergedRegion(region1);
cell.setCellValue(planAC.getPlanThing());
row.createCell(3).setCellStyle(style);
row.createCell(4).setCellStyle(style);
break;
case 5:
cell.setCellValue(planAC.getPlanIdeal());
break;
case 6:
region1 = new CellRangeAddress(rowNum, rowNum, k, k + 1);
k = k + 1;
wbSheet.addMergedRegion(region1);
cell.setCellValue(planAC.getApprovalMethod());
row.createCell(7).setCellStyle(style);
break;
case 8:
cell.setCellValue(planAC.getStartDate());
break;
case 9:
cell.setCellValue(planAC.getEndDate());
break;
case 10:
region1 = new CellRangeAddress(rowNum, rowNum, k, k + 1);
k = k + 1;
wbSheet.addMergedRegion(region1);
cell.setCellValue(planAC.getCoDept());
row.createCell(11).setCellStyle(style);
break;
case 12:
cell.setCellValue(planAC.getWeight());
break;
case 13:
cell.setCellValue(planAC.getSelfEval());
break;
case 14:
cell.setCellValue(planAC.getReview());
break;
case 15:
cell.setCellValue(planAC.getExamApproval());
break;
}
}
rowNum++;
}
}
return wbSheet;
}
//写下部分内容
public static XSSFSheet writePlanEndContent(PlanAndCompleteSheet sheet ,XSSFSheet wbSheet,XSSFCellStyle style,XSSFCellStyle explainStyle,int endStartRow,XSSFFont typeFont,XSSFFont contentFont){
Other other = null;
int otherFlag = 2;
for (int i = endStartRow; i < endStartRow + 4; i++) {
Row row = wbSheet.createRow(i);
row.setHeight((short)(550));
if (i == endStartRow) {
// 完成情况部门自评
other = sheet.getOthers().get(0);
Cell cell = row.createCell(0);
style.setFont(typeFont);
cell.setCellStyle(style);
cell.setCellValue(other.getName());
cell = row.createCell(1);
style.setFont(contentFont);
cell.setCellStyle(style);
cell.setCellValue(other.getContent());
for(int j=2;j<11;j++){
row.createCell(j).setCellStyle(style);
}
CellRangeAddress region2 = new CellRangeAddress(i, i, 1, 11);
wbSheet.addMergedRegion(region2);
// 得分
Other other1 = sheet.getOthers().get(1);
cell = row.createCell(12);
style.setFont(typeFont);
cell.setCellStyle(style);
cell.setCellValue(other1.getName());
row.createCell(13).setCellStyle(style);
cell = row.createCell(14);
style.setFont(contentFont);
cell.setCellStyle(style);
cell.setCellValue(other1.getContent());
row.createCell(15).setCellStyle(style);
CellRangeAddress region3 = new CellRangeAddress(i, i, 12, 13);
wbSheet.addMergedRegion(region3);
CellRangeAddress region4 = new CellRangeAddress(i, i, 14, 15);
wbSheet.addMergedRegion(region4);
} else {
other = sheet.getOthers().get(otherFlag);
Cell cell = row.createCell(0);
style.setFont(typeFont);
cell.setCellStyle(style);
cell.setCellValue(other.getName());
cell = row.createCell(1);
style.setFont(contentFont);
cell.setCellStyle(style);
cell.setCellValue(other.getContent());
for(int j=2;j<16;j++){
row.createCell(j).setCellStyle(style);
}
CellRangeAddress region5 = new CellRangeAddress(i, i, 1, 15);
wbSheet.addMergedRegion(region5);
otherFlag++;
}
}
// 填入表格说明
Row row = wbSheet.createRow(endStartRow + 4);
row.setHeightInPoints((short)50);
Cell cell = row.createCell(0);
explainStyle.setFont(contentFont);
cell.setCellStyle(explainStyle);
cell.setCellValue(sheet.getExplan());
CellRangeAddress region6 = new CellRangeAddress(endStartRow + 4, endStartRow + 4, 0, 15);
wbSheet.addMergedRegion(region6);
return wbSheet;
}