java excel 导入demo_简单的Excel文件导入demo

package util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.ArrayList;

import java.util.List;

import org.apache.log4j.Logger;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

import entity.User;

public class ReadExcelUtil {

private static final Logger logger = Logger.getLogger(ReadExcelUtil.class);

public List importDataFromExcel(Object vo, InputStream is) {

List list = new ArrayList<>();

try {

// 创建工作簿

Workbook workbook = WorkbookFactory.create(is);

// 创建sheet

Sheet sheet = workbook.getSheetAt(0);

// 获取sheet中数据的行数

int rows = sheet.getLastRowNum();

// 获取sheet中数据的列数

int columns = sheet.getRow(0).getLastCellNum();

// 利用反射，给实体类的属性进行赋值

Field[] fields = vo.getClass().getDeclaredFields();

// 第一行为标题，从第二行开始读取数据

for (int rowNum = 1; rowNum <= rows; rowNum++) {

Row row = sheet.getRow(rowNum);

for (int colNum = 0; colNum < columns; colNum++) {

Cell cell = row.getCell(colNum);

if (cell == null) {

cell = row.createCell(colNum);

}

cell.setCellType(CellType.STRING);

String value = cell.getStringCellValue();

if (value == null) {

value = "";

}

Field field = fields[colNum];

String fieldName = field.getName();

String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

Method setMethod = null;

if (field.getType() == int.class) {

setMethod = vo.getClass().getMethod(methodName, new Class[] { int.class });

setMethod.invoke(vo, new Object[] { Integer.valueOf(value) });

} else {

setMethod = vo.getClass().getMethod(methodName, new Class[] { String.class });

setMethod.invoke(vo, new Object[] { value });

}

}

list.add(vo);

// 重新创建一个实体类

vo = vo.getClass().getConstructor(new Class[] {}).newInstance(new Object[] {});

}

} catch (Exception e) {

logger.error(e);

} finally {

try {

is.close();

} catch (IOException e) {

logger.error(e);

}

}

return list;

}

public static void main(String[] args) throws FileNotFoundException {

InputStream is = new FileInputStream(new File("E://test.xlsx"));

ReadExcelUtil util = new ReadExcelUtil();

List list = util.importDataFromExcel(new User(), is);

System.out.println(list);

}

}

package entity;

public class User {

private int id;

private String username;

private String password;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

@Override

public String toString() {

return "User [id=" + id + ", username=" + username + ", password=" + password + "]";

}

}

表格:

输出结果:

[User [id=1, username=tang, password=123], User [id=2, username=mi, password=123456], User [id=3, username=ss, password=0.01]]