通过给类定义注解(设置名称，设置kv转换值)，然后利用设置的名称和传入的数据进行导出。
只需要在项目添加两个工具类就可以实现excel导出功能。
1、单sheet
步骤：1、根据业务需求定义导出的类，并设置表头名称。
2、调用工具类
定义类：
java">@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;
}调用方式：
java"> @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、使用工具类进行调用。
定义的类：
java">@Data
public class ScPerformanceForChildExcelVo {
//个人能力
private List<ScPersonalDetailedExcelVo> scPersonalDetailedExcelVoList=new ArrayList<>();
//工作业绩
private List<ScPostDetailedExcelVo> scPostDetailedExcelVoList=new ArrayList<>();
//额外
private List<ScOtherDetailedExcelVo> scOtherDetailedExcelVoList=new ArrayList<>();
}java">@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;
}调用方式：
java"> @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、工具类
注解类：
java">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：
java">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);
}
}
}
