废话不多说,直接上代码
添加pom.xml依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
具体导出代码
List<List<String>> heads = new ArrayList<>(); //表头信息
List<String> head1 = new ArrayList<>();
head1 .add("银行名称1");
head1 .add("期间新增项目");
head1 .add("期间新增担保总额");
head1 .add("期间新增解保项目数");
head1 .add("期间新增解保金额");
heads.add(head1);
List<String> head2 = new ArrayList<>();
head2 .add("银行名称2");
head2 .add("期间新增项目");
head2 .add("期间新增担保总额");
head2 .add("期间新增解保项目数");
head2 .add("期间新增解保金额");
heads.add(head2);
List<Map<String, Object>> list = new ArrayList<>(); //内容信息
Map<String,Object> test1 = new LinkedHashMap<>();//手动添加测试数据(可根据需要从数据库查询)
test1 .put("t1", 1);
test1 .put("t2", 2);
test1 .put("t3", 3);
test1 .put("t4", 4);
test1 .put("t5", 5);
list.add(test1);
Map<String,Object> test2 = new LinkedHashMap<>();
test2 .put("t1", 1);
test2 .put("t2", 2);
test2 .put("t3", 3);
test2 .put("t4", 4);
test2 .put("t5", 5);
list.add(test2);
Collection<Object> values;
List<List<Object>> resultData = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
values = list.get(i).values();
for (Object value : values) {
objects.add(value.toString());
}
resultData.add(objects);
}
// 导出excel
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
String date = sdf.format(new Date());
String fileName = URLEncoder.encode("导出EXCEl_" + date + ".xlsx", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
//添加表格样式
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new StyleHandler())
.registerWriteHandler(new LongestCellWidthHandler())
.registerWriteHandler(new LongestCellRowHandler())
.build();
//循环输出sheet和表头以及表格内容
for (int i = 0; i < resultData.size(); i++) {
WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(heads.get(i)).build();
excelWriter.write(resultData.get(i), sheet);
}
excelWriter.finish();
} catch (Exception e) {
log.error("导出失败" + e.getMessage(), e);
} finally {
try {
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}
注:response 是从请求接口带过来 HttpServletResponse response
表格样式类:
StyleHandler类(自定义样式)
public class StyleHandler extends AbstractVerticalCellStyleStrategy {
private static String fontName = "等线";
private static Integer[] columnIndex = {1, 2, 3, 4, 7, 8, 9};
@Override
protected WriteCellStyle headCellStyle(Head head) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
headWriteFont.setFontName(fontName);
headWriteFont.setColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
//自动换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
} else {
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
}
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteFont.setBold(true);
return headWriteCellStyle;
}
@Override
protected WriteCellStyle contentCellStyle(Head head) {
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
} else {
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
}
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName(fontName);
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setWrapped(true);
return contentWriteCellStyle;
}
}
LongestCellWidthHandler类(根据表头设置列宽)
public class LongestCellWidthHandler extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (Boolean.TRUE.equals(isHead)) {
int columnWidth = cell.getStringCellValue().length();
columnWidth = Math.max(columnWidth * 5, 25);
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
LongestCellRowHandler类(高度设置)
public class LongestCellRowHandler extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 500;
@Override
protected void setHeadColumnHeight(Row row, int i) {
}
@Override
protected void setContentColumnHeight(Row row, int i) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
int maxHeight = 3;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (Objects.requireNonNull(cell.getCellType() == CellType.STRING)) {
if (cell.getStringCellValue().contains("\n")) {
int length = cell.getStringCellValue().split("\n").length;
maxHeight = Math.max(maxHeight, length);
}
}
}
row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
}
}