java导出excel通用工具(POI,类注解形式)

??????? 通过给类定义注解(设置名称，设置kv转换值)，然后利用设置的名称和传入的数据进行导出。

只需要在项目添加两个工具类就可以实现excel导出功能。

1、单sheet

??????? 步骤：1、根据业务需求定义导出的类，并设置表头名称。
?????????????????? 2、调用工具类???????????????????????? ????????????

?定义类：

@Data
public class ScDetailsReviewExcelVo {
    //年份
    @ExcelIO(value = "年份")
    private String examineYear;
    /**考核批次*/
    @ExcelIO(value = "考核批次")
    private String batchName;
    //姓名
    @ExcelIO(value = "姓名")
    private String realname;
    //科室
    @ExcelIO(value = "科室")
    private String departName;
    /**考核总分*/
    @ExcelIO(value = "考核总分")
    private String examineScore;
    /**个人能力得分*/
    @ExcelIO(value = "个人能力得分")
    private String personnelScore;
    /**工作业绩得分*/
    @ExcelIO(value = "工作业绩得分")
    private String postScore;
    /**额外工作得分*/
    @ExcelIO(value = "额外工作得分")
    private String otherScore;
    /**状态*/
    @ExcelIO(value = "状态",kv="0-未提交;1-已完成;2-已退回;3-已归档")
    private Integer userState;
}

调用方式：

    @Override
    public void exprotExcel(HttpServletResponse response, ScDetailsReview scDetailsReview) {
        //获取数据
        List<ScDetailsReviewExcelVo> list=scDetailsReviewMapper.selectForExcel(scDetailsReview);
        //调用工具
        ExcelUtil<ScDetailsReviewExcelVo> excelVoExcelUtil=new ExcelUtil<>(ScDetailsReviewExcelVo.class);
        //导出数据
        excelVoExcelUtil.getExcel(list,response,"打分");
    }

2、多sheet

??????? 步骤：1、定义大类作为参数对象。
?????????????????? 2、在大类中设置List子类，且各List对应各sheet页数据，设置List泛型中的属性为其注
????????????????????????解表头名称（下列例子中只展示了一个大类和其中的一个子类）
?????????????????? 3、使用工具类进行调用。

????????

定义的类：???????

@Data
public class ScPerformanceForChildExcelVo {

    //个人能力
    private List<ScPersonalDetailedExcelVo> scPersonalDetailedExcelVoList=new ArrayList<>();
    //工作业绩
    private List<ScPostDetailedExcelVo> scPostDetailedExcelVoList=new ArrayList<>();
    //额外
    private List<ScOtherDetailedExcelVo> scOtherDetailedExcelVoList=new ArrayList<>();

}

@Data
public class ScPersonalDetailedExcelVo {
    /**类别*/
    @ExcelIO(value = "类别")
    private String category;
    /**考评指标*/
    @ExcelIO(value = "考评指标")
    private String assessmentIndex;
    /**考评要点*/
    @ExcelIO(value = "考评要点")
    private String keyPoint;
    /**评分参考细则*/
    @ExcelIO(value = "评分参考细则")
    private String detailedRules;
    /**总分分值*/
    @ExcelIO(value = "总分分值")
    private String ruleScore;
    /**得分*/
    @ExcelIO(value = "得分")
    private String personnelUserScore;
}

调用方式：

    @Override
    public void exprotExcel(HttpServletResponse response, ScDetailsReview scDetailsReview) {
        //个人能力
        List<ScPersonalDetailedExcelVo> personalDetailedExcelVoList= scPersonalDetailedService.selectExcel(scDetailsReview);
        //工作业绩
        List<ScPostDetailedExcelVo> scPostDetailedExcelVoList= scPostDetailedService.selectExcel(scDetailsReview);
        //额外
        List<ScOtherDetailedExcelVo> scOtherDetailedExcelVoList= scOtherDetailedService.selectExcel(scDetailsReview);

        ScPerformanceForChildExcelVo scPerformanceForChildExcelVo=new ScPerformanceForChildExcelVo();
        scPerformanceForChildExcelVo.setScPersonalDetailedExcelVoList(personalDetailedExcelVoList);
        scPerformanceForChildExcelVo.setScPostDetailedExcelVoList(scPostDetailedExcelVoList);
        scPerformanceForChildExcelVo.setScOtherDetailedExcelVoList(scOtherDetailedExcelVoList);

        ExcelUtil<ScPerformanceForChildExcelVo> scPerformanceForChildExcelVoExcelUtil=new ExcelUtil<>(ScPerformanceForChildExcelVo.class);
        scPerformanceForChildExcelVoExcelUtil.getExcels(scPerformanceForChildExcelVo,response);

    }

3、工具类

注解类：

package org.jeecg.utils;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIO {
    /** 字段名称 */
    String value();

    /** 导出映射，格式如：0-未知;1-男;2-女 */
    String kv() default "";
}

ExcelUtil：

package org.jeecg.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil<T> {
    //泛型
    public Class<T> clazz;

    public Workbook workbook;

    //sheet对象
    public Sheet sheet;

    //标题行总列数
    private Integer tableNames;

    //样式集合
    private Map<String,CellStyle> styleMap=new HashMap<>();

    //需要导出字段
    private List<Field> fieldList=new ArrayList<>();

    public ExcelUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    //创建样式
    private void setStyleMap(){
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        titleFont.setBold(true);
        style.setFont(titleFont);
        style.setWrapText(true);
        //标题样式
        styleMap.put("title", style);

    }

    //设置单元格宽度
    private void setWidth(Sheet sheet,Integer colNum){
        for (int i = 0; i < colNum; i++) {
            sheet.autoSizeColumn(i);
//            int columnWidth = sheet.getColumnWidth(i);
//            sheet.setColumnWidth(colNum,Math.min(sheet.getColumnWidth(i)+1000,15*256));
        }
    }

    //创建表头
    private void setTableName(Class clazz,Sheet sheet,Integer rowNum){
        //创建标题
        List<String> tiltes=new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            //获取字段属性
            ExcelIO annotation = field.getAnnotation(ExcelIO.class);
            if(null!=annotation){
                String value = annotation.value();
                //需要的标题
                tiltes.add(value);
                //需要的字段
                fieldList.add(field);
            }
        }
        //写入标题
        tableNames=tiltes.size();
        Row row = sheet.createRow(rowNum);
        for (int i = 0; i < tableNames; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(tiltes.get(i));
            cell.setCellStyle(styleMap.get("title"));
        }
    }

    //创建标题行
    private void setTilte(String tilte){
        //创建标题
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue(tilte);
        cell.setCellStyle(styleMap.get("title"));
        //合并
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,tableNames-1));
    }

    //写入数据
    private void setData(List<T> data,Integer rowNum){
        for (Object datum : data) {
            Row row = sheet.createRow(rowNum);
            rowNum++;
            for (Integer integer = 0; integer < tableNames; integer++) {
                Cell cell = row.createCell(integer);
                Field field = fieldList.get(integer);
                cell.setCellValue(changeValue(field,datum));
            }
        }
    }

    //写入数据
    private void setDataForChildeSheet(List<Object> data,Sheet sheet,Integer rowNum){
        for (Object datum : data) {
            Row row = sheet.createRow(rowNum);
            rowNum++;
            for (Integer integer = 0; integer < tableNames; integer++) {
                Cell cell = row.createCell(integer);
                Field field = fieldList.get(integer);
                cell.setCellValue(changeValue(field,datum));
            }
        }
    }

    //有需要转换的值
    private String changeValue(Field field,Object data){
        try {
            field.setAccessible(true);
            String filed = null==field.get(data)?"":field.get(data).toString();
            if(null==filed){
                return "";
            }

            ExcelIO annotation = field.getAnnotation(ExcelIO.class);
            if(null!=annotation){
                String value = annotation.kv();
                if(null==value || "".equals(value)){
                    return filed;
                }
                String[] split = value.split(";");
                for (String s : split) {
                    String[] split1 = s.split("-");
                    if(split1.length>=2){
                        if(split1[0].equals(filed)){
                            return split1[1];
                        }
                    }
                }
            }
            return "";
        } catch (IllegalAccessException e) {
            return "";
        }
    }

    //多sheet页其中一个
    private void setSheetData(Sheet sheet1, Class<?> genericClass, List<Object> o) {
        //创建标题
        setTableName(genericClass,sheet1,0);
        //写数据
        setDataForChildeSheet(o,sheet1,1);
        //设置宽度
        setWidth(sheet1,tableNames);
        //清除存在的数据
        fieldList.clear();
        tableNames=0;
    }

    //导出excel
    public void getExcel(List<T> data, HttpServletResponse response,String name){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 创建一个新的工作薄
        workbook = new XSSFWorkbook();
        // 创建一个工作表
        sheet = workbook.createSheet("Sheet1");
        //初始化样式
        setStyleMap();
        //创建表头
        setTableName(clazz,sheet,1);
        //创建标题
        setTilte(name);
        //写入数据
        setData(data,2);
        //设置宽度
        setWidth(sheet,tableNames);

        //返回给前端
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    //导出多个sheet页,传入的data的内部是多个list，list泛型就是各个sheet的数据，且泛型需要用excle注解进行设置
    public void getExcels(T data, HttpServletResponse response){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 创建一个新的工作薄
        workbook = new XSSFWorkbook();
        //初始化样式
        setStyleMap();
        //获取子类class对象
        Field[] declaredFields = clazz.getDeclaredFields();
        Integer i=0;
        for (Field declaredField : declaredFields) {
            Type genericType = declaredField.getGenericType();
            if (genericType instanceof ParameterizedType) {
                ParameterizedType parameterizedType = (ParameterizedType) genericType;
                Type[] typeArguments = parameterizedType.getActualTypeArguments();
                if (typeArguments.length > 0) {
                    //子类class对象
                    Class<?> genericClass = (Class<?>) typeArguments[0];
                    try {
                        declaredField.setAccessible(true);
                        //获取子类数据
                        List<Object> o = (List)declaredField.get(data);
                        if(null!=o && o.size()>0){
                            // 创建子工作表
                            Sheet sheet1 = workbook.createSheet("Sheet"+i);
                            i++;
                            //为子工作表赋值
                            setSheetData(sheet1,genericClass,o);
                        }
                    } catch (IllegalAccessException e) {
                        return;
                    }
                }
            }
        }

        //返回给前端
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

}