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:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeIndividualTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastUserName = null;//from  w ww  .  j  a v a2 s  .c  om
    List months = new ArrayList();
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        }
        HSSFCell cell = row.getCell((short) 2);
        if (cell != null) {
            if (lastUserName == null) {
                lastUserName = cell.getStringCellValue();
            } else {
                String newUserName = cell.getStringCellValue();
                if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) {
                    continue;
                }
                if ("Normal Working day of a Month".equals(newUserName.trim())) {
                    months = new ArrayList();
                    row = templateSheet.getRow(i - 1);
                    for (int j = 3; j < 1000; j++) {
                        HSSFCell monthCell = row.getCell((short) j);
                        if (monthCell == null) {
                            if (row.getCell((short) (j + 1)) == null) {
                                break;
                            }
                        } else {
                            String monthValue = monthCell.getStringCellValue();
                            if (monthValue != null && !monthValue.equals("")) {
                                months.add(monthCell.getStringCellValue());
                            }
                        }
                    }
                    continue;
                }
                if (newUserName != null) {
                    if (newUserName.equals(lastUserName)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) 2);
                        region.setColumnTo((short) 2);
                        templateSheet.addMergedRegion(region);
                        templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle()
                                .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                        HSSFCell activityCell = row.getCell((short) 3);
                        HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3);
                        if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) {
                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        }

                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                String prec = precOfType.getStringCellValue().trim();
                                String key = newUserName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                                if (!prec.equals("")) {
                                    if (timeUsage.getHours() > 0) {
                                        precOfType.setCellStyle(percentageStyle);
                                        precOfType.setCellValue(
                                                (new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                                    }
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 4));
                                region.setColumnTo((short) (j + 4));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getHours() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 4))
                                            .setCellValue(timeUsage.getHours());
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 5));
                                region.setColumnTo((short) (j + 5));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellStyle(percentageStyle);
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellValue(timeUsage.getPercentage());
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastUserName = newUserName;
                        writePrec(templateSheet, months, newUserName, userHours, row, i);
                    }
                } else {
                    lastUserName = newUserName;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrecOfSubActivity(HSSFSheet templateSheet, List months, String newUserName,
        Map userHours, HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }//from   w  ww.ja va  2  s . c  o  m
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            HSSFCell precOfSubActivity = row.getCell((short) (j + 3));
            try {
                double prec = precOfType.getNumericCellValue();
                String key = newUserName + "." + months.get(k);
                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                if (prec > 0) {
                    if (timeUsage.getHours() > 0) {
                        precOfSubActivity.setCellStyle(percentageStyle);
                        precOfSubActivity.setCellValue(prec);
                    }
                }
            } catch (Exception e) {
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrec(HSSFSheet templateSheet, List months, String newUserName, Map userHours,
        HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }//www  .j  av a 2 s  .  c  o m
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            String prec = precOfType.getStringCellValue().trim();
            String key = newUserName + "." + months.get(k);
            TimeUsage timeUsage = (TimeUsage) userHours.get(key);
            if (!prec.equals("")) {
                if (timeUsage.getHours() > 0) {
                    precOfType.setCellStyle(percentageStyle);
                    precOfType.setCellValue((new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                }
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.mario256.blog.ExcelView.java

License:Open Source License

/**
 * ?Excel/*from   w  w w . j  a v a  2  s . com*/
 * 
 * @param model
 *            ?
 * @param workbook
 *            HSSFWorkbook
 * @param request
 *            HttpServletRequest
 * @param response
 *            HttpServletResponse
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);

    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:co.com.codesoftware.logica.excel.ExcelLogica.java

public String generarExcel(String sql) {
    String rta;//from   w  w w .j  av a 2s  .  c o m
    try {
        try (ConexionJdbc conexion = ConexionJdbc.getInstance()) {
            ResultSetMetaData rsmt = null;
            Connection con = conexion.conexion();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            rsmt = rs.getMetaData();
            ArrayList<Map> nombres = null;
            HSSFRow fila = null;
            if (rsmt.getColumnCount() > 0) {
                fila = hoja.createRow(0);
            }
            for (int i = 1; i <= rsmt.getColumnCount(); i++) {
                HSSFCell celda = fila.createCell(i - 1);
                String nombreCol = rsmt.getColumnName(i);
                int tipoColumna = rsmt.getColumnType(i);
                Map<String, String> item = new HashMap();
                item.put("nombre", nombreCol);
                item.put("tipo", "" + tipoColumna);
                HSSFRichTextString texto = new HSSFRichTextString(nombreCol.toUpperCase());
                texto.applyFont(this.titulo);
                celda.setCellValue(texto);
                if (nombres == null) {
                    nombres = new ArrayList();
                }
                nombres.add(item);
            }
            int filas = 1;
            while (rs.next()) {
                fila = hoja.createRow(filas);
                int celdaNum = 0;
                for (Map columna : nombres) {
                    HSSFCell celda = fila.createCell(celdaNum);
                    if ("-5".equalsIgnoreCase((String) columna.get("tipo"))
                            || "4".equalsIgnoreCase((String) columna.get("tipo"))
                            || "2".equalsIgnoreCase((String) columna.get("tipo"))) {
                        Double valorCelda = rs.getDouble((String) columna.get("nombre"));
                        celda.setCellValue(valorCelda);
                    } else {
                        String valorCelda = rs.getString((String) columna.get("nombre"));
                        celda.setCellValue(valorCelda);
                    }
                    celdaNum++;
                }
                filas++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        FileOutputStream fos = new FileOutputStream(ruta);
        libro.write(fos);
        fos.close();
        rta = "Ok";
    } catch (Exception e) {
        e.printStackTrace();
        rta = "Error " + e;
    }
    return rta;
}

From source file:com.abacus.reports.ExcelBuilder.java

@Override
protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // get data model which is passed by the Spring container
    List headerlist = (List) map.get("header");
    List<Object[]> data = (List) map.get("data");
    String reportname = String.valueOf(map.get("report_name"));

    // create a new Excel sheet
    HSSFSheet sheet = workbook.createSheet(reportname);
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls");
    OutputStream outStream = response.getOutputStream();
    sheet.setDefaultColumnWidth(30);/* w w w.  j a va2  s .c om*/

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    HSSFPalette palette = workbook.getCustomPalette();
    HSSFColor color = palette.findSimilarColor(152, 35, 17);
    short paindex = color.getIndex();
    font.setFontName("Trebuchet MS");
    style.setFillForegroundColor(paindex);
    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);
    int row = 0;
    for (Object headerlist1 : headerlist) {

        header.createCell(row).setCellValue(String.valueOf(headerlist1));
        header.getCell(row).setCellStyle(style);
        row++;
    }

    CellStyle style2 = workbook.createCellStyle();
    HSSFFont font2 = workbook.createFont();
    font2.setFontName("Trebuchet MS");
    style2.setFont(font2);

    System.out.println("data.size(): " + data.size());
    int rownum = 1;
    // create data rows         
    for (int rowCount = 0; rowCount < data.size(); rowCount++) {
        HSSFRow aRow = sheet.createRow(rownum);
        Object[] value = data.get(rowCount);
        int col = 0;
        for (Object value1 : value) {
            HSSFCell cell = aRow.createCell(col++);
            cell.setCellStyle(style2);
            if (value1 instanceof java.lang.String)
                cell.setCellValue(String.valueOf(value1));
            if (value1 instanceof java.lang.Integer)
                cell.setCellValue(Integer.parseInt(String.valueOf(value1)));
            if (value1 instanceof java.lang.Boolean)
                cell.setCellValue(Integer.parseInt(String.valueOf(value1)));
            if (value1 instanceof java.lang.Double)
                cell.setCellValue(Double.parseDouble(String.valueOf(value1)));
            if (value1 instanceof java.lang.Float)
                cell.setCellValue(Float.parseFloat(String.valueOf(value1)));
        }
        rownum++;
    }
    workbook.write(outStream);
    outStream.close();
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//from   www .j  a v a2 s.  c om
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);
    if (!displayAll) {
        if (stage == 1) {
            HSSFRow fourthRow = sheet.createRow(2);
            fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
            fourthRow.getCell(0).setCellStyle(headerCellStyle);
        }
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));

    HSSFRow lastRow;
    double totalSold = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            String cost = row.getCell(3).getStringCellValue();
            if (cost != null && !cost.isEmpty()) {
                row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK);
                row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                row.getCell(3).setCellValue(Double.valueOf(cost));
                totalSold += Double.parseDouble(cost.replace(",", ""));
            }
        }
    }
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 4; i < 5; i++) {
                String cost1 = row.getCell(i).getStringCellValue();
                row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                row.getCell(i).setCellValue(Double.valueOf(cost1));
                //                    totalSold += Double.valueOf(row.getCell(3).getStringCellValue());

            }
        }
    }

    lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
    HSSFCell totalSumTextCell = lastRow.createCell(2);
    totalSumTextCell.setCellValue("Total Sales Amount: ");

    HSSFCell totalUnitsCell = lastRow.createCell(3);
    totalUnitsCell.setCellValue(totalSold);
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLSSalesReturn(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);/*from w ww .  j  a v  a  2s .  c  om*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES RETURN REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    if (stage == 1) {
        HSSFRow fourthRow = sheet.createRow(2);
        fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
        fourthRow.getCell(0).setCellStyle(headerCellStyle);
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));

    HSSFRow lastRow;
    double totalSold = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {

            String cost = row.getCell(4).getStringCellValue();
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK);
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            row.getCell(4).setCellValue(Double.valueOf(cost));
            totalSold += Double.valueOf(cost);

        }
    }
    lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
    HSSFCell totalSumTextCell = lastRow.createCell(3);
    totalSumTextCell.setCellValue("Total : ");

    HSSFCell totalUnitsCell = lastRow.createCell(4);
    totalUnitsCell.setCellValue(totalSold);
}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void exportEntitleKeyInfo() {
    try {/* w w  w.ja  v a  2s  .c  om*/
        // create a new file
        FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH);
        // create a new workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet s = wb.createSheet("Sheet1");
        // declare a row object reference
        HSSFRow r = null;
        // declare a cell object reference
        HSSFCell c = null;
        // row index
        int rowNum = 0;
        // cell count
        int cellcount = 0;
        if (NmsUtil.isHMForOEM()) {
            cellcount = 7;
        } else if (getIsInHomeDomain()) {
            cellcount = 8;
        } else if (NmsUtil.isHostedHMApplication()) {
            cellcount = 9;
        }
        if (cellcount == 0) {
            return;
        } else {
            for (int i = 0; i < cellcount; i++) {
                s.setColumnWidth(i, getColumnWidthByIndex(i) * 256);
            }
        }
        // create cell style
        HSSFCellStyle cs = wb.createCellStyle();
        // create font object
        HSSFFont f = wb.createFont();

        //set font to 12 point type
        f.setFontHeightInPoints((short) 12);
        f.setFontName("Calibri");

        //set cell stlye
        cs.setFont(f);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs1 = wb.createCellStyle();
        // create font object
        HSSFFont f1 = wb.createFont();

        //set font to 12 point type
        f1.setFontHeightInPoints((short) 12);
        f1.setFontName("Calibri");
        f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //set cell stlye
        cs1.setFont(f1);
        cs1.setAlignment(CellStyle.ALIGN_RIGHT);
        cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setFont(f1);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs3 = wb.createCellStyle();
        cs3.setFont(f);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs4 = wb.createCellStyle();
        cs4.setFont(f1);
        cs4.setAlignment(CellStyle.ALIGN_LEFT);
        cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs5 = wb.createCellStyle();
        // create font object
        HSSFFont f2 = wb.createFont();

        //set font to 12 point type
        f2.setFontHeightInPoints((short) 12);
        f2.setFontName("Calibri");
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setColor(Font.COLOR_RED);
        cs5.setFont(f2);
        cs5.setAlignment(CellStyle.ALIGN_LEFT);
        cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs6 = wb.createCellStyle();
        cs6.setFont(f);
        cs6.setAlignment(CellStyle.ALIGN_LEFT);
        cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create a row
        // row 1
        s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title"));
        c.setCellStyle(cs);

        // row 2
        String sysInfo = "";
        if (getIsInHomeDomain()) {
            sysInfo = "System ID: " + getSystemId();
            // HiveManager Online user
        } else if (NmsUtil.isHostedHMApplication()) {
            sysInfo = "VHM ID: " + getDomain().getVhmID();
        }
        s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(sysInfo);
        c.setCellStyle(cs);

        // row 3
        s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        List<?> userInfo = QueryUtil.executeQuery(
                "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null,
                new FilterParams("owner.domainName", getDomain().getDomainName()));
        if (!userInfo.isEmpty()) {
            Object[] userInfos = (Object[]) userInfo.get(0);
            c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company",
                    new String[] { (String) userInfos[0], (String) userInfos[1] }));
        }
        c.setCellStyle(cs);

        // row 4
        r = s.createRow(rowNum++);

        // row 5
        r = s.createRow(rowNum++);

        // row 6 cell 1
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed"));
        c.setCellStyle(cs1);

        // row 6 cell 2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getHiveAps());
        c.setCellStyle(cs2);

        // device management info
        Map<String, Integer> apcount = HiveAPInfoFromeDatabase
                .getManagedDeviceTypeAndNumber(getDomain().getDomainName());
        int vpnCount = 0;
        int totalCount = 0;
        if (null != apcount) {
            Object[] typeNames = apcount.keySet().toArray();
            Arrays.sort(typeNames);
            for (Object typeName : typeNames) {

                // VPN Gateway VA does not belong to device
                if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) {
                    vpnCount = apcount.get(typeName);
                } else {
                    r = s.createRow(rowNum++);
                    c = r.createCell(0);
                    c.setCellValue((String) typeName);
                    c.setCellStyle(cs3);

                    c = r.createCell(1);
                    c.setCellValue(apcount.get(typeName));
                    c.setCellStyle(cs);
                    totalCount += apcount.get(typeName);
                }
            }

        }

        // managed device total number cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total"));
        c.setCellStyle(cs1);

        // managed device total number cell2
        c = r.createCell(1);
        c.setCellValue(totalCount);
        c.setCellStyle(cs2);

        // blank row
        r = s.createRow(rowNum++);

        // licensed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed"));
        c.setCellStyle(cs1);

        // licensed VPN Gateway VA cell2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getCvgNumber());
        c.setCellStyle(cs2);

        // managed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total"));
        c.setCellStyle(cs1);

        // managed VPN Gateway VA cell 2
        c = r.createCell(1);
        c.setCellValue(vpnCount);
        c.setCellStyle(cs2);

        // entitlement key information
        preparePage();
        if (null != page && !page.isEmpty()) {
            r = s.createRow(rowNum++);
            List<OrderHistoryInfo> normalKey = new ArrayList<>();
            List<OrderHistoryInfo> invalidKey = new ArrayList<>();
            List<OrderHistoryInfo> expiredKey = new ArrayList<>();

            for (Object obj : page) {
                OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj;
                if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL
                        && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) {
                    normalKey.add(orderInfo);
                } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE
                        || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) {
                    invalidKey.add(orderInfo);
                } else {
                    expiredKey.add(orderInfo);
                }
            }

            if (!normalKey.isEmpty()) {

                // normal entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key"));

                // normal entitle key info
                for (OrderHistoryInfo keyInfo : normalKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!invalidKey.isEmpty()) {

                if (!normalKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // invalid entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title"));

                // invalid entitle key info
                for (OrderHistoryInfo keyInfo : invalidKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!expiredKey.isEmpty()) {

                if (!normalKey.isEmpty() || !invalidKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // expired entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title"));

                // expired entitle key info
                for (OrderHistoryInfo keyInfo : expiredKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }
        }

        // write the workbook to the output stream
        // close our file (don't blow out our file handles)
        wb.write(out);
        out.close();
        generateAuditLog(HmAuditLog.STATUS_SUCCESS,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
    } catch (Exception ex) {
        generateAuditLog(HmAuditLog.STATUS_FAILURE,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
        log.error("exportEntitleKeyInfo()", ex.getMessage());
    }
}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void setEntitlementKeyCellValue(int cellcount, HSSFCellStyle cs, HSSFCellStyle cs1, HSSFRow r,
        OrderHistoryInfo lsInfo, String keyTitle) {
    for (int i = 0; i < cellcount; i++) {
        HSSFCell c = r.createCell(i);
        c.setCellStyle(cs);//from   www . j  a  v a2s .  c  o  m
        switch (i) {
        case 0:
            c.setCellValue(null == lsInfo ? keyTitle : lsInfo.getOrderKey());
            break;
        case 1:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.type")
                    : lsInfo.getLicenseTypeStr());
            break;
        case 2:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.ap")
                    : String.valueOf(lsInfo.getNumberOfAps()));
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 3:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            }
            if (null != lsInfo) {
                if (!NmsUtil.isHostedHMApplication()) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                }
                if (!"N/A".equals(c.getStringCellValue())) {
                    c.setCellStyle(cs1);
                }
            }
            break;
        case 4:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.vhm")
                        : String.valueOf(lsInfo.getNumberOfVhms()));
            }
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 5:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getCvgSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 6:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 7:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 8:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                    : lsInfo.getActiveTimeStr());
            if (null != lsInfo) {
                c.setCellStyle(cs1);
            }
            break;
        default:
            break;
        }
    }
}