基于poi和JavaBean的excel导出

little_how

已于 2022-08-04 11:30:58 修改

阅读量251

点赞数

分类专栏： java 文章标签： java 开发语言

于 2022-08-03 17:48:58 首次发布

本文链接：https://blog.csdn.net/w172087242/article/details/126145738

版权

java 专栏收录该内容

47 篇文章 1 订阅

订阅专栏

写在前面

示例写出时间：2022-08-03

这仅仅是excel导出的一个示例, 记录一下

poi依赖

其他spring、lombok等依赖就不列举了

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

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

核心类

注解ExcelColumn

package com.littlehow.excel;

import java.lang.annotation.*;

/**
 * excel字段注解
 * @author littlehow
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface ExcelColumn {
    /**
     * @return - 表头
     */
    String title();

    /**
     * @return - 顺序
     */
    int order();

    /**
     * @return 日期格式化
     */
    String format() default "";

    /**
     * @return 行高
     */
    short height() default 450;

    /**
     * @return - 列宽
     */
    int width() default 4800;
}

列注解属性ExcelColumnMeta

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;

/**
 *  列的基本属性
 * @author littlehow
 */
@Setter
@Getter
@Accessors(chain = true)
public class ExcelColumnMeta {
    /**
     * 表头
     */
    private String title;

    /**
     * 行高
     */
    private short height;

    /**
     * 列宽
     */
    private int width;

    /**
     * 日期格式化
     */
    private DateTimeFormatter formatter;

    public String getDateString(TemporalAccessor accessor) {
        if (formatter != null) {
            return formatter.format(accessor);
        }
        return accessor.toString();
    }
}

列字段属性ExcelMetaData

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.temporal.TemporalAccessor;

/**
 * 字段属性以及基本获取值的方法
 * @author littlehow
 */
@Setter
@Getter
public class ExcelMetaData {
    /**
     * 字段
     */
    private Field field;

    /**
     * 对应的get方法
     */
    private Method method;

    /**
     * 排序
     */
    private int order;

    /**
     * 日期顶级接口
     */
    private boolean isTemporalAccessor = false;

    /**
     * 是否为decimal类型
     */
    private boolean isDecimal = false;

    /**
     * 字段属性
     */
    private ExcelColumnMeta columnMeta;

    /**
     * 获取对象的值，做简单类型的格式化
     * @param obj - 对象
     * @return - obj对应该字段的格式化值
     */
    public String getString(Object obj) {
        if (obj == null) {
            return "";
        }
        Object value = getValue(obj);
        if (value == null) {
            return "";
        }

        if (isTemporalAccessor) {
            return columnMeta.getDateString((TemporalAccessor) value);
        } else if (isDecimal) {
            return ((BigDecimal) value).stripTrailingZeros().toPlainString();
        } else {
            return value.toString();
        }
    }

