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

直接传入对应的对象直接生成Excel中的数据集合

Excel模板-表头所在行为1(所在行从一开始)

image.png

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

1.导入依赖包

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml-schemas

3.17

org.apache.poi

poi-ooxml

3.17

org.apache.poi

poi-scratchpad

3.17

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 List readExcel(MultipartFile excelFile, int

headRowIn, Class tClass) {

List 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

* @throws NoSuchFieldException

* @throws IllegalAccessException

*/

private static void handlerRowData(int headRowIn, Class tClass,

List 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 contacts = POIUtil.readExcel(file, 3, ContactsBean.class);