自定义注解实现Excel 导出

概述

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

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

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface PoiExportField {
    // Label of the column
    String label();

    // Order of the column,default 0,means the first column
    int order() default 0;

    // If true, this field will be used to create subgroup rows
    boolean subGroup() default false;

    // Width of the column
    int width() default 20;

    // Alignment of the column
    HorizontalAlignment 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 column
        String label();

        // Order of the column,default 0,means the first column
        int order() default 0;

        // If true, this field will be used to create subgroup rows
        boolean subGroup() default false;

        // Width of the column
        int width() default 20;

        // Alignment of the column
        HorizontalAlignment 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 file
        try (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, return
        if (dataList == null || dataList.isEmpty()) {
            return;
        }

        // If styles are not provided, use default styles
        if (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 annotation
        List<Field> annotatedFields = new ArrayList<>();
        // Filter fields with PoiExportField annotation and subGroup is true, for creating subgroup rows
        List<Field> subGroupFields = new ArrayList<>();

        // Filter fields with PoiExportField annotation and sort them by order attribute
        for (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 attribute
        annotatedFields.sort(Comparator.comparingInt(field -> {
            PoiExportField annotation = field.getAnnotation(PoiExportField.class);
            return annotation.order();
        }));

        //annotated fields is empty, return
        if (annotatedFields.isEmpty()) {
            return;
        }
        // Create header row
        createHeaderRow(sheet, annotatedFields, headerStyle);

        // Create data rows
        createSheetWithData(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 width
            sheet.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 row
            if (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 row
            Row 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 method
                    field.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 alignment
                    CellStyle 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 style
        Font 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 style
        CellStyle 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 {
    @Test
    public 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 orderTime
        return 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;
    }
}

参考源代码

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java注解是一种元数据,它可以为类、方法、字段等元素添加额外的信息。在Java中,可以使用自定义注解和反射来实现导入导出Excel文档。 首先,定义一个自定义注解,用于标记需要导出的实体类的字段: ```java @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { /** * 列名 */ public String name(); /** * 顺序 */ public int order(); } ``` 然后,在实体类的字段上添加该注解: ```java public class User { @ExcelField(name = "姓名", order = 1) private String name; @ExcelField(name = "年龄", order = 2) private int age; // 省略其他字段和方法 } ``` 接着,定义一个工具类,用于读取和写入Excel文档: ```java public class ExcelUtil { /** * 从Excel中读取数据 */ public static <T> List<T> readFromExcel(InputStream is, Class<T> clazz) { List<T> list = new ArrayList<>(); try { Workbook workbook = WorkbookFactory.create(is); Sheet sheet = workbook.getSheetAt(0); Map<Integer, String> headers = getHeaders(sheet.getRow(0)); for (int i = 1; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); T obj = clazz.newInstance(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String value = getValue(cell); String fieldName = headers.get(j); Field field = clazz.getDeclaredField(fieldName); field.setAccessible(true); setValue(obj, field, value); } list.add(obj); } } catch (Exception e) { e.printStackTrace(); } return list; } /** * 写入数据到Excel中 */ public static <T> void writeToExcel(List<T> list, OutputStream os) { try { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); Row header = sheet.createRow(0); Map<String, Integer> fields = getFields(list.get(0).getClass()); List<String> fieldNames = new ArrayList<>(fields.keySet()); Collections.sort(fieldNames); for (int i = 0; i < fieldNames.size(); i++) { String fieldName = fieldNames.get(i); Cell cell = header.createCell(i); cell.setCellValue(fields.get(fieldName)); } for (int i = 0; i < list.size(); i++) { Row row = sheet.createRow(i + 1); T obj = list.get(i); for (int j = 0; j < fieldNames.size(); j++) { String fieldName = fieldNames.get(j); Field field = obj.getClass().getDeclaredField(fieldName); field.setAccessible(true); Object value = field.get(obj); Cell cell = row.createCell(j); cell.setCellValue(value.toString()); } } workbook.write(os); } catch (Exception e) { e.printStackTrace(); } } /** * 获取Excel中的列名 */ private static Map<Integer, String> getHeaders(Row row) { Map<Integer, String> headers = new HashMap<>(); for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); String value = getValue(cell); headers.put(i, value); } return headers; } /** * 获取实体类中的字段名和顺序 */ private static <T> Map<String, Integer> getFields(Class<T> clazz) { Map<String, Integer> fields = new HashMap<>(); Field[] declaredFields = clazz.getDeclaredFields(); for (Field field : declaredFields) { if (field.isAnnotationPresent(ExcelField.class)) { ExcelField excelField = field.getAnnotation(ExcelField.class); fields.put(field.getName(), excelField.order()); } } return fields; } /** * 设置实体类中的字段值 */ private static <T> void setValue(T obj, Field field, String value) throws Exception { String typeName = field.getType().getName(); if ("int".equals(typeName)) { field.set(obj, Integer.parseInt(value)); } else if ("java.lang.String".equals(typeName)) { field.set(obj, value); } // 省略其他类型的判断 } /** * 获取单元格中的值 */ private static String getValue(Cell cell) { String value = ""; if (cell != null) { switch (cell.getCellType()) { case STRING: value = cell.getStringCellValue(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: value = String.valueOf(cell.getCellFormula()); break; default: value = ""; } } return value; } } ``` 最后,可以使用该工具类来读取和写入Excel文档: ```java public class Main { public static void main(String[] args) { // 从Excel中读取数据 try (InputStream is = new FileInputStream("users.xlsx")) { List<User> list = ExcelUtil.readFromExcel(is, User.class); for (User user : list) { System.out.println(user.getName() + ", " + user.getAge()); } } catch (Exception e) { e.printStackTrace(); } // 写入数据到Excel中 List<User> list = new ArrayList<>(); list.add(new User("张三", 20)); list.add(new User("李四", 30)); list.add(new User("王五", 40)); try (OutputStream os = new FileOutputStream("users.xlsx")) { ExcelUtil.writeToExcel(list, os); } catch (Exception e) { e.printStackTrace(); } } } ```

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值