我正在使用excel为测试自动化脚本输入数据,当我知道要读取的确切行和列时,便可以从excel读取数据。我面临的挑战是在工作表中合并单元格时。例如。 Test data excel sample

在这里,ScriptName和Iteration是我的主键,用于标识脚本的唯一数据集。
所以我的问题是:

  • 我想获取有关ScriptName和迭代的所有ReferenceSetName,即对于登录脚本,迭代1:我必须获取ABC1引用集,ABC2引用集,ABC3引用集
  • 我想获取与ScriptName,Iteration和ReferenceSet有关的所有PackageName,即,对于登录脚本,Iteration1,ReferenceSet ABC1 Ref set:我必须获取ABC1,ABC2,ABC3

  • 目前,下面是方法-getEntireCellValue()我正在用来从excel提取数据,我需要帮助来解决上述2个问题。真的很感谢任何支持。
    public void getExcelRowNum() {
        boolean found = false;
        String scriptCell = null, iterationCell = null;
        try {
            @SuppressWarnings("rawtypes")
            Iterator iterator = sheet.rowIterator();
            while (iterator.hasNext()) {
                Row row = (Row) iterator.next();
                scriptCell = row.getCell(1).toString().trim();
                iterationCell = row.getCell(2).toString().trim();
                if (row.getCell(2).getCellTypeEnum() == CellType.NUMERIC)
                    iterationCell = iterationCell.substring(0, iterationCell.indexOf(".")).trim();
                if ((scriptCell.equals(scriptName) && iterationCell.equals(String.valueOf(iteration).trim()))
                        || (scriptCell.equals(scriptName) && Integer.parseInt(iterationCell) == iteration)) {
                    rowNum = row.getRowNum();
    
                    found = true;
                    break;
                }
            }
            if (rowNum == -1 || found == false)
                throw new Exception("Please check the test name: " + scriptName + " or the iteration: " + iteration
                        + " in the test data sheet");
    
            row = sheet.getRow(0);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void getExcelColNum(String colName) {
        boolean found = false;
        try {
            for (int i = 0; i < row.getLastCellNum(); i++) {
                if (row.getCell(i).getStringCellValue().trim().equals(colName.trim())) {
                    col_Num = i;
                    found = true;
                    break;
                }
            }
            if (col_Num == -1 || found == false)
                throw new Exception("Please check the column name: " + colName + " in the test data sheet");
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void getCell() {
        try {
            row = sheet.getRow(rowNum);
            cell = row.getCell(col_Num);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
    
        //Prior to calling this method. I am connecting to the excel sheet which
        is in .xlsx or xls format
        public String getEntireCellValue(String sheetName, String colName) {
                try {
                    sheet = workbook.getSheet(sheetName);
                    getExcelRowNum();
                    getExcelColNum(colName);
                    getCell();
                    if (cell.getCellTypeEnum() == CellType.STRING)
                        return cell.getStringCellValue().trim();
                    else if (cell.getCellTypeEnum() == CellType.BLANK)
                        return null;
                }
                catch (Exception e) {
                    e.printStackTrace();
                    return null;
             }
        }
    
        public int getNumOfMergedRows() {
            int rowsMerged = 0;
            try {
                for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
                    CellRangeAddress range = sheet.getMergedRegion(i);
                    if (range.getFirstRow() <= rowNum && range.getLastRow() >=
                    rowNum) {
                        ++rowsMerged;
                    }
                }
                System.out.println("Number of rows merged are: " + rowsMerged);
            }
            catch (Exception e) {
                e.printStackTrace();
            }
            return rowsMerged;
        }
    

    附言我在这里所做的是,我正在尝试获取脚本的合并行数,例如将6行合并为Login脚本,然后在这6行中查找单元格数以获得参考集名称(3个单元格)。
    注意:当我调用上述方法-getNumOfMergedRows()来确定用于Login脚本的合并行数时,我将输出4而不是6。

    最佳答案

    下面的代码将确定一列中的合并单元格数量-colName,起始行为startingRow

    public int getNumOfMergedRows(String colName, int startingRow) {
        int rowsMerged = 0, col = 0;
        XSSFRow mergedRow = null;
        XSSFCell mergedCell = null;
        try {
            col = getExcelColNum(colName);
            for (int i = startingRow + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                mergedRow = sheet.getRow(i);
                mergedCell = mergedRow.getCell(col);
                if (mergedCell.getCellTypeEnum() == null || mergedCell.getCellTypeEnum() == CellType.BLANK)
                    rowsMerged++;
                else
                    break;
            }
            rowsMerged++;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        logger.info(rowsMerged + " rows are merged in columne" + colName + " for " + scriptName + " script");
        return rowsMerged;
    }
    

    关于java - 如何使用apache poi确定合并单元格中的行数?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51667393/

    10-11 20:34
    查看更多