List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont
@Override
public HSSFFont createFont()
From source file:com.hp.action.StaffHistoryAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize//from ww w . ja va 2s . com if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA listStaffHistory = (List<StaffHistory>) session.getAttribute("listStaffHistory"); if (listStaffHistory == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Chm sc khch hng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co chm sc khch hng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < listStaffHistory.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(listStaffHistory.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoChamSocKhachHang" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.StaffsAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize//from w w w .ja v a2 s. com if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA staffsList = (List<Staff>) session.getAttribute("staffsList"); if (staffsList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Nhn vin"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 10 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Danh sch nhn vin"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 10 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); cell1.setCellValue(""); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < staffsList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(staffsList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "DanhSachNhanVien.xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.TimeKeeperAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize//from w w w . j av a2s . com if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList"); if (timeKeeperList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Chm cng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co chm cng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < timeKeeperList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(timeKeeperList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoChamCong" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultErrorMixLog(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;/*from w w w.j a v a 2s . c o m*/ HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.success") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getResultErrorMixLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); HSSFCellStyle styleErr = wb.createCellStyle(); HSSFCellStyle styleSuccess = wb.createCellStyle(); HSSFFont fontSuccess = wb.createFont(); HSSFFont fontErr = wb.createFont(); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("RESULT LOG" + "_" + "ORDERNO"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); if ((dtoObj.getResultLogValue() == null || dtoObj.getResultLogValue().length() == 0) && dtoObj.getErrorLogValue() != null) { fontErr.setColor(HSSFColor.RED.index); styleErr.setFont(fontErr); wc.setCellStyle(styleErr); wc.setCellValue(dtoObj.getErrorLogValue().toString()); } else { fontSuccess.setColor(HSSFColor.BLACK.index); styleSuccess.setFont(fontSuccess); wc.setCellStyle(styleSuccess); wc.setCellValue(dtoObj.getResultLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + " Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.dao.NewOrderDaoExt.java
/** * Method to get all data for Masters Download * @param productID//from w ww. j ava 2s .c o 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; try {//from www.j a v a2s. co m 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; try {// w w w . jav a 2s. c o m 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; try {//from ww w. j a v a2s . c om 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; try {// w w w .ja v a 2 s. co m 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();//from w ww. j a v a 2 s . co m 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; } }