Excel基于POI导入导出的Annotation化之路（一）
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
