Java通过反射将 Excel 解析成对象集合实例

Java通过反射将 Excel 解析成对象集合实例

1.这是一个通过java反射机制解析的工具类

2.使用时只需创建对应的对象，并在Excel的第一行填上对应的属性名

3.首先要添加相关的jar包：

poi-3.8.jar

poi-ooxml-3.9.jar

poi-ooxml-schemas-3.9.jar

xmlbeans-2.6.0.jar

4.看一下Excel的内容：

5.创建对应的实体类：

package com.office.user.dto;

public class UserDTO {

private String idUser;

private String userName;

private String gender;

private String birthDate;

private String idType;

private String idNo;

private String mobile;

public String getIdUser() {

return idUser;

}

public void setIdUser(String idUser) {

this.idUser = idUser;

}

public String getUserName() {

return userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

public String getGender() {

return gender;

}

public void setGender(String gender) {

this.gender = gender;

}

public String getBirthDate() {

return birthDate;

}

public void setBirthDate(String birthDate) {

this.birthDate = birthDate;

}

public String getIdType() {

return idType;

}

public void setIdType(String idType) {

this.idType = idType;

}

public String getIdNo() {

return idNo;

}

public void setIdNo(String idNo) {

this.idNo = idNo;

}

public String getMobile() {

return mobile;

}

public void setMobile(String mobile) {

this.mobile = mobile;

}

@Override

public String toString() {

return "UserDTO [idUser=" + idUser + ", userName=" + userName + ", gender=" + gender + ", birthDate="

+ birthDate + ", idType=" + idType + ", idNo=" + idNo + ", mobile=" + mobile + "]";

}

}

6.编写工具类：ExcelReader.java

package com.office.poi;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.lang.reflect.Field;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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

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

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

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 com.office.user.dto.UserDTO;

/**

* Excel 解析工具

*

* @author Neo 2017-5-15

*

* 所需jar： poi-3.8.jar poi-ooxml-3.9.jar poi-ooxml-schemas-3.9.jar

* xmlbeans-2.6.0.jar

*

*/

public class ExcelReader {

private String filePath;

private String sheetName;

private Workbook workBook;

private Sheet sheet;

private List columnHeaderList;

private List> listData;

private List

private boolean flag;

public ExcelReader(String filePath, String sheetName) {

this.filePath = filePath;

this.sheetName = sheetName;

this.flag = false;

this.load();

}

private void load() {

FileInputStream inStream = null;

try {

inStream = new FileInputStream(new File(filePath));

workBook = WorkbookFactory.create(inStream);

sheet = workBook.getSheet(sheetName);

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (inStream != null) {

inStream.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

private String getCellValue(Cell cell) {

String cellValue = "";

DataFormatter formatter = new DataFormatter();

if (cell != null) {

switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

cellValue = formatter.formatCellValue(cell);

} else {

double value = cell.getNumericCellValue();

int intValue = (int) value;

cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);

}

break;

case Cell.CELL_TYPE_STRING:

cellValue = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_BOOLEAN:

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

cellValue = String.valueOf(cell.getCellFormula());

break;

case Cell.CELL_TYPE_BLANK:

cellValue = "";

break;

case Cell.CELL_TYPE_ERROR:

cellValue = "";

break;

default:

cellValue = cell.toString().trim();

break;

}

}

return cellValue.trim();

}

private void getSheetData() {

listData = new ArrayList>();

mapData = new ArrayList

columnHeaderList = new ArrayList();

int numOfRows = sheet.getLastRowNum() + 1;

for (int i = 0; i < numOfRows; i++) {

Row row = sheet.getRow(i);

Map map = new HashMap();

List list = new ArrayList();

if (row != null) {

for (int j = 0; j < row.getLastCellNum(); j++) {

Cell cell = row.getCell(j);

if (i == 0) {

columnHeaderList.add(getCellValue(cell));

} else {

map.put(columnHeaderList.get(j), this.getCellValue(cell));

}

list.add(this.getCellValue(cell));

}

}

if (i > 0) {

mapData.add(map);

}

listData.add(list);

}

flag = true;

}

public String getCellData(int row, int col) {

if (row <= 0 || col <= 0) {

return null;

}

if (!flag) {

this.getSheetData();

}

if (listData.size() >= row && listData.get(row - 1).size() >= col) {

return listData.get(row - 1).get(col - 1);

} else {

return null;

}

}

public String getCellData(int row, String headerName) {

if (row <= 0) {

return null;

}

if (!flag) {

this.getSheetData();

}

if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {

return mapData.get(row - 1).get(headerName);

} else {

return null;

}

}

/**

* 获取标题

*

* @param eh

* @param maxX

* @return

*/

public List getTitleList(ExcelReader eh, int maxX) {

List result = new ArrayList();

for (int i = 1; i <= maxX; i++) {

result.add(eh.getCellData(1, i));

}

return result;

}

/**

* 获取单行对象

*

* @param object

* @param eh

* @param maxX

* @param titles

* @return

*/

public Object getObject(String className, ExcelReader eh, int y, List titles) throws Exception {

Object bean = Class.forName(className).newInstance();

int length = titles.size();

for (int x = 0; x < length; x++) {

try {

Field field = bean.getClass().getDeclaredField(titles.get(x));

field.setAccessible(true);

field.set(bean, eh.getCellData(y, x+1));

} catch (Exception e) {

System.out.println("没有对应的方法：" + e);

}

}

return bean;

}

/**

* 获取Excel数据列表

*

* @param bean

* @param eh

* @param x

* 每行有多少列数据

* @param y

* 整个sheet有多少行数据

* @param titles

* @return

*/

public List

List

String className = clazz.getName();

try {

for (int i = 2; i <=y; i++) {

Object object = eh.getObject(className, eh, i, titles);

result.add(object);

}

} catch (Exception e) {

System.out.println(e);

}

return result;

}

public static void main(String[] args) {

try {

ExcelReader eh = new ExcelReader("C:\\Users\\Neo\\Desktop\\POI.xlsx", "Sheet1");

List titles = eh.getTitleList(eh, 7);

List

for (Object object : userList) {

System.out.println(object);

}

} catch (Exception e) {

System.out.println(e);

}

}

}

6.看一下测试结果：

备注：这是我写于两年前的测试demo，如果生产上有类似需求更建议使用 EasyExcel

补充知识：简单好用-JAVA使用POI解析Excel

相信使用POI的目前已经非常多了，我这边提供一个非常简单便利又通用的POI解析工具类，代码最后有示例代码。可以按照本文直接使用。

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.log4j.LogManager;

import org.apache.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**

*

* Title: ExcelReader

* Description: 可以读取xls,xlsx等文件

* Copyright @ 2012~2016 xiaour.github.com .All rights reserved.

* @author 小鱼儿

* @createDate 2016年8月23日

* @version v1.0

*/

public class ExcelReader {

private POIFSFileSystem fs;

private HSSFWorkbook wb;

private HSSFSheet sheet;

private HSSFRow row;

private static Logger logger = LogManager.getLogger(ExcelReader.class);

private String fileFullPath;

private int sheetNo;

public ExcelReader(String fileFullPath, int sheetNo) {

super();

this.fileFullPath = fileFullPath;

this.sheetNo = sheetNo;

}

/**

* 读取Excel数据内容

* @param InputStream

* @param sheetNo sheet 页号

* @return Map 包含单元格数据内容的Map对象

*/

public List

logger.info("开始解析xls...");

sheetNo--;//从1开始及从0开始

InputStream is = null;

try {

is = new FileInputStream(fileFullPath);

} catch (FileNotFoundException e1) {

logger.error(e1);

}

Map dataMap = null;

List

String value = "";

try {

fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

} catch (IOException e) {

logger.error(e);

}

sheet = wb.getSheetAt(sheetNo);

row = sheet.getRow(0);

// 标题总列数

int colNum = row.getPhysicalNumberOfCells();

String[] keyArray = new String[colNum];

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

keyArray[i] = getCellFormatValue(row.getCell((short) i));

}

int rowNum = sheet.getLastRowNum();

// 正文内容应该从第二行开始,第一行为表头的标题

for (int i = 2; i <= rowNum; i++) {

dataMap= new HashMap<>();

row = sheet.getRow(i);

if(row!=null){

int j = 0;

while (j < colNum) {

//这里把列循环到Map

if(row.getCell((short) j)!=null){

value = getCellFormatValue(row.getCell((short) j)).trim();

dataMap.put(keyArray[j],value);

}

Ikbolq j++;

}

value = "";

dataList.add(dataMap);

}

}

logger.info("解析xls完成...");

try {

if(is!=null)

is.close();

} catch (IOException e) {

logger.error(e.toString());

}

return dataList;

}

/**

* 根据HSSFCell类型设置数据

* @param cell

* @return

*/

private String getCellFormatValue(HSSFCell cell) {

String cellvalue = "";

if (cell != null) {

// 判断当前Cell的Type

switch (cell.getCellType()) {

// 如果当前Cell的Type为NUMERICIkbolq

case HSSFCell.CELL_TYPE_NUMERIC:

case HSSFCell.CELL_TYPE_FORMULA: {

// 判断当前的cell是否为Date

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date date = cell.getDateCellValue();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

cellvalue = sdf.format(date);

}

// 如果是纯数字

else {

// 取得当前Cell的数值

DecimalFormat df = new DecimalFormat("0");

String dfStr = df.format(cell.getNumericCellValue());

cellvalue = dfStr;

}

break;

}

// 如果当前Cell的Type为STRIN

case HSSFCell.CELL_TYPE_STRING:

// 取得当前的Cell字符串

cellvalue = cell.getRichStringCellValue().getString();

break;

// 默认的Cell值

default:

cellvalue = " ";

}

} else {

cellvalue = "";

}

return cellvalue;

}

public static void main(String[] args) {

List

// 对读取Excel表格标题测试

ExcelReader excelReader = new ExcelReader("D:\\okcoin-2016-08-3XZS.xls",1);

dataList = excelReader.readExcel();

for(Map theMap:dataList){

System.out.println(theMap);

}

}

}

这个类导入相应的jar之后就可以用了哦。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。