List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCustomPalette
public HSSFPalette getCustomPalette()
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
public static Style getCellStyle(Workbook workbook, Cell cell) { if (cell == null) { return Style.NO_STYLE; }// w ww . j a v a 2 s. co m final CellStyle cellStyle = cell.getCellStyle(); final short fontIndex = cellStyle.getFontIndex(); final Font font = workbook.getFontAt(fontIndex); final StyleBuilder styleBuilder = new StyleBuilder(); // Font bold, italic, underline if (font.getBoldweight() >= Font.BOLDWEIGHT_BOLD) { styleBuilder.bold(); } if (font.getItalic()) { styleBuilder.italic(); } if (font.getUnderline() != FontUnderline.NONE.getByteValue()) { styleBuilder.underline(); } // Font size final Font stdFont = workbook.getFontAt((short) 0); final short fontSize = font.getFontHeightInPoints(); if (stdFont.getFontHeightInPoints() != fontSize) { styleBuilder.fontSize(fontSize, SizeUnit.PT); } // Font color final short colorIndex = font.getColor(); if (font instanceof HSSFFont) { if (colorIndex != HSSFFont.COLOR_NORMAL) { final HSSFWorkbook wb = (HSSFWorkbook) workbook; HSSFColor color = wb.getCustomPalette().getColor(colorIndex); if (color != null) { short[] triplet = color.getTriplet(); styleBuilder.foreground(triplet); } } } else if (font instanceof XSSFFont) { XSSFFont xssfFont = (XSSFFont) font; XSSFColor color = xssfFont.getXSSFColor(); if (color != null) { String argbHex = color.getARGBHex(); if (argbHex != null) { styleBuilder.foreground(argbHex.substring(2)); } } } else { throw new IllegalStateException( "Unexpected font type: " + (font == null ? "null" : font.getClass()) + ")"); } // Background color if (cellStyle.getFillPattern() == 1) { Color color = cellStyle.getFillForegroundColorColor(); if (color instanceof HSSFColor) { short[] triplet = ((HSSFColor) color).getTriplet(); if (triplet != null) { styleBuilder.background(triplet); } } else if (color instanceof XSSFColor) { String argb = ((XSSFColor) color).getARGBHex(); if (argb != null) { styleBuilder.background(argb.substring(2)); } } else { throw new IllegalStateException( "Unexpected color type: " + (color == null ? "null" : color.getClass()) + ")"); } } // alignment switch (cellStyle.getAlignment()) { case CellStyle.ALIGN_LEFT: styleBuilder.leftAligned(); break; case CellStyle.ALIGN_RIGHT: styleBuilder.rightAligned(); break; case CellStyle.ALIGN_CENTER: styleBuilder.centerAligned(); break; case CellStyle.ALIGN_JUSTIFY: styleBuilder.justifyAligned(); break; } return styleBuilder.create(); }
From source file:org.bbreak.excella.core.test.util.TestUtil.java
License:Open Source License
private static String getHSSFColorString(HSSFWorkbook workbook, short index) { HSSFPalette palette = workbook.getCustomPalette(); if (palette.getColor(index) != null) { HSSFColor color = palette.getColor(index); return color.getHexString(); } else {/*ww w . ja v a2s. c om*/ return ""; } }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * HSSF????/*ww w .ja v a 2s .c o m*/ * * @param workbook * @param index * @return HSSF?? */ private static String getHSSFColorString(HSSFWorkbook workbook, short index) { HSSFPalette palette = workbook.getCustomPalette(); if (palette.getColor(index) != null) { HSSFColor color = palette.getColor(index); return color.getHexString(); } else { return ""; } }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private void createReportByTagExport(Workbook workbook) { //handles the export of the execution by tag data HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>(); HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>(); List<String> mapCountries = new ArrayList<String>(); List<CellStyle> stylesList = new LinkedList<CellStyle>(); if (exportOptions.contains("chart") || exportOptions.contains("list")) { //then we need to create the default colors for each cell HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); CellStyle okStyle = workbook.createCellStyle(); // get the color which most closely matches the color you want to use // code to get the style for the cell goes here okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex()); okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //okStyle.setFont(); stylesList.add(okStyle);/* w w w . j ava 2s .c o m*/ } for (TestCaseExecution execution : (List<TestCaseExecution>) list) { //check if the country and application shows if (exportOptions.contains("chart") || exportOptions.contains("summary")) { String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " " + execution.getEnvironment(); SummaryStatisticsDTO stats; String status = execution.getControlStatus(); if (summaryMap.containsKey(keySummaryTable)) { stats = summaryMap.get(keySummaryTable); } else { stats = new SummaryStatisticsDTO(); stats.setApplication(execution.getApplication()); stats.setCountry(execution.getCountry()); stats.setEnvironment(execution.getEnvironment()); } stats.updateStatisticByStatus(status); summaryMap.put(keySummaryTable, stats); //updates the map } if (exportOptions.contains("list")) { if (exportOptions.contains("filter")) { //filter active } else { //all data is saved } HashMap<String, List<TestCaseExecution>> listExecution; List<TestCaseExecution> testCaseList; String testKey = execution.getTest(); String testCaseKey = execution.getTestCase(); if (mapList.containsKey(testKey)) { listExecution = mapList.get(testKey); } else { listExecution = new HashMap<String, List<TestCaseExecution>>(); } if (listExecution.containsKey(testCaseKey)) { testCaseList = listExecution.get(testCaseKey); } else { testCaseList = new ArrayList<TestCaseExecution>(); } testCaseList.add(execution); listExecution.put(testCaseKey, testCaseList); mapList.put(testKey, listExecution); if (mapCountries.indexOf(execution.getCountry()) == -1) { mapCountries.add(execution.getCountry()); } } } int rowCount = -1; //Create a blank sheet Sheet sheet = workbook.createSheet("Report by Tag"); sheet.getPrintSetup().setLandscape(true); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); //ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setColumnWidth(0, 9000); if (exportOptions.contains("chart")) { SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap); Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Report By Status"); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Status"); row.createCell(1).setCellValue("Total"); row.createCell(2).setCellValue("Percentage"); row = sheet.createRow(++rowCount); CellStyle okStyle = stylesList.get(0); Cell cellOk = row.createCell(0); cellOk.setCellValue("OK"); cellOk.setCellStyle(okStyle); row.createCell(1).setCellValue(sumsTotal.getOk()); row.createCell(2).setCellValue(sumsTotal.getPercOk()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("KO"); row.createCell(1).setCellValue(sumsTotal.getKo()); row.createCell(2).setCellValue(sumsTotal.getPercKo()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("FA"); row.createCell(1).setCellValue(sumsTotal.getFa()); row.createCell(2).setCellValue(sumsTotal.getPercFa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NA"); row.createCell(1).setCellValue(sumsTotal.getNa()); row.createCell(2).setCellValue(sumsTotal.getPercNa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NE"); row.createCell(1).setCellValue(sumsTotal.getNe()); row.createCell(2).setCellValue(sumsTotal.getPercNe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("PE"); row.createCell(1).setCellValue(sumsTotal.getPe()); row.createCell(2).setCellValue(sumsTotal.getPercPe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("CA"); row.createCell(1).setCellValue(sumsTotal.getCa()); row.createCell(2).setCellValue(sumsTotal.getPercCa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(sumsTotal.getTotal()); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("summary")) { //draw the table with data Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Summary Table"); //start creating data row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Application"); row.createCell(1).setCellValue("Country"); row.createCell(2).setCellValue("Environment"); row.createCell(3).setCellValue("OK"); row.createCell(4).setCellValue("KO"); row.createCell(5).setCellValue("FA"); row.createCell(6).setCellValue("NA"); row.createCell(7).setCellValue("NE"); row.createCell(8).setCellValue("PE"); row.createCell(9).setCellValue("CA"); row.createCell(10).setCellValue("NOT OK"); row.createCell(11).setCellValue("Total"); /*temporary styles*/ CellStyle styleBlue = workbook.createCellStyle(); CellStyle styleGreen = workbook.createCellStyle(); HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(66, 139, 202); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here styleBlue.setFillForegroundColor(palIndex); styleBlue.setFillPattern(CellStyle.SPARSE_DOTS); HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0); styleGreen.setFillForegroundColor(myColorGreen.getIndex()); styleGreen.setFillPattern(CellStyle.SPARSE_DOTS); int startRow = (rowCount + 2); TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>( summaryMap); for (String key : sortedSummaryMap.keySet()) { row = sheet.createRow(++rowCount); SummaryStatisticsDTO sumStats = summaryMap.get(key); //application row.createCell(0).setCellValue((String) sumStats.getApplication()); //country row.createCell(1).setCellValue((String) sumStats.getCountry()); //environment row.createCell(2).setCellValue((String) sumStats.getEnvironment()); //OK row.createCell(3).setCellValue(sumStats.getOk()); //KO row.createCell(4).setCellValue(sumStats.getKo()); //FA row.createCell(5).setCellValue(sumStats.getFa()); //NA row.createCell(6).setCellValue(sumStats.getNa()); //NE row.createCell(7).setCellValue(sumStats.getNe()); //PE row.createCell(8).setCellValue(sumStats.getPe()); //CA row.createCell(9).setCellValue(sumStats.getCa()); int rowNumber = row.getRowNum() + 1; //NOT OK //row.createCell(11).setCellValue(sumStats.getNotOkTotal()); row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")"); //row.createCell(12).setCellValue(sumStats.getTotal()); if (sumStats.getOk() == sumStats.getTotal()) { for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleGreen); } } } //TODO:FN percentages missing //Total row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(""); row.createCell(2).setCellValue(""); //OK row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")"); //KO row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")"); //FA row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")"); //NA row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")"); //NE row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")"); //PE row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")"); //CA row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")"); int rowNumberTotal = row.getRowNum() + 1; //NOT OK row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")"); for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleBlue); } //add some empty rows sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("list")) { //exports the data from test cases' executions Row r = sheet.createRow(++rowCount); r.createCell(0).setCellValue("Test"); r.createCell(1).setCellValue("Test Case"); r.createCell(2).setCellValue("Description"); r.createCell(3).setCellValue("Application"); r.createCell(4).setCellValue("Environment"); r.createCell(5).setCellValue("Browser"); //creates the country list Collections.sort(mapCountries);//sorts the list of countries int startIndexForCountries = 6; for (String country : mapCountries) { r.createCell(startIndexForCountries).setCellValue(country); startIndexForCountries++; } TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>( mapList); rowCount++; for (String keyMapList : sortedKeys.keySet()) { rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries); } } }
From source file:org.joeffice.spreadsheet.TableStyleable.java
License:Apache License
/** * Add the attribute as defined in {@link AttributedString} to the {@link MutableAttributeSet} for the JTextPane. * * @see java.awt.font.TextAttribute//w w w . j a v a 2s .c o m */ protected void addAttribute(AttributedCharacterIterator.Attribute attribute, Object attributeValue, Cell cell) { CellStyle oldStyle = cell.getCellStyle(); Workbook workbook = cell.getSheet().getWorkbook(); CellStyle style = cell.getSheet().getWorkbook().createCellStyle(); style.cloneStyleFrom(oldStyle); Font newFont = copyFont(cell); if (attribute == FAMILY) { newFont.setFontName((String) attributeValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == FOREGROUND) { Color color = (Color) attributeValue; if (cell instanceof XSSFCell) { ((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(color)); } else { HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook; HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); if (xlsColor == null) { xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); } style.setFillForegroundColor(xlsColor.getIndex()); } style.setFillPattern(CellStyle.SOLID_FOREGROUND); } else if (attribute == BACKGROUND) { Color color = (Color) attributeValue; if (cell instanceof XSSFCell) { ((XSSFCellStyle) style).setFillBackgroundColor(new XSSFColor(color)); } else { HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook; HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); if (xlsColor == null) { xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); } style.setFillBackgroundColor(xlsColor.getIndex()); } } else if (attribute == WEIGHT) { short boldValue = Font.BOLDWEIGHT_BOLD; if (newFont.getBoldweight() == Font.BOLDWEIGHT_BOLD) { boldValue = Font.BOLDWEIGHT_NORMAL; } newFont.setBoldweight(boldValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == UNDERLINE) { byte underlineValue = Font.U_SINGLE; if (newFont.getUnderline() == Font.U_SINGLE) { underlineValue = Font.U_NONE; } newFont.setUnderline(underlineValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == SUPERSCRIPT) { short superscriptValue = Font.SS_NONE; if (SUPERSCRIPT_SUB.equals(attributeValue)) { superscriptValue = Font.SS_SUB; } else if (SUPERSCRIPT_SUPER.equals(attributeValue)) { superscriptValue = Font.SS_SUPER; } newFont.setTypeOffset(superscriptValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == STRIKETHROUGH) { boolean strikeThrough = true; if (newFont.getStrikeout()) { strikeThrough = false; } newFont.setStrikeout(strikeThrough); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == POSTURE) { boolean italic = true; if (newFont.getItalic()) { italic = false; } newFont.setItalic(italic); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == SIZE) { newFont.setFontHeightInPoints(((Number) attributeValue).shortValue()); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == JUSTIFICATION) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_JUSTIFY); } else if (attribute == ALIGNMENT) { if (attributeValue.equals(StyleConstants.ALIGN_LEFT)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_LEFT); } else if (attributeValue.equals(StyleConstants.ALIGN_RIGHT)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_RIGHT); } else if (attributeValue.equals(StyleConstants.ALIGN_CENTER)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER); } } else if (attribute == INDENTATION) { style.setIndention(((Number) attributeValue).shortValue()); } else if (attribute == TEXT_TRANSFORM) { String text = CellUtils.getFormattedText(cell); String transformedText = ((TextTransformer) attributeValue).transformText(text); cell.setCellValue(transformedText); } }
From source file:org.metaeffekt.core.inventory.processor.writer.InventoryWriter.java
License:Apache License
private HSSFCellStyle createHeaderStyle(HSSFWorkbook myWorkBook) { Font headerFont = myWorkBook.createFont(); headerFont.setColor(Font.COLOR_NORMAL); HSSFPalette palette = myWorkBook.getCustomPalette(); HSSFColor headerColor = palette.findSimilarColor((byte) 149, (byte) 179, (byte) 215); HSSFCellStyle headerStyle = myWorkBook.createCellStyle(); headerStyle.setFillForegroundColor(headerColor.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont);//from ww w . j a v a 2s . c om headerStyle.setWrapText(true); return headerStyle; }
From source file:org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java
License:Open Source License
public HSSFColor getColor(byte r, byte g, byte b) { HSSFWorkbook hwb = getHSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); HSSFColor color = palette.findSimilarColor(r, g, b); return color; }
From source file:org.sevenorcas.style.app.mod.ss.SpreadSheet.java
/** * Set a color by substituting an used color * //from w w w. jav a2s . com * Thanks to http://stackoverflow.com/questions/10528516/poi-setting-background-color-to-a-cell * Thanks to http://stackoverflow.com/questions/842817/how-does-java-convert-int-into-byte * * @param workbook * @param r * @param g * @param b * @return */ public HSSFColor setColor(HSSFWorkbook workbook, int r, int g, int b) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; try { byte rb = (byte) (r); byte gb = (byte) (g); byte bb = (byte) (b); hssfColor = palette.findColor(rb, gb, bb); if (hssfColor == null && customColors != null) { short s = customColors.getColorIndex(customColors.getNextIndex()); palette.setColorAtIndex(s, rb, gb, bb); hssfColor = palette.getColor(s); } } catch (Exception e) { } return hssfColor; }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public CellStyle getGlobalExportHeaderStyle(HSSFWorkbook wb) { CellStyle style = createBorderedStyle(wb); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFPalette palette = wb.getCustomPalette(); palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, ExportConstants.GRAY_5_RGB[0], ExportConstants.GRAY_5_RGB[1], ExportConstants.GRAY_5_RGB[2]); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(getItalicFont(wb, (short) 10)); style.setWrapText(true);// w w w. j a va2 s . co m style.setIndention((short) 1); return style; }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public CellStyle getHeaderStyle(HSSFWorkbook wb) { CellStyle style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFPalette palette = wb.getCustomPalette(); palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, ExportConstants.GRAY_10_RGB[0], ExportConstants.GRAY_10_RGB[1], ExportConstants.GRAY_10_RGB[2]); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(getBoldFont(wb, (short) 10)); style.setWrapText(true);//from w w w .jav a 2 s .c o m return style; }