Example usage for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows

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

Introduction

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

Prototype

@Override
public int getPhysicalNumberOfRows()

Source Link

Document

Returns the number of physically defined rows (NOT the number of rows in the sheet)

Usage

From source file:com.adobe.acs.commons.data.SpreadsheetTest.java

License:Apache License

private static XSSFRow createRow(XSSFSheet sheet, String... values) {
    int rowNum = sheet.getPhysicalNumberOfRows();
    XSSFRow row = sheet.createRow(rowNum);
    for (int i = 0; i < values.length; i++) {
        if (values[i] == null) {
            row.createCell(i);//from ww w .j av a  2s  .  c o  m
        } else {
            row.createCell(i).setCellValue(values[i]);
        }
    }
    return row;
}

From source file:com.adobe.acs.commons.mcp.util.SpreadsheetTest.java

License:Apache License

static private XSSFRow createRow(XSSFSheet sheet, String... values) {
    int rowNum = sheet.getPhysicalNumberOfRows();
    XSSFRow row = sheet.createRow(rowNum);
    for (int i = 0; i < values.length; i++) {
        row.createCell(i).setCellValue(values[i]);
    }//from   ww w . j av  a2 s  .  c o m
    return row;
}

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }// w  ww.  j  a va 2 s.  co  m

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

@Override
public void initialize(InputSplit split, TaskAttemptContext context) throws IOException, InterruptedException {
    FileSplit fileSplit = (FileSplit) split;
    Configuration conf = context.getConfiguration();
    Path file = fileSplit.getPath();
    FileSystem fs = file.getFileSystem(conf);
    this.in = fs.open(file);
    XSSFWorkbook workbook = new XSSFWorkbook(this.in);
    XSSFSheet sheet = workbook.getSheetAt(0);
    this.totalRows = sheet.getPhysicalNumberOfRows();
    this.processedRows = 0;
    this.rowIterator = sheet.rowIterator();
}

From source file:com.cn.util.ExcelImport.java

/**
* ?2007excel//from  w ww .  java2s. c om
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

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;/*from w  w  w . j  av  a 2s .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 SetStatusForSimTypSheets(String sheet, FileNames fileName) throws IOException {
    int statusColumn;
    XSSFSheet workSheet = null;
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = GetWorkBook(fileName);/*from  ww  w. j  a  v  a 2s.  co m*/
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        if (workBook.getSheetName(i).toLowerCase().contains(sheet.toLowerCase())) {
            workSheet = workBook.getSheetAt(i);
            break;
        }
    }

    try {
        for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Row statusRow = workSheet.getRow(i);
            for (int ct = 0; ct < statusRow.getPhysicalNumberOfCells(); ct++) {
                System.out.println(statusRow.getCell(ct));
            }
            int numOfCells = statusRow.getPhysicalNumberOfCells();
            if (workSheet.getRow(i).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                    .contains(Status.STATUS.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.UNUSED.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.INUSE.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.USED.toString())) {
                statusColumn = numOfCells - 1;
            } else {
                statusColumn = numOfCells;
                Cell statusCell = workSheet.getRow(i).createCell(statusColumn);
                statusCell.setCellValue(Status.UNUSED.toString());
                continue;
            }
            System.out.println(statusRow.getCell(statusColumn));
            try {
                if (statusRow.getCell(statusColumn) == null) {

                    Cell cell = row.createCell(statusColumn);
                    cell.setCellValue(Status.UNUSED.toString());
                }
            } catch (Exception ex) {
                row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
            }
        }
    } catch (Exception ex) {
        Log.error(ex.toString());
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

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 {/*w  ww.  jav a2s  .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

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 {/*w ww . j  a v  a2  s. 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 {/* ww w.  j  a  va 2  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;
}