简单的Excel文件导入demo

最新推荐文章于 2023-06-25 09:10:10 发布

转载最新推荐文章于 2023-06-25 09:10:10 发布 · 100 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2968198/blog/1633657

本文介绍了一种使用Java实现的Excel数据导入工具，该工具能够将Excel文件中的数据读取并映射到Java实体类中，适用于批量数据导入场景。通过反射机制，工具能自动匹配实体类属性与Excel列，并进行类型转换。

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<Object> importDataFromExcel(Object vo, InputStream is) {
		List<Object> 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<Object> 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 + "]";
	}
}

表格: