通过maven引入jar
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<poi.version>4.1.0</poi.version>
<commons-lang3.version>3.5</commons-lang3.version>
<guava.version>18.0</guava.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commons-lang3.version}</version>
</dependency>
</dependencies>
自定义注解@ExcelField
package cn.lisongyu.common.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author lisongyu
* @date 2019/09/23
*/
@Target({ElementType.FIELD, ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名(默认调用当前字段的“get”方法,如指定导出字段为对象,请填写“对象名.对象属性”,例:“area.name”、“office.name”)
*/
String value() default "";
/**
* 导出字段标题(需要添加批注请用“**”分隔,标题**批注,仅对导出模板有效)
*/
String title();
/**
* 导出字段对齐方式(0:自动;1:靠左;2:居中;3:靠右)
*/
int align() default 0;
/**
* 导出字段字段排序(升序)
*/
int sort() default 0;
}
工具类
- 导出
package cn.lisongyu.common.util;
import cn.lisongyu.common.annotation.ExcelField;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
/**
* @author lisongyu
* @date 2019/09/23
*/
@Slf4j
public class ExportExcel {
/**
* 工作薄对象
*/
private SXSSFWorkbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 当前行号
*/
private int rownum;
/**
* 注解列表(Object[]{ ExcelField, Field/Method })
*/
private List<Object[]> annotationList = new ArrayList<>();
/**
* 构造函数
*
* @param title 表格标题,传“空值”,表示无标题
* @param cls 实体对象,通过annotation.ExportField获取标题
*/
public ExportExcel(String title, Class<?> cls) {
//通过反射获取类的字段
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
//获取类中包含注解的字段
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null) {
//将注解ExcelField和字段保存
annotationList.add(new Object[]{ef, f});
}
}
//获取方法
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null) {
//将注解ExcelField和方法名保存
annotationList.add(new Object[]{ef, m});
}
}
//通过注解中的sort排序
annotationList.sort(Comparator.comparing((Object[] o) -> ((ExcelField) o[0]).sort()));
//初始化表格
List<String> headerList = new ArrayList<>();
for (Object[] os : annotationList) {
//获取标题
String t = ((ExcelField) os[0]).title();
headerList.add(t);
}
initialize(title, headerList);
}
/**
* 初始化表头
*
* @param title title
* @param headerList headerList
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet(title);
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
//创建列
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
//创建行
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
if (sheet instanceof SXSSFSheet) {
((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
} else {
sheet.autoSizeColumn(i);
}
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<>(8);
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBold(true);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/**
* 添加数据(通过annotation.ExportField添加数据)
*
* @return list 数据列表
*/
public <E> ExportExcel setDataList(List<E> list) {
for (E e : list) {
int colunm = 0;
Row row = this.addRow();
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList) {
ExcelField ef = (ExcelField) os[0];
Object val = null;
// Get entity value
try {
if (StringUtils.isNotBlank(ef.value())) {
val = Reflections.invokeGetter(e, ef.value());
} else {
if (os[1] instanceof Field) {
val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
} else if (os[1] instanceof Method) {
val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[]{}, new Object[]{});
}
}
} catch (Exception ex) {
log.info(ex.toString());
val = "";
}
this.addCell(row, colunm++, val, ef.align());
sb.append(val).append(",");
}
}
return this;
}
/**
* 添加一行
*
* @return 行对象
*/
private Row addRow() {
return sheet.createRow(rownum++);
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式(1:靠左;2:居中;3:靠右)
* @return 单元格对象
*/
private Cell addCell(Row row, int column, Object val, int align) {
Cell cell = row.createCell(column);
CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
try {
if (val == null) {
cell.setCellValue("");
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof BigDecimal) {
cell.setCellValue(val.toString());
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Date) {
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("yyyy-MM-dd"));
cell.setCellValue((Date) val);
} else {
cell.setCellValue((String) val);
}
} catch (Exception ex) {
log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
assert val != null;
cell.setCellValue(val.toString());
}
cell.setCellStyle(style);
return cell;
}
/**
* 输出数据流
*
* @param os 输出数据流
*/
public ExportExcel write(OutputStream os) throws IOException {
wb.write(os);
return this;
}
/**
* 输出到客户端
*
* @param fileName 输出文件名
*/
public ExportExcel write(HttpServletResponse response, String fileName) throws IOException {
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
write(response.getOutputStream());
return this;
}
/**
* 输出到客户端 解决乱码问题
*
* @param fileName 输出文件名
*/
public ExportExcel write2(HttpServletResponse response, String fileName) throws IOException {
fileName = new String(fileName.getBytes(), "ISO8859-1");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
write(response.getOutputStream());
return this;
}
/**
* 输出到文件
*
* @param fileName 输出文件名
*/
public void writeFile(String fileName) throws IOException {
FileOutputStream os = new FileOutputStream(fileName);
this.write(os);
}
/**
* 清理临时文件
*/
public ExportExcel dispose() {
wb.dispose();
return this;
}
}
- 导入
package cn.lisongyu.common.util;
import cn.lisongyu.common.annotation.ExcelField;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
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.Method;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
/**
* 导入Excel文件(支持“XLS”和“XLSX”格式)
*
* @author
*/
public class ImportExcel {
private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
*
* @param fileName 导入文件,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
*
* @param file 导入文件对象,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
*
* @param fileName 导入文件
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param file 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param multipartFile 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param fileName 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)) {
throw new RuntimeException("导入文档为空!");
} else if (fileName.toLowerCase().endsWith("xls")) {
this.wb = new HSSFWorkbook(is);
} else if (fileName.toLowerCase().endsWith("xlsx")) {
this.wb = new XSSFWorkbook(is);
} else {
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets() < sheetIndex) {
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
}
/**
* 获取行对象
*
* @param rownum
* @return
*/
public Row getRow(int rownum) {
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
*
* @return
*/
public int getDataRowNum() {
return headerNum + 1;
}
/**
* 获取最后一个数据行号
*
* @return
*/
public int getLastDataRowNum() {
return this.sheet.getLastRowNum() + headerNum;
}
/**
* 获取最后一个列号
*
* @return
*/
public int getLastCellNum() {
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellType() == CellType.NUMERIC) {
// val = cell.getNumericCellValue();
// 当excel 中的数据为数值或日期是需要特殊处理
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
SimpleDateFormat dformat = new SimpleDateFormat(
"yyyy-MM-dd");
val = dformat.format(date);
} else {
NumberFormat nf = NumberFormat.getInstance();
// true时的格式:1,234,567,890
nf.setGroupingUsed(false);
// 数值类型的数据为double,所以需要转换一下
val = nf.format(cell.getNumericCellValue());
}
} else if (cell.getCellType() == CellType.STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellType() == CellType.FORMULA) {
val = cell.getCellFormula();
} else if (cell.getCellType() == CellType.BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == CellType.ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
/**
* 获取导入数据列表
*
* @param cls 导入对象类型
*/
public <E> List<E> getDataList(Class<E> cls) throws InstantiationException, IllegalAccessException {
List<Object[]> annotationList = new ArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null) {
annotationList.add(new Object[]{ef, f});
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null) {
annotationList.add(new Object[]{ef, m});
}
}
// Field sorting
annotationList.sort(Comparator.comparing((Object[] o) -> ((ExcelField) o[0]).sort()));
List<E> dataList = new ArrayList<>();
for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
E e = (E) cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList) {
Object val = this.getCellValue(row, column++);
if (val != null) {
Class<?> valType = Class.class;
if (os[1] instanceof Field) {
valType = ((Field) os[1]).getType();
} else if (os[1] instanceof Method) {
Method method = ((Method) os[1]);
if ("get".equals(method.getName().substring(0, 3))) {
valType = method.getReturnType();
} else if ("set".equals(method.getName().substring(0, 3))) {
valType = ((Method) os[1]).getParameterTypes()[0];
}
}
try {
//如果导入的java对象,需要在这里自己进行变换。
if (valType == String.class) {
String s = String.valueOf(val.toString());
if (StringUtils.endsWith(s, ".0")) {
val = StringUtils.substringBefore(s, ".0");
} else {
val = String.valueOf(val.toString());
}
} else if (valType == Integer.class) {
val = Double.valueOf(val.toString()).intValue();
} else if (valType == Long.class) {
val = Double.valueOf(val.toString()).longValue();
} else if (valType == Double.class) {
val = Double.valueOf(val.toString());
} else if (valType == Float.class) {
val = Float.valueOf(val.toString());
} else if (valType == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
val = sdf.parse(val.toString());
} else {
val = String.valueOf(val);
}
} catch (Exception ex) {
log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field) {
Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
} else if (os[1] instanceof Method) {
String mthodName = ((Method) os[1]).getName();
if ("get".equals(mthodName.substring(0, 3))) {
mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[]{valType}, new Object[]{val});
}
}
sb.append(val + ", ");
}
dataList.add(e);
log.info("Read success: [{}] ", sb.toString());
}
return dataList;
}
}
- 反射
package cn.lisongyu.common.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
/**
* 反射工具类.
* 提供调用getter/setter方法, 访问私有变量, 调用私有方法, 获取泛型类型Class, 被AOP过的真实类等工具函数.
*
* @author lisongyu
* @date 2019/09/23
*/
@SuppressWarnings("rawtypes")
public class Reflections {
private static final String SETTER_PREFIX = "set";
private static final String GETTER_PREFIX = "get";
private static final String CGLIB_CLASS_SEPARATOR = "$$";
private static final String POINT = ".";
private static Logger logger = LoggerFactory.getLogger(Reflections.class);
/**
* 调用Getter方法.
* 支持多级,如:对象名.对象名.方法
*/
static Object invokeGetter(Object obj, String propertyName) {
Object object = obj;
for (String name : StringUtils.split(propertyName, POINT)) {
String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(name);
object = invokeMethod(object, getterMethodName, new Class[]{}, new Object[]{});
}
return object;
}
/**
* 调用Setter方法, 仅匹配方法名。
* 支持多级,如:对象名.对象名.方法
*/
static void invokeSetter(Object obj, String propertyName, Object value) {
Object object = obj;
String[] names = StringUtils.split(propertyName, ".");
for (int i = 0; i < names.length; i++) {
if (i < names.length - 1) {
String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);
object = invokeMethod(object, getterMethodName, new Class[]{}, new Object[]{});
} else {
String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);
invokeMethodByName(object, setterMethodName, new Object[]{value});
}
}
}
/**
* 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数.
*/
public static Object getFieldValue(final Object obj, final String fieldName) {
Field field = getAccessibleField(obj, fieldName);
if (field == null) {
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");
}
Object result = null;
try {
result = field.get(obj);
} catch (IllegalAccessException e) {
logger.error("不可能抛出的异常{}", e.getMessage());
}
return result;
}
/**
* 直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数.
*/
public static void setFieldValue(final Object obj, final String fieldName, final Object value) {
Field field = getAccessibleField(obj, fieldName);
if (field == null) {
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");
}
try {
field.set(obj, value);
} catch (IllegalAccessException e) {
logger.error("不可能抛出的异常:{}", e.getMessage());
}
}
/**
* 直接调用对象方法, 无视private/protected修饰符.
* 用于一次性调用的情况,否则应使用getAccessibleMethod()函数获得Method后反复调用.
* 同时匹配方法名+参数类型,
*/
static Object invokeMethod(final Object obj, final String methodName, final Class<?>[] parameterTypes,
final Object[] args) {
Method method = getAccessibleMethod(obj, methodName, parameterTypes);
if (method == null) {
throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
}
try {
return method.invoke(obj, args);
} catch (Exception e) {
throw convertReflectionExceptionToUnchecked(e);
}
}
/**
* 直接调用对象方法, 无视private/protected修饰符,
* 用于一次性调用的情况,否则应使用getAccessibleMethodByName()函数获得Method后反复调用.
* 只匹配函数名,如果有多个同名函数调用第一个。
*/
public static Object invokeMethodByName(final Object obj, final String methodName, final Object[] args) {
Method method = getAccessibleMethodByName(obj, methodName);
if (method == null) {
throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
}
try {
return method.invoke(obj, args);
} catch (Exception e) {
throw convertReflectionExceptionToUnchecked(e);
}
}
/**
* 循环向上转型, 获取对象的DeclaredField, 并强制设置为可访问.
* <p>
* 如向上转型到Object仍无法找到, 返回null.
*/
private static Field getAccessibleField(final Object obj, final String fieldName) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(fieldName, "fieldName can't be blank");
for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
try {
Field field = superClass.getDeclaredField(fieldName);
makeAccessible(field);
return field;
} catch (NoSuchFieldException e) {//NOSONAR
// Field不在当前类定义,继续向上转型
}
}
return null;
}
/**
* 循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问.
* 如向上转型到Object仍无法找到, 返回null.
* 匹配函数名+参数类型。
* <p>
* 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args)
*/
private static Method getAccessibleMethod(final Object obj, final String methodName,
final Class<?>... parameterTypes) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(methodName, "methodName can't be blank");
for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
try {
Method method = searchType.getDeclaredMethod(methodName, parameterTypes);
makeAccessible(method);
return method;
} catch (NoSuchMethodException e) {
// Method不在当前类定义,继续向上转型
}
}
return null;
}
/**
* 循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问.
* 如向上转型到Object仍无法找到, 返回null.
* 只匹配函数名。
* <p>
* 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args)
*/
private static Method getAccessibleMethodByName(final Object obj, final String methodName) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(methodName, "methodName can't be blank");
for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
Method[] methods = searchType.getDeclaredMethods();
for (Method method : methods) {
if (method.getName().equals(methodName)) {
makeAccessible(method);
return method;
}
}
}
return null;
}
/**
* 改变private/protected的方法为public,尽量不调用实际改动的语句,避免JDK的SecurityManager抱怨。
*/
private static void makeAccessible(Method method) {
if ((!Modifier.isPublic(method.getModifiers())
|| !Modifier.isPublic(method.getDeclaringClass().getModifiers()))
&& !method.isAccessible()) {
method.setAccessible(true);
}
}
/**
* 改变private/protected的成员变量为public,尽量不调用实际改动的语句,避免JDK的SecurityManager抱怨。
*/
private static void makeAccessible(Field field) {
if ((!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers()) || Modifier
.isFinal(field.getModifiers())) && !field.isAccessible()) {
field.setAccessible(true);
}
}
/**
* 通过反射, 获得Class定义中声明的泛型参数的类型, 注意泛型必须定义在父类处
* 如无法找到, 返回Object.class.
* eg.
* public UserDao extends HibernateDao<User>
*
* @param clazz The class to introspect
* @return the first generic declaration, or Object.class if cannot be determined
*/
@SuppressWarnings("unchecked")
public static <T> Class<T> getClassGenricType(final Class clazz) {
return getClassGenricType(clazz, 0);
}
/**
* 通过反射, 获得Class定义中声明的父类的泛型参数的类型.
* 如无法找到, 返回Object.class.
* <p>
* 如public UserDao extends HibernateDao<User,Long>
*
* @param clazz clazz The class to introspect
* @param index the Index of the generic ddeclaration,start from 0.
* @return the index generic declaration, or Object.class if cannot be determined
*/
private static Class getClassGenricType(final Class clazz, final int index) {
Type genType = clazz.getGenericSuperclass();
if (!(genType instanceof ParameterizedType)) {
logger.warn(clazz.getSimpleName() + "'s superclass not ParameterizedType");
return Object.class;
}
Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
if (index >= params.length || index < 0) {
logger.warn("Index: " + index + ", Size of " + clazz.getSimpleName() + "'s Parameterized Type: "
+ params.length);
return Object.class;
}
if (!(params[index] instanceof Class)) {
logger.warn(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");
return Object.class;
}
return (Class) params[index];
}
public static Class<?> getUserClass(Object instance) {
Assert.notNull(instance, "Instance must not be null");
Class clazz = instance.getClass();
if (clazz != null && clazz.getName().contains(CGLIB_CLASS_SEPARATOR)) {
Class<?> superClass = clazz.getSuperclass();
if (superClass != null && !Object.class.equals(superClass)) {
return superClass;
}
}
return clazz;
}
/**
* 将反射时的checked exception转换为unchecked exception.
*/
private static RuntimeException convertReflectionExceptionToUnchecked(Exception e) {
if (e instanceof IllegalAccessException || e instanceof IllegalArgumentException
|| e instanceof NoSuchMethodException) {
return new IllegalArgumentException(e);
} else if (e instanceof InvocationTargetException) {
return new RuntimeException(((InvocationTargetException) e).getTargetException());
} else if (e instanceof RuntimeException) {
return (RuntimeException) e;
}
return new RuntimeException("Unexpected Checked Exception.", e);
}
}
-
测试
package cn.lisongyu.common.base; import cn.lisongyu.common.annotation.ExcelField; import cn.lisongyu.common.util.ExportExcel; import cn.lisongyu.common.util.ImportExcel; import lombok.*; import lombok.extern.slf4j.Slf4j; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Random; /** * @author lisongyu * @date 2019/09/23 */ @Data @Builder @AllArgsConstructor(access = AccessLevel.PRIVATE) @NoArgsConstructor public class TestClass { @ExcelField(title = "编号", align = 2) private Integer id; @ExcelField(title = "姓名", align = 2, sort = 1) private String name; @ExcelField(title = "年龄", align = 2, sort = 2) private Integer age; @ExcelField(title = "薪水", align = 2, sort = 3) private Double salary; @ExcelField(title = "出生日期", align = 2, sort = 4) private String birthday; } @Slf4j class Test { public static void main(String[] args) throws Exception { List<TestClass> testClasses = new ArrayList<>(); for (int i = 1; i <= 10; i++) { TestClass testClass = TestClass.builder() .id(i) .name("测试" + i) .age(new Random().nextInt(100)) .salary(10000d) .birthday(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) .build(); testClasses.add(testClass); } ExportExcel exportExcel = new ExportExcel("测试导出", TestClass.class).setDataList(testClasses); exportExcel.writeFile("E:\\1.xlsx"); exportExcel.dispose(); ImportExcel importExcel = new ImportExcel("E:\\1.xlsx", 1, 0); List<TestClass> list = importExcel.getDataList(TestClass.class); for (int i = 0; i < list.size(); i++) { TestClass excelModelTest = list.get(i); log.info(excelModelTest.toString()); } } }
-
结果
19:46:59.423 [main] DEBUG cn.lisongyu.common.util.ExportExcel - Initialize success. 19:46:59.813 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [1, 测试1, 0, 10000.0, 2019-09-24, ] 19:46:59.814 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [2, 测试2, 63, 10000.0, 2019-09-24, ] 19:46:59.815 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [3, 测试3, 18, 10000.0, 2019-09-24, ] 19:46:59.815 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [4, 测试4, 91, 10000.0, 2019-09-24, ] 19:46:59.816 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [5, 测试5, 1, 10000.0, 2019-09-24, ] 19:46:59.816 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [6, 测试6, 47, 10000.0, 2019-09-24, ] 19:46:59.817 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [7, 测试7, 19, 10000.0, 2019-09-24, ] 19:46:59.818 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [8, 测试8, 40, 10000.0, 2019-09-24, ] 19:46:59.818 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [9, 测试9, 72, 10000.0, 2019-09-24, ] 19:46:59.819 [main] INFO cn.lisongyu.common.util.ImportExcel - Read success: [10, 测试10, 51, 10000.0, 2019-09-24, ] 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=1, name=测试1, age=0, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=2, name=测试2, age=63, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=3, name=测试3, age=18, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=4, name=测试4, age=91, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=5, name=测试5, age=1, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=6, name=测试6, age=47, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=7, name=测试7, age=19, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=8, name=测试8, age=40, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=9, name=测试9, age=72, salary=10000.0, birthday=2019-09-24) 19:46:59.819 [main] INFO cn.lisongyu.common.base.Test - TestClass(id=10, name=测试10, age=51, salary=10000.0, birthday=2019-09-24)