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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

License:Open Source License

/**
 * Returns the first row headers (field names) mapped to the column indexes
 * @return  Map<ColumnNumber, ColumnHeader>
 *//*from   w w  w .  ja v a 2s  . co m*/
static SortedMap<Integer, String> getFirstRowHeaders(Workbook hSSFWorkbook, Integer sheetID) {
    SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
    if (hSSFWorkbook == null || sheetID == null) {
        return firstRowMap;
    }
    //first search for duplicate columns
    Set<String> sameColumnNames = new HashSet<String>();
    Set<String> columnNames = new HashSet<String>();
    Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    Row firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            String columnHeader = ExcelImportBL.getStringCellValue(cell);
            if (columnHeader != null && !"".equals(columnHeader)) {
                if (columnNames.contains(columnHeader)) {
                    sameColumnNames.add(columnHeader);
                } else {
                    columnNames.add(columnHeader);
                }
            }
        }
    }
    sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            String columnHeader = ExcelImportBL.getStringCellValue(cell);
            if (columnHeader != null && !"".equals(columnHeader)) {
                if (sameColumnNames.contains(columnHeader)) {
                    //for duplicate columns add also the column index
                    columnHeader += " (" + cell.getColumnIndex() + ")";
                }
                firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), columnHeader);
            }
        }
    }
    return firstRowMap;
}

---

From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

License:Open Source License

/**
 * Returns the first row of a sheet where there is the fields name
 * @return  Map<ColumnNumber, FieldLabelName>
 *//*from ww w  . j  ava2s  .c  o  m*/
static SortedMap<Integer, String> getFirstRowNumericToLetter(Workbook hSSFWorkbook, Integer sheetID) {
    SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
    if (hSSFWorkbook == null || sheetID == null) {
        return firstRowMap;
    }
    Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    Row firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), colNumericToLetter(cell.getColumnIndex()));
        }
    }
    return firstRowMap;
}

---

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   www .ja v  a  2  s . c om

        }

        i++;

    }

    return i;

}

---

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

License:Apache License

private LinkedList<String> processProperties(final Sheet sheet, final int propertyIndex) {
    final LinkedList<String> componentProperties = Lists.newLinkedList();
    for (int i = 0; i < propertyIndex; i++) {
        final Row row = sheet.getRow(i);
        if (row != null) {
            componentProperties/*from w  w w  . j  a  v a  2 s.  c o  m*/
                    .add(ExcelUtilities.extractContentAsString(row.getCell(1, Row.CREATE_NULL_AS_BLANK)));
        }
    }
    return componentProperties;
}

---

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

License:Apache License

private Multimap<String, String> processKeywords(Sheet sheet, int propertyIndex, int memberStartIndex) {

    final Multimap<String, String> componentMetadata = HashMultimap.create();

    for (int i = propertyIndex + 1; i < memberStartIndex - 1; i++) {

        final Row row = sheet.getRow(i);

        if (null != row) {

            final String groupName = ExcelUtilities
                    .extractContentAsString(row.getCell(0, Row.CREATE_NULL_AS_BLANK));
            final String keyword = ExcelUtilities
                    .extractContentAsString(row.getCell(1, Row.CREATE_NULL_AS_BLANK));

            if (!groupName.isEmpty()) {

                componentMetadata.put(groupName, keyword);

            }/* w  ww  .  j  av a2 s . co  m*/

        }
    }

    return componentMetadata;

}

---

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  www  .  j av a 2 s.co m*/
                    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;/*  w ww.j a  v  a 2 s . co  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 boolean processExcelFile(final SubsetEntry entry) throws InvalidFormatException, IOException {

    final FileInputStream inputStream = createFileInputStream(entry);
    final Workbook workbook = WorkbookFactory.create(inputStream);
    final List<Integer> list = getSheetAndFirstRowNumber(workbook, workbook.getNumberOfSheets());

    if (null != list) {
        final int sheetNumber = list.get(0);
        final int firstRowNumber = list.get(1);
        final Sheet sheet = workbook.getSheetAt(sheetNumber);
        final List<String> row = collectRowValues(sheet.getRow(firstRowNumber));

        entry.setHeadings(row);/*from w w  w  .  j a v  a  2s  . c  o  m*/
        entry.setSheetNumber(sheetNumber);

        if (entry.isHasHeader()) {
            Optional<String> match = FluentIterable.from(row).firstMatch(new Predicate<String>() {
                @Override
                public boolean apply(String input) {
                    return input.contains("concept") && (input.contains("id") || input.contains("sctid"));
                }
            });
            entry.setIdColumnNumber(match.isPresent() ? row.indexOf(match.get()) : 0); // default to first?
        } else {
            for (int i = 0; i < row.size(); i++) {
                if (isConceptId(row.get(i).trim())) {
                    entry.setIdColumnNumber(i);
                }
            }
        }

        return true;
    } else {
        return false;
    }
}

---

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;//w  w  w  . java  2s  .  c  om
                }
            }

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

    return null;
}

---

From source file:com.b510.excel.client.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from w  ww  . j  av  a 2 s.co m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »