读取excel到List<Map>,适用于多Sheet

落日映苍穹つ 2021-12-10 16:09 338阅读 0赞

方法简介:通过poi等一些列框架工具将excel中的数据读出来封装到一个List list1中.  
Listlist1中的Map有两对值,一对是Sheet的名称,因为导入时excel中会有多个Sheet存在.另一则是对应的Sheet中的数据List list2.list2中一个map对应excel中一行数据,其中map的key值取的是传入的columnName值,如果columnName为空,map的key值取的是excel中首行的值

package test;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    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.ss.usermodel.Cell;
    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.xssf.usermodel.XSSFWorkbook;
    import com.ibm.icu.text.DecimalFormat;
    
    
    
    
     public class ReadExcelToMap {
    
    
    	/**
    	 * 读取excel到List<Map>中
    	 * @param filePath  需要读取的excel路径
    	 * @param columnName  对应的列名
    	 * @return
    	 */
    	public static List<Map> excel2dc(String filePath,String columnName) {
    		List<Map> dataLst = null;
    		if (filePath != null && filePath.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
    			boolean isExcel2003 = true;
    			if (filePath.matches("^.+\\.(?i)(xlsx)$")) {
    				isExcel2003 = false;
    			}
    
    			File file = new File(filePath);
    			if (file != null && file.exists()) {
    				try {
    					dataLst = read(new FileInputStream(file), isExcel2003,columnName);
    				} catch (Exception e) {
    					
    					e.printStackTrace();
    				}
    				return  dataLst;
    			} else {
    				return dataLst;
    			}
    		} else {
    			return  dataLst;
    		}
    	}
    
    	private static List<Map> read(InputStream inputStream, boolean isExcel2003,String columnName) {
    		List<Map> dataLst = null;
    
    		try {
    			Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
    			dataLst = read((Workbook) wb,columnName);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		return dataLst;
    	}
    
    	/**
    	 * 读取excel
    	 * @param wb
    	 * @param columnName
    	 * @return
    	 */
    	private static List<Map> read(Workbook wb, String columnName) {
    		List<Map> dataLstAll = new ArrayList<Map>();
    		List columnList =null;
    		if(StringUtils.isNotBlank(columnName)) {
    			columnList =Arrays.asList(StringUtils.split(columnName, ","));
    		}
    		
    		String cellValue = "";
    		//每一页的值
    		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
    			Map map = new HashMap<>();
    			List<ArrayList<String>> dataLst = new ArrayList();
    			Sheet sheet = wb.getSheetAt(i);
    			String sheetName = sheet.getSheetName();
    			map.put("sheetName", sheetName);// 保存sheet的名称
    			int totalRows = sheet.getPhysicalNumberOfRows();
    			int totalCells = 0;
    			List<Map> rowLst = new ArrayList<Map>();
    			if (totalRows >= 1 && sheet.getRow(0) != null) {
                    //获取每一行的值
    				for (int r = 0; r < totalRows; ++r) {
    					Row row = sheet.getRow(r);
    					Map rowMap = new HashMap<String, String>();
    					totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    					if (row != null) {
    						if (r == 0) {
    							// 首行,如果没有传入相应字段key,则首行字段直接作为key使用
    							if (columnList==null||columnList.isEmpty() || columnList.size() == 0) {
    								columnList=new ArrayList<String>();
    								for (short c = 0; c < totalCells; ++c) {
    									Cell cell = row.getCell(c);
    									cellValue = getCellValue(cell);
    									columnList.add(cellValue);
    								}
    							}
    						} else {
    							for (short c = 0; c < totalCells; ++c) {
    								Cell cell = row.getCell(c);
    								cellValue = getCellValue(cell);
    								rowMap.put(columnList.get(c), cellValue);
    							}
    						}
    
    					}
    					rowLst.add(rowMap);// 每一页的值
    				}
    			}
    			map.put("pageList", rowLst);//将获取到的每一页的值存到对应map中
    			dataLstAll.add(map);//将map存到总的list中
    		}
    		return dataLstAll;
    	}
    
    
    	private static String getCellValue(Cell cell) {
    		String cellValue = "";
    		if (cell == null) {
    			cellValue = "";
    		} else {
    			if (cell.getCellType() == 0) {
    				if (HSSFDateUtil.isCellDateFormatted(cell)) {
    					cellValue = formatDate(cell.getDateCellValue(), "yyyy-MM-dd hh:mm:ss");
    				} else {
    					cellValue = getRightStr(String.valueOf(cell.getNumericCellValue()));
    				}
    			} else if (1 == cell.getCellType()) {
    				cellValue = cell.getStringCellValue();
    			} else if (4 == cell.getCellType()) {
    				cellValue = String.valueOf(cell.getBooleanCellValue());
    			} else {
    				cellValue = cell.toString();
    			}
    
    		}
    		return cellValue;
    	}
    
    	private static String getRightStr(String sNum) {
    		DecimalFormat decimalFormat = new DecimalFormat("#.000000");
    		String resultStr = decimalFormat.format(new Double(sNum));
    		if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
    			resultStr = resultStr.substring(0, resultStr.indexOf("."));
    		}
    
    		return resultStr;
    	}
    	public static String formatDate(Date date, String formate) {
    		try {
    			SimpleDateFormat simpleDateFormate = new SimpleDateFormat(formate);
    			return simpleDateFormate.format(date);
    		} catch (Exception var3) {
    			return "";
    		}
    	}
    }