Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.asakusafw.testdriver.excel.ExcelSheetRuleProvider.java

License:Apache License

private <T> VerifyRule resolve(DataModelDefinition<T> definition, VerifyContext context, Sheet sheet,
        ExcelRuleExtractor extractor) throws ExcelRuleExtractor.FormatException {
    assert definition != null;
    assert context != null;
    assert sheet != null;
    assert extractor != null;

    VerifyRuleBuilder builder = new VerifyRuleBuilder(definition);
    Set<DataModelCondition> modelPredicates = extractor.extractDataModelCondition(sheet);
    if (modelPredicates.contains(DataModelCondition.IGNORE_ABSENT)) {
        builder.acceptIfAbsent();/* w w w .j a  va  2  s. c o m*/
    }
    if (modelPredicates.contains(DataModelCondition.IGNORE_UNEXPECTED)) {
        builder.acceptIfUnexpected();
    }
    if (modelPredicates.contains(DataModelCondition.IGNORE_MATCHED) == false) {
        int start = extractor.extractPropertyRowStartIndex(sheet);
        int end = sheet.getLastRowNum() + 1;
        for (int i = start; i < end; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            resolveRow(builder, definition, context, row, extractor);
        }
    }
    return builder.toVerifyRule();
}

From source file:com.b2international.snowowl.datastore.server.importer.TerminologyImportExcelParser.java

License:Apache License

private int getMemberStartIndex(final Sheet sheet, final int propertyIndex) {

    int i = propertyIndex;

    while (i < sheet.getLastRowNum()) {

        final Row row = sheet.getRow(i);

        if (null != row) {

            final String value = ExcelUtilities
                    .extractContentAsString(row.getCell(0, Row.CREATE_NULL_AS_BLANK));

            if (!StringUtils.isEmpty(value)
                    && (value.equalsIgnoreCase("code") || value.equalsIgnoreCase("effective time"))) {
                break;
            }/*from w w w.  j  a  v  a 2s . co  m*/

        }

        i++;

    }

    return i;

}

From source file:com.b2international.snowowl.datastore.server.importer.TerminologyImportExcelParser.java

License:Apache License

private Set<Row> processMembers(final Sheet sheet, int memberStartIndex) {

    final int lastRowNum = sheet.getLastRowNum();
    final Set<Row> componentMembers = Sets.newHashSet();

    for (int i = memberStartIndex; i <= lastRowNum; i++) {
        final Row row = sheet.getRow(i);
        if (null != row) {

            boolean hasValue = false;
            short lastCellNum = row.getLastCellNum();

            for (int j = 0; j < lastCellNum; j++) {
                final String cellValue = ExcelUtilities.extractContentAsString(row.getCell(j));

                // member header row
                if (memberStartIndex == i) {
                    // header row does not have values, but column index to name map
                    columnIndexesByName.put(cellValue, j);
                } else if (!StringUtils.isEmpty(cellValue)) {
                    hasValue = true;//from   w ww . ja v  a  2 s . com
                    break;
                }
            }

            if (hasValue) {
                componentMembers.add(row);
            }
        }
    }

    return componentMembers;

}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

private void parse(Sheet sheet) throws SnowowlServiceException {

    int firstRowIndex = findFirstRow(sheet);

    if (firstRowIndex == -1) {
        return;//from  w  ww  .ja v  a 2s.  c  o  m
    }

    if (hasHeader) {
        header = collectRowValues(sheet.getRow(firstRowIndex));
        firstRowIndex++;
    } else {
        final Row firstRow = sheet.getRow(firstRowIndex);
        Cell first = firstRow.getCell(firstRow.getFirstCellNum());
        Cell second = firstRow.getCell(firstRow.getFirstCellNum() + 1);
        if (isNumeric(first) || isNumeric(second)) {
            header.add("ID");
        }
        if (isString(first) || isString(second)) {
            header.add("Label");
        }
    }

    for (int i = firstRowIndex; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);

        // totally empty row w/o any value
        if (row == null) {
            if (!skipEmptyRows) {
                content.add(Collections.<String>emptyList());
            }
            continue;
        }

        if (row.getLastCellNum() > maxWidth) {
            maxWidth = row.getLastCellNum();
        }

        List<String> rowValues = collectRowValues(row);

        if (rowValues.isEmpty()) {
            if (!skipEmptyRows) {
                content.add(Collections.<String>emptyList());
            }
            continue;
        }

        content.add(rowValues);
    }
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private List<Integer> getSheetAndFirstRowNumber(final Workbook workbook, final int numberOfSheets) {
    for (int i = 0; i < numberOfSheets; i++) {
        final Sheet sheet = workbook.getSheetAt(i);

        int firstRow = -1;

        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            final List<String> row = collectRowValues(sheet.getRow(j));

            for (final String value : row) {
                if (!value.isEmpty() && -1 == firstRow) {
                    firstRow = j;/*from   w  w  w  .  j  av a2s . c om*/
                }
            }

            if (containsConceptId(row)) {
                return Lists.newArrayList(i, firstRow);
            }
        }
    }

    return null;
}

