List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook()
From source file:com.ibm.asset.trails.ws.AlertPriorityIsvSwInstancesReviewedServiceEndpoint.java
@GET @Path("/download/{accountId}") public Response download(@PathParam("accountId") Long accountId, @Context HttpServletRequest request, @Context HttpServletResponse response) throws IOException { try {/* w ww . ja va 2s. c o m*/ HSSFWorkbook hwb = new HSSFWorkbook(); Account account = accountService.getAccount(accountId); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=PrioISV" + account.getAccount() + ".xls"); reportService.getAlertUnlicensed(account, request.getRemoteUser(), null, hwb, response.getOutputStream(), "SWISVPR", "SWISVPR", "SOM4b: PRIORITY ISV SW INSTANCES REVIEWED", "PrioISV"); //story 35896 response.flushBuffer(); } catch (Exception e) { e.printStackTrace(); } ResponseBuilder responseBuilder = Response.ok(response.getOutputStream()); return responseBuilder.build(); }
From source file:com.ibm.asset.trails.ws.AlertWithDefinedContractScopeServiceEndpoint.java
@GET @Path("/download/{accountId}") public Response download(@PathParam("accountId") Long accountId, @Context HttpServletRequest request, @Context HttpServletResponse response) throws IOException { try {/* w w w . ja va 2s.c o m*/ HSSFWorkbook hwb = new HSSFWorkbook(); Account account = accountService.getAccount(accountId); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=alertContractScope" + account.getAccount() + ".xls"); reportService.getAlertUnlicensed(account, request.getRemoteUser(), null, hwb, response.getOutputStream(), "SWISCOPE", "SWISCOPE", "SOM3: SW INSTANCES WITH DEFINED CONTRACT SCOPE", "Alert Contract Scope"); //story 35896 response.flushBuffer(); } catch (Exception e) { e.printStackTrace(); } ResponseBuilder responseBuilder = Response.ok(response.getOutputStream()); return responseBuilder.build(); }
From source file:com.ibm.ioes.dao.NewOrderDaoExt.java
/** * Method to get all data for Masters Download * @param productID//from ww w . j av a2 s.co m * @return */ public HSSFWorkbook downloadMasters(long productID) { String methodName = "downloadMasters", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; AppConstants.IOES_LOGGER.info(methodName + " method of " + className + " class have been called"); Connection connection = null; ArrayList<String> allowedSections = new ArrayList<String>(); HSSFWorkbook wb = new HSSFWorkbook(); CallableStatement getAllDropDownAttributes = null; CallableStatement getDetailForEachdropDown = null; ResultSet rsForAllDropDowns = null; ResultSet rsForEachDropDown = null; CallableStatement getAllMasters = null; ResultSet rsAllMasters = null; try { connection = DbConnection.getConnectionObject(); connection.setAutoCommit(false); allowedSections = getAllowedSections(productID); //for Service Summary if (allowedSections.contains(new String("SERVICE_SUMMARY"))) { getAllDropDownAttributes = connection.prepareCall(sp_getAllDropDownAttributes); getAllDropDownAttributes.setLong(1, productID); rsForAllDropDowns = getAllDropDownAttributes.executeQuery(); while (rsForAllDropDowns.next()) { //makeSheetForServiceSummary(wb,rsForAllDropDowns.getInt("ATTMASTERID")); HSSFSheet sheet = wb.createSheet(rsForAllDropDowns.getString("ATTDESCRIPTION")); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = sheet.createRow(0); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString(rsForAllDropDowns.getString("ATTDESCRIPTION"))); excelRow = sheet.createRow(1); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VALUE")); getDetailForEachdropDown = connection.prepareCall(sp_getDataForEachDropDown); getDetailForEachdropDown.setLong(1, rsForAllDropDowns.getInt("ATTMASTERID")); rsForEachDropDown = getDetailForEachdropDown.executeQuery(); int rowNo = 2; while (rsForEachDropDown.next()) { //create a sheet excelRow = sheet.createRow(rowNo); for (int cell = 0, col = 1; cell < 2; cell++, col++) { excelCell = excelRow.createCell(cell); excelCell.setCellValue(new HSSFRichTextString(rsForEachDropDown.getString(col))); } rowNo = rowNo + 1; } } } //for all other Sections getAllMasters = connection.prepareCall(sqlSp_getAllMasters); rsAllMasters = getAllMasters.executeQuery(); while (rsAllMasters.next()) { String sectionName = rsAllMasters.getString("SECTION_NAME"); if (allowedSections.contains(sectionName)) { String columns = rsAllMasters.getString("COLUMN_NAMES"); String[] columnNames = columns.split(","); HSSFSheet sheet = wb.createSheet(rsAllMasters.getString("MASTER_NAME")); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = sheet.createRow(0); for (int count = 0; count < columnNames.length; count++) { excelCell = excelRow.createCell(count); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString(columnNames[count])); } PreparedStatement getMasterData = null; ResultSet rsMasterData = null; getMasterData = connection.prepareStatement(rsAllMasters.getString("QUERY")); rsMasterData = getMasterData.executeQuery(); int rowNo = 1; while (rsMasterData.next()) { excelRow = sheet.createRow(rowNo); for (int cell = 0, col = 1; cell < columnNames.length; cell++, col++) { excelCell = excelRow.createCell(cell); excelCell.setCellValue(new HSSFRichTextString(rsMasterData.getString(col))); } rowNo = rowNo + 1; } } } } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { Utility.onEx_LOG_RET_NEW_EX(e1, methodName, className, msg, logToFile, logToConsole); } } finally { try { DbConnection.closeResultset(rsAllMasters); DbConnection.closeResultset(rsForAllDropDowns); DbConnection.closeCallableStatement(getAllDropDownAttributes); DbConnection.closeCallableStatement(getAllMasters); DbConnection.closeCallableStatement(getDetailForEachdropDown); DbConnection.closeCallableStatement(getAllMasters); DbConnection.freeConnection(connection); } catch (Exception e) { } } return wb; }
From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java
public HSSFWorkbook generateExcel_ACS(ArrayList<ACSMailTemplateDto> mailDataList) { HSSFWorkbook wb = null;/* ww w. j a v a 2s .co m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); int i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Region")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Type of ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Customer Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Address")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("City State")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Pin")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Moderator Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Contact Person")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account Manager Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId()))); /*excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/ excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone()))); excelCell = excelRow.createCell(i_cell++); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone()) + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java
public HSSFWorkbook generateExcel_VCS(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null;//w w w .java 2s.co m try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel(ArrayList<ACSMailTemplateDto> mailDataList) { HSSFWorkbook wb = null;/*from w w w.j a v a2 s. c om*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); int i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Region")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Type of ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Customer Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Address")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("City State")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Pin")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Moderator Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Contact Person")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account Manager Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId()))); /*excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/ excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone()))); excelCell = excelRow.createCell(i_cell++); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone()) + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel_Multiple(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null;/*from ww w . j a v a 2 s. c o m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("OVCC ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled OVCC plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } else { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } else { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadas() throws IOException { cargaResumen();// w w w . j a v a 2 s .c om if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }