Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow.

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.insoul.ti.controller.ContestProjectEntryController.java

@RequestMapping("/download")
public ModelAndView download(HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();// Excel
    HSSFSheet sheet = workbook.createSheet();// ExcelSheet
    sheet.createFreezePane(1, 2);// 
    // /*from  www  . j  a va2s . com*/
    sheet.setColumnWidth(0, 1000);
    sheet.setColumnWidth(1, 3500);
    sheet.setColumnWidth(2, 3500);
    sheet.setColumnWidth(3, 6500);
    sheet.setColumnWidth(4, 6500);
    sheet.setColumnWidth(5, 6500);
    sheet.setColumnWidth(6, 6500);
    sheet.setColumnWidth(7, 2500);
    // Sheet?
    //        HSSFCellStyle sheetStyle = workbook.createCellStyle();
    // 
    //        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    // ?
    //        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    // ?
    //        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
    // ?
    //        for (int i = 0; i <= 14; i++) {
    //            sheet.setDefaultColumnStyle((short) i, sheetStyle);
    //        }
    // 
    HSSFFont headfont = workbook.createFont();
    headfont.setFontName("");
    headfont.setFontHeightInPoints((short) 22);// ?
    headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 
    // ??
    HSSFCellStyle headstyle = workbook.createCellStyle();
    headstyle.setFont(headfont);
    headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    headstyle.setLocked(true);
    headstyle.setWrapText(true);// ?
    // ??
    HSSFFont columnHeadFont = workbook.createFont();
    columnHeadFont.setFontName("");
    columnHeadFont.setFontHeightInPoints((short) 10);
    columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // ?
    HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
    columnHeadStyle.setFont(columnHeadFont);
    columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    columnHeadStyle.setLocked(true);
    columnHeadStyle.setWrapText(true);
    columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 
    columnHeadStyle.setBorderLeft((short) 1);// ?
    columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ?
    columnHeadStyle.setBorderRight((short) 1);// ?
    columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    // ????
    columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ??
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 
    style.setWrapText(true);
    style.setLeftBorderColor(HSSFColor.BLACK.index);
    style.setBorderLeft((short) 1);
    style.setRightBorderColor(HSSFColor.BLACK.index);
    style.setBorderRight((short) 1);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    style.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    style.setFillForegroundColor(HSSFColor.WHITE.index);// ?
    // ??
    HSSFCellStyle centerstyle = workbook.createCellStyle();
    centerstyle.setFont(font);
    centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    centerstyle.setWrapText(true);
    centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
    centerstyle.setBorderLeft((short) 1);
    centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
    centerstyle.setBorderRight((short) 1);
    centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ?
    try {
        // 
        HSSFRow row0 = sheet.createRow(0);
        // 
        row0.setHeight((short) 900);
        // 
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue(new HSSFRichTextString("???"));
        cell0.setCellStyle(headstyle);
        /**
         * ?? ??0 ??0 ??0 ??0
         */
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14);
        sheet.addMergedRegion(range);
        // 
        HSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) 750);
        HSSFCell cell = row1.createCell(0);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(1);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(2);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(3);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(4);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(5);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(6);
        cell.setCellValue(new HSSFRichTextString("?"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(7);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(8);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(9);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(10);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(11);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(12);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(13);
        cell.setCellValue(new HSSFRichTextString("?"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(14);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        List<ContestEntry> projectList = contestEntryDAO.findAll();
        int m = 2;
        int len = projectList.size();
        for (int i = 0; i < len; i++) {
            ContestEntry c = projectList.get(i);
            HSSFRow row = sheet.createRow(m + i);
            cell = row.createCell(0);
            cell.setCellValue(new HSSFRichTextString(c.getId() + ""));
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(new HSSFRichTextString(c.getLocation()));
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue(new HSSFRichTextString(c.getInstance()));
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue(new HSSFRichTextString(c.getIndustry()));
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue(new HSSFRichTextString(c.getLegalFormation()));
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue(new HSSFRichTextString(c.getRegtime()));
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + ""));
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue(new HSSFRichTextString(c.getLegalPerson()));
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue(new HSSFRichTextString(c.getUserCategory()));
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue(new HSSFRichTextString(c.getContact()));
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue(new HSSFRichTextString(c.getIdNumber()));
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue(new HSSFRichTextString(c.getBankName()));
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue(new HSSFRichTextString(c.getBankUserName()));
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue(new HSSFRichTextString(c.getBankAccount()));
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue(new HSSFRichTextString(c.getSupportMoney()));
            cell.setCellStyle(style);
        }
        String filename = System.nanoTime() + ".xls";// Excel??
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    } catch (Exception e) {
        log.error("download excel Error.", e);
    }
    return null;
}