From source file:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel/*from  w  w  w.  j  a v a2s .c  o  m*/
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}

From source file:com.ben12.reta.util.RETAAnalysis.java

License:Open Source License

public void writeExcel(Window parent) throws IOException, InvalidFormatException {
    logger.info("Start write excel output");

    Path outputFile = Paths.get(output);
    if (!outputFile.isAbsolute()) {
        Path root = config.getAbsoluteFile().getParentFile().toPath();
        outputFile = root.resolve(outputFile);
    }//from  w ww.j  ava  2  s . c o m

    // test using template
    InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx");
    ExcelTransformer transformer = new ExcelTransformer();
    List<String> sheetNames = new ArrayList<>();
    List<String> sheetTemplateNames = new ArrayList<>();
    for (InputRequirementSource requirementSource : requirementSources.values()) {
        sheetTemplateNames.add("DOCUMENT");
        sheetTemplateNames.add("COVERAGE");
        sheetNames.add(requirementSource.getName());
        sheetNames.add(requirementSource.getName() + " coverage");
    }

    List<Map<String, Object>> sheetValues = new ArrayList<>();
    for (InputRequirementSource source : requirementSources.values()) {
        Map<String, Object> values = new HashMap<>();
        values.put("source", source);
        values.put("null", null);
        values.put("line", "\n");

        Set<String> attributes = new LinkedHashSet<>();
        attributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            attributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        attributes.addAll(source.getAttributesGroup().keySet());
        attributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("attributes", attributes);

        Set<String> refAttributes = new LinkedHashSet<>();
        refAttributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            refAttributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        refAttributes.addAll(source.getRefAttributesGroup().keySet());
        refAttributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("refAttributes", refAttributes);

        sheetValues.add(values);
        sheetValues.add(values);
    }

    Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues);
    int sheetCount = wb.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int columns = 0;
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row != null) {
                row.setHeight((short) -1);
                columns = Math.max(columns, row.getLastCellNum() + 1);
            }
        }
        for (int j = 0; j < columns; j++) {
            sheet.autoSizeColumn(j);
        }
    }

    try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
        wb.write(fos);
    } catch (FileNotFoundException e) {
        int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed.");

        if (confirm == MessageDialog.OK_OPTION) {
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                wb.write(fos);
            } catch (IOException e2) {
                throw e2;
            }
        } else {
            throw e;
        }
    }

    logger.info("End write excel output");
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {//w  ww .j  a va2  s .c  o m
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean deleteColumn(int[] columnIndex)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Sheet sheet = null;
    try {//from w  w w. j a v  a  2  s .  c  o  m
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            //?
            Row tmpRow = sheet.getRow(i);

            for (int j = columnIndex.length - 1; j > -1; j--) {
                //????
                for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) {
                    Cell tmpCell = tmpRow.getCell(k);
                    if (null != tmpCell) {
                        tmpRow.removeCell(tmpCell);
                    }
                    Cell rightCell = tmpRow.getCell(k + 1);
                    if (null != rightCell) {
                        HSSFRow hr = (HSSFRow) tmpRow;
                        hr.moveCell((HSSFCell) rightCell, (short) k);
                    }
                }

            }
        }
        m_InputStream.close();
        try ( // Write the output to a file
                final FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }
    return flag;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean addColumn(Object[] columnName, Object[][] columnData)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Row rowCaption;/*from  ww w  .  j av  a  2 s  .  c  o m*/
    Sheet sheet = null;

    try {
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }
        //
        rowCaption = sheet.getRow(0);
        if (rowCaption != null) {
            int columnsCount = rowCaption.getLastCellNum();
            for (int i = 0; i < columnName.length; i++) {
                Cell cell = rowCaption.createCell(columnsCount + i);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(columnName[i]));
            }

            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                //?
                Row tmpRow = sheet.getRow(i + 1);

                for (int cIndex = 0; cIndex < columnName.length; cIndex++) {
                    Cell cell = tmpRow.getCell(columnsCount + cIndex);
                    if (cell == null) {
                        cell = tmpRow.createCell(columnsCount + cIndex);
                    }
                    //?
                    Object obj = columnData[i][cIndex];
                    if (obj.getClass().getName().equals(Double.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    } else if (obj.getClass().getName().equals(String.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    } else {
                        //?
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    setCellValue(cell, obj);
                }

            }
        }

        m_InputStream.close();
        try ( // Write the output to a file
                FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;

    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }

    return flag;
}