常规POI上传和超大POI上传

项目中遇到一个问题，正常运行的项目中，一个excel数据上传，解析数据后生成json文件保存在服务器上，某天突然出现故障，排查后发现是用户数据了超多导致，记录此次解决方案。

当exel的数据量为几万或者十几万时，普通的poi读取即可，但是如果excel数据量为几十万或者几百万时，就会导致读取速度慢，出现读取超时，就需要使用流处理方式解决。

对于excel表格的处理，根据后缀不同处理方式不同，且两者存在区别需要注意：

• HSSFworkbook对象可读取/导出的xls格式的excel
• XSSFworkbook对象可读取/导出的xlsx格式的excel
• xls导出最大行数为65535，xlsx最大行数为104万
• xlsx和xls在数据量相同的情况下xlsx文件更小

1.添加依赖

<properties>
    <poi.version>4.0.1</poi.version>
</properties>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

2.常规POI上传方式

@PostMapping(value = "/uploadJsonData") 
public ResultBody uploadJsonData(@RequestParam("file") MultipartFile file, String name, String city) {
        FileOutputStream fileOutputStream=null;
        OutputStreamWriter outputStreamWriter=null;
        BufferedWriter writer=null;
        try {
            if(!file.isEmpty()) {
                InputStream inputStream = file.getInputStream();
                Workbook book=null;
                //2003
                if(fileName.matches("^.+\\.(?i)(xls)$")) {
                    book=new HSSFWorkbook(inputStream);
                }
                //2007
                if(fileName.matches("^.+\\.(?i)(xlsx)$")) {
                    book = new XSSFWorkbook(inputStream);
                }
                Sheet sheet = book.getSheetAt(0);
                int allRowNum = sheet.getLastRowNum();
                if(allRowNum==0) {
                    return ResultBody.error("导入文件数据为空");
                }
                SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                List<FlowChart> flowChartList=new ArrayList<>();
                for(int i=1;i<=allRowNum;i++) {
                    Row row = sheet.getRow(i);
                    if(row!=null) {
                        //读取数据
                        Cell cell1 = row.getCell(1);
                        Cell cell2 = row.getCell(2);
                        if(cell1==null){
                            //判断数据是否为空，为空停止本次循环，开始下次循环
                            break;
                        }
                        //定义对象，保存数据
                        FlowChart flowChart=new FlowChart();
                        flowChart.setAcquisitionDate(dformat.parse(cell1.toString()));
                        flowChart.setBrodband(new BigDecimal(cell2.toString()));
                        flowChartList.add(flowChart);
                    }
                }

                //数据保存到新文件
                File jsonFile = new File("D:/1.json");
                if(!jsonFile.exists()){
                    jsonFile.createNewFile();
                }
                fileOutputStream=new FileOutputStream(jsonFile);
                outputStreamWriter=new OutputStreamWriter(fileOutputStream,"utf-8");
                writer = new BufferedWriter(outputStreamWriter);
                writer.write(JSONArray.toJSONString(flowChartList));
                writer.flush();
                writer.close();
                outputStreamWriter.close();
                fileOutputStream.close();
                return ResultBody.success("导入成功");
            }else{
                return ResultBody.error("导入失败,文件不存在");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return   ResultBody.error("导入失败");
        }finally {
            try{
                writer.flush();
                writer.close();
                outputStreamWriter.close();
                fileOutputStream.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

3.超大POI上传方式

 @PostMapping(value = "/uploadJsonData")
 public ResultBody uploadJsonData(@RequestParam("file") MultipartFile file) {
        FileOutputStream fileOutputStream=null;
        OutputStreamWriter outputStreamWriter=null;
        BufferedWriter writer=null;
        try {
            if(!file.isEmpty()) {
                InputStream inputStream = file.getInputStream();
                //1.根据excel报表获取OPCpackage
                OPCPackage opcPackage = OPCPackage.open(inputStream);
                // 2.创建XSSFReader
                XSSFReader xssfReader = new XSSFReader(opcPackage);
                // 3.获取SharedStringTable对象
                SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
                // 4.获取styleTable对象
                StylesTable stylesTable = xssfReader.getStylesTable();
                // 5.创建sax xmlReader对象
                XMLReader xmlReader = XMLReaderFactory.createXMLReader();
                // 6.注册事件驱动处理器
                SheetHandler sheetHandler = new SheetHandler();
                XSSFSheetXMLHandler xssfSheetXMLHandler = new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, sheetHandler, false);
                xmlReader.setContentHandler(xssfSheetXMLHandler);
                // 7.逐行读取
                XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
                while (sheetIterator.hasNext()) {
                    InputStream in = sheetIterator.next();
                    InputSource is = new InputSource(in);
                    xmlReader.parse(is);
                }
                //8.读取集合
                List<Map<String, String>> employeeList = sheetHandler.getList();
                if(employeeList.size()==0) {
                    return ResultBody.error("导入文件数据为空");
                }
                //9.循环解析数据
                List<FlowChart> flowChartList=new ArrayList<>();
                SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                for(int i=0;i<employeeList.size();i++) {
                    int rownum=i+2;
                    String dayTime=employeeList.get(i).get("F"+rownum);
                    String bandwidth=employeeList.get(i).get("I"+rownum);
                    if(dayTime==null||bandwidth==null){
                        continue;
                    }
                    //保存数据
                    FlowChart flowChart=new FlowChart();
                    flowChart.setAcquisitionDate(dformat.parse(dayTime));
                    flowChart.setBrodband(new BigDecimal(bandwidth));
                    flowChartList.add(flowChart);
                }
                //保存新文件
                File jsonFile = new File("D:/1.json");
                if(!jsonFile.exists()){
                    jsonFile.createNewFile();
                }
                fileOutputStream=new FileOutputStream(jsonFile);
                outputStreamWriter=new OutputStreamWriter(fileOutputStream,"utf-8");
                writer = new BufferedWriter(outputStreamWriter);
                writer.write(JSONArray.toJSONString(flowChartList));
                writer.flush();
                writer.close();
                outputStreamWriter.close();
                fileOutputStream.close();
                return ResultBody.success("导入成功");
            }else{
                return ResultBody.error("导入失败,文件不存在");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return   ResultBody.error("导入失败");
        }finally {
            try{
                writer.flush();
                writer.close();
                outputStreamWriter.close();
                fileOutputStream.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }