Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createFont

Introduction

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

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

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

License:Open Source License

private void exportEntitleKeyInfo() {
    try {/*from w  w w  .  j  av  a 2s .com*/
        // 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.alibaba.differ.biz.TableExporter.java

License:Open Source License

public void export() throws IOException {
    fc.setFileFilter(new ExcelFileFilter());
    fc.setFileHidingEnabled(true);/*from www  . ja va 2s.  c om*/
    fc.setAcceptAllFileFilterUsed(false);
    int returnValue = fc.showSaveDialog(null);
    if (returnValue != JFileChooser.APPROVE_OPTION) {
        return;
    }
    File file = fc.getSelectedFile();
    if (file.exists()) {
        JOptionPane.showMessageDialog(null, "");
        return;
    }
    FileOutputStream fos = new FileOutputStream(file + ".xls");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet hs = wb.createSheet();
    TableModel tm = table.getModel();
    int row = tm.getRowCount();
    int cloumn = tm.getColumnCount();
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 11);
    style.setFont(font);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style1.setFillForegroundColor(HSSFColor.ORANGE.index);
    style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font1 = wb.createFont();
    font1.setFontHeightInPoints((short) 15);
    font1.setBoldweight((short) 700);
    style1.setFont(font);

    for (int i = 0; i < row + 1; i++) {
        HSSFRow hr = hs.createRow(i);
        for (int j = 0; j < cloumn; j++) {
            if (i == 0) {
                String value = tm.getColumnName(j);
                hs.setColumnWidth(j, UIConfig.EXCEL_COLUMN_WIDTH);
                HSSFRichTextString srts = new HSSFRichTextString(value);
                HSSFCell hc = hr.createCell((short) j);
                hc.setCellStyle(style1);
                hc.setCellValue(srts);
            } else {
                if (tm.getValueAt(i - 1, j) != null) {
                    String value = tm.getValueAt(i - 1, j).toString();
                    HSSFRichTextString srts = new HSSFRichTextString(value);
                    HSSFCell hc = hr.createCell((short) j);
                    hc.setCellStyle(style);

                    if (value.equals("") || value == null) {
                        hc.setCellValue(new HSSFRichTextString(""));
                    } else {
                        hc.setCellValue(srts);
                    }
                }
            }
        }
    }
    wb.write(fos);
    fos.close();
    JOptionPane.showMessageDialog(null, "Excel");
}

From source file:com.app.ExcelView.java

License:Open Source License

/**
 * ?Excel/*from  ww  w  .java  2s.co m*/
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
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" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M"));
                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:com.bdx.rainbow.spsy.service.impl.EnterpriseLicenseServiceImpl.java

/**
 *
 * @param title ??// ww w  .  ja v  a  2s  .  com
 * @param headers ??
 * @param resultLists ?
 * @param pattern ?,"yyyy-MM-dd"
 */
private HSSFWorkbook exportExcel(String title, String[] headers, List<DubboEnterpriseLicense> resultLists,
        String pattern) {
    if (resultLists == null) {
        return null;
    }
    if (pattern == null && StringUtils.isEmpty(pattern)) {
        pattern = "yyyy-MM-dd";
    }
    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    // 
    HSSFWorkbook workbook = new HSSFWorkbook();
    // ?
    HSSFSheet sheet = workbook.createSheet(title);
    // 15
    sheet.setDefaultColumnWidth((short) 15);
    // ??
    HSSFCellStyle style = workbook.createCellStyle();
    // ?
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // ?
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // ??
    style.setFont(font);
    // ???
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // ??
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // ??
    style2.setFont(font2);
    // 
    HSSFRow row = sheet.createRow(0);
    for (short i = 0; i < headers.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(text);
    }
    // ?????
    for (int i = 0; i < resultLists.size(); i++) {
        row = sheet.createRow(i + 1);
        int index = 0;
        //                HSSFCell cell = row.createCell(j);
        //                row.createCell(index++).setCellStyle(style2);
        DubboEnterpriseLicense info = resultLists.get(i);
        row.createCell(index++).setCellValue(info.getEnterpriseName());
        row.createCell(index++).setCellValue(info.getOrganizationCode());
        row.createCell(index++).setCellValue(info.getLegalPerson());
        row.createCell(index++).setCellValue(info.getLegalPersonPhone());
        String type = info.getLicenseType();
        if ("001".equals(type)) {
            row.createCell(index++).setCellValue("");
        } else if ("002".equals(type)) {
            row.createCell(index++).setCellValue("??");
        } else if ("003".equals(type)) {
            row.createCell(index++).setCellValue("");
        } else if ("004".equals(type)) {
            row.createCell(index++).setCellValue("?");
        } else {
            row.createCell(index++).setCellValue(type);
        }

        row.createCell(index++).setCellValue(info.getLicenseCode());
        if (info.getLicenseTime() != null) {
            row.createCell(index++).setCellValue(sdf.format(info.getLicenseTime()));
        } else {
            row.createCell(index++).setCellValue("");
        }
        if (info.getInvalidDate() != null) {
            Long time = (System.currentTimeMillis() - info.getInvalidDate().getTime()) / (1000 * 3600 * 24);
            if (time > 0) {
                row.createCell(index++).setCellValue("");
            } else if (time <= 0) {
                row.createCell(index++).setCellValue("");
            } else {
                row.createCell(index++).setCellValue("1");
            }
        } else {
            row.createCell(index++).setCellValue("");
        }

    }
    return workbook;
}

From source file:com.beginner.core.utils.ObjectExcelView.java

License:Apache License

