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

2023-07-18 15:50:03 80

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.创建对应的实体类：

packagecom.office.user.dto;
publicclassUserDTO{

	privateStringidUser;
	privateStringuserName;
	privateStringgender;
	privateStringbirthDate;
	privateStringidType;
	privateStringidNo;
	privateStringmobile;
	publicStringgetIdUser(){
		returnidUser;
	}

	publicvoidsetIdUser(StringidUser){
		this.idUser=idUser;
	}

	publicStringgetUserName(){
		returnuserName;
	}

	publicvoidsetUserName(StringuserName){
		this.userName=userName;
	}

	publicStringgetGender(){
		returngender;
	}

	publicvoidsetGender(Stringgender){
		this.gender=gender;
	}

	publicStringgetBirthDate(){
		returnbirthDate;
	}

	publicvoidsetBirthDate(StringbirthDate){
		this.birthDate=birthDate;
	}

	publicStringgetIdType(){
		returnidType;
	}

	publicvoidsetIdType(StringidType){
		this.idType=idType;
	}

	publicStringgetIdNo(){
		returnidNo;
	}

	publicvoidsetIdNo(StringidNo){
		this.idNo=idNo;
	}

	publicStringgetMobile(){
		returnmobile;
	}

	publicvoidsetMobile(Stringmobile){
		this.mobile=mobile;
	}

	@Override
	publicStringtoString(){
		return"UserDTO[idUser="+idUser+",userName="+userName+",gender="+gender+",birthDate="
				+birthDate+",idType="+idType+",idNo="+idNo+",mobile="+mobile+"]";
	}
}

6.编写工具类：ExcelReader.java

packagecom.office.poi;

importjava.io.File;
importjava.io.FileInputStream;
importjava.io.IOException;
importjava.lang.reflect.Field;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.List;
importjava.util.Map;

importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.DataFormatter;
importorg.apache.poi.ss.usermodel.DateUtil;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.ss.usermodel.WorkbookFactory;

importcom.office.user.dto.UserDTO;

/**
*Excel解析工具
*
*@authorNeo2017-5-15
*
*所需jar：poi-3.8.jarpoi-ooxml-3.9.jarpoi-ooxml-schemas-3.9.jar
*xmlbeans-2.6.0.jar
*
*/
publicclassExcelReader{

	privateStringfilePath;
	privateStringsheetName;
	privateWorkbookworkBook;
	privateSheetsheet;
	privateListcolumnHeaderList;
	privateList>listData;
	privateList>mapData;
	privatebooleanflag;

	publicExcelReader(StringfilePath,StringsheetName){
		this.filePath=filePath;
		this.sheetName=sheetName;
		this.flag=false;
		this.load();
	}
	privatevoidload(){
		FileInputStreaminStream=null;
		try{
			inStream=newFileInputStream(newFile(filePath));
			workBook=WorkbookFactory.create(inStream);
			sheet=workBook.getSheet(sheetName);
		}catch(Exceptione){
			e.printStackTrace();
		}finally{
			try{
				if(inStream!=null){
					inStream.close();
				}
			}catch(IOExceptione){
				e.printStackTrace();
			}
		}
	}
	privateStringgetCellValue(Cellcell){
		StringcellValue="";
		DataFormatterformatter=newDataFormatter();
		if(cell!=null){
			switch(cell.getCellType()){
			caseCell.CELL_TYPE_NUMERIC:
				if(DateUtil.isCellDateFormatted(cell)){
					cellValue=formatter.formatCellValue(cell);
				}else{
					doublevalue=cell.getNumericCellValue();
					intintValue=(int)value;
					cellValue=value-intValue==0?String.valueOf(intValue):String.valueOf(value);
				}
				break;
			caseCell.CELL_TYPE_STRING:
				cellValue=cell.getStringCellValue();
				break;
			caseCell.CELL_TYPE_BOOLEAN:
				cellValue=String.valueOf(cell.getBooleanCellValue());
				break;
			caseCell.CELL_TYPE_FORMULA:
				cellValue=String.valueOf(cell.getCellFormula());
				break;
			caseCell.CELL_TYPE_BLANK:
				cellValue="";
				break;
			caseCell.CELL_TYPE_ERROR:
				cellValue="";
				break;
			default:
				cellValue=cell.toString().trim();
				break;
			}
		}
		returncellValue.trim();
	}
	privatevoidgetSheetData(){
		listData=newArrayList>();
		mapData=newArrayList>();
		columnHeaderList=newArrayList();
		intnumOfRows=sheet.getLastRowNum()+1;
		for(inti=0;imap=newHashMap();
			Listlist=newArrayList();
			if(row!=null){
				for(intj=0;j0){
				mapData.add(map);
			}
			listData.add(list);
		}
		flag=true;
	}
	publicStringgetCellData(introw,intcol){
		if(row<=0||col<=0){
			returnnull;
		}
		if(!flag){
			this.getSheetData();
		}
		if(listData.size()>=row&&listData.get(row-1).size()>=col){
			returnlistData.get(row-1).get(col-1);
		}else{
			returnnull;
		}
	}
	publicStringgetCellData(introw,StringheaderName){
		if(row<=0){
			returnnull;
		}
		if(!flag){
			this.getSheetData();
		}
		if(mapData.size()>=row&&mapData.get(row-1).containsKey(headerName)){
			returnmapData.get(row-1).get(headerName);
		}else{
			returnnull;
		}
	}

	/**
	*获取标题
	*
	*@parameh
	*@parammaxX
	*@return
	*/
	publicListgetTitleList(ExcelReadereh,intmaxX){
		Listresult=newArrayList();
		for(inti=1;i<=maxX;i++){
			result.add(eh.getCellData(1,i));
		}
		returnresult;
	}

	/**
	*获取单行对象
	*
	*@paramobject
	*@parameh
	*@parammaxX
	*@paramtitles
	*@return
	*/
	publicObjectgetObject(StringclassName,ExcelReadereh,inty,Listtitles)throwsException{
		Objectbean=Class.forName(className).newInstance();
		intlength=titles.size();
		for(intx=0;xgetDataList(Classclazz,ExcelReadereh,intx,inty,Listtitles){
		List

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

热门推荐

随机推荐