Excel 解析，通过Excel的地址和MultipartFile进行解析

目录

两种方法都用到了read()和getValue()方法对数据进行解析，只是二者传入的Excel数据格式不一样。

第一种方法：通过Excel地址进行解析Excel的数据

第二种方法：解析Excel的MultipartFile数据流获取数据。 

HSSFWorkbook

操作Excel2003以前(包括2033)的版本，扩展名是 .xls    行数限制65535行，超出会报错；

XSSWorkbook

操作Excel2007以后的版本，扩展名是 .xlsx；  最多104万行，  回出现OOM的内存溢出问题；

1.通过Excel的地址来进行解析Excel的数据 

导入pom文件

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15</version>
    </dependency>

第一种方法：通过Excel地址进行解析Excel的数据

//使用Excel的path地址进行解析
public static void main(String[] args){
    String excelPath="c:\\*\\*.xlsx";
    File excel = new File(excelPath);
    if(excel.isFile() && excel.exists()){
    List<Map> array = null;
    try {
                String[] split = excel.getName().split("\\.");
                Workbook work =null;
                if ( "xlsx".equals(split[1])){
                    work = new XSSFWorkbook(new FileInputStream(excel));
                }else if ("xls".equals(split[1])){
                    POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(excel));
                    work =new HSSFWorkbook(poifsFileSystem);
                }
                array = read(work);
                List<Map> sheet1 =  array;

                //System.out.println(sheet1);
            } catch (IOException e) {
                e.printStackTrace();
            }
}
}
 public static List<Map> pathExcel(String path){
        File excel= new File(path);
        if(excel.isFile() &&excel.exists()){
            List<Map> array = null;
            String[] split= excel.getName().split("\\.");
            try {
            Workbook workbook =null;
            if("xlsx".equals(split[1])){
                workbook = new XSSFWorkbook(new FileInputStream(excel));
            }else if("xls".equals(split[1])){
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(excel));
                workbook =new HSSFWorkbook(poifsFileSystem);
            }
            array =read(workbook);
            return array;
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

第二种方法：解析Excel的MultipartFile数据流获取数据。 

//使用MultipartFile 流进行的Excel的解析
public static List<Map> IOexcel(MultipartFile file){
        //流要转换整文件
        File file1= mutipartFile(file);
        List<Map> array =null;
        try {
        String fileName =file.getName().toLowerCase();
            Workbook work =null;
        if (fileName.endsWith(XLSX)){
            work = new XSSFWorkbook(new FileInputStream(file1));
        }else if (fileName.endsWith(XLS)){
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file1));
            work =new HSSFWorkbook(poifsFileSystem);
        }else{
            return array;
        }
            array = read(work);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return array;
    }
//MultipartFile转换成为File
 public static File mutipartFile(MultipartFile file){
        File toFile = null;
        if(file.equals("")||file.getSize()<=0){
            file =null;
        }else {
            InputStream InputStream = null;
            try {
                InputStream = file.getInputStream();
                toFile =new File(file.getOriginalFilename());
                inputStreamToFile(InputStream,toFile);
                InputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return toFile;
    }

 //获取流文件
    private static void inputStreamToFile(InputStream inputStream, File file) {
        OutputStream os =null;
        try {
            os = new FileOutputStream(file);
            int bytesRead =0;
            byte[] butter =new byte[1024];
            while((bytesRead = ins.read(butter, 0,1024))!=-1){
                os.write(butter,0,bytesRead);
            }
                os.close();
                inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

public static void deleteTempFile(File file){
    if(file != null){
        File del = new File(file.toURI());
        del.delete
    }
}

 解析Excel中的数据

private static List<Map>  read(Worknook book) throws IOException{
    List<Map> list= new ArrayList<>();
    for(int i=0;i<book.geNumberOfSheets();i++){
        Sheet sheet = book.getSheetAt(i);
        int rowStart =sheet.getFirstRowNum(); //首行下标
        int rowEnd =sheet.getLastRowNum(); //尾行下标
        
        Row firstRow =sheet.getRow(rowStart);
        int cellStart = firstRow.getFirstCellNum();  //获取一行中的开始索引
        int cellEnd = firstRow.getLastCellNum();// 获取一行中的结束索引
        Map<Integer,String> keyMap =new HashMap<>();
        for(int j= cellStart;j<cellEnd;j++){
            // 对表头数据进行解析 获取当前单元格中的数据
            String val = getValue(firstRow.getCell(j));
            if(val == null || val.trim().length == 0){    //val.trim() 去除空格
            cellEnd = j;
            break;
            }
            keyMap.put(j,val);
    }    
    if(keyMap.isEmpty()){
        return (JSONArray) Collections.emptyList();
    }
    //获取每行JSON对象的值
    List<Map> array = new ArrayList<>();
    //如果首行与尾行相同，表明只有一行，返回表头数据
    if(rowStart == rowEnd){
        Map<Object,String> object = new HashMap<>();
        for(int i: keyMap.keySet()){
        object.put(keyMap.get(i),"");
        }
        array.add(object);
    }
    //解析表中全部数据
    for(int i= rowStart+1;i<=rowEnd;i++){
        Row eachRow = Sheet.getRow(i);
        Map<Object,String> obj =new HashMap<>();
        StringBuffer sb =new StringBUffer();
        for(int k = cellStart;k<cellEnd;k++){
            if(eachRow != null){
            String val =getValue(eachRow.getCell(k));
            sb.append(val);//所有数据添加到里面，用于判断该行是否为空
            obj.put(keyMap.get(k),val);
        }
        }
     if(sb.toString().length()>0){
        array.add(obj);
    }
    }
    Map map = new HashMap();
    map.put("sheet",array);
    list.add(map);
    }
    return list;
}

获取单元格的数据 

//获取单元格数据
 private static String getValue(Cell cell){
        // 空白或空
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
            return "";
        }
        // 布尔值 CELL_TYPE_BOOLEAN
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue()+"";
        }
        //  数字 类型
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return df.format(date);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String val = cell.getStringCellValue()+"";
            val = val.toUpperCase();
            if (val.contains("E")) {
                val = val.split("E")[0].replace(".", "");
            }
            return val;
        }
        //  公式 CELL_TYPE_FORMULA
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        }
        // String类型
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        
        //  错误 CELL_TYPE_ERROR
        if(cell.getCellType() == CELL_TYPE_ERROE){
            return "错误";
        }
            
        return "";
    }