原来Excel导出这么简单! 一小时不到快速实现一个简单的Excel导出工具

wj振藩

已于 2023-12-29 17:05:39 修改

阅读量501

点赞数 12

文章标签： excel java

于 2023-12-29 16:15:09 首次发布

本文链接：https://blog.csdn.net/qq_25652949/article/details/135292399

版权

注:转载请携带本文链接及公众号信息

公众号：codelike
在这里插入图片描述
本文相关代码github地址: https://github.com/biaosang/happy-excel

背景

经常使用poi进行excel导入导出,单纯使用poi要写很多代码
那么就会使用市面上比较好的一些开源框架来减轻代码量，那别人都能开发出好工具我们自己为什么就不行呢？
于是今天闲来无事就自己动手实现一个方便使用的excel导出工具耗时一小时不到实现导出功能

实现思路及用法

大致思路:
通过在给定的类上加上注解，然后传递的数据类上的注解，确定该类是否可以解析，该类的被注解标记的字段上的信息来确定要写入的excel的行列，然后通过反射拿到字段的值写到对应行列就可以

用法:
给要导出的类做上@HappyExcel注解标记，然后要导出的字段做上@ExcelField标记,
然后通过api

new Excel(文件名, Excel类型)
    .addSheet(表名/sheetName, 数据类1.class,数据1集合)
    .addSheet(表名/sheetName, 数据类2.class,数据2集合)
    .export();

这样是一个excel里按代码顺序添加了两张sheet 就可以导出两个类型对应的两张表的xls/xlsx文档.

目前只实现了XLSX的导出，xls的导出也同理

前置环境:maven3、oracle jdk8、idea2022.3.2
apache poi 5.2.0

效果

一行代码搞定数据导出(单模型数据)
模型定义:

@HappyExcel
public class User {

    @ExcelField(header = "姓名",col = 0)
    private String name;

    @ExcelField(header = "年龄",col = 1)
    private int age;

    @ExcelField(header = "生日",col = 2)
    private Date birth;
}

@HappyExcel
public class Class {

    @ExcelField(header = "班级",col = 0)
    private String name;

    @ExcelField(header = "年级",col = 1)
    private String level;
}

导出示例代码:

    public static void main(String[] args) throws IOException {
        List<User> users = new ArrayList<>();
        users.add(new User("张三",11,new Date()));
        users.add(new User("李四",17,new Date()));
        users.add(new User("王五",21,new Date()));

        List<Class> classes = new ArrayList<>();
        classes.add(new Class("一班","一年级"));
        classes.add(new Class("二班","一年级"));
        classes.add(new Class("一班","二年级"));
        classes.add(new Class("二班","二年级"));
        classes.add(new Class("三班","二年级"));
		//下面这行就是导出代码
        new Excel("content.xlsx", ExcelType.XLSX)
                .addSheet("用户表", User.class,users)
                .addSheet("班级表", Class.class,classes)
                .addSheet("班级表无表头", Class.class,classes,true,0)
                .export();
        System.out.println("导出完成");
    }

在这里插入图片描述

开发过程

Step1.新建maven工程

引入poi依赖

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.0</version>
        </dependency>
    </dependencies>

开始编码

定义注解类

这里定义两个注解，一个是标记类的@HappyExcel,一个是标记字段的@ExcelField

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface HappyExcel {

}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {

    String header () default "";

    int col();

    String format() default "yyyy-MM-dd HH:mm:ss";

}

Excel创建类和数据处理类

Excel.java

public class Excel {

    private Workbook workbook;

    private ExcelType excelType;
    private String fileName;

    private List<Sheet> sheets = new ArrayList<>();

    private Sheet currentSheet = null;

    public Excel(String fileName){
        excel(fileName,ExcelType.XLSX);
    }

    public Excel(String fileName,ExcelType excelType){
        excel(fileName,excelType);
    }

    private void excel(String fileName,ExcelType excelType){
        this.excelType = excelType;
        this.fileName = fileName;
        this.workbook = ExcelFactory.createWorkbook(excelType);
    }
    public <T> Excel addSheet(String sheetName,Class<T> clazz,List<T> data){
        return addSheet(sheetName,clazz,data,false,0);
    }
    public <T> Excel addSheet(String sheetName,Class<T> clazz,List<T> data,boolean ignoreHeader,int startRow){
        return createSheet(sheetName,clazz,data,ignoreHeader,startRow);
    }

