Java自定义注解导出Excel

女爷i 2022-12-02 10:42 250阅读 0赞

# 基于自定义实现导出Excel #

一般导入（解析Excel）导出不用Java做，这门语言不太适合做文件处理可以使用前端的node.js或goland做文件处理，小编写了一套基于自定义注解的导入和导出方便大家使用。

网上找的导入导出代码很多代码都是基本生成Excel文档，表头、文档名称都得手动传入参数实际实用性不强，小编基于自定义注解动态获取这些名称可用性非常高。

--------------------

## 引用POM文件 ##

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
            </dependency>
    
    		
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>3.17</version>
            </dependency>

## 新建自定义注解 ##

*  **模型上使用该注解可以动态获得Excel名称**

package com.monkey.excel;
    
    import java.lang.annotation.*;
    
    /** * @Author: LailaiMonkey * @Description：描述表格名 * @Date：Created in 2020-08-29 16:48 * @Modified By： */
    @Target(ElementType.TYPE)
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface MonkeyExcelName { 
        String value();
    }

*  **模型字段上使用该注解可以动态获得列名**

package com.monkey.excel;
    
    import java.lang.annotation.*;
    
    /** * @Author: LailaiMonkey * @Description：描述列名 * @Date：Created in 2020-08-29 16:48 * @Modified By： */
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface MonkeyExcelColumnName { 
        String value();
    
        int sort() default Integer.MAX_VALUE;
    }

## 新建两个描述表格元素类 ##

这两类主要作用是获得Excel和列的名称，创建Excel时候直接使用不需要再次获取。

*  **描述表格名称及所有列信息**

package com.monkey.excel;
    
    import org.apache.commons.lang3.reflect.FieldUtils;
    
    import java.lang.reflect.Field;
    import java.util.ArrayList;
    import java.util.Comparator;
    import java.util.List;
    
    /** * @Author: LailaiMonkey * @Description：描述表格名称及所有列信息 * @Date：Created in 2020-08-29 16:03 * @Modified By： */
    public class MonkeyExcel { 
        private List<MonkeyExcelColumn> columns;
        private String name;
    
        MonkeyExcel(Class<?> clazz) { 
            this.columns = getCellName(clazz);
            this.name = getExcelName(clazz);
        }
    
        private List<MonkeyExcelColumn> getCellName(Class<?> clazz) { 
            List<MonkeyExcelColumn> cellNames = new ArrayList<>();
            Field[] fields = FieldUtils.getAllFields(clazz);
            for (Field field : fields) { 
                MonkeyExcelColumnName annotation = field.getAnnotation(MonkeyExcelColumnName.class);
                if (annotation != null) { 
                    cellNames.add(new MonkeyExcelColumn(annotation.value(), field.getName(), annotation.sort()));
                } else { 
                    cellNames.add(new MonkeyExcelColumn(field.getName(), field.getName(), Integer.MAX_VALUE));
                }
            }
            cellNames.sort(Comparator.comparingInt(MonkeyExcelColumn::getSort));
            return cellNames;
        }
    
        private String getExcelName(Class clazz) { 
            MonkeyExcelName annotation = (MonkeyExcelName) clazz.getAnnotation(MonkeyExcelName.class);
            if (annotation != null) { 
                return annotation.value();
            }
            return clazz.getSimpleName();
        }
    
        public List<MonkeyExcelColumn> getColumns() { 
            return columns;
        }
    
        public String getName() { 
            return name;
        }
    }

*  **描述所有列信息**

package com.monkey.excel;
    
    /** * @Author: LailaiMonkey * @Description：描述所有列信息 * @Date：Created in 2020-08-29 18:39 * @Modified By： */
    public class MonkeyExcelColumn { 
        private String name;
        private String fieldName;
        private Integer sort;
    
        MonkeyExcelColumn(String name, String fieldName, Integer sort) { 
            this.name = name;
            this.fieldName = fieldName;
            this.sort = sort;
        }
    
        public String getName() { 
            return name;
        }
    
        public String getFieldName() { 
            return fieldName;
        }
    
        public Integer getSort() { 
            return sort;
        }
    }

## 生成Excel工具类 ##

package com.monkey.excel;
    
    import org.apache.commons.lang3.reflect.FieldUtils;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.springframework.util.CollectionUtils;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    
    /** * @Author: LailaiMonkey * @Description：导出工具类 * @Date：Created in 2020-08-29 16:02 * @Modified By： */
    public class MonkeyExcelExportUtils { 
        public static void export(HttpServletResponse response, List<?> list) { 
            if (CollectionUtils.isEmpty(list)) { 
                return;
            }
    
            //声明一个工作薄
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            //生成一个表格
            SXSSFSheet sheet = workbook.createSheet();
            //获取要生成表格信息
            MonkeyExcel excelModel = new MonkeyExcel(list.get(0).getClass());
            //获取所有列名
            List<MonkeyExcelColumn> models = excelModel.getColumns();
    
            //循环字段名数组，创建标题行
            Row row = sheet.createRow(0);
            for (int i = 0; i < models.size(); i++) { 
                //创建列
                Cell cell = row.createCell(i);
                //设置单元类型为String
                cell.setCellType(CellType.STRING);
                cell.setCellValue(models.get(i).getName());
            }
    
            //创建普通行
            for (int i = 0; i < list.size(); i++) { 
                //因为第一行已经用于创建标题行，故从第二行开始创建
                row = sheet.createRow(i + 1);
                for (int j = 0; j < models.size(); j++) { 
                    //创建列
                    Cell cell = row.createCell(j);
                    //集合中某行为空
                    Object value = null;
                    if (list.get(i) != null) { 
                        value = getFieldValue(list.get(i), models.get(j).getFieldName());
                    }
                    cell.setCellValue(transCellType(value));
                }
            }
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            try { 
                response.setHeader("Content-Disposition", "attachment;fileName=" +
                        URLEncoder.encode(excelModel.getName(), "utf-8") +
                        ".xlsx");
                response.flushBuffer();
                workbook.write(response.getOutputStream());
            } catch (IOException e) { 
                e.printStackTrace();
            }
        }
    
        private static Object getFieldValue(Object object, String fieldName) { 
            Class<?> clazz = object.getClass();
            try { 
                Field name = FieldUtils.getField(clazz, fieldName, true);
                return FieldUtils.readField(name, object, true);
            } catch (IllegalAccessException e) { 
                e.printStackTrace();
            }
            return null;
        }
    
        private static String transCellType(Object value) { 
            if (value instanceof Date) { 
                Date date = (Date) value;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return sdf.format(date);
            }
            return "null".equals(String.valueOf(value)) ? "" : String.valueOf(value);
        }
    
    }

## 使用方法 ##

controller调用  
MonkeyExcelExportUtils.export(response, 需要生成数据集合);  
传入HttpServletResponse请求和数据集合。

模型定义：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNzM5Nzk1ODY_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNzM5Nzk1ODY_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/20546be36db3457f9b8b7657fba55fea.png