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

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

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

Introduction

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

Prototype

Sheet getSheet();

Source Link

Document

Returns the Sheet this row belongs to

Usage

From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java

License:Apache License

/**
 * Interpretation method for previous function
 * //  ww w .j a  va2 s  .  c o  m
 * @param workBook
 *            the workbook
 * @param row
 *            the row
 * @return the index of the previous row satisfying the given property or in
 *         minimum the first row
 * @throws JeXcException
 *             thrown if operation fails
 */
private Object interpretPreviousFunction(Workbook workBook, Row row) throws JeXcException {
    ExqlExpression ex = getExpressions().get(0);

    Sheet sheet = row.getSheet();
    for (int i = row.getRowNum(); i >= sheet.getFirstRowNum() + 1; i--) {
        Row r = sheet.getRow(i);
        Object result = ex.interpret(workBook, Arrays.asList(new Row[] { r }));
        if (result instanceof Collection && !((Collection<?>) result).isEmpty()) {
            return r.getRowNum();
        }
    }
    /*
     * get minimum rows
     */
    return sheet.getFirstRowNum();
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*from  ww w  . j  a v a2s  . c  om*/
 * @return
 * @throws IllegalStateException
 */
private Competitor parseCompetitorRow(Row row) throws IllegalStateException {
    Competitor competitor = new Competitor();
    // parse competitor data
    for (int i = 1; i < 6; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            switch (i) {
            // parse name
            case 1:
                String name = cell.getStringCellValue();
                if (name != null) {
                    name = StringUtil.ucwords(name);
                    if (name.lastIndexOf(' ') != -1) {
                        competitor.setFirstname(StringUtil.parseFirstname(name));
                        competitor.setSurname(StringUtil.parseSurname(name));
                        log.debug("Found competitor: {}", name);
                    } else {
                        log.error("[{}] Missing firstname and/or surname for row: {}",
                                row.getSheet().getSheetName(), row.getRowNum() + 1);
                    }
                }
                break;

            // parse iso country code
            case 2:
                String country = cell.getStringCellValue();
                if (country != null) {
                    String countryCode = null;
                    if (country.length() > 2) {
                        countryCode = getCountryUtil().getCountryCodeByName(country);
                    } else {
                        countryCode = getCountryUtil().getCountryByCode(country);
                    }
                    if (countryCode != null) {
                        competitor.setCountry(countryCode);
                        log.debug("Country: {} - {}", countryCode, country);
                    } else {
                        log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                                row.getRowNum() + 1);
                    }
                }
                break;

            // parse wca id
            case 3:
                String wcaId = cell.getStringCellValue();
                if (wcaId != null) {
                    wcaId = wcaId.trim();
                    if (wcaId.length() == 10) {
                        Matcher m = wcaIdPattern.matcher(wcaId);
                        if (m.find()) {
                            competitor.setWcaId(wcaId);
                            log.debug("WCA Id: {}", competitor.getWcaId());
                        } else {
                            log.warn("[{}] Invalid wcaId format: {}", row.getSheet().getSheetName(), wcaId);
                        }
                    } else {
                        log.warn("[{}] Entered WCA id has wrong length. Expected: 10, Was: {}. Row: {}",
                                new Object[] { row.getSheet().getSheetName(), wcaId.length(),
                                        row.getRowNum() + 1 });
                    }
                }
                break;

            // parse gender
            case 4:
                String gender = cell.getStringCellValue();
                if (gender != null) {
                    gender = gender.toLowerCase();
                    if ("f".equals(gender) || "m".equals(gender)) {
                        competitor.setGender(gender);
                        log.debug("Gender: {}", ("f".equals(gender) ? "Female" : "Male"));
                    } else {
                        log.warn("[{}] Invalid gender: {}", row.getSheet().getSheetName(), gender);
                    }
                } else {
                    log.warn("[{}] Missing gender information for row: {}", row.getSheet().getSheetName(),
                            row.getRowNum() + 1);
                }
                break;

            // parse birthday
            case 5:
                Date birthday = cell.getDateCellValue();
                if (birthday != null) {
                    try {
                        competitor.setBirthday(birthday);
                        log.debug("Birthday: {}", birthdayFormat.format(birthday));
                    } catch (Exception e) {
                        log.warn("[{}] Invalid birthday format: {}", row.getSheet().getSheetName(), birthday);
                    }
                } else {
                    log.warn("[{}] Missing birthday information for row: {}", row.getSheet().getSheetName(),
                            row.getRowNum() + 1);
                }
                break;
            }
        } else {
            switch (i) {
            case 1:
                log.error("[{}] Missing firstname and/or surname for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 2:
                log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 3:
                // WCA ID are optional
                break;
            case 4:
                log.warn("[{}] Missing gender information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 5:
                log.warn("[{}] Missing birthday information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            }
        }
    }

    // parse registered events
    if (competitor.getFirstname() != null && competitor.getSurname() != null) {
        RegisteredEvents registeredEvents = new RegisteredEvents();
        for (int i = 0; i < getLastEventNum(); i++) {
            Cell cell = row.getCell(i + 7);
            boolean registered = false;
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                log.debug("Registered for: {}", getEventName(i));
                registered = true;
            } else {
                log.debug("Not registered for: {}", getEventName(i));
                registered = false;
            }
            try {
                Method method = registeredEvents.getClass().getMethod("setSignedUpFor" + getEventName(i),
                        boolean.class);
                method.invoke(registeredEvents, registered);
            } catch (Exception e) {
                log.error("[{}] " + e.getLocalizedMessage(), row.getSheet().getSheetName(), e);
            }
        }
        competitor.setRegisteredEvents(registeredEvents);
        return competitor;
    } else {
        return null;
    }
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row//from   w ww  . ja v a2  s . co m
 * @param event
 * @return
 * @throws IllegalStateException
 */
private Result parseResultRow(Row row, Event event) throws IllegalStateException {
    Result result = new Result();

    // competitor data for teams
    if (event.getName().toLowerCase().contains("team")) {
        parseTeamCompetitorData(row, result);
        // normal competitors
    } else {
        parseCompetitorData(row, result);
    }

    // only parse results for competitors with a name / country
    if (result.getFirstname() != null && result.getSurname() != null && result.getCountry() != null) {

        // handle special multiple blindfolded sheet
        if (event.getName().toLowerCase().contains("multi")) {
            // best of 1
            if (Event.Format.BEST_OF_1.getValue().equals(event.getFormat())) {
                result.setResult1(parseResultCell(row, 5));
                result.setBest(result.getResult1());
                result.setRegionalSingleRecord(parseRecordCell(row, 4));

                // best of 2
            } else if (Event.Format.BEST_OF_2.getValue().equals(event.getFormat())) {
                result.setResult1(parseResultCell(row, 4));
                result.setResult2(parseResultCell(row, 8));
                result.setBest(parseResultCell(row, 9));
                result.setRegionalSingleRecord(parseRecordCell(row, 10));

                // unsupported format
            } else {
                log.warn("[{}] Unsupported format: {}", row.getSheet().getSheetName(), event.getFormat());
            }

            // handle special team events
        } else if (event.getName().toLowerCase().contains("team")) {
            // best of 1
            if (Event.Format.BEST_OF_1.getValue().equals(event.getFormat())) {
                result.setResult1(parseResultCell(row, 3 + 1));
                result.setBest(result.getResult1());
                result.setRegionalSingleRecord(parseRecordCell(row, 3 + 2));

                // best of 2
            } else if (Event.Format.BEST_OF_2.getValue().equals(event.getFormat())) {
                result.setResult1(parseResultCell(row, 3 + 1));
                result.setResult2(parseResultCell(row, 3 + 2));
                result.setBest(parseResultCell(row, 3 + 3));
                result.setRegionalSingleRecord(parseRecordCell(row, 3 + 4));

                // best of 3
            } else if (Event.Format.BEST_OF_3.getValue().equals(event.getFormat())) {
                result.setResult1(parseResultCell(row, 3 + 1));
                result.setResult2(parseResultCell(row, 3 + 2));
                result.setResult3(parseResultCell(row, 3 + 3));
                result.setBest(parseResultCell(row, 3 + 4));
                result.setRegionalSingleRecord(parseRecordCell(row, 3 + 5));

                // unsupported format
            } else {
                log.warn("[{}] Unsupported format: {}", row.getSheet().getSheetName(), event.getFormat());
            }

            // average of 5
        } else if (Event.Format.AVERAGE.getValue().equals(event.getFormat())) {
            result.setResult1(parseResultCell(row, 1));
            result.setResult2(parseResultCell(row, 2));
            result.setResult3(parseResultCell(row, 3));
            result.setResult4(parseResultCell(row, 4));
            result.setResult5(parseResultCell(row, 5));
            result.setBest(parseResultCell(row, 6));
            result.setRegionalSingleRecord(parseRecordCell(row, 7));
            result.setWorst(parseResultCell(row, 8));
            result.setAverage(parseResultCell(row, 9));
            result.setRegionalAverageRecord(parseRecordCell(row, 10));

            // best of 1
        } else if (Event.Format.BEST_OF_1.getValue().equals(event.getFormat())) {
            result.setResult1(parseResultCell(row, 1));
            result.setBest(result.getResult1());
            result.setRegionalSingleRecord(parseRecordCell(row, 2));

            // best of 2
        } else if (Event.Format.BEST_OF_2.getValue().equals(event.getFormat())) {
            result.setResult1(parseResultCell(row, 1));
            result.setResult2(parseResultCell(row, 2));
            result.setBest(parseResultCell(row, 3));
            result.setRegionalSingleRecord(parseRecordCell(row, 4));

            // best of 3
        } else if (Event.Format.BEST_OF_3.getValue().equals(event.getFormat())) {
            result.setResult1(parseResultCell(row, 1));
            result.setResult2(parseResultCell(row, 2));
            result.setResult3(parseResultCell(row, 3));
            result.setBest(parseResultCell(row, 4));
            result.setRegionalSingleRecord(parseRecordCell(row, 5));

            // mean of 3
        } else if (Event.Format.MEAN.getValue().equals(event.getFormat())) {
            result.setResult1(parseResultCell(row, 1));
            result.setResult2(parseResultCell(row, 2));
            result.setResult3(parseResultCell(row, 3));
            result.setBest(parseResultCell(row, 4));
            result.setRegionalSingleRecord(parseRecordCell(row, 5));
            result.setAverage(parseResultCell(row, 6));
            result.setRegionalAverageRecord(parseRecordCell(row, 7));

            // unsupported format
        } else {
            log.error("[{}] Unsupported format: {}", row.getSheet().getSheetName(), event.getFormat());
        }
    } else {
        log.debug("[{}] Skipping results for competitor with no name and/or country: Row: {}",
                row.getSheet().getSheetName(), row.getRowNum() + 1);
    }
    return result;
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*from   ww  w.java 2s.  c  o  m*/
 * @param i
 * @return
 * @throws IllegalStateException
 */
private int parseResultCell(Row row, int i) throws IllegalStateException {
    int result = 0;
    Cell cell = row.getCell(3 + i);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        String cellStr = null;

        switch (cell.getCellType()) {
        // result values
        case Cell.CELL_TYPE_NUMERIC:
            // seconds
            if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue() * 100).intValue();

                // minutes
            } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                try {
                    result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                } catch (ParseException e) {
                    log.error("[{}] " + e.getLocalizedMessage(), e);
                }

                // number
            } else if ("0".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue()).intValue();

                // unsupported
            } else {
                log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                        new Object[] { row.getSheet().getSheetName(), cell.getCellStyle().getDataFormatString(),
                                cell.getRowIndex(), cell.getColumnIndex() });
            }
            break;

        // Penalties
        case Cell.CELL_TYPE_STRING:
            cellStr = cell.getStringCellValue();
            if (cellStr != null) {
                if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                    result = Result.Penalty.DNF.getValue();
                } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                    result = Result.Penalty.DNS.getValue();
                }
            }
            break;

        // best / worst
        case Cell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            // calculated value
            case Cell.CELL_TYPE_NUMERIC:
                // seconds
                if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cellValue.getNumberValue() * 100).intValue();

                    // minutes
                } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                    try {
                        result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                    } catch (ParseException e) {
                        log.error("[{}] " + e.getLocalizedMessage(), e);
                    }

                    // number
                } else if ("0".equals(cell.getCellStyle().getDataFormatString())
                        || "GENERAL".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cell.getNumericCellValue()).intValue();

                    // unsupported
                } else {
                    log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                            new Object[] { row.getSheet().getSheetName(),
                                    cell.getCellStyle().getDataFormatString(), cell.getRowIndex(),
                                    cell.getColumnIndex() });
                }
                break;

            // Penalties
            case Cell.CELL_TYPE_STRING:
                cellStr = cellValue.getStringValue();
                if (cellStr != null) {
                    if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                        result = Result.Penalty.DNF.getValue();
                    } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                        result = Result.Penalty.DNS.getValue();
                    }
                }
                break;
            }
            break;
        }
    }
    return result;
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*from  w  w w  .  j a  v a2s .  c om*/
 * @param result
 * @throws IllegalStateException
 */