    private <T> Excel createSheet(String sheetName,Class<T> clazz,List<T> data,boolean ignoreHeader,int startRow){
        Sheet sheet = workbook.createSheet(sheetName);
        currentSheet = sheet;
        sheets.add(sheet);
        try {
            new XlsxDataHandler(sheet).run(clazz,data,ignoreHeader,startRow);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return this;
    }

    public void export() throws IOException {
        workbook.write(Files.newOutputStream(Paths.get(fileName), StandardOpenOption.CREATE));
    }

ExcelFactory.java

public class ExcelFactory {
    public static Workbook createWorkbook(ExcelType excelType){
        return ExcelType.XLSX == excelType ?  new XSSFWorkbook() : new HSSFWorkbook();
    }
}

ExcelType.java

public enum ExcelType {
    XLS(".xls"),
    XLSX("xlsx"),
    ;

    private String value;

    ExcelType(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}

XlsxDataHandler.java 实现导出的核心类 100行代码不到

public class XlsxDataHandler {

    private Sheet sheet;

    private int currentRow;

    public XlsxDataHandler(Sheet sheet){
        this.sheet = sheet;
    }

    public <T> void run(Class<T> clazz, List<T> data, boolean ignoreHeader, int startRow) throws Exception {
        HappyExcel happyExcel = clazz.getAnnotation(HappyExcel.class);
        if(happyExcel == null)
            throw new HappyExcelException("数据类缺少'@HappyExcel'注解标识");

        currentRow = startRow;
        if(!ignoreHeader){
            addHeaderRow(clazz);
        }
        addData(clazz,data);
    }

    private <T> void addData(Class<T> clazz, List<T> data) throws Exception {
        Field[] fields =clazz.getDeclaredFields();
        for(T t : data){
            Row dataRow = sheet.createRow(currentRow++);
            for(Field field : fields) {
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField != null) {
                    field.setAccessible(true);
                    Object object = field.get(t);
                    setCellValue(dataRow,excelField.col(),object,excelField.format());
                }
            }
        }
    }

    private <T> void addHeaderRow(Class<T> clazz) throws Exception {
        Row headerRow = sheet.createRow(currentRow++);
        //colSet 用于校验列是否重复
        Set<Integer> colSet = new HashSet<>();

        Field[] fields =clazz.getDeclaredFields();
        for(Field field : fields){
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if(excelField != null){
                if(colSet.contains(excelField.col())){
                    throw new HappyExcelException("class " + clazz.getName() +" 发现列序号配置重复");
                }
                colSet.add(excelField.col());

                String header = excelField.header();
                if(header == null){
                    header = field.getName();
                }
                headerRow.createCell(excelField.col()).setCellValue(header);
            }
        }
    }

    protected void setCellValue(Row row,int col,Object value,String format){
        if(value == null){
            row.createCell(col).setCellValue("");
            return ;
        }

        if(value instanceof Integer || value instanceof Double || value instanceof Float || value instanceof Long){
            row.createCell(col).setCellValue(Double.parseDouble(String.valueOf(value)));
        }else if(value instanceof Boolean){
            row.createCell(col).setCellValue((Boolean)value);
        }else if(value instanceof Date){
            row.createCell(col).setCellValue(new SimpleDateFormat(format).format((Date)value));
        }else if(value instanceof Calendar){
            row.createCell(col).setCellValue(new SimpleDateFormat(format).format(((Calendar)value).getTime()));
        }else if(value instanceof LocalDate){
            row.createCell(col).setCellValue(((LocalDate)value).format(DateTimeFormatter.ofPattern(format)));
        }else if(value instanceof LocalDateTime){
            row.createCell(col).setCellValue(((LocalDateTime)value).format(DateTimeFormatter.ofPattern(format)));
        }else if(value instanceof RichTextString){
            row.createCell(col).setCellValue(((RichTextString)value).getString());
        }else{
            row.createCell(col).setCellValue((String)value);
        }

    }
}

HappyExcelException.java 自定义异常类

public class HappyExcelException extends RuntimeException{

    public HappyExcelException(String message, Throwable cause) {
        super(message, cause);
    }

    public HappyExcelException(String message) {
        super(message);
    }

    public HappyExcelException(Throwable cause) {
        super(cause);
    }

}