Excel在web项目里的使用变得越来越广泛，特别是和线下耦合度较高的业务，Excel导入导出变得非常频繁，尽管很多人写了诸多的工具方法，但是终究没有解决一个问题：有效的控制字段英文名称和实际表头名称（这里指Excel中文表头名称）的对应关系，在编码开发过程中，大量时间用于解决这些问题，并因此衍生出大量的工作量，以至于硬性的加了许多约定，在开发过程中，工具方法通用性并不高。因此，基于实体Bean的Annotation化是一个比较不错的尝试。
Excel导入到Bean
public class ExcelToBean{
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private int etimes = 0;
/**
* 从文件读取数据，最好是所有的单元格都是文本格式，日期格式要求yyyy-MM-dd HH:mm:ss,布尔类型0：真，1：假
*
* @param edf
* 数据格式化
*
* @param file
* Excel文件，支持xlsx后缀，xls的没写，基本一样
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
public <E> List<E> readFromFile(ExcelDataFormatter edf, File file, Class<?> clazz) throws Exception {
Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(clazz);
Map<String, String> textToKey = new HashMap<String, String>();
Excel _excel = null;
for (Field field : fields) {
_excel = field.getAnnotation(Excel.class);
if (_excel == null || _excel.skip() == true) {
continue;
}
textToKey.put(_excel.name(), field.getName());
}
InputStream is = new FileInputStream(file);
Workbook wb = new XSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
Row title = sheet.getRow(0);
// 标题数组，后面用到，根据索引去标题名称，通过标题名称去字段名称用到 textToKey
String[] titles = new String[title.getPhysicalNumberOfCells()];
for (int i = 0; i < title.getPhysicalNumberOfCells(); i++) {
titles[i] = title.getCell(i).getStringCellValue();
}
List<E> list = new ArrayList<E>();
E e = null;
int rowIndex = 0;
int columnCount = titles.length;
Cell cell = null;
Row row = null;
for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
row = it.next();
if (rowIndex++ == 0) {
continue;
}
if (row == null) {
break;
}
e = (E)clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
cell = row.getCell(i);
if(null==cell)continue;
etimes = 0;
readCellContent(textToKey.get(titles[i]), fields, cell, e, edf);
}
list.add(e);
}
return list;
}
public static void main(String[] args) throws Exception {
}
/**
* 从单元格读取数据，根据不同的数据类型，使用不同的方式读取<br>
* 有时候经常和我们期待的数据格式不一样，会报异常，<br>
* 我们这里采取强硬的方式<br>
* 使用各种方法，知道尝试到读到数据为止，然后根据Bean的数据类型，进行相应的转换<br>
* 如果尝试完了（总共7次），还是不能得到数据，那么抛个异常出来，没办法了
*
* @param key
* 当前单元格对应的Bean字段
* @param fields
* Bean所有的字段数组
* @param cell
* 单元格对象
* @param obj
* @throws Exception
*/
public void readCellContent(String key, Field[] fields, Cell cell, Object obj, ExcelDataFormatter edf) throws Exception {
Object o = null;
try {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BOOLEAN:
o = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
o = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
o = DateUtil.getJavaDate(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING:
o = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_ERROR:
o = cell.getErrorCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
o = null;
break;
case XSSFCell.CELL_TYPE_FORMULA:
o = cell.getCellFormula();
break;
default:
o = null;
break;
}
if (o == null)
return;
for (Field field : fields) {
field.setAccessible(true);
if (field.getName().equals(key)) {
Boolean bool = true;
Map<String, String> map = null;
if (edf == null) {
bool = false;
} else {
map = edf.get(field.getName());
if (map == null) {
bool = false;
}
}
if (field.getType().equals(Date.class)) {
if (o.getClass().equals(Date.class)) {
field.set(obj, o);
} else {
field.set(obj, sdf.parse(o.toString()));
}
} else if (field.getType().equals(String.class)) {
if (o.getClass().equals(String.class)) {
field.set(obj, o);
} else {
field.set(obj, o.toString());
}
} else if (field.getType().equals(Long.class)) {
if (o.getClass().equals(Long.class)) {
field.set(obj, o);
} else {
field.set(obj, Long.parseLong(o.toString()));
}
} else if (field.getType().equals(Integer.class)) {
if (o.getClass().equals(Integer.class)) {
field.set(obj, o);
} else {
// 检查是否需要转换
String ostr = o.toString();
ostr = ostr.split("\\.").length>0?ostr.split("\\.")[0]:ostr;
if (bool) {
field.set(obj, map.get(ostr) != null ? Integer.parseInt(map.get(ostr)) : Integer.parseInt(ostr));
} else {
field.set(obj, Integer.parseInt(ostr));
}
}
} else if (field.getType().equals(BigDecimal.class)) {
if (o.getClass().equals(BigDecimal.class)) {
field.set(obj, o);
} else {
field.set(obj, BigDecimal.valueOf(Double.parseDouble(o.toString())));
}
} else if (field.getType().equals(Boolean.class)) {
if (o.getClass().equals(Boolean.class)) {
field.set(obj, o);
} else {
// 检查是否需要转换
if (bool) {
field.set(obj, map.get(o.toString()) != null ? Boolean.parseBoolean(map.get(o.toString())) : Boolean.parseBoolean(o.toString()));
} else {
field.set(obj, Boolean.parseBoolean(o.toString()));
}
}
} else if (field.getType().equals(Float.class)) {
if (o.getClass().equals(Float.class)) {
field.set(obj, o);
} else {
field.set(obj, Float.parseFloat(o.toString()));
}
} else if (field.getType().equals(Double.class)) {
if (o.getClass().equals(Double.class)) {
field.set(obj, o);
} else {
field.set(obj, Double.parseDouble(o.toString()));
}
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
// 如果还是读到的数据格式还是不对，只能放弃了
if (etimes > 7) {
throw ex;
}
etimes++;
if (o == null) {
readCellContent(key, fields, cell, obj, edf);
}
}
}
}
Bean导出到Excel
public class BeanToExcel {
/**
* 获得Workbook对象
*
* @param list
* 数据集合
* @return Workbook
* @throws Exception
*/
public static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf) throws Exception {
// 创建工作簿
Workbook wb = new SXSSFWorkbook();
if (list == null || list.size() == 0)
return wb;
// 创建一个工作表sheet
Sheet sheet = wb.createSheet();
// 申明行
Row row = sheet.createRow(0);
// 申明单元格
Cell cell = null;
CreationHelper createHelper = wb.getCreationHelper();
Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(list.get(0).getClass());
XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置前景色
titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(159, 213, 183)));
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setColor(HSSFColor.BROWN.index);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体
titleStyle.setFont(font);
int columnIndex = 0;
Excel excel = null;
for (Field field : fields) {
field.setAccessible(true);
excel = field.getAnnotation(Excel.class);
if (excel == null || excel.skip() == true) {
continue;
}
// 列宽注意乘256
sheet.setColumnWidth(columnIndex, excel.width() * 256);
// 写入标题
cell = row.createCell(columnIndex);
cell.setCellStyle(titleStyle);
cell.setCellValue(excel.name());
columnIndex++;
}
int rowIndex = 1;
CellStyle cs = wb.createCellStyle();
for (T t : list) {
row = sheet.createRow(rowIndex);
columnIndex = 0;
Object o = null;
for (Field field : fields) {
field.setAccessible(true);
// 忽略标记skip的字段
excel = field.getAnnotation(Excel.class);
if (excel == null || excel.skip() == true) {
continue;
}
// 数据
cell = row.createCell(columnIndex);
o = field.get(t);
// 如果数据为空，跳过
if (o == null)
continue;
// 处理日期类型
if (o instanceof Date) {
// excel.dateFormat()获取注解的日期格式，默认yyyy-MM-dd HH:mm:ss
cs.setDataFormat(createHelper.createDataFormat().getFormat(excel.dateFormat()));
cell.setCellStyle(cs);
cell.setCellValue((Date) field.get(t));
} else if (o instanceof Double || o instanceof Float) {// 浮点数
cell.setCellValue(field.get(t).toString());
if (excel.precision() != -1) {
cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_UP : BigDecimal.ROUND_FLOOR).toString());
}
} else if (o instanceof BigDecimal) {// BigDecimal
cell.setCellValue((field.get(t).toString()));
if (excel.precision() != -1) {
cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_UP : BigDecimal.ROUND_FLOOR).toString());
}
} else if (o instanceof Boolean) {// 布尔类型
Boolean bool = (Boolean) field.get(t);
if (edf == null) {
cell.setCellValue(bool);
} else {
Map<String, String> map = edf.get(field.getName());
if (map == null) {
cell.setCellValue(bool);
} else {
cell.setCellValue(map.get(bool.toString().toLowerCase()));
}
}
} else if (o instanceof Integer) {// 整型
Integer intValue = (Integer) field.get(t);
if (edf == null) {
cell.setCellValue(intValue);
} else {
Map<String, String> map = edf.get(field.getName());
if (map == null) {
cell.setCellValue(intValue);
} else {
cell.setCellValue(map.get(intValue.toString()));
}
}
} else {
cell.setCellValue(field.get(t).toString());
}
columnIndex++;
}
rowIndex++;
}
return wb;
}
/**
* 将数据写入到EXCEL文档
*
* @param list
* 数据集合
* @param edf
* 数据格式化，比如有些数字代表的状态，像是0:女，1：男，或者0：正常，1：锁定，变成可读的文字
* 该字段仅仅针对Boolean,Integer两种类型作处理
* @param filePath
* 文件路径
* @throws Exception
*/
public static <T> void writeToFile(List<T> list, ExcelDataFormatter edf, String filePath) throws Exception {
// 创建并获取工作簿对象
Workbook wb = getWorkBook(list, edf);
// 写入到文件
FileOutputStream out = new FileOutputStream(filePath);
wb.write(out);
out.close();
}
数据格式化工具类
public class ExcelDataFormatter {
private Map<String,Map<String,String>> formatter=new HashMap<String, Map<String,String>>();
public void set(String key,Map<String,String> map){
formatter.put(key, map);
}
public Map<String,String> get(String key){
return formatter.get(key);
}
}
反射工具类
public class ReflectUtils {
/**
* 获取成员变量的修饰符
*
* @param clazz
* @param field
* @return
* @throws Exception
*/
public static <T> int getFieldModifier(Class<T> clazz, String field) throws Exception {
// getDeclaredFields可以获取所有修饰符的成员变量，包括private,protected等getFields则不可以
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].getName().equals(field)) {
return fields[i].getModifiers();
}
}
throw new Exception(clazz + " has no field \"" + field + "\"");
}
/**
* 获取成员方法的修饰符
*
* @param clazz
* @param method
* @return
* @throws Exception
*/
public static <T> int getMethodModifier(Class<T> clazz, String method) throws Exception {
// getDeclaredMethods可以获取所有修饰符的成员方法，包括private,protected等getMethods则不可以
Method[] m = clazz.getDeclaredMethods();
for (int i = 0; i < m.length; i++) {
if (m[i].getName().equals(m)) {
return m[i].getModifiers();
}
}
throw new Exception(clazz + " has no method \"" + m + "\"");
}
/**
* [对象]根据成员变量名称获取其值
*
* @param clazzInstance
* @param field
* @return
* @throws NoSuchFieldException
* @throws SecurityException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static <T> Object getFieldValue(Object clazzInstance, Object field) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
Field[] fields = clazzInstance.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].getName().equals(field)) {
// 对于私有变量的访问权限，在这里设置，这样即可访问Private修饰的变量
fields[i].setAccessible(true);
return fields[i].get(clazzInstance);
}
}
return null;
}
/**
* [类]根据成员变量名称获取其值（默认值）
*
* @param clazz
* @param field
* @return
* @throws NoSuchFieldException
* @throws SecurityException
* @throws IllegalArgumentException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static <T> Object getFieldValue(Class<T> clazz, String field) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, InstantiationException {
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].getName().equals(field)) {
// 对于私有变量的访问权限，在这里设置，这样即可访问Private修饰的变量
fields[i].setAccessible(true);
return fields[i].get(clazz.newInstance());
}
}
return null;
}
/**
* 获取所有的成员变量(通过GET，SET方法获取)
*
* @param clazz
* @return
*/
public static <T> String[] getFields(Class<T> clazz) {
Field[] fields = clazz.getDeclaredFields();
String[] fieldsArray = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fieldsArray[i] = fields[i].getName();
}
return fieldsArray;
}
/**
* 获取所有的成员变量,包括父类
*
* @param clazz
* @param superClass
* 是否包括父类
* @return
* @throws Exception
*/
public static <T> Field[] getFields(Class<T> clazz, boolean superClass) throws Exception {
Field[] fields = clazz.getDeclaredFields();
Field[] superFields = null;
if (superClass) {
Class superClazz = clazz.getSuperclass();
if (superClazz != null) {
superFields = superClazz.getDeclaredFields();
}
}
Field[] allFields = null;
if (superFields == null || superFields.length == 0) {
allFields = fields;
} else {
allFields = new Field[fields.length + superFields.length];
for (int i = 0; i < fields.length; i++) {
allFields[i] = fields[i];
}
for (int i = 0; i < superFields.length; i++) {
allFields[fields.length + i] = superFields[i];
}
}
return allFields;
}
/**
* 获取所有的成员变量,包括父类
*
* @param clazz
* @return
* @throws Exception
*/
public static <T> Field[] getClassFieldsAndSuperClassFields(Class<T> clazz) throws Exception {
Field[] fields = clazz.getDeclaredFields();
if (clazz.getSuperclass() == null) {
throw new Exception(clazz.getName() + "没有父类");
}
Field[] superFields = clazz.getSuperclass().getDeclaredFields();
Field[] allFields = new Field[fields.length + superFields.length];
for (int i = 0; i < fields.length; i++) {
allFields[i] = fields[i];
}
for (int i = 0; i < superFields.length; i++) {
allFields[fields.length + i] = superFields[i];
}
return allFields;
}
/**
* 指定类，调用指定的无参方法
*
* @param clazz
* @param method
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws InstantiationException
*/
public static <T> Object invoke(Class<T> clazz, String method) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException {
Object instance = clazz.newInstance();
Method m = clazz.getMethod(method, new Class[] {});
return m.invoke(instance, new Object[] {});
}
/**
* 通过对象，访问其方法
*
* @param clazzInstance
* @param method
* @return
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws InstantiationException
*/
public static <T> Object invoke(Object clazzInstance, String method) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException {
Method m = clazzInstance.getClass().getMethod(method, new Class[] {});
return m.invoke(clazzInstance, new Object[] {});
}
/**
* 指定类，调用指定的方法
*
* @param clazz
* @param method
* @param paramClasses
* @param params
* @return Object
* @throws InstantiationException
* @throws IllegalAccessException
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public static <T> Object invoke(Class<T> clazz, String method, Class<T>[] paramClasses, Object[] params) throws InstantiationException, IllegalAccessException, NoSuchMethodException, SecurityException, IllegalArgumentException, InvocationTargetException {
Object instance = clazz.newInstance();
Method _m = clazz.getMethod(method, paramClasses);
return _m.invoke(instance, params);
}
/**
* 通过类的实例，调用指定的方法
*
* @param clazzInstance
* @param method
* @param paramClasses
* @param params
* @return
* @throws InstantiationException
* @throws IllegalAccessException
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public static <T> Object invoke(Object clazzInstance, String method, Class<T>[] paramClasses, Object[] params) throws InstantiationException, IllegalAccessException, NoSuchMethodException, SecurityException, IllegalArgumentException, InvocationTargetException {
Method _m = clazzInstance.getClass().getMethod(method, paramClasses);
return _m.invoke(clazzInstance, params);
}
// @SuppressWarnings("unchecked")
// public static void main(String[] args) throws Exception {
// // getFields(User.class);
// User u = new User();
// invoke(u, "setName", new Class[] { String.class }, new Object[] { "xx发大水法大水法x" });
// System.out.println(getFieldValue(u, "name"));
// }
Annotation定义类
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD,ElementType.TYPE})
public @interface Excel { //列名
String name() default ""; //宽度
int width() default 20; //忽略该字段
boolean skip() default false; //日期格式化
String dateFormat() default "yyyy-MM-dd HH:mm:ss"; //浮点数的精度
int precision() default -1; //四舍五入
boolean round() default true; }
使用方法
以Excel导入做示例：
如下图所示的Excel数据
在写代码前，按照Annotation的定义对实体Bean进行添加相关标签，例如下图：
示例代码：
public class ElearnSysExcelService extends CustomService{
/**
* 导入Excel文件数据到数据库
* @param file
* @author zhanglongping
* @date 2016-11-10 下午2:10:24
*/
public static void importExcelToDB(File file){
ExcelToBean e = new ExcelToBean();
ExcelDataFormatter edf = new ExcelDataFormatter();
Map<String,String> map = new HashMap<String,String>();
map.put( "准备就绪","111");
map.put( "开始","222");
map.put( "工作","333");
map.put( "结束","444");
edf.set("state", map);
try {
List<ElearnSysUser> list = e.readFromFile(edf, file, ElearnSysUser.class);
for(ElearnSysUser esu:list){
System.out.println(esu.getUserName()+"~~~"+esu.getState()+"~~~"+esu.getRealName());
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
public static void main(String[] args) {
ElearnSysExcelService e = new ElearnSysExcelService();
File file = new File("f://test/excel_sys_user.xlsx");
e.importExcelToDB(file);
}
}
运行结果：
可以看到，Excel中的状态数据已被转换为111,444等数据
参考文献：http://www.xdemo.org
