List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:com.insoul.ti.controller.ContestProjectEntryController.java
@RequestMapping("/download") public ModelAndView download(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook();// Excel HSSFSheet sheet = workbook.createSheet();// ExcelSheet sheet.createFreezePane(1, 2);// // /*from w w w. j av a2 s.c om*/ sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet? // HSSFCellStyle sheetStyle = workbook.createCellStyle(); // // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // ? // for (int i = 0; i <= 14; i++) { // sheet.setDefaultColumnStyle((short) i, sheetStyle); // } // HSSFFont headfont = workbook.createFont(); headfont.setFontName(""); headfont.setFontHeightInPoints((short) 22);// ? headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// // ?? HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// headstyle.setLocked(true); headstyle.setWrapText(true);// ? // ?? HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName(""); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ? HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// columnHeadStyle.setBorderLeft((short) 1);// ? columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ? columnHeadStyle.setBorderRight((short) 1);// ? columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? // ???? columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?? HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? style.setBottomBorderColor(HSSFColor.BLACK.index); // ? style.setFillForegroundColor(HSSFColor.WHITE.index);// ? // ?? HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ? try { // HSSFRow row0 = sheet.createRow(0); // row0.setHeight((short) 900); // HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("???")); cell0.setCellStyle(headstyle); /** * ?? ??0 ??0 ??0 ??0 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(range); // HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 750); HSSFCell cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(1); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(2); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(3); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(4); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(5); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(6); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(7); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(8); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(9); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(10); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(11); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(12); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(13); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(14); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); List<ContestEntry> projectList = contestEntryDAO.findAll(); int m = 2; int len = projectList.size(); for (int i = 0; i < len; i++) { ContestEntry c = projectList.get(i); HSSFRow row = sheet.createRow(m + i); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(c.getId() + "")); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(c.getLocation())); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(c.getInstance())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(c.getIndustry())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(c.getLegalFormation())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(c.getRegtime())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + "")); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(c.getLegalPerson())); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(new HSSFRichTextString(c.getUserCategory())); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(new HSSFRichTextString(c.getContact())); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(new HSSFRichTextString(c.getIdNumber())); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(new HSSFRichTextString(c.getBankName())); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue(new HSSFRichTextString(c.getBankUserName())); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue(new HSSFRichTextString(c.getBankAccount())); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue(new HSSFRichTextString(c.getSupportMoney())); cell.setCellStyle(style); } String filename = System.nanoTime() + ".xls";// Excel?? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { log.error("download excel Error.", e); } return null; }
From source file:com.jfinal.ext.render.excel.PoiKit.java
License:Apache License
public HSSFWorkbook export() { Preconditions.checkNotNull(headers, "headers can not be null"); Preconditions.checkNotNull(columns, "columns can not be null"); Preconditions.checkArgument(cellWidth >= 0, "cellWidth < 0"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); HSSFRow row = null;/*w w w . ja v a 2 s . c o m*/ HSSFCell cell = null; if (headers.length > 0) { row = sheet.createRow(0); if (headerRow <= 0) { headerRow = HEADER_ROW; } headerRow = Math.min(headerRow, MAX_ROWS); for (int h = 0, lenH = headers.length; h < lenH; h++) { @SuppressWarnings("deprecation") Region region = new Region(0, (short) h, (short) headerRow - 1, (short) h);// ?rowFromcolumnFrom sheet.addMergedRegion(region);// rowTocolumnTo // sheet.getNumMergedRegions(); if (cellWidth > 0) { sheet.setColumnWidth(h, cellWidth); } cell = row.createCell(h); cell.setCellValue(headers[h]); } } if (data.size() == 0) { return wb; } for (int i = 0, len = data.size(); i < len; i++) { row = sheet.createRow(i + headerRow); Object obj = data.get(i); if (obj == null) { continue; } if (obj instanceof Map) { processAsMap(columns, row, obj); } else if (obj instanceof Model) { processAsModel(columns, row, obj); } else if (obj instanceof Record) { processAsRecord(columns, row, obj); } } return wb; }
From source file:com.kcs.action.ForwardContractAction.java
@Override @SuppressWarnings("empty-statement") public String export() throws Exception { list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate())); getLogger().debug("exportExcel : begin..."); setFileName("Forward contract Report.xls"); DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFCellStyle style = myWorkBook.createCellStyle(); HSSFCellStyle styleFont = myWorkBook.createCellStyle(); HSSFFont fontB = myWorkBook.createFont(); fontB.setBoldweight(Font.BOLDWEIGHT_BOLD); styleFont.setFont(fontB);// www . j a va 2 s.co m list = sortSheet(list); List<String> listSheet = countSheet(list); for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) { List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet)); HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet)); List<String> currencyList = getCurrency(sheetObject); //------------------------- ROW 1 -----------------------------// Row FW1_row_0 = FW1.createRow(0); FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT"); CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER); HSSFCellStyle styleRow1 = myWorkBook.createCellStyle(); styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow1.setFont(fontB); FW1_row_0.getCell(0).setCellStyle(styleRow1); FW1_row_0.createCell(1) .setCellValue(" ? ?()"); FW1_row_0.getCell(1).setCellStyle(styleRow1); // FW1_row_0.getCell(8).setCellStyle(styleFont); //------------------------- ROW 2 -----------------------------// Row FW1_row_1 = FW1.createRow(1); FW1_row_1.createCell(0).setCellValue("FW"); // FW1_row_1.getCell(0).setCellStyle(styleBR); // FW1.addMergedRegion(new CellRangeAddress(1,1,7,8)); FW1.autoSizeColumn(0); FW1.autoSizeColumn(7); FW1.autoSizeColumn(8); Row FW1_row_2 = FW1.createRow(2); FW1_row_2.createCell(0).setCellValue("?"); //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT); HSSFCellStyle style2 = myWorkBook.createCellStyle(); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); FW1_row_2.getCell(0).setCellStyle(style2); Row FW1_row_3 = FW1.createRow(3); FW1_row_3.createCell(0).setCellValue("?"); HSSFCellStyle style3 = myWorkBook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_3.getCell(0).setCellStyle(style3); FW1.createRow(4).createCell(0).setCellValue("?"); FW1.createRow(5).createCell(0).setCellValue("? (129027)"); FW1.createRow(6).createCell(0).setCellValue( "???"); FW1.createRow(7).createCell(0).setCellValue(" ?"); FW1.createRow(8).createCell(0).setCellValue(" - ?? (129030)"); FW1.createRow(9).createCell(0) .setCellValue(" - ??? (129031)"); FW1.createRow(10).createCell(0).setCellValue(" ??"); FW1.createRow(11).createCell(0).setCellValue(" "); FW1.createRow(12).createCell(0).setCellValue(" - ?? (129034)"); FW1.createRow(13).createCell(0) .setCellValue(" - ??? (129035)"); FW1.createRow(14).createCell(0).setCellValue(" ???"); FW1.createRow(15).createCell(0).setCellValue(" - ?? (129037)"); FW1.createRow(16).createCell(0) .setCellValue(" - ??? (129038) "); FW1.createRow(17).createCell(0) .setCellValue(" ?"); FW1.createRow(18).createCell(0).setCellValue(" - ?? (129040)"); FW1.createRow(19).createCell(0) .setCellValue(" - ??? (129041) "); FW1.createRow(20).createCell(0) .setCellValue(" ??"); FW1.createRow(21).createCell(0).setCellValue(" - ?? (129043)"); FW1.createRow(22).createCell(0) .setCellValue(" - ??? (129044)"); FW1.createRow(23).createCell(0) .setCellValue(" ?"); FW1.createRow(24).createCell(0).setCellValue(" - ?? (129046)"); FW1.createRow(25).createCell(0) .setCellValue(" - ??? (129047) "); FW1.createRow(26).createCell(0).setCellValue("????"); FW1.createRow(27).createCell(0).setCellValue(" ??"); FW1.createRow(28).createCell(0).setCellValue(" - ?? (129050)"); FW1.createRow(29).createCell(0) .setCellValue(" - ??? (129051)"); FW1.createRow(30).createCell(0) .setCellValue(" "); FW1.createRow(31).createCell(0).setCellValue(" - ?? (129053)"); FW1.createRow(32).createCell(0) .setCellValue(" - ??? (129054)"); FW1.createRow(33).createCell(0).setCellValue(" ? "); FW1.createRow(34).createCell(0).setCellValue(" - ?? (129056) "); FW1.createRow(35).createCell(0) .setCellValue(" - ??? (129057) "); FW1.createRow(36).createCell(0).setCellValue(" "); FW1.createRow(37).createCell(0).setCellValue(" - ?? (129059) "); FW1.createRow(38).createCell(0) .setCellValue(" - ??? (129060) "); FW1.createRow(39).createCell(0).setCellValue("? (129061) "); FW1.createRow(40).createCell(0) .setCellValue("? (129062) "); // FW1.getRow(40).getCell(0).setCellStyle(styleBR); FW1.getRow(4).getCell(0).setCellStyle(styleFont); FW1.getRow(5).getCell(0).setCellStyle(styleFont); FW1.getRow(6).getCell(0).setCellStyle(styleFont); FW1.getRow(7).getCell(0).setCellStyle(styleFont); FW1.getRow(11).getCell(0).setCellStyle(styleFont); FW1.getRow(14).getCell(0).setCellStyle(styleFont); FW1.getRow(17).getCell(0).setCellStyle(styleFont); FW1.getRow(20).getCell(0).setCellStyle(styleFont); FW1.getRow(23).getCell(0).setCellStyle(styleFont); FW1.getRow(26).getCell(0).setCellStyle(styleFont); FW1.getRow(30).getCell(0).setCellStyle(styleFont); FW1.getRow(33).getCell(0).setCellStyle(styleFont); FW1.getRow(36).getCell(0).setCellStyle(styleFont); FW1.getRow(39).getCell(0).setCellStyle(styleFont); FW1.getRow(40).getCell(0).setCellStyle(styleFont); int cellCurrency = 1; for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); String currencyName = currencyList.get(iCurrencyList); FW1_row_2.createCell(cellCurrency).setCellValue(currencyName); FW1_row_3.createCell(cellCurrency).setCellValue(""); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style); FW1_row_3.getCell(cellCurrency).setCellStyle(style); List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName); for (ForwardContract objCCY : tmpGroupCCY) { FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + ""); if ("????" .equals(objCCY.getTRANS_TYPE())) { if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(8).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true)); FW1.getRow(8).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(9).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false)); FW1.getRow(9).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false)); } } else if ("????" .equals(objCCY.getTRANS_TYPE().trim())) { if ("New Forward".equals(objCCY.getLIST_ITEM())) { FW1.getRow(28).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true)); FW1.getRow(28).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(29).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false)); FW1.getRow(29).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false)); } } else if ("?".equals(objCCY.getTRANS_TYPE())) { } FW1.getRow(39).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false)); FW1.getRow(39).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false)); } // // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+""); CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency)); FW1_row_3.createCell(cellCurrency).setCellValue(""); HSSFCellStyle styleTop = style; styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN); FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop); cellCurrency++; } System.out.println("cellCurrency ++++ " + cellCurrency); int finishM = cellCurrency - 3; int noOfColumns = FW1.getRow(2).getLastCellNum(); int noOfRow = FW1.getLastRowNum(); // HSSFPalette palette = myWorkBook.getCustomPalette(); // HSSFColor hssfColor = null; // // // palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255); // hssfColor = palette.getColor(HSSFColor.WHITE.index); // // // // HSSFCellStyle styleBG = myWorkBook.createCellStyle(); // styleBG.setFillForegroundColor(hssfColor.getIndex()); // styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND); for (int i = 0; i < noOfRow; i++) { for (int j = 0; j < noOfColumns; j++) { FW1.autoSizeColumn(i); // row.getCell(j).setCellStyle(styleBG); } } for (int i = 4; i < noOfRow + 1; i++) { for (int j = 1; j < noOfColumns; j++) { HSSFCellStyle style6 = myWorkBook.createCellStyle(); try { if (j == 1) { style6.setBorderLeft(HSSFCellStyle.BORDER_THIN); style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } else { style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } if (i == noOfRow) { style6.setBorderBottom(HSSFCellStyle.BORDER_THIN); } FW1.getRow(i).getCell(j).setCellStyle(style6); } catch (Exception e) { FW1.getRow(i).createCell(j).setCellStyle(style6); } } } HSSFFont fontBold = myWorkBook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle(); borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN); borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); borderRightCenterFontBold.setFont(fontBold); HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold; borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleR = myWorkBook.createCellStyle(); styleR.setBorderRight(HSSFCellStyle.BORDER_THIN); // Forward contract // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold); FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM)); FW1_row_0.createCell(finishM + 1) .setCellValue(" " + dateFormat.format(new Date()) + ""); HSSFCellStyle style4 = myWorkBook.createCellStyle(); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_0.getCell(finishM + 1).setCellStyle(style4); FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont); FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2)); FW1_row_1.createCell(finishM + 1).setCellValue( ":"); FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2)); FW1_row_1.getCell(finishM + 1).setCellStyle(style4); FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont); FW1_row_0.createCell(finishM + 2).setCellStyle(styleR); FW1_row_1.createCell(finishM + 2).setCellStyle(styleR); FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold); HSSFCellStyle borderBottom = myWorkBook.createCellStyle(); borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN); borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT); borderBottom.setFont(fontBold); FW1.getRow(40).getCell(0).setCellStyle(borderBottom); HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch(); /* Here is the thing: the line will go from top left in cell (0,0) to down left of cell (0,1) */ // int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3); HSSFSimpleShape shape = patriarch.createSimpleShape(anchor); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); shape.setLineStyleColor(10, 10, 10); shape.setFillColor(90, 10, 200); shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT); shape.setLineStyle(HSSFShape.LINESTYLE_SOLID); // createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); // FW1.createFreezePane(0,4); } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.ReportCompareUtilizeFwdAction.java
public String export() throws Exception { logger.debug("= = = = = EXPORT = = = = ="); logger.debug("PALM : processDate >>> " + DateUtil.convertDateFromJsp(processDate) + " reportType >>> " + reportType);// w w w.ja v a 2s . c o m list = reportCompareUtilizeFWDService.findWithCriteriaService(reportType, DateUtil.convertDateFromJsp(processDate)); fileType = "XLS"; HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Utilize_FWD"); if ("RPT_TYP1_41".equals(reportType)) { fileName = "Report_FWD_not_found_EES_(Trade_Finance)_CBS_(Remittance)_" + hidDate + ".xls"; Row rowHeader1 = mySheet.createRow(0); rowHeader1.createCell(0).setCellValue( "? FWD ? EES (Trade Finance) & CBS (Remittance)"); } else if ("RPT_TYP2_41".equals(reportType)) { fileName = "Report_EES_(Trade_Finance)_CBS_(Remittance)_not_found_FWD_" + hidDate + ".xls"; Row rowHeader1 = mySheet.createRow(0); rowHeader1.createCell(0).setCellValue( "? EES (Trade Finance) & CBS (Remittance)? FWD"); } // Cell cell1 = CellUtil.createCell(rowHeader1, 0,"? FWD ? EES (Trade Finance) & CBS (Remittance)"); // CellUtil.setAlignment(cell1, myWorkBook, CellStyle.ALIGN_CENTER); Row rowHeader2 = mySheet.createRow(1); rowHeader2.createCell(0).setCellValue(processDate); // Cell cell2 = CellUtil.createCell(rowHeader2, 1,processDate); // CellUtil.setAlignment(cell2, myWorkBook, CellStyle.ALIGN_CENTER); mySheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) )); mySheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based) 1, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) )); if (null != list && list.size() > 0) { int rownum = 2; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("Curr"); row.createCell(cellnum++).setCellValue("Fi Arrg No"); row.createCell(cellnum++).setCellValue("Amount"); for (ReportCompareUtilizeFwd obj : list) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getCURR() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFI_ARRG_NO() + ""); rowData.createCell(cellnum++).setCellValue(obj.getAMT() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; // return REPORT; }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells./* ww w. j a v a 2 s. c om*/ */ private static void testCreateSampleSheet(String outputFilename) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); int rownum; for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue( rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet try (FileOutputStream out = new FileOutputStream(outputFilename)) { wb.write(out); } } }
From source file:com.leosys.core.utils.ExcelUtil.java
public void exportExcel(List<?> dataList, OutputStream out) throws Exception { HSSFWorkbook workbook = null;//from ww w.j a v a 2s.c om HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle titleStyle = null; int rowIndex = 0; try { workbook = new HSSFWorkbook();// sheet = workbook.createSheet("?");// ? sheet.setDefaultColumnWidth((short) 30);// 15 titleStyle = workbook.createCellStyle();//? titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setFillForegroundColor(HSSFColor.WHITE.index); titleStyle.setFillBackgroundColor(HSSFColor.WHITE.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 17); titleStyle.setFont(font); row = sheet.createRow(rowIndex++); row.setHeight((short) 600); for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); //? if (i == 0) cell.setCellValue(new HSSFRichTextString(title)); cell.setCellStyle(titleStyle); } // ??? sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headArr.length - 1))); titleStyle = workbook.createCellStyle(); titleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setFontHeightInPoints((short) 13); titleStyle.setFont(font);// ?? row = sheet.createRow(rowIndex++);// for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); cell.setCellStyle(titleStyle); cell.setCellValue(new HSSFRichTextString(headArr[i])); } //? titleStyle = workbook.createCellStyle(); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); if (dataList == null || dataList.isEmpty()) return; short dataType = 0;// if (dataList.get(0) instanceof Map<?, ?>) dataType = 1; else if (dataList.get(0) instanceof List<?>) dataType = 2; if (dataType == 0) { String[] dataArr = null; for (Object data : dataList) { dataArr = (String[]) data; if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 1) { Map<?, ?> map = null; for (Object data : dataList) { map = (Map<?, ?>) data; if (map == null) continue; Object[] dataArr = map.values().toArray(); if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 2) { List<?> list = null; for (Object data : dataList) { list = (List<?>) data; if (list == null || list.isEmpty()) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < list.size()) { Object val = list.get(i); if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(list.get(i), i, list); fillCell(row, titleStyle, font, i, val); } } } } else throw new Exception("excel???"); workbook.write(out); } catch (Exception e) { throw new Exception("excel" + e.getMessage()); } finally { // if(out != null){ // try { // out.close(); // } catch (IOException e) {} // } } }
From source file:com.lition.service.impl.OwnedServiceImpl.java
/** * POI??//w ww.j ava2s .com */ @Override public InputStream getOutExcelDate() { //1.? String headTitle[] = { "id", "?", "??", "", "??" }; //2.Dao?? List<OwnedVehicle> list = dao.queryAll(); //3.?HSSFWorkbook HSSFWorkbook wb = new HSSFWorkbook(); //4.?sheet HSSFSheet sheet = wb.createSheet("?"); //5.? HSSFCellStyle style = wb.createCellStyle(); // ?? style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); //6. HSSFRow row0 = sheet.createRow(0); //7.?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); HSSFCell row0cell0 = row0.createCell(0); row0cell0.setCellValue("?"); row0cell0.setCellStyle(style); HSSFRow row1 = sheet.createRow(1); for (int i = 0; i < headTitle.length; i++) { HSSFCell row0cell = row1.createCell(i); row0cell.setCellValue(headTitle[i]); row0cell.setCellStyle(style); } for (int i = 0; i < list.size(); i++) { HSSFRow row = sheet.createRow(i + 2); //ID HSSFCell cell0 = row.createCell(0); cell0.setCellValue(list.get(i).getId()); cell0.setCellStyle(style); //? HSSFCell cell1 = row.createCell(1); cell1.setCellValue(list.get(i).getVehicleId()); cell1.setCellStyle(style); //?? HSSFCell cell2 = row.createCell(2); cell2.setCellValue(list.get(i).getDepid()); cell2.setCellStyle(style); // HSSFCell cell3 = row.createCell(3); cell3.setCellValue(list.get(i).getModel()); cell3.setCellStyle(style); //?? HSSFCell cell4 = row.createCell(4); cell4.setCellValue(list.get(i).getVehicleUsageId()); cell4.setCellStyle(style); } //?inputstream; try { OutputStream out = new FileOutputStream("abc.xls"); wb.write(out); out.close(); InputStream in = new FileInputStream("abc.xls"); return in; } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; }
From source file:com.mss.mirage.marketing.MarketingServiceImpl.java
License:Open Source License
public String getCampaignContactsExcel(HttpServletRequest httpServletRequest, MarketingAction marketingAction) throws ServiceLocatorException { // System.out.println("getCampaignContactsExcel"); String filePath = ""; StringBuffer sb = null;//from w w w. j a va2 s . com Connection connection = null; /** * preStmt,preStmtTemp are reference variable for PreparedStatement . */ PreparedStatement preStmt = null; /** * The queryString is useful to get queryString result to the particular * jsp page */ String queryString = ""; /** * The statement is useful to execute the above queryString */ ResultSet resultSet = null; HashMap map = null; List finalList = new ArrayList(); String campaignTitle = ""; try { PreparedStatement preparedStatement = null; Statement statement = null; ResultSet resultSet1 = null; String email = ""; String phone = ""; String CompanyName = ""; String contactName = ""; int z = 0; int n = 1; Map<Integer, String> contactIdMap = new TreeMap<Integer, String>(); // queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId=" + campaignId + " ORDER BY CreatedDate DESC"; String loginId = httpServletRequest.getSession(false).getAttribute(ApplicationConstants.SESSION_USER_ID) .toString(); String roleName = httpServletRequest.getSession(false) .getAttribute(ApplicationConstants.SESSION_ROLE_NAME).toString(); campaignTitle = DataSourceDataProvider.getInstance() .getCampaignNameByCampaignId(marketingAction.getCampaignId()); // queryString = "SELECT Id,Title,CreatedBy,CurrStatus FROM tblCrmLeads WHERE AccountId=" + accId; // queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId="+campaignId+" ORDER BY CreatedDate DESC"; queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId=" + marketingAction.getCampaignId(); if (marketingAction.getContactStartDate() != null && !"".equalsIgnoreCase(marketingAction.getContactStartDate())) { queryString = queryString + " AND (DATE(CreatedDate))>='" + DateUtility.getInstance().getMysqlDate(marketingAction.getContactStartDate()) + "' "; } if (marketingAction.getContactEndDate() != null && !"".equalsIgnoreCase(marketingAction.getContactEndDate())) { queryString = queryString + " AND (DATE(CreatedDate))<='" + DateUtility.getInstance().getMysqlDate(marketingAction.getContactEndDate()) + "' "; } if (roleName.equals("Sales")) { queryString = queryString + " AND CreatedById='" + loginId + "' "; } queryString = queryString + " ORDER BY CreatedDate DESC"; // String contactQuery = "SELECT Id, CONCAT(FirstName,' ',MiddleName,'.',LastName) AS ContactName,Email1,CellPhone FROM tblCrmContact WHERE Id=?"; String contactQuery = "SELECT tblCrmContact.id AS contactId,tblCrmAccount.Id AS accountId," + "CONCAT(tblCrmContact.FirstName,'.',tblCrmContact.LastName) AS ContactName," + " tblCrmContact.Email1 AS Email1, tblCrmAccount.NAME AS CompanyName,tblCrmContact.CellPhone AS CellPhone" + " FROM tblCrmContact LEFT JOIN tblCrmAccount ON(tblCrmContact.AccountId=tblCrmAccount.Id) WHERE tblCrmContact.id=?"; connection = ConnectionProvider.getInstance().getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(queryString); while (resultSet.next()) { z++; int Id = resultSet.getInt("Id"); String ContactId = resultSet.getString("ContactId"); contactIdMap.put(Id, ContactId); //totalStream=totalStream+i+"|"+createdDate+"|"+actType+"|"+description+"|"+comments+"|"+assignedToId+"|"+contactName+"|"+datedue+"|"+actid+"|"+"^"; // totalActivities=totalActivities+count; } preparedStatement = connection.prepareStatement(contactQuery); for (Map.Entry<Integer, String> entry : contactIdMap.entrySet()) { Integer key = entry.getKey(); String value = entry.getValue(); String[] parts = value.split("\\,"); if (parts.length > 0 && !"".equals(value)) { for (int j = 0; j < parts.length; j++) { preparedStatement.setInt(1, Integer.parseInt(parts[j])); resultSet1 = preparedStatement.executeQuery(); while (resultSet1.next()) { CompanyName = resultSet1.getString("CompanyName"); contactName = resultSet1.getString("ContactName"); email = resultSet1.getString("Email1"); phone = resultSet1.getString("CellPhone"); map = new HashMap(); map.put("SNO", String.valueOf(n)); map.put("contactName", contactName); map.put("CompanyName", CompanyName); map.put("email", email); // map.put("phone", phone); // System.out.println(map); finalList.add(map); n++; } } } } // System.out.println("map" + finalList.size()); File file = new File(Properties.getProperty("Emp.Qmeet.Path")); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream( file.getAbsolutePath() + File.separator + "Contacts.xls"); filePath = file.getAbsolutePath() + File.separator + "Contacts.xls"; HSSFRow row1; HSSFWorkbook workbook = new HSSFWorkbook(); System.out.println("filePath " + filePath); HSSFSheet worksheet = workbook.createSheet("Campaign Contacts"); for (int i = 0; i < 4; i++) { worksheet.setColumnWidth(i, 10 * 256); } HSSFFont timesBoldFont = workbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Calibri"); HSSFCellStyle headercs = workbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.PALE_BLUE.index); headercs.setAlignment(HSSFCellStyle.ALIGN_LEFT); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border headercs.setFont(timesBoldFont); HSSFFont footerFont = workbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); footerFont.setFontName("Calibri"); // HSSFRow row1; // LogisticsDocBean logisticsDocBean = null; // index from 0,0... cell A1 is cell(0,0) // if(list.size()!=0){// //System.out.println("list size-->"+list.size()); HSSFFont font4 = workbook.createFont(); font4.setColor(HSSFColor.WHITE.index); font4.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font4.setFontHeightInPoints((short) 14); font4.setFontName("Calibri"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFont(font4); HSSFFont font1 = workbook.createFont(); //font1.setColor(HSSFColor.WHITE.index); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 14); font1.setFontName("Calibri"); HSSFCellStyle cs = workbook.createCellStyle(); cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cs.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs.setBorderTop((short) 1); // single line border cs.setBorderBottom((short) 1); // single line border cs.setFont(font1); HSSFCellStyle cs1 = workbook.createCellStyle(); cs1.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); cs1.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs1.setFont(font4); cs1.setBorderTop((short) 1); // single line border cs1.setBorderBottom((short) 1); // single line border HSSFCellStyle cs2 = workbook.createCellStyle(); cs2.setFillForegroundColor(HSSFColor.WHITE.index); cs2.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs2.setBorderTop((short) 1); // single line border cs2.setBorderBottom((short) 1); // single line border cs2.setFont(font1); HSSFFont font3 = workbook.createFont(); //font1.setColor(HSSFColor.WHITE.index); // font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font3.setFontHeightInPoints((short) 14); font3.setFontName("Calibri"); HSSFCellStyle cs3 = workbook.createCellStyle(); cs3.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cs3.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs3.setFont(font3); cs3.setBorderTop((short) 1); // single line border cs3.setBorderBottom((short) 1); // single line border HSSFCellStyle cs4 = workbook.createCellStyle(); cs4.setFillForegroundColor(HSSFColor.WHITE.index); cs4.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs4.setBorderTop((short) 1); // single line border cs4.setBorderBottom((short) 1); // single line border cs4.setFont(font3); //start row1 = worksheet.createRow((short) 0); HSSFCell cellpo0 = row1.createCell((short) 0); // cellpo0.setCellValue("Purchase Order"); HSSFCell cellpo1 = row1.createCell((short) 1); HSSFCell cellpo2 = row1.createCell((short) 2); // cellpo2.setCellValue("Created Date"); HSSFCell cellpo3 = row1.createCell((short) 3); //cellpo3.setCellValue((date.getYear()+1900)+"-"+(date.getMonth()+1)+"-"+date.getDate()); HSSFCell cellpo4 = row1.createCell((short) 4); HSSFCell cellpo5 = row1.createCell((short) 5); HSSFCell cellpo6 = row1.createCell((short) 6); HSSFCell cellpo7 = row1.createCell((short) 7); HSSFCell cellpo8 = row1.createCell((short) 8); HSSFCell cellpo9 = row1.createCell((short) 9); HSSFCell cellpo10 = row1.createCell((short) 10); HSSFCell cellpo11 = row1.createCell((short) 11); row1 = worksheet.createRow((short) 0); Cell cell[] = new Cell[4]; for (int i = 0; i < 4; i++) { cell[i] = row1.createCell((short) i); } // cell.setCellValue("Logistics Document :-Created Date : "+(date.getYear()+1900)+"-"+(date.getMonth()+1)+"-"+date.getDate()); cell[0].setCellValue("Contacts Details :" + campaignTitle); HSSFCellStyle cellStyleHead = workbook.createCellStyle(); cellStyleHead.setFont(timesBoldFont); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setFillBackgroundColor(HSSFColor.PALE_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cell[0].setCellStyle(cellStyle); worksheet.addMergedRegion(CellRangeAddress.valueOf("A1:" + "D2")); //sno row1 = worksheet.createRow((short) 2); cell[0] = row1.createCell((short) 0); cell[0].setCellValue("SNo"); // cellStyleHead.setFont(font4); cellStyleHead.setFont(timesBoldFont); // cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER); //cellStyleHead.setFillBackgroundColor(HSSFColor.PALE_BLUE.index); // cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT); // cell[0].setCellStyle(cellStyleHead); cell[0].setCellStyle(cs1); worksheet.addMergedRegion(CellRangeAddress.valueOf("A3:A4")); cell[0] = row1.createCell((short) 1); cell[0].setCellValue("Contact Name"); cellStyleHead.setFont(timesBoldFont); cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT); //cell[0].setCellStyle(cellStyleHead); cell[0].setCellStyle(cs1); worksheet.addMergedRegion(CellRangeAddress.valueOf("B3:B4")); cell[0] = row1.createCell((short) 2); cell[0].setCellValue("Company Name"); cellStyleHead.setFont(timesBoldFont); cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT); //cell[0].setCellStyle(cellStyleHead); cell[0].setCellStyle(cs1); worksheet.addMergedRegion(CellRangeAddress.valueOf("C3:C4")); cell[0] = row1.createCell((short) 3); cell[0].setCellValue("E-Mail"); cellStyleHead.setFont(timesBoldFont); cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT); // cell[0].setCellStyle(cellStyleHead); cell[0].setCellStyle(cs1); worksheet.addMergedRegion(CellRangeAddress.valueOf("D3:D4")); // cell[0] = row1.createCell((short) 4); // cell[0].setCellValue("Phone"); // cellStyleHead.setFont(timesBoldFont); // cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT); // // cell[0].setCellStyle(cellStyleHead); // cell[0].setCellStyle(cs1); // worksheet.addMergedRegion(CellRangeAddress.valueOf("E3:E4")); int count = 4; int len = finalList.size(); if (finalList.size() > 0) { for (int k = 0; k < finalList.size(); k++) { java.util.Map subList = (java.util.Map) finalList.get(k); row1 = worksheet.createRow((short) count++); for (int j = 0; j < 4; j++) { cell[j] = row1.createCell((short) j); } //System.out.println("subList-->"+subList); cell[0].setCellValue((String) subList.get("SNO")); cell[1].setCellValue((String) subList.get("contactName")); cell[2].setCellValue((String) subList.get("CompanyName")); cell[3].setCellValue((String) subList.get("email").toString()); // cell[4].setCellValue((String) subList.get("phone").toString()); for (int h = 0; h < 4; h++) { if (count % 2 == 0) { cell[h].setCellStyle(cs1); } else { cell[h].setCellStyle(cs); } } } // for (int i = 1; i < 4; i++) { worksheet.setColumnWidth(i, 40 * 256); // } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } } catch (FileNotFoundException fne) { fne.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (resultSet != null) { resultSet.close(); resultSet = null; } if (preStmt != null) { preStmt.close(); preStmt = null; } if (connection != null) { connection.close(); connection = null; } } catch (Exception se) { se.printStackTrace(); } } return filePath; }
From source file:com.netxforge.netxstudio.server.logic.reporting.BaseComponentReportingLogic.java
License:Open Source License
public void createHeaderStructure(HSSFSheet sheet) { CellStyle baseStyle = this.getWorkBook().createCellStyle(); baseStyle.setBorderTop(CellStyle.BORDER_MEDIUM); baseStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); baseStyle.setBorderLeft(CellStyle.BORDER_MEDIUM); baseStyle.setBorderRight(CellStyle.BORDER_MEDIUM); baseStyle.setAlignment(CellStyle.ALIGN_LEFT); CellStyle typeStyle = this.getWorkBook().createCellStyle(); typeStyle.cloneStyleFrom(baseStyle); Font typeFont = getWorkBook().createFont(); typeFont.setFontHeightInPoints((short) 24); typeStyle.setFont(typeFont);//from w w w . j a v a 2s. c om HSSFRow typeRow = sheet.createRow(0); typeCell = typeRow.createCell(0); typeCell.setCellValue("<Service Type>"); typeCell.setCellStyle(typeStyle); for (int i = 1; i < HEADER_CELL_SIZE; i++) { typeRow.createCell(i).setCellStyle(typeStyle); } CellStyle titleStyle = this.getWorkBook().createCellStyle(); titleStyle.cloneStyleFrom(baseStyle); Font titleFont = getWorkBook().createFont(); titleFont.setFontHeightInPoints((short) 16); titleStyle.setFont(titleFont); HSSFRow titleRow = sheet.createRow(1); titleCell = titleRow.createCell(0); titleCell.setCellValue("<Report title>"); titleCell.setCellStyle(titleStyle); for (int i = 1; i < HEADER_CELL_SIZE; i++) { titleRow.createCell(i).setCellStyle(titleStyle); } HSSFRow periodRow = sheet.createRow(2); periodCell = periodRow.createCell(0); periodCell.setCellValue("<Period>"); periodCell.setCellStyle(titleStyle); for (int i = 1; i < HEADER_CELL_SIZE; i++) { periodRow.createCell(i).setCellStyle(typeStyle); } // Merge sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, HEADER_CELL_SIZE - 1)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, HEADER_CELL_SIZE - 1)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, HEADER_CELL_SIZE - 1)); }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the bank wise summary report in to Excel for both Inward and Outward. *//from w w w. j a va 2s. co m * @param out ServletOutputStream * */ public void generateNEFTInwBankSummaryReportToExcel(ServletOutputStream out) throws Exception { try { if (returnedList.contains(" ")) { throw new Exception("No Data Found! Cannot Export as Excel Sheet!!"); } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(reportTitle); HSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle fontStyle = wb.createCellStyle(); HSSFFont fontSize = wb.createFont(); fontSize.setFontHeightInPoints((short) 10); fontSize.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontStyle.setFont(fontSize); fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); sheet.setColumnWidth((short) 0, (short) 2000); sheet.setColumnWidth((short) 1, (short) 4000); sheet.setColumnWidth((short) 2, (short) 6000); sheet.setColumnWidth((short) 3, (short) 5000); sheet.setColumnWidth((short) 4, (short) 7000); sheet.setColumnWidth((short) 5, (short) 7000); sheet.setColumnWidth((short) 6, (short) 7000); sheet.setColumnWidth((short) 7, (short) 7000); HSSFCellStyle contentStyle = wb.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle stringStyle = wb.createCellStyle(); stringStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); stringStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle stringRightStyle = wb.createCellStyle(); stringRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); stringRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); stringRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); stringRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); stringRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont headingFont = wb.createFont(); headingFont.setFontHeightInPoints((short) 9); headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headingStyle = wb.createCellStyle(); headingStyle.setFont(headingFont); headingStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headingStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headingStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS); headingStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); headingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle titleRightStyle = wb.createCellStyle(); titleRightStyle.setFont(headingFont); titleRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); titleRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle titleLeftStyle = wb.createCellStyle(); titleLeftStyle.setFont(headingFont); titleLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); titleLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); int rowCount = 2; String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); HSSFRow reportTimeRow = sheet.createRow((short) rowCount); HSSFCell reportTimeRowCell = reportTimeRow.createCell((short) 0); reportTimeRowCell.setCellStyle(titleRightStyle); reportTimeRowCell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7)); rowCount++; HSSFRow stTitle = sheet.createRow((short) rowCount); HSSFCell title = stTitle.createCell((short) 0); title.setCellStyle(fontStyle); title.setCellValue(reportTitle); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7)); rowCount++; HSSFRow stTitleTwo = sheet.createRow((short) rowCount); HSSFCell stTitleCellOne = stTitleTwo.createCell((short) 0); stTitleCellOne.setCellStyle(fontStyle); stTitleCellOne.setCellValue("from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7)); rowCount = rowCount + 2; HSSFRow rowhead = sheet.createRow((short) rowCount); HSSFCell snoHead = rowhead.createCell((short) 0); snoHead.setCellStyle(headingStyle); snoHead.setCellValue("S. NO"); HSSFCell ifscHead = rowhead.createCell((short) 1); ifscHead.setCellStyle(headingStyle); ifscHead.setCellValue("Bank IFSC"); HSSFCell txnCountHead = rowhead.createCell((short) 2); txnCountHead.setCellStyle(headingStyle); txnCountHead.setCellValue("Txn Count"); HSSFCell amountHead = rowhead.createCell((short) 3); amountHead.setCellStyle(headingStyle); amountHead.setCellValue("Sum of Txn AMT (Rs)"); if (reportTitle.equals(inwSummaryReport)) { HSSFCell txnCompletedCountHead = rowhead.createCell((short) 4); txnCompletedCountHead.setCellStyle(headingStyle); txnCompletedCountHead.setCellValue("TXN count Completed/Credited "); HSSFCell txnCompletedCountAmtHead = rowhead.createCell((short) 5); txnCompletedCountAmtHead.setCellStyle(headingStyle); txnCompletedCountAmtHead.setCellValue("Sum of Completed/Credited AMT"); HSSFCell txnRtnCountHead = rowhead.createCell((short) 6); txnRtnCountHead.setCellStyle(headingStyle); txnRtnCountHead.setCellValue("TXN count Inward Returned"); HSSFCell txnRtnCountAmtHead = rowhead.createCell((short) 7); txnRtnCountAmtHead.setCellStyle(headingStyle); txnRtnCountAmtHead.setCellValue("Sum of Inward Returned AMT"); } else { HSSFCell txnCompletedCountHead = rowhead.createCell((short) 4); txnCompletedCountHead.setCellStyle(headingStyle); txnCompletedCountHead.setCellValue("TXN count Settled/Credited "); HSSFCell txnCompletedCountAmtHead = rowhead.createCell((short) 5); txnCompletedCountAmtHead.setCellStyle(headingStyle); txnCompletedCountAmtHead.setCellValue("Sum of Settled/Credited AMT"); HSSFCell txnRtnCountHead = rowhead.createCell((short) 6); txnRtnCountHead.setCellStyle(headingStyle); txnRtnCountHead.setCellValue("TXN count Outward Returned"); HSSFCell txnRtnCountAmtHead = rowhead.createCell((short) 7); txnRtnCountAmtHead.setCellStyle(headingStyle); txnRtnCountAmtHead.setCellValue("Sum of Outward Returned AMT"); } int rowIndex = 1; for (Iterator itr = returnedList.iterator(); itr.hasNext();) { ReportDTO reportDTO = (ReportDTO) itr.next(); HSSFRow row = null; rowCount++; row = sheet.createRow(rowCount); HSSFCell snoCell = row.createCell((short) 0); snoCell.setCellStyle(contentStyle); snoCell.setCellValue(rowIndex); rowIndex++; HSSFCell ifscCell = row.createCell((short) 1); ifscCell.setCellStyle(stringStyle); if (reportDTO.getSenderAddress() != null) { ifscCell.setCellValue(reportDTO.getSenderAddress()); } else { ifscCell.setCellValue(""); } HSSFCell txnCountCell = row.createCell((short) 2); txnCountCell.setCellStyle(stringRightStyle); if (reportDTO.getCount() != 0) { txnCountCell.setCellValue(reportDTO.getCount()); } else { txnCountCell.setCellValue(""); } HSSFCell txnAmountCell = row.createCell((short) 3); txnAmountCell.setCellStyle(stringRightStyle); if (reportDTO.getAmt() != null && !(reportDTO.getAmt().equals("0"))) { txnAmountCell.setCellValue(reportDTO.getAmt()); } else { txnAmountCell.setCellValue(""); } HSSFCell cmpTxnCountCell = row.createCell((short) 4); cmpTxnCountCell.setCellStyle(stringRightStyle); if (reportDTO.getCompletedTxnCount() != 0) { cmpTxnCountCell.setCellValue(reportDTO.getCompletedTxnCount()); } else { cmpTxnCountCell.setCellValue(""); } HSSFCell cmpTxnAmtCell = row.createCell((short) 5); cmpTxnAmtCell.setCellStyle(stringRightStyle); if (reportDTO.getCompletedTxnAmount() != null && !(reportDTO.getCompletedTxnAmount().equals("0"))) { cmpTxnAmtCell.setCellValue(reportDTO.getCompletedTxnAmount()); } else { cmpTxnAmtCell.setCellValue(""); } HSSFCell rtnTxnCountCell = row.createCell((short) 6); rtnTxnCountCell.setCellStyle(stringRightStyle); if (reportDTO.getRtnTxnCount() != 0) { rtnTxnCountCell.setCellValue(reportDTO.getRtnTxnCount()); } else { rtnTxnCountCell.setCellValue(""); } HSSFCell rtnTxnAmtCell = row.createCell((short) 7); rtnTxnAmtCell.setCellStyle(stringRightStyle); if (reportDTO.getRtnTxnAmount() != null && !(reportDTO.getRtnTxnAmount().equals("0"))) { rtnTxnAmtCell.setCellValue(reportDTO.getRtnTxnAmount()); } else { rtnTxnAmtCell.setCellValue(""); } } wb.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }