自定义excel导出模板并导出数据

最新推荐文章于 2024-08-01 18:45:00 发布

嘀嗒嗒咚呲哒哒

最新推荐文章于 2024-08-01 18:45:00 发布

阅读量3k

点赞数 1

文章标签： java

本文链接：https://blog.csdn.net/qq_38223135/article/details/106729582

版权

功能需求是用户自定义导出模板，可以自定义excel的表头，并且自定义导出的列。

实现思路通过自动以注解扫描全局实体类，获取注解中标识的业务名称与类名存在数据库中，当用户上传模板时需要选择是哪一个业务名称的功能，将模板与实体类进行关联，导出时利用类的反射机制获取模板关联信息，下载模板并填充数据。

具体实现：

扫描实体类并插入数据库

@Component
@Slf4j
public class ExcelClassScanner implements CommandLineRunner {

    private final ExcelTemplateMapService excelTemplateMapService;

    public ExcelClassScanner(ExcelTemplateService excelTemplateService, ExcelTemplateMapService excelTemplateMapService) {
        this.excelTemplateMapService = excelTemplateMapService;
    }

    @Override
    public void run(String... args) throws Exception {
        log.debug("开始 -> excel模板，扫描全局获取业务名称与类名");
        initExcelTemplateMap();
        log.debug("完毕 -> excel模板扫描");
    }


    public void initExcelTemplateMap(){
        Set<ExcelTemplateMap> object = ClassScanner.getExcelTemplateMapClazz("com.***.***");//写自己项目的路径
        excelTemplateMapService.intiExcelTemplateMap(object);
        log.debug("共扫描到：" + object.size());
    }
}

项目启动执行，每次执行会清空表重新插入。

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("sys_user")
@BusinessName("用户管理")
public class User extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /**
     * 用户ID
     */
    @TableId
    @Excel(name = "用户编号", cellType = Excel.ColumnType.NUMERIC, prompt = "用户编号")
    private Integer id;

    /**
     * 用户名
     */
    @Excel(name = "用户名")
    private String userName;

    /**
     * 密码
     */
    private String password;

    /**
     * 昵称
     */
    @Excel(name = "用户昵称")
    private String nickName;

    /**
     * 用户类型
     */
    private String type;

    /**
     * 手机号码
     */
    @Excel(name = "手机号码")
    private String phone;

    /**
     * 用户性别（0男 1女 2未知）
     */
    @Excel(name = "用户性别", convertExp = "0=男,1=女,2=未知")
    private String sex;

    /**
     * 头像路径
     */
    private String avatar;

    /**
     * 用户邮箱
     */
    @Excel(name = "用户邮箱")
    private String email;

    /**
     * 机构ID
     */
    private Integer deptId;
    /**
     * 机构名称
     */
    @Excel(name = "机构名称")
    private String deptName;
}

实体类中用到两个注解一个是@BusinessName用于标识业务名称，@Excel用于标识列名，这两个注解的value都必须与前端页面的名称一致，后面会根据列名匹配来填充数据。注解如下：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface BusinessName {
    String value() default "";
}

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
    /**
     * 导出到Excel中的名字.
     */
    public String name() default "";

    /**
     * 日期格式, 如: yyyy-MM-dd
     */
    public String dateFormat() default "";

    /**
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)
     */
    public String convertExp() default "";

    /**
     * 导出类型（0数字 1字符串）
     */
    public ColumnType cellType() default ColumnType.STRING;

    /**
     * 导出时在excel中每个列的高度 单位为字符
     */
    public double height() default 14;

    /**
     * 导出时在excel中每个列的宽 单位为字符
     */
    public double width() default 16;

    /**
     * 文字后缀,如% 90 变成90%
     */
    public String suffix() default "";

    /**
     * 当值为空时,字段的默认值
     */
    public String defaultValue() default "";

    /**
     * 提示信息
     */
    public String prompt() default "";

    /**
     * 设置只能选择不能输入的列内容.
     */
    public String[] combo() default {};

    /**
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
     */
    public boolean isExport() default true;

    /**
     * 另一个类中的属性名称,支持多级获取,以小数点隔开
     */
    public String targetAttr() default "";

    /**
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）
     */
    Type type() default Type.ALL;

    public enum Type {
        ALL(0), EXPORT(1), IMPORT(2);
        private final int value;

        Type(int value) {
            this.value = value;
        }

        public int value() {
            return this.value;
        }
    }

    public enum ColumnType {
        NUMERIC(0), STRING(1);
        private final int value;

        ColumnType(int value) {
            this.value = value;
        }

        public int value() {
            return this.value;
        }
    }
}

模板上传

@PostMapping("/save")
    @ResponseBody
    public Result save(@RequestParam("className") String className,
                       @RequestParam("businessName") String businessName,
                       @RequestParam("templateStatus") String templateStatus,
                       @RequestParam("file") MultipartFile file) {

        if (file == null){
            return Result.error("请上传模板");
        }

        ExportTemplate exportTemplate = new ExportTemplate(className, businessName, templateStatus);
        //exportTemplateService.saveTemplate(exportTemplate, file);

        Class c = null;
        try {
            c = Class.forName(exportTemplate.getClassName());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        ExcelUtil<Object> util = new ExcelUtil<Object>(c);
        List<String> list = util.getDifferentFormTemplate(file);
        if (list.size() > 0){
            return Result.error("模板中列："+list.toString()+"不符合数据规范，请重新上传");
        }

        exportTemplateService.saveTemplate(exportTemplate, file);
        return Result.ok();
    }

上传时对模板进行校验避免后面导出时列对应不上：

/**
     * 校验上传模板的列和类的属性的差异
     * @param file
     * @return List
     */
    public List<String> getDifferentFormTemplate(MultipartFile file){
        InputStream is = null;
        List<String> classCellList = new ArrayList<>();
        List<String> templateCellList = new ArrayList<>();
        try {
            is = file.getInputStream();
            Map<String,Object> map = readExcel(is);
            templateCellList = (List<String>) map.get("templateCellList");

            //获取类中注解@Excel的属性名称list
            createExcelField();
            fields.forEach(item -> {
                Excel excel = (Excel) item[1];
                classCellList.add(excel.name());
            });
        } catch (IOException e) {
            log.error("校验Excel模板异常{}", e.getMessage());
            throw new BaseException("校验Excel模板失败，请联系网站管理员！");
        }

        return listCompare(templateCellList, classCellList);
    }

数据导出

 SystemExcelUtil<User> util = new SystemExcelUtil<User>(User.class);
 return util.exportTemplate(list);

在controller中调用，这里的list是要导出的数据。

导出工具类

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Type type;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 实体对象
     */
    public Class<T> clazz;
     
    /**
     * 按照excel模板导出数据
     * @param filePath
     * @param list
     * @param filename
     * @return
     */
    public Result exportFormTemplate(String filePath, List<T> list, String filename){
        this.list = list;
        try {
        createExcelField();
        Map<String,Object> map = readExcel(readFtp(filePath));
        Workbook workbook = (Workbook) map.get("workbook");
        Integer rowNum = (Integer) map.get("rowNum");
        List<String> templateCellList = (List<String>) map.get("templateCellList");

        //克隆sheet
        Sheet cloneSheet = workbook.cloneSheet(0);
        //填充数据
        for (int i = 0; i < list.size(); i++){
            Integer index = i+1;
            Row nRow = cloneSheet.createRow(rowNum + index);

            T vo = (T) list.get(i);
            for (int j = 0; j < fields.size(); j++) {
                Field field = (Field) fields.get(j)[0];
                Excel excel = (Excel) fields.get(j)[1];
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                if (templateCellList.contains(excel.name())){
                    Integer cellNum = templateCellList.indexOf(excel.name());
                    Cell cell = nRow.createCell(cellNum);
                    cell = this.addTemplateCell(excel, nRow, vo, field, cellNum);
                }
            }
        }

        FileOutputStream output = new FileOutputStream(getAbsoluteFile(filename));
        //删除模板sheet
        workbook.removeSheetAt(0);
        //重命名模板sheet
        workbook.setSheetName(0, sheetName != null ? sheetName : "sheet1");
        workbook.write(output);
        workbook.close();
        output.flush();
        output.close();
        return Result.ok(filename);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e);
            throw new BaseException("导出Excel失败，请联系网站管理员！");
        }
    }

    /**
     * 从ftp服务器获取文件
     * @param ftpPath
     * @return inputStream
     */
    public InputStream readFtp(String ftpPath){
        int index = ftpPath.lastIndexOf("/");
        String path = ftpPath.substring(0, index);
        int pathBeg = path.indexOf(FTPUtil.hostname) + FTPUtil.hostname.length();
        path = path.substring(pathBeg);
        String filename = ftpPath.substring(index + 1, ftpPath.length());
        InputStream inputStream = null;
        try {
            FTPClient ftpClient=new FTPClient();
            ftpClient.connect(FTPUtil.hostname, FTPUtil.port);
            ftpClient.login(FTPUtil.username, FTPUtil.password);
            if (FTPReply.isPositiveCompletion(ftpClient.getReplyCode())){
                ftpClient.changeWorkingDirectory(path);
                inputStream = ftpClient.retrieveFileStream(filename);
            }
        } catch (IOException e) {
            log.error("获取ftp模板文件异常{}", e);
            throw new BaseException("导出Excel失败，请联系网站管理员！");
        }
        return  inputStream;
    }


    /**
     * 添加单元格
     */
    public Cell addTemplateCell(Excel attr, Row row, T vo, Field field, int column) {
        Cell cell = null;
        try {
            // 设置行高
            row.setHeight((short) (attr.height() * 20));
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport()) {
                // 创建cell
                cell = row.createCell(column);
                //cell.setCellStyle(styles.get("data"));

                // 用于读取对象中的属性
                Object value = getTargetValue(vo, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.convertExp();
                if (StrUtil.isNotEmpty(dateFormat) && !StrUtil.isEmptyIfStr(value)) {
                    cell.setCellValue(com.dzj.drone.core.base.util.DateUtil.parseDateToStr(dateFormat, (Date) value));
                } else if (StrUtil.isNotEmpty(readConverterExp) && !StrUtil.isEmptyIfStr(value)) {
                    cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                } else {
                    // 设置列类型
                    setCellVo(value, attr, cell);
                }
            }
        } catch (Exception e) {
            log.error("导出Excel失败{}", e);
        }
        return cell;
    }

    /**
     * 读取上传的模板
     * @param is
     * @return rowNum 列名的行号，templateCellList 模板列集合，workbook 模板工作簿
     */
    public Map<String,Object> readExcel(InputStream is){
        //FileInputStream is = null;
        Workbook workbook = null;
        Map<String,Object> map = new HashMap<>();
        try {
            //is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0);

            //获取列最多的行，排除表明和合并的表名
            List<Integer> cellSizeList = new ArrayList();
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                int cellSize = 0;
                for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++){
                    Cell cell = row.getCell(cellNum);
                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        cellSize++;
                    }
                };
                cellSizeList.add(cellSize);
            }
            Integer rowNum =cellSizeList.indexOf(Collections.max(cellSizeList));

            //获取模板中每一列的列名
            Row row = sheet.getRow(rowNum);
            List<String> templateCellList = new ArrayList<>();
            for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++){
                Cell cell = row.getCell(cellNum);
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    templateCellList.add(cell.toString());
                }
            };

            //模板表头中列的行号
            map.put("rowNum", rowNum);
            //模板中的列
            map.put("templateCellList", templateCellList);
            //模板的workbook
            map.put("workbook", workbook);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 得到所有定义字段
     */
    private void createExcelField() {
        this.fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
            // 单注解
            if (field.isAnnotationPresent(Excel.class)) {
                putToField(field, field.getAnnotation(Excel.class));
            }

            // 多注解
            if (field.isAnnotationPresent(Excels.class)) {
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for (Excel excel : excels) {
                    putToField(field, excel);
                }
            }
        }
    }

    /**
     * 放到字段集合中
     */
    private void putToField(Field field, Excel attr) {
        if (attr != null && (attr.type() == Type.ALL || attr.type() == type) && attr.isExport()) {
            this.fields.add(new Object[]{field, attr});
        }
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo    实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws Exception
     */
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
        Object o = field.get(vo);
        if (StrUtil.isNotEmpty(excel.targetAttr())) {
            String target = excel.targetAttr();
            if (target.indexOf(".") > -1) {
                String[] targets = target.split("[.]");
                for (String name : targets) {
                    o = getValue(o, name);
                }
            } else {
                o = getValue(o, target);
            }
        }
        return o;
    }

    /**
     * 解析导出值 0=男,1=女,2=未知
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @return 解析后值
     * @throws Exception
     */
    public static String convertByExp(String propertyValue, String converterExp) throws Exception {
        try {
            String[] convertSource = converterExp.split(",");
            for (String item : convertSource) {
                String[] itemArray = item.split("=");
                if (itemArray[0].equals(propertyValue)) {
                    return itemArray[1];
                }
            }
        } catch (Exception e) {
            throw e;
        }
        return propertyValue;
    }

    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr  注解相关
     * @param cell  单元格信息
     */
    public void setCellVo(Object value, Excel attr, Cell cell) {
        if (ColumnType.STRING == attr.cellType()) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(StrUtil.isEmptyIfStr(value) ? attr.defaultValue() : value + attr.suffix());
        } else if (ColumnType.NUMERIC == attr.cellType()) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Integer.parseInt(value + ""));
        }
    }

excel操作用的是poi