List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertHardwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "HW_LPAR").getSingleResult(); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, HL.Name AS HL_Name, H.Serial, MT.Name AS MT_Name, VA.Creation_Time, VA.Alert_Age, MT.Type, HL.OS_Type, VA.Remote_User, VA.Comments, VA.Record_Time, AC.name as ac_name, CC.target_date, CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware_Lpar HL, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE_LPAR' AND VA.Open = 1 AND HL.Id = VA.FK_Id AND H.Id = HL.Hardware_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY HL.Name ASC") .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue()) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert HwLPAR Report"); printHeader(ALERT_HARDWARE_LPAR_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_LPAR_REPORT_COLUMN_HEADERS, sheet); int i = 3;// ww w . j a v a2s . c o m while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert HWLpar Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertHardwareReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "HARDWARE").getSingleResult(); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, H.Serial, MT.Name, VA.Creation_Time, VA.Alert_Age, MT.Type, VA.Remote_User, VA.Comments, VA.Record_Time, AC.name as ac_name, CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE' AND VA.Open = 1 AND H.Id = VA.FK_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY H.Serial ASC") .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue()) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert Hardware Report"); printHeader(ALERT_HARDWARE_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_REPORT_COLUMN_HEADERS, sheet); int i = 3;/*from w ww .j a v a 2 s.c om*/ while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert HW Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertSoftwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "SW_LPAR").getSingleResult(); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(SQL_QUERY_SW_LPAR) .setLong("customerId", pAccount.getId()).setString("type", "SOFTWARE_LPAR") .setInteger("alertTypeId", alertType.getId().intValue()).scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert SwLpar Report"); printHeader(ALERT_SOFTWARE_LPAR_REPORT_NAME, pAccount.getAccount(), ALERT_SW_LPAR_REPORT_COLUMN_HEADERS, sheet);//from w ww .j av a2s . c om int i = 3; while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert SWLpar Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertUnlicensedIbmSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()) .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId()) .setString("type", "UNLICENSED_IBM_SW").scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_IBM_SW Report"); printHeader(ALERT_UNLICENSED_IBM_SW_REPORT_NAME, pAccount.getAccount(), ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet); int i = 3;/*from w w w.j av a 2s .c om*/ while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert UIBM SW Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } // lsrReport.close(); @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17)) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertUnlicensedIsvSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()) .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId()) .setString("type", "UNLICENSED_ISV_SW").scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_ISV_SW Report"); printHeader(ALERT_UNLICENSED_ISV_SW_REPORT_NAME, pAccount.getAccount(), ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet); int i = 3;// w w w . j a v a2 s .com while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert UISV SW Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } // lsrReport.close(); @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17)) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Override @Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertHardwareCfgDataReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "HWCFGDTA").getSingleResult(); StringBuffer sb = new StringBuffer( "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, "); sb.append(/*from w ww . j a v a 2 s. co m*/ "H.Serial, MT.Name, H.processor_manufacturer, H.MAST_PROCESSOR_TYPE, H.PROCESSOR_MODEL, H.NBR_CORES_PER_CHIP, H.CHIPS, ") .append("H.PROCESSOR_COUNT, H.NBR_OF_CHIPS_MAX, H.CPU_GARTNER_MIPS, H.CPU_MIPS, H.CPU_MSU, ") .append("VA.Creation_Time, VA.Alert_Age, MT.Type, VA.Remote_User, VA.Comments, VA.Record_Time,AC.name as ac_name, ") .append("CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id ") .append("FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC ") .append("WHERE VA.Customer_Id = :customerId AND VA.Type = 'HWCFGDTA' AND VA.Open = 1 AND H.Id = VA.FK_Id AND MT.Id = H.Machine_Type_Id ") .append("and VA.id=CC.alert_id and CC.alert_type_id= :alertTypeId and CC.alert_cause_id=AC.id ORDER BY H.Serial ASC "); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(sb.toString()) .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue()) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert Hardware Config " + pAccount.getAccount() + " Report"); printHeader(ALERT_HARDWARE_CFGDATA_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_CFGDATA_REPORT_COLUMN_HEADERS, sheet); int i = 3; while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert Hardware Config " + pAccount.getAccount() + " Report" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Override @Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertUnlicensed(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream, String type, String code, String reportName, String sheetName) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", code).getSingleResult(); StringBuffer sb = new StringBuffer(unlicensedAlertQuery); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(sb.toString()) .setLong("customerId", pAccount.getId()).setParameter("type", type).setParameter("code", code) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet(sheetName + " " + pAccount.getAccount() + " Report"); printHeader(reportName, pAccount.getAccount(), UNLICENSED_GROUP_COLUMN_HEADERS, sheet); int i = 3;/*from www . j a v a 2 s .com*/ while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert Contract Scope " + pAccount.getAccount() + " Report" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
@SuppressWarnings("deprecation") public String getErrorExcel(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;/*from ww w . j a va 2s. c om*/ HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.errors") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getErrorLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); 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("ERROR LOG"); 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); System.out.println(dtoObj.getErrorLogValue()); if (dtoObj.getErrorLogValue() == null) { wc.setCellValue("No Errors"); } else { wc.setCellValue(dtoObj.getErrorLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel 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); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultExcel(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 va 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.getResultLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); 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); System.out.println(dtoObj.getResultLogValue()); if (dtoObj.getResultLogValue() == null) { wc.setCellValue("SUCCESS"); } else { if (dtoObj.getOrderNo() != null) wc.setCellValue(dtoObj.getResultLogValue().toString() + "_(" + dtoObj.getOrderNo().toString() + ")"); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: 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); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getFilledTemplate(String filePath, int templateId, int flag, String logicalLSI) throws IOESException, ParseException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); String fileName;//from www . ja v a 2 s . co m HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList filledTemplateData = new ArrayList(); ArrayList<ViewOrderDto> filledTemplateDataLineDetails = new ArrayList<ViewOrderDto>(); ArrayList<ViewOrderDto> filledTemplateDataChargeDetails = new ArrayList<ViewOrderDto>(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ViewOrderModel objviewmodel = new ViewOrderModel(); //ErrorLogDto dtoObj ; TransactionTemplateDto dtoObj; ViewOrderDto dtoObj1; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); int totalRowsOfSheet = 0; ViewOrderDto objdto = null; String str = null; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); HSSFCellStyle whiteFG_yellow = wb.createCellStyle(); HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00); //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index); whiteFG_yellow.setFillBackgroundColor(yellow.getIndex()); HSSFCellStyle whiteFG_green = wb.createCellStyle(); HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00); //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index); whiteFG_green.setFillBackgroundColor(green.getIndex()); //System.out.println(filledTemplateData.toString()); //System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); if (s == 0 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0; totalRowsOfSheetMain = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(dtoObj.getOrderNo()); wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID()))); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId()))); } ctr++; } totalRowsOfSheetAtt = objDao.getTotalRowsOfSheet(11, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(11, templateId, flag, logicalLSI); ctr = 0; for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { wr = ws.getRow(r); dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); if (wr != null) { wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getRFSDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getActMngrPhoneNo()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getActMngrEmailID()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getIRUOrderYN()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getFreePeriodYN()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getOrdExclusiveTax()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getCAFDate()))); } ctr++; } } //GAM Sheet if (s == 1 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { //to do nothing } //Contact Sheet if (s == 2 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getContactType()); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getSalutation()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getFirstName()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLastName()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getEmail()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getCellno()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getFaxno()))); wr.createCell(10).setCellValue(rownum); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getAddress1()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAddress2()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getAddress3()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getCountrycode()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getStateid()))); wr.createCell(16).setCellValue(new HSSFRichTextString((dtoObj.getCityid()))); wr.createCell(17).setCellValue(new HSSFRichTextString((dtoObj.getPincode()))); } ctr++; rownum++; } } if (s == 3 && (templateId == 1 || templateId == 22 || templateId == 21)) { int ctr = 0, rownum = 1; if (templateId == 21) { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getServiceName()))); wr.createCell(6).setCellValue(dtoObj.getLineItemID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLineItemName()))); } ctr++; rownum++; } } else { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); } ctr++; rownum++; } } } if (s == 3 && templateId == 41) { /*int ctr=0,rownum=1; totalRowsOfSheet=objDao.getTotalRowsOfSheet(s+1,templateId,flag,logicalLSI); filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag,logicalLSI); for(int r = 2; r <= (totalRowsOfSheet+1); r++) { dtoObj = (TransactionTemplateDto)filledTemplateData.get(ctr); wr=ws.createRow(r); if(wr!=null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getCustPONumber()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCustPODate()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getIsDefaultPO()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntity()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getTotalPOAmount()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getPeriodInMonths()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getContractStartDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getContractEndDate()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getPoRemarks()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getPoEmailId()))); } ctr++;rownum++; }*/ } if (s == 4 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); wr.createCell(6).setCellValue(dtoObj.getChargeID()); wr.createCell(7).setCellValue(dtoObj.getChargeAmount()); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getChargeFrequency()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeType()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeName()))); wr.createCell(11).setCellValue(dtoObj.getFrequncyAmount()); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAnnotation()))); } ctr++; rownum++; } } if (s == 5 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(new HSSFRichTextString("")); wr.createCell(3).setCellValue(dtoObj.getLineItemID()); wr.createCell(4).setCellValue(dtoObj.getCreditPeriodID()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getCreditPeriodName()))); wr.createCell(6).setCellValue(dtoObj.getLegealEntityID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntityName()))); wr.createCell(8).setCellValue(dtoObj.getLicenseCompanyID()); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getLicenseCompanyName()))); wr.createCell(10).setCellValue(dtoObj.getBillingModeID()); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getBillingModeName()))); wr.createCell(12).setCellValue(dtoObj.getBillingFormatID()); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getBillingFormatName()))); wr.createCell(14).setCellValue(dtoObj.getBillingTypeID()); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getBillingTypeName()))); wr.createCell(16).setCellValue(dtoObj.getTaxationID()); wr.createCell(17).setCellValue(new HSSFRichTextString(dtoObj.getTaxationName())); wr.createCell(18).setCellValue(dtoObj.getBillingLevelID()); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj.getBillingLevelName())); wr.createCell(20).setCellValue(dtoObj.getNoticePeriod()); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj.getPenaltyClause())); wr.createCell(22).setCellValue(dtoObj.getCommitPeriod()); wr.createCell(23).setCellValue(dtoObj.getIsNfa()); wr.createCell(24).setCellValue(dtoObj.getBcpID()); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj.getBcpName())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonId())); wr.createCell(27).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonName())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 start if (s == 0 && templateId == 61) { int ctr = 0, rownum = 1; filledTemplateDataLineDetails = objviewmodel.getFilledTemplateforBillingLineSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataLineDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataLineDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { HSSFCell cell0 = wr.createCell(0); cell0.setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineName())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getLogicalSino())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getCustLogicalSino())); wr.createCell(5).setCellValue(dtoObj1.getOrderno()); wr.createCell(6).setCellValue(new HSSFRichTextString(dtoObj1.getOrdertype())); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getOrder_subtype())); wr.createCell(8).setCellValue(new HSSFRichTextString(dtoObj1.getSiid())); wr.createCell(9).setCellValue(new HSSFRichTextString(dtoObj1.getAccountid())); wr.createCell(10).setCellValue(new HSSFRichTextString(dtoObj1.getFx_status())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getLine_status())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_No())); wr.createCell(13).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_date())); validateDto = dtoObj1.getBillingTriggerAllowDenyLogic(); HSSFCell cell14 = wr.createCell(14); cell14.setCellValue(new HSSFRichTextString(dtoObj1.getLocNo())); HSSFCellStyle sty = ws.getRow(1).getCell(14).getCellStyle(); if ("allow".equals(validateDto.getLocNoForEdit())) { cell14.setCellStyle(sty); } else { cell14.setCellStyle(sty1); } HSSFCell cell15 = wr.createCell(15); if (!(dtoObj1.getLocDate() == null || "".equals(dtoObj1.getLocDate()))) { cell15.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocDate())))); } else { cell15.setCellValue(new HSSFRichTextString(dtoObj1.getLocDate())); } if ("allow".equals(validateDto.getLocDateForEdit())) { cell15.setCellStyle(sty); } else { cell15.setCellStyle(sty1); } HSSFCell cell16 = wr.createCell(16); if (!(dtoObj1.getLocRecDate() == null || "".equals(dtoObj1.getLocRecDate()))) { cell16.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocRecDate())))); } else { cell16.setCellValue(new HSSFRichTextString(dtoObj1.getLocRecDate())); } if ("allow".equals(validateDto.getLocRecDateForEdit())) { cell16.setCellStyle(sty); } else { cell16.setCellStyle(sty1); } HSSFCell cell17 = wr.createCell(17); if (!(dtoObj1.getBillingTriggerDate() == null || "".equals(dtoObj1.getBillingTriggerDate()))) { cell17.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getBillingTriggerDate())))); } else { cell17.setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerDate())); } if ("allow".equals(validateDto.getBtdForEdit())) { cell17.setCellStyle(sty); } else { cell17.setCellStyle(sty1); } wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerProcess())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 end //billing trigger bulkupload sheet 2 start if (s == 1 && templateId == 61) { int ctr = 0, rownum = 1; if (filledTemplateDataLineDetails.size() > 0) { filledTemplateDataChargeDetails = objviewmodel .getFilledTemplateforBillingChargeSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataChargeDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataChargeDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(0).setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(dtoObj1.getChargeInfoId()); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getChargeType())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getChargeName())); wr.createCell(5).setCellValue(dtoObj1.getChargePeriod()); wr.createCell(6).setCellValue(dtoObj1.getChargeAmt()); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getChargeStatus())); HSSFCell cell8 = wr.createCell(8); HSSFCellStyle sty8 = ws.getRow(1).getCell(8).getCellStyle(); if (!(dtoObj1.getDisconnectiondate() == null || "".equals(dtoObj1.getDisconnectiondate()))) { cell8.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getDisconnectiondate())))); } else { cell8.setCellValue(new HSSFRichTextString(dtoObj1.getDisconnectiondate())); } if ("Changed".equalsIgnoreCase(dtoObj1.getChargeStatus())) { cell8.setCellStyle(sty8); } wr.createCell(9) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeCreatedOnOrder())); wr.createCell(10) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndedOnOrder())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getBillPeriod())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getStartdatelogic())); wr.createCell(13).setCellValue(dtoObj1.getStart_date_days()); wr.createCell(14).setCellValue(dtoObj1.getStart_date_month()); wr.createCell(15).setCellValue(new HSSFRichTextString(dtoObj1.getEnddatelogic())); wr.createCell(16).setCellValue(dtoObj1.getEnd_date_days()); wr.createCell(17).setCellValue(dtoObj1.getEnd_date_month()); wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndDate_String())); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj1.getAnnualRate())); wr.createCell(20).setCellValue(new HSSFRichTextString(dtoObj1.getAnnotation())); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj1.getStartTokenNo())); wr.createCell(22).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxStatus())); wr.createCell(23).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxNo())); wr.createCell(24).setCellValue(new HSSFRichTextString(dtoObj1.getEndTokenNo())); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxStatus())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxNo())); wr.createCell(27) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeStartStatus())); wr.createCell(28) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndStatus())); wr.createCell(29) .setCellValue(new HSSFRichTextString(dtoObj1.getChargefrequency())); wr.createCell(30).setCellValue(new HSSFRichTextString(dtoObj1.getFxViewId())); } ctr++; rownum++; } } } // billing trigger bulkupload sheet 2 end } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate 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); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }