读取Excel，并以Map形式存储
package com.hxqc.basic.dependency.util; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; public class ReadExcel { /** * * @Description： * @param is 文件流 * @param rowStart 从第几行开始读取（不包含） * @param colStart 从第几列开始读取（不包含） * @return List<Map<String,Object>> */ public List<Map<String, Object>> readXls(InputStream is, int rowStart, int colStart) throws IOException, EncryptedDocumentException, InvalidFormatException { Workbook hssfWorkbook = WorkbookFactory.create(is); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); // Read the Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } List<List<Map<String, String>>> headers = new ArrayList<>(); //read the sheet header for(int i = 0; i < rowStart; i++){ List<Map<String, String>> headerList = new ArrayList<>(); Row headerRow = hssfSheet.getRow(i); for(int j = colStart; j < headerRow.getLastCellNum(); j++){ Map<String, String> map = new HashMap<>(); map.put("cellVal", getValue(headerRow.getCell(j)) == null ? "" : getValue(headerRow.getCell(j)).toString()); Map<String, String> mergedMap = isMergedRegion(hssfSheet, i, j); if(mergedMap.get("isMerged").equals("yes")){ map.put("group", mergedMap.get("index")); } headerList.add(map); } if(i == rowStart - 1){ headers.add(headerList); }else{ headers.add(dealList(headerList)); } } //拼接表头 List<String> keys = joinHeader(headers); System.out.println(keys); for (int rowNum = rowStart; rowNum < hssfSheet.getLastRowNum(); rowNum++) { Row hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = colStart, j = hssfRow.getLastCellNum(); i < j; i++) { map.put(keys.get(i - colStart), getValue(hssfRow.getCell(i))); } list.add(map); } } } hssfWorkbook.close(); return list; } //判断是否是合并单元格，并返回sheet中该合并单元格的索引 private Map<String, String> isMergedRegion(Sheet sheet,int row ,int column) { Map<String, String> map = new HashMap<>(); int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ map.put("isMerged", "yes"); map.put("index", i+""); return map; } } } map.put("isMerged", "no"); return map; } /** * @Description： * @param * @return List<String> */ private List<Map<String,String>> dealList(List<Map<String, String>> headerList) { if(headerList != null && !headerList.isEmpty()){ for(int i = 1; i < headerList.size(); i++){ Map<String, String> map = headerList.get(i); Map<String, String> map1 = headerList.get(i-1); if(StringUtil.isEmpty(map.get("cellVal")) && map1.containsKey("group") && map.containsKey("group") && map.get("group").equals(map1.get("group"))){ headerList.set(i, headerList.get(i-1)); } } return headerList; } return null; } private List<String> joinHeader(final List<List<Map<String, String>>> headers) { final List<Iterator<Map<String, String>>> it = new LinkedList<>(); for (List<Map<String, String>> l : headers) { it.add(l.iterator()); } final List<String> combined = new ArrayList<>(); int index = 1; boolean flag = false; outer: while (true) { final StringBuilder sb = new StringBuilder(); if(flag){ index = 1; } for (final Iterator<Map<String, String>> i : it) { if (!i.hasNext()) { break outer; } Map<String, String> map = i.next(); sb.append(map.get("cellVal")+"-"); } String str = sb.toString(); str = str.replaceAll("-{1,}$", ""); if(combined.contains(str)){ str = str + "-"+index; index++; flag = false; }else{ flag = true; } combined.add(str); } for (final Iterator<Map<String, String>> i : it) { if (i.hasNext()) { throw new IllegalArgumentException("Lists not the same length."); } } return combined; } @SuppressWarnings("static-access") private Object getValue(Cell hssfCell) { Object value = null; if(hssfCell !=null){ switch (hssfCell.getCellType()) { case Cell.CELL_TYPE_FORMULA: try { value = hssfCell.getNumericCellValue(); DecimalFormat df = new DecimalFormat("#.#########"); value = df.format(value); } catch (IllegalStateException e) { value = hssfCell.getRichStringCellValue(); } break; case Cell.CELL_TYPE_NUMERIC: SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); if(HSSFDateUtil.isCellDateFormatted(hssfCell)){ //将excel日期时间转换为标准时间格式 value= sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString(); break; } value = hssfCell.getNumericCellValue(); DecimalFormat df = new DecimalFormat("#.#########"); value = df.format(value); break; case Cell.CELL_TYPE_STRING: value = hssfCell.getStringCellValue(); break; } } return value; } }
读取Excel，并以Map形式存储
