功能:解析Excel是个很常见的需求,笔者文件是通过注解形式结合poi来解析Excel文件。
如将下面的Excel映射成Student类。
第一步:引入相关依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
第二步:创建相关注解:
第一个为主注解,用于规定映射对象中字段和标题的对应关系。
package com.psp.poidemo;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.util.Map;
/**
* @author psp
* @description 用于标识对应字符对应文件上的属性
* @date 2020/3/26
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface FileAttributes {
String value();
String defualtValue() default "";
boolean isNull() default true;
boolean hasMap() default false;
FileProperty[] propertyList() default {};
Class type() default String.class;
}
第二个注解用于解析单元格中内容的一些特定映射关系,比如单元格中假设存在性别字段,男映射成对象中的M,女映射成对象中的F,此时需要用到这个注解。
package com.psp.poidemo;
import java.lang.annotation.*;
/**
* @author psp
* @description 内容映射关系
* @date 2020/3/26
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface FileProperty {
String key();
String value();
}
第三步:创建映射对象类:
package com.psp.poidemo;
import lombok.Data;
/**
* @author psp
* @description 学生测试类
* @date 2020/3/26
*/
@Data
public class Student {
private String id;
@FileAttributes(value = "学号",isNull = false)
private String studentNo;
@FileAttributes(value = "姓名",isNull = false)
private String studentName;
@FileAttributes(value = "性别",hasMap = true,defualtValue = "M",propertyList = {
@FileProperty(key = "男",value = "M"),
@FileProperty(key = "女",value = "F")
})
private String sex;
}
第四步:逻辑代码的编写
package com.psp.poidemo;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
/**
* @author psp
* @description 解析文件工具类
* @date 2020/3/26
*/
public class ParsingFileUtils {
//文件后缀
private static final String FILE_CONTENT_TYPE_XLSX="xlsx";
private static final String FILE_CONTENT_TYPE_XLS="xls";
private static Logger logger= LoggerFactory.getLogger(ParsingFileUtils.class);
/**
* 将Excel文件转换成list集合
* @param in 文件输入流
* @param suffix 文件后缀
* @param clazz 文件转换对象类型
* @return list集合
* @throws IOException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static List<Object> parsingFileXls(InputStream in,String suffix,Class clazz) throws IOException, IllegalAccessException, InstantiationException {
Field[] fields = clazz.getDeclaredFields();
ConcurrentHashMap<String,Integer> titlesMap=new ConcurrentHashMap<>(8);
Workbook book;
try {
//判断文件类型,进行对象构造
if (FILE_CONTENT_TYPE_XLSX.equals(suffix)) {
book = new XSSFWorkbook(in);
} else if (FILE_CONTENT_TYPE_XLS.equals(suffix)) {
book = new HSSFWorkbook(in);
}else {
throw new IllegalArgumentException("file type error!");
}
}catch (IOException e){
logger.error(e.getLocalizedMessage());
throw e;
}
//此处获取第一个sheet页进行操作,如需要多sheet页操作,将其变成for循环
Sheet sheet =book.getSheetAt(0);
//从类中属性注解进行字段-标题映射
for (Field field:fields){
FileAttributes fileAttributes = field.getAnnotation(FileAttributes.class);
if (fileAttributes==null){
continue;
}
titlesMap.put(fileAttributes.value(),-1);
}
//将字段对应标题所述的列进行映射
mappingColumns(titlesMap,sheet.getRow(sheet.getFirstRowNum()));
List<Object> result =new ArrayList<>(8);
//遍历所有行,进行添加值,此处优化可改为多线程操作
for (int i=sheet.getFirstRowNum()+1;i<sheet.getLastRowNum()+1;i++){
Row row = sheet.getRow(i);
Object target = clazz.newInstance();
//解析每一列,并将列属性给对象赋值
parsingRow(titlesMap, row, fields, target);
if(target!=null) {
result.add(target);
}
}
return result;
}
/**
* description:
* @param titleMap 列与字段映射集合
* @param row 列标题
* @return
* @author psp
* @date 2020/3/26 23:13
*/
private static void mappingColumns(ConcurrentHashMap<String,Integer> titleMap,Row row){
for (int i=row.getFirstCellNum();i<row.getLastCellNum();i++){
Cell cell = row.getCell(i);
Integer integer = titleMap.get(cell.getStringCellValue());
if (integer != null){
titleMap.put(cell.getStringCellValue(),i);
}
}
}
/**
*
* @param titleMap 列与字段映射集合
* @param row 每一行数据
* @param fields 目标对象属性集合
* @param target 目标对象
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
private static void parsingRow(ConcurrentHashMap<String,Integer> titleMap,Row row,Field[] fields,Object target) throws IllegalArgumentException, IllegalAccessException {
for (Field field:fields){
FileAttributes fieldAnnotation = field.getAnnotation(FileAttributes.class);
if (fieldAnnotation == null){
continue;
}
field.setAccessible(true);
if (!StringUtils.isEmpty(fieldAnnotation.defualtValue())){
field.set(target,fieldAnnotation.defualtValue());
}
Integer integer = titleMap.get(fieldAnnotation.value());
if (integer==-1){
if (!fieldAnnotation.isNull()&&StringUtils.isEmpty(fieldAnnotation.defualtValue())){
throw new IllegalArgumentException("we need "+fieldAnnotation.value()+" ,but the excel doesn't!");
}
continue;
}
Cell cell = row.getCell(integer);
Object o = parsingCell(cell, fieldAnnotation);
if (o==null&&!fieldAnnotation.isNull()){
throw new IllegalArgumentException("the ("+row.getRowNum()+","+integer+")cell Not allowed to be empty");
}
if (fieldAnnotation.hasMap()&&o instanceof String){
o= mappingProperty(fieldAnnotation,o);
}
field.set(target,o);
}
}
/**
*
* @param cell 单元格
* @param fileAttributes 注解类型
* @return 注解中type属性类型
*/
private static Object parsingCell(Cell cell,FileAttributes fileAttributes){
if (cell==null){
return null;
}
Class type = fileAttributes.type();
if (type ==Date.class){
return cell.getDateCellValue();
}else if(type== boolean.class){
return cell.getBooleanCellValue();
}else if(type==double.class){
return cell.getNumericCellValue();
}else if(type==RichTextString.class){
return cell.getRichStringCellValue();
}else if(type==Integer.class){
String stringCellValue = cell.getStringCellValue();
return Integer.parseInt(stringCellValue);
} else{
try {
return cell.getStringCellValue();
}catch (Exception e){
if (cell.getCellTypeEnum()==CellType.NUMERIC) {
return ""+cell.getNumericCellValue();
}
throw e;
}
}
}
/**
* 由于存在一个字段的列内容与数据存储的 数据有一定的映射关系,该方法就是对于解析单元格内容与数据库存储数据的映射关系
* @param fileAttributes 注解属性
* @param o 单元格内容
* @return 映射后的数据库存储值
*/
private static String mappingProperty(FileAttributes fileAttributes,Object o){
String str= (String) o;
for (FileProperty fileProperty:fileAttributes.propertyList()){
if (fileProperty.key().equals(str)){
return fileProperty.value();
}
}
throw new IllegalArgumentException("No correspondence exists");
}
}
最后我们来看一下调试效果吧: