Java Excel导入
1、引入jar包，<poi.version>3.8</poi.version>
1 <dependency> 2 <groupId>org.apache.poi</groupId> 3 <artifactId>poi</artifactId> 4 <version>${poi.version}</version> 5 </dependency> 6 <dependency> 7 <groupId>org.apache.poi</groupId> 8 <artifactId>poi-ooxml</artifactId> 9 <version>${poi.version}</version> 10 </dependency>
2、Excel单元格参数类定义
ExcelCellImportParam.java
1 /** 2 * The Class ExcelCellImportParam. 3 * 4 * @author 下一个雨季~ 5 * @version 1.0 6 * @date 2015-8-19 7 */ 8 public class ExcelCellImportParam { 9 10 /** 数据所在Excel单元格下标. */ 11 private int cellIndex; 12 13 /** 需要被赋值的属性名称. */ 14 private String propertyName; 15 16 /** 17 * Instantiates a new excel cell import param. 18 * 19 * @param cellIndex the cell index 20 * @param propertyName the property name 21 */ 22 public ExcelCellImportParam(int cellIndex, String propertyName){ 23 this.cellIndex = cellIndex; 24 this.propertyName = propertyName; 25 } 26 27 /** 28 * Sets the value. 29 * 30 * @param entity the entity 31 * @param row the row 32 * @throws NoSuchMethodException 33 * @throws InvocationTargetException 34 * @throws IllegalAccessException 35 */ 36 public void setValue(Object entity, Row row) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException { 37 if(row == null){ 38 throw new NullPointerException("Excel row is null."); 39 } 40 if(entity == null){ 41 throw new NullPointerException("Excel convert Object is null."); 42 } 43 Class<?> clazz = PropertyUtils.getPropertyType(entity, this.getPropertyName()); 44 Object cellValue = getCellValue(row.getCell(this.getCellIndex()), clazz); 45 PropertyUtils.setProperty(entity, this.getPropertyName(), cellValue); 46 } 47 48 /** 49 * Gets the cell value. 50 * 51 * @param cell the cell 52 * @param propertyType the property type 53 * @return the cell value 54 */ 55 private Object getCellValue(Cell cell, Class<?> propertyType){ 56 Object cellValue = null; 57 if(cell != null){ 58 if(propertyType.isAssignableFrom(Boolean.class)) 59 cellValue = Boolean.valueOf(getCellValue(cell)); 60 else if(propertyType.isAssignableFrom(Double.class)) 61 cellValue = Double.valueOf(getCellValue(cell)); 62 else if(propertyType.isAssignableFrom(Float.class)) 63 cellValue = Float.valueOf(getCellValue(cell)); 64 else if(propertyType.isAssignableFrom(BigDecimal.class)) 65 cellValue = new BigDecimal(getCellValue(cell)); 66 else if(propertyType.isAssignableFrom(Integer.class)) 67 cellValue = Integer.valueOf(getCellValue(cell)); 68 else if(propertyType.isAssignableFrom(Long.class)) 69 cellValue = Long.valueOf(getCellValue(cell)); 70 else if(propertyType.isAssignableFrom(Date.class)) 71 cellValue = getDateCellValue(cell); 72 else if(propertyType.isAssignableFrom(String.class)) 73 cellValue = getCellValue(cell); 74 else 75 throw new IllegalArgumentException("Invalid data type."); 76 } 77 return cellValue; 78 } 79 80 /** 81 * Gets the cell value. 82 * 83 * @param cell the cell 84 * @return the cell value 85 */ 86 private String getCellValue(Cell cell){ 87 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 88 return cell.getStringCellValue(); 89 } 90 91 /** 92 * Gets the date cell value. 93 * 94 * @param cell the cell 95 * @return the date cell value 96 */ 97 private Object getDateCellValue(Cell cell){ 98 switch (cell.getCellType()) { 99 case Cell.CELL_TYPE_BLANK: 100 return null; 101 case Cell.CELL_TYPE_NUMERIC: 102 return cell.getDateCellValue(); 103 default: 104 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 105 String time = cell.getStringCellValue(); 106 return DateUtils.parse(time, DateUtils.DEFAULT_DATETIME_PATTERN); 107 } 108 } 109 110 /** 111 * Gets the cell index. 112 * 113 * @return the cell index 114 */ 115 public int getCellIndex() { 116 return cellIndex; 117 } 118 119 /** 120 * Sets the cell index. 121 * 122 * @param cellIndex the new cell index 123 */ 124 public void setCellIndex(int cellIndex) { 125 this.cellIndex = cellIndex; 126 } 127 128 /** 129 * Gets the property name. 130 * 131 * @return the property name 132 */ 133 public String getPropertyName() { 134 return propertyName; 135 } 136 137 /** 138 * Sets the property name. 139 * 140 * @param propertyName the new property name 141 */ 142 public void setPropertyName(String propertyName) { 143 this.propertyName = propertyName; 144 } 145 146 }
DateUtils.java
1 /** 2 * The Class DateUtils. 3 * 4 * @author Derek 5 * @version 1.0, 2014-1-26 6 */ 7 public class DateUtils { 8 /** 默认的时间格式化模式。. */ 9 public static final String DEFAULT_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss"; 10 /** 长型的日期格式化模式。. */ 11 public static final String LONG_DATE_PATTERN = "yyyy-MM-dd"; 12 /** 短型的日期格式化模式。. */ 13 public static final String SHORT_DATE_PATTERN = "yy-MM-dd"; 14 /** 长型的时间格式化模式。. */ 15 public static final String LONG_TIME_PATTERN = "HH:mm:ss"; 16 /** 短型的时间格式化模式。. */ 17 public static final String SHORT_TIME_PATTERN = "HH:mm"; 18 /** 19 * 从给定字符串的开始解析文本，以生成一个日期。该方法不使用给定字符串的整个文本。. 20 * 21 * @param input 一个 {@code String}，应从其开始处进行解析。 22 * @param pattern 描述日期和时间格式的模式。 23 * @param locale 给定的语言环境（可变参数）。 24 * @return {@code input} 中包含的日期字符串的 {@code java.util.Date} 对象。 25 */ 26 public static Date parse(String input, String pattern, Locale... locale) { 27 if (StringUtils.isEmpty(input)) { 28 return null; 29 } 30 if (StringUtils.isEmpty(pattern)) { 31 pattern = DEFAULT_DATETIME_PATTERN; 32 } 33 try { 34 DateFormat df = new SimpleDateFormat(pattern, 35 zlocale == null || locale.length == 0 ? Locale.CHINESE : locale[0]); 36 return df.parse(input); 37 } catch (ParseException ex) { 38 return null; 39 } 40 } 41 }
ExcelUtil.java
1 /** 2 * The Class ExcelUtil. 3 * 4 * @author 下一个雨季~ 5 * @version 1.0 6 * @date 2015-8-19 7 */ 8 public class ExcelUtil { 9 10 /** The Constant EXTENSION_XLS. */ 11 public final static String EXTENSION_XLS = "xls"; 12 13 /** The Constant EXTENSION_XLSX. */ 14 public final static String EXTENSION_XLSX = "xlsx"; 15 16 /** 17 * Gets the workbook. 18 * 19 * @param format the format 20 * @param ins the ins 21 * @return the workbook 22 * @throws IOException Signals that an I/O exception has occurred. 23 */ 24 public static Workbook getWorkbook(String format, InputStream ins) throws IOException{ 25 if(EXTENSION_XLS.equals(format)) 26 return new HSSFWorkbook(ins); 27 else if(EXTENSION_XLSX.equals(format)) 28 return new XSSFWorkbook(ins); 29 else 30 throw new IllegalArgumentException("Unsupported file type"); 31 } 32 33 /** 34 * 导入Excel表格. 35 * 36 * @param <T> the generic type 37 * @param clazz the clazz - 导入实体对象 38 * @param workbook the workbook - Excel对象 39 * @param params the params - 导入的参数列表 40 * @param sheetIndex the sheet index - Excel的sheet索引 41 * @return the list - 返回导入的数据集合 42 * @throws IllegalAccessException the illegal access exception 43 * @throws InvocationTargetException the invocation target exception 44 * @throws NoSuchMethodException the no such method exception 45 * @throws InstantiationException 46 */ 47 public static <T> List<T> doExcelImport(Class<T> clazz, Workbook workbook, List<ExcelCellImportParam> params, int sheetIndex) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException{ 48 Sheet sheet = workbook.getSheetAt(sheetIndex); 49 Iterator<Row> rows = sheet.iterator(); 50 List<T> list = null; 51 52 if(rows.hasNext()){ 53 //标题行跨过 54 rows.next(); 55 list = new ArrayList<T>(); 56 while(rows.hasNext()){ 57 Row row = rows.next(); 58 T entity = clazz.newInstance(); 59 for (ExcelCellImportParam param : params) { 60 param.setValue(entity, row); 61 } 62 list.add(entity); 63 } 64 } 65 return list; 66 } 67 }
下面在贴上ExcelCellImportParam具体参数示例：
1 /** 2 * 导入Excel参数列表. 3 * 4 * @return the params 5 */ 6 private List<ExcelCellImportParam> getParams(){ 7 List<ExcelCellImportParam> list = new ArrayList<ExcelCellImportParam>(); 8 //0表示Excel单元格第一列的为username值，username对应实体propertyName 9 ExcelCellImportParam username = new ExcelCellImportParam(0, "username"); 10 ExcelCellImportParam phone = new ExcelCellImportParam(1, "phone"); 11 ExcelCellImportParam addr = new ExcelCellImportParam(2, "addr"); 12 13 list.add(username); 14 list.add(phone); 15 list.add(addr); 16 return list; 17 }
以上代码只是提供一个思路，实际使用请根据需求修改。
