List of usage examples for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD
short BOLDWEIGHT_BOLD
To view the source code for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD.
Click Source Link
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel/* ww w .j a v a 2 s . c o m*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "D" + "P" + "2" + "3" + "4" + "5")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
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 ww w . ja v a 2 s.co m 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 a 2 s . c o 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.emi.loan.util.Utilities.java
public static void exportTOExcel(DefaultTableModel dtm, Map<String, String> ln_info) { FileOutputStream out = null;/* w w w . j a v a2 s .c o m*/ try { Workbook wb = new HSSFWorkbook(); // CreationHelper createhelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("EMI TABLE"); Row row; Cell cell; File file = chooseFile(); out = new FileOutputStream(file); HSSFFont headerFont = (HSSFFont) wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setFontName("CENTURY GOTHIC"); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.WHITE.index); HSSFFont infoFont = (HSSFFont) wb.createFont(); infoFont.setFontHeightInPoints((short) 14); infoFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // HSSFFont font = (HSSFFont) wb.createFont(); // font.setFontHeightInPoints((short) 10); // font.setFontName("CENTURY GOTHIC"); // font.setColor(HSSFColor.BLACK.index); CellStyle defaultStyle = wb.createCellStyle(); defaultStyle.setFillForegroundColor(HSSFColor.AQUA.index); defaultStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); defaultStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); defaultStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY); defaultStyle.setFont(headerFont); CellStyle borderStyle = wb.createCellStyle(); borderStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); borderStyle.setFont(infoFont); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(defaultStyle); cell.setCellValue("Loan Amount(Rs.)"); cell = row.createCell(1); cell.setCellStyle(borderStyle); cell.setCellValue(Double.parseDouble(ln_info.get("Loan Amount"))); row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(defaultStyle); cell.setCellValue("Interest %"); cell = row.createCell(1); cell.setCellStyle(borderStyle); cell.setCellValue(Double.parseDouble(ln_info.get("Interest"))); row = sheet.createRow(5); cell = row.createCell(0); cell.setCellStyle(defaultStyle); cell.setCellValue("Period (months)"); cell = row.createCell(1); cell.setCellStyle(borderStyle); cell.setCellValue(Integer.parseInt(ln_info.get("Period"))); for (int i = 0; i <= dtm.getRowCount(); i++) { row = sheet.createRow(i + 8); for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j); if (i == 0) { // writing the column headers cell.setCellStyle(defaultStyle); cell.setCellValue(dtm.getColumnName(j)); } else if (j == 0 || j == 5) { cell.setCellValue(Integer.parseInt(dtm.getValueAt(i - 1, j).toString())); } else { cell.setCellValue(Double.parseDouble(dtm.getValueAt(i - 1, j).toString())); } } } row = sheet.createRow(dtm.getRowCount() + 12); cell = row.createCell(0); cell.setCellValue("-- END OF REPORT --"); for (int j = 0; j < dtm.getColumnCount(); j++) { sheet.autoSizeColumn(j, true); } wb.write(out); } catch (FileNotFoundException ex) { System.out.println("File not Found"); Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { System.out.println("IOException"); Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex); } finally { try { out.close(); } catch (IOException ex) { Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * /* w w w.ja v a2 s . com*/ * * @param FilePath * @param companyList * @return */ public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow0 = sheet.createRow(0); HSSFCell headCell = headRow0.createCell(0); // ?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ??? headCell = headRow0.createCell(0); // headCell.setCellValue(model.getTitle()); // ? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREEN.index); style.setAlignment(CellStyle.ALIGN_CENTER);// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// // HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // style.setFont(font); headCell.setCellStyle(style); // ? ?? HSSFRow RowTow = sheet.createRow(1); HSSFCell CellTow = headRow0.createCell(1); // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ??? CellTow = RowTow.createCell(0); // CellTow.setCellValue(model.getCreateCompany()); // ? // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ??? CellTow = RowTow.createCell(6); HSSFCellStyle style1 = wb.createCellStyle(); style1.setFillBackgroundColor(HSSFColor.GREEN.index); style1.setAlignment(CellStyle.ALIGN_RIGHT);// ? CellTow.setCellStyle(style1); // CellTow.setCellValue(model.getCreateData()); // ? HSSFRow headRow = sheet.createRow(2); HSSFCell headell = headRow.createCell(2); // ??? headell = headRow.createCell(0); headell.setCellValue(model.getType()); headell = headRow.createCell(1); headell.setCellValue("??"); headell = headRow.createCell(2); headell.setCellValue("??"); sheet.setColumnWidth(2, 3000); // headell = headRow.createCell(3); headell.setCellValue("???"); sheet.setColumnWidth(3, 3000); // headell = headRow.createCell(4); headell.setCellValue("???"); sheet.setColumnWidth(4, 4000); // headell = headRow.createCell(5); headell.setCellValue("???"); sheet.setColumnWidth(5, 4000); // headell = headRow.createCell(6); headell.setCellValue("???"); sheet.setColumnWidth(6, 4500); // headell = headRow.createCell(7); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(8); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(9); headell.setCellValue(""); sheet.setColumnWidth(9, 4000); headell = headRow.createCell(10); headell.setCellValue("?"); sheet.setColumnWidth(10, 4000); headell = headRow.createCell(11); headell.setCellValue("???"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("?"); for (int i = 0; i < companyList.size(); i++) { ReportViewModel company = companyList.get(i); // Excel? HSSFRow row = sheet.createRow(i + 3); HSSFCell cell = row.createCell(i + 3); // ??? // ???? cell = row.createCell(0); cell.setCellValue(company.getReportName()); // ?? cell = row.createCell(1); cell.setCellValue(company.getUnitNum()); // ?? cell = row.createCell(2); cell.setCellValue(company.getEmpTotal()); // ??? cell = row.createCell(3); cell.setCellValue(company.getUnAudit()); // ?, ??? cell = row.createCell(4); cell.setCellValue(company.getUnReAudit()); // ?, ?? cell = row.createCell(5); cell.setCellValue(company.getAuditOk()); // ?, ?? cell = row.createCell(6); cell.setCellValue(company.getUnauditOk()); // cell = row.createCell(7); cell.setCellValue(company.getShouldTotal().toString()); // ? cell = row.createCell(8); cell.setCellValue(company.getAlreadyTotal().toString()); // cell = row.createCell(9); cell.setCellValue(company.getLessTotal().toString()); // ? cell = row.createCell(10); cell.setCellValue(company.getAmountPayable().toString()); // ??? cell = row.createCell(11); cell.setCellValue(company.getReductionAmount().toString()); // ? cell = row.createCell(12); cell.setCellValue(company.getActualAmount().toString()); // ? cell = row.createCell(13); cell.setCellValue(company.getAlreadyAmount().toString()); } // ? HSSFRow row = sheet.createRow(companyList.size() + 3); HSSFCell cell = row.createCell(companyList.size() + 3); // ??? // ???? sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ??? cell = row.createCell(0); // ? HSSFCellStyle styleFoot = wb.createCellStyle(); styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ? cell.setCellStyle(styleFoot); // cell.setCellValue(model.getCreatePeople()); try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }
From source file:com.globalsight.everest.qachecks.DITAQAChecker.java
License:Apache License
private Font getRedFont(Workbook p_workbook) { if (redFont == null) { Font font = p_workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setColor(HSSFColor.RED.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); redFont = font;// w w w. j a v a 2s .c o m } return redFont; }
From source file:com.haulmont.cuba.gui.export.ExcelAutoColumnSizer.java
License:Apache License
private FontMetrics getFontMetrics(HSSFFont hf) { FontMetrics fm;//from www. ja v a2 s. c o m Short pFont = new Short(hf.getIndex()); fm = (FontMetrics) fontMetrics.get(pFont); if (fm == null) { int style; if ((hf.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) || hf.getItalic()) { style = 0; if (hf.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) style ^= Font.BOLD; if (hf.getItalic()) style ^= Font.ITALIC; } else { style = Font.PLAIN; } Font f = new java.awt.Font(hf.getFontName(), style, hf.getFontHeightInPoints()); if (graphics == null) { BufferedImage i = new BufferedImage(1, 1, BufferedImage.TYPE_BYTE_GRAY); graphics = i.createGraphics(); } fm = graphics.getFontMetrics(f); fontMetrics.put(pFont, fm); } return fm; }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * , Excel/*from w w w .jav a 2s. com*/ * * @param modelId * Id * @return Excel */ public String getResouceDataToExcel(String modelId) { Model m = ServiceManager.getModelService().getModelById(modelId); List<ResourceObject> l = ServiceManager.getResourceService().getResourcesByModelId(modelId, 1); List<ModelAttribute> mas = m.getAttributes(); Workbook wb = new HSSFWorkbook(); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); font.setFontName(""); style.setFont(font); Sheet sheet = wb.createSheet(modelId); Row row = sheet.createRow(0); int i = 0; HSSFRichTextString textString; for (ModelAttribute ma : mas) { Cell cell = row.createCell(i); textString = new HSSFRichTextString(ma.getDefine().getName()); cell.setCellStyle(style); cell.setCellValue(textString); i++; } i = 1; for (ResourceObject ro : l) { row = sheet.createRow(i); int j = 0; for (ModelAttribute ma : mas) { textString = new HSSFRichTextString(ro.getAttributeValue(ma.getAttrId())); row.createCell(j).setCellValue(textString); j++; } i++; } for (int k = 0; k < mas.size(); k++) { sheet.autoSizeColumn(k); } // Write the output to a file FileOutputStream fileOut; String file; try { file = System.getProperty("user.dir") + "/../temp/" + modelId + new Random().nextLong() + "_data.xls"; fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return ""; } catch (IOException e) { e.printStackTrace(); return ""; } return file; }
From source file:com.ibm.ioes.dao.NewOrderDaoExt.java
/** * Method to get all data for Masters Download * @param productID//from ww w .j a v a 2 s . com * @return */ public HSSFWorkbook downloadMasters(long productID) { String methodName = "downloadMasters", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; AppConstants.IOES_LOGGER.info(methodName + " method of " + className + " class have been called"); Connection connection = null; ArrayList<String> allowedSections = new ArrayList<String>(); HSSFWorkbook wb = new HSSFWorkbook(); CallableStatement getAllDropDownAttributes = null; CallableStatement getDetailForEachdropDown = null; ResultSet rsForAllDropDowns = null; ResultSet rsForEachDropDown = null; CallableStatement getAllMasters = null; ResultSet rsAllMasters = null; try { connection = DbConnection.getConnectionObject(); connection.setAutoCommit(false); allowedSections = getAllowedSections(productID); //for Service Summary if (allowedSections.contains(new String("SERVICE_SUMMARY"))) { getAllDropDownAttributes = connection.prepareCall(sp_getAllDropDownAttributes); getAllDropDownAttributes.setLong(1, productID); rsForAllDropDowns = getAllDropDownAttributes.executeQuery(); while (rsForAllDropDowns.next()) { //makeSheetForServiceSummary(wb,rsForAllDropDowns.getInt("ATTMASTERID")); HSSFSheet sheet = wb.createSheet(rsForAllDropDowns.getString("ATTDESCRIPTION")); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = sheet.createRow(0); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString(rsForAllDropDowns.getString("ATTDESCRIPTION"))); excelRow = sheet.createRow(1); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VALUE")); getDetailForEachdropDown = connection.prepareCall(sp_getDataForEachDropDown); getDetailForEachdropDown.setLong(1, rsForAllDropDowns.getInt("ATTMASTERID")); rsForEachDropDown = getDetailForEachdropDown.executeQuery(); int rowNo = 2; while (rsForEachDropDown.next()) { //create a sheet excelRow = sheet.createRow(rowNo); for (int cell = 0, col = 1; cell < 2; cell++, col++) { excelCell = excelRow.createCell(cell); excelCell.setCellValue(new HSSFRichTextString(rsForEachDropDown.getString(col))); } rowNo = rowNo + 1; } } } //for all other Sections getAllMasters = connection.prepareCall(sqlSp_getAllMasters); rsAllMasters = getAllMasters.executeQuery(); while (rsAllMasters.next()) { String sectionName = rsAllMasters.getString("SECTION_NAME"); if (allowedSections.contains(sectionName)) { String columns = rsAllMasters.getString("COLUMN_NAMES"); String[] columnNames = columns.split(","); HSSFSheet sheet = wb.createSheet(rsAllMasters.getString("MASTER_NAME")); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = sheet.createRow(0); for (int count = 0; count < columnNames.length; count++) { excelCell = excelRow.createCell(count); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString(columnNames[count])); } PreparedStatement getMasterData = null; ResultSet rsMasterData = null; getMasterData = connection.prepareStatement(rsAllMasters.getString("QUERY")); rsMasterData = getMasterData.executeQuery(); int rowNo = 1; while (rsMasterData.next()) { excelRow = sheet.createRow(rowNo); for (int cell = 0, col = 1; cell < columnNames.length; cell++, col++) { excelCell = excelRow.createCell(cell); excelCell.setCellValue(new HSSFRichTextString(rsMasterData.getString(col))); } rowNo = rowNo + 1; } } } } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { Utility.onEx_LOG_RET_NEW_EX(e1, methodName, className, msg, logToFile, logToConsole); } } finally { try { DbConnection.closeResultset(rsAllMasters); DbConnection.closeResultset(rsForAllDropDowns); DbConnection.closeCallableStatement(getAllDropDownAttributes); DbConnection.closeCallableStatement(getAllMasters); DbConnection.closeCallableStatement(getDetailForEachdropDown); DbConnection.closeCallableStatement(getAllMasters); DbConnection.freeConnection(connection); } catch (Exception e) { } } return wb; }
From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java
public HSSFWorkbook generateExcel_ACS(ArrayList<ACSMailTemplateDto> mailDataList) { HSSFWorkbook wb = null;/*from ww w .ja v a2 s . c o m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); int i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Region")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Type of ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Customer Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Address")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("City State")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Pin")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Moderator Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Contact Person")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account Manager Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId()))); /*excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/ excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone()))); excelCell = excelRow.createCell(i_cell++); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone()) + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }