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.lushapp.common.excel.ExcelUtil.java

License:Apache License

/**
 * //from www  .j av a2  s  . c o  m
 * @param excel_name
 *            ?Excel+??
 * @param headList
 *            ExcelHead
 * @param valueList
 *            Excel?
 * @throws Exception
 */
public static void bulidExcel(String excel_name, String[] headList, List<String[]> valueList) throws Exception {
    // Excel 
    HSSFWorkbook workbook = new HSSFWorkbook();

    // Excel???
    // ???""?
    // HSSFSheet sheet = workbook.createSheet("");
    HSSFSheet sheet = workbook.createSheet();
    // 0?
    HSSFRow row = sheet.createRow(0);
    // ===============================================================
    for (int i = 0; i < headList.length; i++) {

        // 0??
        HSSFCell cell = row.createCell(i);
        // ?
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        // ?
        cell.setCellValue(headList[i]);
    }
    // ===============================================================

    for (int n = 0; n < valueList.size(); n++) {
        // 1?
        HSSFRow row_value = sheet.createRow(n + 1);
        String[] valueArray = valueList.get(n);
        // ===============================================================
        for (int i = 0; i < valueArray.length; i++) {

            // 0??
            HSSFCell cell = row_value.createCell(i);
            // ?
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // ?
            cell.setCellValue(valueArray[i]);
        }
        // ===============================================================
    }

    // ?
    FileOutputStream fOut = new FileOutputStream(excel_name);
    // Excel 
    workbook.write(fOut);
    fOut.flush();
    // ??
    fOut.close();
    //System.out.println("[" + excel_name + "]" + "?...");
}

From source file:com.macleod2486.magicbuilder.BlockConstructed.java

License:Open Source License

