List of usage examples for org.apache.poi.ss.usermodel Font getFontHeight
short getFontHeight();
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) { ListHashMap<Integer, Integer> col2lens = new ListHashMap<>(); for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) { Row r = sheet.getRow(row);/* w w w . j a v a2 s .c o m*/ if (r == null || r.getFirstCellNum() < 0) continue; short maxH = 0; for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) { Cell c = r.getCell(col); if (c == null || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC)) continue; Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex()); String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue() : "" + c.getNumericCellValue(); String[] lines = MiscUtils.split(s, "\n"); int maxLen = 1; for (int i = 0; i < lines.length; i++) { maxLen = Math.max(lines[i].length(), maxLen); } if (font.getFontHeightInPoints() < 12) { col2lens.add(col, 700 + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20); } maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2)); } if (resizeHeight) r.setHeight(maxH); } for (int col : col2lens.keySet()) { List<Integer> lens = col2lens.get(col); Collections.sort(lens); int len = lens.get(lens.size() - 1); if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) { len = lens.get(lens.size() - 2); } sheet.setColumnWidth(col, Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500)); } }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? //from w ww. j av a 2s.c o m */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); // sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); Font font = wb.createFont(); font.setFontHeight((short) 16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(CellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); // row.setHeight((short)300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); CellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); Font fontT = ((HSSFCellStyle) cs1).getFont(wbT); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(16, fontT.getFontHeight()); assertEquals(3, fontT.getBoldweight()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
public void updateLongestExecutions() { if (!longestExecutionCells.isEmpty()) { for (Cell cell : longestExecutionCells) { CellStyle original = cell.getCellStyle(); Sheet sheet = cell.getSheet(); Workbook workbook = sheet.getWorkbook(); CellStyle newStyle = workbook.createCellStyle(); newStyle.cloneStyleFrom(original); int originalFontIndex = original.getFontIndexAsInt(); Font originalFont = workbook.getFontAt(originalFontIndex); Font font = workbook.createFont(); font.setBold(true);/*from w w w. ja v a 2s .c om*/ font.setColor(IndexedColors.DARK_RED.getIndex()); font.setFontHeight((short) Math.round(originalFont.getFontHeight() * 1.5)); newStyle.setFont(font); cell.setCellStyle(newStyle); Row row = cell.getRow(); short firstCellNum = row.getFirstCellNum(); short lastCellNum = row.getLastCellNum(); for (int i = firstCellNum; i < lastCellNum; i++) { Cell rowCell = row.getCell(i); original = rowCell.getCellStyle(); CellStyle borderStyle = workbook.createCellStyle(); borderStyle.cloneStyleFrom(original); borderStyle.setBorderTop(BorderStyle.MEDIUM); borderStyle.setBorderBottom(BorderStyle.MEDIUM); if (i == cell.getColumnIndex()) { borderStyle.setBorderLeft(BorderStyle.MEDIUM); borderStyle.setBorderRight(BorderStyle.MEDIUM); } else if (i == firstCellNum) { borderStyle.setBorderLeft(BorderStyle.MEDIUM); } else if (i == lastCellNum - 1) { borderStyle.setBorderRight(BorderStyle.MEDIUM); } rowCell.setCellStyle(borderStyle); } } } }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
protected void colorCompromisedThemes() { Collection<Cell> failed = statii.get("FAILED"); if (!failed.isEmpty()) { List<Row> rows = Lists.newArrayListWithExpectedSize(failed.size()); for (Cell cell : failed) { Row row = cell.getRow();//from w w w. j a v a2s . c om rows.add(row); } Set<Cell> compromisedThemeCells = Sets.newHashSet(); Map<String, Collection<Cell>> themeMap = themes.asMap(); for (Map.Entry<String, Collection<Cell>> mapEntry : themeMap.entrySet()) { Collection<Cell> themeCells = mapEntry.getValue(); boolean compromised = false; for (Iterator<Cell> iterator = themeCells.iterator(); !compromised && iterator.hasNext();) { Cell themeCell = iterator.next(); Row row = themeCell.getRow(); compromised = rows.contains(row); } if (compromised) { compromisedThemeCells.addAll(themeCells); } } Set<String> compromisedThemes = Sets.newHashSet(); for (Cell themeCell : compromisedThemeCells) { String contents = themeCell.getStringCellValue(); if (null != contents) { Iterable<String> themes = Splitter.onPattern("\\s+").omitEmptyStrings().split(contents); Iterables.addAll(compromisedThemes, themes); } } for (String theme : compromisedThemes) { Collection<Cell> cells = themes.get(theme); for (Cell cell : cells) { CellStyle cellStyle = cell.getCellStyle(); Sheet sheet = cell.getSheet(); Workbook workbook = sheet.getWorkbook(); int originalFontIndex = cellStyle.getFontIndexAsInt(); Font originalFont = workbook.getFontAt(originalFontIndex); CellStyle clone = workbook.createCellStyle(); clone.cloneStyleFrom(cellStyle); Font font = workbook.findFont(true, IndexedColors.DARK_RED.getIndex(), originalFont.getFontHeight(), originalFont.getFontName(), originalFont.getItalic(), originalFont.getStrikeout(), originalFont.getTypeOffset(), originalFont.getUnderline()); if (null == font) { font = workbook.createFont(); font.setBold(true); font.setColor(IndexedColors.DARK_RED.getIndex()); font.setFontHeight(originalFont.getFontHeight()); font.setFontName(originalFont.getFontName()); font.setItalic(originalFont.getItalic()); font.setStrikeout(originalFont.getStrikeout()); font.setTypeOffset(originalFont.getTypeOffset()); font.setUnderline(originalFont.getUnderline()); } clone.setFont(font); cell.setCellStyle(clone); } } } }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private Font getFont(short fontIndex, AttributeSet attributeSet, Workbook wb) { Font font = null;/* ww w . j av a2 s. c om*/ String family = null; String sizeStr = null; short color = 0; boolean bold = false; boolean italic = false; boolean underline = false; boolean line_through = false; boolean sub = false; boolean sup = false; Enumeration<?> en = attributeSet.getAttributeNames(); while (en.hasMoreElements()) { Object key = en.nextElement(); String name = key.toString(); String attribute = attributeSet.getAttribute(key).toString(); switch (name) { case "font-weight": bold = attribute.equals("bold"); break; case "font-style": italic = attribute.equals("italic"); break; case "text-decoration": if (attribute.equals("underline")) { underline = true; } else if (attribute.equals("line-through")) { line_through = true; } break; case "font-family": family = attribute; break; case "font-size": sizeStr = attribute; break; case "color": Color fontColor = Utils.colorByName(attribute); if (fontColor == null) { try { fontColor = Utils.stringToColor(attribute); } catch (Exception ignored) { } } if (fontColor != null) { color = colorToIndex(wb, fontColor); } break; case "vertical-align": if (attribute.equals("sub")) { sub = true; } else if (attribute.equals("sup")) { sup = true; } break; } } if (family != null || bold || italic || underline || line_through || color > 0 || sizeStr != null || sub || sup) { font = wb.createFont(); if (fontIndex > 0) { Font parentFont = wb.getFontAt(fontIndex); if (parentFont != null) { font.setBold(parentFont.getBold()); font.setColor(parentFont.getColor()); try { font.setCharSet(parentFont.getCharSet()); } catch (Throwable ignored) { } font.setFontHeight(parentFont.getFontHeight()); font.setFontName(parentFont.getFontName()); font.setItalic(parentFont.getItalic()); font.setStrikeout(parentFont.getStrikeout()); font.setUnderline(parentFont.getUnderline()); font.setTypeOffset(parentFont.getTypeOffset()); } } if (family != null) { font.setFontName(family); } if (bold) { font.setBold(true); } if (italic) { font.setItalic(italic); } if (underline) { font.setUnderline(Font.U_SINGLE); } if (line_through) { font.setStrikeout(line_through); } if (color > 0) { font.setColor(color); } if (sizeStr != null) { short size = (short) Float.parseFloat(sizeStr); if (sizeStr.charAt(0) == '+' || sizeStr.charAt(0) == '-') { size = (short) (Content.pointToSize(font.getFontHeightInPoints()) + size); } font.setFontHeightInPoints(Content.sizeToPoints(size)); } if (sup) { font.setTypeOffset(Font.SS_SUPER); } else if (sub) { font.setTypeOffset(Font.SS_SUB); } } return font; }
From source file:org.joeffice.spreadsheet.TableStyleable.java
License:Apache License
private Font copyFont(Cell cell) { CellStyle style = cell.getCellStyle(); Workbook workbook = cell.getSheet().getWorkbook(); short fontIndex = style.getFontIndex(); Font xlsFont = cell.getSheet().getWorkbook().getFontAt(fontIndex); Font newFont = workbook.createFont(); newFont.setFontName(xlsFont.getFontName()); newFont.setFontHeight((short) xlsFont.getFontHeight()); newFont.setBoldweight(xlsFont.getBoldweight()); newFont.setItalic(xlsFont.getItalic()); newFont.setUnderline(xlsFont.getUnderline()); newFont.setColor(xlsFont.getColor()); return newFont; }
From source file:org.netxilia.impexp.impl.PoiUtils.java
License:Open Source License
public static CellStyle netxiliaStyle2Poi(Styles nxStyle, Workbook workbook, CellStyle poiStyle) { if (nxStyle == null) { return poiStyle; }// w w w . j av a 2 s . c om poiStyle.setWrapText(nxStyle.contains(DefaultStyle.nowrap.getStyle())); // font short bold = nxStyle.contains(DefaultStyle.bold.getStyle()) ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL; byte underline = nxStyle.contains(DefaultStyle.underline.getStyle()) ? Font.U_SINGLE : Font.U_NONE; boolean italic = nxStyle.contains(DefaultStyle.italic.getStyle()); boolean strikeout = nxStyle.contains(DefaultStyle.strikeout.getStyle()); Font defaultFont = workbook.getFontAt(poiStyle.getFontIndex()); Font font = workbook.findFont(bold, defaultFont.getColor(), defaultFont.getFontHeight(), defaultFont.getFontName(), italic, strikeout, defaultFont.getTypeOffset(), underline); if (font == null) { font = workbook.createFont(); font.setBoldweight(bold); font.setItalic(italic); font.setUnderline(underline); font.setStrikeout(strikeout); } poiStyle.setFont(font); // borders if (nxStyle.contains(DefaultStyle.borderLeft.getStyle())) { poiStyle.setBorderLeft(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderRight.getStyle())) { poiStyle.setBorderRight(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderTop.getStyle())) { poiStyle.setBorderTop(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderBottom.getStyle())) { poiStyle.setBorderBottom(CellStyle.BORDER_THIN); } // align if (nxStyle.contains(DefaultStyle.alignLeft.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_LEFT); } else if (nxStyle.contains(DefaultStyle.alignRight.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_RIGHT); } else if (nxStyle.contains(DefaultStyle.alignCenter.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_CENTER); } else if (nxStyle.contains(DefaultStyle.alignJustify.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); } return poiStyle; }
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 a v a 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); } }
From source file:ru.icc.cells.ssdc.DataLoader.java
License:Apache License
private void fillFont(CFont font, Font excelFont) { font.setName(excelFont.getFontName()); // TODO CFont font //font.setColor( excelFont.getColor() ); font.setHeight(excelFont.getFontHeight()); font.setHeightInPoints(excelFont.getFontHeightInPoints()); // TODO ? ? Boldweight, ?? ? ? short boldWeight = excelFont.getBoldweight(); if (boldWeight >= 700) font.setBold(true);/*from w w w.ja v a 2s . co m*/ font.setItalic(excelFont.getItalic()); font.setStrikeout(excelFont.getStrikeout()); byte underline = excelFont.getUnderline(); if (underline != Font.U_NONE) font.setUnderline(true); if (underline == Font.U_DOUBLE || underline == Font.U_DOUBLE_ACCOUNTING) font.setDoubleUnderline(true); }