使用poi操作Excel的处理工具类自动解析填充数据

直接传入对应的对象直接生成Excel中的数据集合
Excel模板-表头所在行为1（所在行从一开始）

image.png

首先需要导入的Excel表头行中要添加批注，内容为对应JavaBean的属性值。处理导入数据时是根据批注使用反射来填充bean对应的属性值。

1.导入依赖包

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
   <!--用于处理word、ppt、viso、outlook-->
   <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-scratchpad</artifactId>
        <version>3.17</version>
    </dependency>

2.工具类

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.*;
/**
 * @author chenzan
 * @version V1.0
 * @modifier
 * @modifier-data
 */
public class POIUtil {
/**
 * 读取excel文件
 *
 * @param excelFile 包含文件数据
 * @param headRowIn 表头所在所在行 1-n
 * @return
 */
public static <T> List<T> readExcel(MultipartFile excelFile, int 
  headRowIn, Class<T> tClass) {
    List<T> rowLists = new ArrayList<>();
    try {
        InputStream inputStream = excelFile.getInputStream();
        Workbook workbook = WorkbookFactory.create(inputStream);
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            //获得数据行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            if (physicalNumberOfRows > 0) {
                handlerRowData(headRowIn, tClass, rowLists, sheet, 
                 physicalNumberOfRows);
            }
        }
        return rowLists;
    } catch (Exception e) {
        e.printStackTrace();
        throw new RuntimeException();
    }
}

/**
 * 处理每一行数据
 *
 * @param headRowIn
 * @param tClass
 * @param rowLists
 * @param sheet
 * @param physicalNumberOfRows
 * @param <T>
 * @throws NoSuchFieldException
 * @throws IllegalAccessException
 */
private static <T> void handlerRowData(int headRowIn, Class<T> tClass, 
 List<T> rowLists, Sheet sheet, int physicalNumberOfRows)
        throws IllegalAccessException, InstantiationException {
    Row headRow = sheet.getRow(headRowIn - 1);
    if (headRow != null) {
        int cellCount = headRow.getPhysicalNumberOfCells();
        for (int j = headRowIn; j < physicalNumberOfRows; j++) {
            T t = tClass.newInstance();
            for (int k = 0; k < cellCount; k++) {
                Cell cell = sheet.getRow(j).getCell(k);
                String cellValue;
                cellValue = handlerCellValue(cell);
                Cell headCell = headRow.getCell(k);
                //表头批注
                String cellCode = headCell.getCellComment().getString().toString();
                try {
                    Field field = tClass.getDeclaredField(cellCode);
                    field.setAccessible(true);
                    field.set(t, cellValue);
                } catch (NoSuchFieldException e) {
                    e.printStackTrace();
                }
            }
            rowLists.add(t);
        }
    } else {
        throw new RuntimeException("please check headRowIn parameters");
    }
}

/**
 * 处理列数据
 *
 * @param cell
 * @return
 */
private static String handlerCellValue(Cell cell) {
    String cellValue;
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:// true：日期类型；false：数字类型
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                cellValue = dateFormat.format(date);
            } else {
                Double doubleValue = cell.getNumericCellValue();
                cellValue = String.valueOf(doubleValue.longValue());
            }
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        default:
            HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
            cellValue = hSSFDataFormatter.formatCellValue(cell);
            break;
    }
    return cellValue;
   }
}

3.创建bean

public class ContactBean implements Serializable {
private String staffId;//员工Id
private String name;
private String mobile;
private String deptName;//部门
private String title;//职位
private String jobNumber;//工号

public String getStaffId() {
    return staffId;
}
public void setStaffId(String staffId) {
    this.staffId = staffId;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getMobile() {
    return mobile;
}
public void setMobile(String mobile) {
    this.mobile = mobile;
}
public String getDeptName() {
    return deptName;
}
public void setDeptName(String deptName) {
    this.deptName = deptName;
}
public String getTitle() {
    return title;
}
public void setTitle(String title) {
    this.title = title;
}
public String getJobNumber() {
    return jobNumber;
}
public void setJobNumber(String jobNumber) {
    this.jobNumber = jobNumber;
}
}

4.使用

//参数3 为表头所在行 从一开始
List<ContactsBean> contacts = POIUtil.readExcel(file, 3, ContactsBean.class);