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;} @Overridepublic 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<String> columnHeaderList;private List<List<String>> listData;private List<Map<String, String>> mapData;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<List<String>>();mapData = new ArrayList<Map<String, String>>();columnHeaderList = new ArrayList<String>();int numOfRows = sheet.getLastRowNum() + 1;for (int i = 0; i < numOfRows; i++) {Row row = sheet.getRow(i);Map<String, String> map = new HashMap<String, String>();List<String> list = new ArrayList<String>();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<String> getTitleList(ExcelReader eh, int maxX) {List<String> result = new ArrayList<String>();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<String> 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<Object> getDataList(Class<?> clazz, ExcelReader eh, int x, int y, List<String> titles) {List<Object> result = new ArrayList<Object>();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:UsersNeoDesktopPOI.xlsx', 'Sheet1');List<String> titles = eh.getTitleList(eh, 7);List<Object> userList = eh.getDataList(UserDTO.class, eh, 7, 4, titles);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<br> * Description: 可以讀取xls,xlsx等文件<br> * Copyright @ 2012~2016 xiaour.github.com<span style='font-size: 1em;'> .All rights reserved.<br></span> * @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<Map<String,Object>> readExcel() { logger.info('開始解析xls...'); sheetNo--;//從1開始及從0開始 InputStream is = null; try { is = new FileInputStream(fileFullPath); } catch (FileNotFoundException e1) { logger.error(e1); } Map<String,Object> dataMap = null; List<Map<String,Object>> dataList= new ArrayList<>(); 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); } 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為NUMERIC 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<Map<String, Object>> dataList; // 對讀取Excel表格標題測試 ExcelReader excelReader = new ExcelReader('D:okcoin-2016-08-3XZS.xls',1); dataList = excelReader.readExcel(); for(Map<String,Object> theMap:dataList){ System.out.println(theMap); } }}

這個類導入相應的jar之后就可以用了哦。

以上這篇Java通過反射將 Excel 解析成對象集合實例就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持好吧啦網。