List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle
@Override
public HSSFCellStyle createCellStyle()
From source file:com.hp.action.ReportTakeOrderAction.java
public String exportTakeOrderList() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize/*ww w . j a v a2s. co m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA takeOrdersList = (List<TakeOrder>) session.getAttribute("takeOrdersList"); takeOrderDetailList = (List<List<TakeOrderDetail>>) session.getAttribute("takeOrderDetailList"); if (takeOrdersList == 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("Take Order"); //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) 8 //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 ha n t 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) 8 //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 < takeOrdersList.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(takeOrdersList.get(i), takeOrderDetailList.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); } //SUM ROW if (i == (takeOrdersList.size() - 1)) { Row rowEnd = sheet.createRow(i + 7); for (int j = (titleArray().length - 5); j < (titleArray().length - 1); j++) { Cell cell = rowEnd.createCell(j); cell.setCellValue((Double) sumArray()[j - (titleArray().length - 5)]); } } } outputFile = "BaoCaoHoaDonDatHang" + 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.SetLunchAction.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 ww . java2 s.c o m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA setLunchList = (List<SetLunch>) session.getAttribute("setLunchList"); if (setLunchList == 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("Bo cm"); //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 cm vn phng"); //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 < setLunchList.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(setLunchList.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 = "BaoComVanPhong" + 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.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.j ava2 s . co m*/ 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//ww w .ja v a 2s . c o m 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//w w w . j a v a 2s.co m 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.asset.trails.service.impl.CauseCodeServiceImpl.java
@Transactional(readOnly = true, propagation = Propagation.REQUIRES_NEW) public ByteArrayOutputStream loadSpreadsheet(File file, String remoteUser, List<State> steps) throws IOException { ByteArrayOutputStream bos = null; FileInputStream fin = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(fin); HSSFSheet sheet = wb.getSheetAt(0);//w ww . ja va 2 s .c om HSSFCell reportNameCell = sheet.getRow(ROW_ALERT_TYPE).getCell(COL_ALERT_TYPE); String reportName = reportNameCell.getStringCellValue().trim(); HSSFCellStyle errorStyle = wb.createCellStyle(); errorStyle.setFillForegroundColor(HSSFColor.RED.index); errorStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); colIndexes = ECauseCodeReport.getReportByName(reportName); boolean error = validateExcelCauseCodeContent(sheet, errorStyle, steps); if (!error) { saveCauseCode(wb, remoteUser, steps); } else { State state = State.findStateByLable(steps, STEP3_LABEL); if (state == null) { state = new State(); state.setDescription("Persist changes"); state.setLabel(STEP3_LABEL); state.setStatus(EStatus.IGNORED); steps.add(state); } } bos = new ByteArrayOutputStream(); wb.write(bos); return bos; }
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 a2 s.c o 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; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getTemplate(String filePath, int templateId) throws IOESException, ParseException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); String fileName;/*ww w .j a v a 2 s.co m*/ HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList filledTemplateData = new ArrayList(); 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; 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)) { int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0; //filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag); 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++; } } wb.write(fileOut); fileOut.close(); filePath = newFile; } } 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 getTemplate method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
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 2 s . co 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 w w . j a v a2 s. 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; }