Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook(PackagePart part) throws IOException

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java

License:Apache License

@SuppressWarnings("rawtypes")
private void readXmlBasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int index = 0; index < sheetCount; index++) {
        XSSFSheet sheet = workbook.getSheetAt(index);

        Sheet s = new Sheet();

        sheets.add(s);/*from w w w  . jav a2 s  . c om*/

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

            while (it.hasNext()) {
                record.addCellValue(it.next());
            }
        }
    }

}

From source file:com.compomics.cell_coord.parser.impl.XLSFileParser.java

@Override
public Sample parseTrackFile(File trackFile) throws FileParserException {
    List<Track> list = new ArrayList<>();
    // create a new sample object -- watch out to set the relationships!
    Sample sample = new Sample(trackFile.getName());
    try {//  w w  w .j  a  v a  2 s. c om
        FileInputStream fileInputStream = new FileInputStream(trackFile);
        Workbook workbook = null;
        // xls extension
        if (trackFile.getName().endsWith("xls")) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else if (trackFile.getName().endsWith("xlsx")) { // xlsx extension
            workbook = new XSSFWorkbook(fileInputStream);
        }
        if (workbook != null) {
            // check that at least one sheet is present
            if (workbook.getNumberOfSheets() > 0) {
                Track currentTrack = null;
                List<TrackSpot> currentTrackPointList = new ArrayList<>();
                Long currentId = 0L;
                Sheet sheet = workbook.getSheetAt(0);
                // iterate through all the rows, starting from the second one to skip the header
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    // get the row
                    Row row = sheet.getRow(i);
                    // check the track id
                    Long trackid = (long) row.getCell(0).getNumericCellValue();
                    if (!Objects.equals(currentId, trackid)) {
                        currentTrack = new Track();
                        currentTrack.setTrackid(trackid);
                        list.add(currentTrack);
                        currentId = trackid;
                        currentTrackPointList = new ArrayList<>();
                    }
                    // create new Track Spot object
                    Long spotid = (long) row.getCell(1).getNumericCellValue();
                    double x = row.getCell(2).getNumericCellValue();
                    double y = row.getCell(3).getNumericCellValue();
                    double time = row.getCell(4).getNumericCellValue();
                    TrackSpot trackSpot = new TrackSpot(spotid, x, y, time, currentTrack);
                    currentTrackPointList.add(trackSpot);
                    currentTrack.setTrackSpots(currentTrackPointList);
                    currentTrack.setSample(sample);
                }
            } else {
                throw new FileParserException(
                        "It seems an Excel file does not have any sheets!\nPlease check your files!");
            }
        } else {
            throw new FileParserException("The parser did not find a single workbook!\nCheck your files!!");
        }
    } catch (IOException ex) {
        LOG.error(ex.getMessage(), ex);
    } catch (NumberFormatException ex) {
        LOG.error(ex.getMessage(), ex);
        throw new FileParserException(
                "It seems like a line does not contain a number!\nPlease check your files!");
    }
    sample.setTracks(list);
    return sample;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetSimType(FileNames fileName, SheetName sheetName) throws IOException {
    String filePath = FilePath(fileName);
    String simType = null;//  ww w  . ja  v a2s  .c o  m
    try {
        String sheet = sheetName.toString();
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            sheet = Name[1];
        }
        SetStatusForSimTypSheets(sheet, fileName);
        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        XSSFSheet workSheet = workBook.getSheet(sheet);
        for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Row currentRow = workSheet.getRow(i);
            int type = 1;
            Cell cell = currentRow.getCell(1);
            try {
                type = cell.getCellType();
                System.out.println(type);
                System.out.println(workSheet.getRow(i).getCell(1).toString());
                System.out.println(cell.getStringCellValue().toLowerCase());
                if (type == 1 && cell.getStringCellValue().toLowerCase()
                        .equals(Status.UNUSED.toString().toLowerCase())) {
                    cell.setCellValue(Status.INUSE.toString());
                    simType = currentRow.getCell(0).getStringCellValue();
                    break;
                }
            }

            catch (Exception e) {
                if (type != 1) {
                    cell = currentRow.createCell(1);
                    cell.setCellValue(Status.UNUSED.toString());
                }
            }
        }
    } catch (Exception ex) {
        Log.error(ex.toString());
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
    return simType;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString)
        throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    String sheet = sheetName.toString();
    int rowIndex = -1;

    try {/*w w w  .  j a v  a 2 s  .c  o m*/
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            if (Name[0].contains("IMEI")) {
                sheet = Name[1] + " " + Name[0];
            } else
                sheet = Name[1];
        }
        worksheet = workBook.getSheet(sheet);
        System.out.println(worksheet.getSheetName());
        Iterator<Row> rowIterator = worksheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String
                        .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) {
                    rowIndex = row.getRowNum();
                    break;
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equals(searchString))
                        rowIndex = row.getRowNum();
                    break;
                }
            }
        }
        if (rowIndex != -1) {
            boolean flag = false;
            Row searchStringRow = worksheet.getRow(rowIndex);
            System.out.println(searchStringRow.getPhysicalNumberOfCells());
            Iterator<Cell> statusChangeCell = searchStringRow.cellIterator();
            while (statusChangeCell.hasNext()) {
                Cell statusCell = statusChangeCell.next();
                if (statusCell.getStringCellValue().toLowerCase()
                        .equals(Status.INUSE.toString().toLowerCase())) {
                    statusCell.setCellValue(Status.USED.toString());
                    flag = true;
                }
            }
            if (flag == false)
                Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath
                        + ", Sheet: " + sheet);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetIMEINumber(FileNames fileName, SheetName workSheet) throws IOException {
    String filePath = FilePath(fileName);
    String sheet = null;/*w ww. j av  a 2s . c  om*/
    String tempSheet = null;
    String imei = "";
    int statusColumn = -1;
    try {
        int numOfWorkSheets = 0;

        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        numOfWorkSheets = workBook.getNumberOfSheets();
        for (int count = 0; count < numOfWorkSheets; count++) {
            if (workSheet.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
                sheet = workBook.getSheetName(count);
                break;
            }
        }
        worksheet = workBook.getSheet(sheet);
        if (sheet.equals("iPhone 5C")) {
            tempSheet = sheet;
            sheet = "iPhone 4S";
        }

        switch (sheet) {
        case "4G IMEI":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(2);
                if (row.getCell(5).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf(cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(5).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
            return imei;

        case "3G":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                if (row.getCell(4).toString().equals(Status.UNUSED.toString())) {
                    imei = String.valueOf(row.getCell(2).getNumericCellValue());
                    worksheet.getRow(i).getCell(4).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "Jetpack":
            statusColumn = CreateStatusColumn(filePath, sheet);
            if (statusColumn == -1) {
                statusColumn = 3;
            }

            for (int i = 2; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(1);
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "iPhone 4S":
            if (tempSheet.contains("5C"))
                statusColumn = CreateStatusColumn(filePath, tempSheet);
            if (statusColumn == -1) {
                Cell cell = worksheet.getRow(0).createCell(4);
                cell.setCellValue(Status.STATUS.toString());
                statusColumn = 4;
            }
            for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(0);
                if (row.getCell(statusColumn).getCellType() == Cell.CELL_TYPE_BLANK) {
                    row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
                }
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    if (imei != "" || imei != null)
                        row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    return imei;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public AccountDetails GetAccountDetails(FileNames fileName, SheetName worksheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, worksheetName.toString());
    AccountDetails accountDetails = new AccountDetails();

    try {//from ww  w .j ava  2  s.  co m
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("MTN")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Password")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("SSN")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                accountDetails.MTN = String
                        .valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                accountDetails.Password = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                accountDetails.SSN = String
                        .valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (accountDetails == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return accountDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
//Inputs IDType; State
public CustomerDetails GetCustomerDetails(IdType idType) throws IOException, NullPointerException {
    CustomerDetails customerDetails = new CustomerDetails();
    String filePath = FilePath(FileNames.GlobalTestData);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    worksheet = workBook.getSheet("Customer");
    System.out.println(worksheet.getSheetName());
    String IDType = "Driver's License";
    if (idType.toString().equals("USPASSPORT")) {
        IDType = "Passport";
    } else if (idType.toString().equals("STATEID")) {
        IDType = "STATE ID";

    }//from  ww  w .  jav  a 2  s .co  m

    try {
        int statusColumn = -1;

        for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
            row = worksheet.getRow(i);

            if (i == 0) {
                int numOfCells = row.getPhysicalNumberOfCells();
                if (worksheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                        .contains(Status.STATUS.toString())) {
                    statusColumn = numOfCells - 1;
                } else {
                    statusColumn = numOfCells;
                    Cell statusCell = row.createCell(statusColumn);
                    statusCell.setCellValue(Status.STATUS.toString());
                    continue;
                }
            }

            if (row.getCell(statusColumn) == null) {

                Cell cell = row.createCell(statusColumn);
                cell.setCellValue(Status.UNUSED.toString());
            }

            if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())
                    && row.getCell(12).toString().equals(IDType)) {
                customerDetails.FirstName = String.valueOf(row.getCell(0));
                customerDetails.LastName = String.valueOf(row.getCell(1));
                customerDetails.Address1 = String.valueOf(row.getCell(2));
                customerDetails.City = String.valueOf(row.getCell(4));
                customerDetails.State = String.valueOf(row.getCell(5));
                System.out.println(customerDetails.State.toString());
                if (row.getCell(6).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.Zip = String.valueOf((int) row.getCell(6).getNumericCellValue());
                if (row.getCell(7).getCellType() != Cell.CELL_TYPE_BLANK) {
                    System.out.println(row.getCell(7).getCellType());
                    customerDetails.PhNum = String.valueOf((int) row.getCell(7).getNumericCellValue());
                }
                if (row.getCell(8).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.EMail = String.valueOf(row.getCell(8));
                if (row.getCell(9).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayMonth = String.valueOf(row.getCell(9));
                if (row.getCell(10).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayDay = String.valueOf((int) row.getCell(10).getNumericCellValue());
                if (row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayYear = String.valueOf((int) row.getCell(11).getNumericCellValue());
                if (row.getCell(12).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDType = String.valueOf(row.getCell(12));
                if (row.getCell(13).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDState = String.valueOf(row.getCell(13));
                if (row.getCell(14).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDNumber = String.valueOf(row.getCell(14));
                if (row.getCell(15).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDExpirationMonth = String.valueOf(row.getCell(15));
                if (row.getCell(16).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDExpirationYear = String
                            .valueOf((int) row.getCell(16).getNumericCellValue());
                if (row.getCell(17).getCellType() != Cell.CELL_TYPE_BLANK)
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                if (customerDetails != null)
                    break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (customerDetails == null) {
        System.out.println("FAILED to get customer details ");
    }

    return customerDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public CreditCardDetails GetCreditCardDetails(CardType cardType) throws IOException {
    CreditCardDetails creditCardDetails = new CreditCardDetails();
    String filePath = FilePath(FileNames.GlobalTestData);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    worksheet = workBook.getSheet("CreditCard");

    try {//from w  ww. java  2s .com
        int cardRow = -1;

        for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
            row = worksheet.getRow(i);

            if (row.getCell(0).getCellType() != Cell.CELL_TYPE_BLANK) {
                if (row.getCell(0).toString().equals(cardType)) {
                    creditCardDetails.Number = row.getCell(1).getStringCellValue();
                    creditCardDetails.ExpiryMonth = row.getCell(2).getStringCellValue();
                    if (row.getCell(3).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                        creditCardDetails.ExpiryYear = String.valueOf(row.getCell(3).getNumericCellValue());
                    } else
                        creditCardDetails.ExpiryYear = row.getCell(3).getStringCellValue();
                    creditCardDetails.CVV = String.valueOf((int) row.getCell(2).getNumericCellValue());
                    if (creditCardDetails != null)
                        break;
                }
            }

        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (creditCardDetails == null) {
        System.out.println("FAILED to get Credit card details ");
    }

    return creditCardDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public int CreateStatusColumn(String filePath, String workSheetName) throws IOException {
    //String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet worksheet = GetSheetFromWorkBook(workBook, workSheetName);
    int statusColumn = -1;
    try {/*from ww  w  . ja  va 2s . c  o  m*/
        for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
            row = worksheet.getRow(i);
            if (i == 0) {
                int numOfCells = row.getPhysicalNumberOfCells();
                if (worksheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                        .contains(Status.STATUS.toString())) {
                    statusColumn = numOfCells - 1;
                } else {
                    statusColumn = numOfCells;
                    Cell statusCell = row.createCell(statusColumn);
                    statusCell.setCellValue(Status.STATUS.toString());
                    continue;
                }
            }
            if (row.getCell(statusColumn) == null) {

                Cell cell = row.createCell(statusColumn);
                cell.setCellValue(Status.UNUSED.toString());
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return statusColumn;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public NPANXX GetNumberPortData(FileNames fileName, SheetName workSheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, workSheetName.toString());
    NPANXX npaNXX = new NPANXX();

    try {// w  w w  .  j a v a2 s .c o m
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("NGP")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Location")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("NPANXX")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                npaNXX.NGP = String.valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                npaNXX.Location = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                npaNXX.NPANXX = String.valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (npaNXX == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return npaNXX;
}