List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
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 www . j a v a 2 s.c o 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.modelmetrics.common.poi.ExcelSupport.java
License:Open Source License
public void decorateRowWithBoldCellBlueBackground(int cellId, HSSFRow row, Object value) { HSSFCellStyle boldStyleBlueBground = workbook.createCellStyle(); boldStyleBlueBground.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); boldStyleBlueBground.setFont(boldFont); boldStyleBlueBground.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); boldStyleBlueBground.setFillForegroundColor(new HSSFColor.LIGHT_TURQUOISE().getIndex()); boldStyleBlueBground.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); boldStyleBlueBground.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); boldStyleBlueBground.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); boldStyleBlueBground.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); this.decorateRowWithCell((short) cellId, row, value, boldStyleBlueBground); }
From source file:com.modelmetrics.common.poi.ExcelSupport.java
License:Open Source License
public void decorateRowWithBoldCellYellowBackground(int cellId, HSSFRow row, Object value) { HSSFCellStyle boldStyleBlueBground = workbook.createCellStyle(); boldStyleBlueBground.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); boldStyleBlueBground.setFont(boldFont); boldStyleBlueBground.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); boldStyleBlueBground.setFillForegroundColor(new HSSFColor.LIGHT_YELLOW().getIndex()); // boldStyleBlueBground.setBorderBottom(HSSFCellStyle.BORDER_HAIR); // boldStyleBlueBground.setBorderLeft(HSSFCellStyle.BORDER_HAIR); // boldStyleBlueBground.setBorderRight(HSSFCellStyle.BORDER_HAIR); // boldStyleBlueBground.setBorderTop(HSSFCellStyle.BORDER_HAIR); this.decorateRowWithCell((short) cellId, row, value, boldStyleBlueBground); }
From source file:com.mss.mirage.employee.general.EmployeeServiceImpl.java
License:Open Source License
public String generateEmployeeList(String loginId) { DateUtility dateutility = new DateUtility(); String filePath = ""; StringBuffer sb = null;//from w w w. ja va 2 s.c o m Connection connection = null; /** callableStatement is a reference variable for CallableStatement . */ CallableStatement callableStatement = null; /** preStmt,preStmtTemp are reference variable for PreparedStatement . */ PreparedStatement preStmt = null, preStmtTemp = null; /** The queryString is useful to get queryString result to the particular jsp page */ String queryString = ""; Statement statement = null; /** The statement is useful to execute the above queryString */ ResultSet resultSet = null; String timeSheetStatus = ""; HashMap map = null; HashMap map1 = null; List finalList = new ArrayList(); try { String TABLE_EMP_STATE_HISTORY = Properties.getProperty("TABLE_EMP_STATE_HISTORY"); File file = new File(Properties.getProperty("Emp.StateHistory.Path")); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream( file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls"); connection = ConnectionProvider.getInstance().getConnection(); String query = null; // query = "SELECT * FROM tblEmpStateHistory WHERE LoginId='"+loginId+"' ORDER BY StartDate DESC"; query = "SELECT * FROM " + TABLE_EMP_STATE_HISTORY + " WHERE LoginId='" + loginId + "' ORDER BY StartDate DESC"; // System.out.println("query123-->"+query); String reportToName = ""; List teamList = null; int j = 1; preStmt = connection.prepareStatement(query); resultSet = preStmt.executeQuery(); while (resultSet.next()) { String state = ""; if (!"".equals(resultSet.getString("State")) && resultSet.getString("State") != null) { state = resultSet.getString("State"); } double IntRatePerHour = resultSet.getFloat("IntRatePerHour"); double InvRatePerHour = resultSet.getFloat("InvRatePerHour"); String LoginId = resultSet.getString("LoginId"); String SkillSet = ""; if (!"".equals(resultSet.getString("SkillSet")) && resultSet.getString("SkillSet") != null) { SkillSet = resultSet.getString("SkillSet"); } String EndDate = ""; if (!"".equals(resultSet.getString("EndDate")) && resultSet.getString("EndDate") != null) { EndDate = resultSet.getString("EndDate"); } String StartDate = ""; if (!"".equals(resultSet.getString("StartDate")) && resultSet.getString("StartDate") != null) { StartDate = resultSet.getString("StartDate"); } String CreatedDate = ""; if (!"".equals(resultSet.getString("CreatedDate")) && resultSet.getString("CreatedDate") != null) { CreatedDate = resultSet.getString("CreatedDate"); } String PrjName = ""; if (!"".equals(resultSet.getString("PrjName")) && resultSet.getString("PrjName") != null) { PrjName = resultSet.getString("PrjName"); } String reportsTo = ""; map = new HashMap(); map.put("SNO", String.valueOf(j)); map.put("state", state); map.put("IntRatePerHour", IntRatePerHour); map.put("InvRatePerHour", InvRatePerHour); map.put("LoginId", LoginId); map.put("SkillSet", SkillSet); map.put("EndDate", EndDate); map.put("StartDate", StartDate); map.put("CreatedDate", CreatedDate); map.put("PrjName", PrjName); //System.out.println("map=="+map); finalList.add(map); j++; } if (finalList.size() > 0) { filePath = file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("State History"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFCellStyle headercs = hssfworkbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.BLACK.index); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border HSSFFont timesBoldFont = hssfworkbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Arial"); headercs.setFont(timesBoldFont); HSSFFont footerFont = hssfworkbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); footerFont.setFontName("Arial"); 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); HSSFCell cell8 = row.createCell((short) 8); cell.setCellValue("SNO"); cell1.setCellValue("State"); cell2.setCellValue("StartDate"); cell3.setCellValue("EndDate"); cell4.setCellValue("IntRatePerHour"); cell5.setCellValue("InvRatePerHour"); cell6.setCellValue("SkillSet"); cell7.setCellValue("ProjectName"); cell8.setCellValue("CreatedDate"); cell.setCellStyle(headercs); cell1.setCellStyle(headercs); cell2.setCellStyle(headercs); cell3.setCellStyle(headercs); cell4.setCellStyle(headercs); cell5.setCellStyle(headercs); cell6.setCellStyle(headercs); cell7.setCellStyle(headercs); cell8.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); cell8 = row.createCell((short) 8); cell.setCellValue((String) stateHistorylMap.get("SNO")); cell1.setCellValue((String) stateHistorylMap.get("state")); cell2.setCellValue((String) stateHistorylMap.get("StartDate")); cell3.setCellValue((String) stateHistorylMap.get("EndDate")); cell4.setCellValue((Double) stateHistorylMap.get("IntRatePerHour")); cell5.setCellValue((Double) stateHistorylMap.get("InvRatePerHour")); cell6.setCellValue((String) stateHistorylMap.get("SkillSet")); cell7.setCellValue((String) stateHistorylMap.get("PrjName")); cell8.setCellValue((String) stateHistorylMap.get("CreatedDate")); cell.setCellStyle(cs); cell1.setCellStyle(cs); cell2.setCellStyle(cs); cell3.setCellStyle(cs); cell4.setCellStyle(cs); cell5.setCellStyle(cs); cell6.setCellStyle(cs); cell7.setCellStyle(cs); cell8.setCellStyle(cs); } 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); cell.setCellValue(""); cell4.setCellValue(""); cell.setCellStyle(footercs); cell1.setCellStyle(footercs); cell2.setCellStyle(footercs); cell3.setCellStyle(footercs); cell4.setCellStyle(footercs); } sheet.autoSizeColumn((int) 0); sheet.autoSizeColumn((int) 1); sheet.autoSizeColumn((int) 2); sheet.autoSizeColumn((int) 3); sheet.autoSizeColumn((int) 4); hssfworkbook.write(fileOut); fileOut.flush(); fileOut.close(); } } catch (FileNotFoundException fne) { fne.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { System.out.println("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 generateInvestmentXls(String queryString) throws ServiceLocatorException { String filePath = ""; StringBuffer sb = null;// ww w . ja v a 2 s. co 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;//from w w w . ja va2 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 */ 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.nokia.s60tools.swmtanalyser.model.ExcelCreator.java
License:Open Source License
/** * create set of cell styles/*from www .ja v a 2s.c o m*/ */ private Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) { Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>(); HSSFCellStyle style; HSSFFont headerFont = wb.createFont(); headerFont.setColor(HSSFColor.WHITE.index); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); headerFont.setFontHeightInPoints((short) 18); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 12); font1.setFontName(HSSFFont.FONT_ARIAL); font1.setColor(HSSFColor.WHITE.index); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font1); style.setWrapText(true); styles.put("header2", style); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font1); style.setWrapText(true); styles.put("header1", style); HSSFFont font3 = wb.createFont(); font3.setColor(HSSFColor.BLACK.index); font3.setFontHeightInPoints((short) 10); font3.setFontName(HSSFFont.FONT_ARIAL); font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFont(font3); styles.put("cell_bold", style); HSSFFont font5 = wb.createFont(); font5.setColor(HSSFColor.BLACK.index); font5.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFont(font5); styles.put("cell_normal", style); HSSFFont font4 = wb.createFont(); font4.setFontHeightInPoints((short) 10); font4.setColor(HSSFColor.WHITE.index); style = createBorderedStyle(wb); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font4); styles.put("cell_blue_font_white", style); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styles.put("cell_number", style); HSSFFont blue_font = wb.createFont(); blue_font.setFontHeightInPoints((short) 10); blue_font.setColor(HSSFColor.BLUE.index); style = createBorderedStyle(wb); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setFont(blue_font); styles.put("blue_font", style); return styles; }
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the paymentreport in to Excel *///w w w. ja v a 2s .c om public void paymentExportToExcel(ServletOutputStream out) throws Exception { try { List exportXLS = new ArrayList(1); long sno = 0; int rowCount = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; //Adding the items to a list for (Iterator i = getDetailReportDTOs().iterator(); i.hasNext();) { NEFTDetailsReportDTO indentList = (NEFTDetailsReportDTO) i.next(); exportXLS.add(indentList); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; if (report.equalsIgnoreCase("submitted")) { book.setSheetName(0, "Payments Submitted", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); } else { book.setSheetName(0, "Payments Received", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); } caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); if (report.equalsIgnoreCase("submitted")) { cell.setCellValue("Payment Submitted Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } else { cell.setCellValue("Payment Received Report " + reportDto.getPaymentType() + " From " + reportDto.getValueDate() + " to " + reportDto.getToDate() + " with status " + reportDto.getStatusValue()); } for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 9; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("VALUE DATE"); } else if (j == 2) { cell.setCellValue("SENDER ADDRESS"); } else if (j == 3) { cell.setCellValue("RECEIVER ADDRESS"); } else if (j == 4) { cell.setCellValue("UTR NUMBER"); } else if (j == 5) { cell.setCellValue("ACCOUNT NUMBER"); } else if (j == 6) { cell.setCellValue("BENIFICIARY DETAILS"); } else if (j == 7) { cell.setCellValue("AMOUNT(Rs)"); } else if (j == 8) { cell.setCellValue("STATUS"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String valueDate = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate() != null) { valueDate = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getValueDate(); } cell.setCellValue(valueDate); } else if (j == 2) { String sendAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 3) { String recAdd = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 4) { String utrNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 5) { String accNo = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6021(); } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6061() != null) { accNo = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6061(); } cell.setCellValue(accNo); } else if (j == 6) { String beniDetails = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField5565(); if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6081() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6081() + "-" + beniDetails; } } else if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)) .getField6091() != null) { beniDetails = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getField6091(); } cell.setCellValue(beniDetails); } else if (j == 7) { String amt = null; if (new BigDecimal(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((NEFTDetailsReportDTO)exportXLS // .get(roww - 1)).getAmount(); totAmt = totAmt.add(new BigDecimal( ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()).setScale(2)); amt = String.valueOf(((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } else if (j == 8) { //To add status column in the excel sheet. String status = null; if (((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus() != null) { status = ((NEFTDetailsReportDTO) exportXLS.get(roww - 1)).getStatus(); } cell.setCellValue(status); } } cell.setCellStyle(caption_style); } } row = sheet.createRow(rowCount); cell = row.createCell((short) 6); cell.setCellValue("TOTAL AMOUNT"); cell.setCellStyle(caption_style); cell = row.createCell((short) 7); cell.setCellValue(String.valueOf(totAmt)); cell.setCellStyle(caption_style); sheet = book.createSheet(); book.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 graduated paymentreport in to Excel *///from w ww. j a va2 s . c o m public void graduatedPaymentExportToExcel(ServletOutputStream out) throws Exception { try { List<ReportDTO> exportXLS = new ArrayList<ReportDTO>(1); long sno = 0; int rowCount = 0; //double totAmt = 0; //Adding the items to a list for (Iterator i = getGraduadtedPayments().iterator(); i.hasNext();) { ReportDTO dto = (ReportDTO) i.next(); exportXLS.add(dto); } //Only If the list is not empty if (exportXLS.size() != 0) { // start to export excel HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; book.setSheetName(0, "Graduated Payment", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 0); cell.setCellValue(""); cell = row.createCell((short) 1); cell.setCellValue("Datewise Graduated Payment Report"); row = sheet.createRow(rowCount); rowCount += 1; cell = row.createCell((short) 10); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue("Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); rowCount += 1; for (int i = exportXLS.size(), roww = 0; roww <= i; roww++) { row = sheet.createRow(rowCount); rowCount += 1; for (short j = 0; j < 13; j++) { cell = row.createCell(j); // for header if (roww == 0) { if (j == 0) { cell.setCellValue("S.No"); } else if (j == 1) { cell.setCellValue("UTR No"); } else if (j == 2) { cell.setCellValue("Msg Type"); } else if (j == 3) { cell.setCellValue("Tran Type"); } else if (j == 4) { cell.setCellValue("Sender Address"); } else if (j == 5) { cell.setCellValue("Receiver Address"); } else if (j == 6) { cell.setCellValue("Credit Amount(Rs)"); } else if (j == 7) { cell.setCellValue("Debit Amount(Rs)"); } else if (j == 8) { cell.setCellValue("Batch Time"); } else if (j == 9) { cell.setCellValue("Rescheduled Date"); } else if (j == 10) { cell.setCellValue("Rescheduled Batch Time"); } else if (j == 11) { cell.setCellValue("Rejected Date"); } else if (j == 12) { cell.setCellValue("Rejected Batch Time"); } } else { // Setting values in cell for each and every row if (j == 0) { String no = null; sno += 1; no = String.valueOf(sno); cell.setCellValue(no); } else if (j == 1) { String utrNo = null; if ((exportXLS.get(roww - 1)).getUtrNo() != null) { utrNo = (exportXLS.get(roww - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 2) { String msgType = null; if ((exportXLS.get(roww - 1)).getMsgType() != null) { msgType = (exportXLS.get(roww - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String tranType = null; if ((exportXLS.get(roww - 1)).getTranType() != null) { tranType = (exportXLS.get(roww - 1)).getTranType(); } cell.setCellValue(tranType); } else if (j == 4) { String sendAdd = null; if ((exportXLS.get(roww - 1)).getSenderAddress() != null) { sendAdd = (exportXLS.get(roww - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if ((exportXLS.get(roww - 1)).getReceiverAddress() != null) { recAdd = (exportXLS.get(roww - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { //Modified by priyak to maintain uniformity String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Credit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 7) { String crdDeb = null; String amount = null; if ((exportXLS.get(roww - 1)).getDebitCredit() != null) { crdDeb = (exportXLS.get(roww - 1)).getDebitCredit(); if (crdDeb.equals("Debit")) { amount = (exportXLS.get(roww - 1)).getAmt(); } else { amount = "0.00"; } } cell.setCellValue(amount); } else if (j == 8) { String batchTime = null; if ((exportXLS.get(roww - 1)).getBatchTime() != null) { batchTime = (exportXLS.get(roww - 1)).getBatchTime(); } cell.setCellValue(batchTime); } else if (j == 9) { String reshDate = null; if ((exportXLS.get(roww - 1)).getReshDate() != null) { reshDate = (exportXLS.get(roww - 1)).getReshDate(); reshDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, reshDate); } cell.setCellValue(reshDate); } else if (j == 10) { String reshBatchTime = null; if ((exportXLS.get(roww - 1)).getReshBatchTime() != null) { reshBatchTime = (exportXLS.get(roww - 1)).getReshBatchTime(); } cell.setCellValue(reshBatchTime); } else if (j == 11) { String rejDate = null; if ((exportXLS.get(roww - 1)).getRejDate() != null) { rejDate = (exportXLS.get(roww - 1)).getRejDate(); rejDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, rejDate); } cell.setCellValue(rejDate); } else if (j == 12) { String rejBatchTime = null; if ((exportXLS.get(roww - 1)).getRejBatchTime() != null) { rejBatchTime = (exportXLS.get(roww - 1)).getRejBatchTime(); } cell.setCellValue(rejBatchTime); } } cell.setCellStyle(caption_style); } } sheet = book.createSheet(); book.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 Br inward returned report in to Excel */// www .ja v a 2 s.c om public void returnedInwardExportToExcel(ServletOutputStream out) throws Exception { try { int roww = 0; // double totAmt = 0; BigDecimal totAmt = BigDecimal.ZERO; HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow row = null; HSSFCell cell = null; HSSFFont caption_font = null; HSSFCellStyle caption_style = null; //int roww = 0; Set keySet = getReportMap().keySet(); Iterator it = keySet.iterator(); book.setSheetName(0, "Inward Returned", HSSFWorkbook.ENCODING_COMPRESSED_UNICODE); caption_font = book.createFont(); caption_font.setFontHeightInPoints((short) 10); caption_font.setFontName("Verdana"); caption_style = book.createCellStyle(); caption_style.setFont(caption_font); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("Branch Report - Inward Returned from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 5); String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); cell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); roww += 1; while (it.hasNext()) { List exportXLS = new ArrayList(1); // double subTotal = 0; BigDecimal subTotal = BigDecimal.ZERO; String date = (String) it.next(); List listRep = (List) reportMap.get(date); for (Iterator itr = listRep.iterator(); itr.hasNext();) { ReportDTO repDTO = (ReportDTO) itr.next(); exportXLS.add(repDTO); } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 0); cell.setCellValue("DATE :" + date + " BATCH TIME :" + reportDto.getBatchTime()); //Only If the DTO is not empty if (exportXLS.size() != 0) { for (int i = exportXLS.size(), count = 0; count <= i; count++) { row = sheet.createRow(roww); roww += 1; for (short j = 0; j < 8; j++) { cell = row.createCell(j); // for header if (count == 0) { // for header if (j == 0) { cell.setCellValue("S.NO"); } else if (j == 1) { cell.setCellValue("VALUE DATE"); } else if (j == 2) { cell.setCellValue("MSG TYPE"); } else if (j == 3) { cell.setCellValue("UTR NUMBER"); } else if (j == 4) { cell.setCellValue("SENDER ADDRESS"); } else if (j == 5) { cell.setCellValue("RECEIVER ADDRESS"); } else if (j == 6) { cell.setCellValue("OUTWARD UTR NO"); } else if (j == 7) { cell.setCellValue("AMOUNT(Rs)"); } } else { cell = row.createCell(j); // Setting values in cell for each and every row if (j == 0) { cell.setCellValue(count); } else if (j == 1) { String valueDate = null; if (date != null) { valueDate = date; } valueDate = InstaReportUtil.getDateInSpecificFormat(dateFormat, valueDate); cell.setCellValue(valueDate); } else if (j == 2) { String msgType = null; if (((ReportDTO) exportXLS.get(count - 1)).getMsgType() != null) { msgType = ((ReportDTO) exportXLS.get(count - 1)).getMsgType(); } cell.setCellValue(msgType); } else if (j == 3) { String utrNo = null; if (((ReportDTO) exportXLS.get(count - 1)).getUtrNo() != null) { utrNo = ((ReportDTO) exportXLS.get(count - 1)).getUtrNo(); } cell.setCellValue(utrNo); } else if (j == 4) { String sendAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getSenderAddress() != null) { sendAdd = ((ReportDTO) exportXLS.get(count - 1)).getSenderAddress(); } cell.setCellValue(sendAdd); } else if (j == 5) { String recAdd = null; if (((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress() != null) { recAdd = ((ReportDTO) exportXLS.get(count - 1)).getReceiverAddress(); } cell.setCellValue(recAdd); } else if (j == 6) { String outUtr = null; if (((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo() != null) { outUtr = ((ReportDTO) exportXLS.get(count - 1)).getOutUTRNo(); } cell.setCellValue(outUtr); } else if (j == 7) { String amt = null; // if (((ReportDTO)exportXLS // .get(count-1)).getAmount() != 0) { if (new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt()) .compareTo(BigDecimal.ZERO) != 0) { // totAmt += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // subTotal += ((ReportDTO)exportXLS // .get(count-1)).getAmount(); // amt = String.valueOf(((ReportDTO)exportXLS // .get(count-1)).getAmount()); totAmt = totAmt.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); subTotal = subTotal.add( new BigDecimal(((ReportDTO) exportXLS.get(count - 1)).getAmt())); amt = String.valueOf(((ReportDTO) exportXLS.get(count - 1)).getAmt()); } cell.setCellValue(new BigDecimal(amt).setScale(2).toString()); } cell.setCellStyle(caption_style); } } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Sub Total(Date :" + date + ")"); cell = row.createCell((short) 7); //cell.setCellValue(String.valueOf(subTotal)); cell.setCellValue(subTotal.setScale(2).toString()); } } row = sheet.createRow(roww); roww += 1; cell = row.createCell((short) 6); cell.setCellValue("Total Amount"); cell = row.createCell((short) 7); // cell.setCellValue(String.valueOf(totAmt)); cell.setCellValue(totAmt.setScale(2).toString()); sheet = book.createSheet(); book.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); } }