List of usage examples for org.apache.poi.ss.usermodel Hyperlink setAddress
public void setAddress(String address);
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unused") private static void writeCell(Cell cell, Object val, boolean userTemplate, ExcelWriteFieldMappingAttribute attribute, Object bean) { if (attribute != null && attribute.getLinkField() != null) { String addressFieldName = attribute.getLinkField(); String address = null;//from w ww .j a va2s .c o m if (bean != null) { address = (String) getFieldValue(bean, addressFieldName, true); } Workbook wb = cell.getRow().getSheet().getWorkbook(); Hyperlink link = wb.getCreationHelper().createHyperlink(attribute.getLinkType()); link.setAddress(address); cell.setHyperlink(link); // Its style can't inherit from cell. CellStyle style = wb.createCellStyle(); Font hlinkFont = wb.createFont(); hlinkFont.setUnderline(Font.U_SINGLE); hlinkFont.setColor(IndexedColors.BLUE.getIndex()); style.setFont(hlinkFont); if (cell.getCellStyle() != null) { style.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor()); } cell.setCellStyle(style); } if (val == null) { cell.setCellValue((String) null); return; } Class<?> clazz = val.getClass(); if (val instanceof Byte) {// Double Byte temp = (Byte) val; cell.setCellValue((double) temp.byteValue()); } else if (val instanceof Short) { Short temp = (Short) val; cell.setCellValue((double) temp.shortValue()); } else if (val instanceof Integer) { Integer temp = (Integer) val; cell.setCellValue((double) temp.intValue()); } else if (val instanceof Long) { Long temp = (Long) val; cell.setCellValue((double) temp.longValue()); } else if (val instanceof Float) { Float temp = (Float) val; cell.setCellValue((double) temp.floatValue()); } else if (val instanceof Double) { Double temp = (Double) val; cell.setCellValue((double) temp.doubleValue()); } else if (val instanceof Date) {// Date Date dateVal = (Date) val; long time = dateVal.getTime(); // read is based on 1899/12/31 but DateUtil.getExcelDate is base on // 1900/01/01 if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_01_00_00_00_000) { Date incOneDay = new Date(time + 24 * 60 * 60 * 1000); double d = DateUtil.getExcelDate(incOneDay); cell.setCellValue(d - 1); } else { cell.setCellValue(dateVal); } if (!userTemplate) { Workbook wb = cell.getRow().getSheet().getWorkbook(); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle == null) { cellStyle = wb.createCellStyle(); } DataFormat dataFormat = wb.getCreationHelper().createDataFormat(); // @see #BuiltinFormats // 0xe, "m/d/yy" // 0x14 "h:mm" // 0x16 "m/d/yy h:mm" // {@linke https://en.wikipedia.org/wiki/Year_10,000_problem} /** [1899/12/31 00:00:00:000~1900/01/01 00:00:000) */ if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_02_00_00_00_000) { cellStyle.setDataFormat(dataFormat.getFormat("h:mm")); // cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm")); } else { // if ( time % (24 * 60 * 60 * 1000) == 0) {//for time // zone,we can't use this way. Calendar calendar = Calendar.getInstance(); calendar.setTime(dateVal); int hour = calendar.get(Calendar.HOUR_OF_DAY); int minute = calendar.get(Calendar.MINUTE); int second = calendar.get(Calendar.SECOND); int millisecond = calendar.get(Calendar.MILLISECOND); if (millisecond == 0 && second == 0 && minute == 0 && hour == 0) { cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy")); } else { cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm")); } } cell.setCellStyle(cellStyle); } } else if (val instanceof Boolean) {// Boolean cell.setCellValue(((Boolean) val).booleanValue()); } else {// String cell.setCellValue((String) val.toString()); } }
From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java
License:Apache License
private void writeExecutionPlansForWorstStatements(List<DeltaSQLStatementSnapshot> worstStatements) { Row templateStatementHeaderRow = executionPlansSheet .getRow(EXECUTION_PLANS_INDEX_ROW_TEMPLATE_STATEMENT_HEADER_ROW); Row templateChildHeaderRow = executionPlansSheet .getRow(EXECUTION_PLANS_INDEX_ROW_TEMPLATE_CHILD_HEADER_ROW); Row templateExecutionStepRow = executionPlansSheet .getRow(EXECUTION_PLANS_INDEX_ROW_TEMPLATE_EXECUTION_STEP_ROW); if (listener != null) { listener.setMessage("Loading the execution plans..."); }//from w w w. ja v a 2 s.com SQLHelper.loadExecutionPlansForStatements(worstStatements, listener); // Now write the execution plans into the Excel sheet int currentRowIndex = EXECUTION_PLANS_INDEX_START_EXECUTION_PLANS; Row currentRow; int currentDataSet = 0; if (listener != null) { listener.setMessage("Writing the execution plans..."); listener.setStartValue(0); listener.setEndValue(worstStatements.size()); } for (DeltaSQLStatementSnapshot currentSnapshot : worstStatements) { currentDataSet++; if (listener != null) { listener.setCurrentValue(currentDataSet); } // Header for statement currentRow = SSUtilities.copyRow(executionPlansSheet, templateStatementHeaderRow, currentRowIndex); currentRow.getCell(0).setCellValue(currentSnapshot.getSqlStatement().getSqlId()); currentRow.getCell(1).setCellValue(currentSnapshot.getSqlStatement().getSqlTextTrimmedForExcel()); currentRowIndex++; // Link from sheet with statements to this execution plan Hyperlink link = statementsSheet.getWorkbook().getCreationHelper() .createHyperlink(Hyperlink.LINK_DOCUMENT); // Later on we will delete three rows at the beginning of the sheet. Prepare the // hyperlink to point to the correct cell after the deletion. link.setAddress("'" + executionPlansSheet.getSheetName() + "'!A" + (currentRowIndex - 3)); addLinkFromStatement(currentSnapshot.getSqlStatement().getSqlId(), INDEX_COLUMN_HAS_PLAN, link); for (ExecutionPlan currentPlan : currentSnapshot.getSqlStatement().getExecutionPlans()) { // Header for Child / Execution Plan // There may be more than one execution plans... currentRow = SSUtilities.copyRow(executionPlansSheet, templateChildHeaderRow, currentRowIndex); currentRow.getCell(0).setCellValue(currentPlan.getInstanceId()); currentRow.getCell(1).setCellValue(currentPlan.getChildNumber()); currentRowIndex++; currentRowIndex = writeExecutionPlanStepToExcel(templateExecutionStepRow, currentRowIndex, currentPlan.getParentStep()); } } // delete the template rows SSUtilities.deleteRow(executionPlansSheet, templateStatementHeaderRow); SSUtilities.deleteRow(executionPlansSheet, templateChildHeaderRow); SSUtilities.deleteRow(executionPlansSheet, templateExecutionStepRow); }
From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java
License:Apache License
private void writeWaitEventsForWorstStatements(List<DeltaSQLStatementSnapshot> worstStatements) { Row templateStatementHeaderRow = waitEventsForStatementSheet .getRow(WAIT_EVENTS_SQL_INDEX_ROW_TEMPLATE_STATEMENT_HEADER_ROW); Row templateWaitEventRow = waitEventsForStatementSheet .getRow(WAIT_EVENTS_SQL_INDEX_ROW_TEMPLATE_WAIT_EVENT_ROW); Map<DeltaSQLStatementSnapshot, List<WaitEventForStatementTuple>> waitEventsPerStatementMap = SQLHelper .loadWaitEventsForStatements(worstStatements, deltaSnapshot.getSnapshotA().getSnapshotTime(), deltaSnapshot.getSnapshotB().getSnapshotTime(), null); // Now write the wait events into the Excel sheet int currentRowIndex = WAIT_EVENTS_SQL_INDEX_START_WAIT_EVENTS; Row currentRow;/*from w w w .j av a 2s . c o m*/ int currentDataSet = 0; if (listener != null) { listener.setMessage("Writing the wait events..."); listener.setStartValue(0); listener.setEndValue(worstStatements.size()); } for (DeltaSQLStatementSnapshot currentSnapshot : worstStatements) { currentDataSet++; if (listener != null) { listener.setCurrentValue(currentDataSet); } if (waitEventsPerStatementMap.get(currentSnapshot) == null) { // if this worst statement has no wait events associated with, go to the next one continue; } // Header for statement currentRow = SSUtilities.copyRow(waitEventsForStatementSheet, templateStatementHeaderRow, currentRowIndex); currentRow.getCell(0).setCellValue(currentSnapshot.getSqlStatement().getSqlId()); currentRow.getCell(1).setCellValue(currentSnapshot.getSqlStatement().getSqlTextTrimmedForExcel()); currentRowIndex++; // Link from sheet with statements to this list of wait events Hyperlink link = statementsSheet.getWorkbook().getCreationHelper() .createHyperlink(Hyperlink.LINK_DOCUMENT); // Later on we will delete two rows at the beginning of the sheet. Prepare the // hyperlink to point to the correct cell after the deletion. link.setAddress("'" + waitEventsForStatementSheet.getSheetName() + "'!A" + (currentRowIndex - 2)); addLinkFromStatement(currentSnapshot.getSqlStatement().getSqlId(), INDEX_COLUMN_HAS_WAIT, link); for (WaitEventForStatementTuple currentTuple : waitEventsPerStatementMap.get(currentSnapshot)) { currentRow = SSUtilities.copyRow(waitEventsForStatementSheet, templateWaitEventRow, currentRowIndex); int columnIndex = 0; currentRow.getCell(columnIndex++).setCellValue(currentTuple.getWaitEventName()); currentRow.getCell(columnIndex++).setCellValue(currentTuple.getWaitObjectOwner()); currentRow.getCell(columnIndex++).setCellValue(currentTuple.getWaitObjectName()); //noinspection UnusedAssignment nullSafeBigDecimalIntoCellWriter(currentRow.getCell(columnIndex++), currentTuple.getWaitedSeconds()); currentRowIndex++; } } // delete the template rows SSUtilities.deleteRow(waitEventsForStatementSheet, templateStatementHeaderRow); SSUtilities.deleteRow(waitEventsForStatementSheet, templateWaitEventRow); }
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 {/* w w w. j av a 2 s .c o m*/ 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); } }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a short version excel worksheet *//*from w w w .ja v a2 s. co m*/ private Workbook createShortVersonWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("event_overview", "Events Overview"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20 * 256); sheet.setColumnWidth(1, 15 * 256); sheet.setColumnWidth(2, 16 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 25 * 256); sheet.setColumnWidth(5, 19 * 256); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 0; i <= 6; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(0); titleCell.setCellValue(eventTitle); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); // Cureent viewer row Row row = sheet.createRow(2); row.setHeightInPoints(rowHigh); Cell cell = row.createCell(0); cell.setCellValue(rb.getString("event_viewer", "Viewer:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentUserName()); // site title row row = sheet.createRow(3); row.setHeightInPoints(rowHigh); cell = row.createCell(0); cell.setCellValue(rb.getString("event_site_title", "Site Title:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentSiteTitle()); // Table titles th row row = sheet.createRow(5); row.setHeightInPoints(rowHigh); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabColNames")); } cell = row.getCell(0); cell.setCellValue(tabTitles_shortVersion[0]); cell = row.getCell(1); cell.setCellValue(tabTitles_shortVersion[1]); cell = row.getCell(2); cell.setCellValue(tabTitles_shortVersion[2]); cell = row.getCell(3); cell.setCellValue(tabTitles_shortVersion[3]); cell = row.getCell(4); cell.setCellValue(tabTitles_shortVersion[4]); cell = row.getCell(5); cell.setCellValue(tabTitles_shortVersion[5]); cell = row.getCell(6); cell.setCellValue(tabTitles_shortVersion[6]); /* table row data */ int rowNum = 6; int seqNum = 1; for (SignupMeetingWrapper wrp : wrappers) { if (wrp.isToDownload()) { row = sheet.createRow(rowNum); int rowHighNum = 1; rowNum++; for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabItem_fields")); } // event ttile cell = row.getCell(0); cell.setCellStyle(styles.get("item_left_wrap")); cell.setCellValue(wrp.getMeeting().getTitle()); Hyperlink sheetLink = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); String validSheetName = CreateValidWorksheetName(wrp.getMeeting().getTitle(), seqNum, true); String hlinkAddr = "'" + validSheetName + "'" + "!A1"; sheetLink.setAddress(hlinkAddr); cell.setHyperlink(sheetLink); cell.setCellStyle(styles.get("hyperLink")); seqNum++; // event owner cell = row.getCell(1); cell.setCellValue(wrp.getCreator()); // event location cell = row.getCell(2); cell.setCellValue(wrp.getMeeting().getLocation()); // event category cell = row.getCell(3); cell.setCellValue(wrp.getMeeting().getCategory()); // event Date cell = row.getCell(4); cell.setCellValue(getShortWeekDayName(wrp.getStartTime()) + ", " + getTime(wrp.getStartTime()).toStringLocalShortDate()); // event time period cell = row.getCell(5); cell.setCellValue(getMeetingPeriodShortVersion(wrp)); // event status cell = row.getCell(6); cell.setCellValue( ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(wrp.getAvailableStatus())); } } // end of table line row = sheet.createRow(rowNum); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } return wb; }
From source file:org.seasar.fisshplate.core.element.Link.java
License:Apache License
void mergeImpl(FPContext context, Cell out) throws FPMergeException { String cellValue = getCellValue().toString(); Matcher mat = pat.matcher(cellValue); if (!mat.find()) { throw new FPMergeException(FPConsts.MESSAGE_ID_LINK_MERGE_ERROR, new Object[] { cellValue }, cell.getRow());//from www . ja v a2 s . com } String type = mat.group(1); String link = mat.group(2); String text = mat.group(3); LinkElementType linkType = LinkElementType.get(type); Hyperlink hyperLink = linkType.createHyperLink(); hyperLink.setAddress(link); out.setHyperlink(hyperLink); out.setCellValue(text); }
From source file:packtest.HyperlinkExample.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);/* w w w . java 2 s .c o m*/ Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream(Utils.getPath("hyperinks.xlsx")); wb.write(out); out.close(); }
From source file:uk.co.spudsoft.birt.emitters.excel.handlers.CellContentHandler.java
License:Open Source License
/** * Finish processing for the current (real) cell. * @param element/* ww w. j a va2 s .co m*/ * The element that signifies the end of the cell (this may not be an ICellContent object if the * cell is created for a label or text outside of a table). */ protected void endCellContent(HandlerState state, ICellContent birtCell, IContent element, Cell cell, Area area) { StyleManager sm = state.getSm(); StyleManagerUtils smu = state.getSmu(); BirtStyle birtCellStyle = null; if (birtCell != null) { birtCellStyle = new BirtStyle(birtCell); if (element != null) { // log.debug( "Overlaying style from ", element ); birtCellStyle.overlay(element); } } else if (element != null) { birtCellStyle = new BirtStyle(element); } else { birtCellStyle = new BirtStyle(state.getSm().getCssEngine()); } if (preferredAlignment != null) { birtCellStyle.setProperty(StyleConstants.STYLE_TEXT_ALIGN, preferredAlignment); } if (CSSConstants.CSS_TRANSPARENT_VALUE .equals(birtCellStyle.getString(StyleConstants.STYLE_BACKGROUND_COLOR))) { if (parent != null) { birtCellStyle.setProperty(StyleConstants.STYLE_BACKGROUND_COLOR, parent.getBackgroundColour()); } } if (hyperlinkUrl != null) { Hyperlink hyperlink = cell.getSheet().getWorkbook().getCreationHelper() .createHyperlink(Hyperlink.LINK_URL); hyperlink.setAddress(hyperlinkUrl); cell.setHyperlink(hyperlink); } if (hyperlinkBookmark != null) { Hyperlink hyperlink = cell.getSheet().getWorkbook().getCreationHelper() .createHyperlink(Hyperlink.LINK_DOCUMENT); hyperlink.setAddress(prepareName(hyperlinkBookmark)); cell.setHyperlink(hyperlink); } if (lastValue != null) { if (lastValue instanceof String) { String lastString = (String) lastValue; smu.correctFontColorIfBackground(birtCellStyle); for (RichTextRun run : richTextRuns) { run.font = smu.correctFontColorIfBackground(sm.getFontManager(), state.getWb(), birtCellStyle, run.font); } if (!richTextRuns.isEmpty()) { RichTextString rich = smu.createRichTextString(lastString); int runStart = richTextRuns.get(0).startIndex; Font lastFont = richTextRuns.get(0).font; for (int i = 0; i < richTextRuns.size(); ++i) { RichTextRun run = richTextRuns.get(i); log.debug("Run: ", run.startIndex, " font :", run.font); if (!lastFont.equals(run.font)) { log.debug("Applying ", runStart, " - ", run.startIndex); rich.applyFont(runStart, run.startIndex, lastFont); runStart = run.startIndex; lastFont = richTextRuns.get(i).font; } } log.debug("Finalising with ", runStart, " - ", lastString.length()); rich.applyFont(runStart, lastString.length(), lastFont); setCellContents(cell, rich); } else { setCellContents(cell, lastString); } if (lastString.contains("\n")) { if (!CSSConstants.CSS_NOWRAP_VALUE.equals(lastElement.getStyle().getWhiteSpace())) { birtCellStyle.setProperty(StyleConstants.STYLE_WHITE_SPACE, new StringValue(StringValue.CSS_STRING, CSSConstants.CSS_PRE_VALUE)); } } if (!richTextRuns.isEmpty()) { birtCellStyle.setProperty(StyleConstants.STYLE_VERTICAL_ALIGN, new StringValue(StringValue.CSS_STRING, CSSConstants.CSS_TOP_VALUE)); } if (preferredAlignment != null) { birtCellStyle.setProperty(StyleConstants.STYLE_TEXT_ALIGN, preferredAlignment); } } else { setCellContents(cell, lastValue); } } int colIndex = cell.getColumnIndex(); state.getSmu().applyAreaBordersToCell(state.areaBorders, cell, birtCellStyle, state.rowNum, colIndex); if ((birtCell != null) && ((birtCell.getColSpan() > 1) || (birtCell.getRowSpan() > 1))) { AreaBorders mergedRegionBorders = AreaBorders.createForMergedCells( state.rowNum + birtCell.getRowSpan() - 1, colIndex, colIndex + birtCell.getColSpan() - 1, state.rowNum, birtCellStyle); if (mergedRegionBorders != null) { state.insertBorderOverload(mergedRegionBorders); } } String customNumberFormat = EmitterServices.stringOption(state.getRenderOptions(), element, ExcelEmitter.CUSTOM_NUMBER_FORMAT, null); if (customNumberFormat != null) { StyleManagerUtils.setNumberFormat(birtCellStyle, ExcelEmitter.CUSTOM_NUMBER_FORMAT + customNumberFormat, null); } setCellStyle(sm, cell, birtCellStyle, lastValue); // Excel auto calculates the row height (if it isn't specified) as long as the cell isn't merged - if it is merged I have to do it if (((colSpan > 1) || (state.rowHasSpans(state.rowNum))) && ((lastValue instanceof String) || (lastValue instanceof RichTextString))) { int spannedRowAlgorithm = EmitterServices.integerOption(state.getRenderOptions(), element, ExcelEmitter.SPANNED_ROW_HEIGHT, ExcelEmitter.SPANNED_ROW_HEIGHT_SPREAD); Font defaultFont = state.getWb().getFontAt(cell.getCellStyle().getFontIndex()); double cellWidth = spanWidthMillimetres(state.currentSheet, cell.getColumnIndex(), cell.getColumnIndex() + colSpan - 1); float cellDesiredHeight = smu.calculateTextHeightPoints(cell.getStringCellValue(), defaultFont, cellWidth, richTextRuns); if (cellDesiredHeight > state.requiredRowHeightInPoints) { int rowSpan = birtCell.getRowSpan(); if (rowSpan < 2) { state.requiredRowHeightInPoints = cellDesiredHeight; } else { switch (spannedRowAlgorithm) { case ExcelEmitter.SPANNED_ROW_HEIGHT_FIRST: state.requiredRowHeightInPoints = cellDesiredHeight; break; case ExcelEmitter.SPANNED_ROW_HEIGHT_IGNORED: break; default: if (area != null) { area.setHeight(cellDesiredHeight); } } } } } // Adjust the required row height for any relevant areas based on what's left float rowSpanHeightRequirement = state.calculateRowSpanHeightRequirement(state.rowNum); if (rowSpanHeightRequirement > state.requiredRowHeightInPoints) { state.requiredRowHeightInPoints = rowSpanHeightRequirement; } if (EmitterServices.booleanOption(state.getRenderOptions(), element, ExcelEmitter.FREEZE_PANES, false)) { if (state.currentSheet.getPaneInformation() == null) { state.currentSheet.createFreezePane(state.colNum, state.rowNum); } } lastValue = null; lastElement = null; richTextRuns.clear(); }
From source file:utilities.XLSReportsManager.java
License:Open Source License
private void processDataListForXLS(ArrayList<ArrayList<KeyValue>> dArray, Sheet sheet, Sheet settingsSheet, Map<String, CellStyle> styles, ArrayList<Column> cols, String tz, ArrayList<KeyValue> settings) throws IOException { CreationHelper createHelper = wb.getCreationHelper(); for (int index = 0; index < dArray.size(); index++) { Row row = sheet.createRow(rowNumber++); ArrayList<KeyValue> record = dArray.get(index); for (Column col : cols) { Cell cell = row.createCell(col.colIndex); String value = "error"; if (col.dataIndex >= 0) { value = record.get(col.dataIndex).v; }/*w w w . j ava 2s . c o m*/ cell.setCellStyle(styles.get("default")); if (value != null && (value.startsWith("https://") || value.startsWith("http://"))) { cell.setCellStyle(styles.get("link")); Hyperlink url = createHelper.createHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); /* if(isXLSX) { XSSFHyperlink url = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); } else { HSSFHyperlink url = new HSSFHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); } */ } boolean cellWritten = false; if (col.type.equals("datetime")) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { java.util.Date date = dateFormat.parse(value); cell.setCellStyle(styles.get("datetime")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } else if (col.type.equals("date")) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); try { java.util.Date date = dateFormat.parse(value); cell.setCellStyle(styles.get("date")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { // Try to write as number by default try { double vDouble = Double.parseDouble(value); cell.setCellStyle(styles.get("default")); cell.setCellValue(vDouble); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { cell.setCellStyle(styles.get("default")); cell.setCellValue(value); } } } // Populate settings sheet int settingsRowIdx = 0; Row settingsRow = settingsSheet.createRow(settingsRowIdx++); Cell k = settingsRow.createCell(0); Cell v = settingsRow.createCell(1); k.setCellStyle(styles.get("header")); k.setCellValue("Time Zone:"); v.setCellValue(tz); // Show filter settings settingsRowIdx++; settingsRow = settingsSheet.createRow(settingsRowIdx++); Cell f = settingsRow.createCell(0); f.setCellStyle(styles.get("header2")); f.setCellValue("Filters:"); if (settings != null) { for (KeyValue kv : settings) { settingsRow = settingsSheet.createRow(settingsRowIdx++); k = settingsRow.createCell(1); v = settingsRow.createCell(2); k.setCellStyle(styles.get("header")); k.setCellValue(kv.k); v.setCellValue(kv.v); } } }