List of usage examples for org.apache.poi.hssf.util HSSFColor getIndex
public short getIndex()
From source file:br.com.tecsinapse.dataio.Table.java
License:LGPL
public HSSFColor newCustomColor(HSSFColor replaceColor, Color newColor) { HSSFColor hssfColor = new CustomColor(replaceColor.getIndex(), newColor); colorsReplaceMap.put(replaceColor, hssfColor); return hssfColor; }
From source file:cn.afterturn.easypoi.excel.html.css.impl.BorderCssConverImpl.java
License:Apache License
@Override public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) { if (style == null || style.getBorder() == null) { return;/*from w w w . j a va 2 s . com*/ } CellStyleBorderEntity border = style.getBorder(); for (String pos : new String[] { TOP, RIGHT, BOTTOM, LEFT }) { String posName = StringUtils.capitalize(pos.toLowerCase()); // color String colorAttr = null; try { colorAttr = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Color"); } catch (Exception e) { log.error("Set Border Style Error Caused.", e); } if (StringUtils.isNotEmpty(colorAttr)) { if (cell instanceof HSSFCell) { HSSFColor poiColor = PoiCssUtils.parseColor((HSSFWorkbook) cell.getSheet().getWorkbook(), colorAttr); if (poiColor != null) { try { MethodUtils.invokeMethod(cellStyle, "set" + posName + "BorderColor", poiColor.getIndex()); } catch (Exception e) { log.error("Set Border Color Error Caused.", e); } } } if (cell instanceof XSSFCell) { XSSFColor poiColor = PoiCssUtils.parseColor(colorAttr); if (poiColor != null) { try { MethodUtils.invokeMethod(cellStyle, "set" + posName + "BorderColor", poiColor); } catch (Exception e) { log.error("Set Border Color Error Caused.", e); } } } } // width int width = 0; try { String widthStr = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Width"); if (PoiCssUtils.isNum(widthStr)) { width = Integer.parseInt(widthStr); } } catch (Exception e) { log.error("Set Border Style Error Caused.", e); } String styleValue = null; try { styleValue = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Style"); } catch (Exception e) { log.error("Set Border Style Error Caused.", e); } BorderStyle shortValue = BorderStyle.NONE; // empty or solid if (StringUtils.isBlank(styleValue) || "solid".equals(styleValue)) { if (width > 2) { shortValue = BorderStyle.THICK; } else if (width > 1) { shortValue = BorderStyle.MEDIUM; } else { shortValue = BorderStyle.THIN; } } else if (ArrayUtils.contains(new String[] { NONE, HIDDEN }, styleValue)) { shortValue = BorderStyle.NONE; } else if (DOUBLE.equals(styleValue)) { shortValue = BorderStyle.DOUBLE; } else if (DOTTED.equals(styleValue)) { shortValue = BorderStyle.DOTTED; } else if (DASHED.equals(styleValue)) { if (width > 1) { shortValue = BorderStyle.MEDIUM_DASHED; } else { shortValue = BorderStyle.DASHED; } } // border style if (shortValue != BorderStyle.NONE) { try { MethodUtils.invokeMethod(cellStyle, "setBorder" + posName, shortValue); } catch (Exception e) { log.error("Set Border Style Error Caused.", e); } } } }
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 . ja v a2 s .co 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.canoo.webtest.plugins.exceltest.ExcelColorUtils.java
License:Open Source License
public static String getColorName(final AbstractExcelStep step, final Color color) { if (color == null) { return "none"; }//from w w w . ja v a 2 s.co m if (color instanceof HSSFColor) { HSSFColor hssfcolor = (HSSFColor) color; if (hssfcolor.getIndex() == AUTOMATIC_COLOR) { return "auto"; } final short[] triplet = hssfcolor.getTriplet(); final String colorString = "#" + toHex(triplet[0]) + toHex(triplet[1]) + toHex(triplet[2]); return lookupStandardColorName(colorString); } else { XSSFColor xssfcolor = (XSSFColor) color; if (xssfcolor.isAuto()) { return "auto"; } final byte[] triplet = xssfcolor.getRgb(); final String colorString = "#" + toHex(triplet[0]) + toHex(triplet[1]) + toHex(triplet[2]); return lookupStandardColorName(colorString); } }
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 w w . ja va 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 {// w w w.j a va 2 s.co m 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 w w w. jav a 2 s.c o m return cellStyle; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
private static short findColor(short index, HSSFWorkbook srcwb, HSSFWorkbook destwb) { Integer id = new Integer(index); if (HSSFColor.getIndexHash().containsKey(id)) return index; if (index == HSSFColor.AUTOMATIC.index) return index; HSSFColor color = srcwb.getCustomPalette().getColor(index); if (color == null) { return index; }//from www . ja v a 2 s . c o m HSSFColor ncolor = destwb.getCustomPalette().findColor((byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); if (ncolor != null) return ncolor.getIndex(); destwb.getCustomPalette().setColorAtIndex(index, (byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); return index; }
From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiStyleHelper.java
License:Open Source License
/** * @param workbook/*from w w w. j a v a 2 s .co m*/ * workbook for conversion * @param wrapperCellStyle * the cell style * @return the internal cell style */ public CellStyle toCellStyle(final Workbook workbook, final WrapperCellStyle wrapperCellStyle) { final CellStyle cellStyle = workbook.createCellStyle(); final Font font = this.fontHelper.toCellFont(workbook, wrapperCellStyle); cellStyle.setFont(font); final WrapperColor backgroundColor = wrapperCellStyle.getBackgroundColor(); if (backgroundColor != null) { final HSSFColor hssfColor = this.colorHelper.toHSSFColor(backgroundColor); final short index = hssfColor.getIndex(); cellStyle.setFillForegroundColor(index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); } this.borderHelper.setCellBorders(wrapperCellStyle, cellStyle); return cellStyle; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getFilledTemplate(String filePath, int templateId, int flag, String logicalLSI) throws IOESException, ParseException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); String fileName;//from w w w.ja va2 s .co m HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList filledTemplateData = new ArrayList(); ArrayList<ViewOrderDto> filledTemplateDataLineDetails = new ArrayList<ViewOrderDto>(); ArrayList<ViewOrderDto> filledTemplateDataChargeDetails = new ArrayList<ViewOrderDto>(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ViewOrderModel objviewmodel = new ViewOrderModel(); //ErrorLogDto dtoObj ; TransactionTemplateDto dtoObj; ViewOrderDto dtoObj1; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); int totalRowsOfSheet = 0; ViewOrderDto objdto = null; String str = null; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); HSSFCellStyle whiteFG_yellow = wb.createCellStyle(); HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00); //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index); whiteFG_yellow.setFillBackgroundColor(yellow.getIndex()); HSSFCellStyle whiteFG_green = wb.createCellStyle(); HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00); //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index); whiteFG_green.setFillBackgroundColor(green.getIndex()); //System.out.println(filledTemplateData.toString()); //System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); if (s == 0 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0; totalRowsOfSheetMain = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(dtoObj.getOrderNo()); wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID()))); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId()))); } ctr++; } totalRowsOfSheetAtt = objDao.getTotalRowsOfSheet(11, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(11, templateId, flag, logicalLSI); ctr = 0; for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { wr = ws.getRow(r); dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); if (wr != null) { wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getRFSDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getActMngrPhoneNo()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getActMngrEmailID()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getIRUOrderYN()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getFreePeriodYN()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getOrdExclusiveTax()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getCAFDate()))); } ctr++; } } //GAM Sheet if (s == 1 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { //to do nothing } //Contact Sheet if (s == 2 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getContactType()); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getSalutation()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getFirstName()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLastName()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getEmail()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getCellno()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getFaxno()))); wr.createCell(10).setCellValue(rownum); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getAddress1()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAddress2()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getAddress3()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getCountrycode()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getStateid()))); wr.createCell(16).setCellValue(new HSSFRichTextString((dtoObj.getCityid()))); wr.createCell(17).setCellValue(new HSSFRichTextString((dtoObj.getPincode()))); } ctr++; rownum++; } } if (s == 3 && (templateId == 1 || templateId == 22 || templateId == 21)) { int ctr = 0, rownum = 1; if (templateId == 21) { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getServiceName()))); wr.createCell(6).setCellValue(dtoObj.getLineItemID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLineItemName()))); } ctr++; rownum++; } } else { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); } ctr++; rownum++; } } } if (s == 3 && templateId == 41) { /*int ctr=0,rownum=1; totalRowsOfSheet=objDao.getTotalRowsOfSheet(s+1,templateId,flag,logicalLSI); filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag,logicalLSI); for(int r = 2; r <= (totalRowsOfSheet+1); r++) { dtoObj = (TransactionTemplateDto)filledTemplateData.get(ctr); wr=ws.createRow(r); if(wr!=null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getCustPONumber()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCustPODate()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getIsDefaultPO()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntity()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getTotalPOAmount()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getPeriodInMonths()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getContractStartDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getContractEndDate()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getPoRemarks()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getPoEmailId()))); } ctr++;rownum++; }*/ } if (s == 4 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); wr.createCell(6).setCellValue(dtoObj.getChargeID()); wr.createCell(7).setCellValue(dtoObj.getChargeAmount()); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getChargeFrequency()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeType()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeName()))); wr.createCell(11).setCellValue(dtoObj.getFrequncyAmount()); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAnnotation()))); } ctr++; rownum++; } } if (s == 5 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(new HSSFRichTextString("")); wr.createCell(3).setCellValue(dtoObj.getLineItemID()); wr.createCell(4).setCellValue(dtoObj.getCreditPeriodID()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getCreditPeriodName()))); wr.createCell(6).setCellValue(dtoObj.getLegealEntityID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntityName()))); wr.createCell(8).setCellValue(dtoObj.getLicenseCompanyID()); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getLicenseCompanyName()))); wr.createCell(10).setCellValue(dtoObj.getBillingModeID()); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getBillingModeName()))); wr.createCell(12).setCellValue(dtoObj.getBillingFormatID()); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getBillingFormatName()))); wr.createCell(14).setCellValue(dtoObj.getBillingTypeID()); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getBillingTypeName()))); wr.createCell(16).setCellValue(dtoObj.getTaxationID()); wr.createCell(17).setCellValue(new HSSFRichTextString(dtoObj.getTaxationName())); wr.createCell(18).setCellValue(dtoObj.getBillingLevelID()); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj.getBillingLevelName())); wr.createCell(20).setCellValue(dtoObj.getNoticePeriod()); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj.getPenaltyClause())); wr.createCell(22).setCellValue(dtoObj.getCommitPeriod()); wr.createCell(23).setCellValue(dtoObj.getIsNfa()); wr.createCell(24).setCellValue(dtoObj.getBcpID()); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj.getBcpName())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonId())); wr.createCell(27).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonName())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 start if (s == 0 && templateId == 61) { int ctr = 0, rownum = 1; filledTemplateDataLineDetails = objviewmodel.getFilledTemplateforBillingLineSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataLineDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataLineDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { HSSFCell cell0 = wr.createCell(0); cell0.setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineName())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getLogicalSino())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getCustLogicalSino())); wr.createCell(5).setCellValue(dtoObj1.getOrderno()); wr.createCell(6).setCellValue(new HSSFRichTextString(dtoObj1.getOrdertype())); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getOrder_subtype())); wr.createCell(8).setCellValue(new HSSFRichTextString(dtoObj1.getSiid())); wr.createCell(9).setCellValue(new HSSFRichTextString(dtoObj1.getAccountid())); wr.createCell(10).setCellValue(new HSSFRichTextString(dtoObj1.getFx_status())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getLine_status())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_No())); wr.createCell(13).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_date())); validateDto = dtoObj1.getBillingTriggerAllowDenyLogic(); HSSFCell cell14 = wr.createCell(14); cell14.setCellValue(new HSSFRichTextString(dtoObj1.getLocNo())); HSSFCellStyle sty = ws.getRow(1).getCell(14).getCellStyle(); if ("allow".equals(validateDto.getLocNoForEdit())) { cell14.setCellStyle(sty); } else { cell14.setCellStyle(sty1); } HSSFCell cell15 = wr.createCell(15); if (!(dtoObj1.getLocDate() == null || "".equals(dtoObj1.getLocDate()))) { cell15.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocDate())))); } else { cell15.setCellValue(new HSSFRichTextString(dtoObj1.getLocDate())); } if ("allow".equals(validateDto.getLocDateForEdit())) { cell15.setCellStyle(sty); } else { cell15.setCellStyle(sty1); } HSSFCell cell16 = wr.createCell(16); if (!(dtoObj1.getLocRecDate() == null || "".equals(dtoObj1.getLocRecDate()))) { cell16.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocRecDate())))); } else { cell16.setCellValue(new HSSFRichTextString(dtoObj1.getLocRecDate())); } if ("allow".equals(validateDto.getLocRecDateForEdit())) { cell16.setCellStyle(sty); } else { cell16.setCellStyle(sty1); } HSSFCell cell17 = wr.createCell(17); if (!(dtoObj1.getBillingTriggerDate() == null || "".equals(dtoObj1.getBillingTriggerDate()))) { cell17.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getBillingTriggerDate())))); } else { cell17.setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerDate())); } if ("allow".equals(validateDto.getBtdForEdit())) { cell17.setCellStyle(sty); } else { cell17.setCellStyle(sty1); } wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerProcess())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 end //billing trigger bulkupload sheet 2 start if (s == 1 && templateId == 61) { int ctr = 0, rownum = 1; if (filledTemplateDataLineDetails.size() > 0) { filledTemplateDataChargeDetails = objviewmodel .getFilledTemplateforBillingChargeSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataChargeDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataChargeDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(0).setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(dtoObj1.getChargeInfoId()); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getChargeType())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getChargeName())); wr.createCell(5).setCellValue(dtoObj1.getChargePeriod()); wr.createCell(6).setCellValue(dtoObj1.getChargeAmt()); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getChargeStatus())); HSSFCell cell8 = wr.createCell(8); HSSFCellStyle sty8 = ws.getRow(1).getCell(8).getCellStyle(); if (!(dtoObj1.getDisconnectiondate() == null || "".equals(dtoObj1.getDisconnectiondate()))) { cell8.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getDisconnectiondate())))); } else { cell8.setCellValue(new HSSFRichTextString(dtoObj1.getDisconnectiondate())); } if ("Changed".equalsIgnoreCase(dtoObj1.getChargeStatus())) { cell8.setCellStyle(sty8); } wr.createCell(9) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeCreatedOnOrder())); wr.createCell(10) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndedOnOrder())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getBillPeriod())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getStartdatelogic())); wr.createCell(13).setCellValue(dtoObj1.getStart_date_days()); wr.createCell(14).setCellValue(dtoObj1.getStart_date_month()); wr.createCell(15).setCellValue(new HSSFRichTextString(dtoObj1.getEnddatelogic())); wr.createCell(16).setCellValue(dtoObj1.getEnd_date_days()); wr.createCell(17).setCellValue(dtoObj1.getEnd_date_month()); wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndDate_String())); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj1.getAnnualRate())); wr.createCell(20).setCellValue(new HSSFRichTextString(dtoObj1.getAnnotation())); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj1.getStartTokenNo())); wr.createCell(22).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxStatus())); wr.createCell(23).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxNo())); wr.createCell(24).setCellValue(new HSSFRichTextString(dtoObj1.getEndTokenNo())); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxStatus())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxNo())); wr.createCell(27) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeStartStatus())); wr.createCell(28) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndStatus())); wr.createCell(29) .setCellValue(new HSSFRichTextString(dtoObj1.getChargefrequency())); wr.createCell(30).setCellValue(new HSSFRichTextString(dtoObj1.getFxViewId())); } ctr++; rownum++; } } } // billing trigger bulkupload sheet 2 end } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }