List of usage examples for org.apache.poi.common.usermodel HyperlinkType DOCUMENT
HyperlinkType DOCUMENT
To view the source code for org.apache.poi.common.usermodel HyperlinkType DOCUMENT.
Click Source Link
From source file:org.bbreak.excella.reports.tag.ColRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ?/*from w w w . ja v a 2 s. co m*/ checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo info = (ReportsParserInfo) data; ParamInfo paramInfo = info.getParamInfo(); ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); // ?? Object[] paramValues = null; try { // ??? String replaceParam = paramDef.get(PARAM_VALUE); // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(info.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil .getSheetValues(info.getReportBook(), propertyName, info.getReportParsers()).toArray(); } else { // ??? if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? if (hideDuplicate && paramValues.length > 1) { List<Object> paramValuesList = new ArrayList<Object>(); for (int i = 0; i <= paramValues.length - 1; i++) { // ????? if (!paramValuesList.contains(paramValues[i])) { paramValuesList.add(paramValues[i]); } else { paramValuesList.add(null); } } paramValues = paramValuesList.toArray(); } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ?? int unitColSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitColSize = curMergedAdress.getLastColumn() - curMergedAdress.getFirstColumn() + 1; // ?????? shiftNum = shiftNum * unitColSize; } } } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ?(repeatNum)??(unitColSize)?? shiftNum = repeatNum * unitColSize; // ?????? paramLength = repeatNum; } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellColIndex = tagCell.getColumnIndex() + unitColSize; for (int i = defaultFromCellColIndex; i < defaultToOverCellColIndex; i++) { Row targetCellRow = sheet.getRow(tagCell.getRowIndex()); cellList.add(new CellClone(targetCellRow.getCell(i))); } // ? if (shiftNum > 1) { // ?(????????) int shiftColSize = tagCell.getColumnIndex() + shiftNum - unitColSize - 1; // ??? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), tagCell.getRowIndex(), tagCell.getColumnIndex(), shiftColSize); PoiUtil.insertRangeRight(sheet, rangeAddress); // int tagCellWidth = sheet.getColumnWidth(tagCell.getColumnIndex()); for (int i = tagCell.getColumnIndex() + 1; i <= shiftColSize; i++) { int colWidth = sheet.getColumnWidth(i); if (colWidth < tagCellWidth) { // ?? ??????? // ?????? sheet.setColumnWidth(i, tagCellWidth); } } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(info.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int colIndex = 0; colIndex < shiftNum; colIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex()); if (row == null) { // ???? // ? // (??)?????????? // ??null???(RowCreate?)??????????? row = sheet.createRow(tagCell.getRowIndex()); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex() + colIndex); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex() + colIndex); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = colIndex % unitColSize; // ????????? boolean skipCol = false; if (cellIndex != 0) { skipCol = true; } else { valueIndex++; } // // ? PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipCol && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipCol && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // ?? // ?????????????????? if (!skipCol && unitColSize > 1 && paramLength > valueIndex + 1) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex() + unitColSize - 1); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = value; } // ??????? if (unitColSize == 1) { beforeValue = value; } } parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex()); // ?? parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex() + unitColSize - 1); parsedReportInfo.setRowIndex(tagCell.getRowIndex()); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex() + shiftNum - 1); parsedReportInfo.setParsedObject(resultValues); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.tag.RowRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ?//ww w . j a v a 2 s . c o m checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; // ? Object[] paramValues = null; try { // ? boolean rowShift = false; if (paramDef.containsKey(PARAM_ROW_SHIFT)) { rowShift = Boolean.valueOf(paramDef.get(PARAM_ROW_SHIFT)); } // ?? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // ? Integer breakNum = null; if (paramDef.containsKey(PARAM_BREAK_NUM)) { breakNum = Integer.valueOf(paramDef.get(PARAM_BREAK_NUM)); } // boolean changeBreak = false; if (paramDef.containsKey(PARAM_CHANGE_BREAK)) { changeBreak = Boolean.valueOf(paramDef.get(PARAM_CHANGE_BREAK)); } // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? String replaceParam = paramDef.get(PARAM_VALUE); // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil.getSheetValues(reportsParserInfo.getReportBook(), propertyName, reportsParserInfo.getReportParsers()).toArray(); } else { // ??? ParamInfo paramInfo = reportsParserInfo.getParamInfo(); if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ??? int unitRowSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitRowSize = curMergedAdress.getLastRow() - curMergedAdress.getFirstRow() + 1; // ?????? shiftNum = shiftNum * unitRowSize; } } } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellRowIndex = tagCell.getRowIndex() + unitRowSize; for (int i = defaultFromCellRowIndex; i < defaultToOverCellRowIndex; i++) { Row targetCellRow = sheet.getRow(i); cellList.add(new CellClone(targetCellRow.getCell(tagCell.getColumnIndex()))); } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ????? shiftNum = repeatNum * unitRowSize; // ?????? paramLength = repeatNum; } // ? if (shiftNum > 1) { // ?(????????) int shiftRowSize = tagCell.getRowIndex() + shiftNum - unitRowSize - 1; if (!rowShift) { // ????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), shiftRowSize, tagCell.getColumnIndex(), tagCell.getColumnIndex()); PoiUtil.insertRangeDown(sheet, rangeAddress); } else { // ????? // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex() + unitRowSize, tagCell.getRowIndex() + shiftNum - 1, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); // int shiftStartRow = tagCell.getRowIndex() + 1; // int shiftEndRow = sheet.getLastRowNum(); // if ( shiftEndRow < shiftStartRow) { // // ???????????????? // // ???????????? // shiftEndRow = shiftStartRow + 1; // } // sheet.shiftRows( shiftStartRow, shiftEndRow, shiftNum - unitRowSize); } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int rowIndex = 0; rowIndex < shiftNum; rowIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex() + rowIndex); if (row == null) { row = sheet.createRow(tagCell.getRowIndex() + rowIndex); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex()); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex()); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = rowIndex % unitRowSize; // ????????? boolean skipRow = false; if (cellIndex != 0) { skipRow = true; } else { valueIndex++; } // (?) PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipRow && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipRow && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // if (!skipRow) { if (breakNum != null && valueIndex != 0 && valueIndex % breakNum == 0) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } if (changeBreak && valueIndex != 0 && !duplicateValue) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } } // ?? // ?????????? // ???????????? if (!skipRow && unitRowSize > 1) { // ??? boolean mergedRegionFlag = false; if (rowShift && valueIndex != 0) { // ????????????? mergedRegionFlag = true; } else if (!rowShift && paramLength > valueIndex + 1) { // ???????????? mergedRegionFlag = true; } // ?? if (mergedRegionFlag) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + unitRowSize - 1, cell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = currentValue; } } // ??????? if (unitRowSize == 1) { beforeValue = currentValue; } } // ???? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); parsedReportInfo.setParsedObject(resultValues); // ?? parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex() + unitRowSize - 1); parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex()); parsedReportInfo.setRowIndex(tagCell.getRowIndex() + shiftNum - 1); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex()); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX, Object[] row, int fieldNr, boolean isTitle) throws KettleException { try {/*from w ww.java 2 s . c om*/ boolean cellExisted = true; // get the cell Cell cell = xlsRow.getCell(posX); if (cell == null) { cellExisted = false; cell = xlsRow.createCell(posX); } // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { // if the style of this field is cached, reuse it if (!isTitle && data.getCachedStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedStyle(fieldNr)); } else { // apply style if requested if (excelField != null) { // determine correct cell for title or data rows String styleRef = null; if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) { styleRef = excelField.getStyleCell(); } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) { styleRef = excelField.getTitleStyleCell(); } if (styleRef != null) { Cell styleCell = getCellFromReference(styleRef); if (styleCell != null && cell != styleCell) { cell.setCellStyle(styleCell.getCellStyle()); } } } // set cell format as specified, specific format overrides cell specification if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat()) && !excelField.getFormat().startsWith("Image")) { setDataFormat(excelField.getFormat(), cell); } // cache it for later runs if (!isTitle) { data.cacheStyle(fieldNr, cell.getCellStyle()); } } } // create link on cell if requested if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) { String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr]) .getString(row[data.linkfieldnrs[fieldNr]]); if (!Utils.isEmpty(link)) { CreationHelper ch = data.wb.getCreationHelper(); // set the link on the cell depending on link type Hyperlink hyperLink = null; if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) { hyperLink = ch.createHyperlink(HyperlinkType.URL); hyperLink.setLabel("URL Link"); } else if (link.startsWith("mailto:")) { hyperLink = ch.createHyperlink(HyperlinkType.EMAIL); hyperLink.setLabel("Email Link"); } else if (link.startsWith("'")) { hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT); hyperLink.setLabel("Link within this document"); } else { hyperLink = ch.createHyperlink(HyperlinkType.FILE); hyperLink.setLabel("Link to a file"); } hyperLink.setAddress(link); cell.setHyperlink(hyperLink); // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { if (data.getCachedLinkStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedLinkStyle(fieldNr)); } else { // CellStyle style = cell.getCellStyle(); Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex()); Font hlink_font = data.wb.createFont(); // reporduce original font characteristics hlink_font.setBold(origFont.getBold()); hlink_font.setCharSet(origFont.getCharSet()); hlink_font.setFontHeight(origFont.getFontHeight()); hlink_font.setFontName(origFont.getFontName()); hlink_font.setItalic(origFont.getItalic()); hlink_font.setStrikeout(origFont.getStrikeout()); hlink_font.setTypeOffset(origFont.getTypeOffset()); // make it blue and underlined hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); CellStyle style = cell.getCellStyle(); style.setFont(hlink_font); cell.setCellStyle(style); data.cacheLinkStyle(fieldNr, cell.getCellStyle()); } } } } // create comment on cell if requrested if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0 && data.wb instanceof XSSFWorkbook) { String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr]) .getString(row[data.commentfieldnrs[fieldNr]]); if (!Utils.isEmpty(comment)) { String author = data.commentauthorfieldnrs[fieldNr] >= 0 ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString( row[data.commentauthorfieldnrs[fieldNr]]) : "Kettle PDI"; cell.setCellComment(createCellComment(author, comment)); } } // cell is getting a formula value or static content if (!isTitle && excelField != null && excelField.isFormula()) { // formula case cell.setCellFormula(vMeta.getString(v)); } else { // static content case switch (vMeta.getType()) { case ValueMetaInterface.TYPE_DATE: if (v != null && vMeta.getDate(v) != null) { cell.setCellValue(vMeta.getDate(v)); } break; case ValueMetaInterface.TYPE_BOOLEAN: if (v != null) { cell.setCellValue(vMeta.getBoolean(v)); } break; case ValueMetaInterface.TYPE_STRING: case ValueMetaInterface.TYPE_BINARY: if (v != null) { cell.setCellValue(vMeta.getString(v)); } break; case ValueMetaInterface.TYPE_BIGNUMBER: case ValueMetaInterface.TYPE_NUMBER: case ValueMetaInterface.TYPE_INTEGER: if (v != null) { cell.setCellValue(vMeta.getNumber(v)); } break; default: break; } } } catch (Exception e) { logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString()); logError(Const.getStackTracker(e)); throw new KettleException(e); } }