刚毕业那会儿写过一个 通用的excel导入导出工具类.
这个工具类呢也用了挺久了的，有兴趣的也可以去看一下哈。
最近呢又新写了一个 基于注解+反射实现的Excel导入导出 工具类，具体请看下文
导入方法传入Excel文件输入流，以及一个Class类型
导出方法接收一个list集合，和一个Class类型
首先需要创建三个注解
一个是 EnableExcelimport，必须有这个注解才能导入
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExcelimport {
String sheetName() default "";
int sheetAt() default 0;
int startRow() default 0;
}
然后是EnableExcelExport，必须有这个注解才能导出到Excel里面
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExcelExport {
String fileType() default "xlsx";
String sheetName() default "";
boolean isIncludeHeader() default false;
int startRow() default 0;
short startColumn() default 0;
}
再就是SheetColumn，实体类字段上有这个注解才导出或者导入该字段
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SheetColumn {
String name() default "";
int index() default -1;
int width() default 4;
boolean required() default false;
boolean imported() default true;
boolean exported() default true;
}
注解使用示例
@EnableExcelimport(startRow = 1)
@EnableExcelExport(fileName = "test", isIncludeHeader = true)
public class Employee {
@SheetColumn(name = "姓名", required = true)
private String fullName;
@SheetColumn(name = "性别", width = 3, required = true)
private String gender;
@SheetColumn(name = "年龄", width = 3, required = true)
private BigDecimal age;
@SheetColumn(name = "地址", width = 15, required = true)
private String address;
@SheetColumn(name = "电话", width = 6, required = true)
private String phone;
@SheetColumn(name = "邮箱", width = 10, required = true)
private String email;
// getter and setter...
}
#三个注解创建好之后，下面就开始封装Excel导入导出的工具类了
public class ExcelUtils {
public static final String EXCEL_XLS = "xls";
public static List parseExcelToList(InputStream inputStream, Class clazz) {
List res = Lists.newArrayList();
EnableExcelimport enableExcelimport = clazz.getAnnotation(EnableExcelimport.class);
if (Objects.isNull(enableExcelimport)) {
return res;
}
String sheetName = enableExcelimport.sheetName();
int sheetAt = enableExcelimport.sheetAt();
int startRow = enableExcelimport.startRow();
Workbook wb;
Sheet st;
Row row;
Cell cell;
try {
wb = WorkbookFactory.create(inputStream);
st = StringUtils.isEmpty(sheetName) ? wb.getSheetAt(sheetAt) : wb.getSheet(sheetName);
if (Objects.isNull(st)) {
return res;
}
// 实体类的属性
List fields = Arrays.stream(clazz.getDeclaredFields())
.filter(field -> field.isAnnotationPresent(SheetColumn.class)
&& field.getAnnotation(SheetColumn.class).imported())
.collect(Collectors.toList());
// 所有属性注解中列索引index最大值
int maxIndex = fields.stream().mapToInt(item
-> item.getAnnotation(SheetColumn.class).index()).max().getAsInt();
for (int i = startRow; i < st.getPhysicalNumberOfRows(); i++) {
// 空行跳过
if (Objects.isNull(row = st.getRow(i))) {
continue;
}
T object = clazz.newInstance();
int currentIndex = maxIndex;
for (Field field : fields) {
// 通过属性注解SheetColumn的列索引index取单元格的值
// 未指定index，即index为-1，则按照所有索引中最大索引+1取值，最大索引+1即为新的最大索引
SheetColumn sheetColumn = field.getAnnotation(SheetColumn.class);
int index = sheetColumn.index();
cell = index == -1 ? row.getCell(++currentIndex) : row.getCell(index);
String value = getCellValue(cell);
field.setAccessible(true);
Object val = TypeUtils.cast(value, field.getType(), null);
field.set(object, val);
}
res.add(object);
}
} catch (Exception e) {
// 自定义处理
e.printStackTrace();
}
return res;
}
private static String getCellValue(Cell cell) {
if (Objects.isNull(cell)) {
return null;
}
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
public static void writeListToWorkbook(List data, Class clazz, OutputStream outputStream) {
Optional.ofNullable(writeListToWorkbook(data, clazz)).ifPresent(wb -> {
try {
wb.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
});
}
public static Workbook writeListToWorkbook(List data, Class clazz) {
EnableExcelExport enableExcelExport = clazz.getAnnotation(EnableExcelExport.class);
if (Objects.isNull(enableExcelExport)) {
return null;
}
Workbook wb;
Sheet st;
Row row;
Cell cell;
try {
String sheetName = enableExcelExport.sheetName();
boolean includeHeader = enableExcelExport.isIncludeHeader();
int rowNum = enableExcelExport.startRow();
short startColumn = enableExcelExport.startColumn();
String fileType = enableExcelExport.fileType();
// 实体类的属性
List fields = Arrays.stream(clazz.getDeclaredFields())
.filter(field -> field.isAnnotationPresent(SheetColumn.class)
&& field.getAnnotation(SheetColumn.class).exported())
.collect(Collectors.toList());
// 所有属性注解中列索引index最大值
int maxIndex = fields.stream().mapToInt(item -> item.getAnnotation(SheetColumn.class).index()).max().getAsInt();
// 创建工作簿，默认类型为xlsx
wb = EXCEL_XLS.equals(fileType) ? new HSSFWorkbook() : new XSSFWorkbook();
st = wb.createSheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName);
row = null;
int currentIndex = maxIndex;
if (includeHeader) {
row = st.createRow(rowNum++);
}
for (Field field : fields) {
SheetColumn sheetColumn = field.getAnnotation(SheetColumn.class);
int index = sheetColumn.index();
int width = sheetColumn.width();
// 通过属性注解SheetColumn的列索引index创建单元格
// 未指定index，即index为-1，则按照所有索引中最大索引+1取值，最大索引+1即为新的最大索引
index = (index == -1 ? ++currentIndex : index) + startColumn;
// row已经被初始化说明需要创建表头
if (Objects.nonNull(row)) {
String name = sheetColumn.name();
boolean required = sheetColumn.required();
cell = row.createCell(index);
fillCell(wb, cell, name, required);
}
// 列宽为-1为自适应列宽
if (width == -1) {
st.autoSizeColumn(index);
} else {
st.setColumnWidth(index, width * 2 * 256);
}
}
for (T datum : data) {
currentIndex = maxIndex;
row = st.createRow(rowNum++);
for (Field field : fields) {
SheetColumn sheetColumn = field.getAnnotation(SheetColumn.class);
int index = sheetColumn.index();
index = (index == -1 ? ++currentIndex : index) + startColumn;
cell = row.createCell(index);
field.setAccessible(true);
Object value = field.get(datum);
fillCell(wb, cell, value, false);
}
}
return wb;
} catch (Exception e) {
// 自定义处理
e.printStackTrace();
}
return null;
}
private static void fillCell(Workbook wb, Cell cell, Object value, boolean required) {
// 必输列表头填充-浅黄色
if (required) {
CellStyle yStyle = wb.createCellStyle();
yStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
yStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(yStyle);
}
if (Objects.isNull(value)) {
return;
}
switch (value.getClass().getName()) {
case "java.lang.String":
cell.setCellValue((String) value);
break;
case "java.lang.Integer":
cell.setCellValue((Integer) value);
break;
case "java.lang.Long":
cell.setCellValue((Long) value);
break;
case "java.lang.Float":
cell.setCellValue((Float) value);
break;
case "java.lang.Double":
cell.setCellValue((Double) value);
break;
case "java.math.BigDecimal":
cell.setCellValue(Double.valueOf(String.valueOf(value)));
break;
case "java.lang.Boolean":
cell.setCellValue((Boolean) value);
break;
case "java.util.Date":
cell.setCellValue((Date) value);
break;
case "java.util.GregorianCalendar":
cell.setCellValue((Calendar) value);
break;
case "org.apache.poi.xssf.usermodel.XSSFRichTextString":
cell.setCellValue((XSSFRichTextString) value);
break;
default:
return;
}
}
}
工具类封装好之后，直接调用即可。
工具类调用示例如下
public static void main(String[] args) throws IOException {
File file = new File("/Users/sch/DATA/Temp/11.xlsx");
List employees = ExcelUtils.parseExcelToList(new FileInputStream(file.getAbsolutePath()), Employee.class);
System.out.println(employees);
File file1 = new File("/Users/sch/DATA/Temp/22.xlsx");
if (!file1.exists()) {
file1.createNewFile();
}
OutputStream outputStream = new FileOutputStream(file1);
ExcelUtils.writeListToWorkbook(initData(), Employee.class, outputStream);
outputStream.close();
}
private static List initData() {
List employees = Lists.newArrayList();
employees.add(new Employee("张三", "男", new BigDecimal(18), "广东省广州市龙洞地铁口A口天桥下", "15211111111", "zhangsan@sanchar.cn"));
employees.add(new Employee("李四", "女", new BigDecimal(16), "广东省深圳市坪山区光祖公园小凉亭", "15299999999", "lisi@sanchar.cn"));
employees.add(new Employee("王五", "男", new BigDecimal(21), "广西省桂林市灵川县桂林电子科技大学花江校区", "15288888888", "wangwu@sanchar.cn"));
return employees;
}
该工具类只是做了一些简单的封装，有兴趣的小伙伴或者该工具类未满足您当前的开发需求，可以基于该工具类进行二次封装处理哈，比如数据导出的时候excel表格需要有下拉框，必输字段非必输字段的颜色控制等都可以集成到该工具类里边