From source file:com.isoftstone.web.dao.Car_dao.java

/**
 * ?excel,?excel/*  w w  w.  ja v  a2  s . com*/
 * @param carlist
 * @param path
 * @return ?excel
 */
public boolean addToExcel(List<Car_inf> carlist, String path) {

    System.out.println("excel\n");
    // webbookExcel  
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet  
    HSSFSheet sheet = wb.createSheet("");
    // sheet0,poiExcelshort  
    HSSFRow row = sheet.createRow((int) 0);
    //    
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //   

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("id");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 4);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 5);
    cell.setCellValue("");
    cell.setCellStyle(style);

    for (int i = 0; i < carlist.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car_inf car = (Car_inf) carlist.get(i);
        //   
        String logon = car.getLogon().toString();
        String dated = car.getDated().toString();
        row.createCell((short) 0).setCellValue(car.getId());
        row.createCell((short) 1).setCellValue(car.getBrand());
        row.createCell((short) 2).setCellValue(car.getSeat());
        row.createCell((short) 3).setCellValue(logon);
        row.createCell((short) 4).setCellValue(dated);
        row.createCell((short) 5).setCellValue(car.getD_license());
        row.createCell((short) 6).setCellValue(car.getC_license());
    }
    //   
    try {
        System.out.println("true\n");
        String name = path + "?.xls";
        FileOutputStream fout = new FileOutputStream(name);
        wb.write(fout);
        fout.close();
        System.out.println("\n");
        return true;
    } catch (Exception e) {
        e.printStackTrace();
    }

    return false;
}

From source file:com.itn.excelDao.ExcelView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest requesr,
        HttpServletResponse response) throws Exception {

    // get data model which is passed by the Spring container
    List<Users> allUsers = (List<Users>) model.get("allUsers");

    //Create new excel sheet
    HSSFSheet sheet = workbook.createSheet("Java Books");
    sheet.setDefaultColumnWidth(30);/* ww  w.ja v a2s.  c  o m*/

    //create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // create header row
    HSSFRow header = sheet.createRow(0);

    header.createCell(0).setCellValue("id");
    header.getCell(0).setCellStyle(style);

    header.createCell(1).setCellValue("First Name");
    header.getCell(1).setCellStyle(style);

    header.createCell(2).setCellValue("Last Name");
    header.getCell(2).setCellStyle(style);

    header.createCell(3).setCellValue("Email Address");
    header.getCell(3).setCellStyle(style);

    // create data rows
    int rowCount = 1;

    for (Users aUsers : allUsers) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(aUsers.getId());
        aRow.createCell(1).setCellValue(aUsers.getFirstName());
        aRow.createCell(2).setCellValue(aUsers.getLastName());
        aRow.createCell(3).setCellValue(aUsers.getEmail());

    }

}

From source file:com.jfinal.ext.render.excel.PoiKit.java

License:Apache License

public HSSFWorkbook export() {
    Preconditions.checkNotNull(headers, "headers can not be null");
    Preconditions.checkNotNull(columns, "columns can not be null");
    Preconditions.checkArgument(cellWidth >= 0, "cellWidth < 0");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    HSSFRow row = null;//from  w w w.  j  a  va 2s  . c o m
    HSSFCell cell = null;
    if (headers.length > 0) {
        row = sheet.createRow(0);
        if (headerRow <= 0) {
            headerRow = HEADER_ROW;
        }
        headerRow = Math.min(headerRow, MAX_ROWS);
        for (int h = 0, lenH = headers.length; h < lenH; h++) {
            @SuppressWarnings("deprecation")
            Region region = new Region(0, (short) h, (short) headerRow - 1, (short) h);// ?rowFromcolumnFrom
            sheet.addMergedRegion(region);// rowTocolumnTo
            // 
            sheet.getNumMergedRegions();
            if (cellWidth > 0) {
                sheet.setColumnWidth(h, cellWidth);
            }
            cell = row.createCell(h);
            cell.setCellValue(headers[h]);
        }
    }
    if (data.size() == 0) {
        return wb;
    }
    for (int i = 0, len = data.size(); i < len; i++) {
        row = sheet.createRow(i + headerRow);
        Object obj = data.get(i);
        if (obj == null) {
            continue;
        }
        if (obj instanceof Map) {
            processAsMap(columns, row, obj);
        } else if (obj instanceof Model) {
            processAsModel(columns, row, obj);
        } else if (obj instanceof Record) {
            processAsRecord(columns, row, obj);
        }
    }
    return wb;
}

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java

