直接傳入對應(yīng)的對象直接生成Excel中的數(shù)據(jù)集合
Excel模板-表頭所在行為1（所在行從一開始）

image.png

首先需要導(dǎo)入的Excel表頭行中要添加批注坦康，內(nèi)容為對應(yīng)JavaBean的屬性值脖捻。處理導(dǎo)入數(shù)據(jù)時是根據(jù)批注使用反射來填充bean對應(yīng)的屬性值柑潦。

1.導(dǎo)入依賴包

    <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 包含文件數(shù)據(jù)
 * @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);
            //獲得數(shù)據(jù)行數(shù)
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            if (physicalNumberOfRows > 0) {
                handlerRowData(headRowIn, tClass, rowLists, sheet, 
                 physicalNumberOfRows);
            }
        }
        return rowLists;
    } catch (Exception e) {
        e.printStackTrace();
        throw new RuntimeException();
    }
}

/**
 * 處理每一行數(shù)據(jù)
 *
 * @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");
    }
}

/**
 * 處理列數(shù)據(jù)
 *
 * @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：數(shù)字類型
            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.創(chuàng)建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.使用

//參數(shù)3 為表頭所在行 從一開始
List<ContactsBean> contacts = POIUtil.readExcel(file, 3, ContactsBean.class);