List of usage examples for org.apache.poi.hssf.usermodel HSSFPalette findSimilarColor
public HSSFColor findSimilarColor(int red, int green, int blue)
From source file:com.abacus.reports.ExcelBuilder.java
@Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List headerlist = (List) map.get("header"); List<Object[]> data = (List) map.get("data"); String reportname = String.valueOf(map.get("report_name")); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet(reportname); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls"); OutputStream outStream = response.getOutputStream(); sheet.setDefaultColumnWidth(30);/* w w w . j ava 2 s . c o m*/ // create style for header cells CellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); HSSFPalette palette = workbook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(152, 35, 17); short paindex = color.getIndex(); font.setFontName("Trebuchet MS"); style.setFillForegroundColor(paindex); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); int row = 0; for (Object headerlist1 : headerlist) { header.createCell(row).setCellValue(String.valueOf(headerlist1)); header.getCell(row).setCellStyle(style); row++; } CellStyle style2 = workbook.createCellStyle(); HSSFFont font2 = workbook.createFont(); font2.setFontName("Trebuchet MS"); style2.setFont(font2); System.out.println("data.size(): " + data.size()); int rownum = 1; // create data rows for (int rowCount = 0; rowCount < data.size(); rowCount++) { HSSFRow aRow = sheet.createRow(rownum); Object[] value = data.get(rowCount); int col = 0; for (Object value1 : value) { HSSFCell cell = aRow.createCell(col++); cell.setCellStyle(style2); if (value1 instanceof java.lang.String) cell.setCellValue(String.valueOf(value1)); if (value1 instanceof java.lang.Integer) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Boolean) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Double) cell.setCellValue(Double.parseDouble(String.valueOf(value1))); if (value1 instanceof java.lang.Float) cell.setCellValue(Float.parseFloat(String.valueOf(value1))); } rownum++; } workbook.write(outStream); outStream.close(); }
From source file:com.efficio.fieldbook.service.ExportExcelServiceImpl.java
License:Open Source License
public FileOutputStream exportFieldMapToExcel(String fileName, UserFieldmap userFieldMap) throws FieldbookException { Locale locale = LocaleContextHolder.getLocale(); boolean isTrial = userFieldMap.isTrial(); // Summary of Trial/Nursery, Field and Planting Details String summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.trial", null, locale); //SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS String selectedFieldbookLabel = messageSource.getMessage("fieldmap.trial.selected.trial", null, locale); //Selected Trial: if (!isTrial) { summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.nursery", null, locale); //SUMMARY OF NURSERY, FIELD AND PLANTING DETAILS selectedFieldbookLabel = messageSource.getMessage("fieldmap.nursery.selected.nursery", null, locale); //Selected Nursery: }//from w ww . ja v a 2 s . com String selectedFieldbookValue = userFieldMap.getBlockName(); String orderHeader = messageSource.getMessage("fieldmap.trial.order", null, locale); String studyHeader = messageSource.getMessage((isTrial ? "fieldmap.trial" : "fieldmap.nursery"), null, locale); String instanceHeader = messageSource.getMessage("fieldmap.trial.instance", null, locale); String entriesCountHeader = messageSource.getMessage("fieldmap.trial.entry.count", null, locale); String repsCountHeader = messageSource.getMessage("fieldmap.trial.reps.count", null, locale); String plotsNeededHeader = messageSource.getMessage("fieldmap.trial.plots.needed", null, locale); String totalPlotsHeader = messageSource.getMessage("fieldmap.trial.total.number.of.plots", null, locale); String datasetNameHeader = messageSource.getMessage("fieldmap.nursery.dataset", null, locale); // Field And Block Details String fieldAndBlockDetailsLabel = messageSource.getMessage("fieldmap.trial.field.and.block.details", null, locale); //FIELD AND BLOCK DETAILS String fieldLocationLabel = messageSource.getMessage("fieldmap.label.field.location", null, locale); //Field Location String fieldLocationValue = userFieldMap.getLocationName(); String fieldNameLabel = messageSource.getMessage("fieldmap.label.field.name", null, locale); //Field Name String fieldNameValue = userFieldMap.getFieldName(); String blockNameLabel = messageSource.getMessage("fieldmap.label.block.name", null, locale); //Block Name String blockNameValue = userFieldMap.getBlockName(); // Row, Range & Plot Details String rowRangePlotDetailsLabel = messageSource.getMessage("fieldmap.trial.row.and.range.and.plot.details", null, locale); //ROW, RANGE AND PLOT DETAILS String blockCapacityLabel = messageSource.getMessage("fieldmap.label.block.capacity", null, locale); //Block Capacity String blockCapacityValue = userFieldMap.getBlockCapacityString(messageSource); //e.g. "10 Columns, 10 Ranges" String rowsPerPlotLabel = messageSource.getMessage("fieldmap.label.rows.per.plot", null, locale); //Rows per Plot int rowsPerPlotValue = userFieldMap.getNumberOfRowsPerPlot(); String columnsLabel = messageSource.getMessage("fieldmap.label.columns", null, locale); //Columns Integer columnsValue = userFieldMap.getNumberOfColumnsInBlock(); // 10 String machineCapacityLabel = messageSource.getMessage("fieldmap.label.row.capacity.machine", null, locale); //machine row capacity Integer machineCapacityValue = userFieldMap.getMachineRowCapacity(); //Planting Details String plantingDetailsLabel = messageSource.getMessage("fieldmap.header.planting.details", null, locale); //PLANTING DETAILS String startingCoordinatesLabel = messageSource.getMessage("fieldmap.label.starting.coordinates", null, locale); //Starting Coordinates String startingCoordinatesValue = userFieldMap.getStartingCoordinateString(messageSource); // Column 1, Range 1 String plantingOrderLabel = messageSource.getMessage("fieldmap.label.planting.order", null, locale); //Planting Order String plantingOrderValue = userFieldMap.getPlantingOrderString(messageSource); //"Row/Column" or "Serpentine" // FieldMap String fieldMapLabel = messageSource.getMessage("fieldmap.header.fieldmap", null, locale); //FIELD MAP String rowsLabel = messageSource.getMessage("fieldmap.label.rows", null, locale); //Rows String columnLabel = messageSource.getMessage("fieldmap.label.capitalized.column", null, locale); //Column String rangeLabel = messageSource.getMessage("fieldmap.label.capitalized.range", null, locale); //Range try { //Create workbook HSSFWorkbook workbook = new HSSFWorkbook(); String summaryLabelSheet = messageSource.getMessage("fieldmap.header.excel.summary", null, locale); Sheet summarySheet = workbook.createSheet(summaryLabelSheet); Sheet fieldMapSheet = workbook.createSheet(fieldMapLabel); CellStyle labelStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); labelStyle.setFont(font); CellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); wrapStyle.setAlignment(CellStyle.ALIGN_CENTER); CellStyle mainHeaderStyle = workbook.createCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(179, 165, 165); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here mainHeaderStyle.setFillForegroundColor(palIndex); mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); CellStyle mainSubHeaderStyle = workbook.createCellStyle(); HSSFPalette paletteSubHeader = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186); // get the palette index of that color short palIndexSubHeader = myColorSubHeader.getIndex(); // code to get the style for the cell goes here mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader); mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); int rowIndex = 0; int columnIndex = 0; // Create Header Information // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS CellStyle headerLabelStyle = workbook.createCellStyle(); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerLabelStyle.setFont(font); headerLabelStyle.setAlignment(CellStyle.ALIGN_CENTER); Row row = summarySheet.createRow(rowIndex++); Cell summaryCell = row.createCell(columnIndex); summaryCell.setCellValue(summaryOfFieldbookFieldPlantingDetailsLabel); summaryCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex, //first column (0-based) columnIndex + 5 //last column (0-based) )); // Row 2: Space row = summarySheet.createRow(rowIndex++); // Row 3: Fieldbook Name, Entries, Reps, Plots row = summarySheet.createRow(rowIndex++); // Selected Trial : [Fieldbook Name] TABLE SECTION Cell labelCell = row.createCell(columnIndex++); labelCell.setCellValue(selectedFieldbookLabel); row = summarySheet.createRow(rowIndex++); columnIndex = 0; Cell headerCell = row.createCell(columnIndex++); headerCell.setCellValue(orderHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(studyHeader); headerCell.setCellStyle(labelStyle); if (isTrial) { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(instanceHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(entriesCountHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(repsCountHeader); headerCell.setCellStyle(labelStyle); } else { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(datasetNameHeader); headerCell.setCellStyle(labelStyle); } headerCell = row.createCell(columnIndex++); headerCell.setCellValue(plotsNeededHeader); headerCell.setCellStyle(labelStyle); for (SelectedFieldmapRow rec : userFieldMap.getSelectedFieldmapList().getRows()) { row = summarySheet.createRow(rowIndex++); columnIndex = 0; row.createCell(columnIndex++).setCellValue(rec.getOrder()); row.createCell(columnIndex++).setCellValue(rec.getStudyName()); if (isTrial) { row.createCell(columnIndex++).setCellValue(rec.getTrialInstanceNo()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getRepCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getPlotCount())); } else { row.createCell(columnIndex++).setCellValue(rec.getDatasetName()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); } } row = summarySheet.createRow(rowIndex++); columnIndex = 0; headerCell = row.createCell(columnIndex++); headerCell.setCellValue(totalPlotsHeader); headerCell.setCellStyle(labelStyle); row.createCell(columnIndex++) .setCellValue(String.valueOf(userFieldMap.getSelectedFieldmapList().getTotalNumberOfPlots())); // Row 4: Space row = summarySheet.createRow(rowIndex++); // Row 5: Header - Details Heading row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldAndBlockDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowRangePlotDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); //Row 6: Field Location, Block Capacity, Starting Coordinates row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldLocationLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldLocationValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockCapacityValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(startingCoordinatesLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(startingCoordinatesValue); // Row 7: Field Name, Rows Per Plot, Planting Order row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowsPerPlotLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(rowsPerPlotValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingOrderLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(plantingOrderValue); // Row 8: Block Name, Columns row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(columnsLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(columnsValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(machineCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(machineCapacityValue)); // Row 9: Space row = summarySheet.createRow(rowIndex++); for (int columnsResize = 0; columnsResize < columnIndex; columnsResize++) { summarySheet.autoSizeColumn(columnsResize); } // Get FieldMap data //we reset the row index rowIndex = 0; // Row 10: FIELD MAP row = fieldMapSheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldMapLabel); labelCell.setCellStyle(labelStyle); // Row 11: Space row = fieldMapSheet.createRow(rowIndex++); Plot[][] plots = userFieldMap.getFieldmap(); int range = userFieldMap.getNumberOfRangesInBlock(); int col = userFieldMap.getNumberOfColumnsInBlock(); int rowsPerPlot = userFieldMap.getNumberOfRowsPerPlot(); int machineRowCapacity = userFieldMap.getMachineRowCapacity(); int rows = userFieldMap.getNumberOfRowsInBlock(); boolean isSerpentine = userFieldMap.getPlantingOrder() == 2; for (int j = range - 1; j >= 0; j--) { if (j == range - 1) { // TOP TABLE LABELS // Row 12: Rows Header rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); // Row 13: UP, DOWN Direction rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); // Row 14: Column labels rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); } // Rows 15 onwards: Ranges and Row Data row = fieldMapSheet.createRow(rowIndex); row.setHeightInPoints(45); columnIndex = 0; int rangeValue = j + 1; Cell cellRange = row.createCell(columnIndex++); cellRange.setCellValue(rangeLabel + " " + rangeValue); cellRange.setCellStyle(mainSubHeaderStyle); for (int i = 0; i < col; i++) { String displayString = plots[i][j].getDisplayString().replace("<br/>", "\n"); if (plots[i][j].isPlotDeleted()) { displayString = " X "; } Cell dataCell = row.createCell(columnIndex++); //dataCell.setCellValue(displayString); dataCell.setCellValue(new HSSFRichTextString(displayString)); dataCell.setCellStyle(wrapStyle); //row.createCell(columnIndex).setCellValue(""); for (int k = 0; k < rowsPerPlot - 1; k++) { row.createCell(columnIndex++).setCellValue(""); } fieldMapSheet.addMergedRegion(new CellRangeAddress(rowIndex, //first row (0-based) rowIndex, //last row (0-based) columnIndex - rowsPerPlot, //first column (0-based) columnIndex - 1 //last column (0-based) )); //columnIndex++; } rowIndex++; if (j == 0) { // BOTTOM TABLE LABELS rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); } } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); workbook.write(fileOutputStream); fileOutputStream.close(); return fileOutputStream; } catch (FileNotFoundException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } catch (IOException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } }
From source file:com.efficio.fieldbook.service.LabelPrintingServiceImpl.java
License:Open Source License
@Override public String generateXlSLabels(List<StudyTrialInstanceInfo> trialInstances, UserLabelPrinting userLabelPrinting, ByteArrayOutputStream baos) throws MiddlewareQueryException { int pageSizeId = Integer.parseInt(userLabelPrinting.getSizeOfLabelSheet()); int numberOfLabelPerRow = Integer.parseInt(userLabelPrinting.getNumberOfLabelPerRow()); int numberofRowsPerPageOfLabel = Integer.parseInt(userLabelPrinting.getNumberOfRowsPerPageOfLabel()); int totalPerPage = numberOfLabelPerRow * numberofRowsPerPageOfLabel; String leftSelectedFields = userLabelPrinting.getLeftSelectedLabelFields(); String rightSelectedFields = userLabelPrinting.getRightSelectedLabelFields(); String barcodeNeeded = userLabelPrinting.getBarcodeNeeded(); String firstBarcodeField = userLabelPrinting.getFirstBarcodeField(); String secondBarcodeField = userLabelPrinting.getSecondBarcodeField(); String thirdBarcodeField = userLabelPrinting.getThirdBarcodeField(); String currentDate = DateUtil.getCurrentDate(); //String fileName = currentDate + ".xls"; String fileName = userLabelPrinting.getFilenameDLLocation(); try {//from w w w .j a v a2 s. com HSSFWorkbook workbook = new HSSFWorkbook(); String sheetName = cleanSheetName(userLabelPrinting.getName()); if (sheetName == null) sheetName = "Labels"; Sheet labelPrintingSheet = workbook.createSheet(sheetName); CellStyle labelStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); labelStyle.setFont(font); CellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); wrapStyle.setAlignment(CellStyle.ALIGN_CENTER); CellStyle mainHeaderStyle = workbook.createCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(179, 165, 165); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here mainHeaderStyle.setFillForegroundColor(palIndex); mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); CellStyle mainSubHeaderStyle = workbook.createCellStyle(); HSSFPalette paletteSubHeader = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186); // get the palette index of that color short palIndexSubHeader = myColorSubHeader.getIndex(); // code to get the style for the cell goes here mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader); mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); int rowIndex = 0; int columnIndex = 0; // Create Header Information // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS Row row = labelPrintingSheet.createRow(rowIndex++); //we add all the selected fields header StringTokenizer token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } //we populate the info now int i = 0; for (StudyTrialInstanceInfo trialInstance : trialInstances) { FieldMapTrialInstanceInfo fieldMapTrialInstanceInfo = trialInstance.getTrialInstance(); Map<String, String> moreFieldInfo = new HashMap<String, String>(); moreFieldInfo.put("locationName", fieldMapTrialInstanceInfo.getLocationName()); moreFieldInfo.put("blockName", fieldMapTrialInstanceInfo.getBlockName()); moreFieldInfo.put("selectedName", trialInstance.getFieldbookName()); moreFieldInfo.put("trialInstanceNumber", fieldMapTrialInstanceInfo.getTrialInstanceNo()); for (FieldMapLabel fieldMapLabel : fieldMapTrialInstanceInfo.getFieldMapLabels()) { row = labelPrintingSheet.createRow(rowIndex++); columnIndex = 0; i++; token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String leftText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(leftText); //summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String rightText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(rightText); //summaryCell.setCellStyle(labelStyle); } } } for (int columnPosition = 0; columnPosition < columnIndex; columnPosition++) { labelPrintingSheet.autoSizeColumn((short) (columnPosition)); } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); //workbook.write(baos); workbook.write(fileOutputStream); fileOutputStream.close(); //return fileOutputStream; } catch (Exception e) { LOG.error(e.getMessage(), e); } return fileName; }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java
License:Open Source License
private CellStyle getHeaderStyle(HSSFWorkbook xlsBook, int c1, int c2, int c3) { HSSFPalette palette = xlsBook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(c1, c2, c3); short colorIndex = color.getIndex(); HSSFFont whiteFont = xlsBook.createFont(); whiteFont.setColor(new HSSFColor.WHITE().getIndex()); CellStyle cellStyle = xlsBook.createCellStyle(); cellStyle.setFillForegroundColor(colorIndex); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFont(whiteFont);//from ww w. j a va 2 s. c o m return cellStyle; }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
protected short colorToIndex(Workbook wb, Color color) { if (color == null) { return 0; }/*from w w w .j a v a 2 s . c om*/ if (Color.black.equals(color)) { return IndexedColors.BLACK.getIndex(); } if (Color.white.equals(color)) { return IndexedColors.WHITE.getIndex(); } if (Color.blue.equals(color) || Color.blue.darker().equals(color) || Color.blue.brighter().equals(color)) { return IndexedColors.BLUE.getIndex(); } if (Color.red.equals(color) || Color.red.darker().equals(color) || Color.red.brighter().equals(color)) { return IndexedColors.RED.getIndex(); } if (Color.LIGHT_GRAY.equals(color)) { return IndexedColors.GREY_25_PERCENT.getIndex(); } if (Color.GRAY.equals(color)) { return IndexedColors.GREY_50_PERCENT.getIndex(); } if (Color.DARK_GRAY.equals(color)) { return IndexedColors.GREY_80_PERCENT.getIndex(); } if (Color.green.equals(color) || Color.green.brighter().equals(color) || Color.green.darker().equals(color)) { return IndexedColors.GREEN.getIndex(); } if (Color.magenta.equals(color) || Color.magenta.darker().equals(color) || Color.magenta.brighter().equals(color)) { return IndexedColors.MAROON.getIndex(); } if (Color.orange.equals(color) || Color.orange.darker().equals(color) || Color.orange.brighter().equals(color)) { return IndexedColors.ORANGE.getIndex(); } if (Color.pink.equals(color) || Color.pink.darker().equals(color) || Color.pink.brighter().equals(color)) { return IndexedColors.PINK.getIndex(); } if (Color.yellow.equals(color) || Color.yellow.darker().equals(color) || Color.yellow.brighter().equals(color)) { return IndexedColors.YELLOW.getIndex(); } byte r = (byte) color.getRed(); byte g = (byte) color.getGreen(); byte b = (byte) color.getBlue(); HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor hssColor = palette.findColor(r, g, b); try { if (hssColor == null) { hssColor = palette.addColor(r, g, b); } return hssColor.getIndex(); } catch (RuntimeException e) { hssColor = palette.findSimilarColor(r, g, b); return hssColor != null ? hssColor.getIndex() : 0; } }
From source file:org.apache.metamodel.excel.ExcelUpdateCallback.java
License:Apache License
public short getColorIndex(Color color) { Workbook workbook = getWorkbook(true); if (workbook instanceof HSSFWorkbook) { HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette(); byte r = toRgb(color.getRed()); byte g = toRgb(color.getGreen()); byte b = toRgb(color.getBlue()); HSSFColor index = palette.findColor(r, g, b); if (index == null) { index = palette.findSimilarColor(r, g, b); }/*from ww w . j a va2 s.c o m*/ return index.getIndex(); } throw new IllegalStateException("Unexpected workbook type: " + workbook.getClass()); }
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);/*from www. j a v a 2s.com*/ } 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.kurator.validation.actors.io.AnalysisSpreadsheetBuilder.java
License:Open Source License
/** * Initialize styles to be used when generating the spreadsheet. Each background color maps to * a particular validation state (CORRECT, CURATED, UNABLE_CURATE, UNABLE_DETERMINE_VALIDITY, etc) *///from www . ja v a2 s . com private void initStyles() { HSSFPalette palette = wb.getCustomPalette(); HSSFColor red = palette.findSimilarColor(255, 145, 145); HSSFColor green = palette.findSimilarColor(156, 255, 153); HSSFColor yellow = palette.findSimilarColor(255, 248, 153); HSSFColor yellow4 = palette.findSimilarColor(230, 230, 76); HSSFColor grey = palette.findSimilarColor(204, 204, 204); HSSFColor sun4 = palette.findSimilarColor(204, 204, 255); HSSFCellStyle unableCurateCellStyle = wb.createCellStyle(); unableCurateCellStyle.setFillForegroundColor(red.getIndex()); unableCurateCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle correctCellStyle = wb.createCellStyle(); correctCellStyle.setFillForegroundColor(green.getIndex()); correctCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle curatedCellStyle = wb.createCellStyle(); curatedCellStyle.setFillForegroundColor(yellow.getIndex()); curatedCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle filledinCellStyle = wb.createCellStyle(); filledinCellStyle.setFillForegroundColor(yellow4.getIndex()); filledinCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle unableDetermineValidityCellStyle = wb.createCellStyle(); unableDetermineValidityCellStyle.setFillForegroundColor(grey.getIndex()); unableDetermineValidityCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); validationStateStyles = new HashMap<String, HSSFCellStyle>(); validationStateStyles.put("UNABLE_DETERMINE_VALIDITY", unableDetermineValidityCellStyle); validationStateStyles.put("CURATED", curatedCellStyle); validationStateStyles.put("CORRECT", correctCellStyle); validationStateStyles.put("FILLED_IN", filledinCellStyle); validationStateStyles.put("UNABLE_CURATE", unableCurateCellStyle); }
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 w w w .j a v a 2 s . 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; }