public void getBlock(int selection) {
    selection--;/*from   w w w  . ja v  a2  s .c  om*/

    try {
        int indexAdjust = 0;
        int rowIndex;
        int numberOfSets = Integer.parseInt(NumberOfBlocks.get(selection));
        int blockNameIndex = selection;
        selection = Integer.parseInt(IndexOfBlocks.get(selection));
        String tempItem;

        //Various elements
        Document page;
        Element setSection;
        Elements row;
        Elements td;

        //Worksheet the information will be written too
        HSSFWorkbook block = new HSSFWorkbook();
        HSSFSheet blockSet;
        Row infoRow;

        DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy");
        Date date = new Date();

        while (numberOfSets > 0) {
            page = Jsoup.connect(tcgSite + BlockSets.get(selection + indexAdjust).toString()).get();
            setSection = page.select("table").get(2);
            row = setSection.select("tr");
            rowIndex = 1;

            blockSet = block.createSheet(BlockSets.get(selection + indexAdjust).toString()
                    .replaceAll("%20", " ").replaceAll("%27", " "));

            infoRow = blockSet.createRow(0);
            infoRow.createCell(0).setCellValue("Card Name");
            infoRow.createCell(1).setCellValue("High Price");
            infoRow.createCell(2).setCellValue("Medium Price");
            infoRow.createCell(3).setCellValue("Low Price");

            for (Element cardrow : row) {
                td = cardrow.select("td");
                tempItem = td.get(0).text().substring(1);

                if (!tempItem.contains("Forest") && !tempItem.contains("Mountain")
                        && !tempItem.contains("Swamp") && !tempItem.contains("Island")
                        && !tempItem.contains("Plains") && !tempItem.isEmpty()) {
                    if (td.get(5).text().length() > 2 && td.get(6).text().length() > 2
                            && td.get(7).text().length() > 2) {
                        infoRow = blockSet.createRow(rowIndex);
                        infoRow.createCell(0).setCellValue(td.get(0).text().substring(1));
                        infoRow.createCell(1).setCellValue(Double.parseDouble(
                                td.get(5).text().substring(1, td.get(5).text().length() - 1).replace(",", "")));
                        infoRow.createCell(2).setCellValue(Double.parseDouble(
                                td.get(6).text().substring(1, td.get(6).text().length() - 1).replace(",", "")));
                        infoRow.createCell(3).setCellValue(Double.parseDouble(
                                td.get(7).text().substring(1, td.get(7).text().length() - 1).replace(",", "")));

                        rowIndex++;
                    }

                }
            }

            blockSet.autoSizeColumn(0);
            blockSet.autoSizeColumn(1);
            blockSet.autoSizeColumn(2);

            indexAdjust++;
            numberOfSets--;

        }

        File blockFile = new File(
                BlockNames.get(blockNameIndex) + "-Block-" + dateFormat.format(date) + "-.xls");
        FileOutputStream blockOutput = new FileOutputStream(blockFile);
        block.write(blockOutput);
        blockOutput.close();

    }

    catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.macleod2486.magicbuilder.Constructed.java

License:Open Source License

public void getCards() {
    try {/*  w w  w. j ava 2 s.c  om*/
        //Keeps track of the rows within the sheet as data is being written
        int rowNum;

        //Creates a excel file for the information to be stored
        HSSFWorkbook standard = new HSSFWorkbook();
        HSSFSheet setname;
        Row newRow;

        //Various values to screen the data
        String clean;
        double highprice = 0;
        double mediumPrice = 0;
        double lowPrice = 0;
        String temp;

        //Variables to take in information
        Document page;
        Element table;
        Elements row;
        Elements item;

        //Variables for extra information about the set
        double averageHighPrice = 0;
        double averageMediumPrice = 0;
        double averageLowPrice = 0;

        DecimalFormat format = new DecimalFormat("#.00");

        /*
         * Grabs the modified set values to then be used for the website url format
         * Not the most effecient for loop but will be modified as time goes on.
         */
        for (int limit = 0; limit < Sets.size(); limit++) {
            rowNum = 0;

            System.out.println("\nSet name: " + Names.get(limit).toString() + "\n");

            //Creates a new sheet per set after it filters out bad characters
            if (Names.get(limit).toString().contains(":")) {
                Names.set(limit, Names.get(limit).toString().replaceAll(":", "\\W"));
            } else if (Names.get(limit).toString().contains("/")) {
                Names.set(limit, Names.get(limit).replaceAll("/", "\\W"));
            }
            setname = standard.createSheet(Names.get(limit).toString());

            //Sets up the initial row in the sheet
            newRow = setname.createRow(0);
            newRow.createCell(0).setCellValue("Card Name");
            newRow.createCell(1).setCellValue("High Price");
            newRow.createCell(2).setCellValue("Medium Price");
            newRow.createCell(3).setCellValue("Low Price");

            /*Each modified string value is then put in the following url to then parse
              the information from it. */

            page = Jsoup.connect(
                    "http://magic.tcgplayer.com/db/price_guide.asp?setname=" + Sets.get(limit).toString())
                    .get();
            table = page.select("table").get(2);
            row = table.select("tr");

            //Grabs each card that was selected
            for (Element tableRow : row) {
                //Gets the first row 
                item = tableRow.select("td");
                clean = item.get(0).text();

                //Filters out land cards
                if (!clean.isEmpty()) {
                    if (item.get(5).text().length() > 2 && item.get(6).text().length() > 2
                            && item.get(7).text().length() > 2) {
                        //Creates new row in the sheet
                        newRow = setname.createRow(rowNum + 1);

                        //Gets the name of the card
                        clean = clean.substring(1);
                        newRow.createCell(0).setCellValue(clean);

                        //This gets the high price
                        temp = item.get(5).text();
                        highprice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(1).setCellValue(highprice);
                        averageHighPrice += highprice;

                        //This gets the medium price
                        temp = item.get(6).text();
                        mediumPrice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(2).setCellValue(mediumPrice);
                        averageMediumPrice += mediumPrice;

                        //This gets the low price
                        temp = item.get(7).text();
                        lowPrice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(3).setCellValue(lowPrice);
                        averageLowPrice += lowPrice;

                        System.out.println(
                                clean + "  H:$" + highprice + " M:$" + mediumPrice + " L:$" + lowPrice);
                        rowNum++;

                    }
                }

            }

            if (Double.isNaN(averageHighPrice) && Double.isNaN(averageMediumPrice)
                    && Double.isNaN(averageLowPrice)) {
                //Finds the averages
                averageHighPrice /= rowNum;
                averageMediumPrice /= rowNum;
                averageLowPrice /= rowNum;

                //Formats them
                averageHighPrice = Double.parseDouble(format.format(averageHighPrice));
                averageMediumPrice = Double.parseDouble(format.format(averageMediumPrice));
                averageLowPrice = Double.parseDouble(format.format(averageLowPrice));

                //Inserts the values into the table
                newRow = setname.getRow(0);

                newRow.createCell(4).setCellValue("Average High Price");
                newRow.createCell(5).setCellValue("Average Medium Price");
                newRow.createCell(6).setCellValue("Average Low Price");

                newRow = setname.getRow(1);
                newRow.createCell(4).setCellValue(averageHighPrice);
                newRow.createCell(5).setCellValue(averageMediumPrice);
                newRow.createCell(6).setCellValue(averageLowPrice);

                System.out.println("Average Prices " + averageHighPrice + " " + averageMediumPrice + " "
                        + averageLowPrice);
            }

            //Zeroes them out
            averageHighPrice = averageMediumPrice = averageLowPrice = 0;

            //Sets the sheet to auto size columns
            for (int index = 0; index < 7; index++) {
                setname.autoSizeColumn(index);
            }

        }

        //Creates the date to be added in the output file name. 
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy");
        Date date = new Date();

        if (this.selection == 0) {
            File standardFile = new File("Standard-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        } else if (this.selection == 1) {
            File standardFile = new File("Modern-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        } else {
            File standardFile = new File("All-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        }

    }

    catch (Exception e) {
        e.printStackTrace();
        if (e.toString().contains("Status=400")) {
            System.out.println("That webpage does not exist!");
        } else if (e.toString().contains("SocketTimeout")) {
            System.out.println("Your connection timed out");
        }
    }

}

From source file:com.mmj.app.common.file.ExcelUtils.java

License:Open Source License

/**
 * excelrow?T??title?T// w w  w  . java 2  s  .  c  o  m
 * 
 * @param response
 * @param list
 * @param xlsName
 * @param headTitle
 * @return
 */
public static <T> HSSFWorkbook defBuildExcel(List<T> list, String xlsName, String... headTitle) {

    return buildExcel(list, xlsName, new IExcel<T>() {

        @Override
        public void initHSSRow(List<T> list, HSSFSheet sheet) {
            HSSFCell cell;
            for (int j = 0; j < list.size(); j++) {
                T row = list.get(j);
                if (row == null) {
                    continue;
                }
                HSSFRow hssrow = sheet.createRow(j + 1);
                Map<String, String> fieldValMap = getFieldValueMap(row);

                int i = 0;
                for (Entry<String, String> entry : fieldValMap.entrySet()) {
                    Object value = entry.getValue();
                    cell = hssrow.createCell(i++);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(value == null ? StringUtils.EMPTY : value + StringUtils.EMPTY);
                }
            }
        }
    }, headTitle);
}

From source file:com.mmj.app.common.file.ExcelUtils.java

License:Open Source License

/**
 * excelrow?IExcel.initHSSRow//from  w  w w .  j a v  a2 s. c  o  m
 * 
 * @param response
 * @param list
 * @param name
 * @param iExcel
 * @param headTitle
 * @return
 */
public static <T> HSSFWorkbook buildExcel(List<T> list, String name, IExcel<T> iExcel, String... headTitle) {
    if (list == null || list.size() == 0) {
        log.error("ExcelUtils buildExcel List<T>:list is null,name={}", name);
        return null;
    }
    if (iExcel == null || Argument.isEmptyArray(headTitle)) {
        return new HSSFWorkbook();
    }
    String xlsName = name + "_" + DateViewTools.format(new Date(), "yyyy_MM_dd_HH_mm");
    // 
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 
    HSSFSheet sheet = workbook.createSheet();
    workbook.setSheetName(0, xlsName);

    for (int i = 0; i < headTitle.length; i++) {
        //   ?
        sheet.setColumnWidth(i, 17 * 256);
    }
    HSSFRow rowTitle = sheet.createRow(0);
    HSSFCell cell = null;
    for (int i = 0; i < headTitle.length; i++) {
        // ?
        cell = rowTitle.createCell(i);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(headTitle[i]);
    }
    // row
    iExcel.initHSSRow(list, sheet);
    return workbook;
}

From source file:com.modelmetrics.common.poi.ExcelSupport.java

License:Open Source License

public HSSFRow addRow(short i, HSSFSheet sheet) {
    return sheet.createRow(i);
}

From source file:com.ms.commons.test.datawriter.impl.ExcelDataWriter.java

License:Open Source License

private void writeTable(HSSFWorkbook book, MemoryTable table) {
    HSSFSheet sheet = book.createSheet(table.getName());
    if (table.getRowCount() > 0) {
        int rownum = 0;
        List<String> fields = new ArrayList<String>();
        for (MemoryField field : table.getRow(0).getFieldList()) {
            fields.add(field.getName());
        }/*from  w ww . j ava 2s.c o  m*/
        writeRow(sheet.createRow(rownum++), fields);

        for (MemoryRow row : table.getRowList()) {
            List<String> values = new ArrayList<String>();
            for (MemoryField field : row.getFieldList()) {
                values.add(field.getStringValue());
            }
            writeRow(sheet.createRow(rownum++), values);
        }
    }
}

From source file:com.mss.mirage.employee.general.EmployeeServiceImpl.java

License:Open Source License

public String generateEmployeeList(String loginId) {
    DateUtility dateutility = new DateUtility();
    String filePath = "";
    StringBuffer sb = null;/*from  w  w w.j  av  a  2  s . com*/
    Connection connection = null;

    /** callableStatement is a reference variable for CallableStatement . */
    CallableStatement callableStatement = null;

    /** preStmt,preStmtTemp are reference variable for PreparedStatement . */
    PreparedStatement preStmt = null, preStmtTemp = null;

    /** The queryString is useful to get  queryString result to the particular jsp page */
    String queryString = "";
    Statement statement = null;

    /** The statement is useful  to execute the above queryString */
    ResultSet resultSet = null;
    String timeSheetStatus = "";
    HashMap map = null;
    HashMap map1 = null;
    List finalList = new ArrayList();
    try {

        String TABLE_EMP_STATE_HISTORY = Properties.getProperty("TABLE_EMP_STATE_HISTORY");

        File file = new File(Properties.getProperty("Emp.StateHistory.Path"));

        if (!file.exists()) {
            file.mkdirs();
        }

        FileOutputStream fileOut = new FileOutputStream(
                file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls");

        connection = ConnectionProvider.getInstance().getConnection();
        String query = null;

        // query = "SELECT * FROM tblEmpStateHistory WHERE LoginId='"+loginId+"' ORDER BY StartDate DESC";
        query = "SELECT * FROM " + TABLE_EMP_STATE_HISTORY + " WHERE LoginId='" + loginId
                + "' ORDER BY StartDate DESC";

        //  System.out.println("query123-->"+query);
        String reportToName = "";
        List teamList = null;

        int j = 1;
        preStmt = connection.prepareStatement(query);

        resultSet = preStmt.executeQuery();

        while (resultSet.next()) {
            String state = "";
            if (!"".equals(resultSet.getString("State")) && resultSet.getString("State") != null) {
                state = resultSet.getString("State");
            }

            double IntRatePerHour = resultSet.getFloat("IntRatePerHour");

            double InvRatePerHour = resultSet.getFloat("InvRatePerHour");

            String LoginId = resultSet.getString("LoginId");

            String SkillSet = "";
            if (!"".equals(resultSet.getString("SkillSet")) && resultSet.getString("SkillSet") != null) {
                SkillSet = resultSet.getString("SkillSet");
            }

            String EndDate = "";
            if (!"".equals(resultSet.getString("EndDate")) && resultSet.getString("EndDate") != null) {
                EndDate = resultSet.getString("EndDate");
            }

            String StartDate = "";
            if (!"".equals(resultSet.getString("StartDate")) && resultSet.getString("StartDate") != null) {
                StartDate = resultSet.getString("StartDate");
            }

            String CreatedDate = "";
            if (!"".equals(resultSet.getString("CreatedDate")) && resultSet.getString("CreatedDate") != null) {
                CreatedDate = resultSet.getString("CreatedDate");
            }

            String PrjName = "";

            if (!"".equals(resultSet.getString("PrjName")) && resultSet.getString("PrjName") != null) {
                PrjName = resultSet.getString("PrjName");
            }

            String reportsTo = "";

            map = new HashMap();
            map.put("SNO", String.valueOf(j));
            map.put("state", state);
            map.put("IntRatePerHour", IntRatePerHour);
            map.put("InvRatePerHour", InvRatePerHour);
            map.put("LoginId", LoginId);
            map.put("SkillSet", SkillSet);
            map.put("EndDate", EndDate);
            map.put("StartDate", StartDate);
            map.put("CreatedDate", CreatedDate);
            map.put("PrjName", PrjName);
            //System.out.println("map=="+map);

            finalList.add(map);
            j++;

        }

        if (finalList.size() > 0) {
            filePath = file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls";
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            HSSFSheet sheet = hssfworkbook.createSheet("State History");

            HSSFCellStyle cs = hssfworkbook.createCellStyle();
            HSSFCellStyle headercs = hssfworkbook.createCellStyle();
            headercs.setFillForegroundColor(HSSFColor.BLACK.index);
            headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headercs.setBorderTop((short) 1); // single line border
            headercs.setBorderBottom((short) 1); // single line border

            HSSFFont timesBoldFont = hssfworkbook.createFont();
            timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            timesBoldFont.setColor(HSSFColor.WHITE.index);
            timesBoldFont.setFontName("Arial");
            headercs.setFont(timesBoldFont);

            HSSFFont footerFont = hssfworkbook.createFont();
            footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            footerFont.setFontName("Arial");

            HSSFCellStyle footercs = hssfworkbook.createCellStyle();
            footercs.setFont(footerFont);

            HSSFDataFormat df = hssfworkbook.createDataFormat();
            HSSFRow row = sheet.createRow((short) 0);
            HSSFCell cell = row.createCell((short) 0);

            HSSFCell cell1 = row.createCell((short) 1);

            HSSFCell cell2 = row.createCell((short) 2);
            HSSFCell cell3 = row.createCell((short) 3);

            HSSFCell cell4 = row.createCell((short) 4);
            HSSFCell cell5 = row.createCell((short) 5);
            HSSFCell cell6 = row.createCell((short) 6);
            HSSFCell cell7 = row.createCell((short) 7);
            HSSFCell cell8 = row.createCell((short) 8);
            cell.setCellValue("SNO");
            cell1.setCellValue("State");
            cell2.setCellValue("StartDate");
            cell3.setCellValue("EndDate");
            cell4.setCellValue("IntRatePerHour");
            cell5.setCellValue("InvRatePerHour");
            cell6.setCellValue("SkillSet");
            cell7.setCellValue("ProjectName");
            cell8.setCellValue("CreatedDate");

            cell.setCellStyle(headercs);
            cell1.setCellStyle(headercs);
            cell2.setCellStyle(headercs);
            cell3.setCellStyle(headercs);
            cell4.setCellStyle(headercs);
            cell5.setCellStyle(headercs);
            cell6.setCellStyle(headercs);
            cell7.setCellStyle(headercs);
            cell8.setCellStyle(headercs);
            int count = 1;

            if (finalList.size() > 0) {
                Map stateHistorylMap = null;
                for (int i = 0; i < finalList.size(); i++) {
                    stateHistorylMap = (Map) finalList.get(i);
                    row = sheet.createRow((short) count++);
                    cell = row.createCell((short) 0);

                    cell1 = row.createCell((short) 1);
                    cell2 = row.createCell((short) 2);
                    cell3 = row.createCell((short) 3);
                    cell4 = row.createCell((short) 4);
                    cell5 = row.createCell((short) 5);
                    cell6 = row.createCell((short) 6);
                    cell7 = row.createCell((short) 7);
                    cell8 = row.createCell((short) 8);

                    cell.setCellValue((String) stateHistorylMap.get("SNO"));
                    cell1.setCellValue((String) stateHistorylMap.get("state"));
                    cell2.setCellValue((String) stateHistorylMap.get("StartDate"));
                    cell3.setCellValue((String) stateHistorylMap.get("EndDate"));
                    cell4.setCellValue((Double) stateHistorylMap.get("IntRatePerHour"));
                    cell5.setCellValue((Double) stateHistorylMap.get("InvRatePerHour"));
                    cell6.setCellValue((String) stateHistorylMap.get("SkillSet"));
                    cell7.setCellValue((String) stateHistorylMap.get("PrjName"));
                    cell8.setCellValue((String) stateHistorylMap.get("CreatedDate"));

                    cell.setCellStyle(cs);
                    cell1.setCellStyle(cs);
                    cell2.setCellStyle(cs);
                    cell3.setCellStyle(cs);
                    cell4.setCellStyle(cs);
                    cell5.setCellStyle(cs);
                    cell6.setCellStyle(cs);
                    cell7.setCellStyle(cs);
                    cell8.setCellStyle(cs);
                }
                row = sheet.createRow((short) count++);
                cell = row.createCell((short) 0);

                cell1 = row.createCell((short) 1);
                cell2 = row.createCell((short) 2);
                cell3 = row.createCell((short) 3);
                cell4 = row.createCell((short) 4);
                cell.setCellValue("");

                cell4.setCellValue("");

                cell.setCellStyle(footercs);
                cell1.setCellStyle(footercs);
                cell2.setCellStyle(footercs);
                cell3.setCellStyle(footercs);

                cell4.setCellStyle(footercs);
            }
            sheet.autoSizeColumn((int) 0);
            sheet.autoSizeColumn((int) 1);
            sheet.autoSizeColumn((int) 2);
            sheet.autoSizeColumn((int) 3);
            sheet.autoSizeColumn((int) 4);

            hssfworkbook.write(fileOut);
            fileOut.flush();
            fileOut.close();

        }

    } catch (FileNotFoundException fne) {

        fne.printStackTrace();
    } catch (IOException ioe) {

        ioe.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        System.out.println("finally");
        try {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (preStmt != null) {
                preStmt.close();
                preStmt = null;
            }
            if (connection != null) {
                connection.close();
                connection = null;
            }
        } catch (Exception se) {
            se.printStackTrace();
        }
    }

    return filePath;

}

From source file:com.mss.mirage.marketing.MarketingServiceImpl.java

License:Open Source License

public String generateInvestmentXls(String queryString) throws ServiceLocatorException {
    String filePath = "";
    StringBuffer sb = null;//from w  w  w. j a v a 2  s  .  com

    Connection connection = null;
    /**
     * preStmt,preStmtTemp are reference variable for PreparedStatement .
     */
    PreparedStatement preStmt = null;

    /**
     * The queryString is useful to get queryString result to the particular
     * jsp page
     */
    /**
     * The statement is useful to execute the above queryString
     */
    ResultSet resultSet = null;
    HashMap map = null;
    double totalAmount = 0.0;
    double totalOpprtunity = 0.0;
    double floortotalsum = 0.0;
    String generatedPath = "";
    List finalList = new ArrayList();
    try {
        generatedPath = com.mss.mirage.util.Properties.getProperty("Marketing.Investment.Path");
        File file = new File(generatedPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        FileOutputStream fileOut = new FileOutputStream(file.getAbsolutePath() + "/Investment.xls");
        connection = ConnectionProvider.getInstance().getConnection();
        String query = null;
        if (!"".equals(queryString)) {
            query = queryString;
        } else {
            query = "SELECT * from vwInvestments WHERE STATUS='Active' ORDER BY createdDate DESC";
        }
        String reportToName = "";
        List teamList = null;
        int j = 1;
        //  System.out.println("query...."+query);
        preStmt = connection.prepareStatement(query);
        resultSet = preStmt.executeQuery();
        while (resultSet.next()) {
            String InvestmentName = resultSet.getString("Inv_Name");
            String TotalExpenses = resultSet.getString("TotalExpenses");
            String StartDate = resultSet.getString("StartDate");
            String EndDate = resultSet.getString("EndDate");
            String Location = resultSet.getString("Location");
            String InvestmentType = resultSet.getString("InvestmentType");
            String TotalOpprtunity = resultSet.getString("TotalOpprtunity");
            totalAmount = totalAmount + resultSet.getDouble("TotalExpenses");
            totalOpprtunity = totalOpprtunity + resultSet.getDouble("TotalOpprtunity");
            map = new HashMap();
            map.put("SNO", String.valueOf(j));
            map.put("InvestmentName", InvestmentName);
            map.put("TotalExpenses", TotalExpenses);
            map.put("StartDate", StartDate);
            map.put("EndDate", EndDate);
            map.put("Location", Location);
            if ("S".equalsIgnoreCase(InvestmentType)) {
                map.put("InvestmentType", "Lead Source");
            } else if ("P".equalsIgnoreCase(InvestmentType)) {
                map.put("InvestmentType", "Lead Pass");
            }
            map.put("TotalOpprtunity", TotalOpprtunity);
            map.put("Sum", totalAmount);
            map.put("SumOpp", totalOpprtunity);

            finalList.add(map);
            j++;

        }

        if (finalList.size() > 0) {
            filePath = file.getAbsolutePath() + "/Investment.xls";
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            HSSFSheet sheet = hssfworkbook.createSheet("Investment Sheet");

            HSSFFont timesBoldFont1 = hssfworkbook.createFont();
            timesBoldFont1.setFontHeightInPoints((short) 13);
            timesBoldFont1.setColor(HSSFColor.BLACK.index);
            timesBoldFont1.setFontName("Arial");

            HSSFCellStyle cellColor = hssfworkbook.createCellStyle();
            cellColor.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
            cellColor.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            cellColor.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellColor.setBorderTop((short) 1); // single line border
            cellColor.setBorderBottom((short) 1); // single line border
            cellColor.setFont(timesBoldFont1);

            HSSFCellStyle cellColor1 = hssfworkbook.createCellStyle();

            cellColor1.setFillForegroundColor(HSSFColor.WHITE.index);
            cellColor1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            cellColor1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellColor1.setBorderTop((short) 1); // single line border
            cellColor1.setBorderBottom((short) 1); // single line border
            cellColor1.setFont(timesBoldFont1);

            HSSFCellStyle cs = hssfworkbook.createCellStyle();

            HSSFCellStyle headercs = hssfworkbook.createCellStyle();
            headercs.setFillForegroundColor(HSSFColor.BLUE.index);
            headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headercs.setBorderTop((short) 1); // single line border
            headercs.setBorderBottom((short) 1); // single line border
            // cs.setFont(timesBoldFont1);

            HSSFFont timesBoldFont = hssfworkbook.createFont();
            timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            timesBoldFont.setFontHeightInPoints((short) 13);
            timesBoldFont.setColor(HSSFColor.WHITE.index);
            timesBoldFont.setFontName("Calibri");
            headercs.setFont(timesBoldFont);
            // cs.setFont(timesBoldFont);
            HSSFFont footerFont = hssfworkbook.createFont();
            footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            timesBoldFont.setFontHeightInPoints((short) 13);
            footerFont.setFontName("Calibri");

            HSSFCellStyle footercs = hssfworkbook.createCellStyle();
            footercs.setFont(footerFont);

            HSSFDataFormat df = hssfworkbook.createDataFormat();
            HSSFRow row = sheet.createRow((short) 0);
            HSSFCell cell = row.createCell((short) 0);

            HSSFCell cell1 = row.createCell((short) 1);

            HSSFCell cell2 = row.createCell((short) 2);
            HSSFCell cell3 = row.createCell((short) 3);

            HSSFCell cell4 = row.createCell((short) 4);
            HSSFCell cell5 = row.createCell((short) 5);
            HSSFCell cell6 = row.createCell((short) 6);
            HSSFCell cell7 = row.createCell((short) 7);

            cell.setCellValue("SNO");
            cell1.setCellValue("InvestmentName");
            cell2.setCellValue("TotalExpenses");
            cell3.setCellValue("StartDate");
            cell4.setCellValue("EndDate");
            cell5.setCellValue("Location");
            cell6.setCellValue("InvestmentType");
            cell7.setCellValue("TotalOpprtunity");

            cell.setCellStyle(headercs);
            cell1.setCellStyle(headercs);
            cell2.setCellStyle(headercs);
            cell3.setCellStyle(headercs);
            cell4.setCellStyle(headercs);
            cell5.setCellStyle(headercs);
            cell6.setCellStyle(headercs);
            cell7.setCellStyle(headercs);

            int count = 1;

            if (finalList.size() > 0) {
                Map stateHistorylMap = null;
                for (int i = 0; i < finalList.size(); i++) {
                    stateHistorylMap = (Map) finalList.get(i);
                    row = sheet.createRow((short) count++);
                    cell = row.createCell((short) 0);

                    cell1 = row.createCell((short) 1);
                    cell2 = row.createCell((short) 2);
                    cell3 = row.createCell((short) 3);
                    cell4 = row.createCell((short) 4);
                    cell5 = row.createCell((short) 5);
                    cell6 = row.createCell((short) 6);
                    cell7 = row.createCell((short) 7);

                    cell.setCellValue((String) stateHistorylMap.get("SNO"));
                    cell1.setCellValue((String) stateHistorylMap.get("InvestmentName"));
                    HSSFCellStyle css1 = hssfworkbook.createCellStyle();
                    HSSFCellStyle css2 = hssfworkbook.createCellStyle();
                    css1.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
                    css1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                    css1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    css1.setBorderTop((short) 1); // single line border
                    css1.setBorderBottom((short) 1); // single line border
                    css1.setFont(timesBoldFont1);

                    HSSFDataFormat df1 = hssfworkbook.createDataFormat();
                    css1.setDataFormat(df1.getFormat("#,##0.0"));
                    css2.setDataFormat(df1.getFormat("#,##0.0"));
                    css2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                    css2.setFont(timesBoldFont1);
                    cell2.setCellValue(Convert.toDouble(stateHistorylMap.get("TotalExpenses")));
                    cell3.setCellValue((String) stateHistorylMap.get("StartDate"));
                    cell4.setCellValue((String) stateHistorylMap.get("EndDate"));
                    cell5.setCellValue((String) stateHistorylMap.get("Location"));
                    cell6.setCellValue((String) stateHistorylMap.get("InvestmentType"));
                    cell7.setCellValue((String) stateHistorylMap.get("TotalOpprtunity"));

                    if (count % 2 == 0) {
                        cell.setCellStyle(cellColor1);
                        cell1.setCellStyle(cellColor1);
                        cell2.setCellStyle(css2);
                        cell3.setCellStyle(cellColor1);
                        cell4.setCellStyle(cellColor1);
                        cell5.setCellStyle(cellColor1);
                        cell6.setCellStyle(cellColor1);
                        cell7.setCellStyle(cellColor1);

                    } else {
                        cell.setCellStyle(cellColor);
                        cell1.setCellStyle(cellColor);
                        cell2.setCellStyle(css1);
                        cell3.setCellStyle(cellColor);
                        cell4.setCellStyle(cellColor);
                        cell5.setCellStyle(cellColor);
                        cell6.setCellStyle(cellColor);
                        cell7.setCellStyle(cellColor);
                    }
                }
                row = sheet.createRow((short) count++);
                cell = row.createCell((short) 0);

                cell1 = row.createCell((short) 1);
                cell2 = row.createCell((short) 2);
                cell3 = row.createCell((short) 3);
                cell4 = row.createCell((short) 4);
                cell5 = row.createCell((short) 5);
                cell6 = row.createCell((short) 6);
                cell7 = row.createCell((short) 7);
                cell.setCellValue("");
                cell7.setCellValue("");

                cell.setCellStyle(footercs);
                cell1.setCellStyle(footercs);
                cell2.setCellStyle(footercs);
                cell3.setCellStyle(footercs);

                cell4.setCellStyle(footercs);
                cell5.setCellStyle(footercs);
                cell6.setCellStyle(footercs);
                cell7.setCellStyle(footercs);
            }
            HSSFCellStyle totalSum = hssfworkbook.createCellStyle();
            totalSum.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
            totalSum.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            totalSum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            totalSum.setBorderTop((short) 1); // single line border
            totalSum.setBorderBottom((short) 1); // single line border
            totalSum.setFont(timesBoldFont1);
            HSSFCellStyle totalSum1 = hssfworkbook.createCellStyle();
            totalSum1.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
            totalSum1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
            totalSum1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            totalSum1.setBorderTop((short) 1); // single line border
            totalSum1.setBorderBottom((short) 1); // single line border
            totalSum1.setFont(timesBoldFont1);

            HSSFDataFormat totalSumdf1 = hssfworkbook.createDataFormat();

            totalSum.setDataFormat((short) 7);
            HSSFCellStyle test = hssfworkbook.createCellStyle();
            HSSFDataFormat testdf = hssfworkbook.createDataFormat();

            sheet.autoSizeColumn((int) 0);
            sheet.autoSizeColumn((int) 3);
            sheet.autoSizeColumn((int) 4);
            sheet.setColumnWidth(1, 50 * 256);
            sheet.setColumnWidth(2, 35 * 256);
            sheet.setColumnWidth(5, 25 * 256);
            sheet.setColumnWidth(6, 25 * 256);
            sheet.setColumnWidth(7, 25 * 256);
            BigDecimal bb, bc, cc, cd;
            bb = new BigDecimal(totalAmount);
            bc = bb.setScale(2, RoundingMode.CEILING);
            cc = new BigDecimal(totalOpprtunity);
            cd = cc.setScale(2, RoundingMode.CEILING);
            totalSum.setDataFormat(testdf.getFormat("#,##0.0"));

            cell.setCellStyle(totalSum);

            cell1.setCellValue("Sum ");
            cell1.setCellStyle(totalSum1);
            cell2.setCellValue(bc.longValue());

            cell2.setCellStyle(totalSum);
            cell3.setCellStyle(totalSum);
            cell4.setCellStyle(totalSum);
            cell5.setCellStyle(totalSum);

            cell6.setCellStyle(totalSum);
            cell7.setCellValue(cd.longValue());

            cell7.setCellStyle(totalSum);

            hssfworkbook.write(fileOut);
            fileOut.flush();
            fileOut.close();

        }

    } catch (FileNotFoundException fne) {

        fne.printStackTrace();
    } catch (IOException ioe) {

        ioe.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (preStmt != null) {
                preStmt.close();
                preStmt = null;
            }
            if (connection != null) {
                connection.close();
                connection = null;
            }
        } catch (Exception se) {
            se.printStackTrace();
        }
    }

    return filePath;

}

From source file:com.mss.mirage.marketing.MarketingServiceImpl.java

License:Open Source License

public String getCampaignContactsExcel(HttpServletRequest httpServletRequest, MarketingAction marketingAction)
        throws ServiceLocatorException {

    // System.out.println("getCampaignContactsExcel");
    String filePath = "";
    StringBuffer sb = null;//from  www . j a v  a2 s .  c  om

    Connection connection = null;
    /**
     * preStmt,preStmtTemp are reference variable for PreparedStatement .
     */
    PreparedStatement preStmt = null;

    /**
     * The queryString is useful to get queryString result to the particular
     * jsp page
     */
    String queryString = "";
    /**
     * The statement is useful to execute the above queryString
     */
    ResultSet resultSet = null;
    HashMap map = null;

    List finalList = new ArrayList();
    String campaignTitle = "";
    try {
        PreparedStatement preparedStatement = null;
        Statement statement = null;
        ResultSet resultSet1 = null;
        String email = "";
        String phone = "";
        String CompanyName = "";
        String contactName = "";
        int z = 0;
        int n = 1;
        Map<Integer, String> contactIdMap = new TreeMap<Integer, String>();
        // queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId=" + campaignId + " ORDER BY CreatedDate DESC";
        String loginId = httpServletRequest.getSession(false).getAttribute(ApplicationConstants.SESSION_USER_ID)
                .toString();
        String roleName = httpServletRequest.getSession(false)
                .getAttribute(ApplicationConstants.SESSION_ROLE_NAME).toString();
        campaignTitle = DataSourceDataProvider.getInstance()
                .getCampaignNameByCampaignId(marketingAction.getCampaignId());
        // queryString = "SELECT Id,Title,CreatedBy,CurrStatus FROM tblCrmLeads WHERE AccountId=" + accId;
        // queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId="+campaignId+" ORDER BY CreatedDate DESC";
        queryString = "SELECT Id,ContactId FROM tblCrmActivity WHERE CampaignId="
                + marketingAction.getCampaignId();

        if (marketingAction.getContactStartDate() != null
                && !"".equalsIgnoreCase(marketingAction.getContactStartDate())) {
            queryString = queryString + " AND (DATE(CreatedDate))>='"
                    + DateUtility.getInstance().getMysqlDate(marketingAction.getContactStartDate()) + "' ";
        }
        if (marketingAction.getContactEndDate() != null
                && !"".equalsIgnoreCase(marketingAction.getContactEndDate())) {
            queryString = queryString + " AND (DATE(CreatedDate))<='"
                    + DateUtility.getInstance().getMysqlDate(marketingAction.getContactEndDate()) + "' ";
        }
        if (roleName.equals("Sales")) {
            queryString = queryString + " AND CreatedById='" + loginId + "' ";
        }
        queryString = queryString + " ORDER BY CreatedDate DESC";

        // String contactQuery = "SELECT Id, CONCAT(FirstName,' ',MiddleName,'.',LastName) AS ContactName,Email1,CellPhone FROM tblCrmContact WHERE Id=?";
        String contactQuery = "SELECT tblCrmContact.id AS contactId,tblCrmAccount.Id AS accountId,"
                + "CONCAT(tblCrmContact.FirstName,'.',tblCrmContact.LastName) AS ContactName,"
                + " tblCrmContact.Email1 AS Email1, tblCrmAccount.NAME AS CompanyName,tblCrmContact.CellPhone AS CellPhone"
                + "  FROM tblCrmContact LEFT JOIN  tblCrmAccount ON(tblCrmContact.AccountId=tblCrmAccount.Id) WHERE tblCrmContact.id=?";
        connection = ConnectionProvider.getInstance().getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(queryString);
        while (resultSet.next()) {
            z++;
            int Id = resultSet.getInt("Id");
            String ContactId = resultSet.getString("ContactId");
            contactIdMap.put(Id, ContactId);
            //totalStream=totalStream+i+"|"+createdDate+"|"+actType+"|"+description+"|"+comments+"|"+assignedToId+"|"+contactName+"|"+datedue+"|"+actid+"|"+"^";
            //  totalActivities=totalActivities+count;
        }
        preparedStatement = connection.prepareStatement(contactQuery);

        for (Map.Entry<Integer, String> entry : contactIdMap.entrySet()) {
            Integer key = entry.getKey();
            String value = entry.getValue();
            String[] parts = value.split("\\,");
            if (parts.length > 0 && !"".equals(value)) {
                for (int j = 0; j < parts.length; j++) {
                    preparedStatement.setInt(1, Integer.parseInt(parts[j]));
                    resultSet1 = preparedStatement.executeQuery();

                    while (resultSet1.next()) {
                        CompanyName = resultSet1.getString("CompanyName");
                        contactName = resultSet1.getString("ContactName");
                        email = resultSet1.getString("Email1");
                        phone = resultSet1.getString("CellPhone");
                        map = new HashMap();
                        map.put("SNO", String.valueOf(n));
                        map.put("contactName", contactName);
                        map.put("CompanyName", CompanyName);
                        map.put("email", email);
                        // map.put("phone", phone);
                        // System.out.println(map);
                        finalList.add(map);
                        n++;
                    }

                }
            }

        }
        //  System.out.println("map" + finalList.size());
        File file = new File(Properties.getProperty("Emp.Qmeet.Path"));

        if (!file.exists()) {
            file.mkdirs();
        }

        FileOutputStream fileOut = new FileOutputStream(
                file.getAbsolutePath() + File.separator + "Contacts.xls");
        filePath = file.getAbsolutePath() + File.separator + "Contacts.xls";
        HSSFRow row1;
        HSSFWorkbook workbook = new HSSFWorkbook();
        System.out.println("filePath " + filePath);
        HSSFSheet worksheet = workbook.createSheet("Campaign Contacts");
        for (int i = 0; i < 4; i++) {
            worksheet.setColumnWidth(i, 10 * 256);

        }

        HSSFFont timesBoldFont = workbook.createFont();
        timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        timesBoldFont.setColor(HSSFColor.WHITE.index);

        timesBoldFont.setFontName("Calibri");
        HSSFCellStyle headercs = workbook.createCellStyle();
        headercs.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        headercs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headercs.setBorderTop((short) 1); // single line border
        headercs.setBorderBottom((short) 1); // single line border
        headercs.setFont(timesBoldFont);

        HSSFFont footerFont = workbook.createFont();
        footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        footerFont.setFontName("Calibri");

        // HSSFRow row1;
        // LogisticsDocBean logisticsDocBean = null;
        // index from 0,0... cell A1 is cell(0,0)

        // if(list.size()!=0){//
        //System.out.println("list size-->"+list.size());

        HSSFFont font4 = workbook.createFont();
        font4.setColor(HSSFColor.WHITE.index);
        font4.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font4.setFontHeightInPoints((short) 14);
        font4.setFontName("Calibri");

        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFont(font4);

        HSSFFont font1 = workbook.createFont();
        //font1.setColor(HSSFColor.WHITE.index);
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font1.setFontHeightInPoints((short) 14);
        font1.setFontName("Calibri");
        HSSFCellStyle cs = workbook.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs.setBorderTop((short) 1); // single line border
        cs.setBorderBottom((short) 1); // single line border
        cs.setFont(font1);

        HSSFCellStyle cs1 = workbook.createCellStyle();
        cs1.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
        cs1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs1.setFont(font4);
        cs1.setBorderTop((short) 1); // single line border
        cs1.setBorderBottom((short) 1); // single line border

        HSSFCellStyle cs2 = workbook.createCellStyle();

        cs2.setFillForegroundColor(HSSFColor.WHITE.index);
        cs2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs2.setBorderTop((short) 1); // single line border
        cs2.setBorderBottom((short) 1); // single line border
        cs2.setFont(font1);

        HSSFFont font3 = workbook.createFont();
        //font1.setColor(HSSFColor.WHITE.index);
        //  font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font3.setFontHeightInPoints((short) 14);
        font3.setFontName("Calibri");

        HSSFCellStyle cs3 = workbook.createCellStyle();
        cs3.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cs3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs3.setFont(font3);
        cs3.setBorderTop((short) 1); // single line border
        cs3.setBorderBottom((short) 1); // single line border

        HSSFCellStyle cs4 = workbook.createCellStyle();

        cs4.setFillForegroundColor(HSSFColor.WHITE.index);
        cs4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs4.setBorderTop((short) 1); // single line border
        cs4.setBorderBottom((short) 1); // single line border
        cs4.setFont(font3);

        //start   

        row1 = worksheet.createRow((short) 0);

        HSSFCell cellpo0 = row1.createCell((short) 0);
        // cellpo0.setCellValue("Purchase Order");
        HSSFCell cellpo1 = row1.createCell((short) 1);
        HSSFCell cellpo2 = row1.createCell((short) 2);
        // cellpo2.setCellValue("Created Date");
        HSSFCell cellpo3 = row1.createCell((short) 3);
        //cellpo3.setCellValue((date.getYear()+1900)+"-"+(date.getMonth()+1)+"-"+date.getDate());

        HSSFCell cellpo4 = row1.createCell((short) 4);
        HSSFCell cellpo5 = row1.createCell((short) 5);
        HSSFCell cellpo6 = row1.createCell((short) 6);
        HSSFCell cellpo7 = row1.createCell((short) 7);
        HSSFCell cellpo8 = row1.createCell((short) 8);
        HSSFCell cellpo9 = row1.createCell((short) 9);
        HSSFCell cellpo10 = row1.createCell((short) 10);
        HSSFCell cellpo11 = row1.createCell((short) 11);
        row1 = worksheet.createRow((short) 0);
        Cell cell[] = new Cell[4];
        for (int i = 0; i < 4; i++) {
            cell[i] = row1.createCell((short) i);
        }

        // cell.setCellValue("Logistics Document :-Created Date : "+(date.getYear()+1900)+"-"+(date.getMonth()+1)+"-"+date.getDate());
        cell[0].setCellValue("Contacts Details :" + campaignTitle);
        HSSFCellStyle cellStyleHead = workbook.createCellStyle();
        cellStyleHead.setFont(timesBoldFont);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setFillBackgroundColor(HSSFColor.PALE_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cell[0].setCellStyle(cellStyle);

        worksheet.addMergedRegion(CellRangeAddress.valueOf("A1:" + "D2"));

        //sno
        row1 = worksheet.createRow((short) 2);
        cell[0] = row1.createCell((short) 0);
        cell[0].setCellValue("SNo");
        // cellStyleHead.setFont(font4);
        cellStyleHead.setFont(timesBoldFont);
        //  cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //cellStyleHead.setFillBackgroundColor(HSSFColor.PALE_BLUE.index);
        // cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // cell[0].setCellStyle(cellStyleHead);
        cell[0].setCellStyle(cs1);

        worksheet.addMergedRegion(CellRangeAddress.valueOf("A3:A4"));

        cell[0] = row1.createCell((short) 1);
        cell[0].setCellValue("Contact Name");
        cellStyleHead.setFont(timesBoldFont);
        cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //cell[0].setCellStyle(cellStyleHead);
        cell[0].setCellStyle(cs1);
        worksheet.addMergedRegion(CellRangeAddress.valueOf("B3:B4"));

        cell[0] = row1.createCell((short) 2);
        cell[0].setCellValue("Company Name");
        cellStyleHead.setFont(timesBoldFont);
        cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //cell[0].setCellStyle(cellStyleHead);
        cell[0].setCellStyle(cs1);
        worksheet.addMergedRegion(CellRangeAddress.valueOf("C3:C4"));

        cell[0] = row1.createCell((short) 3);
        cell[0].setCellValue("E-Mail");
        cellStyleHead.setFont(timesBoldFont);
        cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // cell[0].setCellStyle(cellStyleHead);
        cell[0].setCellStyle(cs1);
        worksheet.addMergedRegion(CellRangeAddress.valueOf("D3:D4"));

        //            cell[0] = row1.createCell((short) 4);
        //            cell[0].setCellValue("Phone");
        //            cellStyleHead.setFont(timesBoldFont);
        //            cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //            //  cell[0].setCellStyle(cellStyleHead);
        //            cell[0].setCellStyle(cs1);
        //            worksheet.addMergedRegion(CellRangeAddress.valueOf("E3:E4"));

        int count = 4;

        int len = finalList.size();
        if (finalList.size() > 0) {

            for (int k = 0; k < finalList.size(); k++) {

                java.util.Map subList = (java.util.Map) finalList.get(k);
                row1 = worksheet.createRow((short) count++);
                for (int j = 0; j < 4; j++) {
                    cell[j] = row1.createCell((short) j);
                }
                //System.out.println("subList-->"+subList);
                cell[0].setCellValue((String) subList.get("SNO"));
                cell[1].setCellValue((String) subList.get("contactName"));
                cell[2].setCellValue((String) subList.get("CompanyName"));
                cell[3].setCellValue((String) subList.get("email").toString());
                // cell[4].setCellValue((String) subList.get("phone").toString());

                for (int h = 0; h < 4; h++) {
                    if (count % 2 == 0) {
                        cell[h].setCellStyle(cs1);
                    } else {
                        cell[h].setCellStyle(cs);
                    }

                }
            }
            // 
            for (int i = 1; i < 4; i++) {

                worksheet.setColumnWidth(i, 40 * 256);

                //
            }
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();

        }

    } catch (FileNotFoundException fne) {

        fne.printStackTrace();
    } catch (IOException ioe) {

        ioe.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (preStmt != null) {
                preStmt.close();
                preStmt = null;
            }
            if (connection != null) {
                connection.close();
                connection = null;
            }
        } catch (Exception se) {
            se.printStackTrace();
        }
    }

    return filePath;

}