List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet(String sheetname)
From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultAutoRecordServiceImpl.java
License:Open Source License
@Override public String importDataToExcel(HttpServletRequest request, HttpServletResponse response) { String realPath = request.getSession().getServletContext().getRealPath("/"); Map<String, Object> params = new HashMap<String, Object>(); Subject subject = SecurityUtils.getSubject(); User user = (User) subject.getPrincipal(); String userId = user.getId().toString(); if (!"1".equals(userId) && !"50".equals(userId) && !"38".equals(userId) && !"53".equals(userId) && !"15".equals(userId) && !"16".equals(userId) && !"17".equals(userId)) { // FY FY_NJ 33 params.put("subStation", user.getId()); } else {/*from w ww.j a v a 2 s .c om*/ // ggz bj WHY zht params.put("subStation", request.getParameter("subStation")); } params.put("name", request.getParameter("name")); params.put("sortOrder", null); params.put("offset", null); params.put("rows", null); List<AutoRecordData> autoRecordDataList = autoRecordDataMapper.page(params); String _fileName = null; FileOutputStream out = null; Map<String, Object> subStationMap = new HashMap<String, Object>(); subStationMap.put("18", "?"); subStationMap.put("19", ""); subStationMap.put("20", ""); subStationMap.put("21", ""); subStationMap.put("22", "?"); subStationMap.put("23", ""); subStationMap.put("24", "?"); subStationMap.put("25", ""); subStationMap.put("26", ""); subStationMap.put("27", "?"); subStationMap.put("28", ""); subStationMap.put("29", ""); subStationMap.put("30", ""); subStationMap.put("31", "?"); subStationMap.put("32", ""); subStationMap.put("33", "?"); subStationMap.put("51", ""); try { // HSSFWorkbook wb = new HSSFWorkbook(); // HSSFSheet sheet = wb.createSheet(""); HSSFRow firstRow = sheet.createRow(0); firstRow.createCell(0).setCellValue(""); firstRow.createCell(1).setCellValue("???"); firstRow.createCell(2).setCellValue(""); firstRow.createCell(3).setCellValue("?"); firstRow.createCell(4).setCellValue("?(???)"); firstRow.createCell(5).setCellValue("?"); firstRow.createCell(6).setCellValue(""); firstRow.createCell(7).setCellValue(""); firstRow.createCell(8).setCellValue("??"); firstRow.createCell(9).setCellValue("???"); firstRow.createCell(10).setCellValue("???"); firstRow.createCell(11).setCellValue("??"); firstRow.createCell(12).setCellValue("??"); firstRow.createCell(13).setCellValue(""); firstRow.createCell(14).setCellValue("??"); firstRow.createCell(15).setCellValue(""); firstRow.createCell(16).setCellValue("?"); firstRow.createCell(17).setCellValue("???"); firstRow.createCell(18).setCellValue("???"); firstRow.createCell(19).setCellValue("???"); firstRow.createCell(20).setCellValue("?"); firstRow.createCell(21).setCellValue("??"); firstRow.createCell(22).setCellValue("????"); firstRow.createCell(23).setCellValue("????"); firstRow.createCell(24).setCellValue("???"); firstRow.createCell(25).setCellValue("??"); firstRow.createCell(26).setCellValue("??"); firstRow.createCell(27).setCellValue("???"); firstRow.createCell(28).setCellValue("??"); firstRow.createCell(29).setCellValue("??"); firstRow.createCell(30).setCellValue("??"); firstRow.createCell(31).setCellValue("??"); firstRow.createCell(32).setCellValue("??"); firstRow.createCell(33).setCellValue("????"); firstRow.createCell(34).setCellValue("?"); if (autoRecordDataList.size() > 0) { for (int j = 1; j < autoRecordDataList.size() + 1; ++j) { HSSFRow row = sheet.createRow(j); // subStation HSSFCell subStation = row.createCell(0); subStation.setCellValue( subStationMap.get(autoRecordDataList.get(j - 1).getSubStation()).toString()); // deputyCard ??? HSSFCell deputyCard = row.createCell(1); deputyCard.setCellValue(autoRecordDataList.get(j - 1).getDeputyCard()); // carKind HSSFCell carKind = row.createCell(2); carKind.setCellValue(autoRecordDataList.get(j - 1).getCarKind()); // licenseNo ? HSSFCell licenseNo = row.createCell(3); licenseNo.setCellValue(autoRecordDataList.get(j - 1).getLicenseNo()); // license ?(???) HSSFCell license = row.createCell(4); license.setCellValue(autoRecordDataList.get(j - 1).getLicense()); // licenseDate ? HSSFCell licenseDate = row.createCell(5); licenseDate.setCellValue(null == autoRecordDataList.get(j - 1).getLicenseDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getLicenseDate(), DateFormatUtils.ymd)); // licenseName HSSFCell licenseName = row.createCell(6); licenseName.setCellValue(autoRecordDataList.get(j - 1).getLicenseName()); // inspectionDate HSSFCell inspectionDate = row.createCell(7); inspectionDate.setCellValue(null == autoRecordDataList.get(j - 1).getInspectionDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getInspectionDate(), DateFormatUtils.ymd)); // name ?? HSSFCell name = row.createCell(8); name.setCellValue(autoRecordDataList.get(j - 1).getName()); // idCard ??? HSSFCell idCard = row.createCell(9); idCard.setCellValue(autoRecordDataList.get(j - 1).getIdCard()); // ftReceive ??? HSSFCell ftReceive = row.createCell(10); ftReceive.setCellValue(null == autoRecordDataList.get(j - 1).getFtReceive() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getFtReceive(), DateFormatUtils.ymd)); // changeDate ?? HSSFCell changeDate = row.createCell(11); changeDate.setCellValue(null == autoRecordDataList.get(j - 1).getChangeDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getChangeDate(), DateFormatUtils.ymd)); // telephone ?? HSSFCell telephone = row.createCell(12); telephone.setCellValue(autoRecordDataList.get(j - 1).getTelephone()); // strongInsDate HSSFCell strongInsDate = row.createCell(13); strongInsDate.setCellValue(null == autoRecordDataList.get(j - 1).getStrongInsDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getStrongInsDate(), DateFormatUtils.ymd)); // tLInsurance ?? (?) HSSFCell tLInsurance = row.createCell(14); Double _tLInsurance = autoRecordDataList.get(j - 1).gettLInsurance(); if (null == _tLInsurance) { tLInsurance.setCellValue(""); } else { tLInsurance.setCellValue(_tLInsurance); } // tLInsuranceDate HSSFCell tLInsuranceDate = row.createCell(15); tLInsuranceDate.setCellValue(null == autoRecordDataList.get(j - 1).gettLInsuranceDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).gettLInsuranceDate(), DateFormatUtils.ymd)); // policeProve ? HSSFCell policeProve = row.createCell(16); policeProve.setCellValue(autoRecordDataList.get(j - 1).getPoliceProve()); // householdCopy ??? HSSFCell householdCopy = row.createCell(17); householdCopy.setCellValue(autoRecordDataList.get(j - 1).getHouseholdCopy()); // idCardCopy ??? HSSFCell idCardCopy = row.createCell(18); idCardCopy.setCellValue(autoRecordDataList.get(j - 1).getIdCardCopy()); // licenseCopy ??? HSSFCell licenseCopy = row.createCell(19); licenseCopy.setCellValue(autoRecordDataList.get(j - 1).getLicenseCopy()); // guaranRespon ? HSSFCell guaranRespon = row.createCell(20); guaranRespon.setCellValue(autoRecordDataList.get(j - 1).getGuaranRespon()); // guaranIncome ?? HSSFCell guaranIncome = row.createCell(21); guaranIncome.setCellValue(autoRecordDataList.get(j - 1).getGuaranIncome()); // guaranHouseCopy ???? HSSFCell guaranHouseCopy = row.createCell(22); guaranHouseCopy.setCellValue(autoRecordDataList.get(j - 1).getGuaranHouseCopy()); // guaranIDCopy ???? HSSFCell guaranIDCopy = row.createCell(23); guaranIDCopy.setCellValue(autoRecordDataList.get(j - 1).getGuaranIDCopy()); // driLicenseCopy ??? HSSFCell driLicenseCopy = row.createCell(24); driLicenseCopy.setCellValue(autoRecordDataList.get(j - 1).getDriLicenseCopy()); // strongInsCopy ?? HSSFCell strongInsCopy = row.createCell(25); strongInsCopy.setCellValue(autoRecordDataList.get(j - 1).getStrongInsCopy()); // commerInsuCopy ?? HSSFCell commerInsuCopy = row.createCell(26); commerInsuCopy.setCellValue(autoRecordDataList.get(j - 1).getCommerInsuCopy()); // certificate ??? HSSFCell certificate = row.createCell(27); certificate.setCellValue(autoRecordDataList.get(j - 1).getCertificate()); // agreeDate ?? HSSFCell agreeDate = row.createCell(28); agreeDate.setCellValue(null == autoRecordDataList.get(j - 1).getAgreeDate() ? "" : DateFormatUtils.format(autoRecordDataList.get(j - 1).getAgreeDate(), DateFormatUtils.ymd)); // rentalAgreement ?? HSSFCell rentalAgreement = row.createCell(29); rentalAgreement.setCellValue(autoRecordDataList.get(j - 1).getRentalAgreement()); // strongInsPrompt ?? HSSFCell strongInsPrompt = row.createCell(30); strongInsPrompt.setCellValue(autoRecordDataList.get(j - 1).getStrongInsPrompt()); // tLInsurancePrompt ?? HSSFCell tLInsurancePrompt = row.createCell(31); tLInsurancePrompt.setCellValue(autoRecordDataList.get(j - 1).gettLInsurancePrompt()); // inspectionPrompt ?? HSSFCell inspectionPrompt = row.createCell(32); inspectionPrompt.setCellValue(autoRecordDataList.get(j - 1).getInspectionPrompt()); // changePrompt ???? HSSFCell changePrompt = row.createCell(33); changePrompt.setCellValue(autoRecordDataList.get(j - 1).getChangePrompt()); // status ? HSSFCell status = row.createCell(34); status.setCellValue(autoRecordDataList.get(j - 1).getStatus()); } } _fileName = "excel" + DateFormatUtils.format(new Date(), "yyyy-MM-dd-HH_mm_ss-SSS") + ".xls"; String fileName = realPath + File.separator + _fileName; out = new FileOutputStream(fileName); wb.write(out); } catch (Exception e) { e.printStackTrace(); } finally { // ? try { if (null != out) { out.close(); } } catch (IOException e) { e.printStackTrace(); } } return _fileName; }
From source file:com.jubination.service.CallMaintainService.java
public boolean createCallExcel(List<Call> list) { System.out.println("*******com.jubination.service.CallMaintainService.createCallExcel()"); FileOutputStream out = null;//from w w w. ja v a 2s . c o m HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "data.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "data.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); Call[] messageArray = new Call[list.size()]; list.toArray(messageArray); Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "CallFrom", "CallTo", "Status", "TrackStatus", "CallType", "DailWhomNumber", "DailCallDuration", "Message", "DateCreated", "AnsweredBy", "StartTime", "EndTime", "DateUpdated", "Duration", "Price", "Direction", "Digits", "Sid", "Uri", "RecordingUrl", "PhoneNumberSid", "AccountSid", "ForwardedFrom", "CallerName", "ParentCallSid" }); index++; for (Call message : messageArray) { data.put(index.toString(), new Object[] { message.getCallFrom(), message.getCallTo(), message.getStatus(), message.getTrackStatus(), message.getCallType(), message.getDialWhomNumber(), message.getDialCallDuration(), message.getMessage(), message.getDateCreated(), message.getAnsweredBy(), message.getStartTime(), message.getEndTime(), message.getDateUpdated(), message.getDuration(), message.getPrice(), message.getDirection(), message.getDigits(), message.getSid(), message.getUri(), message.getRecordingUrl(), message.getPhoneNumberSid(), message.getAccountSid(), message.getForwardedFrom(), message.getCallerName(), message.getParentCallSid() }); index++; } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { } try { if (out != null) { out.close(); } } catch (Exception e) { } } return flag; }
From source file:com.jubination.service.CallMaintainService.java
public boolean createClientExcel(String date) { System.out.println("*******com.jubination.service.CallMaintainService.createClientExcel()"); FileOutputStream out = null;/*from w ww. j ava 2 s . c o m*/ HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "client.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "client.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Client Sheet"); Map<String, Object[]> data = doReportingOperation(getClientDumpForDisplay(date)); Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } return flag; }
From source file:com.jubination.service.CallMaintainService.java
public boolean createClientExcelAllLead(String date) { System.out.println("*******com.jubination.service.CallMaintainService.createClientExcelAllLead()"); FileOutputStream out = null;/*from ww w . ja v a 2s . c om*/ HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "client.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "client.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Client Sheet"); List<Lead> list = getLeadDumpForDisplay(date); Lead[] messageArray = new Lead[list.size()]; list.toArray(messageArray); Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "Lead id", "Name", "Number", "Email", "Campaign Name", "Pub Id", "Source", "Date", "City", "Affiliate Status", "Picked up by", "Follow ups left", "Client comment", "Lead comment", "Follow up date", "Status", "Date", "Status-1", "Date-1", " Status-2", "Date-2", " Status-3", "Date-3", " Status-4", "Date-4", " Status-5", "Date-5", " Status-6", "Date-6", " Status-7", "Date-7", " Status-8", "Date-8", " Status-9", "Date-9", " Status-10", "Date-10", " Status-11", "Date-11", " Status-12", "Date-12", " Status-13", "Date-13", " Status-14", "Date-14", " Status-15", "Date-15", "", "Final Status Beta" }); index++; for (Lead lead : messageArray) { String[] leadDetailsArray = new String[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; String[] dateDetailsArray = new String[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; String affiliateDetails = "WIP"; for (int i = 0; i < 20; i++) { dateDetailsArray[i] = ""; leadDetailsArray[i] = ""; } int count = 0; String caller = ""; if (lead != null) { if (lead.getCall().size() > 0) { for (int i = lead.getCall().size() - 1; i >= 0; i--) { if (count < 15) { Call call = lead.getCall().get(i); if (call == null) { break; } System.out.println(count + " " + i + " " + call.getDateCreated()); ////////change to allow all lead sent to thyrocare leads/////////// //////////////////////// if (call.getStatus() != null && call.getStatus().contains("busy")) { leadDetailsArray[count] = "Busy"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getStatus() != null && call.getStatus().contains("failed")) { leadDetailsArray[count] = "Failed"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Not Reachable"; } } else if (call.getStatus() != null && call.getStatus().contains("no-answer")) { leadDetailsArray[count] = "No Answer"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Ringing"; } } else if (call.getStatus() != null && call.getStatus().contains("completed") && call.getCallType().contains("trans")) { leadDetailsArray[count] = "Hanged up while greetings"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("did not speak") && call.getCallType().contains("client-hangup")) { leadDetailsArray[count] = "Hanged up while connecting"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("did not speak") && call.getCallType().contains("incomplete")) { leadDetailsArray[count] = "We missed client's call"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "WIP"; } caller = call.getDialWhomNumber(); } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("spoke")) { if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Follow up/Call back") || lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Not interested") || lead.getLeadStatus().contains("Not registered") || lead.getLeadStatus().contains("Language not recognizable") || lead.getLeadStatus().contains("No Service") || lead.getLeadStatus().contains("Customer complained") || lead.getLeadStatus().contains("Disapproved") || lead.getLeadStatus().contains("Rescheduled"))) { leadDetailsArray[count] = lead.getLeadStatus(); if (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled")) { affiliateDetails = "Interested"; } else { affiliateDetails = lead.getLeadStatus(); } dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } else { if (i == lead.getCall().size() - 1) { leadDetailsArray[count] = "Spoke but not updated"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); affiliateDetails = "Spoke but not updated"; } else { leadDetailsArray[count] = lead.getLeadStatus() + ":"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } } caller = call.getDialWhomNumber(); } else { if (i == lead.getCall().size() - 1) { if (lead.getLeadStatus() != null) { caller = call.getDialWhomNumber(); if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Follow up/Call back") || lead.getLeadStatus().contains("Not interested") || lead.getLeadStatus().contains("Not registered") || lead.getLeadStatus() .contains("Language not recognizable") || lead.getLeadStatus().contains("No Service") || lead.getLeadStatus().contains("Customer complained") || lead.getLeadStatus().contains("Disapproved"))) { affiliateDetails = lead.getLeadStatus(); } else if (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled")) { affiliateDetails = "Interested"; } else if (lead.getLeadStatus().contains("Busy")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus().contains("Failed")) { affiliateDetails = "Not Reachable"; } else if (lead.getLeadStatus().contains("No Answer")) { affiliateDetails = "Ringing"; } else if (lead.getLeadStatus().contains("Hanged up while greetings")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus() .contains("Hanged up while connecting")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus().contains("Spoke but not updated")) { affiliateDetails = "Spoke but not updated"; } else { affiliateDetails = lead.getLeadStatus(); } leadDetailsArray[count] = lead.getLeadStatus(); dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } else { leadDetailsArray[count] = call.getStatus() + "%"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); caller = call.getDialWhomNumber(); affiliateDetails = lead.getLeadStatus(); } } else { leadDetailsArray[count] = lead.getLeadStatus() + "$"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } } if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled"))) { affiliateDetails = "Interested"; } if (lead.getFollowUpDate() != null && !affiliateDetails.contains("") && !lead.getLeadStatus().contains("Follow up/Call back") && !lead.getLeadStatus().contains("Not interested") && !lead.getLeadStatus().contains("Not registered") && !lead.getLeadStatus().contains("Language not recognizable") && !lead.getLeadStatus().contains("No Service") && !lead.getLeadStatus().contains("Customer complained") && !lead.getLeadStatus().contains("Disapproved")) { affiliateDetails = "Follow up/Call back"; } if (lead.isMissedAppointment() != null && lead.isMissedAppointment() && lead.getCount() < 1) { affiliateDetails = "Missed Appointment"; } count++; } } } data.put(index.toString(), new Object[] { lead.getLeadId(), lead.getClient().getName(), lead.getClient().getPhoneNumber(), lead.getClient().getEmailId(), lead.getClient().getCampaignName(), lead.getClient().getPubId(), lead.getClient().getSource(), lead.getClient().getDateCreation(), lead.getClient().getCity(), affiliateDetails, caller, Integer.toString(lead.getCount()), lead.getClient().getInitialComments(), lead.getComments(), lead.getFollowUpDate(), leadDetailsArray[0], dateDetailsArray[0], leadDetailsArray[1], dateDetailsArray[1], leadDetailsArray[2], dateDetailsArray[2], leadDetailsArray[3], dateDetailsArray[3], leadDetailsArray[4], dateDetailsArray[4], leadDetailsArray[5], dateDetailsArray[5], leadDetailsArray[6], dateDetailsArray[6], leadDetailsArray[7], dateDetailsArray[7], leadDetailsArray[8], dateDetailsArray[8], leadDetailsArray[9], dateDetailsArray[9], leadDetailsArray[10], dateDetailsArray[10], leadDetailsArray[11], dateDetailsArray[11], leadDetailsArray[12], dateDetailsArray[12], leadDetailsArray[13], dateDetailsArray[13], leadDetailsArray[14], dateDetailsArray[14], leadDetailsArray[15], dateDetailsArray[15], "", lead.getLeadStatus() }); index++; lead = null; } } list = null; Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } return flag; }
From source file:com.jubinationre.service.AdminMaintainService.java
public boolean createExcel(List<CallAPIMessage> list) { boolean flag = false; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); CallAPIMessage[] messageArray = new CallAPIMessage[list.size()]; list.toArray(messageArray);/* w w w . j a v a2 s.c o m*/ Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "CallFrom", "CallTo", "Status", "TrackStatus", "CallType", "DailWhomNumber", "DailCallDuration", "Message", "DateCreated", "AnsweredBy", "StartTime", "EndTime", "DateUpdated", "Duration", "Price", "Direction", "Digits", "Sid", "Uri", "RecordingUrl", "PhoneNumberSid", "AccountSid", "ForwardedFrom", "CallerName", "ParentCallSid" }); index++; for (CallAPIMessage message : messageArray) { data.put(index.toString(), new Object[] { message.getCallFrom(), message.getCallTo(), message.getStatus(), message.getTrackStatus(), message.getCallType(), message.getDailWhomNumber(), message.getDailCallDuration(), message.getMessage(), message.getDateCreated(), message.getAnsweredBy(), message.getStartTime(), message.getEndTime(), message.getDateUpdated(), message.getDuration(), message.getPrice(), message.getDirection(), message.getDigits(), message.getSid(), message.getUri(), message.getRecordingUrl(), message.getPhoneNumberSid(), message.getAccountSid(), message.getForwardedFrom(), message.getCallerName(), message.getParentCallSid() }); index++; } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } try { FileOutputStream out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out.close(); flag = true; System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return flag; }
From source file:com.kcs.action.CompareDsFcpDsFtsAction.java
@Override public String export() throws Exception { list = service.findMainList(DateUtil.getDateFromString(dataSetDate, DateUtil.DEFAULT_DATE_FORMAT)); logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + list.size()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Compate Ds FCP with Ds FTS Report"); setFileName("Compate Ds FCP with Ds FTS Report_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != list && list.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("data_set_Date"); row.createCell(cellnum++).setCellValue("pos_item"); row.createCell(cellnum++).setCellValue("cl_nm_thai_fcp"); row.createCell(cellnum++).setCellValue("fcp_amt"); row.createCell(cellnum++).setCellValue("fcp_curr"); row.createCell(cellnum++).setCellValue("tran_item"); row.createCell(cellnum++).setCellValue("cl_nm_thai_fts"); row.createCell(cellnum++).setCellValue("buy_fts_amt"); row.createCell(cellnum++).setCellValue("sell_fts_amt"); row.createCell(cellnum++).setCellValue("fts_curr"); row.createCell(cellnum++).setCellValue("Diff_amt"); for (CompareDsFcpDsFts obj : list) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;/*from www . j a va 2s . c om*/ rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPosItem() + ""); rowData.createCell(cellnum++).setCellValue(obj.getClNmThaiFcp() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFcpAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFcpCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTranItem() + ""); rowData.createCell(cellnum++).setCellValue(obj.getClNmThaiFts() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBuyFtsAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSellFtsAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFtsCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDiffAmt() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.ForwardContractAction.java
@Override @SuppressWarnings("empty-statement") public String export() throws Exception { list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate())); getLogger().debug("exportExcel : begin..."); setFileName("Forward contract Report.xls"); DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFCellStyle style = myWorkBook.createCellStyle(); HSSFCellStyle styleFont = myWorkBook.createCellStyle(); HSSFFont fontB = myWorkBook.createFont(); fontB.setBoldweight(Font.BOLDWEIGHT_BOLD); styleFont.setFont(fontB);//from w ww.ja v a2s . co m list = sortSheet(list); List<String> listSheet = countSheet(list); for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) { List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet)); HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet)); List<String> currencyList = getCurrency(sheetObject); //------------------------- ROW 1 -----------------------------// Row FW1_row_0 = FW1.createRow(0); FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT"); CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER); HSSFCellStyle styleRow1 = myWorkBook.createCellStyle(); styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow1.setFont(fontB); FW1_row_0.getCell(0).setCellStyle(styleRow1); FW1_row_0.createCell(1) .setCellValue(" ? ?()"); FW1_row_0.getCell(1).setCellStyle(styleRow1); // FW1_row_0.getCell(8).setCellStyle(styleFont); //------------------------- ROW 2 -----------------------------// Row FW1_row_1 = FW1.createRow(1); FW1_row_1.createCell(0).setCellValue("FW"); // FW1_row_1.getCell(0).setCellStyle(styleBR); // FW1.addMergedRegion(new CellRangeAddress(1,1,7,8)); FW1.autoSizeColumn(0); FW1.autoSizeColumn(7); FW1.autoSizeColumn(8); Row FW1_row_2 = FW1.createRow(2); FW1_row_2.createCell(0).setCellValue("?"); //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT); HSSFCellStyle style2 = myWorkBook.createCellStyle(); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); FW1_row_2.getCell(0).setCellStyle(style2); Row FW1_row_3 = FW1.createRow(3); FW1_row_3.createCell(0).setCellValue("?"); HSSFCellStyle style3 = myWorkBook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_3.getCell(0).setCellStyle(style3); FW1.createRow(4).createCell(0).setCellValue("?"); FW1.createRow(5).createCell(0).setCellValue("? (129027)"); FW1.createRow(6).createCell(0).setCellValue( "???"); FW1.createRow(7).createCell(0).setCellValue(" ?"); FW1.createRow(8).createCell(0).setCellValue(" - ?? (129030)"); FW1.createRow(9).createCell(0) .setCellValue(" - ??? (129031)"); FW1.createRow(10).createCell(0).setCellValue(" ??"); FW1.createRow(11).createCell(0).setCellValue(" "); FW1.createRow(12).createCell(0).setCellValue(" - ?? (129034)"); FW1.createRow(13).createCell(0) .setCellValue(" - ??? (129035)"); FW1.createRow(14).createCell(0).setCellValue(" ???"); FW1.createRow(15).createCell(0).setCellValue(" - ?? (129037)"); FW1.createRow(16).createCell(0) .setCellValue(" - ??? (129038) "); FW1.createRow(17).createCell(0) .setCellValue(" ?"); FW1.createRow(18).createCell(0).setCellValue(" - ?? (129040)"); FW1.createRow(19).createCell(0) .setCellValue(" - ??? (129041) "); FW1.createRow(20).createCell(0) .setCellValue(" ??"); FW1.createRow(21).createCell(0).setCellValue(" - ?? (129043)"); FW1.createRow(22).createCell(0) .setCellValue(" - ??? (129044)"); FW1.createRow(23).createCell(0) .setCellValue(" ?"); FW1.createRow(24).createCell(0).setCellValue(" - ?? (129046)"); FW1.createRow(25).createCell(0) .setCellValue(" - ??? (129047) "); FW1.createRow(26).createCell(0).setCellValue("????"); FW1.createRow(27).createCell(0).setCellValue(" ??"); FW1.createRow(28).createCell(0).setCellValue(" - ?? (129050)"); FW1.createRow(29).createCell(0) .setCellValue(" - ??? (129051)"); FW1.createRow(30).createCell(0) .setCellValue(" "); FW1.createRow(31).createCell(0).setCellValue(" - ?? (129053)"); FW1.createRow(32).createCell(0) .setCellValue(" - ??? (129054)"); FW1.createRow(33).createCell(0).setCellValue(" ? "); FW1.createRow(34).createCell(0).setCellValue(" - ?? (129056) "); FW1.createRow(35).createCell(0) .setCellValue(" - ??? (129057) "); FW1.createRow(36).createCell(0).setCellValue(" "); FW1.createRow(37).createCell(0).setCellValue(" - ?? (129059) "); FW1.createRow(38).createCell(0) .setCellValue(" - ??? (129060) "); FW1.createRow(39).createCell(0).setCellValue("? (129061) "); FW1.createRow(40).createCell(0) .setCellValue("? (129062) "); // FW1.getRow(40).getCell(0).setCellStyle(styleBR); FW1.getRow(4).getCell(0).setCellStyle(styleFont); FW1.getRow(5).getCell(0).setCellStyle(styleFont); FW1.getRow(6).getCell(0).setCellStyle(styleFont); FW1.getRow(7).getCell(0).setCellStyle(styleFont); FW1.getRow(11).getCell(0).setCellStyle(styleFont); FW1.getRow(14).getCell(0).setCellStyle(styleFont); FW1.getRow(17).getCell(0).setCellStyle(styleFont); FW1.getRow(20).getCell(0).setCellStyle(styleFont); FW1.getRow(23).getCell(0).setCellStyle(styleFont); FW1.getRow(26).getCell(0).setCellStyle(styleFont); FW1.getRow(30).getCell(0).setCellStyle(styleFont); FW1.getRow(33).getCell(0).setCellStyle(styleFont); FW1.getRow(36).getCell(0).setCellStyle(styleFont); FW1.getRow(39).getCell(0).setCellStyle(styleFont); FW1.getRow(40).getCell(0).setCellStyle(styleFont); int cellCurrency = 1; for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); String currencyName = currencyList.get(iCurrencyList); FW1_row_2.createCell(cellCurrency).setCellValue(currencyName); FW1_row_3.createCell(cellCurrency).setCellValue(""); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style); FW1_row_3.getCell(cellCurrency).setCellStyle(style); List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName); for (ForwardContract objCCY : tmpGroupCCY) { FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + ""); if ("????" .equals(objCCY.getTRANS_TYPE())) { if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(8).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true)); FW1.getRow(8).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(9).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false)); FW1.getRow(9).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false)); } } else if ("????" .equals(objCCY.getTRANS_TYPE().trim())) { if ("New Forward".equals(objCCY.getLIST_ITEM())) { FW1.getRow(28).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true)); FW1.getRow(28).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(29).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false)); FW1.getRow(29).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false)); } } else if ("?".equals(objCCY.getTRANS_TYPE())) { } FW1.getRow(39).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false)); FW1.getRow(39).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false)); } // // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+""); CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency)); FW1_row_3.createCell(cellCurrency).setCellValue(""); HSSFCellStyle styleTop = style; styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN); FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop); cellCurrency++; } System.out.println("cellCurrency ++++ " + cellCurrency); int finishM = cellCurrency - 3; int noOfColumns = FW1.getRow(2).getLastCellNum(); int noOfRow = FW1.getLastRowNum(); // HSSFPalette palette = myWorkBook.getCustomPalette(); // HSSFColor hssfColor = null; // // // palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255); // hssfColor = palette.getColor(HSSFColor.WHITE.index); // // // // HSSFCellStyle styleBG = myWorkBook.createCellStyle(); // styleBG.setFillForegroundColor(hssfColor.getIndex()); // styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND); for (int i = 0; i < noOfRow; i++) { for (int j = 0; j < noOfColumns; j++) { FW1.autoSizeColumn(i); // row.getCell(j).setCellStyle(styleBG); } } for (int i = 4; i < noOfRow + 1; i++) { for (int j = 1; j < noOfColumns; j++) { HSSFCellStyle style6 = myWorkBook.createCellStyle(); try { if (j == 1) { style6.setBorderLeft(HSSFCellStyle.BORDER_THIN); style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } else { style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } if (i == noOfRow) { style6.setBorderBottom(HSSFCellStyle.BORDER_THIN); } FW1.getRow(i).getCell(j).setCellStyle(style6); } catch (Exception e) { FW1.getRow(i).createCell(j).setCellStyle(style6); } } } HSSFFont fontBold = myWorkBook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle(); borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN); borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); borderRightCenterFontBold.setFont(fontBold); HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold; borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleR = myWorkBook.createCellStyle(); styleR.setBorderRight(HSSFCellStyle.BORDER_THIN); // Forward contract // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold); FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM)); FW1_row_0.createCell(finishM + 1) .setCellValue(" " + dateFormat.format(new Date()) + ""); HSSFCellStyle style4 = myWorkBook.createCellStyle(); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_0.getCell(finishM + 1).setCellStyle(style4); FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont); FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2)); FW1_row_1.createCell(finishM + 1).setCellValue( ":"); FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2)); FW1_row_1.getCell(finishM + 1).setCellStyle(style4); FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont); FW1_row_0.createCell(finishM + 2).setCellStyle(styleR); FW1_row_1.createCell(finishM + 2).setCellStyle(styleR); FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold); HSSFCellStyle borderBottom = myWorkBook.createCellStyle(); borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN); borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT); borderBottom.setFont(fontBold); FW1.getRow(40).getCell(0).setCellStyle(borderBottom); HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch(); /* Here is the thing: the line will go from top left in cell (0,0) to down left of cell (0,1) */ // int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3); HSSFSimpleShape shape = patriarch.createSimpleShape(anchor); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); shape.setLineStyleColor(10, 10, 10); shape.setFillColor(90, 10, 200); shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT); shape.setLineStyle(HSSFShape.LINESTYLE_SOLID); // createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); // FW1.createFreezePane(0,4); } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.FrfImpExpAction.java
@Override public String export() throws Exception { // paging = datasetIrfService.findListIrf(createPaginate(), dataSetDate, systemCode); resultList = getDatasetFrfService().findMainList(dataSetDate, ""); resultSize = resultList.size();//from w w w. j av a 2 s. c o m logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset FRF"); setFileName("Export Data DS_FRF_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != resultList && resultList.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("orgId"); row.createCell(cellnum++).setCellValue("dataSetDate"); row.createCell(cellnum++).setCellValue("loanDepsitTrnTye"); row.createCell(cellnum++).setCellValue("currCode"); row.createCell(cellnum++).setCellValue("paymentMethod"); row.createCell(cellnum++).setCellValue("brOrBcFlg"); row.createCell(cellnum++).setCellValue("commInLieuRate"); row.createCell(cellnum++).setCellValue("minCommInLieu"); row.createCell(cellnum++).setCellValue("maxCommInLieu"); row.createCell(cellnum++).setCellValue("othFeeDesc"); row.createCell(cellnum++).setCellValue("effectiveDate"); row.createCell(cellnum++).setCellValue("endDate"); row.createCell(cellnum++).setCellValue("seq"); row.createCell(cellnum++).setCellValue("updBy"); row.createCell(cellnum++).setCellValue("updDate"); row.createCell(cellnum++).setCellValue("sysCode"); for (Datasetfrf obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getLoanDepsitTrnTye() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurrCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPaymentMethod() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBrOrBcFlg() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieu() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieu() + ""); rowData.createCell(cellnum++).setCellValue(obj.getOthFeeDesc() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEffectiveDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEndDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.FrwImpExpAction.java
@Override public String export() throws Exception { resultList = datasetFrwService.findMainList(dataSetDate, ""); resultSize = resultList.size();/* www . j a v a2 s . c om*/ logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset FRW"); setFileName("Export Data DS_FRW_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != resultList && resultList.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("orgId"); row.createCell(cellnum++).setCellValue("dataSetDate"); row.createCell(cellnum++).setCellValue("currCode"); row.createCell(cellnum++).setCellValue("commInLieuRateForDepsit"); row.createCell(cellnum++).setCellValue("minCommInLieuForDepsit"); row.createCell(cellnum++).setCellValue("maxCommInLieuForDepsit"); row.createCell(cellnum++).setCellValue("inwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("minInwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("maxInwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("othFeeDescForDepsit"); row.createCell(cellnum++).setCellValue("commInLieuRateForWithdw"); row.createCell(cellnum++).setCellValue("minCommInLieuRateFWithdw"); row.createCell(cellnum++).setCellValue("maxCommInLieuRateFWithdw"); row.createCell(cellnum++).setCellValue("withdwFeeForOthTransf"); row.createCell(cellnum++).setCellValue("effectiveDate"); row.createCell(cellnum++).setCellValue("endDate"); row.createCell(cellnum++).setCellValue("seq"); row.createCell(cellnum++).setCellValue("updBy"); row.createCell(cellnum++).setCellValue("updDate"); row.createCell(cellnum++).setCellValue("sysCode"); for (Datasetfrw obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurrCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRateForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieuForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieuForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getOthFeeDescForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRateForWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieuRateFWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieuRateFWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getWithdwFeeForOthTransf() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEffectiveDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEndDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.ImportExportDsDipAction.java
@Override public String export() throws Exception { resultList = service.findByCriteriaServiceList(DateUtil.convertDateFromJsp(getDataSetDate()), ""); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + resultList.size()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("DS_DIP"); setFileName("DS_DIP_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != resultList && resultList.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("OrgId"); row.createCell(cellnum++).setCellValue("DataPvdrBrcNo"); row.createCell(cellnum++).setCellValue("FiRptGrp"); row.createCell(cellnum++).setCellValue("DataSetDate"); row.createCell(cellnum++).setCellValue("ItemType"); row.createCell(cellnum++).setCellValue("ItemDesc"); row.createCell(cellnum++).setCellValue("CtryId"); row.createCell(cellnum++).setCellValue("Curr"); row.createCell(cellnum++).setCellValue("Amt"); row.createCell(cellnum++).setCellValue("UpdDate"); row.createCell(cellnum++).setCellValue("UpdBy"); row.createCell(cellnum++).setCellValue("SysCode"); row.createCell(cellnum++).setCellValue("Seq"); row.createCell(cellnum++).setCellValue("CustCode"); for (Datasetdip obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;//from w w w. j a v a 2s . c o m rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataPvdrBrcNo() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFiRptGrp() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getItemType() + ""); rowData.createCell(cellnum++).setCellValue(obj.getItemDesc() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCtryId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCustCode() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }