List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle getBorderBottom
@Override
public BorderStyle getBorderBottom()
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public String getCellBorderStyle(HSSFCell cell) { HSSFCellStyle style = cell.getCellStyle(); StringBuilder sb = new StringBuilder(); if ((cell != null) && (style != null)) { switch (style.getBorderBottom()) { case 3:// www . j av a 2 s .c o m sb.append("border-bottom:dashed;"); break; case 7: sb.append("border-bottom:dotted;"); break; case 0: sb.append("border-bottom:none;"); break; case 2: sb.append("border-bottom:medium none;"); break; case 6: sb.append("border-bottom:double;"); break; case 8: sb.append("border-bottom:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderLeft()) { case 3: sb.append("border-left:dashed;"); break; case 7: sb.append("border-left:dotted;"); break; case 0: sb.append("border-left:none;"); break; case 2: sb.append("border-left:medium none;"); break; case 6: sb.append("border-left:double;"); break; case 8: sb.append("border-left:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderRight()) { case 3: sb.append("border-right:dashed;"); break; case 7: sb.append("border-right:dotted;"); break; case 0: sb.append("border-right:none;"); break; case 2: sb.append("border-right:medium none;"); break; case 6: sb.append("border-right:double;"); break; case 8: sb.append("border-right:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderTop()) { case 3: sb.append("border-top:dashed;"); break; case 7: sb.append("border-top:dotted;"); break; case 0: sb.append("border-top:none;"); break; case 2: sb.append("border-top:medium none;"); break; case 6: sb.append("border-top:double;"); break; case 8: sb.append("border-top:medium dashed;"); case 1: case 4: case 5: } if (tripleToRGBString(style.getBottomBorderColor()) != null) sb.append(new StringBuilder().append("border-bottom:1px thin ") .append(tripleToRGBString(style.getBottomBorderColor())).append(";").toString()); else { sb.append("border-bottom:1px thin windowtext;"); } if (tripleToRGBString(style.getLeftBorderColor()) != null) sb.append(new StringBuilder().append("border-left:1px thin ") .append(tripleToRGBString(style.getLeftBorderColor())).append(";").toString()); else { sb.append("border-left:1px thin windowtext;"); } if (tripleToRGBString(style.getRightBorderColor()) != null) sb.append(new StringBuilder().append("border-right:1px thin ") .append(tripleToRGBString(style.getRightBorderColor())).append(";").toString()); else { sb.append("border-right:1px thin windowtext;"); } if (tripleToRGBString(style.getTopBorderColor()) != null) sb.append(new StringBuilder().append("border-top:1px thin ") .append(tripleToRGBString(style.getTopBorderColor())).append(";").toString()); else { sb.append("border-top:1px thin windowtext;"); } return sb.toString(); } return ""; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static String dumpCellStyle(HSSFCellStyle style) { StringBuffer sb = new StringBuffer(); sb.append(style.getHidden()).append(","); sb.append(style.getLocked()).append(","); sb.append(style.getWrapText()).append(","); sb.append(style.getAlignment()).append(","); sb.append(style.getBorderBottom()).append(","); sb.append(style.getBorderLeft()).append(","); sb.append(style.getBorderRight()).append(","); sb.append(style.getBorderTop()).append(","); sb.append(style.getBottomBorderColor()).append(","); sb.append(style.getDataFormat()).append(","); sb.append(style.getFillBackgroundColor()).append(","); sb.append(style.getFillForegroundColor()).append(","); sb.append(style.getFillPattern()).append(","); sb.append(style.getIndention()).append(","); sb.append(style.getLeftBorderColor()).append(","); sb.append(style.getRightBorderColor()).append(","); sb.append(style.getRotation()).append(","); sb.append(style.getTopBorderColor()).append(","); sb.append(style.getVerticalAlignment()); return sb.toString(); }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static HSSFCellStyle findStyle(HSSFCellStyle style, HSSFWorkbook srcwb, HSSFWorkbook destwb) { HSSFPalette srcpalette = srcwb.getCustomPalette(); HSSFPalette destpalette = destwb.getCustomPalette(); for (short i = 0; i < destwb.getNumCellStyles(); i++) { HSSFCellStyle old = destwb.getCellStyleAt(i); if (old == null) continue; if (style.getAlignment() == old.getAlignment() && style.getBorderBottom() == old.getBorderBottom() && style.getBorderLeft() == old.getBorderLeft() && style.getBorderRight() == old.getBorderRight() && style.getBorderTop() == old.getBorderTop() && isSameColor(style.getBottomBorderColor(), old.getBottomBorderColor(), srcpalette, destpalette)//ww w .j a va 2 s . c o m && style.getDataFormat() == old.getDataFormat() && isSameColor(style.getFillBackgroundColor(), old.getFillBackgroundColor(), srcpalette, destpalette) && isSameColor(style.getFillForegroundColor(), old.getFillForegroundColor(), srcpalette, destpalette) && style.getFillPattern() == old.getFillPattern() && style.getHidden() == old.getHidden() && style.getIndention() == old.getIndention() && isSameColor(style.getLeftBorderColor(), old.getLeftBorderColor(), srcpalette, destpalette) && style.getLocked() == old.getLocked() && isSameColor(style.getRightBorderColor(), old.getRightBorderColor(), srcpalette, destpalette) && style.getRotation() == old.getRotation() && isSameColor(style.getTopBorderColor(), old.getTopBorderColor(), srcpalette, destpalette) && style.getVerticalAlignment() == old.getVerticalAlignment() && style.getWrapText() == old.getWrapText()) { HSSFFont oldfont = destwb.getFontAt(old.getFontIndex()); HSSFFont font = srcwb.getFontAt(style.getFontIndex()); if (oldfont.getBoldweight() == font.getBoldweight() && oldfont.getItalic() == font.getItalic() && oldfont.getStrikeout() == font.getStrikeout() && oldfont.getCharSet() == font.getCharSet() && isSameColor(oldfont.getColor(), font.getColor(), srcpalette, destpalette) && oldfont.getFontHeight() == font.getFontHeight() && oldfont.getFontName().equals(font.getFontName()) && oldfont.getTypeOffset() == font.getTypeOffset() && oldfont.getUnderline() == font.getUnderline()) { return old; } } } return null; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) { if (src == null || dest == null) return;// w w w . j ava 2 s.c o m dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }/*from w w w .j ava 2 s . c o m*/ newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixUpCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) { HSSFCell upCell = sheet.getRow(rowIndex).getCell(columnIndex); if (upCell != null) { HSSFCellStyle upCellStyle = upCell.getCellStyle(); if (upCellStyle.getBorderBottom() != cellStyle.getBorderTop() || upCellStyle.getBottomBorderColor() != cellStyle.getTopBorderColor()) { HSSFCellStyle draftUpStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook()); XslStyleHelper.cloneStyleRelations(upCellStyle, draftUpStyle); draftUpStyle.setBorderBottom(cellStyle.getBorderTop()); draftUpStyle.setBottomBorderColor(cellStyle.getTopBorderColor()); HSSFCellStyle newUpStyle = styleCache.getCellStyleByTemplate(draftUpStyle); if (newUpStyle == null) { newUpStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftUpStyle); styleCache.processCellStyle(newUpStyle); }// ww w. j a v a2s. co m upCell.setCellStyle(newUpStyle); } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixDownCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) { HSSFRow nextRow = sheet.getRow(rowIndex); if (nextRow != null) { HSSFCell downCell = nextRow.getCell(columnIndex); if (downCell != null) { HSSFCellStyle downCellStyle = downCell.getCellStyle(); if (downCellStyle.getBorderTop() != cellStyle.getBorderBottom() || downCellStyle.getTopBorderColor() != cellStyle.getBottomBorderColor()) { HSSFCellStyle draftDownStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook()); XslStyleHelper.cloneStyleRelations(downCellStyle, draftDownStyle); draftDownStyle.setBorderTop(cellStyle.getBorderBottom()); draftDownStyle.setTopBorderColor(cellStyle.getBottomBorderColor()); HSSFCellStyle newDownStyle = styleCache.getCellStyleByTemplate(draftDownStyle); if (newDownStyle == null) { newDownStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftDownStyle); styleCache.processCellStyle(newDownStyle); }//from w ww . j a va 2s.c o m downCell.setCellStyle(newDownStyle); } } } }
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
protected String buildStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle) { StringBuilder style = new StringBuilder(); style.append("white-space:pre-wrap;"); ExcelToHtmlUtils.appendAlign(style, cellStyle.getAlignment()); if (cellStyle.getFillPattern() == 0) { // no fill } else if (cellStyle.getFillPattern() == 1) { final HSSFColor foregroundColor = cellStyle.getFillForegroundColorColor(); if (foregroundColor != null) style.append("background-color:" + ExcelToHtmlUtils.getColor(foregroundColor) + ";"); } else {// w w w. j ava2s. com final HSSFColor backgroundColor = cellStyle.getFillBackgroundColorColor(); if (backgroundColor != null) style.append("background-color:" + ExcelToHtmlUtils.getColor(backgroundColor) + ";"); } buildStyle_border(workbook, style, "top", cellStyle.getBorderTop(), cellStyle.getTopBorderColor()); buildStyle_border(workbook, style, "right", cellStyle.getBorderRight(), cellStyle.getRightBorderColor()); buildStyle_border(workbook, style, "bottom", cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()); buildStyle_border(workbook, style, "left", cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()); HSSFFont font = cellStyle.getFont(workbook); buildStyle_font(workbook, style, font); return style.toString(); }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
protected String buildStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle) { StringBuffer style = new StringBuffer(); style.append("white-space:pre-wrap;"); ExcelToHtmlUtils.appendAlign(style, cellStyle.getAlignment()); if (cellStyle.getFillPattern() == 0) { // no fill } else if (cellStyle.getFillPattern() == 1) { final HSSFColor foregroundColor = cellStyle.getFillForegroundColorColor(); if (foregroundColor != null) { style.append("background-color:" + AbstractExcelUtils.getColor(foregroundColor) + ";"); }//from w w w . java2 s.co m } else { final HSSFColor backgroundColor = cellStyle.getFillBackgroundColorColor(); if (backgroundColor != null) { style.append("background-color:" + AbstractExcelUtils.getColor(backgroundColor) + ";"); } } this.buildStyle_border(workbook, style, "top", cellStyle.getBorderTop(), cellStyle.getTopBorderColor()); this.buildStyle_border(workbook, style, "right", cellStyle.getBorderRight(), cellStyle.getRightBorderColor()); this.buildStyle_border(workbook, style, "bottom", cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()); this.buildStyle_border(workbook, style, "left", cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()); HSSFFont font = cellStyle.getFont(workbook); this.buildStyle_font(workbook, style, font); return style.toString(); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
/** * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML * table cell which has the same font styles, alignments, colours and * borders as the Excel cell./* w w w . ja v a2 s. c om*/ * * @param cell * The Excel cell. */ private void td(final HSSFCell cell) { int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1; mergeEnd = -1; return; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return; } //KLO 05022018 //out.append("<td "); out.append("<td height=\"15\" "); if (colspan > 1) { out.append("colspan='").append(colspan).append("' "); } if (cell == null) { out.append("/>\n"); return; } out.append("style='"); final HSSFCellStyle style = cell.getCellStyle(); // Text alignment switch (style.getAlignment()) { case CellStyle.ALIGN_LEFT: out.append("text-align: left; "); break; case CellStyle.ALIGN_RIGHT: out.append("text-align: right; "); break; case CellStyle.ALIGN_CENTER: out.append("text-align: center; "); break; default: break; } // Font style, size and weight final HSSFFont font = style.getFont(book); if (font == null) return; if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) { out.append("font-weight: bold; "); } if (font.getItalic()) { out.append("font-style: italic; "); } if (font.getUnderline() != HSSFFont.U_NONE) { out.append("text-decoration: underline; "); } out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; "); // Cell background and font colours final short[] backRGB = style.getFillForegroundColorColor().getTriplet(); final HSSFColor foreColor = palette.getColor(font.getColor()); if (foreColor != null) { final short[] foreRGB = foreColor.getTriplet(); if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) { out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',') .append(foreRGB[2]).append(");"); } } if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) { out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',') .append(backRGB[2]).append(");"); } // Border if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) { out.append("border-top-style: solid; "); } if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) { out.append("border-right-style: solid; "); } if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) { out.append("border-bottom-style: solid; "); } if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) { out.append("border-left-style: solid; "); } out.append("'>"); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } if (pix.containsKey(rowIndex)) { if (pix.get(rowIndex).containsKey(colIndex)) { for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) { out.append("<img alt='Image in Excel sheet' src='data:"); out.append(pic.getMimeType()); out.append(";base64,"); try { out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII")); } catch (final UnsupportedEncodingException e) { throw new RuntimeException(e); } out.append("'/>"); } } } if (isCode(val) && this.URL != null) { val = getHyperLink(val); } out.append(val); out.append("</td>\n"); }