List of usage examples for org.apache.poi.ss.usermodel Cell getRowIndex
int getRowIndex();
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
private Cell fill(final Workbook workbook, Cell tmp, final CellDefinition cellDefinition, boolean setType) { final String type = cellDefinition.getType(); if (type.equalsIgnoreCase("string")) { if (setType) tmp.setCellType(Cell.CELL_TYPE_STRING); tmp.setCellValue(cellDefinition.value); } else if (type.equalsIgnoreCase("number")) { if (setType) { tmp.setCellType(Cell.CELL_TYPE_NUMERIC); tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value)); }/*w ww . j a va 2 s.co m*/ try { tmp.setCellValue(Double.parseDouble(cellDefinition.value.split("@@")[0])); } catch (NumberFormatException e) { throw new RuntimeException(String.format("Could not parse value \"%s\" for numeric cell %dx%d!", cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex())); } } else if (type.equalsIgnoreCase("date") || type.equalsIgnoreCase("datetime")) { if (setType) { tmp.setCellType(Cell.CELL_TYPE_NUMERIC); tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value)); } try { tmp.setCellValue(getDateFormatSql(type).parse(cellDefinition.value)); } catch (ParseException e) { throw new RuntimeException( String.format("Could not parse value \"%s\" for date/datetime cell %dx%d!", cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex())); } } else if (type.equalsIgnoreCase("formula")) { if (setType) tmp.setCellType(Cell.CELL_TYPE_FORMULA); tmp.setCellFormula(cellDefinition.value); } else throw new RuntimeException("Invalid type " + type); return tmp; }
From source file:de.ingrid.iplug.excel.service.SheetsService.java
License:EUPL
/** * Create sheets.//from ww w .j av a 2s . c om * * @param inputStream * @return Created sheets. * @throws IOException */ public static Sheets createSheets(final InputStream inputStream) throws IOException { // sheets final Sheets sheets = new Sheets(); // create workbook final Workbook workbook = new HSSFWorkbook(inputStream); final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum); // ingrid sheet final Sheet sheet = new Sheet(); sheet.setSheetIndex(sheetNum); sheets.addSheet(sheet); final Values values = new Values(); sheet.setValues(values); for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) { boolean hasValues = false; final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>(); for (final Cell poiCell : poiRow) { Comparable<? extends Object> value = null; switch (poiCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { value = getFormattedDateString(poiCell); } else { value = new Double(poiCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = poiCell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: value = calculateFormula(poiCell, eval); break; default: value = ""; break; } // trim strings if (value instanceof String) { value = ((String) value).trim(); } // only add if at least one value does exist in row if (!value.equals("")) { hasValues = true; // ingrid column if (sheet.getColumn(poiCell.getColumnIndex()) == null) { final Column column = new Column(poiCell.getColumnIndex()); sheet.addColumn(column); } } // ingrid point and value final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex()); valuesInCell.put(point, value); } // ingrid row // ! only add if at least one value does exist if (hasValues) { final Row row = new Row(poiRow.getRowNum()); sheet.addRow(row); for (final Point point : valuesInCell.keySet()) { // if (sheet.getColumn(point.getX()) != null) { values.addValue(point, valuesInCell.get(point)); } } } } } return sheets; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java
License:Open Source License
/** * Returns a CellReference containing the cell's sheet name, as opposed to the standard * {@link CellReference#CellReference(Cell)} constructor. * @param cell//from w w w . j ava 2s. c o m * Cell to create a CellReference from * @return The CellReference including the sheet name */ public static CellReference getFullCellReference(Cell cell) { return new CellReference(cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex(), true, true); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java
License:Open Source License
/** * Returns a {@link Cell}'s coordinates//from ww w. ja v a 2 s.c o m * @param cell * the given {@link Cell} * @return String representing the cell's coordinates */ public static String getCellRef(Cell cell) { if (cell == null) { return "undef"; } CellReference cellref = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); return cellref.formatAsString(); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java
License:Open Source License
/** * Returns a {@link Cell}'s row number/*from w ww. j a v a2s.c o m*/ * @param cell * the given {@link Cell} * @return The cell's row number as String */ public static String getCellRow(Cell cell) { if (cell == null) { return "undef"; } CellReference cellref = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); return String.valueOf(cellref.getRow() + 1); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.sheets.model.InformationSystemInterfaceSheetImporter.java
License:Open Source License
@Override protected boolean isValidRow(Map<String, Cell> buildingBlockRowData) { String releaseA = getCellByKey(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_A), buildingBlockRowData);/* w w w .j av a 2 s . c o m*/ String releaseB = getCellByKey(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_B), buildingBlockRowData); Cell releaseANameCell = buildingBlockRowData .get(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_A)); String releaseANameCellCoords = ExcelImportUtilities.getCellRef(releaseANameCell); Cell releaseBNameCell = buildingBlockRowData .get(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_B)); String releaseBNameCellCoords = ExcelImportUtilities.getCellRef(releaseBNameCell); if (releaseANameCell == null && releaseBNameCell != null) { CellReference cellRelA = new CellReference(releaseBNameCell.getRowIndex(), releaseBNameCell.getColumnIndex() - 1); releaseANameCellCoords = cellRelA.formatAsString(); } if (releaseBNameCell == null && releaseANameCell != null) { CellReference cellRelB = new CellReference(releaseANameCell.getRowIndex(), releaseANameCell.getColumnIndex() + 1); releaseBNameCellCoords = cellRelB.formatAsString(); } if (!isNameSet(releaseA)) { getProcessingLog().warn("Release A in cell [{0}] is empty: not importing", releaseANameCellCoords); return false; } if (!isNameSet(releaseB)) { getProcessingLog().warn("Release B in cell [{0}] is empty: not importing", releaseBNameCellCoords); return false; } return true; }
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)); }//from w w w . ja v a2s . c o m } adjustSheetColumnWidths(); }
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();//from w ww . j av a 2 s . c o 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.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
public void generateExcelFile() { Row row;/* www . j a v a 2s . c om*/ 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 * /* ww w . j a va 2 s. co 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 + "'!"); }