思路: 自定义注解, 导出/读取 excel 根据注解自动解析字段
- 注解
package com.excel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelClassAnnotation {
String sheetName();
}
package com.excel;
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 ExcelFieldAnnotation {
int columnIndex();
String headerName() default "";
}
- 注解与excel mapping
package com.excel;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
public class CellTemplate implements Comparable<CellTemplate> {
private final static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:ss:mm");
private Field field;
private String annotation_headerName;
private int annotation_columnIndex;
public static CellTemplate getInstance(Field field) {
CellTemplate mappingCell = new CellTemplate();
Annotation[] annotations = field.getAnnotations();
if (annotations == null || annotations.length == 0) {
return null;
}
for (Annotation annotation : annotations) {
if (annotation instanceof ExcelFieldAnnotation) {
ExcelFieldAnnotation res = (ExcelFieldAnnotation) annotation;
mappingCell.field = field;
mappingCell.annotation_columnIndex = res.columnIndex();
mappingCell.annotation_headerName = res.headerName();
if (mappingCell.annotation_headerName == null || mappingCell.annotation_headerName.trim().length() == 0) {
mappingCell.annotation_headerName = field.getName();
}
return mappingCell;
}
}
return null;
}
public void createCell(Row row, Object obj) throws IllegalArgumentException, IllegalAccessException {
Cell cell = row.createCell(annotation_columnIndex);
boolean isAccessible = field.isAccessible();
field.setAccessible(true);
Class<?> fieldClass = field.getType();
if (fieldClass == Integer.class || fieldClass == int.class) {
int value = field.getInt(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == Short.class || fieldClass == short.class) {
short value = field.getShort(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == Long.class || fieldClass == long.class) {
long value = field.getShort(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == String.class) {
String value = field.get(obj).toString();
cell.setCellValue(value);
cell.setCellType(CellType.STRING);
} else if (fieldClass == Double.class || fieldClass == double.class) {
double value = field.getDouble(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == Float.class || fieldClass == float.class) {
float value = field.getFloat(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == Byte.class || fieldClass == byte.class) {
byte value = field.getByte(obj);
cell.setCellValue(value);
cell.setCellType(CellType.NUMERIC);
} else if (fieldClass == Character.class || fieldClass == char.class) {
char value = field.getChar(obj);
cell.setCellValue(value);
cell.setCellType(CellType.STRING);
} else if (fieldClass == Boolean.class) {
boolean value = field.getBoolean(obj);
cell.setCellValue(value);
cell.setCellType(CellType.BOOLEAN);
} else if (fieldClass == Date.class) {
String value = DATE_FORMAT.format((Date) field.get(obj));
cell.setCellValue(value);
cell.setCellType(CellType.STRING);
} else {
throw new RuntimeException(fieldClass + " is not supported.");
}
field.setAccessible(isAccessible);
}
public void createHeaderCell(Row row) {
Cell cell = row.createCell(annotation_columnIndex);
cell.setCellValue(annotation_headerName);
cell.setCellType(CellType.STRING);
}
public void invokeObjectProperty(Cell cell, Object obj) throws IllegalArgumentException, IllegalAccessException, ParseException {
boolean isAccessible = field.isAccessible();
field.setAccessible(true);
Class<?> fieldClass = field.getType();
if (fieldClass == Integer.class || fieldClass == int.class) {
int value = (int) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Short.class || fieldClass == short.class) {
short value = (short) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Long.class || fieldClass == long.class) {
long value = (long) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == String.class) {
String value = cell.getStringCellValue();
field.set(obj, value);
} else if (fieldClass == Double.class || fieldClass == double.class) {
double value = cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Float.class || fieldClass == float.class) {
float value = (float) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Byte.class || fieldClass == byte.class) {
byte value = (byte) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Character.class || fieldClass == char.class) {
char value = (char) cell.getNumericCellValue();
field.set(obj, value);
} else if (fieldClass == Boolean.class) {
Boolean value = (Boolean) cell.getBooleanCellValue();
field.set(obj, value);
} else if (fieldClass == Date.class) {
Date value = DATE_FORMAT.parse(cell.getStringCellValue());
field.set(obj, value);
} else {
throw new RuntimeException(fieldClass + " is not supported.");
}
field.setAccessible(isAccessible);
}
public int compareTo(CellTemplate obj) {
if (this.annotation_columnIndex == obj.annotation_columnIndex) {
return 0;
}
return this.annotation_columnIndex > obj.annotation_columnIndex ? 1 : -1;
}
}
- 工厂, 根据后缀名调用不同的api
package com.excel;
import java.io.IOException;
public class ExcelUtilsFactory {
static final String EXCEL_XLS = ".xls";
static final String EXCEL_XLSX = ".xlsx";
public static ExcelUtilsBase getInstance(String fileFullName) throws IOException {
ExcelVersionEnum version = getExcelPostfix(fileFullName);
switch (version) {
case XLS:
return new ExcelUtilsXls(fileFullName);
case XLSX:
return new ExcelUtilsXlsx(fileFullName);
default:
throw new IllegalArgumentException(fileFullName);
}
}
private static ExcelVersionEnum getExcelPostfix(String excelFileName) {
if (excelFileName == null) {
throw new IllegalArgumentException(excelFileName);
}
if (excelFileName.endsWith(EXCEL_XLS)) {
return ExcelVersionEnum.XLS;
} else if (excelFileName.endsWith(EXCEL_XLSX)) {
return ExcelVersionEnum.XLSX;
}
throw new IllegalArgumentException(excelFileName);
}
}
package com.excel;
public enum ExcelVersionEnum {
NONE, XLS, XLSX;
}
- 核心类
package com.excel;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public abstract class ExcelUtilsBase {
protected String fileFullName;
public ExcelUtilsBase(String fileFullName) {
this.fileFullName = fileFullName;
}
/**
* add a sheet to the excel located on fileFullName; <br/>
* If the file exist, will add a new sheet to it; if not will create a new
* excel.
*
* @param srcContent
* the excel content
* @param cls
* the type of the content pojo
* @throws IOException
*/
public <T> void create(List<T> srcContent, Class<T> cls) throws IOException {
FileOutputStream outputStream = null;
Workbook workbook = null;
try {
String sheetName = getExportSheetName(cls);
List<CellTemplate> templateList = getExportableFields(cls);
workbook = createOrGetWorkBook(true);
fillWorkBook(workbook, sheetName, templateList, srcContent);
outputStream = new FileOutputStream(fileFullName);
workbook.write(outputStream);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
close(workbook, outputStream);
}
}
public <T> List<T> read(Class<T> cls) throws IOException {
List<T> content = new ArrayList<T>();
FileInputStream inputStream = null;
Workbook workbook = null;
try {
workbook = createOrGetWorkBook(false);
Sheet sheet = readSheet(cls, workbook);
content = readContent(sheet, cls);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
close(workbook, inputStream);
}
return content;
}
protected abstract Workbook createBlankNewWorkbook();
protected abstract Workbook getWorkbook() throws FileNotFoundException, IOException;
protected abstract <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException;
private <T> Workbook createOrGetWorkBook(boolean createNewIfNotFound) throws FileNotFoundException, IOException {
if (fileExist()) {
return getWorkbook();
} else {
if (createNewIfNotFound) {
return createBlankNewWorkbook();
} else {
throw new FileNotFoundException(fileFullName);
}
}
}
protected boolean fileExist() {
File file = new File(fileFullName);
return file.exists();
}
private Sheet getSheetBySheetName(Workbook workbook, String sheetName) {
Iterator<Sheet> iterator = workbook.sheetIterator();
while (iterator.hasNext()) {
Sheet sheet = iterator.next();
if (sheet.getSheetName().equals(sheetName)) {
return sheet;
}
}
throw new RuntimeException("Can't find sheet " + sheetName);
}
private <T> Sheet readSheet(Class<T> cls, Workbook workbook) {
String sheetName = getExportSheetName(cls);
Sheet sheet = getSheetBySheetName(workbook, sheetName);
return sheet;
}
private <T> List<T> readContent(Sheet sheet, Class<T> cls) throws InstantiationException, IllegalAccessException, IllegalArgumentException, ParseException {
List<T> result = new ArrayList<T>();
List<CellTemplate> templateList = getExportableFields(cls);
Iterator<Row> rows = sheet.rowIterator();
skipExcelHeader(rows);
while (rows.hasNext()) {
Row row = rows.next();
T item = readRow(cls, templateList, row);
result.add(item);
}
return result;
}
private void skipExcelHeader(Iterator<Row> rows) {
rows.next();
}
private <T> T readRow(Class<T> cls, List<CellTemplate> templateList, Row row) throws InstantiationException, IllegalAccessException, IllegalArgumentException, ParseException {
T item = cls.newInstance();
Iterator<Cell> cells = row.cellIterator();
int columnIndex = 0;
while (cells.hasNext()) {
Cell cell = cells.next();
templateList.get(columnIndex).invokeObjectProperty(cell, item);
columnIndex++;
}
return item;
}
private <T> String getExportSheetName(Class<T> cls) {
Annotation[] annotations = cls.getAnnotations();
for (Annotation annotation : annotations) {
if (annotation instanceof ExcelClassAnnotation) {
return ((ExcelClassAnnotation) annotation).sheetName();
}
}
throw new IllegalArgumentException(cls + "is not exportable.");
}
private <T> List<CellTemplate> getExportableFields(Class<T> cls) {
List<CellTemplate> templateList = new ArrayList<CellTemplate>();
Field[] declaredFields = cls.getDeclaredFields();
for (Field field : declaredFields) {
CellTemplate template = CellTemplate.getInstance(field);
if (template != null) {
templateList.add(template);
}
}
Collections.sort(templateList);
return templateList;
}
protected void close(Closeable... itemsToClose) throws IOException {
if (itemsToClose != null) {
for (Closeable closeable : itemsToClose) {
if (closeable != null) {
closeable.close();
}
}
}
}
}
package com.excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelUtilsXls extends ExcelUtilsBase {
public ExcelUtilsXls(String fileFullName) {
super(fileFullName);
}
@Override
protected Workbook createBlankNewWorkbook() {
return new HSSFWorkbook();
}
@Override
protected Workbook getWorkbook() throws FileNotFoundException, IOException {
try (FileInputStream inputStream = new FileInputStream(fileFullName)) {
return new HSSFWorkbook(inputStream);
}
}
@Override
public <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException {
HSSFSheet sheet = ((HSSFWorkbook) workbook).createSheet(sheetName);
createHeaderRow(templateList, sheet);
createBodyRows(templateList, srcContent, sheet);
}
private void createHeaderRow(List<CellTemplate> templateList, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
for (CellTemplate template : templateList) {
template.createHeaderCell(headerRow);
}
}
private <T> void createBodyRows(List<CellTemplate> templateList, List<T> srcContent, HSSFSheet sheet) throws IllegalAccessException {
int rownum = 1;
for (T item : srcContent) {
HSSFRow row = sheet.createRow(rownum++);
for (CellTemplate template : templateList) {
template.createCell(row, item);
}
}
}
}
package com.excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtilsXlsx extends ExcelUtilsBase {
public ExcelUtilsXlsx(String fileFullName) {
super(fileFullName);
}
@Override
protected Workbook createBlankNewWorkbook() {
return new XSSFWorkbook();
}
@Override
protected Workbook getWorkbook() throws FileNotFoundException, IOException {
try (FileInputStream inputStream = new FileInputStream(fileFullName)) {
return new XSSFWorkbook(inputStream);
}
}
@Override
public <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException {
XSSFSheet sheet = ((XSSFWorkbook) workbook).createSheet(sheetName);
createHeaderRow(templateList, sheet);
createBodyRows(templateList, srcContent, sheet);
}
private void createHeaderRow(List<CellTemplate> templateList, XSSFSheet sheet) {
XSSFRow headerRow = sheet.createRow(0);
for (CellTemplate template : templateList) {
template.createHeaderCell(headerRow);
}
}
private <T> void createBodyRows(List<CellTemplate> templateList, List<T> srcContent, XSSFSheet sheet) throws IllegalAccessException {
int rownum = 1;
for (T item : srcContent) {
XSSFRow row = sheet.createRow(rownum++);
for (CellTemplate template : templateList) {
template.createCell(row, item);
}
}
}
}
- test
package pojo;
import java.util.Date;
import com.excel.ExcelClassAnnotation;
import com.excel.ExcelFieldAnnotation;
@ExcelClassAnnotation(sheetName = "staff")
public class Record {
@ExcelFieldAnnotation(columnIndex = 1, headerName = "id")
private int id;
@ExcelFieldAnnotation(columnIndex = 3, headerName = "name")
private String name;
@ExcelFieldAnnotation(columnIndex = 0, headerName = "age")
private int age;
@ExcelFieldAnnotation(columnIndex = 2)
private double salary;
@ExcelFieldAnnotation(columnIndex = 4)
private Date aaa;
public Record(){}
public Record(int id, String name, int age, double salary,Date date) {
this.id = id;
this.name = name;
this.age = age;
this.salary = salary;
this.aaa=date;
}
@Override
public String toString() {
return String.format("id:%s\tage:%s\tsalary:%s\tname:%s\tdate:%s", id, age, salary,name,aaa.toLocaleString());
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
}
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.excel.ExcelUtilsFactory;
import pojo.Record;
public class Main {
public static void main(String[] args) throws IOException, IllegalArgumentException, IllegalAccessException, InstantiationException {
test7();
test8();
test9();
// test10();
}
private static void test10() throws IOException {
List<Record> records = new ArrayList<Record>();
records.add(new Record(1, "a", 12, 11.1d,new Date()));
records.add(new Record(2, "b", 13, 12.1d,new Date()));
records.add(new Record(3, "c", 14, 13.1d,new Date()));
ExcelUtilsFactory.getInstance("c:\\work\\b.xls").create(records, Record.class);
ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").create(records, Record.class);
}
private static void test9() throws IOException {
System.out.println(ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").read(Record.class));
System.out.println(ExcelUtilsFactory.getInstance("c:\\work\\b.xls").read(Record.class));
}
static void test7() throws IOException, IllegalArgumentException, IllegalAccessException {
List<Record> records = new ArrayList<Record>();
records.add(new Record(1, "a", 12, 11.1d,new Date()));
records.add(new Record(2, "b", 13, 12.1d,new Date()));
records.add(new Record(3, "c", 14, 13.1d,new Date()));
ExcelUtilsFactory.getInstance("c:\\work\\b.xls").create(records, Record.class);
}
static void test8() throws IOException, IllegalArgumentException, IllegalAccessException {
List<Record> records = new ArrayList<Record>();
records.add(new Record(1, "a", 12, 11.1d,new Date()));
records.add(new Record(2, "b", 13, 12.1d,new Date()));
records.add(new Record(3, "c", 14, 13.1d,new Date()));
ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").create(records, Record.class);
}
}
