ssm + layui + poi+ 自定义注解 + java反射 做条件导出Excel 工具类

导出效果：

​​为了节省时间使用的的工具类在线下载​​

注：此为SSM+layui项目

配置依赖

<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>
          <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.7</version>
        </dependency>

layui前端代码：

from绑定一个id，条件查询每个属性给个name值，用于根据条件查询导出

1.头部工具栏添加一个按钮

<!-- 头部工具栏 -->
    <script type="text/html" id="toolbarDemo"><div class="layui-btn-container">
            <button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="exportExcel"><i class="layui-icon layui-icon-export"></i>导出用户</button>
        </div></script>

2.在监听头工具栏事件定义方法和实现传参

//监听头工具栏事件
        table.on('toolbar(userList)', function(obj){
            var checkStatus = table.checkStatus(obj.config.id),
                data = checkStatus.data; //获取选中的数据
            switch(obj.event){
              //导出
                case 'exportExcel':
                    exportExcel();
                    break;
            };
        });

        //导出
        function exportExcel(){
            var searchForm = document.getElementById("searchForm");
            searchForm.action="/user/exportExcel";
            searchForm.submit();
        }

3.在UserController接参

<ExportingEntityPoi>

4.创建ExportUtil 工具类

package com.ff.util;

import com.ff.common.excel.ExcelUtilsBean;
import com.ff.common.excel.annotations.ExcelDescAnnotation;
import com.ff.common.excel.annotations.ExcelFieldAnnotation;
import com.ff.common.excel.annotations.model.ExportingEntityPoi;
import com.ff.common.excel.constant.SuperEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExportUtil {

    public static void ExportExcel(List<?> data, Class<?> clazz, HttpServletResponse response) {

        //获取对象中的自定义注解的信息
        ExcelUtilsBean excelUtilsBean = getExceInfo(clazz);
        //导出Excel
        try {
            ExportExcels(data,excelUtilsBean,response);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //获取对象中的自定义注解的信息  描述excel信息
    private static ExcelUtilsBean getExceInfo(Class<?> clazz) {
        ExcelUtilsBean excelUtilsBean = new ExcelUtilsBean();
        //获取类上的自定义注解中的信息
        ExcelDescAnnotation annotation = clazz.getAnnotation(ExcelDescAnnotation.class);
        //给excel设置标题
        excelUtilsBean.setTitleName(annotation.title());
        //给excel设置Sheet页名称
        excelUtilsBean.setSheetName(annotation.sheetName());

        //获取字段上面的注解信息
        Field[] fields = clazz.getDeclaredFields();
        List<String> cloumnNames = new ArrayList<>();
        List<String> fieldNames = new ArrayList<>();
        for (Field field : fields) {
            //获取所有的字段
            fieldNames.add(field.getName());
            //获取字段上的自定义注解信息
            ExcelFieldAnnotation fieldAnnotation = field.getAnnotation(ExcelFieldAnnotation.class);
            //获取表头
           cloumnNames.add(fieldAnnotation.cloumnName());
        }
        excelUtilsBean.setFieldList(fieldNames);
        excelUtilsBean.setCloumnNameList(cloumnNames);
        return excelUtilsBean;
    }

    //导出Excel
    private static void ExportExcels(List<?> data, ExcelUtilsBean excelUtilsBean, HttpServletResponse response) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IOException, NoSuchFieldException {

        // 创建一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建sheet页
        XSSFSheet sheet = workbook.createSheet(excelUtilsBean.getSheetName());

        // 表头数据
        List<String> cloumnNameList = excelUtilsBean.getCloumnNameList();

        // 给列设置默认宽度
        sheet.setDefaultColumnWidth(30);

        // 合并单元格
        CellRangeAddress range = new CellRangeAddress(0,0,0,cloumnNameList.size()-1);
        sheet.addMergedRegion(range);
        CellRangeAddress range2 = new CellRangeAddress(1,1,0,cloumnNameList.size()-1);
        sheet.addMergedRegion(range2);

        // 创建行
        XSSFRow row1 = sheet.createRow(0);

        // 创建单元格
        XSSFCell cell = row1.createCell(0);
        // 给单元格赋值
        cell.setCellValue(excelUtilsBean.getTitleName());
        // 设置第一行样式
        XSSFCellStyle titleStyle = createTitleStyle(workbook);
        cell.setCellStyle(titleStyle);

        // 创建第二行
        XSSFRow row2 = sheet.createRow(1);
        XSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("总数："+data.size()+"条，导出时间："+new Date().toLocaleString());
        // 设置第二行样式
        XSSFCellStyle subTitleStyle = createSubTitleStyle(workbook);
        cell2.setCellStyle(subTitleStyle);

        // 创建第三行(表头行)
        XSSFRow row3 = sheet.createRow(2);
        for (int i = 0; i <cloumnNameList.size() ; i++) {
            XSSFCell row3_cell = row3.createCell(i);
            row3_cell.setCellValue(cloumnNameList.get(i));
            XSSFCellStyle tableTitle = createTableTitle(workbook);
            row3_cell.setCellStyle(tableTitle);
        }

        // 创建数据行
        XSSFCellStyle baseStyle = createBaseStyle(workbook);
        List<String> fielNames = excelUtilsBean.getFieldList();
        for (int i = 0; i < data.size(); i++) {
            Object obj = data.get(i);
            Class<?> clazz = obj.getClass();
            XSSFRow dataRow = sheet.createRow(i + 3);

            for (int j = 0; j < fielNames.size(); j++) {
                String fielName = fielNames.get(j);
                String methodName =getMethod(fielName);
                Method method = clazz.getMethod(methodName);
                // 执行get方法获取该字段的值
                Object fieldValue = method.invoke(obj);
                // 创建数据行的单元格
                XSSFCell dataRowCell = dataRow.createCell(j);
                //获取单元格的值
                String cellValue=getCellValue(fieldValue,clazz,fielName,i,j);
                dataRowCell.setCellValue(cellValue);
                dataRowCell.setCellStyle(baseStyle);
            }

        }

        //设置自适应
        for (int i = 0; i < fielNames.size(); i++) {
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }

        //添加返回的数据的类型，和文件名
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;fileName=" +System.currentTimeMillis() + ".xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());

    }

    //获取单元格的值
    private static String getCellValue(Object data, Class<?> clazz, String fielName, int i, int j) throws NoSuchFieldException, InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        if(data == null ){
            return "";
        }
        if(data instanceof Date){
            Field field = clazz.getDeclaredField(fielName);
            ExcelFieldAnnotation fieldAnno = field.getAnnotation(ExcelFieldAnnotation.class);
            String pattern=fieldAnno.pattern();
            if(StringUtils.isNotBlank(pattern)){
                String dateValue = DateFormatUtils.format((Date) data, pattern);
                return dateValue;

            }else{
                String dateValue = DateFormatUtils.format((Date) data, "yyyy-MM-dd HH:mm:ss");
                return dateValue;
            }

        }else if (j == 0){
            //编号顺序
            return String.valueOf(i+1);
        }else if(data instanceof String){
            return String.valueOf(data);
        }else if(data instanceof Integer){
            Field field = clazz.getDeclaredField(fielName);
            ExcelFieldAnnotation fieldAnno = field.getAnnotation(ExcelFieldAnnotation.class);
            Class enumClazz = fieldAnno.enumClazz();
            if(enumClazz.isEnum()){
                Method method = enumClazz.getMethod("values");
                SuperEnum[] superEnums= (SuperEnum[]) method.invoke(null);
                for(SuperEnum superEnum:superEnums){
                    Integer code=superEnum.getCode();
                    if(code.equals(data)){
                        return superEnum.getName();
                    }
                }
            }
            return String.valueOf(data);
        }else{
            return "";
        }
    }

    private static String getMethod(String fielName) {
        return "get" + fielName.substring(0,1).toUpperCase()+fielName.substring(1);
    }


    /**
     * 设置技术样式
     * 水平居中和垂直居中
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createBaseStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = workbook.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 设置标题样式
     */
    public static XSSFCellStyle createTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 30);
        // 字体颜色
        font.setColor(HSSFColor.BLUE.index);
        // 字体样式
        font.setFontName("华文彩云");
        style.setFont(font);
        return style;
    }

    /**
     * 设置小标题样式
     */
    public static XSSFCellStyle createSubTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 20);
        // 字体颜色
        font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("微软雅黑");
        style.setFont(font);
        return style;
    }

    /**
     * 设置表格标题样式
     */
    public static XSSFCellStyle createTableTitle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 15);
        // 字体颜色
        // font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("宋体");
        style.setFont(font);
        return style;
    }

}













 /**
     * excel的导入
     * @param inputStream
     * @param clazz
     * @return
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public static  List<?> importExcel(InputStream inputStream, Class<?> clazz)
            throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row titleCell = sheet.getRow(0);
        List<Object> dataList = new ArrayList<>(sheet.getLastRowNum());
        Object datum;
        Map<String, Field> fieldMap = getFieldMap(clazz);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            datum = clazz.newInstance();
            int minCell = row.getFirstCellNum();
            int maxCell = row.getLastCellNum();
            for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {
                Cell title = titleCell.getCell(cellNum);
                if (title == null) {
                    continue;
                }
                String tag = title.getStringCellValue();
                Field field = fieldMap.get(tag);
                if (field == null) {
                    continue;
                }
                Class<?> type = field.getType();
                Object value = null;
                Cell cell = row.getCell(cellNum);
                if (cell == null) {
                    continue;
                }
                if (type.equals(Date.class)){
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getStringCellValue();
                }
                PropertyUtils.setProperty(datum, field.getName(), value);
            }
            dataList.add(datum);
        }
        return dataList;
    }
 
    /**
     * key :headName  val:该名称对应的字段
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Field> fieldMap = new HashMap<>();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelAnnotation.class)) {
                ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
                fieldMap.put(annotation.headName(), field);
            }
        }
        return fieldMap;
    }

​​华文彩云字体下载​​

5.创建两个自定义注解

package com.ff.common.excel.annotations;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

//ElementType.TYPE  //类上使用
@Target(ElementType.TYPE)
//RetentionPolicy.RUNTIM   注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDescAnnotation {
    //用来描述excel中sheet的名字
    String value() default "";
    // 标题
    String title() default "";
    // sheet 名称
    String sheetName() default "";
}

package com.ff.common.excel.annotations;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
// ElementType.FIELD //字段、枚举的上的常量
@Target(ElementType.FIELD)
RetentionPolicy.RUNTIM   注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFieldAnnotation {

    //用来描述字段的值，以及需要转换的格式

    String value() default "";
    //字段名称
    String cloumnName() default "";
    // 时间
    String pattern() default "";
    //性别 格式转换
    Class enumClazz() default Object.class;
}

6.创建接口做枚举类返回值

package com.ff.common.excel.constant;

public interface SuperEnum {
   // 创建接口做枚举类返回值
    public  String getName();

    public Integer getCode();

}

7.创建枚举类用来做字段的转换 比如性别

package com.fh.common.excel.constant;

public enum SexEnum implements SuperEnum {

    //转换性别
    MAN(0,"男"),GIRL(1,"女");


    private Integer code ;
    private String name ;

    private SexEnum(Integer code,String name){
        this.code=code;
        this.name=name;
    }

    @Override
    public String getName() {
        return name;
    }

    @Override
    public Integer getCode() {
        return code;
    }
}

8.创建导出使用的实体类 用于放需要导出字段

package com.fh.common.excel.annotations.model;
import com.fh.common.excel.annotations.ExcelDescAnnotation;
import com.fh.common.excel.annotations.ExcelFieldAnnotation;
import com.fh.common.excel.constant.SexEnum;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

//导出使用的实体类

@Data
//title   Excel标题   sheetName  sheet 名称
@ExcelDescAnnotation(title = "用户信息",sheetName = "用户信息")
public class ExportingEntityPoi {
    //导出需要的字段
    @ExcelFieldAnnotation(cloumnName = "用户编号")
    private String id;
    @ExcelFieldAnnotation(cloumnName = "用户名称")
    private String name;
    @ExcelFieldAnnotation(cloumnName = "登录名称")
    private String loginname;
    @ExcelFieldAnnotation(cloumnName = "用户地址")
    private String address;
    @ExcelFieldAnnotation(cloumnName = "直属领导")
    private String remark;
    @ExcelFieldAnnotation(cloumnName = "用户性别",enumClazz = SexEnum.class)//enumClazz = SexEnum.class  导出时转换性别格式
    private Integer sex;
    @ExcelFieldAnnotation(cloumnName = "所属部门")
    private String deptname;
    @ExcelFieldAnnotation(cloumnName = "入职时间",pattern = "yyyy-MM-dd HH:mm:ss")
    private Date hiredate;
}

9.创建用来存储对象中注解配置的信息

package com.ff.common.excel;

        import lombok.Data;

        import java.util.List;
//此类用来存储对象中注解配置的信息
@Data
public class ExcelUtilsBean {
    private List<String> fieldList;
    private List<String> cloumnNameList;
    private String sheetName;
    private String titleName;
}

10.在UserMapper.xml中执行Sql文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ff.mapper.UserMapper">
    <sql id="where">
        <where>
            <if test="vo.name != null and vo.name != ''">
                u1.name like concat('%',#{vo.name},'%')
            </if>
           <if test="vo.deptid != null  and vo.deptid!=-1">
            and u1.deptid=#{vo.deptid}
            </if>

            <if test="vo.startTime != null">
                and u1.hiredate >= #{vo.startTime}
            </if>

            <if test="vo.endTime != null">
                and u1.hiredate <= #{vo.endTime}
            </if>

            <if test="vo.available != null">
                and u1.available = #{vo.available}
            </if>
        </where>
    </sql>
    <!--查询用户列表-->
    <select id="queryuser" resultType="com.ff.domain.User">
        SELECT
        u1.*,d.title deptname
        FROM
        sys_user u1
        LEFT JOIN sys_dept d ON u1.deptid = d.id
        <include refid="where"></include> order by u1.id desc
        limit #{vo.startIndex},#{vo.limit}
    </select>

    <!--导出-->
    <select id="queryUserList" resultType="com.ff.common.excel.annotations.model.ExportingEntityPoi">
        SELECT
        u1.*,d.title as deptname
        FROM
        sys_user u1
        left join sys_dept d on u1.deptid=d.id
        <include refid="where"></include>
        order by id desc
    </select>

    <!--总条数-->
    <select id="queryCount" resultType="long">
        select count(*) from sys_user u1 <include refid="where"></include>
    </select>
</mapper>