Java (新)将Excel数据读取到ListMap

Java (新)将Excel数据读取到ListMap

Maven依赖: pom.xml

<!-- excel -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.8</version>
</dependency>

Controller访问类:

    @Controller
    @RequestMapping("/excel")
    public class ImportExcelController {
			
	  @RequestMapping(value = "/readto/listmap", method = { RequestMethod.GET, RequestMethod.POST})
	  @ResponseBody
	  public String readExcelToListMap(MultipartFile upfile,HttpServletRequest request, HttpServletResponse response) {		
		List<Map<String, Object>> listMap = ExcelUtil.readExcelDataToListMap(upfile);
        if (CollectionUtils.isNotEmpty(listMap)) {
			for (Map<String, Object> map : listMap) {
				System.out.println(map.toString());
			}
			return "导入成功";
		}else{
			return "文件内容读取为空!";
		}		
	}	
}

Excel工具类:

    import java.io.BufferedReader;
    import java.io.ByteArrayInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DataFormatter;
    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 org.springframework.web.multipart.MultipartFile;
    import com.test.common.pojo.ReturnResult;


    public class ExcelUtil {
              
        /**
         * 将Excel数据读取到ListMap
         * @param file
         * 仅支持 csv,xls,XLS,XLs,Xls,xlsx
         */
        public static List<Map<String,Object>> readExcelDataToListMap(MultipartFile file) {
          if (null != file) {
              String fileName = file.getOriginalFilename();	// 获取文件名
              if (StringUtils.isNotBlank(fileName)) {
                  if (fileName.toLowerCase().matches("^.*(csv)$")) {
                      return getResource(file.getBytes());
                  }else{
                      if (fileName.toLowerCase().matches("^.*(xls|XLS|XLs|Xls|xlsx)$")) {
                          String edition_2003 = "^.+\\.(?i)(xls)$";		// Excel_2003版本
                          String edition_2007 = "^.+\\.(?i)(xlsx)$";		// Excel_2007版本
                          // 根据Excel版本创建对象
                          Workbook wb = null;
                          if (fileName.matches(edition_2003)) {
                              wb = new HSSFWorkbook(file.getInputStream());
                          } else {
                              if (fileName.matches(edition_2007)) {
                                  wb = new XSSFWorkbook(file.getInputStream());
                              }
                          }
                          // 读取Excel里面的数据
                          if (null != wb) {
                              // 得到第一个shell
                              Sheet sheet = wb.getSheetAt(0);
                              // 得到Excel的行数
                              int totalRows = sheet.getPhysicalNumberOfRows();
                              // 得到Excel的列数(前提是有行数)
                              int totalCells = 0;
                              if (totalRows > 1 && sheet.getRow(0) != null) {
                                  totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
                              }
                              List<Map<String, Object>> listMap = new ArrayList<>();
                              // 循环Excel行数
                              for (int r = 1; r < totalRows; r++) {
                                  Row row = sheet.getRow(r);
                                  if (row == null) {
                                      continue;
                                  }
                                  // 循环Excel的列
                                  Map<String, Object> map = new HashMap<>();
                                  for (int c = 0; c < totalCells; c++) {
                                      Cell cell = row.getCell(c);
                                      if (null != cell) {
                                          CellType cellType = cell.getCellType();
                                          if (cellType == CellType.NUMERIC) {
                                              if (DateUtil.isCellDateFormatted(cell)){
                                                  map.put(c+"", DateUnit.getTimeByFormat(cell.getDateCellValue(),"yyyy-MM-dd HH:mm:ss"));
                                              }else {
                                                  DataFormatter dataFormatter = new DataFormatter();
                                                  dataFormatter.addFormat("###########", null);
                                                  String str = dataFormatter.formatCellValue(cell);
                                                  if (StringUtils.isNotBlank(str)) {
                                                      map.put(c+"",str);
                                                  }
                                              }
                                          }else{
                                              String str = String.valueOf(cell);
                                              if (StringUtils.isNotBlank(str)) {
                                                  map.put(c+"",str);
                                              }
                                          }
                                      }
                                  }
                                  // 添加到list
                                  if (CollectionUtil.isNotEmpty(map)) {
                                      listMap.add(map);
                                  }
                              }
                              return listMap;
                          }else{
                              throw new RuntimeException("Excel文件创建对象失败!");
                          }
                      }else{
                          throw new RuntimeException("文件格式错误,仅支持 xls,XLS,XLs,Xls,xlsx后缀的文件!");
                      }
                  }
              }else{
                  throw new RuntimeException("文件名不能为空!");
              }
          }else{
              throw new RuntimeException("文件不能为空!");
          }
        }
        
        /** 获取csv文件内容 **/
        private static List<Map<String,Object>> getResource(byte[] bate) throws IOException {
            List<Map<String,Object>> allString = new ArrayList<>();
            // 获取文件内容
            List<String> list = getSource(bate);
            if (list.size() > 1) {			
            	Map<String,Object> callLogInfo = new HashMap<>();
            	
            	// 循环内容
            	for(int i = 1; i<list.size();i++){
            		List<String> content = Arrays.asList(list.get(i).split(","));
            		if(content!=null && content.size()>0){
            			callLogInfo = new HashMap<>();
            			for (int j = 0; j < content.size(); j++) {
            				callLogInfo.put(j+"",content.get(j));
            			}
            			allString.add(callLogInfo);
            		}
            	}
    		}
            return  allString;
        }
        
        /** 读文件数据 **/
        private static List<String> getSource(byte[] bate) throws  IOException {
            BufferedReader br = null;
            ByteArrayInputStream fis=null;
            InputStreamReader isr = null;
            try {
                fis = new ByteArrayInputStream(bate);
                isr = new InputStreamReader(fis,"GB2312");
                br = new BufferedReader(isr);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String line;
            String everyLine ;
            List<String> allString = new ArrayList<>();
            try {
                //读取到的内容给line变量
                while ((line = br.readLine()) != null){
                    everyLine = line;
                    allString.add(everyLine.trim());
                }
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                if(fis != null){
                    fis.close();
                }
                if(isr != null){
                    isr.close();
                }
            }
            return allString;
        }    
        
    }