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

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

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

Introduction

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

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java

License:Open Source License

public void processRow(Row row, TopicMap tm) {
    Iterator<Cell> cellIterator = row.cellIterator();
    Association a = null;/*from  w  w w  .jav  a 2 s .c  om*/
    while (cellIterator.hasNext() && !forceStop()) {
        try {
            Cell cell = cellIterator.next();
            if (getCellValueAsString(cell) != null) {
                Topic player = getCellTopic(cell, tm);
                if (player != null) {
                    if (a == null) {
                        a = tm.createAssociation(getDefaultAssociationTypeTopic(tm));
                    }
                    if (a != null) {
                        String roleSI = rolesPerColumn.get(Integer.toString(cell.getColumnIndex()));
                        if (roleSI != null) {
                            Topic role = tm.getTopic(roleSI);
                            if (role == null)
                                role = getDefaultRoleTopic(cell, tm);
                            a.addPlayer(player, role);
                        }
                    }
                }
            }
        } catch (TopicMapException ex) {
            log(ex);
        } catch (Exception ex) {
            log(ex);
        }
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java

License:Open Source License

public void processRowAsRoles(Row row, TopicMap topicMap) {
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        try {/*from  www .  j  a v a2  s . co  m*/
            Cell cell = cellIterator.next();
            if (getCellValueAsString(cell) != null) {
                Topic cellTopic = getCellTopic(cell, topicMap);
                rolesPerColumn.put(Integer.toString(cell.getColumnIndex()),
                        cellTopic.getOneSubjectIdentifier().toExternalForm());
            }
        } catch (TopicMapException ex) {
            log(ex);
        } catch (Exception ex) {
            log(ex);
        }
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyMatrixExtractor.java

License:Open Source License

public void processRow(Row row, TopicMap tm) {
    Association a = null;// ww  w.j a v a 2 s  . c  o  m

    try {
        Cell firstColumnCell = row.getCell(0);
        if (firstColumnCell != null) {
            if (getCellValueAsString(firstColumnCell) != null) {
                Topic cellTopic = getCellTopic(firstColumnCell, tm);
                rowLabels.put(Integer.toString(firstColumnCell.getRowIndex()),
                        cellTopic.getOneSubjectIdentifier().toExternalForm());
            } else {
                return;
            }
        }

        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext() && !forceStop()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() > 0) {
                processCell(cell, tm);
            }
        }
    } catch (TopicMapException ex) {
        log(ex);
    } catch (Exception ex) {
        log(ex);
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyMatrixExtractor.java

License:Open Source License

public void processAsLabels(Row row, TopicMap topicMap) {
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        try {/*from  w  w w .  j ava  2s  . c  o  m*/
            Cell cell = cellIterator.next();
            if (getCellValueAsString(cell) != null) {
                Topic cellTopic = getCellTopic(cell, topicMap);
                columnLabels.put(Integer.toString(cell.getColumnIndex()),
                        cellTopic.getOneSubjectIdentifier().toExternalForm());
            }
        } catch (TopicMapException ex) {
            log(ex);
        } catch (Exception ex) {
            log(ex);
        }
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicExtractor.java

License:Open Source License

public void processRow(Row row, TopicMap tm) {
    Iterator<Cell> cellIterator = row.cellIterator();

    try {/*from w w  w.  jav a2  s .c  o  m*/
        while (cellIterator.hasNext() && !forceStop()) {
            Cell cell = cellIterator.next();
            processCell(cell, tm);
        }
    } catch (Exception ex) {
        log(ex);
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicNameExtractor.java

License:Open Source License

public void processRow(Row row, TopicMap tm) {
    try {/*from   www .  j a va  2  s  . com*/
        Iterator<Cell> cellIterator = row.cellIterator();
        Topic topic = null;
        Cell firstCell = row.getCell(0);
        if (getCellValueAsString(firstCell) != null) {
            topic = getCellTopic(firstCell, tm);
        }
        if (topic != null) {
            while (cellIterator.hasNext() && !forceStop()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() != 0) {
                    String name = getCellValueAsString(cell);
                    if (name != null) {
                        String langSI = languagesPerColumn.get(Integer.toString(cell.getColumnIndex()));
                        Topic lang = tm.getTopic(langSI);
                        if (lang == null)
                            lang = getDefaultLanguageTopic(cell, tm);
                        HashSet scope = new LinkedHashSet();
                        scope.add(lang);
                        if (ADD_DISPLAY_TO_SCOPE)
                            scope.add(tm.getTopic(XTMPSI.DISPLAY));
                        if (ADD_SORT_TO_SCOPE)
                            scope.add(tm.getTopic(XTMPSI.SORT));
                        topic.setVariant(scope, name);
                    }
                }
            }
        }
    } catch (TopicMapException ex) {
        log(ex);
    } catch (Exception ex) {
        log(ex);
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicNameExtractor.java

License:Open Source License

public void processRowAsLanguages(Row row, TopicMap tm) {
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        try {/* w w  w.j a  v a2 s . c  o  m*/
            String langSI = null;
            Cell cell = cellIterator.next();
            String lang = getCellValueAsString(cell);
            if (lang != null) {
                Topic langTopic = tm.getTopicWithBaseName(lang);
                if (langTopic != null)
                    langSI = langTopic.getOneSubjectIdentifier().toExternalForm();
                else
                    langSI = XTMPSI.getLang(lang);
                langTopic = tm.getTopic(new Locator(langSI));
                languagesPerColumn.put(Integer.toString(cell.getColumnIndex()), langSI);

                if (langTopic == null && CREATE_MISSING_LANGUAGE_TOPICS) {
                    langTopic = tm.createTopic();
                    langTopic.addSubjectIdentifier(new Locator(langSI));
                    Topic langTypeTopic = tm.getTopic(XTMPSI.LANGUAGE);
                    if (langTypeTopic != null) {
                        langTopic.addType(langTypeTopic);
                    }
                }
            }
        } catch (Exception ex) {
            log(ex);
        }
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicOccurrenceExtractor.java

License:Open Source License

public void processRow(Row row, TopicMap tm) {
    try {// w  w  w.  j a  v  a  2s. co  m
        Iterator<Cell> cellIterator = row.cellIterator();
        Topic topic = null;
        Cell firstCell = row.getCell(0);
        if (getCellValueAsString(firstCell) != null) {
            topic = getCellTopic(firstCell, tm);
        }
        if (topic != null) {
            while (cellIterator.hasNext() && !forceStop()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() > 0) {
                    String occurrence = getCellValueAsString(cell);
                    if (occurrence != null) {
                        Topic type = null;
                        String typeSI = occurrenceTypes.get(Integer.toString(cell.getColumnIndex()));
                        if (typeSI != null)
                            type = tm.getTopic(typeSI);
                        if (type == null)
                            type = getDefaultOccurrenceTypeTopic(cell, tm);
                        topic.setData(type, tm.getTopic(XTMPSI.getLang(DEFAULT_LANG)), occurrence);
                    }
                }
            }
        }
    } catch (TopicMapException ex) {
        log(ex);
    } catch (Exception ex) {
        log(ex);
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicOccurrenceExtractor.java

License:Open Source License

public void processRowAsOccurrenceTypes(Row row, TopicMap topicMap) {
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        try {/*from   w  ww .j  av  a2s.  c  o  m*/
            Cell cell = cellIterator.next();
            if (getCellValueAsString(cell) != null) {
                Topic cellTopic = getCellTopic(cell, topicMap);
                occurrenceTypes.put(Integer.toString(cell.getColumnIndex()),
                        cellTopic.getOneSubjectIdentifier().toExternalForm());
            }
        } catch (TopicMapException ex) {
            log(ex);
        } catch (Exception ex) {
            log(ex);
        }
    }
}

From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java

License:Open Source License

@RequestMapping(method = RequestMethod.POST)
protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile,
        @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response)
        throws Exception {

    // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory?
    File file = multipartToFile(uploadFile);
    String mergedResultFileName = "merged_" + file.getName();
    FileInputStream fis = new FileInputStream(file);

    // Finds the workbook instance of XLSX file
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

    // number of sheets in the workbook
    int numberOfSheets = workbook.getNumberOfSheets();

    // contains all values of the merge column across all sheets
    ArrayList<String> mergeColumnValues = new ArrayList<String>();

    // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>>
    HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>();

    // maps sheet index to the headers in that sheet
    HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>();

    // how many copies of headers need to be created for each sheet
    HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>();

    // loop through the sheets in the workbook and populate the variables
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        int mergeColumnIndex = -1; // index of the merge column in this sheet
        int rowIteratorIndex = 0; // index of current row iteration

        // collect all of the merge column rows in each sheet
        Iterator<Row> rowIterator = sheet.rowIterator();

        int maxSheetRowCountForCurrentSheet = 0;
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();
            if (rowIteratorIndex == 0) {
                // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders
                // and add it to sheetIndexToSheetColumnHeaders
                ArrayList<String> sheetColumnHeaders = new ArrayList<String>();

                int rowCellIteratorIndex = 0;
                Iterator<Cell> topRowCellIterator = row.cellIterator();
                while (topRowCellIterator.hasNext()) {
                    Cell topRowCell = topRowCellIterator.next();
                    String topRowCellString = topRowCell.toString();
                    if (!topRowCellString.isEmpty()) {
                        sheetColumnHeaders.add(topRowCellString);
                    }//from  w ww .j a v  a 2 s .c  om
                    if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) {
                        // this is the mergeColumn. Remember the column index
                        if (mergeColumnIndex == -1) {
                            mergeColumnIndex = rowCellIteratorIndex;
                        } else {
                            // there are multiple mergeColumnTitles in this sheet. Let the user know and exit
                            ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets");
                            mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle
                                    + "\" in worksheet #" + (sheetIndex + 1)
                                    + ". You can have only one merge column per worksheet. Please fix and try again.");
                            return mav;
                        }
                    }
                    rowCellIteratorIndex++;
                }
                sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders);
            } else {
                // for rows that are not the top row (header)
                // 1. get all the mergeColumnValues
                // 2. populate mergeColumnValueToSheetRows
                // 3. calculate sheetIndexToMaxSheetRowCount
                Cell mergeColumnValueCell = row.getCell(mergeColumnIndex);
                if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) {

                    objFormulaEvaluator.evaluate(mergeColumnValueCell);
                    String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell,
                            objFormulaEvaluator);

                    HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows
                            .get(mergeColumnValueString);
                    if (sheetIndexToSheetRows == null) {
                        sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>();
                        mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows);
                    }
                    ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex);
                    if (sheetRows == null) {
                        sheetRows = new ArrayList<>();
                        sheetIndexToSheetRows.put(sheetIndex, sheetRows);
                    }
                    sheetRows.add(row);
                    if (sheetRows.size() > maxSheetRowCountForCurrentSheet) {
                        maxSheetRowCountForCurrentSheet = sheetRows.size();
                    }

                    Iterator<Cell> rowCellIterator = row.cellIterator();
                    int rowCellIteratorIndex = 0;
                    while (rowCellIterator.hasNext()) {
                        Cell rowCell = rowCellIterator.next();
                        if (rowCellIteratorIndex == mergeColumnIndex) {
                            // this is a merge column cell, so add its value to mergeColumnValues
                            if (!rowCell.toString().isEmpty()) {
                                objFormulaEvaluator.evaluate(rowCell);
                                String rowCellValueString = objDefaultFormat.formatCellValue(rowCell,
                                        objFormulaEvaluator);
                                if (!mergeColumnValues.contains(rowCellValueString)) {
                                    mergeColumnValues.add(rowCellValueString);
                                }
                            }
                        }
                        rowCellIteratorIndex++;
                    }
                }
            }
            rowIteratorIndex++;
        }
        sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet);
    }

    // Now we are ready to make the merge sheet. We will be writing one row at a time.

    Workbook wb = new XSSFWorkbook(); // new output workbook
    Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet

    // make the header row
    Row headerRow = mergedSheet.createRow(0);

    // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues.
    Cell mergeColumnHeaderCell = headerRow.createCell(0);
    mergeColumnHeaderCell.setCellValue(mergeColumnTitle);

    // current column index "cursor" where we will be writing to
    int cellIndexWithoutMergeColumn = 1;

    // make the header row
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {

        Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
        ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        String sheetName = sheet.getSheetName();

        for (int i = 0; i < maxSheetRowCount; i++) {
            for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders
                    .size(); sheetColumnHeaderIndex++) {
                String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex);
                if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) {
                    String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) ";
                    Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn);
                    headerCell.setCellValue(newSheetColumnHeader);
                    cellIndexWithoutMergeColumn++;
                }
            }
        }
    }

    // now make all the non-header rows
    for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues
            .size(); mergeColumnValueIndex++) {
        String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex);
        HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows
                .get(mergeColumnValue);
        if (mergeColumnValueSheetRow == null) {
            System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: "
                    + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue);
            continue;
        }

        Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row;

        // reset current cursor as we make each row
        cellIndexWithoutMergeColumn = 0;

        // first column will be the merge column value
        Cell mergeColumnCell = row.createCell(0);
        mergeColumnCell.setCellValue(mergeColumnValue);
        cellIndexWithoutMergeColumn++;

        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex);
            int currentSheetSheetRowIndex = 0;
            ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);

            if (sheetRows != null) {
                for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) {
                    Row sheetRow = sheetRows.get(sheetRowIndex);
                    for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) {
                        String sheetHeader = sheetHeaders.get(sheetHeaderIndex);
                        if (!sheetHeader.equals(mergeColumnTitle)) {
                            Cell cell = sheetRow.getCell(sheetHeaderIndex);
                            Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                            objFormulaEvaluator.evaluate(cell);
                            String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                            exportCell.setCellValue(cellString);
                            cellIndexWithoutMergeColumn++;
                        }
                    }
                    currentSheetSheetRowIndex++;
                }
            }

            // some columns do not have any values to populate, so populate them with empty cells
            Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
            while (currentSheetSheetRowIndex < maxSheetRowCount) {
                for (int i = 0; i < sheetHeaders.size(); i++) {
                    String sheetHeader = sheetHeaders.get(i);
                    if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) {
                        Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                        exportCell.setCellValue("");
                        cellIndexWithoutMergeColumn++;
                    }
                }
                currentSheetSheetRowIndex++;
            }
        }
    }

    // write to response output
    response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\"");
    ServletOutputStream outputStream = response.getOutputStream();
    wb.write(outputStream);
    fis.close();

    return null;
}