springboot导出（POI）

POI官方文档

引入依赖

        <!--POI--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency>

定义注解

我们结合自定义注解，让导出使用起来更方便简洁且更容易扩展。

@ExcelField

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {/*** 字段名*/String name() default "";/*** 字段顺序*/int order() default 1;/*** 单元格宽度*/int width() default 100;/*** 日期格式*/String dateFormat() default "yyyy-MM-dd HH:mm:ss";
}

@ExcelHeader

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelHeader {/*** 文件名称* @return*/String fileName() default "导出文件";/*** sheet页名称* @return*/String sheetName() default "sheet1";/*** 表头是否加粗* @return*/boolean headBold() default false;/*** 表头颜色* @return*/IndexedColors headColor() default IndexedColors.GREEN;
}

编写POI导出工具类

public class ExcelExportUtil {/** @description: 导出 <br>* @create: 2023/9/12 16:28 <br>* @param response* @param data* @param clazz* @return void*/public static<T> void export(HttpServletResponse response, List<T> data, Class<T> clazz){if(ObjectUtils.isEmpty(response) || ObjectUtils.isEmpty(data)){return;}//解析表头ExcelHeader excelHeader = getExcelHeader(clazz);if(ObjectUtils.isEmpty(excelHeader)){return;}//解析字段List<String> keyList = new ArrayList<>();List<ExcelField> excelFieldList = new ArrayList<>();getExcelFields(clazz, keyList, excelFieldList);if(ObjectUtils.isEmpty(keyList)){return;}//创建workbook，SXSSFWorkbook支持大数据量导出Workbook workbook = new SXSSFWorkbook();//创建sheetSheet sheet = workbook.createSheet(excelHeader.sheetName());//创建表头Row row = createHeader(workbook, sheet, excelHeader, excelFieldList);//写入数据setData(data, sheet, keyList, excelFieldList);//输出文件try {String fileName = URLEncoder.encode(excelHeader.fileName(), "UTF-8");response.setContentType("application/octet-stream");response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));response.setHeader("filename", fileName);workbook.write(response.getOutputStream());} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}finally {try {workbook.close();} catch (IOException e) {e.printStackTrace();}}}/** @description: 设置数据 <br>* @create: 2023/9/13 9:33 <br>* @param data* @param sheet* @param keyList* @return void*/private static<T> void setData(List<T> data, Sheet sheet, List<String> keyList, List<ExcelField> excelFieldList){List<Map<String, Object>> dataList = convertDataToJson(data);Map<String, Object> rowData;String dataKey;Object value;ExcelField excelField;for (int i = 0; i < dataList.size(); i++) {Row row = sheet.createRow(i+1);rowData = dataList.get(i);for (int j = 0; j < keyList.size(); j++) {dataKey = keyList.get(j);value = rowData.get(dataKey);if(ObjectUtils.isEmpty(value)){continue;}Cell cell = row.createCell(j);cell.setCellValue(value.toString());}}}/** @description: 创建表头 <br>* @create: 2023/9/12 17:28 <br>* @param workbook* @param sheet* @param excelFieldList* @return org.apache.poi.ss.usermodel.Row*/private static Row createHeader(Workbook workbook, Sheet sheet, ExcelHeader excelHeader,List<ExcelField> excelFieldList){//创建行Row row = sheet.createRow(0);//创建行样式CellStyle cellStyle = workbook.createCellStyle();//单元格颜色cellStyle.setFillForegroundColor(excelHeader.headColor().getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//字体Font font =  workbook.createFont();font.setBold(excelHeader.headBold());font.setFontName("宋体");cellStyle.setFont(font);for (int i = 0; i < excelFieldList.size(); i++) {ExcelField excelField = excelFieldList.get(i);Cell cell = row.createCell(i);cell.setCellValue(excelField.name());cell.setCellStyle(cellStyle);sheet.setColumnWidth(i+1, excelField.width() * 10 );}return row;}/** @description: 获取excel头注解信息 <br>* @create: 2023/9/12 10:28 <br>* @param clazz* @return com.springboot.demo.base.annotation.ExcelHeader*/private static<T> ExcelHeader getExcelHeader(Class<T> clazz){ExcelHeader excelHeader = clazz.getAnnotation(ExcelHeader.class);return excelHeader;}/** @description: 获取excel字段注解信息 <br>* @create: 2023/9/12 10:31 <br>* @param clazz* @return java.util.List<com.springboot.demo.base.annotation.ExcelField>*/private static<T> void getExcelFields(Class<T> clazz, List<String> keyList, List<ExcelField> excelFieldList){Field[] fields = clazz.getDeclaredFields();if(ObjectUtils.isEmpty(fields)){return;}Map<String, String> keyMap = new HashMap<>();for(Field field : fields){ExcelField excelField = field.getAnnotation(ExcelField.class);if(ObjectUtils.isNotEmpty(excelField)){keyMap.put(excelField.name(), field.getName());excelFieldList.add(excelField);}}if(ObjectUtils.isNotEmpty(excelFieldList)){excelFieldList = excelFieldList.stream().sorted(Comparator.comparing(ExcelField::order)).collect(Collectors.toList());}for (ExcelField excelField : excelFieldList) {keyList.add(keyMap.get(excelField.name()));}}/** @description: 将数据转未json <br>* @create: 2023/9/13 10:00 <br>* @param data* @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>*/private static<T> List<Map<String, Object>> convertDataToJson(List<T> data){ObjectMapper objectMapper = new ObjectMapper();//日期格式objectMapper.setDateFormat(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));//设置时区objectMapper.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));//序列化-忽略null值的属性objectMapper.setSerializationInclusion(Include.NON_NULL);//序列化-允许序列化空对象objectMapper.disable(SerializationFeature.FAIL_ON_EMPTY_BEANS);List<Map<String, Object>> dataList = objectMapper.convertValue(data, List.class);return dataList;}
}

测试

Test.java

@Data
@ExcelHeader(fileName = "导出文件.xlsx")
public class Test {@ExcelField(name = "字段1", width = 500, order = 1)private String field1 = "撒大声地撒分都是";@ExcelField(name = "字段2", width = 400, order = 2)private Integer field2 = 11;@ExcelField(name = "字段3", width = 300, order = 5)private Double field3;@ExcelField(name = "字段4", width = 700, order = 10)private BigDecimal field4 = new BigDecimal("12312312312.222");@ExcelField(name = "字段5", width = 600, order = 3)private Date field5 = new Date();
}

TestController.java

@Api(tags = "测试")
@RestController
@RequestMapping("/test")
public class TestController {@ApiOperation("test")@PostMapping("/test")public void test(HttpServletResponse response){Test test = new Test();List<Test> list = new ArrayList<>();for (int i = 0; i < 10000; i++) {list.add(test);}ExcelExportUtil.export(response, list, Test.class);}
}

输出结果