    /**
     * 获取对象的值, 优先get方法获取，没有get方法的将使用field获取，所以如果没有提供get方法，需要将字段访问属性设置为public
     * 这里就不对字段进行field.setAccessible设置
     * @param obj  - 对象
     * @return - 对应对应的值
     */
    private Object getValue(Object obj) {
        try {
            if (method != null) {
                return method.invoke(obj);
            } else {

                return field.get(obj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

excel导出属性ExcelMeta

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;

import java.util.List;

/**
 * 记录表宽度、表头、行高以及excel字段信息
 * @author littlehow
 */
@Setter
@Getter
public class ExcelMeta {
    /**
     * 列宽
     */
    private List<Integer> width;

    /**
     * 行高
     */
    private short height;

    /**
     * 表头
     */
    private List<String> header;

    /**
     * 字段属性集
     */
    private List<ExcelMetaData> excelMetaData;
}

excel属性缓存类ExcelClassCache

package com.littlehow.excel;

import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author littlehow
 */
public class ExcelClassCache {
    private static Map<Class, ExcelMeta> EXCEL_MATE_CACHE = new HashMap<>();

    /**
     * 获取meta配置数据
     * @param clazz - 类
     * @return  -
     */
    static ExcelMeta getMeta(Class clazz) {
        ExcelMeta meta = EXCEL_MATE_CACHE.get(clazz);
        if (meta != null) {
            return meta;
        }
        meta = new ExcelMeta();
        // 解析class
        List<Field> fields = new ArrayList<>();
        getAllField(fields, clazz);
        List<ExcelMetaData> metaData = new ArrayList<>();
        fields.forEach(o -> {
            if (o.isAnnotationPresent(ExcelColumn.class)) {
                ExcelMetaData excelMetaData = new ExcelMetaData();
                ExcelColumn excelColumn = o.getAnnotation(ExcelColumn.class);
                ExcelColumnMeta columnMeta = new ExcelColumnMeta()
                        .setHeight(excelColumn.height())
                        .setTitle(excelColumn.title())
                        .setWidth(excelColumn.width());
                if (TemporalAccessor.class.isAssignableFrom(o.getType())) {
                    excelMetaData.setTemporalAccessor(true);
                    String format = excelColumn.format();
                    if (StringUtils.hasText(format)) {
                        columnMeta.setFormatter(DateTimeFormatter.ofPattern(format));
                    }
                } else if (o.getType() == BigDecimal.class) {
                    excelMetaData.setDecimal(true);
                }
                excelMetaData.setField(o);
                excelMetaData.setMethod(getMethod(o, clazz));
                excelMetaData.setColumnMeta(columnMeta);
                excelMetaData.setOrder(excelColumn.order());
                metaData.add(excelMetaData);
            }
        });
        Assert.notEmpty(metaData, "excel config not found");
        metaData.sort(Comparator.comparing(ExcelMetaData::getOrder));
        meta.setExcelMetaData(metaData);
        meta.setWidth(metaData.stream().map(o -> o.getColumnMeta().getWidth()).collect(Collectors.toList()));
        meta.setHeight(metaData.get(0).getColumnMeta().getHeight());
        meta.setHeader(metaData.stream().map(o -> o.getColumnMeta().getTitle()).collect(Collectors.toList()));
        EXCEL_MATE_CACHE.put(clazz, meta);
        return meta;
    }

    /**
     * 获取所有字段，包括父类的字段
     * @param fieldList -
     * @param clazz -
     */
    private static void getAllField(List<Field> fieldList, Class clazz) {
        if (clazz == Object.class) {
            return;
        }
        Field[] fields = clazz.getDeclaredFields();
        fieldList.addAll(Arrays.asList(fields));
        getAllField(fieldList, clazz.getSuperclass());

    }

    /**
     * 获取字段对应的get方法
     * @param field -
     * @param clazz -
     * @return -
     */
    private static Method getMethod(Field field, Class clazz) {
        try {
            String name = field.getName();
            if (name.length() > 1) {
                return  clazz.getMethod("get" + name.substring(0, 1).toUpperCase() + name.substring(1));
            } else {
                return clazz.getMethod("get" + name.toUpperCase());
            }
        } catch (Exception e) {
            // skip;
            return null;
        }
    }
}

excel导出类Excel

package com.littlehow.excel;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * excel导出工具
 * @author littlehow
 */
public class Excel<T> {
    private SXSSFWorkbook workbook;

    private SXSSFSheet sheet;

    private int rowIndex = 0;

    private boolean create = false;

    private ExcelMeta excelMeta;

    private Excel() {
        // 不开放
    }

    /**
     * 构建excel导出类
     * @param clazz - 导出类型
     * @param <T> -
     * @return - 导出类
     */
    public static <T> Excel<T> build(Class<T> clazz) {
        Excel<T> excel = new Excel<>();
        excel.excelMeta = ExcelClassCache.getMeta(clazz);
        excel.workbook = new SXSSFWorkbook();
        excel.sheet = excel.workbook.createSheet();
        // 设置列头以及列宽
        excel.fullHeader();
        return excel;
    }

    /**
     * 填充类容
     * @param content - 类容
     */
    public Excel<T> write(List<T> content) {
        Assert.isTrue(!create, "excel already created");
        if (CollectionUtils.isEmpty(content)) {
            return this;
        }
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        content.forEach(t -> {
            // 创建表头
            SXSSFRow row = sheet.createRow(this.rowIndex++);
            row.setHeight(excelMeta.getHeight());
            setContent(row, getContent(t), style);
        });
        return this;
    }

    /**
     * 输出 本方法没有关闭外部输出流
     * @param os - 输出流
     * @throws IOException  - io异常
     */
    public void create(OutputStream os) throws IOException {
        workbook.write(os);
        create = true;
    }


    /**
     * 填充表头以及设置列宽
     */
    private void fullHeader() {

        setColumnWidth(this.sheet, excelMeta.getWidth());
        // 设置表头
        SXSSFRow header = this.sheet.createRow(this.rowIndex++);
        header.setHeight(excelMeta.getHeight());
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        setContent(header, excelMeta.getHeader(), style);
    }

    /**
     * 获取字段内容
     * @param t - 对象
     * @return -
     */
    private List<String> getContent(T t) {
        List<ExcelMetaData> excelMetaData = excelMeta.getExcelMetaData();
        List<String> values = new ArrayList<>();
        excelMetaData.forEach(data -> values.add(data.getString(t)));
        return values;
    }


    /**
     * 设置内容
     * @param row        - 行
     * @param content    - 内容
     * @param style      - 样式
     */
    private static void setContent(SXSSFRow row, List<String> content, XSSFCellStyle style) {
        // 设置第一行
        for (int i = 0, len = content.size(); i < len; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(content.get(i));
            // 创建一个单元格样式
            cell.setCellStyle(style);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
    }


    /**
     * 设置列宽
     * @param sheet          - sheet
     * @param columnWidths   - 列宽配置
     */
    private static void setColumnWidth(SXSSFSheet sheet, List<Integer> columnWidths) {
        for (int i = 0, len = columnWidths.size(); i < len; i++) {
            sheet.setColumnWidth(i, columnWidths.get(i));
        }
    }
}

测试类

测试对象父类Biology

package com.littlehow.excel.test;

import com.littlehow.excel.ExcelColumn;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDateTime;

/**
 * 生物对象
 * @author littlehow
 */
@Setter
@Getter
public class Biology {
    @ExcelColumn(title = "发布时间", order = 9, format = "yyyy-MM-dd HH:mm:ss", width = 5200)
    private LocalDateTime time;
}

测试对象子类Person

package com.littlehow.excel.test;

import com.littlehow.excel.ExcelColumn;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.math.BigDecimal;
import java.time.LocalDate;

/**
 * @author littlehow
 */
@Getter
@Setter
@Accessors(chain = true)
public class Person extends Biology {

    @ExcelColumn(title = "用户编号", order = 2)
    private String userId;

    @ExcelColumn(title = "生日", order = 1, format = "yyyyMMdd")
    private LocalDate birthday;

    @ExcelColumn(title = "年龄", order = 4)
    private Integer age;

    @ExcelColumn(title = "公司", order = 6)
    private String company;

    @ExcelColumn(title = "存款", order = 5)
    private BigDecimal balance;
}

导出测试类ExcelTest

package com.littlehow.excel.test;

import com.littlehow.excel.Excel;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @author littlehow
 */
public class ExcelTest {
    public static void main(String[] args) throws Exception {
        List<Person> person = new ArrayList<>();
        person.add(new Person().setAge(18).setBirthday(LocalDate.now().minusYears(18)).setUserId("101").setCompany("正义公司").setBalance(BigDecimal.valueOf(123.5500)));
        person.add(new Person().setAge(20).setBirthday(LocalDate.now().minusYears(20)).setUserId("104").setCompany("正义公司").setBalance(BigDecimal.valueOf(88891.102900)));
        person.add(new Person().setAge(22).setBirthday(LocalDate.now().minusYears(22)).setUserId("110").setCompany("正义公司").setBalance(BigDecimal.valueOf(123.5570)));
        person.add(new Person().setAge(24).setBirthday(LocalDate.now().minusYears(24)).setUserId("120").setCompany("正义公司").setBalance(BigDecimal.valueOf(3333)));
        person.forEach(o -> o.setTime(LocalDateTime.now().minusDays(5)));
        Excel<Person> excel = Excel.build(Person.class);
        excel.write(person)
                 // 这里可以多次写入，所以不需要一次性将所有数据都加载到内存
                .write(person)
                .write(person)
                // 这里就会生成对应的excel文件
                .create(new FileOutputStream("/Users/littlehow/Desktop/person.xlsx"));
    }
}