@SuppressWarnings("unchecked")
@Override/* w ww.j  av a2 s . c o  m*/
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Date date = new Date();
    String filename = DateUtil.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;
    HSSFCell cell;
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    sheet = workbook.createSheet("sheet1");

    List<String> titles = (List<String>) model.get("titles");
    int len = titles.size();
    HSSFCellStyle headerStyle = workbook.createCellStyle(); //?
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    HSSFFont headerFont = workbook.createFont(); //
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 11);
    headerStyle.setFont(headerFont);
    short width = 20, height = 25 * 20;
    sheet.setDefaultColumnWidth(width);
    for (int i = 0; i < len; i++) { //
        String title = titles.get(i);
        cell = getCell(sheet, 0, i);
        cell.setCellStyle(headerStyle);
        setText(cell, title);
    }
    sheet.getRow(0).setHeight(height);

    HSSFCellStyle contentStyle = workbook.createCellStyle(); //?
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    List<PageData> varList = (List<PageData>) model.get("varList");
    int varCount = varList.size();
    for (int i = 0; i < varCount; i++) {
        PageData vpd = varList.get(i);
        for (int j = 0; j < len; j++) {
            String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
            cell = getCell(sheet, i + 1, j);
            cell.setCellStyle(contentStyle);
            setText(cell, varstr);
        }
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

/**
 * Method to put style to header and normally cell
 * @param objLibro/*from   w w w.  jav  a 2  s.  c  om*/
 * @param isHeader 
 * @return
 */
private HSSFCellStyle styleCellNormally(HSSFWorkbook objLibro, boolean isHeader) {
    HSSFFont sourceStyle = objLibro.createFont();
    sourceStyle.setFontHeightInPoints((short) 11);
    sourceStyle.setBoldweight((short) 11);
    sourceStyle.setFontName(HSSFFont.FONT_ARIAL);
    if (isHeader) {
        sourceStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    }
    HSSFCellStyle styleCell = objLibro.createCellStyle();
    styleCell.setWrapText(true);
    styleCell.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);

    styleCell.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    styleCell.setFont(sourceStyle);
    return styleCell;
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

/**
 * Method that pint cells in different style of foreground
 * @param objBook// w w w.j  a  va  2  s.c o  m
 * @param foregroundColor
 * @return
 */
private HSSFCellStyle getStyleCeldSolidForeground(HSSFWorkbook objBook, short foregroundColor) {
    HSSFFont sourceStyle = objBook.createFont();
    sourceStyle.setFontHeightInPoints((short) 11);
    sourceStyle.setFontName(HSSFFont.FONT_ARIAL);
    HSSFCellStyle stileCell = objBook.createCellStyle();
    stileCell.setWrapText(true);
    stileCell.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
    stileCell.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    stileCell.setFont(sourceStyle);
    stileCell.setFillForegroundColor(foregroundColor);
    stileCell.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    return stileCell;
}

From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java

License:Open Source License

public void postProcessXls(Object document) {
    logger.trace("postProcessXls start document " + document);
    if (document != null) {
        HSSFWorkbook workBook = (HSSFWorkbook) document;
        HSSFSheet sheet = workBook.getSheetAt(0);

        HSSFRow headerRow = sheet.getRow(0);

        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            sheet.setColumnWidth(i, 30 * 265); // width for 40 characters
        }//from   w  ww . j a va2  s.  com

        sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n
                                                      // by 1 to get space
                                                      // for header
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3"));

        HSSFFont headerFont = workBook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headerCellStyle = workBook.createCellStyle();
        headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date()));

        HSSFCellStyle metaDataCellStyle = workBook.createCellStyle();
        metaDataCellStyle.setFont(headerFont);

        HSSFRow metaDataRow = sheet.getRow(3);
        if (metaDataRow == null) {
            metaDataRow = sheet.createRow(3);
        }
        HSSFCell metaDataKey = metaDataRow.createCell(0);
        metaDataKey.setCellStyle(metaDataCellStyle);
        metaDataKey.setCellValue("CATS Instance");

        HSSFCell metaDataValue = metaDataRow.createCell(1);
        metaDataValue.setCellStyle(metaDataCellStyle);
        metaDataValue.setCellValue(AuthController.getHostAddress());

        HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle();
        datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        datatTableHeaderCellStyle.setFont(headerFont);

        HSSFRow actualDataTableHeaderRow = sheet.getRow(5);
        for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = actualDataTableHeaderRow.getCell(i);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.replace("<br/> ", ""); // replace
                                                             // any line
                                                             // breaks
                cell.setCellValue(cellValue);
                cell.setCellStyle(datatTableHeaderCellStyle);
            }
        }

    }
    logger.trace("postProcessXls end");
}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {//from www  . j  a v a 2 s  .c o m

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}

From source file:com.creativity.controller.PesquisaFichasBean.java

public void posProcessarXls(Object documento) {
    HSSFWorkbook planilha = (HSSFWorkbook) documento;
    HSSFSheet folha = planilha.getSheetAt(0);
    HSSFRow cabecalho = folha.getRow(0);
    HSSFCellStyle estiloCelula = planilha.createCellStyle();
    HSSFFont fonteCabecalho = planilha.createFont();

    fonteCabecalho.setColor(IndexedColors.BLACK.getIndex());
    fonteCabecalho.setBold(true);/*from   w w  w . j  ava 2s  .  c o m*/
    fonteCabecalho.setFontHeightInPoints((short) 10);

    estiloCelula.setFont(fonteCabecalho);
    estiloCelula.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    estiloCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) {
        cabecalho.getCell(i).setCellStyle(estiloCelula);
    }
}