Example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. setCellValue

Introduction

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

Prototype

@SuppressWarnings("fallthrough")
public void setCellValue(boolean value)

Source Link

Document

set a boolean value for the cell

Usage

From source file:com.lushapp.common.excel.ExcelUtil.java

License:Apache License

/**
 * @param headList/* w w w .  ja va 2s  . c o  m*/
 *            ExcelHead?
 * @param fieldList
 *            ExcelField?
 * @param dataList
 *            Excel?
 * @throws HSSFWorkbook
 */
public static HSSFWorkbook createExcel(List<String> headList, List<String> fieldList,
        List<Map<String, Object>> dataList) 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.size(); i++) {

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

    for (int n = 0; n < dataList.size(); n++) {
        // 1?
        HSSFRow row_value = sheet.createRow(n + 1);
        Map<String, Object> dataMap = dataList.get(n);
        // ===============================================================
        for (int i = 0; i < fieldList.size(); i++) {

            // 0??
            HSSFCell cell = row_value.createCell(i);
            // ?
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // ?
            cell.setCellValue(objToString(dataMap.get(fieldList.get(i))));
        }
        // ===============================================================
    }
    return workbook;
}

From source file:com.lushapp.common.excel.ExcelUtil.java

License:Apache License

/**
 * /*w w w  .j  a v  a 2  s  . com*/
 * @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.mmj.app.common.file.ExcelUtils.java

License:Open Source License

/**
 * excelrow?T??title?T/*from   www.  ja  v  a2 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  va2s .  co  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 void decorateRowWithCell(short cellId, HSSFRow row, Object value, HSSFCellStyle cellStyle) {

    HSSFCell cell = row.createCell(cellId);
    if (value == null) {

    } else if (value instanceof Double) {
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(((Double) value).doubleValue());
    } else if (value instanceof Integer) {
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(((Integer) value).intValue());
    } else {//from w w w  .ja va  2 s .com
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(value.toString()));
    }

    if (cellStyle != null) {
        cell.setCellStyle(cellStyle);
    } else {
        cell.setCellStyle(genericStyle);
    }

}

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

License:Open Source License

private void writeRow(HSSFRow row, List<String> list) {
    short column = 0;
    for (String item : list) {
        HSSFCell cell = row.createCell(column++);
        cell.setCellValue(new HSSFRichTextString((item == null) ? "" : item));
    }//from  ww  w. java  2 s  .  co  m
}

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 ww  w  . j a  v 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 ww  w .  j  av a2 s  .c o m

    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.nkapps.billing.services.BankStatementPrintServiceImpl.java

public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, String value) {
    HSSFCell cell;
    cell = row.createCell(cellCurrent);//from ww w  .  jav a2  s .  co  m
    if (value == null) {
        cell.setCellValue("");
    } else {
        cell.setCellValue(value);
    }
    cell.setCellStyle(style[cellCurrent++]);
    return true;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, Short value) {
    HSSFCell cell;
    cell = row.createCell(cellCurrent);//  w  ww . j av  a2 s .  co  m
    if (value == null) {
        cell.setCellValue("");
    } else {
        cell.setCellValue(value);
    }
    cell.setCellStyle(style[cellCurrent++]);
    return true;
}