private void parseCompetitorData(Row row, Result result) throws IllegalStateException {
    for (int i = 1; i < 4; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            switch (i) {
            // parse name
            case 1:
                String name = cell.getStringCellValue();
                if (name != null) {
                    name = StringUtil.ucwords(name);
                    if (name.lastIndexOf(' ') != -1) {
                        result.setFirstname(StringUtil.parseFirstname(name));
                        result.setSurname(StringUtil.parseSurname(name));
                        log.debug("Found result for : {}", name);
                    } else {
                        log.error("[{}] Missing firstname and/or surname for row: {}",
                                row.getSheet().getSheetName(), row.getRowNum() + 1);
                    }
                }
                break;

            // parse iso country code
            case 2:
                String country = cell.getStringCellValue();
                if (country != null) {
                    String countryCode = null;
                    if (country.length() > 2) {
                        countryCode = getCountryUtil().getCountryCodeByName(country);
                    } else {
                        countryCode = getCountryUtil().getCountryByCode(country);
                    }
                    if (countryCode != null) {
                        result.setCountry(countryCode);
                        log.debug("Country: {} - {}", countryCode, country);
                    } else {
                        log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                                row.getRowNum() + 1);
                    }
                }
                break;

            // parse wca id
            case 3:
                String wcaId = cell.getStringCellValue();
                if (wcaId != null) {
                    wcaId = wcaId.trim();
                    if (wcaId.length() == 10) {
                        Matcher m = wcaIdPattern.matcher(wcaId);
                        if (m.find()) {
                            result.setWcaId(wcaId);
                            log.debug("WCA Id: {}", result.getWcaId());
                        } else {
                            log.warn("[{}] Invalid wcaId format: {}", row.getSheet().getSheetName(), wcaId);
                        }
                    } else {
                        log.warn("[{}] Entered WCA id has wrong length. Expected: 10, Was: {}. Row: {}",
                                new Object[] { row.getSheet().getSheetName(), wcaId.length(),
                                        row.getRowNum() + 1 });
                    }
                }
                break;
            }
        } else {
            switch (i) {
            case 1:
                if (result.getCountry() != null) {
                    log.error("[{}] Missing firstname and/or surname for row: {}",
                            row.getSheet().getSheetName(), row.getRowNum() + 1);
                }
                break;
            case 2:
                if (result.getFirstname() != null) {
                    log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                            row.getRowNum() + 1);
                }
                break;
            case 3:
                // WCA ID are optional
                break;
            }
        }
    }
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*from w  w  w  .  ja v a 2s .  c  o m*/
 * @param result
 * @throws IllegalStateException
 */
