List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
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;//from ww w.ja va2s . co 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.kazak.comeet.admin.gui.misc.XLSExporter.java
License:Open Source License
private void setColumnsWidth(HSSFSheet sheet, int columnsSize) { for (int j = 0; j < columnsSize; j++) { switch (j) { case 0://www . ja va 2 s .c o m sheet.setColumnWidth((short) j, (short) 3000); break; case 1: sheet.setColumnWidth((short) j, (short) 12000); break; case 2: sheet.setColumnWidth((short) j, (short) 9000); break; default: sheet.setColumnWidth((short) j, (short) 5000); } } }
From source file:com.krawler.esp.servlets.exportExcel.java
License:Open Source License
public void exportexcel(HttpServletResponse response, JSONObject jobj, java.util.Hashtable ht, String sheetTitle, String fileName, JSONArray hdr, JSONArray xlshdr, String heading, String[] xtypeArr, com.krawler.spring.exportFunctionality.exportDAOImpl exportDao) throws ServletException, IOException { try {/*from ww w. ja v a 2 s . co m*/ response.setContentType("application/vnd.ms-excel"); if (!StringUtil.isNullOrEmpty(heading)) { fileName = heading + fileName; } response.setHeader("Content-Disposition", "attachement; filename=" + fileName + ".xls"); HSSFSheet sheet = wb.createSheet(sheetTitle); CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); HSSFHeader hh = sheet.getHeader(); int j = 1; int width = 0; int maxrowno = 0; HSSFRow row1 = sheet.createRow((short) maxrowno); HashMap hm = extractData(jobj); JSONArray jarr = (JSONArray) hm.get("data"); JSONObject tempObj; for (int k = 0; k < jarr.length(); k++) { tempObj = jarr.getJSONObject(k); HSSFRow row = sheet.createRow((short) j); int cellcount = 0; for (int i = 0; i < hdr.length(); i++) { Object str = tempObj.optString(hdr.getString(i), ""); try { if (xtypeArr.length > 0) { str = convertValue(tempObj.optString(hdr.getString(i), ""), xtypeArr[i]); } } catch (Exception e) { } if (ht.containsValue(hdr.getString(i))) { if (j == maxrowno + 1) { HSSFCell cell1 = row1.createCell(cellcount); cell1.setCellStyle(cs); width = xlshdr.getString(i).length() * 325; if (width > sheet.getColumnWidth(cellcount)) { sheet.setColumnWidth(cellcount, width); } HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFRichTextString hst = new HSSFRichTextString(xlshdr.getString(i)); hst.applyFont(font); cell1.setCellValue(hst); } HSSFCell cell = row.createCell(cellcount); cell.setCellStyle(cs); if (str instanceof Date) { cal.setTime((Date) str); cell.setCellValue(cal); cell.setCellStyle(this.dateCellStyle); width = 4500; } else if (str instanceof Number) { cell.setCellValue(((Number) str).doubleValue()); width = 4500; } else { String colvalue = str.toString(); cell.setCellValue(new HSSFRichTextString(colvalue)); width = colvalue.length() * 325; } width = Math.min(width, MAX_CELL_WIDTH); if (width > sheet.getColumnWidth(cellcount)) { sheet.setColumnWidth(cellcount, width); } cellcount++; } } j++; } ConfigReader cr = ConfigReader.getinstance(); String dirpath = cr.get("store"); String path = dirpath + "baitheader.png"; // this.addimage(path,HSSFWorkbook.PICTURE_TYPE_PNG, wb, sheet,0,0,0,0,0,0,12,4); if (true) { OutputStream out = response.getOutputStream(); wb.write(out); out.close(); } } catch (JSONException ex) { Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception ex) { Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex); } }
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.//from w w w .j av a 2 s. c o m */ 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.lingxiang2014.ExcelView.java
License:Open Source License
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties);//from w ww. ja v a 2 s . co m 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" + " " + "S" + "H" + "O" + "P" + "+" + "+")); 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.mmj.app.common.file.ExcelUtils.java
License:Open Source License
/** * excelrow?IExcel.initHSSRow/*www .j a v a 2 s .co m*/ * * @param response * @param list * @param name * @param iExcel * @param headTitle * @return */ public static <T> HSSFWorkbook buildExcel(List<T> list, String name, IExcel<T> iExcel, String... headTitle) { if (list == null || list.size() == 0) { log.error("ExcelUtils buildExcel List<T>:list is null,name={}", name); return null; } if (iExcel == null || Argument.isEmptyArray(headTitle)) { return new HSSFWorkbook(); } String xlsName = name + "_" + DateViewTools.format(new Date(), "yyyy_MM_dd_HH_mm"); // HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, xlsName); for (int i = 0; i < headTitle.length; i++) { // ? sheet.setColumnWidth(i, 17 * 256); } HSSFRow rowTitle = sheet.createRow(0); HSSFCell cell = null; for (int i = 0; i < headTitle.length; i++) { // ? cell = rowTitle.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(headTitle[i]); } // row iExcel.initHSSRow(list, sheet); return workbook; }
From source file:com.mss.mirage.marketing.MarketingServiceImpl.java
License:Open Source License
public String generateInvestmentXls(String queryString) throws ServiceLocatorException { String filePath = ""; StringBuffer sb = null;/*w w w.ja v a2 s .c o m*/ 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 */ /** * The statement is useful to execute the above queryString */ ResultSet resultSet = null; HashMap map = null; double totalAmount = 0.0; double totalOpprtunity = 0.0; double floortotalsum = 0.0; String generatedPath = ""; List finalList = new ArrayList(); try { generatedPath = com.mss.mirage.util.Properties.getProperty("Marketing.Investment.Path"); File file = new File(generatedPath); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream(file.getAbsolutePath() + "/Investment.xls"); connection = ConnectionProvider.getInstance().getConnection(); String query = null; if (!"".equals(queryString)) { query = queryString; } else { query = "SELECT * from vwInvestments WHERE STATUS='Active' ORDER BY createdDate DESC"; } String reportToName = ""; List teamList = null; int j = 1; // System.out.println("query...."+query); preStmt = connection.prepareStatement(query); resultSet = preStmt.executeQuery(); while (resultSet.next()) { String InvestmentName = resultSet.getString("Inv_Name"); String TotalExpenses = resultSet.getString("TotalExpenses"); String StartDate = resultSet.getString("StartDate"); String EndDate = resultSet.getString("EndDate"); String Location = resultSet.getString("Location"); String InvestmentType = resultSet.getString("InvestmentType"); String TotalOpprtunity = resultSet.getString("TotalOpprtunity"); totalAmount = totalAmount + resultSet.getDouble("TotalExpenses"); totalOpprtunity = totalOpprtunity + resultSet.getDouble("TotalOpprtunity"); map = new HashMap(); map.put("SNO", String.valueOf(j)); map.put("InvestmentName", InvestmentName); map.put("TotalExpenses", TotalExpenses); map.put("StartDate", StartDate); map.put("EndDate", EndDate); map.put("Location", Location); if ("S".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Source"); } else if ("P".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Pass"); } map.put("TotalOpprtunity", TotalOpprtunity); map.put("Sum", totalAmount); map.put("SumOpp", totalOpprtunity); finalList.add(map); j++; } if (finalList.size() > 0) { filePath = file.getAbsolutePath() + "/Investment.xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("Investment Sheet"); HSSFFont timesBoldFont1 = hssfworkbook.createFont(); timesBoldFont1.setFontHeightInPoints((short) 13); timesBoldFont1.setColor(HSSFColor.BLACK.index); timesBoldFont1.setFontName("Arial"); HSSFCellStyle cellColor = hssfworkbook.createCellStyle(); cellColor.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); cellColor.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor.setBorderTop((short) 1); // single line border cellColor.setBorderBottom((short) 1); // single line border cellColor.setFont(timesBoldFont1); HSSFCellStyle cellColor1 = hssfworkbook.createCellStyle(); cellColor1.setFillForegroundColor(HSSFColor.WHITE.index); cellColor1.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor1.setBorderTop((short) 1); // single line border cellColor1.setBorderBottom((short) 1); // single line border cellColor1.setFont(timesBoldFont1); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFCellStyle headercs = hssfworkbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.BLUE.index); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border // cs.setFont(timesBoldFont1); HSSFFont timesBoldFont = hssfworkbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Calibri"); headercs.setFont(timesBoldFont); // cs.setFont(timesBoldFont); HSSFFont footerFont = hssfworkbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); footerFont.setFontName("Calibri"); HSSFCellStyle footercs = hssfworkbook.createCellStyle(); footercs.setFont(footerFont); HSSFDataFormat df = hssfworkbook.createDataFormat(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); HSSFCell cell1 = row.createCell((short) 1); HSSFCell cell2 = row.createCell((short) 2); HSSFCell cell3 = row.createCell((short) 3); HSSFCell cell4 = row.createCell((short) 4); HSSFCell cell5 = row.createCell((short) 5); HSSFCell cell6 = row.createCell((short) 6); HSSFCell cell7 = row.createCell((short) 7); cell.setCellValue("SNO"); cell1.setCellValue("InvestmentName"); cell2.setCellValue("TotalExpenses"); cell3.setCellValue("StartDate"); cell4.setCellValue("EndDate"); cell5.setCellValue("Location"); cell6.setCellValue("InvestmentType"); cell7.setCellValue("TotalOpprtunity"); cell.setCellStyle(headercs); cell1.setCellStyle(headercs); cell2.setCellStyle(headercs); cell3.setCellStyle(headercs); cell4.setCellStyle(headercs); cell5.setCellStyle(headercs); cell6.setCellStyle(headercs); cell7.setCellStyle(headercs); int count = 1; if (finalList.size() > 0) { Map stateHistorylMap = null; for (int i = 0; i < finalList.size(); i++) { stateHistorylMap = (Map) finalList.get(i); row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue((String) stateHistorylMap.get("SNO")); cell1.setCellValue((String) stateHistorylMap.get("InvestmentName")); HSSFCellStyle css1 = hssfworkbook.createCellStyle(); HSSFCellStyle css2 = hssfworkbook.createCellStyle(); css1.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); css1.setAlignment(HSSFCellStyle.ALIGN_LEFT); css1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); css1.setBorderTop((short) 1); // single line border css1.setBorderBottom((short) 1); // single line border css1.setFont(timesBoldFont1); HSSFDataFormat df1 = hssfworkbook.createDataFormat(); css1.setDataFormat(df1.getFormat("#,##0.0")); css2.setDataFormat(df1.getFormat("#,##0.0")); css2.setAlignment(HSSFCellStyle.ALIGN_LEFT); css2.setFont(timesBoldFont1); cell2.setCellValue(Convert.toDouble(stateHistorylMap.get("TotalExpenses"))); cell3.setCellValue((String) stateHistorylMap.get("StartDate")); cell4.setCellValue((String) stateHistorylMap.get("EndDate")); cell5.setCellValue((String) stateHistorylMap.get("Location")); cell6.setCellValue((String) stateHistorylMap.get("InvestmentType")); cell7.setCellValue((String) stateHistorylMap.get("TotalOpprtunity")); if (count % 2 == 0) { cell.setCellStyle(cellColor1); cell1.setCellStyle(cellColor1); cell2.setCellStyle(css2); cell3.setCellStyle(cellColor1); cell4.setCellStyle(cellColor1); cell5.setCellStyle(cellColor1); cell6.setCellStyle(cellColor1); cell7.setCellStyle(cellColor1); } else { cell.setCellStyle(cellColor); cell1.setCellStyle(cellColor); cell2.setCellStyle(css1); cell3.setCellStyle(cellColor); cell4.setCellStyle(cellColor); cell5.setCellStyle(cellColor); cell6.setCellStyle(cellColor); cell7.setCellStyle(cellColor); } } row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue(""); cell7.setCellValue(""); cell.setCellStyle(footercs); cell1.setCellStyle(footercs); cell2.setCellStyle(footercs); cell3.setCellStyle(footercs); cell4.setCellStyle(footercs); cell5.setCellStyle(footercs); cell6.setCellStyle(footercs); cell7.setCellStyle(footercs); } HSSFCellStyle totalSum = hssfworkbook.createCellStyle(); totalSum.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum.setAlignment(HSSFCellStyle.ALIGN_LEFT); totalSum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum.setBorderTop((short) 1); // single line border totalSum.setBorderBottom((short) 1); // single line border totalSum.setFont(timesBoldFont1); HSSFCellStyle totalSum1 = hssfworkbook.createCellStyle(); totalSum1.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum1.setAlignment(HSSFCellStyle.ALIGN_RIGHT); totalSum1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum1.setBorderTop((short) 1); // single line border totalSum1.setBorderBottom((short) 1); // single line border totalSum1.setFont(timesBoldFont1); HSSFDataFormat totalSumdf1 = hssfworkbook.createDataFormat(); totalSum.setDataFormat((short) 7); HSSFCellStyle test = hssfworkbook.createCellStyle(); HSSFDataFormat testdf = hssfworkbook.createDataFormat(); sheet.autoSizeColumn((int) 0); sheet.autoSizeColumn((int) 3); sheet.autoSizeColumn((int) 4); sheet.setColumnWidth(1, 50 * 256); sheet.setColumnWidth(2, 35 * 256); sheet.setColumnWidth(5, 25 * 256); sheet.setColumnWidth(6, 25 * 256); sheet.setColumnWidth(7, 25 * 256); BigDecimal bb, bc, cc, cd; bb = new BigDecimal(totalAmount); bc = bb.setScale(2, RoundingMode.CEILING); cc = new BigDecimal(totalOpprtunity); cd = cc.setScale(2, RoundingMode.CEILING); totalSum.setDataFormat(testdf.getFormat("#,##0.0")); cell.setCellStyle(totalSum); cell1.setCellValue("Sum "); cell1.setCellStyle(totalSum1); cell2.setCellValue(bc.longValue()); cell2.setCellStyle(totalSum); cell3.setCellStyle(totalSum); cell4.setCellStyle(totalSum); cell5.setCellStyle(totalSum); cell6.setCellStyle(totalSum); cell7.setCellValue(cd.longValue()); cell7.setCellStyle(totalSum); hssfworkbook.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.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;// ww w. j a v a 2s . 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.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 ww. j a v a 2 s . c om * @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); } }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the bank wise detailed report in to Excel for both Inward and Outward. *//from w ww . j a va2s. c om * @param out ServletOutputStream * */ public void generateNEFTInwBankDetailedReportToExcel(ServletOutputStream out) throws Exception { try { 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) 3000); sheet.setColumnWidth((short) 5, (short) 6000); sheet.setColumnWidth((short) 6, (short) 3000); sheet.setColumnWidth((short) 7, (short) 6000); sheet.setColumnWidth((short) 8, (short) 2500); sheet.setColumnWidth((short) 9, (short) 6000); sheet.setColumnWidth((short) 10, (short) 3000); sheet.setColumnWidth((short) 11, (short) 6000); sheet.setColumnWidth((short) 12, (short) 4500); 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); 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) 12)); 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) 12)); 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) 12)); rowCount = rowCount + 2; if (getReportMap().size() != 0) { Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, List<TransactionInfo>> entry = (Map.Entry<String, List<TransactionInfo>>) z .next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); String bankName = entry.getKey().substring(0, 4); String sumTxnAmount = entry.getKey().substring(5); HSSFRow titleRowOne = sheet.createRow((short) rowCount); HSSFCell bankNameCell = titleRowOne.createCell((short) 0); bankNameCell.setCellStyle(titleRightStyle); if (getTranType().equals("inward")) { bankNameCell.setCellValue("Sender Bank : "); } else { bankNameCell.setCellValue(" Receiver Bank : "); } sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell bankNameValCell = titleRowOne.createCell((short) 7); bankNameValCell.setCellStyle(titleLeftStyle); bankNameValCell.setCellValue(bankName); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRowTwo = sheet.createRow((short) rowCount); HSSFCell txnCountCell = titleRowTwo.createCell((short) 0); txnCountCell.setCellStyle(titleRightStyle); txnCountCell.setCellValue("Txn Count : "); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell txnCountValCell = titleRowTwo.createCell((short) 7); txnCountValCell.setCellStyle(titleLeftStyle); txnCountValCell.setCellValue(exportXLS.size()); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRowThree = sheet.createRow((short) rowCount); HSSFCell sumTxnAmountCell = titleRowThree.createCell((short) 0); sumTxnAmountCell.setCellStyle(titleRightStyle); sumTxnAmountCell.setCellValue("Sum of Txn Amount : "); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell sumTxnAmountValCell = titleRowThree.createCell((short) 7); sumTxnAmountValCell.setCellStyle(titleLeftStyle); sumTxnAmountValCell.setCellValue(sumTxnAmount); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRow = sheet.createRow((short) rowCount); HSSFCell beneficiaryTitle = titleRow.createCell((short) 4); beneficiaryTitle.setCellStyle(headingStyle); beneficiaryTitle.setCellValue("Beneficiary Details"); sheet.addMergedRegion(new Region(rowCount, (short) 4, rowCount, (short) 7)); HSSFCell senderTitle = titleRow.createCell((short) 8); senderTitle.setCellStyle(headingStyle); senderTitle.setCellValue("Sender's Details"); sheet.addMergedRegion(new Region(rowCount, (short) 8, rowCount, (short) 11)); rowCount++; HSSFRow rowhead = sheet.createRow((short) rowCount); HSSFCell snoHead = rowhead.createCell((short) 0); snoHead.setCellStyle(headingStyle); snoHead.setCellValue("S .NO"); HSSFCell valueDateHead = rowhead.createCell((short) 1); valueDateHead.setCellStyle(headingStyle); valueDateHead.setCellValue("Value Date"); HSSFCell tranRefHead = rowhead.createCell((short) 2); tranRefHead.setCellStyle(headingStyle); tranRefHead.setCellValue("Transaction Ref. No"); HSSFCell amountHead = rowhead.createCell((short) 3); amountHead.setCellStyle(headingStyle); amountHead.setCellValue("Amount(Rs)"); HSSFCell benIfscHead = rowhead.createCell((short) 4); benIfscHead.setCellStyle(headingStyle); benIfscHead.setCellValue("IFSC"); HSSFCell benACNameHead = rowhead.createCell((short) 5); benACNameHead.setCellStyle(headingStyle); benACNameHead.setCellValue("A/c Name"); HSSFCell benACTypeHead = rowhead.createCell((short) 6); benACTypeHead.setCellStyle(headingStyle); benACTypeHead.setCellValue("A/c Type"); HSSFCell benACNoHead = rowhead.createCell((short) 7); benACNoHead.setCellStyle(headingStyle); benACNoHead.setCellValue("A/c No"); HSSFCell senIfscHead = rowhead.createCell((short) 8); senIfscHead.setCellStyle(headingStyle); senIfscHead.setCellValue("IFSC"); HSSFCell senACNameHead = rowhead.createCell((short) 9); senACNameHead.setCellStyle(headingStyle); senACNameHead.setCellValue("A/c Name"); HSSFCell senACTypeHead = rowhead.createCell((short) 10); senACTypeHead.setCellStyle(headingStyle); senACTypeHead.setCellValue("A/c Type"); HSSFCell senACNoHead = rowhead.createCell((short) 11); senACNoHead.setCellStyle(headingStyle); senACNoHead.setCellValue("A/c No"); HSSFCell tranStatusHead = rowhead.createCell((short) 12); tranStatusHead.setCellStyle(headingStyle); tranStatusHead.setCellValue("Transaction Status"); HSSFRow row = null; for (int i = exportXLS.size(), rowIndex = 0; rowIndex < i; rowIndex++) { TransactionInfo ti = (TransactionInfo) exportXLS.get(rowIndex); rowCount++; row = sheet.createRow(rowCount); HSSFCell snoCell = row.createCell((short) 0); snoCell.setCellStyle(contentStyle); snoCell.setCellValue(rowIndex + 1); HSSFCell valueDateCell = row.createCell((short) 1); valueDateCell.setCellStyle(stringStyle); if (ti.getValueDate() != null) { valueDateCell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, ti.getValueDate().toString())); } else { valueDateCell.setCellValue(""); } HSSFCell tranRefCell = row.createCell((short) 2); tranRefCell.setCellStyle(stringStyle); if (ti.getUtrNo() != null) { tranRefCell.setCellValue(ti.getUtrNo()); } else { tranRefCell.setCellValue(""); } HSSFCell amountCell = row.createCell((short) 3); amountCell.setCellStyle(stringStyle); if (ti.getAmount() != null) { amountCell.setCellValue(ti.getAmount().toString()); } else { amountCell.setCellValue(""); } HSSFCell benIfscCell = row.createCell((short) 4); benIfscCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccIfsc() != null) { benIfscCell.setCellValue(ti.getBeneficiaryInfo().getAccIfsc()); } else { benIfscCell.setCellValue(""); } HSSFCell benACNameCell = row.createCell((short) 5); benACNameCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccName() != null) { benACNameCell.setCellValue(ti.getBeneficiaryInfo().getAccName()); } else { benACNameCell.setCellValue(""); } HSSFCell benACTypeCell = row.createCell((short) 6); benACTypeCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccType() != null) { benACTypeCell.setCellValue(ti.getBeneficiaryInfo().getAccType()); } else { benACTypeCell.setCellValue(""); } HSSFCell benACNoCell = row.createCell((short) 7); benACNoCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccNo() != null) { benACNoCell.setCellValue(ti.getBeneficiaryInfo().getAccNo()); } else { benACNoCell.setCellValue(""); } HSSFCell senIfscCell = row.createCell((short) 8); senIfscCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccIfsc() != null) { senIfscCell.setCellValue(ti.getSenderInfo().getAccIfsc()); } else { senIfscCell.setCellValue(""); } HSSFCell senACNameCell = row.createCell((short) 9); senACNameCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccName() != null) { senACNameCell.setCellValue(ti.getSenderInfo().getAccName()); } else { senACNameCell.setCellValue(""); } HSSFCell senACTypeCell = row.createCell((short) 10); senACTypeCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccType() != null) { senACTypeCell.setCellValue(ti.getSenderInfo().getAccType()); } else { senACTypeCell.setCellValue(""); } HSSFCell senACNoCell = row.createCell((short) 11); senACNoCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccNo() != null) { senACNoCell.setCellValue(ti.getSenderInfo().getAccNo()); } else { senACNoCell.setCellValue(""); } HSSFCell tranStatusCell = row.createCell((short) 12); tranStatusCell.setCellStyle(stringStyle); if (ti != null) { tranStatusCell.setCellValue(ti.getStatusShortDesc()); } else { tranStatusCell.setCellValue(""); } } rowCount = rowCount + 3; } } 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); } }