引言
最近了不起做的需求中有一个需求是要求导出Excel表格，有大约十几张表需要导出吧。
那么问题来了，要是你来实现，你会怎么做？
简易实现导出Excel
一般快速导出可以这么实现：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelExportExample {
public static void main(String[] args) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
// 创建表头行
Row headerRow = sheet.createRow(0);
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("姓名");
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("年龄");
// 添加数据行
Row dataRow1 = sheet.createRow(1);
Cell dataCell1 = dataRow1.createCell(0);
dataCell1.setCellValue("张三");
Cell dataCell2 = dataRow1.createCell(1);
dataCell2.setCellValue(25);
Row dataRow2 = sheet.createRow(2);
Cell dataCell3 = dataRow2.createCell(0);
dataCell3.setCellValue("李四");
Cell dataCell4 = dataRow2.createCell(1);
dataCell4.setCellValue(30);
// 导出Excel文件
try (FileOutputStream outputStream = new FileOutputStream("example.xlsx")) {
workbook.write(outputStream);
System.out.println("Excel导出成功！");
} catch (IOException e) {
System.out.println("导出Excel文件时出现错误：" + e.getMessage());
} finally {
try {
workbook.close();
} catch (IOException e) {
System.out.println("关闭Workbook时出现错误：" + e.getMessage());
}
}
}
}
那么思考一下，了不起要做好多的重复工作啊，我还要来设置表头，装填每个行每个字段的数据。
构思导出Excel工具类
1.使用工厂设计模式
在导出不同的表格时，我们一定会创建表格，那么我们可以将创建这步工厂化。
首先，定义一个抽象的工厂类 WorkbookFactory，用于创建工作簿对象：
publicabstractclassWorkbookFactory{
publicabstract Workbook createWorkbook();
publicabstract Sheet createSheet(Workbook workbook, String sheetName);
publicabstractvoidcreateHeaderRow(Sheet sheet);
publicabstractvoidaddDataRows(Sheet sheet);
}
然后，创建具体的工厂类 ApachePOIWorkbookFactory，继承自 WorkbookFactory，实现具体的工作簿创建和表格操作方法：
publicclassApachePOIWorkbookFactoryextendsWorkbookFactory{
@Override
public Workbook createWorkbook(){
returnnew XSSFWorkbook();
}
@Override
public Sheet createSheet(Workbook workbook, String sheetName){
return workbook.createSheet(sheetName);
}
@Override
publicvoidcreateHeaderRow(Sheet sheet){
// 默认实现为空，具体的子类可以重写该方法来设置表头样式和内容
}
@Override
publicvoidaddDataRows(Sheet sheet){
// 默认实现为空，具体的子类可以重写该方法来添加数据行和设置样式
}
}
接下来，创建一个具体的工厂子类 CustomizedWorkbookFactory，继承自 ApachePOIWorkbookFactory，用于自定义不同表格的表头、样式和数据：
publicclassCustomizedWorkbookFactoryextendsApachePOIWorkbookFactory{
@Override
publicvoidcreateHeaderRow(Sheet sheet){
Row headerRow = sheet.createRow(0);
CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
Font headerFont = sheet.getWorkbook().createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);
// 自定义设置表头样式和内容
Cell cell1 = headerRow.createCell(0);
cell1.setCellValue("姓名");
cell1.setCellStyle(headerStyle);
Cell cell2 = headerRow.createCell(1);
cell2.setCellValue("年龄");
cell2.setCellStyle(headerStyle);
}
@Override
publicvoidaddDataRows(Sheet sheet){
// 自定义添加数据行和样式
Row dataRow1 = sheet.createRow(1);
CellStyle dataCellStyle = sheet.getWorkbook().createCellStyle();
dataCellStyle.setBorderBottom(BorderStyle.THIN);
dataCellStyle.setBorderTop(BorderStyle.THIN);
dataCellStyle.setBorderLeft(BorderStyle.THIN);
dataCellStyle.setBorderRight(BorderStyle.THIN);
Cell cell1 = dataRow1.createCell(0);
cell1.setCellValue("张三");
cell1.setCellStyle(dataCellStyle);
Cell cell2 = dataRow1.createCell(1);
cell2.setCellValue(25);
cell2.setCellStyle(dataCellStyle);
}
}
最后，在导出数据时，使用工厂类的方法创建工作簿并自定义表头和数据：
publicclassExcelExportExample{
publicstaticvoidmain(String[] args){
WorkbookFactory workbookFactory = new CustomizedWorkbookFactory();
Workbook workbook = workbookFactory.createWorkbook();
Sheet sheet = workbookFactory.createSheet(workbook, "Sheet1");
workbookFactory.createHeaderRow(sheet);
workbookFactory.addDataRows(sheet);
// 导出工作簿...
}
}
在上述示例中，通过继承和重写工厂类的方法，我们可以自定义不同表格的表头样式、内容、数据行以及其他样式设置。
这种方式我们可以根据需要灵活地定制不同的表格导出功能。
2. 使用注解
在前面的导出方法中，了不起觉得要把表头传递进去也很费事。
可以加个注解在我导出的实体对象字段上，我做好表头设置，标记好字段就可以了。
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 表头名称
*/
String name();
/**
* 排序
*/
int sort() default 0;
}
3. 使用反射
每个字段我也不想去自己填了，用反射的方式找到那个具体的字段。
/**
* 创建表格主体
* @param sheet Sheet
* @param data 数据
*/
private static void createBody(Sheet sheet, List<?> data) {
for (int i = 0; i < data.size(); i++) {
Row row = sheet.createRow(i + 1);
List<ExcelHeader> headers = getHeaders(data.get(i).getClass());
for (int j = 0; j < headers.size(); j++) {
Cell cell = row.createCell(j);
try {
Field field = data.get(i).getClass().getDeclaredField(headers.get(j).getField());
field.setAccessible(true);
Object value = field.get(data.get(i));
if (value != null) {
cell.setCellValue(value.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
代码实现
在我这次的具体开发中，Excel格式都是一样的，故我这里就不用工厂方法获取Book了。
1. 导入依赖
Spring Boot项目中添加Apache POI和Web Starter依赖。
在 pom.xml文件中添加以下依赖：
<!-- Apache POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- Spring Boot Starter Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
2. 工具类封装
public class ExcelUtils {
/**
* 导出Excel
* @param response HttpServletResponse
* @param fileName 文件名
* @param data 数据
* @throws IOException
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<?> data) throws IOException {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
OutputStream outputStream = response.getOutputStream();
exportExcel(data, outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 导出Excel
* @param data 数据
* @param outputStream OutputStream
* @throws IOException
*/
public static void exportExcel(List<?> data, OutputStream outputStream) throws IOException {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
createHeader(sheet, data.get(0).getClass());
createBody(sheet, data);
workbook.write(outputStream);
}
/**
* 创建表头
* @param sheet Sheet
* @param clazz 类
*/
private static void createHeader(Sheet sheet, Class<?> clazz) {
Row row = sheet.createRow(0);
List<ExcelHeader> headers = getHeaders(clazz);
for (int i = 0; i < headers.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(headers.get(i).getName());
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
Font font = sheet.getWorkbook().createFont();
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
}
/**
* 创建表格主体
* @param sheet Sheet
* @param data 数据
*/
private static void createBody(Sheet sheet, List<?> data) {
for (int i = 0; i < data.size(); i++) {
Row row = sheet.createRow(i + 1);
List<ExcelHeader> headers = getHeaders(data.get(i).getClass());
for (int j = 0; j < headers.size(); j++) {
Cell cell = row.createCell(j);
try {
Field field = data.get(i).getClass().getDeclaredField(headers.get(j).getField());
field.setAccessible(true);
Object value = field.get(data.get(i));
if (value != null) {
cell.setCellValue(value.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 获取表头信息
* @param clazz 类
* @return 表头信息
*/
private static List<ExcelHeader> getHeaders(Class<?> clazz) {
List<Field> fields = new ArrayList<>();
Class<?> tempClass = clazz;
while (tempClass != null) {
Field[] declaredFields = tempClass.getDeclaredFields();
for (Field field : declaredFields) {
fields.add(field);
}
tempClass = tempClass.getSuperclass();
}
Map<String, Field> fieldMap = fields.stream().collect(Collectors.toMap(Field::getName, field -> field));
List<ExcelHeader> headers = new ArrayList<>();
for (Field field : fields) {
Annotation[] annotations = field.getDeclaredAnnotations();
for (Annotation annotation : annotations) {
if (annotation instanceof ExcelField) {
ExcelField excelField = (ExcelField) annotation;
ExcelHeader header = new ExcelHeader();
header.setName(excelField.name());
header.setField(field.getName());
headers.add(header);
}
}
}
return headers.stream().sorted((h1, h2) ->
Integer.compare(fieldMap.get(h1.getField()).
getAnnotation(ExcelField.class).sort(), fieldMap.get(h2.getField()).
getAnnotation(ExcelField.class).sort())).collect(Collectors.toList());
}
/**
* 表头信息
*/
private static class ExcelHeader {
private String name;
private String field;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
}
}
3. 注解使用
直接使用在你的
@ApiModel("活动记录")
@Data
public class ActiveLogDto {
@JSONField(name = "id")
private String id;
@ApiModelProperty("活动id")
private String activeId;
@ApiModelProperty("活动名称")
private String activeName;
@ApiModelProperty("类型")
private String type;
@ApiModelProperty("用户角色")
private Integer userAwardType;
@ApiModelProperty("用户角色")
@ExcelField(name = "奖励类型", sort = 1)
private String userAwardTypeDesc;
@ApiModelProperty("用户邮箱")
@ExcelField(name = "用户邮箱", sort = 2)
private String userEmail;
@ApiModelProperty("记录")
@ExcelField(name = "动作", sort = 3)
private String record;
@ApiModelProperty("订单记录")
@ExcelField(name = "订单奖励", sort = 8)
private String orderRecord;
@ApiModelProperty("下单时间")
@ExcelField(name = "下单时间", sort = 7)
private String orderTime;
@ApiModelProperty("订单编号")
@ExcelField(name = "订单ID", sort = 5)
private String orderNo;
@ApiModelProperty("订单金额")
@ExcelField(name = "下单金额", sort = 6)
private BigDecimal orderAmount;
@ApiModelProperty("订单币种:RMB...")
@ExcelField(name = "下单币种", sort = 7)
private String orderCurrency;
@ApiModelProperty("邮箱")
@ExcelField(name = "邮箱", sort = 11)
private String referrerEmail;
@ApiModelProperty("推荐码")
@ExcelField(name = "推荐码", sort = 4)
private String referrerCode;
@ApiModelProperty("金额")
@ExcelField(name = "金额", sort = 9)
private BigDecimal orderAwardAmount;
@ApiModelProperty("时间")
@ExcelField(name = "时间", sort = 10)
private String orderAwardSendTime;
@ApiModelProperty("createTime")
@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
}
4. 代码具体调用
public Result exportLogExcel(String id, HttpServletResponse httpServletResponse) {
List<ActiveLogDto> dtoList = this.getListById(id);
if (excelList.isEmpty()) {
throw new BusinessException("xxx", messageSource.getMessage("xxx", null,
LocaleContextHolder.getLocale()));
}
String fileName = excelList.get(0).getxxx_name();
try {
ExcelUtils.exportExcel(httpServletResponse,fileName + "操作记录",excelList);
} catch (IOException e) {
log.error("导出异常：",e);
throw new BusinessException("xxx", messageSource.getMessage("xxx", null,
LocaleContextHolder.getLocale()));
}
return Result.success();
}
5. 配合前端页面导出
后端接口
@GetMapping("/list/export/{id}")
@ApiOperation(notes = "导出记录", value = "导出记录", httpMethod = "GET")
public Result listExportLogs(@PathVariable String id, HttpServletResponse httpServletResponse) {
return xxService.exportLogExcel(id,httpServletResponse);
}
前端代码
<template>
<div>
<button @click="exportLogs">导出记录</button>
</div>
</template>
<script>
export default {
methods: {
exportLogs() {
const id = "your_id"; // 替换为实际的ID
window.location.href = `域名/list/export/${id}`;
},
},
};
</script>
导出后：
总结
了不起就工作中的一个小需求进行实现，封装了一个简易的导出excel工具类。
过程中也探讨了一些设计模式和思想，不过真正代码实践的时候和想的还是有一定差距的。
不要因为技术而技术，更多的是为了业务服务。