有时会需要用到poi来生成excel文档,poi插件还是很好用的,各种样式、单元格合并基本上都能做到,本篇文章记录一下poi生成一个类似考勤记录的功能,便于下次编写类似功能使用
![](https://img-blog.csdnimg.cn/3d2b5c50385341fca87be7406e0439e0.png)
1、初始化文档
// 创建excel
HSSFWorkbook excel = new HSSFWorkbook();
2、创建页签
//创建HSSFSheet对象
HSSFSheet sheet = excel.createSheet("考勤记录表");
3、创建一行
HSSFRow head = sheet.createRow(0);
// 设置行高
head.setHeightInPoints(35);
4、在一行中创建列
// 序号
sheet.setColumnWidth(0, 10*256);
batchSetTitle(0,"序号",head,titleCellStyle);
因为每建一列都需要创建,代码过程一样,需要列序号、列内容、以及格子所需样式;故可以建立一个公共的方法,传入这些需要的参数即可
batchSetTitle方法
private void batchSetTitle(int columnIndex,String columnName,HSSFRow titleRow,HSSFCellStyle titleCellStyle){
HSSFCell cell1 = titleRow.createCell(columnIndex);
cell1.setCellStyle(titleCellStyle);
cell1.setCellValue(columnName);
}
创建样式的过程也极其类似,可以先定义好一个方法,一个样式对应一个方法,需要用时提前调用方法生成一个HSSFCellStyle样式,用到这个样式时使用这个对象即可
private HSSFCellStyle createTitleCellStyle(HSSFWorkbook excel){
HSSFCellStyle cellStyle = excel.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setWrapText(true);
HSSFFont font = excel.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
cellStyle.setFont(font);
return cellStyle;
}
private HSSFCellStyle createBodyCellStyle(HSSFWorkbook excel){
HSSFCellStyle cellStyle = excel.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setWrapText(true);
HSSFFont font = excel.createFont();
font.setBold(false);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
return cellStyle;
}
HSSFCellStyle titleCellStyle = createTitleCellStyle(excel);
HSSFCellStyle bodyCellStyle = createBodyCellStyle(excel);
5、合并单元格
mergList.add(new Record().set("firstRow", startIndex).set("lastRow", endIndex-1).set("firstCol", 0).set("lastCol", 0));
//合并单元格
addMergedRegion(mergList, sheet);
public void addMergedRegion(List<Record> mergList, HSSFSheet sheet) {
for (int i = 0; i < mergList.size(); i++) {
Record record = mergList.get(i);
CellRangeAddress region = new CellRangeAddress(record.getInt("firstRow"), record.getInt("lastRow"), record.getInt("firstCol"), record.getInt("lastCol"));
sheet.addMergedRegion(region);
}
}
6、下载文档
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode( classInfo.getStr("name")+"考勤记录.xls", "UTF-8"));
OutputStream os = response.getOutputStream();
excel.write(response.getOutputStream());
os.flush();
os.close();
完成!
附加一个写过的完整方法
@Override
public void exportListData(String classId, String searchSDate, String searchEDate, String elective, HttpServletResponse response) throws IOException {
Record classInfo = Db.findById("teas_jwgl_bjgl", "teas_jwgl_bjgl_id", classId);
long start = System.currentTimeMillis();
List<AttendNoonDateVo> listData = getListData(classId, searchSDate, searchEDate, elective);
long end = System.currentTimeMillis();
System.out.println("获取数据完成总耗时:"+(end-start)/1000 +"秒,数据大小:"+listData.size());
List<Record> mergList = new LinkedList<>();
// 创建excel
HSSFWorkbook excel = new HSSFWorkbook();
HSSFCellStyle titleCellStyle = createTitleCellStyle(excel);
HSSFCellStyle bodyCellStyle = createBodyCellStyle(excel);
//创建HSSFSheet对象
HSSFSheet sheet = excel.createSheet("考勤记录表");
HSSFRow head = sheet.createRow(0);
head.setHeightInPoints(35);
// 序号
sheet.setColumnWidth(0, 10*256);
batchSetTitle(0,"序号",head,titleCellStyle);
// 时间
sheet.setColumnWidth(1, 30*256);
batchSetTitle(1,"时间",head,titleCellStyle);
// 午别
sheet.setColumnWidth(2, 24*256);
batchSetTitle(2,"午别",head,titleCellStyle);
// 课程名称
sheet.setColumnWidth(3, 60*256);
batchSetTitle(3,"课程名称",head,titleCellStyle);
// 应到
sheet.setColumnWidth(4, 20*256);
batchSetTitle(4,"应到",head,titleCellStyle);
// 出勤
sheet.setColumnWidth(5, 20*256);
batchSetTitle(5,"出勤",head,titleCellStyle);
// 迟到
sheet.setColumnWidth(6, 20*256);
batchSetTitle(6,"迟到",head,titleCellStyle);
// 未到
sheet.setColumnWidth(7, 20*256);
batchSetTitle(7,"未到",head,titleCellStyle);
// 请假
sheet.setColumnWidth(8, 20*256);
batchSetTitle(8,"请假",head,titleCellStyle);
// 出勤率
sheet.setColumnWidth(9, 20*256);
batchSetTitle(9,"出勤率",head,titleCellStyle);
int startIndex = 1;
int endIndex = 1;
for (int i = 0; i < listData.size(); i++) {
AttendNoonDateVo attendNoonDateVo = listData.get(i);
String date = attendNoonDateVo.getDate();
List<AttendNoon> noons = attendNoonDateVo.getNoons();
for (int j = 0; j < noons.size(); j++) {
AttendNoon attendNoon = noons.get(j);
HSSFRow bodyrow = sheet.createRow(endIndex);
int p = 0;
createCell(bodyrow, bodyCellStyle,p++,i+1+"");
createCell(bodyrow, bodyCellStyle,p++,date);
createCell(bodyrow, bodyCellStyle,p++,attendNoon.getNoonText());
createCell(bodyrow, bodyCellStyle,p++,attendNoon.getCourseName());
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getShould()));
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getOnTime()));
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getLate()));
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getNot()));
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getLea()));
createCell(bodyrow, bodyCellStyle,p++,String.valueOf(attendNoon.getAttendRate()));
endIndex++;
}
if (endIndex -1 > startIndex){
mergList.add(new Record().set("firstRow", startIndex).set("lastRow", endIndex-1).set("firstCol", 0).set("lastCol", 0));
mergList.add(new Record().set("firstRow", startIndex).set("lastRow", endIndex-1).set("firstCol", 1).set("lastCol", 1));
}
startIndex = endIndex;
}
//合并单元格
addMergedRegion(mergList, sheet);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode( classInfo.getStr("name")+"考勤记录.xls", "UTF-8"));
OutputStream os = response.getOutputStream();
excel.write(response.getOutputStream());
os.flush();
os.close();
}
public void addMergedRegion(List<Record> mergList, HSSFSheet sheet) {
for (int i = 0; i < mergList.size(); i++) {
Record record = mergList.get(i);
CellRangeAddress region = new CellRangeAddress(record.getInt("firstRow"), record.getInt("lastRow"), record.getInt("firstCol"), record.getInt("lastCol"));
sheet.addMergedRegion(region);
}
}
private void batchSetTitle(int columnIndex,String columnName,HSSFRow titleRow,HSSFCellStyle titleCellStyle){
HSSFCell cell1 = titleRow.createCell(columnIndex);
cell1.setCellStyle(titleCellStyle);
cell1.setCellValue(columnName);
}
private void createCell(HSSFRow titleRow,HSSFCellStyle cellStcyle,int columnIndex,String columnName){
HSSFCell cell = titleRow.createCell(columnIndex);
cell.setCellStyle(cellStcyle);
cell.setCellValue(columnName);
}
private HSSFCellStyle createTitleCellStyle(HSSFWorkbook excel){
HSSFCellStyle cellStyle = excel.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setWrapText(true);
HSSFFont font = excel.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
cellStyle.setFont(font);
return cellStyle;
}
private HSSFCellStyle createBodyCellStyle(HSSFWorkbook excel){
HSSFCellStyle cellStyle = excel.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setWrapText(true);
HSSFFont font = excel.createFont();
font.setBold(false);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
return cellStyle;
}