License:Open Source License

public void setOutputSingletResult(String[][] inputData, String[] outputResult, String filePath) {
    String[][] outputResultTwoDimensionArray;
    outputResultTwoDimensionArray = new String[inputData.length][inputData[0].length + 1];
    Path folderPath = null;// w w w . j  a  va  2 s  .com
    try {
        folderPath = Paths.get(filePath);
    } catch (InvalidPathException i) {
        System.out.println("Please Check whether " + filePath + " exist ");
    }
    for (int i = 0; i < outputResultTwoDimensionArray.length; i++) {
        for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) {
            if (j == (outputResultTwoDimensionArray[i].length - 1)) {
                for (int k = j; k < outputResultTwoDimensionArray[i].length; k++) {
                    outputResultTwoDimensionArray[i][k] = outputResult[i];
                }
            } else
                outputResultTwoDimensionArray[i][j] = inputData[i][j];
        }
    }

    CharSequence filepathWindows = "\\";
    CharSequence filepathGNULinux = "/";
    try {
        if (filePath.contains(filepathWindows) || filePath.contains(filepathGNULinux)) {
            String filename = "test_output_singlet_" + String.valueOf(System.currentTimeMillis()) + ".xls";
            String finalFileName = filePath + filename;
            FileOutputStream outputFile = new FileOutputStream(finalFileName);
            HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
            HSSFSheet hSSFSheet = hSSFWorkbook.createSheet("output");
            for (int i = 0; i < outputResultTwoDimensionArray.length; i++) {
                HSSFRow row = hSSFSheet.createRow(i);
                for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(outputResultTwoDimensionArray[i][j]);
                }
            }
            hSSFWorkbook.write(outputFile);
            outputFile.flush();
            outputFile.close();
            System.out.println("An output file named \"" + filename + "\" was created at \"" + filePath + "\"");
            System.out.println("Good Bye !!!");
        }
    } catch (FileNotFoundException fnfe) {
        System.out.println("Sorry " + filePath + " does not exist");
    }

    catch (IOException ioe) {
        System.out.println("Error to open/close file from path " + filePath);
    }

}

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java

License:Open Source License

public void setOutputResult(String[][] inputData, String[][] outputResult, String filePath) {
    String[][] outputResultTwoDimensionArray;
    outputResultTwoDimensionArray = new String[inputData.length][inputData[0].length + outputResult[0].length];
    Path folderPath = null;//  w ww  .ja  va  2  s.co  m
    try {
        folderPath = Paths.get(filePath);
    } catch (InvalidPathException i) {
        System.out.println("Please Check whether " + filePath + " exist ");
    }
    for (int i = 0; i < outputResultTwoDimensionArray.length; i++) {
        for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) {
            if (j >= (outputResultTwoDimensionArray[i].length - outputResult[i].length)) {

                outputResultTwoDimensionArray[i][j] = outputResult[i][j
                        - (outputResultTwoDimensionArray[i].length - outputResult[i].length)];

            } else
                outputResultTwoDimensionArray[i][j] = inputData[i][j];
        }
    }
    CharSequence filepathWindows = "\\";
    CharSequence filepathGNULinux = "/";
    try {
        if (filePath.contains(filepathWindows) || filePath.contains(filepathGNULinux)) {
            String filename = "test_output_" + String.valueOf(System.currentTimeMillis()) + ".xls";
            String finalFileName = filePath + filename;
            FileOutputStream outputFile = new FileOutputStream(finalFileName);
            HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
            HSSFSheet hSSFSheet = hSSFWorkbook.createSheet("output");
            for (int i = 0; i < outputResultTwoDimensionArray.length; i++) {
                HSSFRow row = hSSFSheet.createRow(i);
                for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(outputResultTwoDimensionArray[i][j]);
                }
            }
            hSSFWorkbook.write(outputFile);
            outputFile.flush();
            outputFile.close();
            System.out.println("An output file named \"" + filename + "\" was created at \"" + filePath + "\"");
            System.out.println("Good Bye !!!");
        }
    } catch (FileNotFoundException fnfe) {
        System.out.println("Sorry " + filePath + " does not exist");
    }

    catch (IOException ioe) {
        System.out.println("Error to open/close file from path " + filePath);
    }
}

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 w  w .j  av a2s.c  o  m*/
        // 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  ww  . j  a  v a2s .  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;/*  w w  w. ja  va  2s .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 . j a  v a2  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;
}