easyExcel在线文档https://alibaba-easyexcel.github.io/docs/current/
直接上菜
一、业务层:
@Override
public void downloadSummaryTable(String classId, HttpServletResponse response) throws UnsupportedEncodingException {
if (StrKit.isBlank(classId)) {
return;
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("导出", "utf-8") + ".xlsx";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
try {
EasyExcel.write(response.getOutputStream()).head(getHeader(classId)).sheet("模板")
.registerWriteHandler(new CustomizeColumnWidth())
.registerWriteHandler(new CustomizeColumnWidth().setStyleStrategy())
.doWrite(getAttendanceData(classId));
} catch (Exception e) {
throw new RuntimeException("导出模板异常");
}
}
二、设置动态表头
private List<List<String>> getHeader(String classId) {
Record classInfo = Db.findFirst(Db.getSqlPara(SQL_KEY + "findClassTime", Kv.create().set("classId", classId)));
String className = classInfo.getStr("name");
Date sdate = classInfo.getDate("sdate");
Date edate = classInfo.getDate("edate");
long betweenDay = DateUtil.between(sdate, edate, DateUnit.DAY);
final String mainHead = className + "出勤汇总表";
List<String> head1 = new ArrayList<>();
head1.add(mainHead);
head1.add("学号");
head1.add("学号");
List<String> head2 = new ArrayList<>();
head2.add(mainHead);
head2.add("姓名");
head2.add("姓名");
List<List<String>> resHead = new ArrayList<>();
resHead.add(head1);
resHead.add(head2);
boolean time = true;
for (int i = 0; i < (betweenDay + 1) * 2; i++) {
List<String> head = new ArrayList<>();
head.add(mainHead);
double day = Math.floor(i / 2);
DateTime dateTime = DateUtil.offsetDay(sdate, (int) day);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String dayTime = format.format(dateTime);
head.add(dayTime);
if (time) {
head.add("上午");
time = false;
} else {
head.add("下午");
time = true;
}
resHead.add(head);
}
List<String> head3 = new ArrayList<>();
head3.add(mainHead);
head3.add("出勤汇总");
head3.add("出勤汇总");
resHead.add(head3);
List<String> head4 = new ArrayList<>();
head4.add(mainHead);
head4.add("备注");
head4.add("备注");
resHead.add(head4);
return resHead;
}
三、设置数据
此处简化 反正表格内容数据格式也为List<List<String>>
List<List<String>> total = new ArrayList<>();
for(Reocrd record:list){
List<String> list = new ArrayList<>();
list.add(record.getStr("name");
}
return total;
四、表格自定义格式
public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
/**
* 表格动态设置列宽
*
*/
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//判断是否为表头
if (isHead && cell.getRowIndex() == 1) {
int columnWidth;
int cellIndex = cell.getColumnIndex();
int summarySerial = writeSheetHolder.getHead().size() - 2;
if (cellIndex == 0) { //学号
columnWidth = 13;
} else if (cellIndex == 1) { //姓名
columnWidth = 12;
} else if (cellIndex == summarySerial) { //出勤汇总
columnWidth = 26;
} else {
columnWidth = 9;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
//20倍short值为表格中的磅值
writeSheetHolder.getSheet().setDefaultRowHeight((short) 512);
}
}
/**
* 自定义样式(表头、内容的字体、背景、样式)
* @return HorizontalCellStyleStrategy
*/
public HorizontalCellStyleStrategy setStyleStrategy(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 背景色, 设置为浅灰
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 14);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 设置 自动换行
contentWriteCellStyle.setWrapped(true);
// 设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
五、效果图