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

方法简介:通过poi等一些列框架工具将excel中的数据读出来封装到一个List

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 "";
		}
	}
}