Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getCell.

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper {
    try {// ww  w. j  ava2s  .  c o  m
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo();
        //Create The Summary Sheet
        Sheet sheetSummary = wb.getSheetAt(1);
        int iSummaryRowCounter = 1;
        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {

            Row row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(licenseType);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            row = sheetSummary.getRow(iSummaryRowCounter);
            if (row == null)
                row = sheetSummary.createRow(iSummaryRowCounter);
            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            iSummaryRowCounter = iSummaryRowCounter + 1;
        }
        int iSheetCounter = 1;

        for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) {
            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), licenseType);
            CellReference cellReference = new CellReference("B1");
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B2");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            cellReference = new CellReference("B3");
            row = sheet.getRow(cellReference.getRow());
            cell = row.getCell(cellReference.getCol());
            if (cell == null)
                cell = row.createCell(cellReference.getCol());
            setCellBorder(wb, cell);
            cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))
                    - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1)));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);

            ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>(
                    employeeBL.getAllEmployees().values());
            final String selectedLicenseTypeName = licenseType;
            CollectionUtils.filter(allEmployees, new Predicate() {
                @Override
                public boolean evaluate(Object o) {
                    EmployeeBO emp = (EmployeeBO) o;
                    if (CollectionUtils.exists(emp.getLicenses(), new Predicate() {
                        @Override
                        public boolean evaluate(Object o) {
                            return ((LicenseBO) o).getLicenseTypeName()
                                    .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates.
                        }
                    }))
                        return true;
                    else
                        return false;
                }
            });

            int iRowCounter = 5;
            for (EmployeeBO employeeBO : allEmployees) {
                row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.photon.phresco.eshop.service.EShopPublishService.java

License:Apache License

public List<CategoryHBM> getCategoriesFromExcel() throws Exception {
    System.out.println("Inside getCategoriesFromExcel");
    XSSFWorkbook workbook = new XSSFWorkbook(is);
    System.out.println("workbook = " + workbook);
    XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_CATEGORIES);
    System.out.println("Sheet = " + sheet);
    List<CategoryHBM> categories = new ArrayList<CategoryHBM>(10);
    Iterator<Row> itr = sheet.rowIterator();
    System.out.println("itr " + itr);
    for (int i = 0; i < 1; i++) {
        itr.next();//  www  .  j a  v a 2 s.  c  om
    }
    while (itr.hasNext()) {
        try {
            Row row = itr.next();
            System.out.println("Row 0 value" + row.getCell(0));
            int categoryId = (int) row.getCell(0).getNumericCellValue();
            String categoryName = ServiceUtil.getValue(row.getCell(1));
            String categoryImage = ServiceUtil.getValue(row.getCell(2));
            String categoryDetailsImage = ServiceUtil.getValue(row.getCell(3));
            int categoryParentId = 0;
            String categoryDescription = ServiceUtil.getValue(row.getCell(5));

            CategoryHBM category = new CategoryHBM(categoryId, categoryName, categoryImage,
                    categoryDetailsImage, categoryParentId, categoryDescription);
            categories.add(category);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    return categories;
}

From source file:com.photon.phresco.eshop.service.EShopPublishService.java

License:Apache License

public List<ProductHBM> getProductsFromExcel() throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook(is);
    XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_PRODUCTS);
    List<ProductHBM> products = new ArrayList<ProductHBM>(10);
    Iterator<Row> itr = sheet.rowIterator();

    for (int i = 0; i < 1; i++) {
        itr.next();//from   www  .  ja  va  2s  .c  om
    }

    while (itr.hasNext()) {
        try {
            Row row = itr.next();
            int productId = (int) row.getCell(0).getNumericCellValue();
            String productName = ServiceUtil.getValue(row.getCell(1));
            System.out.println("productId = " + productId);
            String manufacturer = ServiceUtil.getValue(row.getCell(2));
            int categoryId = (int) row.getCell(3).getNumericCellValue();
            System.out.println("categoryId = " + categoryId);
            String model = ServiceUtil.getValue(row.getCell(4));
            String specialProductStr = ServiceUtil.getValue(row.getCell(5));
            String newProductStr = ServiceUtil.getValue(row.getCell(6));
            Integer specialProduct = (YES.equals(specialProductStr) ? 1 : 0);
            Integer newProduct = (YES.equals(newProductStr) ? 1 : 0);
            Double listPrice = row.getCell(7).getNumericCellValue();
            System.out.println("listPrice = " + listPrice);
            Double sellPrice = row.getCell(8).getNumericCellValue();
            System.out.println("sellPrice = " + sellPrice);
            String productDescription = ServiceUtil.getValue(row.getCell(9));
            String productImage = ServiceUtil.getValue(row.getCell(10));
            String productDetailImage = ServiceUtil.getValue(row.getCell(11));
            Date createdDate = new Date();
            Date lastUpdatedDate = new Date();

            ProductHBM product = new ProductHBM(productId, productName, manufacturer, categoryId, model,
                    specialProduct, newProduct, listPrice, sellPrice, productDescription, productImage,
                    productDetailImage, createdDate, lastUpdatedDate);
            products.add(product);
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }

    return products;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) {
    boolean hasTrue = false;
    Iterator<Row> rowIterator;
    try {// w  w w  .  ja v a2 s .co m
        FileInputStream myInput = new FileInputStream(filePath);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        rowIterator = mySheet.rowIterator();
        for (int i = 0; i <= 2; i++) {
            rowIterator.next();
        }
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) {
                mySheet.removeRow(next);
                int rowNum = next.getRowNum();
                int newNum = rowNum + 1;
                mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                int numberOfSheets = myWorkBook.getNumberOfSheets();
                for (int j = 0; j < numberOfSheets; j++) {
                    HSSFSheet mySheet1 = myWorkBook.getSheetAt(j);
                    if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) {
                        myWorkBook.removeSheetAt(j);
                        hasTrue = true;
                        break;
                    }
                }
                myInput.close();
                FileOutputStream outFile = new FileOutputStream(filePath);
                myWorkBook.write(outFile);
                outFile.close();
                break;
            }
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) {
    boolean hasTrue = false;
    Iterator<Row> rowIterator;
    try {//  w w w .  ja va 2s. c o  m
        FileInputStream myInput = new FileInputStream(filePath);

        OPCPackage opc = OPCPackage.open(myInput);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        rowIterator = mySheet.rowIterator();
        for (int i = 0; i <= 2; i++) {
            rowIterator.next();
        }

        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) {
                mySheet.removeRow(next);
                int rowNum = next.getRowNum();
                int newNum = rowNum + 1;
                mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                int numberOfSheets = myWorkBook.getNumberOfSheets();
                for (int j = 0; j < numberOfSheets; j++) {
                    XSSFSheet mySheet1 = myWorkBook.getSheetAt(j);
                    if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) {
                        myWorkBook.removeSheetAt(j);
                        hasTrue = true;
                        break;
                    }
                }
                myInput.close();
                FileOutputStream outFile = new FileOutputStream(filePath);
                myWorkBook.write(outFile);
                outFile.close();
                break;
            }
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void readTestSuiteFromXLSX(List<TestSuite> excels, StringBuilder sb) throws FileNotFoundException,
        InvalidFormatException, IOException, UnknownHostException, PhrescoException {
    Iterator<Row> rowIterator;
    FileInputStream myInput = new FileInputStream(sb.toString());

    OPCPackage opc = OPCPackage.open(myInput);

    XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    rowIterator = mySheet.rowIterator();
    for (int i = 0; i <= 2; i++) {
        rowIterator.next();//from w  ww . j av  a  2  s.c  o m
    }

    while (rowIterator.hasNext()) {
        Row next = rowIterator.next();
        if (StringUtils.isNotEmpty(getValue(next.getCell(2)))
                && !getValue(next.getCell(2)).equalsIgnoreCase("Total")) {
            TestSuite createObject = createObject(next);
            excels.add(createObject);
        }
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void readTestSuitesFromXLS(List<TestSuite> excels, StringBuilder sb)
        throws FileNotFoundException, IOException, UnknownHostException, PhrescoException {
    Iterator<Row> rowIterator;
    FileInputStream myInput = new FileInputStream(sb.toString());
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    rowIterator = mySheet.rowIterator();
    for (int i = 0; i <= 2; i++) {
        rowIterator.next();/*from  w  ww . j a  va2s  .c  om*/
    }
    while (rowIterator.hasNext()) {
        Row next = rowIterator.next();
        if (StringUtils.isNotEmpty(getValue(next.getCell(2)))
                && !getValue(next.getCell(2)).equalsIgnoreCase("Total")) {
            TestSuite createObject = createObject(next);
            excels.add(createObject);
        }
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private static TestSuite createObject(Row next) throws UnknownHostException, PhrescoException {
    TestSuite testSuite = new TestSuite();
    if (next.getCell(2) != null) {
        Cell cell = next.getCell(2);/* w ww  .j av a  2  s  .c o m*/
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            testSuite.setName(value);
        }
    }
    float success = 0;
    if (next.getCell(3) != null) {
        Cell cell = next.getCell(3);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            success = Float.parseFloat(value);
            testSuite.setSuccess(success);
        }
    }
    float failure = 0;
    if (next.getCell(4) != null) {
        Cell cell = next.getCell(4);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            failure = Float.parseFloat(value);
            testSuite.setFailures(failure);
        }
    }
    float notApplicable = 0;
    if (next.getCell(5) != null) {
        Cell cell = next.getCell(5);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            notApplicable = Float.parseFloat(value);
            testSuite.setNotApplicable(notApplicable);
        }
    }
    if (next.getCell(6) != null) {
        Cell cell = next.getCell(6);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            float notExecuted = Float.parseFloat(value);
            testSuite.setErrors(notExecuted);
        }
    }
    float blocked = 0;
    if (next.getCell(7) != null) {
        Cell cell = next.getCell(7);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            blocked = Float.parseFloat(value);
            testSuite.setBlocked(blocked);
        }
    }
    float total = 0;
    if (next.getCell(8) != null) {
        Cell cell = next.getCell(8);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            total = Float.parseFloat(value);
            testSuite.setTotal(total);
        }
    }
    if (next.getCell(9) != null) {
        Cell cell = next.getCell(9);
        String value = getValue(cell);
        if (StringUtils.isNotEmpty(value)) {
            float testCoverage = Float.parseFloat(value);
            testSuite.setTestCoverage(testCoverage);
        }
    }
    float notExecuted = total - (success + failure + notApplicable + blocked);
    testSuite.setNotExecuted(notExecuted);
    return testSuite;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestCasesFromXls(String filePath, String testSuiteName, String testCaseId) {
    boolean hasTrue = false;
    List<TestCase> testCases = new ArrayList<TestCase>();
    TestCase tstCase = new TestCase();
    Iterator<Row> rowIterator;
    try {//from   ww  w .  j  av  a 2  s.  c o m
        FileInputStream myInput = new FileInputStream(filePath);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            HSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                for (int i = 0; i <= 23; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next = rowIterator.next();
                    if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) {
                        tstCase = readTest(next);
                        mySheet.removeRow(next);
                        int rowNum = next.getRowNum();
                        int newNum = rowNum + 1;
                        HSSFRow row = mySheet.getRow(newNum);
                        if (row != null) {
                            mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                        }
                        hasTrue = true;
                        break;
                    }
                }
            }
        }

        if (hasTrue) {
            for (int j = 0; j < numberOfSheets; j++) {
                HSSFSheet myHSSFSheet = myWorkBook.getSheetAt(j);
                if (myHSSFSheet.getSheetName().equals(testSuiteName)) {
                    rowIterator = myHSSFSheet.rowIterator();
                    for (int i = 0; i <= 23; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next = rowIterator.next();
                        TestCase createObject = readTest(next);
                        if (StringUtils.isNotEmpty(createObject.getTestCaseId())) {
                            testCases.add(createObject);
                        }
                    }
                    float totalPass = 0;
                    float totalFail = 0;
                    float totalNotApplicable = 0;
                    float totalBlocked = 0;
                    int totalTestCases = testCases.size();
                    for (TestCase testCase : testCases) {
                        String testCaseStatus = testCase.getStatus();
                        if (testCaseStatus.equalsIgnoreCase("success")) {
                            totalPass = totalPass + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("failure")) {
                            totalFail = totalFail + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) {
                            totalNotApplicable = totalNotApplicable + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("blocked")) {
                            totalBlocked = totalBlocked + 1;
                        }
                    }

                    //                      if(tstCase.getStatus().equalsIgnoreCase("success")) {
                    //                         totalPass = totalPass - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("failure")) {
                    //                         totalFail = totalFail - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) {
                    //                         totalNotApplicable = totalNotApplicable - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) {
                    //                         totalBlocked = totalBlocked - 1;
                    //                      } 

                    HSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet1.rowIterator();
                    for (int i = 0; i <= 2; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next1 = rowIterator.next();
                        if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                                && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                            TestSuite createObject = createObject(next1);
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())
                                    && createObject.getName().equals(testSuiteName)) {
                                updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1,
                                        totalTestCases, "delete");
                            }
                        }
                    }
                }
            }

            myInput.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            myWorkBook.write(outFile);
            outFile.close();
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestCasesFromXLSX(String filePath, String testSuiteName, String testCaseId) {
    boolean hasTrue = false;
    List<TestCase> testCases = new ArrayList<TestCase>();
    TestCase tstCase = new TestCase();
    Iterator<Row> rowIterator;
    try {/*  w  w w .j a  v  a2  s.c  o m*/
        FileInputStream myInput = new FileInputStream(filePath);

        OPCPackage opc = OPCPackage.open(myInput);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            XSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                for (int i = 0; i <= 23; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next = rowIterator.next();
                    if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) {
                        mySheet.removeRow(next);
                        int rowNum = next.getRowNum();
                        int newNum = rowNum + 1;
                        XSSFRow row = mySheet.getRow(newNum);
                        if (row != null) {
                            mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                        }
                        hasTrue = true;
                        break;
                    }
                }
            }
        }
        if (hasTrue) {
            for (int j = 0; j < numberOfSheets; j++) {
                XSSFSheet myXSSFSheet = myWorkBook.getSheetAt(j);
                if (myXSSFSheet.getSheetName().equals(testSuiteName)) {
                    rowIterator = myXSSFSheet.rowIterator();
                    for (int i = 0; i <= 23; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next = rowIterator.next();
                        TestCase createObject = readTest(next);
                        testCases.add(createObject);
                    }
                    float totalPass = 0;
                    float totalFail = 0;
                    float totalNotApplicable = 0;
                    float totalBlocked = 0;
                    int totalTestCases = testCases.size();
                    for (TestCase testCase : testCases) {
                        String testCaseStatus = testCase.getStatus();
                        if (testCaseStatus.equalsIgnoreCase("success")) {
                            totalPass = totalPass + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("failure")) {
                            totalFail = totalFail + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) {
                            totalNotApplicable = totalNotApplicable + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("blocked")) {
                            totalBlocked = totalBlocked + 1;
                        }
                    }

                    if (tstCase.getStatus().equalsIgnoreCase("success")) {
                        totalPass = totalPass - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("failure")) {
                        totalFail = totalFail - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) {
                        totalNotApplicable = totalNotApplicable - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) {
                        totalBlocked = totalBlocked - 1;
                    }

                    XSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet1.rowIterator();
                    for (int i = 0; i <= 2; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next1 = rowIterator.next();
                        if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                                && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                            TestSuite createObject = createObject(next1);
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())
                                    && createObject.getName().equals(testSuiteName)) {
                                updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1,
                                        totalTestCases, "delete");
                            }
                        }
                    }
                }
            }

            myInput.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            myWorkBook.write(outFile);
            outFile.close();
        }
    } catch (Exception e) {

    }

    return hasTrue;
}