springboot结合POI导入导出excel文件

女爷i 2022-10-17 11:07 217阅读 0赞

**1.读取excel第一个sheet页的第一列数据并放到集合**

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.14</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.14</version>
            </dependency>

List<String> sugList = new ArrayList<String>();
            if (file == null) { 
                //代表没有文件
                return 0;
            }
            if (file.getOriginalFilename() != null && !"".equals(file.getOriginalFilename())) { 
                String filename = file.getOriginalFilename();
                //excel2003
                if (filename.split("\\.")[1].equals("xls")) { 
                    try { 
                        //POI导入文件,存放到list集合
                        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
                        //意思是有几个文件,一个excel可能有多个sheet,这里只读取第一个
                        int sheets = workbook.getNumberOfSheets();
                        //只读取第一个sheet
                        HSSFSheet sheetAt = workbook.getSheetAt(0);
                        //这个表示当前sheet有多少行数据,一行一行读取就行
                        int rows = sheetAt.getPhysicalNumberOfRows();
                        for (int i = 0; i < rows; i++) { 
                            // Batch batch = new Batch();
                            //某一行的数据,是一行一行的读取
                            HSSFRow row = sheetAt.getRow(i);
                            HSSFCell cell = row.getCell(0);
                            String cellData = cell.getStringCellValue();
                            sugList.add(cellData);
                        }
                    } catch (RuntimeException | IOException e) { 
    
                    }
                    //excel2007
                } else if (filename.split("\\.")[1].equals("xlsx")) { 
                    //POI导入文件,存放到list集合
                    try { 
                        Workbook workbook = WorkbookFactory.create(file.getInputStream());
                        // XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
                        //意思是有几个文件,一个excel可能有多个sheet,这里只读取第一个
                        // int sheets = workbook.getNumberOfSheets();
                        Sheet sheetAt = workbook.getSheetAt(0);  //示意访问sheet
                        //只读取第一个sheet
                        // XSSFSheet sheetAt = (XSSFSheet) workbook.getSheetAt(0);
                        //这个表示当前sheet有多少行数据,一行一行读取就行,但是会把没有数据的行读出来,需要加异常处理
                        int rows = sheetAt.getPhysicalNumberOfRows();
                        for (int i = 0; i < rows; i++) { 
                            Row row1 = sheetAt.getRow(i);
                            String cellData = row1.getCell(0).getStringCellValue();
                            sugList.add(cellData);
                        }
                    } catch (RuntimeException | IOException | InvalidFormatException e) { 
                        return 0;
                    }
                }
            } else { 
                return 0;
            }