List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.importer.TimeseriesExcelImporter.java
License:Open Source License
private String getValue(Row row, boolean isNumberAt) { Cell valueCell = row.getCell(VALUE_COL_NO); if (ExcelUtils.isEmptyCell(valueCell)) { logError(ExcelUtils.getFullCellReference(row.getSheet(), new CellReference(row.getRowNum(), VALUE_COL_NO)), "No attribute value found."); return null; }/*from w ww . j a v a2 s . c om*/ if (isNumberAt) { Object value = ExcelUtils.getCellValue(valueCell, false); if (!(value instanceof Double)) { logError(ExcelUtils.getFullCellReference(valueCell), "Non-number value for number attribute type found."); return null; } } return ExcelUtils.getStringCellValue(valueCell); }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate a {@link WeightedSumFactorAggregation}. */ private void importWeightedSumFactorAggregation(Sheet sheet, WeightedSumFactorAggregation factorAggregation, EAttribute attribute, int column) { for (Iterator<Row> j = sheet.rowIterator(); j.hasNext();) { Row row = j.next(); if (row.getRowNum() < 2) { continue; }/*from www. j a va2 s .c om*/ try { Object value = row.getCell(column).getNumericCellValue(); String factorName = row.getCell(0).getStringCellValue(); FactorRanking ranking = getFactorRanking(factorAggregation, factorName); if (attribute.getEAttributeType() == EcorePackage.eINSTANCE.getEInt()) { value = ((Double) value).intValue(); } ranking.eSet(attribute, value); } catch (RuntimeException e) { // ignore } } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate a {@link WeightedSumMultiMeasureEvaluation}. */ private void importWeightedSumMultiMeasureEvaluation(Sheet sheet, WeightedSumMultiMeasureEvaluation multiMeasureEvaluation, EAttribute attribute, int column) { for (Iterator<Row> j = sheet.rowIterator(); j.hasNext();) { Row row = j.next(); if (row.getRowNum() < 2) { continue; }// www . j a v a 2s.c om try { Object value = row.getCell(column).getNumericCellValue(); String measureName = row.getCell(0).getStringCellValue(); MeasureRanking ranking = getMeasureRanking(multiMeasureEvaluation, measureName); if (attribute.getEAttributeType() == EcorePackage.eINSTANCE.getEInt()) { value = ((Double) value).intValue(); } ranking.eSet(attribute, value); } catch (RuntimeException e) { // ignore } } }
From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
public void generateExcelFile() { Row row;/* w ww . j a v a 2 s . co m*/ Cell cell; int yOffset = 1; File workingDirectory = new File( (workingDirectoryPath.isEmpty() ? "" : (workingDirectoryPath + File.separatorChar)) + evaluationUID); if (!workingDirectory.exists()) { workingDirectory.mkdir(); } File file = new File(workingDirectory, "auswertung.xls"); try { FileOutputStream out = new FileOutputStream(file); Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Evaluation"); // configure cell styles configureCellStyles(wb); /* * ********************************** * begin formatting document * ********************************** * */ //construct first row of infopanel yOffset = constructInfoPanelRow("Lehrveranstaltung", subject, numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.topLeftCorner, InfoPanelBorderStyles.top, InfoPanelBorderStyles.topRightCorner); //construct second row of infopanel yOffset = constructInfoPanelRow("Semester", semesterType == SemesterType.WINTER ? "Winter" : "Sommer", numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct third row of infopanel StringBuilder tutors = new StringBuilder(); for (int i = 0; i < this.tutors.size(); i++) { if (i + 1 < this.tutors.size()) { tutors.append(this.tutors.get(i)).append(", "); } else { tutors.append(this.tutors.get(i)); } } yOffset = constructInfoPanelRow("Lehrende(r)", tutors.toString(), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fourth row of infopanel yOffset = constructInfoPanelRow("Datum der Befragung", dateOfEvaluation.toString("dd.MM.yy HH:mm"), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fifth row of infopanel yOffset = constructInfoPanelRow("Anzahl der Teilnehmer", Integer.toString(numberStudentsAll), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct sixth row of infopanel () last yOffset = constructInfoPanelRow("Anzahl der ausgefllten Fragebgen", Integer.toString(numberStudentsVoted), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.bottomLeftCorner, InfoPanelBorderStyles.bottom, InfoPanelBorderStyles.bottomRightCorner); //begin construction of evaluationPanel yOffset++; row = sheet.createRow(yOffset); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellValue("Frage"); cell.setCellStyle(headerStyle); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellValue("MW"); cell.setCellStyle(headerStyle); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellValue("Ifd NR."); cell.setCellStyle(headerStyle); cell = row.createCell(4); cell.setCellStyle(commonStyle); // add count of valid evaluations (how many students voted) (horizontal) for (int i = 0; i < numberStudentsVoted; i++) { cell = row.createCell(i + 5); cell.setCellValue(i + 1); sheet.setColumnWidth(cell.getColumnIndex(), 4 * 256); cell.setCellStyle(headerStyle); } // get letter of last student column CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); String endCellName = cellReference.getCellRefParts()[2]; Row headRow = row; for (int i = 1; i < mcQuestionTexts.size() + 1; i++) { //add number of questions row = sheet.createRow(i + yOffset); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(i); cell.setCellStyle(commonStyle); //add average formula cell = row.createCell(2, Cell.CELL_TYPE_FORMULA); //formlua works with blanks, empty strings and negative values String formula = "SUMPRODUCT(ABS(N(+F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))/COUNT(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")"; //String averageFormula = "AVERAGE(IF(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + "<>\"\", ABS(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))"; cell.setCellFormula(formula); cell.setCellStyle(commonStyle); //fill blank cells cell = row.createCell(3); cell.setCellStyle(commonStyle); //add question texts sheet.setColumnWidth(4, findLongestString(mcQuestionTexts) * 256 * (wb.getFontAt(questionStyle.getFontIndex()).getFontHeightInPoints()) / 10); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellValue(mcQuestionTexts.get(i - 1)); cell.setCellStyle(questionStyle); } //add student votes for (int i = 0; i < studentVotes.size(); i++) { Vote vote = studentVotes.get(i); for (int k = 0; k < mcQuestionTexts.size(); k++) { row = sheet.getRow(headRow.getRowNum() + 1 + k); cell = row.createCell(5 + i); for (MCAnswer answer : vote.getMcAnswers()) { //if question of inner loop equals question of outer loop we found // the correct question for this cell if (answer.getQuestion().getText().equals(mcQuestionTexts.get(k))) { Choice choice = answer.getChoice(); if (choice != null && choice.getGrade() != 0) { cell = colorizeCell(cell, wb, choice.getGrade()); cell.setCellValue(answer.getChoice().getGrade()); } else { cell = colorizeCell(cell, wb, -1); cell.setCellValue(""); } } } } } // include textual answers createTextualAnswers(studentVotes, textualQuestionTexts, sheet, wb); wb.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }
From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
private void createTextualAnswers(@NonNull List<Vote> answers, @NonNull List<String> questionTexts, Sheet sheet, Workbook wb) {/*from w w w .j a v a 2s . co m*/ Row row; Cell cell; row = sheet.createRow(sheet.getLastRowNum() + 2); cell = row.createCell(1); CellStyle helpStyle = wb.createCellStyle(); helpStyle.cloneStyleFrom(headerStyle); helpStyle.setBorderBottom(CellStyle.BORDER_NONE); helpStyle.setBorderTop(CellStyle.BORDER_NONE); helpStyle.setBorderLeft(CellStyle.BORDER_NONE); helpStyle.setBorderRight(CellStyle.BORDER_NONE); cell.setCellValue("Kommentare"); cell.setCellStyle(helpStyle); //TODO used to determine style for current line -> its stupid. Think of something better int styleCounter = 0; for (String textualQuestion : questionTexts) { row = sheet.createRow(sheet.getLastRowNum() + 3); cell = row.createCell(1); cell.setCellValue(textualQuestion); setTextQuestionStyle(cell, styleCounter, true); //colorize horizontal neighbour cells of headline for (int i = 2; i < 5; i++) { cell = row.createCell(i); setTextQuestionStyle(cell, styleCounter, false); } int rowNum = sheet.getLastRowNum(); int counter = 1; for (String comment : aggregateTextAnswers(answers, textualQuestion)) { row = sheet.createRow(rowNum + 1); cell = row.createCell(1, Cell.CELL_TYPE_STRING); // introduces line breaks in long comments ArrayList<String> commentChunks = splitComment(comment); StringBuilder formattedComment = new StringBuilder(); formattedComment.append(Integer.toString(counter)); formattedComment.append(": "); int chunkCounter = 0; for (String chunk : commentChunks) { formattedComment.append(chunk); if ((chunkCounter + 1) < commentChunks.size()) { formattedComment.append(System.lineSeparator()); } chunkCounter++; } cell.setCellValue(formattedComment.toString()); CellStyle style = setTextQuestionStyle(cell, styleCounter, false); // increase height of row based on font size, number of lines and line spacing // the origin of 140 % -> http://superuser.com/questions/337181/how-many-pts-is-1-5-line-spacing-in-microsoft-word-2007 float pointsPerLine = (wb.getFontAt(style.getFontIndex()).getFontHeightInPoints() * 140) / 100; row.setHeightInPoints(pointsPerLine * commentChunks.size()); //colorize horizontal neighbour cells of comment for (int i = 2; i < 17; i++) { cell = row.createCell(i); setTextQuestionStyle(cell, styleCounter, false); } sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 1, 17)); rowNum++; counter++; } styleCounter++; } }
From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java
License:Apache License
/** * Interpretation method for next function * //from w w w . ja va 2s . c o m * @param workBook * the workbook * @param row * the row * @return the index of the next row satisfying the given property or in * maximum the last row * @throws JeXcException * thrown if operation fails */ private Object interpretNextFunction(Workbook workBook, Row row) throws JeXcException { ExqlExpression ex = getExpressions().get(0); Sheet sheet = row.getSheet(); for (int i = row.getRowNum() + 1; i < sheet.getLastRowNum() + 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 maximum rows */ return sheet.getLastRowNum() + 1; }
From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java
License:Apache License
/** * Interpretation method for previous function * //w w w. j a va2 s . c om * @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:de.topicmapslab.jexc.eXql.grammar.expression.SelectExpression.java
License:Apache License
/** * {@inheritDoc}//from w w w. ja v a 2 s . co m */ @SuppressWarnings("unchecked") public ExqlResultSet<?> interpret(Workbook workBook, Object... input) throws JeXcException { if (input.length != 1 && input[0] instanceof Collection<?>) { throw new JeXcException("Number of arguments is invalid, expect one collection!"); } Collection<Row> rows = (Collection<Row>) input[0]; List<List<Object>> results = new LinkedList<List<Object>>(); List<Integer> rowIndexes = new LinkedList<Integer>(); for (Row row : rows) { if (row == null) { continue; } List<Object> object = new LinkedList<Object>(); boolean valid = true; for (ExqlExpression ex : getExpressions()) { Object result = ex.interpret(workBook, row); /* * avoid null values */ if (result == null) { valid = false; break; } object.add(result); } if (valid) { /* * filter duplicates if DISTINCT is used */ if (distinct && results.contains(object)) { continue; } results.add(object); rowIndexes.add(row.getRowNum()); } } return new ExqlResultSetImpl(rowIndexes, results); }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected List<Competitor> parseCompetitors(Workbook workBook) throws IllegalStateException { List<Competitor> competitors = new CopyOnWriteArrayList<Competitor>(); Sheet sheet = workBook.getSheet(SHEET_TYPE_REGISTRATION); if (isValidRegistrationSheet(sheet)) { parseEventNames(sheet);/*from w w w. j a va 2 s. co m*/ Row firstRow = sheet.getRow(3); // first row with competitor data if (firstRow != null) { Cell cell = firstRow.getCell(1); // first cell with competitor data if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content log.debug("Parsing: {}", sheet.getSheetName()); for (Row row : sheet) { if (row.getRowNum() > 2) { Competitor competitor = parseCompetitorRow(row); if (competitor != null) { if (competitor.getRegisteredEvents().hasSignedUp()) { competitors.add(competitor); } else { log.warn("[{}] No events registered for: {}", sheet.getSheetName(), competitor.getFirstname()); } } } } } } } return competitors; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
/** * @param row/*w w 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; } }