private void parseTeamCompetitorData(Row row, Result result) throws IllegalStateException {
    Cell nameCell1 = row.getCell(1);
    Cell nameCell2 = row.getCell(1 + 3);
    if ((nameCell1 != null && nameCell1.getCellType() != Cell.CELL_TYPE_BLANK)
            && (nameCell2 != null && nameCell2.getCellType() != Cell.CELL_TYPE_BLANK)) {
        String name1 = nameCell1.getStringCellValue();
        String name2 = nameCell2.getStringCellValue();
        if (name1 != null && name2 != null) {
            name1 = StringUtil.ucwords(name1);
            name2 = StringUtil.ucwords(name2);
            if (name1.lastIndexOf(' ') != -1 && name2.lastIndexOf(' ') != -1) {
                result.setFirstname(
                        StringUtil.parseFirstname(name1) + " / " + StringUtil.parseFirstname(name2));
                result.setSurname(StringUtil.parseSurname(name1) + " / " + StringUtil.parseSurname(name2));
                log.debug("Found result for : {} / {}", name1, name2);
            } else {
                log.error("[{}] Missing firstname and/or surname for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
            }
        }
    }

    Cell countryCell1 = row.getCell(2);
    Cell countryCell2 = row.getCell(2 + 3);
    if ((countryCell1 != null && countryCell1.getCellType() != Cell.CELL_TYPE_BLANK)
            && (countryCell2 != null && countryCell2.getCellType() != Cell.CELL_TYPE_BLANK)) {
        String country1 = countryCell1.getStringCellValue();
        String country2 = countryCell2.getStringCellValue();
        if (country1 != null && country2 != null) {
            String countryCode1 = null;
            String countryCode2 = null;
            if (country1.length() > 2 && country2.length() > 2) {
                countryCode1 = getCountryUtil().getCountryCodeByName(country1);
                countryCode2 = getCountryUtil().getCountryCodeByName(country2);
            } else {
                countryCode1 = getCountryUtil().getCountryByCode(country1);
                countryCode2 = getCountryUtil().getCountryByCode(country2);
            }
            if (countryCode1 != null && countryCode2 != null) {
                result.setCountry(countryCode1); //TODO: for now we only support 1 country
                log.debug("Country: {} - {} / {} - {}",
                        new Object[] { countryCode1, country1, countryCode1, country1 });
            } else {
                log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
            }
        }
    }

    Cell wcaIdCell1 = row.getCell(3);
    Cell wcaIdCell2 = row.getCell(3 + 3);
    if ((wcaIdCell1 != null && wcaIdCell1.getCellType() != Cell.CELL_TYPE_BLANK)
            && (wcaIdCell2 != null && wcaIdCell2.getCellType() != Cell.CELL_TYPE_BLANK)) {
        String wcaId1 = wcaIdCell1.getStringCellValue();
        String wcaId2 = wcaIdCell2.getStringCellValue();
        if (wcaId1 != null && wcaId2 != null) {
            wcaId1 = wcaId1.trim();
            wcaId2 = wcaId2.trim();
            if (wcaId1.length() == 10 && wcaId2.length() == 10) {
                Matcher m1 = wcaIdPattern.matcher(wcaId1);
                Matcher m2 = wcaIdPattern.matcher(wcaId2);
                if (m1.find() && m2.find()) {
                    result.setWcaId(wcaId1); // FIXME: for now only 1 wcaId are supported 
                    log.debug("WCA Id: {} / {}", wcaId1, wcaId2);
                } else {
                    log.warn("[{}] Invalid wcaId format: {} / {}",
                            new Object[] { row.getSheet().getSheetName(), wcaId1, wcaId2 });
                }
            } else {
                log.warn("[{}] Entered WCA id has wrong length. Expected: 10, Was: {} / {}. Row: {}",
                        new Object[] { row.getSheet().getSheetName(), wcaId1.length(), wcaId2.length(),
                                row.getRowNum() + 1 });
            }
        }
    }
}

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static boolean cellIsBold(Cell cell) {
    if (cell == null)
        return false;

    Row row = cell.getRow();
    Sheet sheet = row.getSheet();
    Workbook workbook = sheet.getWorkbook();
    Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    if (font.getBoldweight() == Font.BOLDWEIGHT_BOLD)
        return true;

    return false;
}

From source file:org.mifos.dmt.excel.columnValidator.ColumnStructure.java

License:Open Source License

public Workbook processSheetStructure() throws DMTException {
    ArrayList<String> sheetsToBeProcessed = getSheetsToBeProcessed();
    Iterator<String> itr = sheetsToBeProcessed.iterator();
    while (itr.hasNext()) {
        sheetPass = true;/*from   w w  w . j av  a2 s.  c  om*/
        String sheetName = (String) itr.next();
        Sheet baseComparisonSheet = baseworkbook.getSheet(sheetName);
        Row baseComparisonRow = baseComparisonSheet.getRow(0);
        Sheet compareToSheet = workbook.getSheet(sheetName);
        Row comparisonRow = compareToSheet.getRow(0);
        short lastCell = comparisonRow.getLastCellNum();
        int j = 0;
        for (short i = 0; i <= lastCell - 1; i++, j++) {
            if (!baseComparisonRow.getCell(j).toString().equals(comparisonRow.getCell(j).toString())) {
                sheetPass = false;
                logger.error("Following Column value for the sheet " + comparisonRow.getSheet().getSheetName()
                        + " does not match with base template " + comparisonRow.getCell(j).toString());
            }
        }

        if (!sheetPass) {
            logger.error("Failed column validations for the sheet");
            throw new DMTException("Failed column validations for the sheet");
        }
    }
    logger.info("Excel sheet passed structural validations");
    return workbook;

}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
private int createResultCellsForFormat(HSSFSheet sheet, Row row, Row tRow, String nameIndexPrefix,
        HashMap<String, String> cellNames, WorksheetViewDO wVDO, WorksheetAnalysisViewDO waVDO,
        ArrayList<WorksheetResultViewDO> wrList, boolean isEditable,
        HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap) {
    int c, i, r;// ww w. j  a va 2 s . c om
    String cellNameIndex, name;
    ArrayList<AnalyteParameterViewDO> anaParams, apList;
    DecimalFormat df;
    HashMap<Integer, ArrayList<AnalyteParameterViewDO>> pMap;
    Cell cell, tCell;
    Name cellName;
    AnalyteParameterViewDO apVDO;

    df = new DecimalFormat();
    df.setGroupingUsed(false);
    df.setMaximumFractionDigits(10);

    i = 0;
    r = row.getRowNum();
    for (WorksheetResultViewDO wrVDO : wrList) {
        if (i != 0) {
            row = sheet.createRow(r);
            for (c = 0; c < 7; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(styles.get("row_no_edit"));
            }
        }

        cellNameIndex = nameIndexPrefix + "." + i;

        // analyte
        cell = row.createCell(7);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getAnalyteName());

        // reportable
        cell = row.createCell(8);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getIsReportable());
        cellName = sheet.getWorkbook().createName();
        cellName.setNameName("analyte_reportable." + cellNameIndex);
        cellName.setRefersToFormula(
                "Worksheet!$" + CellReference.convertNumToColString(8) + "$" + (row.getRowNum() + 1));

        apVDO = null;
        for (c = 9; c < tRow.getLastCellNum() && c < 39; c++) {
            tCell = tRow.getCell(c);

            cell = row.createCell(c);
            if (isEditable)
                cell.setCellStyle(tCell.getCellStyle());
            else
                cell.setCellStyle(styles.get("row_no_edit"));
            name = cellNames.get(
                    sheet.getSheetName() + "!$" + CellReference.convertNumToColString(tCell.getColumnIndex())
                            + "$" + (tCell.getRowIndex() + 1));
            if (name != null) {
                cellName = row.getSheet().getWorkbook().createName();
                cellName.setNameName(name + "." + cellNameIndex);
                cellName.setRefersToFormula(
                        sheet.getSheetName() + "!$" + CellReference.convertNumToColString(cell.getColumnIndex())
                                + "$" + (row.getRowNum() + 1));
            }
            if (tCell.getCellType() == Cell.CELL_TYPE_FORMULA && tCell.getCellFormula() != null) {
                cell.setCellFormula(tCell.getCellFormula());
            } else {
                setCellValue(cell, wrVDO.getValueAt(c - 9));
            }
            if ("p1".equals(name) || "p2".equals(name) || "p3".equals(name) || "p_1".equals(name)
                    || "p_2".equals(name) || "p_3".equals(name)) {
                if (wrVDO.getValueAt(c - 9) == null) {
                    pMap = apMap.get("T" + waVDO.getTestId());
                    if (pMap == null) {
                        pMap = new HashMap<Integer, ArrayList<AnalyteParameterViewDO>>();
                        apMap.put("T" + waVDO.getTestId(), pMap);
                        try {
                            anaParams = analyteParameter.fetchByActiveDate(waVDO.getTestId(),
                                    Constants.table().TEST, wVDO.getCreatedDate().getDate());
                            for (AnalyteParameterViewDO anaParam : anaParams) {
                                apList = pMap.get(anaParam.getAnalyteId());
                                if (apList == null) {
                                    apList = new ArrayList<AnalyteParameterViewDO>();
                                    pMap.put(anaParam.getAnalyteId(), apList);
                                }
                                apList.add(anaParam);
                            }
                        } catch (NotFoundException nfE) {
                            continue;
                        } catch (Exception anyE) {
                            log.log(Level.SEVERE,
                                    "Error retrieving analyte parameters for an analysis on worksheet.", anyE);
                            continue;
                        }
                    }

                    apList = pMap.get(wrVDO.getAnalyteId());
                    apVDO = null;
                    if (apList != null && apList.size() > 0) {
                        for (AnalyteParameterViewDO ap : apList) {
                            if (ap.getUnitOfMeasureId() == null
                                    || ap.getUnitOfMeasureId().equals(waVDO.getUnitOfMeasureId())) {
                                if (ap.getUnitOfMeasureId() != null) {
                                    apVDO = ap;
                                    break;
                                } else if (apVDO == null) {
                                    apVDO = ap;
                                }
                            }
                        }
                    }
                    if (apVDO != null) {
                        if (("p1".equals(name) || "p_1".equals(name)) && apVDO.getP1() != null) {
                            setCellValue(cell, df.format(apVDO.getP1()));
                        } else if (("p2".equals(name) || "p_2".equals(name)) && apVDO.getP2() != null) {
                            setCellValue(cell, df.format(apVDO.getP2()));
                        } else if (("p3".equals(name) || "p_3".equals(name)) && apVDO.getP3() != null) {
                            setCellValue(cell, df.format(apVDO.getP3()));
                        }
                    }
                }
            }
        }

        i++;
        r++;
    }

    return r;
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
private int createQcResultCellsForFormat(HSSFSheet sheet, Row row, Row tRow, String nameIndexPrefix,
        HashMap<String, String> cellNames, WorksheetViewDO wVDO, Integer qcId,
        ArrayList<WorksheetQcResultViewDO> wqrList,
        HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap) {
    int c, i, r;/*  w  ww .j  av a 2s  .  c  om*/
    String cellNameIndex, name;
    ArrayList<AnalyteParameterViewDO> anaParams, apList;
    DecimalFormat df;
    HashMap<Integer, ArrayList<AnalyteParameterViewDO>> pMap;
    Cell cell, tCell;
    Name cellName;
    AnalyteParameterViewDO apVDO;

    df = new DecimalFormat();
    df.setGroupingUsed(false);
    df.setMaximumFractionDigits(10);

    i = 0;
    r = row.getRowNum();
    for (WorksheetQcResultViewDO wqrVDO : wqrList) {
        if (i != 0) {
            row = sheet.createRow(r);
            for (c = 0; c < 7; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(styles.get("row_no_edit"));
            }
        }

        cellNameIndex = nameIndexPrefix + "." + i;

        // analyte
        cell = row.createCell(7);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wqrVDO.getAnalyteName());

        // reportable
        cell = row.createCell(8);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue("N");

        apVDO = null;
        for (c = 9; c < tRow.getLastCellNum() && c < 39; c++) {
            tCell = tRow.getCell(c);

            cell = row.createCell(c);
            cell.setCellStyle(tCell.getCellStyle());
            name = cellNames.get(
                    sheet.getSheetName() + "!$" + CellReference.convertNumToColString(tCell.getColumnIndex())
                            + "$" + (tCell.getRowIndex() + 1));
            if (name != null) {
                cellName = row.getSheet().getWorkbook().createName();
                cellName.setNameName(name + "." + cellNameIndex);
                cellName.setRefersToFormula(
                        sheet.getSheetName() + "!$" + CellReference.convertNumToColString(cell.getColumnIndex())
                                + "$" + (row.getRowNum() + 1));
            }
            if (tCell.getCellType() == Cell.CELL_TYPE_FORMULA && tCell.getCellFormula() != null) {
                cell.setCellFormula(tCell.getCellFormula());
            } else {
                setCellValue(cell, wqrVDO.getValueAt(c - 9));
            }
            if ("p1".equals(name) || "p2".equals(name) || "p3".equals(name) || "p_1".equals(name)
                    || "p_2".equals(name) || "p_3".equals(name)) {
                if (wqrVDO.getValueAt(c - 9) == null) {
                    pMap = apMap.get("Q" + qcId);
                    if (pMap == null) {
                        pMap = new HashMap<Integer, ArrayList<AnalyteParameterViewDO>>();
                        apMap.put("Q" + qcId, pMap);
                        try {
                            anaParams = analyteParameter.fetchByActiveDate(qcId, Constants.table().QC,
                                    wVDO.getCreatedDate().getDate());
                            for (AnalyteParameterViewDO anaParam : anaParams) {
                                apList = pMap.get(anaParam.getAnalyteId());
                                if (apList == null) {
                                    apList = new ArrayList<AnalyteParameterViewDO>();
                                    pMap.put(anaParam.getAnalyteId(), apList);
                                }
                                apList.add(anaParam);
                            }
                        } catch (NotFoundException nfE) {
                            continue;
                        } catch (Exception anyE) {
                            log.log(Level.SEVERE, "Error retrieving analyte parameters for a qc on worksheet.",
                                    anyE);
                            continue;
                        }
                    }

                    apList = pMap.get(wqrVDO.getAnalyteId());
                    apVDO = null;
                    if (apList != null && apList.size() > 0)
                        apVDO = apList.get(0);
                    if (apVDO != null) {
                        if (("p1".equals(name) || "p_1".equals(name)) && apVDO.getP1() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP1()));
                        } else if (("p2".equals(name) || "p_2".equals(name)) && apVDO.getP2() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP2()));
                        } else if (("p3".equals(name) || "p_3".equals(name)) && apVDO.getP3() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP3()));
                        }
                    }
                }
            }
        }

        i++;
        r++;
    }

    return r;
}