自定义注解实现Excel 导出

概述

一个用自定义注解实现导出字段定义的工具实现。

1. 注解定义,定义导出Excel的字段

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface PoiExportField {// Label of the columnString label();// Order of the column,default 0,means the first columnint order() default 0;// If true, this field will be used to create subgroup rowsboolean subGroup() default false;// Width of the columnint width() default 20;// Alignment of the columnHorizontalAlignment align() default HorizontalAlignment.LEFT;
}

2. 实体类，使用注解定义导出字段，不导出的字段不用加注解

@Data
public class OrderVO {@PoiExportUtil.PoiExportField(label = "订单编号", order = 1, align = HorizontalAlignment.CENTER)private String orderNo;@PoiExportUtil.PoiExportField(label = "订单用户", order = 2, align = HorizontalAlignment.CENTER)private String orderUser;@PoiExportUtil.PoiExportField(label = "订单时间", order = 3, align = HorizontalAlignment.CENTER)private String orderTime;@PoiExportUtil.PoiExportField(label = "订单金额", order = 4, width = 15, align = HorizontalAlignment.RIGHT)private String orderAmount;private String orderDesc;private String orderRemark;private String orderPhone;private String orderZipCode;@PoiExportUtil.PoiExportField(label = "订单国家", subGroup = true)private String orderCountry;@PoiExportUtil.PoiExportField(label = "订单省份", subGroup = true)private String orderProvince;@PoiExportUtil.PoiExportField(label = "订单城市", order = 6)private String orderCity;@PoiExportUtil.PoiExportField(label = "详细地址", order = 7)private String orderAddressDetail;}

3. Excel导出工具类，注解定义放到工具类中，方便使用

public class PoiExportUtil {/*** Custom annotation for exporting Excel file*/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface PoiExportField {// Label of the columnString label();// Order of the column,default 0,means the first columnint order() default 0;// If true, this field will be used to create subgroup rowsboolean subGroup() default false;// Width of the columnint width() default 20;// Alignment of the columnHorizontalAlignment align() default HorizontalAlignment.LEFT;}/*** Export data to excel file** @param list     List of data* @param fileName File name* @param <T>      Type of data*/public <T> void exportToExcel(List<T> list, String fileName) {Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("Data");writeSheet(sheet, list, null, null, null);// Write to filetry (FileOutputStream fileOut = new FileOutputStream(fileName)) {workbook.write(fileOut);fileOut.flush();} catch (Exception e) {e.printStackTrace();}}/*** Write data to a sheet** @param sheet         Sheet* @param dataList      List of data* @param headerStyle   Header style* @param subGroupStyle Subgroup style* @param dataCellStyle Data cell style* @param <T>           Type of data*/public <T> void writeSheet(Sheet sheet, List<T> dataList, CellStyle headerStyle, CellStyle subGroupStyle, CellStyle dataCellStyle) {// If data list is empty, returnif (dataList == null || dataList.isEmpty()) {return;}// If styles are not provided, use default stylesif (headerStyle == null) {headerStyle = createDefaultHeaderStyle(sheet.getWorkbook());}if (subGroupStyle == null) {subGroupStyle = createDefaultSubGroupStyle(sheet.getWorkbook());}if (dataCellStyle == null) {dataCellStyle = createDefaultDataCellStyle(sheet.getWorkbook());}Field[] fields = dataList.get(0).getClass().getDeclaredFields();// Filter fields with PoiExportField annotationList<Field> annotatedFields = new ArrayList<>();// Filter fields with PoiExportField annotation and subGroup is true, for creating subgroup rowsList<Field> subGroupFields = new ArrayList<>();// Filter fields with PoiExportField annotation and sort them by order attributefor (Field field : fields) {PoiExportField annotation = field.getAnnotation(PoiExportField.class);if (annotation != null) {if (annotation.subGroup()) {subGroupFields.add(field);} else {annotatedFields.add(field);}}}// Sort fields by order attributeannotatedFields.sort(Comparator.comparingInt(field -> {PoiExportField annotation = field.getAnnotation(PoiExportField.class);return annotation.order();}));//annotated fields is empty, returnif (annotatedFields.isEmpty()) {return;}// Create header rowcreateHeaderRow(sheet, annotatedFields, headerStyle);// Create data rowscreateSheetWithData(sheet, dataList, annotatedFields, subGroupFields, subGroupStyle, dataCellStyle);}/*** Create header row** @param sheet           Sheet* @param annotatedFields List of annotated fields* @param headerStyle     Header style*/private void createHeaderRow(Sheet sheet, List<Field> annotatedFields, CellStyle headerStyle) {int lastRowNum = sheet.getLastRowNum();Row headerRow = sheet.createRow(lastRowNum + 1);for (int i = 0; i < annotatedFields.size(); i++) {Field field = annotatedFields.get(i);PoiExportField annotation = field.getAnnotation(PoiExportField.class);Cell headerCell = headerRow.createCell(i);headerCell.setCellValue(annotation.label());headerCell.setCellStyle(headerStyle);// Set column widthsheet.setColumnWidth(i, annotation.width() * 256);}}/*** Create data rows** @param sheet           Sheet* @param dataList        List of data* @param annotatedFields List of annotated fields* @param subGroupFields  List of subgroup fields* @param subGroupStyle   Subgroup style* @param dataCellStyle   Data cell style* @param <T>             Type of data*/private <T> void createSheetWithData(Sheet sheet, List<T> dataList, List<Field> annotatedFields, List<Field> subGroupFields, CellStyle subGroupStyle, CellStyle dataCellStyle) {String lastSubGroupValue = null;int rowIndex = sheet.getLastRowNum() + 1;for (T data : dataList) {// Create subgroup rowif (subGroupFields != null && !subGroupFields.isEmpty()) {String currentSubGroupValue = getSubGroupValue(data, subGroupFields);if (!currentSubGroupValue.equals(lastSubGroupValue)) {Row subGroupRow = sheet.createRow(rowIndex++);Cell subGroupCell = subGroupRow.createCell(0);subGroupCell.setCellValue(currentSubGroupValue);CellRangeAddress mergeRegion = new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, annotatedFields.size() - 1);sheet.addMergedRegion(mergeRegion);//CellRangeAddress  mergeRegion = sheet.getMergedRegion(sheet.getNumMergedRegions() - 1);RegionUtil.setBorderBottom(BorderStyle.THIN, mergeRegion, sheet);RegionUtil.setBorderTop(BorderStyle.THIN, mergeRegion, sheet);RegionUtil.setBorderLeft(BorderStyle.THIN, mergeRegion, sheet);RegionUtil.setBorderRight(BorderStyle.THIN, mergeRegion, sheet);subGroupCell.setCellStyle(subGroupStyle);lastSubGroupValue = currentSubGroupValue;}}// Create data rowRow row = sheet.createRow(rowIndex++);for (int j = 0; j < annotatedFields.size(); j++) {Field field = annotatedFields.get(j);PoiExportField annotation = field.getAnnotation(PoiExportField.class);Cell cell = row.createCell(j);try {// Get field value from getter methodfield.setAccessible(true);String getterName = "get" + Character.toUpperCase(field.getName().charAt(0)) + field.getName().substring(1);Method getterMethod = data.getClass().getMethod(getterName);Object value = getterMethod.invoke(data);if (value != null) {cell.setCellValue(value.toString());}// Set cell style alignmentCellStyle cellStyle = sheet.getWorkbook().createCellStyle();cellStyle.cloneStyleFrom(dataCellStyle);cellStyle.setAlignment(annotation.align());cell.setCellStyle(cellStyle);} catch (Exception e) {e.printStackTrace();}}}}/*** Get subgroup value** @param data           Data* @param subGroupFields List of subgroup fields* @param <T>            Type of data* @return Subgroup value*/private <T> String getSubGroupValue(T data, List<Field> subGroupFields) {StringBuilder subGroupValue = new StringBuilder();for (Field field : subGroupFields) {try {field.setAccessible(true);Object value = field.get(data);if (value != null) {subGroupValue.append(value.toString()).append(" ");}} catch (IllegalAccessException e) {e.printStackTrace();}}return subGroupValue.toString().trim();}/*** Create and return default header style** @param workbook Workbook* @return CellStyle*/private CellStyle createDefaultHeaderStyle(Workbook workbook) {Font fontBold = workbook.createFont();fontBold.setBold(true);fontBold.setFontHeightInPoints((short) 12);CellStyle headerStyle = workbook.createCellStyle();headerStyle.setBorderTop(BorderStyle.THIN);headerStyle.setBorderBottom(BorderStyle.THIN);headerStyle.setBorderLeft(BorderStyle.THIN);headerStyle.setBorderRight(BorderStyle.THIN);headerStyle.setAlignment(HorizontalAlignment.CENTER);headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);headerStyle.setWrapText(true);headerStyle.setFont(fontBold);return headerStyle;}/*** Create and return default subgroup style** @param workbook Workbook* @return CellStyle*/private CellStyle createDefaultSubGroupStyle(Workbook workbook) {// Create and return default subgroup styleFont fontBold = workbook.createFont();fontBold.setBold(true);fontBold.setFontHeightInPoints((short) 11);CellStyle subGroupStyle = workbook.createCellStyle();subGroupStyle.setBorderTop(BorderStyle.THIN);subGroupStyle.setBorderBottom(BorderStyle.THIN);subGroupStyle.setBorderLeft(BorderStyle.THIN);subGroupStyle.setBorderRight(BorderStyle.THIN);subGroupStyle.setAlignment(HorizontalAlignment.CENTER);subGroupStyle.setVerticalAlignment(VerticalAlignment.CENTER);subGroupStyle.setFont(fontBold);return subGroupStyle;}/*** Create and return default data cell style** @param workbook Workbook* @return CellStyle*/private CellStyle createDefaultDataCellStyle(Workbook workbook) {// Create and return default data cell styleCellStyle dataCellStyle = workbook.createCellStyle();Font fontBold = workbook.createFont();fontBold.setBold(true);fontBold.setFontHeightInPoints((short) 11);dataCellStyle.setBorderTop(BorderStyle.THIN);dataCellStyle.setBorderBottom(BorderStyle.THIN);dataCellStyle.setBorderLeft(BorderStyle.THIN);dataCellStyle.setBorderRight(BorderStyle.THIN);dataCellStyle.setFont(fontBold);return dataCellStyle;}
}

4. 测试

public class PoiExportUtilTest {@Testpublic void exportToExcel() {PoiExportUtil poiExportUtil = new PoiExportUtil();List<OrderVO> orderVOList = generateOrders();poiExportUtil.exportToExcel(orderVOList, "order.xlsx");}public List<OrderVO> generateOrders() {String[] COUNTRIES = {"China", "Japan", "Canada"};Random RANDOM = new Random();List<OrderVO> orders = new ArrayList<>();for (int i = 0; i < 30; i++) {String orderNo = "OrderNo" + (i + 1);String orderUser = "User" + (i + 1);String orderTime = "Time" + (i + 1);String orderAmount = RANDOM.nextInt(10000) + ".00";String orderDesc = "Desc" + (i + 1);String orderRemark = "Remark" + (i + 1);String orderPhone = "Phone" + (i + 1);String orderZipCode = "ZipCode" + (i + 1);String orderCountry = COUNTRIES[RANDOM.nextInt(COUNTRIES.length)];String orderProvince = "Province" + (i + 1) % 3;String orderCity = "City" + (i + 1);String orderAddressDetail = "AddressDetail" + (i + 1);OrderVO order = createOrder(orderNo, orderUser, orderTime, orderAmount, orderDesc, orderRemark, orderPhone,orderZipCode, orderCountry, orderProvince, orderCity, orderAddressDetail);orders.add(order);}// Sort by orderCountry and orderTimereturn orders.stream().sorted(Comparator.comparing(OrderVO::getOrderCountry).thenComparing(OrderVO::getOrderProvince).thenComparing(OrderVO::getOrderTime)).collect(Collectors.toList());}private OrderVO createOrder(String orderNo, String orderUser, String orderTime, String orderAmount,String orderDesc, String orderRemark, String orderPhone, String orderZipCode,String orderCountry, String orderProvince, String orderCity, String orderAddressDetail) {OrderVO order = new OrderVO();order.setOrderNo(orderNo);order.setOrderUser(orderUser);order.setOrderTime(orderTime);order.setOrderAmount(orderAmount);order.setOrderDesc(orderDesc);order.setOrderRemark(orderRemark);order.setOrderPhone(orderPhone);order.setOrderZipCode(orderZipCode);order.setOrderCountry(orderCountry);order.setOrderProvince(orderProvince);order.setOrderCity(orderCity);order.setOrderAddressDetail(orderAddressDetail);return order;}
}

参考源代码