记录POI方式如何读取excel文档单元格数据

飞熊 • 2023年8月9日下午4:12 • 技术随笔 • 阅读 281

1. 先上一段代码

public String importExcelFile(MultipartFile file) throws IOException 
{
        int startRow = 0;
        int count = 0;
        if (file.isEmpty()) {
            throw new MyException(MyCodeEnum.PARAMETER_ERROR,"上传的文件不能为空");
        }
        InputStream inputStream = file.getInputStream();
        //文件的原名称
        long size = file.getSize();
        String originalFilename = file.getOriginalFilename();
        String fileExtName = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        logger.info("upload file path: " + originalFilename);
        Date date = new Date();
        // 获取当前操作用户名, 可忽略这段代码
        ActiveUser activeUser = (ActiveUser) SecurityUtils.getSubject().getPrincipal();
        String operator = activeUser.getUser().getUsername();
        
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);//new FileInputStream(inputStream)
            Sheet sheet = wb.getSheetAt(0);
            startRow = 9;    // 从excel第9行开始遍历

            int endRow = sheet.getLastRowNum();  // 获取该excel最后一行长度
            while (startRow <= endRow) {
                Row row = sheet.getRow(startRow);  // 获取每一行数据

                String info1 = getCellValue(row, 0);   // 获取该行第0列数据xxx
                ...
                String infon = getCellValue(row, n);   // 获取该行第n列数据xxx
                System.out.println(info1);
				startRow++;
                count++;
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                wb.close();
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        //TODO
        return  "共导入" + count + "行数据";
}

private String getCellValue(Row row, int cellIndex) {
        logger.info("row: " + row.getRowNum() + " cellIndex: " + cellIndex);
        if (row.getCell(cellIndex) == null || row.getCell(cellIndex).getCellTypeEnum() == CellType.BLANK)
            return "";
        // formatter 格式化， 可以读取任何类型的cell数据，避免了去判断cell类型是String还是Numberic等等
        DataFormatter formatter = new DataFormatter();
        logger.info(formatter.formatCellValue(row.getCell(cellIndex)));
        return formatter.formatCellValue(row.getCell(cellIndex));
}