List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook close
@Override public void close() throws IOException
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void downloadExcel(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook wb) { ServletOutputStream stream = null;/* w w w . ja va 2 s. co m*/ String fileName = "relatorio" + " " + new Date().getTime(); fileName = fileName.replace(" ", "_"); try { stream = response.getOutputStream(); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls"); response.setContentType("application/vnd.ms-excel"); wb.write(stream); System.out.println("Excel saved!!!!!"); } catch (Exception e) { System.out.println("Error write excel" + e); } finally { if (stream != null) { try { stream.close(); wb.close(); } catch (IOException io) { System.out.println("Error close Steram" + io); } } } }
From source file:com.binlist.binlistreader.BinlistReader.java
public static void main(String[] args) { String result[] = null;//from w ww .j a va 2 s. co m String folder = "/opt/"; String sourceFileName = "binlist.xls"; FileInputStream fis = null; try { fis = new FileInputStream(folder + sourceFileName); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator = null; Row row = null; Cell cell = null; int cellNo = 0; String binno = ""; String longUrl = ""; String shortUrl = ""; int rownum = 0; while (rowIterator.hasNext()) { rownum++; if (rownum == 1) continue; row = rowIterator.next(); cellIterator = row.cellIterator(); cellNo = 0; binno = ""; longUrl = ""; shortUrl = ""; cell = row.getCell(4); if (cell != null && cell.getStringCellValue() != null) { binno = cell.getStringCellValue(); } if (binno != null && binno.length() > 5) { result = postRequestV3(binno); System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length); if (result != null) { row.getCell(5).setCellValue(checkNull(result[0], "")); row.getCell(6).setCellValue(checkNull(result[1], "")); row.getCell(7).setCellValue(checkNull(result[2], "")); } } } System.out.println("rownum..:" + rownum); fis.close(); FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName); workbook.write(out); out.close(); workbook.close(); workbook = null; } catch (Exception e) { e.printStackTrace(); } finally { if (fis != null) try { fis.close(); } catch (IOException ex) { Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex); } fis = null; } }
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {/* ww w. ja v a 2 s. c o m*/ ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int columnType = 0; String columnTypeName = ""; int recordNumber = 0; int passwordCol = -1; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFCellStyle cellStyle_varchar = workbook.createCellStyle(); cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle(); cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle(); cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_title = workbook.createCellStyle(); cellStyle_title.setAlignment(HorizontalAlignment.CENTER); HSSFCellStyle cellStyle_char = workbook.createCellStyle(); cellStyle_char.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_date = workbook.createCellStyle(); cellStyle_date.setAlignment(HorizontalAlignment.CENTER); cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle(); cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER); cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_decimal = workbook.createCellStyle(); cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT); HSSFFont font_title = workbook.createFont(); font_title.setColor((short) 0xc); font_title.setBold(true); ; font_title.setItalic(true); font_title.setUnderline(HSSFFont.U_DOUBLE); cellStyle_title.setFont(font_title); HSSFCell cell; HSSFRow row; // rs.beforeFirst(); while (rs.next()) { recordNumber++; if (recordNumber == 1) { row = sheet.createRow((int) 0); for (int column = 1; column <= numberOfColumns; column++) { cell = row.createCell((int) (column - 1)); String columnName = rsmd.getColumnLabel(column); columnName = columnName.replace("_", " "); columnName = JUtility.capitalize(columnName); cell.setCellStyle(cellStyle_title); cell.setCellValue(columnName); if (columnName.equals("Password")) { passwordCol = column; } } } row = sheet.createRow((int) recordNumber); for (int column = 1; column <= numberOfColumns; column++) { columnType = rsmd.getColumnType(column); columnTypeName = rsmd.getColumnTypeName(column); cell = row.createCell((int) (column - 1)); try { switch (columnType) { case java.sql.Types.NVARCHAR: HSSFRichTextString rtf_nvarchar; if (column == passwordCol) { rtf_nvarchar = new HSSFRichTextString("*****"); } else { rtf_nvarchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_nvarchar); cell.setCellValue(rtf_nvarchar); break; case java.sql.Types.VARCHAR: HSSFRichTextString rtf_varchar; if (column == passwordCol) { rtf_varchar = new HSSFRichTextString("*****"); } else { rtf_varchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_varchar); break; case java.sql.Types.CHAR: HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column)); cell.setCellStyle(cellStyle_char); cell.setCellValue(rtf_char); break; case java.sql.Types.DATE: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_date); } catch (Exception ex) { } break; case java.sql.Types.TIMESTAMP: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_timestamp); } catch (Exception ex) { } break; case java.sql.Types.DECIMAL: HSSFRichTextString rtf_decimal = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimal); break; case java.sql.Types.NUMERIC: HSSFRichTextString rtf_decimaln = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimaln); break; case java.sql.Types.BIGINT: HSSFRichTextString rtf_bigint = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_bigint); break; case java.sql.Types.INTEGER: HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_int); break; case java.sql.Types.FLOAT: HSSFRichTextString rtf_float = new HSSFRichTextString( String.valueOf(rs.getFloat(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_float); break; case java.sql.Types.DOUBLE: HSSFRichTextString rtf_double = new HSSFRichTextString( String.valueOf(rs.getDouble(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_double); break; default: cell.setCellValue(new HSSFRichTextString(columnTypeName)); break; } } catch (Exception ex) { String errormessage = ex.getLocalizedMessage(); HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage); cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_exception); break; } } if (recordNumber == 65535) { break; } } for (int column = 1; column <= numberOfColumns; column++) { sheet.autoSizeColumn((int) (column - 1)); } if (recordNumber > 0) { try { FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase()); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { setErrorMessage(ex.getMessage()); } } try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (SQLException e) { setErrorMessage(e.getMessage()); } }
From source file:com.diversityarrays.kdxplore.importdata.bms.BmsExcelImportHelper.java
License:Open Source License
public BmsExcelImportHelper(File excelFile) throws IOException { this.excelFile = excelFile; if (!excelFile.getName().toLowerCase().endsWith(".xls")) { //$NON-NLS-1$ throw new IllegalArgumentException("Only .xls files supported"); }/*from w w w . j a va 2 s.c o m*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile)); HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(fs); try { List<String> missing = new ArrayList<>(); description = workbook.getSheet(SHEET_NAME_DESCRIPTION); if (null == description) { missing.add(SHEET_NAME_DESCRIPTION); } observation = workbook.getSheet(SHEET_NAME_OBSERVATION); if (null == observation) { missing.add(SHEET_NAME_OBSERVATION); } if (!missing.isEmpty()) { throw new IOException(StringUtil.join("Missing required worksheet(s): ", ",", missing)); } } finally { if (workbook != null) { try { workbook.close(); } catch (IOException ignore) { } } } }
From source file:com.glaf.core.todo.util.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null; try {//from w w w .ja va 2 s . c o m wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } int sortNo = 1; Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); // System.out.print("\t" + fieldName + "=" + cellValue); } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); Todo model = new Todo(); dataMap.remove("id"); Tools.populate(model, dataMap); if (!keys.contains(model.getCode())) { model.setSortNo(sortNo++); if (id != null) { model.setId(Long.parseLong(id)); } if (ParamUtils.getDouble(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } catch (Exception ex) { throw new RuntimeException(ex); } finally { if (wb != null) { try { wb.close(); wb = null; } catch (IOException e) { } } } return todos; }
From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java
License:Apache License
@Override public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) { final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>(); try {//from w ww .j a v a2 s. c o m final HSSFWorkbook myWorkBook = new HSSFWorkbook( EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls")); for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) { final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr); addMinistryPerYearToMap(name, map, mySheet); } myWorkBook.close(); } catch ( final IOException e) { LOGGER.warn("Problem loading", e); } return map; }
From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java
License:Apache License
@Override public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(String name) { final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>(); try {// ww w .j av a2 s . c om final HSSFWorkbook myWorkBook = new HSSFWorkbook( EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls")); for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) { final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr); addDataForYearToMap(name, map, mySheet); } myWorkBook.close(); } catch ( final IOException e) { LOGGER.warn("Problem loading", e); } return map; }
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;/* www. j a v a2 s .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;/*ww w .j a v a 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;// w w w. ja v a 2 s . co 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"); 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; }