springBoot使用poi导出Excel_excelfield

一、导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
1
2
3
4
5

二、自定义注解

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
    String value() default "未知列名";
    String className() default "";
}
1
2
3
4
5
6
7

三、创建实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    @ExcelField("教师姓名")
    private String name;
    @ExcelField("性别")
    private String sex;
    @ExcelField("电话号")
    private String phone;
    @ExcelField("年龄")
    private Integer age;
    @ExcelField("工号")
    private String teacherNo;
    @ExcelField("下拉框")
    private List<String> ss;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

四、编写Excel工具类

工具类实现

#使用 BiConsumer 返回类型 通过.accpet()方法进行传参 
#可更改传参方式
public static BiConsumer<Object,String> getHSSFWorkbook( Class<?> c){
    return(list,name)-> {
       #具体实现
    }
1
2
3
4
5
6

1、创建工作簿以及sheet页

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("基本数据");
//设置列宽度大小
sheet.setDefaultColumnWidth((short) 15);
//设置行高
sheet.setDefaultRowHeight((short) (15 * 20));
1
2
3
4
5
6

2、创建表头标题

int indexRow = 0;
int startIndex = 0;
HSSFCell headCell;
#从第0行开始，设置表头 
HSSFRow hssfRow = sheet.createRow(indexRow++);
#通过映射得到实体类字段
Field[] fields = c.getDeclaredFields();
for (Field field : fields) {
    #通过映射得到表头名称
    ExcelField excelField = field.getAnnotation(ExcelField.class);
    if (excelField != null) {
        #设置第0行 第startIndex++列的表头
        headCell = hssfRow.createCell(startIndex++);
        //设置表头的样式
        setHeadStyle(headCell, wb);
        //设置单元格内容
        headCell.setCellValue(excelField.value());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3、设置表内容

#创建行
HSSFRow hssfRowData;
#得到表内数据集合
List<Object> objects = null;
if (list instanceof List) {
    objects = (List) list;
}
for (int i = 0; i< Objects.requireNonNull(objects).size(); i++) {
    #从第 1 行开始插入数据
    hssfRowData = sheet.createRow(indexRow++);
    int temp = 0;
    HSSFCell hssfCell;
    #获取实体类中属性字段
    Field[] fieldDatas = objects.get(i).getClass().getDeclaredFields();
    for (int j=0;j<fieldDatas.length;j++) {
        #字段类型
        Type fieldType = fieldDatas[j].getType();
        #字段名称
        String fieldName = fieldDatas[j].getName();
        #对于List类型字段单独判断
        if ("java.util.List".equals(fieldType.getTypeName())) {
            #通过getFieldValueByFieldName方法 调用get方法的到字段值
            List<String> fieldValueByFieldName = (List<String>) getFieldValueByFieldName(fieldName, objects.get(i));
            #设置第几列为下拉框
            CellRangeAddressList region = new CellRangeAddressList(0,fieldValueByFieldName.size(), j, j);
            #将list集合转变为数组
            String [] wordTypes=fieldValueByFieldName.toArray(new String[fieldValueByFieldName.size()]);
            #创建数据验证
            DataValidationHelper validationHelper = sheet.getDataValidationHelper();
            #加载下拉列表值
            DataValidationConstraint validationConstraint = validationHelper.createExplicitListConstraint(wordTypes);
            #创建验证方式
            DataValidation dataValidation = validationHelper.createValidation(validationConstraint, region);
            #做兼容性
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            #将验证与工作簿绑定
            sheet.addValidationData(dataValidation);
        }else {
            #对于非List类型字段进行设置值
            #从第 0 列创建单元格开始
            hssfCell = hssfRowData.createCell(temp++);
            #得到对应的字段名 调用get方法 的到对应的字段值
            Object value = getFieldValueByFieldName(fieldDatas[j].getName(), objects.get(i));
            #设置样式
            setDataStyle(hssfCell, wb);
            #设置单元格值
            setValue(hssfCell, value);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

4、导出excel

File file = new File(name);
FileOutputStream outputStream;
try {
    outputStream = new FileOutputStream(file);
    wb.write(outputStream);
    outputStream.flush();
} catch (IOException e) {
    e.printStackTrace();
}
1
2
3
4
5
6
7
8
9

5、所封装方法

• setValue(HSSFCell hssfCell,Object value) 设置单元格值

/**
 * 设置表格内容的值
 * @param hssfCell 单元格对象
 * @param value 单元格的值
 */
private static void setValue(HSSFCell hssfCell,Object value){
    if (value instanceof String) {
        hssfCell.setCellValue(value.toString());
    }else if (value instanceof Integer) {
        hssfCell.setCellValue((Integer) value);
    } else if (value instanceof Double) {
        hssfCell.setCellValue((Double) value);
    } else if (value instanceof Boolean) {
        hssfCell.setCellValue((Boolean) value);
    } else if (value instanceof Float) {
        hssfCell.setCellValue((Float) value);
    } else if (value instanceof Short) {
        hssfCell.setCellValue((Short) value);
    }else if (value instanceof Long) {
        hssfCell.setCellValue((Long) value);
    } else if (value instanceof Character) {
        hssfCell.setCellValue((Character) value);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

• setDataStyle(HSSFCell hssfCell,HSSFWorkbook wb) 设置样式

/**
 * 设置数据项单元格样式的样式
 * @param hssfCell 单元格对象
 * @param wb Excel对象
 */
private static void setDataStyle(HSSFCell hssfCell,HSSFWorkbook wb){
    HSSFCellStyle style=wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    Font font=wb.createFont();
    font.setFontName("宋体");
    font.setFontHeightInPoints((short)11);
    style.setFont(font);
    hssfCell.setCellStyle(style);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• getFieldValueByFieldName(String fieldName, Object object) 通过相应的字段名调用get方法

/**
 * 通过属性名字，调用相应的Get方法获取属性值
 *
 * @param object
 * @param fieldName 属性名字
 * @return
 */
public static Object getFieldValueByFieldName(String fieldName, Object object) {
    Class<?> c = object.getClass();
    try {
        //设置对象的访问权限，保证对private的属性的访问
        String s = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        Method method = c.getMethod("get" + s);
        return method.invoke(object);
    } catch (Exception e) {
        return null;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• setHeadStyle(HSSFCell hssfCell, HSSFWorkbook wb) 设置表头样式

    /**
     * 设置表头单元格样式
     */
    private static void setHeadStyle(HSSFCell hssfCell, HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);//设置字体
        //设置背景色
/*      style.setFillForegroundColor((short) 13);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);*/
        hssfCell.setCellStyle(style);

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

五、测试

public static void main(String[] args) {
    List<Teacher> list=new ArrayList<>();
    List<String> stringList=new ArrayList<>();
    stringList.add("delete");
    stringList.add("modify");
    String filePath="poi.xlsx";
    list.add(new Teacher("张三","男","190789799099",20,"88098900989890",stringList));
    list.add(new Teacher("李四","男","190789799099",24,"88777779898880",stringList));
    ExcelUtil.getHSSFWorkbook(Teacher.class).accept(list,filePath);
}
1
2
3
4
5
6
7
8
9
10

以上导出时，excel中存在下拉框列，使用以上方法进行下拉框导出存在长度限制问题，当下拉框中数据超过80个时则不会显示。

六、下拉框优化

1、创建文本簿，设置表头数据

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("基本数据");
XSSFSheet hiddenSheet = wb.createSheet("selectHidden");
int indexRow = 0;
int titleIndex = 0;
XSSFCell headCell;
XSSFRow hssfRow = sheet.createRow(indexRow++);
Field[] fields = Teacher.class.getDeclaredFields();
for (Field field : fields) {
    ExcelField excelField = field.getAnnotation(ExcelField.class);
    if (excelField != null) {
        headCell = hssfRow.createCell(titleIndex++);
        //设置单元格内容
        setValue(headCell, excelField.value());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2、设置excel数据

XSSFCell xssfCell;
for (Teacher teacher : list) {
    hssfRow = sheet.createRow(indexRow++);
    int temp = 0;
    Field[] declaredFields = teacher.getClass().getDeclaredFields();
    for (Field field : declaredFields) {
        String name = field.getName();
        List<String> list1 = new ArrayList<>();
        Object obj = getValue(name, teacher);
        if (obj instanceof ArrayList) {
            for (Object o : (List<?>) obj) {
                list1.add((String) o);
            }
            test(list1, STR, 5, 5, wb, sheet, 0, hiddenSheet);
        } else {
            xssfCell = hssfRow.createCell(temp++);
            setValue(xssfCell, obj);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3、导出excel

public static void test(List<?> stringList, String str, int start, int end,
                        XSSFWorkbook wb, XSSFSheet sheet, int index, XSSFSheet hiddenSheet){
    Object[] wordTypes = stringList.toArray(new Object[0]);
    XSSFCell cell;
    XSSFRow row;
    for (int k = 0; k < wordTypes.length; k++) {
       if (hiddenSheet.getRow(k)!=null){
           row=hiddenSheet.getRow(k);
       }else {
           row=hiddenSheet.createRow(k);
       }
        cell = row.createCell(index);
        setValue(cell, wordTypes[k]);
    }
    wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true);
    CellRangeAddressList addressList = new CellRangeAddressList(0, wordTypes.length, start, end);
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenSheet.getSheetName() + str + wordTypes.length);
    DataValidation validation = helper.createValidation(constraint, addressList);
    if (validation instanceof XSSFDataValidation) {
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
    } else {
        validation.setSuppressDropDownArrow(false);
    }
    sheet.addValidationData(validation);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27