引言
最近了不起做的需求中有一个需求是要求导出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工具类。
过程中也探讨了一些设计模式和思想,不过真正代码实践的时候和想的还是有一定差距的。
不要因为技术而技术,更多的是为了业务服务。