Excel导出数据基于注解实现复制即可用

小王不头秃

发布于 2024-06-19 15:19:30

1270

发布于 2024-06-19 15:19:30

文章被收录于专栏：后端/图像融合/机器学习/爬虫后端/图像融合/机器学习/爬虫

Excel导出数据基于注解实现

简述

之前使用在其他博客看到的excel导出数据需要一个类设置一种导出的方式，作为一个懒人的我实在不想写这么多类去实现excel数据的导出，今天刚好有时间，就利用注解的方式来实现向excel导出数据。

实现思想

利用反射和注解实现的在需要导出的实体类的属性上添加我们自定义的注解，这个注解需要填写的属性有列名和列号，列名的作用就是给出导出的excel的第一行标题，列号就是该数据应放在excel的第几列。自定义的excel工具类会根据list的对象进行生成第一行标题以及其他行的数据

导入依赖

    <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>
    <!--Excel   end-->

代码

Excel工具类

package com.example.demo.Utils;

import com.example.demo.annotation.MyExcel;
import com.example.demo.entities.Award;
import io.swagger.models.auth.In;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

public abstract class ExcelUtil {

    public static Integer stringToInt(String data) {
        return (int) Double.parseDouble(data);
    }


    //填充每一行的数据
    public static void setData(Row row, Map<Integer, String> map) {
        map.keySet().forEach(key -> {
            row.createCell(key).setCellValue(map.get(key));
        });
    }

    //获取对象属性的值
    public static Map<Integer, String> getFieldValues(Object object) throws IllegalAccessException {
        Map map = new HashMap<Integer, String>();
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            //暴力访问
            field.setAccessible(true);
            MyExcel annotation = field.getAnnotation(MyExcel.class);
            Object o = field.get(object);
            map.put(Integer.parseInt(annotation.col()), o.toString());
        }
        return map;
    }

    //获取第一行的标题
    public static Map<Integer, String> getTitles(Object object) {
        Map map = new HashMap<Integer, String>();
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            MyExcel annotation = field.getAnnotation(MyExcel.class);
            map.put(Integer.parseInt(annotation.col()), annotation.colName());
        }
        return map;
    }


    public static void exportExcel(List list, String path, String sheetName) {
        Workbook wb = null;
        try {
            if (list.size() == 0 || path == null || path == "") {
                System.out.println("所需内容不完整");
                return;
            }

            String[] p = path.split("\\.");
            if (p[p.length - 1].equals("xlsx")) {
                wb = new XSSFWorkbook();
            } else if (p[p.length - 1].equals("xls")) {
                wb = new HSSFWorkbook();
            } else {
                System.out.println("文件类型不符");
                return;
            }
            Sheet sheet = wb.createSheet(sheetName);
            Row row = sheet.createRow(0);
            setData(row, getTitles(list.get(0)));
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                setData(row, getFieldValues(list.get(i)));
            }
            OutputStream fileOut = new FileOutputStream(path);
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭流
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


}

注解

package com.example.demo.annotation;

import java.lang.annotation.*;

/**
 * @author xiaow
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface MyExcel {

    /**
     * 第几列
     */
    String  col();

    /**
     * 列名
     */
    String colName();

}

实体类

package com.example.demo.vo;

import com.example.demo.annotation.MyExcel;
import lombok.Data;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
public class TestVo {
    @MyExcel(col = "0", colName = "学号")
    private Integer id;
    @MyExcel(col = "1", colName = "名字")
    private String name;

}

测试样例

 @Test
    void contextLoads() throws IllegalAccessException {

        List list=new LinkedList<TestVo>();
        list.add(new TestVo().setName("xiaow").setId(1));
        list.add(new TestVo().setName("daw").setId(1));
        list.add(new TestVo().setName("dxw").setId(1));
        list.add(new TestVo().setName("dxww").setId(1));
        list.add(new TestVo().setName("ww").setId(1));

        ExcelUtil.exportExcel(list,"xiaow.xls","yes");


    }