List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesIntroSheetGenerator.java
License:Open Source License
@Override protected void createSummary() { Sheet introSheet = getIntroductionSheet(); int rowNum = SUMMARY_ROW; Cell headerCell = introSheet.createRow(rowNum++).createCell(SUMMARY_COL); headerCell.setCellValue(MessageAccess.getStringOrNull("excel.export.timeseries.intro.description")); headerCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HEADER)); introSheet.addMergedRegion(new CellRangeAddress(headerCell.getRowIndex(), headerCell.getRowIndex(), headerCell.getColumnIndex(), headerCell.getColumnIndex() + 1)); CreationHelper createHelper = getWorkbook().getCreationHelper(); // TODO group the summary by building block type with subheaders for (int i = 0; i < getWorkbook().getNumberOfSheets(); i++) { Sheet sheet = getWorkbook().getSheetAt(i); String sheetName = sheet.getSheetName(); if (!introSheet.equals(sheet)) { Row summaryRow = introSheet.createRow(rowNum++); Cell hyperlinkCell = summaryRow.createCell(SUMMARY_COL); hyperlinkCell.setCellValue(sheetName); summaryRow.createCell(SUMMARY_COL + 1) .setCellValue(ExcelUtils.getStringCellValue(sheet.getRow(0).getCell(0))); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link.setAddress("'" + sheetName + "'!A1"); hyperlinkCell.setHyperlink(link); hyperlinkCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HYPERLINK)); }/* www.j av a2 s. c o m*/ } adjustSheetColumnWidths(); }
From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java
License:Apache License
private Double getDoubleCellValue(Cell cell) throws Exception { Double value = null;// ww w . java 2 s. com if (cell != null) { CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.FORMULA) { try { String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator()); if (s != null && s.trim().isEmpty() == false) { Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim()); value = n.doubleValue(); } } catch (Exception e) { if (useCachedValuesForFailedEvaluations) { cellType = cell.getCachedFormulaResultTypeEnum(); if (cellType == CellType.STRING) { String s = cell.getStringCellValue(); if (s != null && s.trim().isEmpty() == false) { Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim()); value = n.doubleValue(); } } else if (cellType == CellType.NUMERIC) { value = cell.getNumericCellValue(); } } else { throw e; } } } else if (cellType == CellType.STRING) { String s = cell.getStringCellValue(); if (s != null && s.trim().isEmpty() == false) { Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim()); value = n.doubleValue(); } } else if (cellType == CellType.NUMERIC) { value = cell.getNumericCellValue(); } } return value; }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setCellComment(Cell cell, String comment) { if (comment == null || comment.trim().isEmpty()) { cell.removeCellComment();// ww w . j av a 2 s . co m } else { Comment c = cell.getCellComment(); if (c == null) { ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setRow1(cell.getRowIndex()); anchor.setRow2(cell.getRowIndex() + commentHeight); anchor.setCol1(cell.getColumnIndex() + 1); anchor.setCol2(cell.getColumnIndex() + commentWidth + 1); anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE); c = getDrawing().createCellComment(anchor); c.setVisible(false); if (commentAuthor != null) { c.setAuthor(commentAuthor); } cell.setCellComment(c); } RichTextString rts = creationHelper.createRichTextString(comment); c.setString(rts); } }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setupStyle(Cell cell, int row) { CellStyle style = cell.getCellStyle(); // cell has its own style and not the default style if (reuseExistingStyles) { // we have to reuse the existing style if (reuseExistingStylesAlternating) { // we have to reuse the style from the even/odd row if (isFirstRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style oddRowColumnStyleMap.put(cell.getColumnIndex(), style); }/* w w w . j a v a 2 s. c o m*/ } else if (isSecondRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style evenRowColumnStyleMap.put(cell.getColumnIndex(), style); } } else if (isEvenDataRow(row)) { // reference to the previously memorized style for even rows CellStyle s = evenRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } else { // reference to the previously memorized style for even rows CellStyle s = oddRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } else { // we take the style from the last row if (isFirstRow(row)) { // memorize the style for reuse in all other rows if (style.getIndex() > 0) { // only if the cell does not use the default style columnStyleMap.put(cell.getColumnIndex(), style); } } else { // set the style from the previous row CellStyle s = columnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } } else { Short formatIndex = cellFormatMap.get(cell.getColumnIndex()); if (formatIndex != null) { if ((style.getIndex() == 0) || (style.getDataFormat() != formatIndex)) { // this is the default style or the current format differs from the given format // we need our own style for this style = columnStyleMap.get(cell.getColumnIndex()); if (style == null) { style = workbook.createCellStyle(); style.setDataFormat(formatIndex.shortValue()); columnStyleMap.put(cell.getColumnIndex(), style); } cell.setCellStyle(style); } } } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java
License:Apache License
/** Helper method to automatically set the width of all columns. */ private void autoSizeColumns(Sheet sheet) { for (Iterator<Cell> i = sheet.getRow(0).cellIterator(); i.hasNext();) { Cell cell = i.next(); sheet.autoSizeColumn(cell.getColumnIndex()); }//from w w w . j av a 2 s. c om }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate the {@link MeasureEvaluation}s. */ private void importMeasureEvaluationCalibration(Sheet sheet, IProgressMonitor monitor) { Row row = sheet.getRow(5);//w w w .j a v a 2s. c om for (Iterator<Cell> i = row.cellIterator(); i.hasNext();) { Cell cell = i.next(); int column = cell.getColumnIndex(); if (column < 1) { continue; } String id = cell.getStringCellValue(); EObject evaluation = getEvaluation(id); if (evaluation instanceof SingleMeasureEvaluation) { SingleMeasureEvaluation measureEvaluation = (SingleMeasureEvaluation) evaluation; importFunction(sheet, column, measureEvaluation); } else { WeightedSumMultiMeasureEvaluation measureEvaluation = (WeightedSumMultiMeasureEvaluation) evaluation; if (measureEvaluation == null) { System.err.println("Evaluation not found. Model and Excel file are inconsistent."); } else { String measureName = sheet.getRow(1).getCell(column).getStringCellValue(); MeasureRanking measureRanking = getMeasureRanking(measureEvaluation, measureName); importFunction(sheet, column, measureRanking); } } monitor.worked(1); } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate a {@link FactorAggregation}. */ private void importFactorAggregationCalibration(Sheet sheet, EAttribute attribute, IProgressMonitor monitor) { Row mainRow = sheet.getRow(0);//w w w . j ava2 s. com for (Iterator<Cell> i = mainRow.cellIterator(); i.hasNext();) { Cell cell = i.next(); int column = cell.getColumnIndex(); if (column < 1) { continue; } String id = cell.getStringCellValue(); EObject evaluation = getEvaluation(id); if (evaluation instanceof WeightedSumFactorAggregation) { WeightedSumFactorAggregation factorAggregation = (WeightedSumFactorAggregation) evaluation; importWeightedSumFactorAggregation(sheet, factorAggregation, attribute, column); } else { WeightedSumMultiMeasureEvaluation multiMeasureEvaluation = (WeightedSumMultiMeasureEvaluation) evaluation; importWeightedSumMultiMeasureEvaluation(sheet, multiMeasureEvaluation, attribute, column); } monitor.worked(1); } }
From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
public void generateExcelFile() { Row row;/*from w ww . j a va 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.topicmapslab.jexc.eXql.grammar.expression.ValueExpression.java
License:Apache License
/** * Returns the cell value represent by the given token * // w w w . j a va2s . c o m * @param cell * the cell to extract the values from cell * @param token * the token specifies the value to extract * @return the cell value * @throws JeXcException * thrown if cell value token is unknown */ public Object getCellValue(final Cell cell, final String token) throws JeXcException { if (VALUE.equalsIgnoreCase(token) || VALUE_STRING.equalsIgnoreCase(token)) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: Double d = cell.getNumericCellValue(); Long l = d.longValue(); /* * check if long value represents the same numeric value then * the double origin */ if (d.doubleValue() == l.longValue()) { return String.valueOf(l); } return String.valueOf(d); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_STRING: default: return cell.getStringCellValue(); } } else if (VALUE_DATE.equalsIgnoreCase(token)) { return cell.getDateCellValue(); } else if (VALUE_NUMERICAL.equalsIgnoreCase(token)) { return cell.getNumericCellValue(); } else if (STYLE_FOREGROUND.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? NULL : style.getFillForegroundColor(); } else if (STYLE_BACKGROUND.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? NULL : style.getFillBackgroundColor(); } else if (BORDER_TOP.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? 0 : style.getBorderTop(); } else if (BORDER_BOTTOM.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? 0 : style.getBorderBottom(); } else if (BORDER_LEFT.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? 0 : style.getBorderLeft(); } else if (BORDER_RIGHT.equalsIgnoreCase(token)) { CellStyle style = cell.getCellStyle(); return style == null ? 0 : style.getBorderRight(); } else if (ADDRESS.equalsIgnoreCase(token)) { StringBuilder builder = new StringBuilder(); builder.append(cell.getSheet().getSheetName()); builder.append(SLASH); builder.append(cell.getRow().getRowNum()); builder.append(COLON); builder.append(cell.getColumnIndex()); return builder.toString(); } else if (HEIGHT.equalsIgnoreCase(token)) { CellRangeAddress address = XlsxCellUtils.getCellRange(cell); if (address != null) { return address.getLastRow() - address.getFirstRow() + 1; } return 1; } else if (ROW.equalsIgnoreCase(token)) { return cell.getRowIndex(); } else if (COLUMN.equalsIgnoreCase(token)) { return cell.getColumnIndex(); } throw new JeXcException("Unknown constant '" + token + "'!"); }
From source file:de.topicmapslab.jexc.utility.XlsxCellUtils.java
License:Apache License
/** * Returns the cell range of the given cell * //from w w w .j av a2 s . c o m * @param cell * the cell * @return the cell range of merged region the cell is part of or * <code>null</code> */ public static CellRangeAddress getCellRange(Cell cell) { Sheet s = cell.getSheet(); for (int i = 0; i < s.getNumMergedRegions(); i++) { CellRangeAddress a = s.getMergedRegion(i); if (a.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { return a; } } return null; }