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:is.idega.idegaweb.egov.cases.business.CasesWriterExtended.java

License:Open Source License

@Override
public MemoryFileBuffer writeXLS(IWContext iwc, Collection cases) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet sheet = workbook.createSheet(StringHandler
            .shortenToLength(iwrb.getLocalizedString("cases_fetcher.statistics", "Statistics"), 30));
    sheet.setColumnWidth((short) 0, (short) (38 * 256));
    sheet.setColumnWidth((short) 1, (short) (85 * 256));

    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);

    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);//from ww  w  .j a  va2s .c o m
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    style2.setWrapText(true);
    HSSFCellStyle style3 = workbook.createCellStyle();
    style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style3.setFont(font);

    User currentUser = iwc.getCurrentUser();

    int cellRow = 0;
    Iterator iter = cases.iterator();
    while (iter.hasNext()) {
        GeneralCase element = (GeneralCase) iter.next();
        CaseCategory category = element.getCaseCategory();
        Group handlerGroup = category.getHandlerGroup();
        if (!currentUser.hasRelationTo(handlerGroup)) {
            continue;
        }
        CaseType type = element.getCaseType();
        CaseStatus status = element.getCaseStatus();
        if (status.equals(getBusiness(iwc).getCaseStatusDeleted())) {
            continue;
        }
        User user = element.getOwner();
        IWTimestamp created = new IWTimestamp(element.getCreated());

        HSSFRow row = sheet.createRow(cellRow++);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("cases_fetcher.case_id", "Case ID"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(element.getPrimaryKey().toString());

        row = sheet.createRow(cellRow++);

        cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("created_date", "Created date"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(created.getLocaleDateAndTime(locale, IWTimestamp.SHORT, IWTimestamp.SHORT));

        if (user != null) {
            row = sheet.createRow(cellRow++);

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
            cell.setCellStyle(style);

            Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName());
            cell = row.createCell((short) 1);
            cell.setCellValue(name.getName(locale));

            row = sheet.createRow(cellRow++);

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID"));
            cell.setCellStyle(style);

            cell = row.createCell((short) 1);
            cell.setCellValue(PersonalIDFormatter.format(user.getPersonalID(), locale));
        }

        row = sheet.createRow(cellRow++);

        cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("case_category", "Case category"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(category.getLocalizedCategoryName(locale));

        if (getBusiness(iwc).useTypes()) {
            row = sheet.createRow(cellRow++);

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("case_type", "Case type"));
            cell.setCellStyle(style);

            cell = row.createCell((short) 1);
            cell.setCellValue(type.getName());
        }

        if (element.getReference() != null) {
            row = sheet.createRow(cellRow++);

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("reference", "Reference"));
            cell.setCellStyle(style);

            cell = row.createCell((short) 1);
            cell.setCellValue(element.getReference());
        }

        row = sheet.createRow(cellRow++);

        cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("status", "Status"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(getBusiness(iwc).getLocalizedCaseStatusDescription(element, status, locale));

        row = sheet.createRow(cellRow++);

        cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("regarding", "Regarding"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(element.getSubject() != null ? element.getSubject() : "-");

        row = sheet.createRow(cellRow++);
        row = sheet.createRow(cellRow++);

        cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("message", "Message"));
        cell.setCellStyle(style3);

        cell = row.createCell((short) 1);
        cell.setCellValue(element.getMessage());
        cell.setCellStyle(style2);

        if (element.getReply() != null) {
            row = sheet.createRow(cellRow++);

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("reply", "Reply"));
            cell.setCellStyle(style3);

            cell = row.createCell((short) 1);
            cell.setCellValue(element.getReply());
            cell.setCellStyle(style2);
        }
    }

    workbook.write(mos);

    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.egov.course.business.CourseAttendanceWriter.java

License:Open Source License

public MemoryFileBuffer writeXLS(IWContext iwc, Collection choices) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30));
    sheet.setColumnWidth((short) 0, (short) (30 * 256));
    sheet.setColumnWidth((short) 1, (short) (14 * 256));
    sheet.setColumnWidth((short) 2, (short) (10 * 256));
    sheet.setColumnWidth((short) 3, (short) (10 * 256));
    sheet.setColumnWidth((short) 4, (short) (10 * 256));
    sheet.setColumnWidth((short) 5, (short) (14 * 256));
    sheet.setColumnWidth((short) 6, (short) (14 * 256));
    sheet.setColumnWidth((short) 7, (short) (30 * 256));
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);/*from  w  w  w  . j  a v a 2  s  .co  m*/

    HSSFFont bigFont = wb.createFont();
    bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    bigFont.setFontHeightInPoints((short) 13);
    HSSFCellStyle bigStyle = wb.createCellStyle();
    bigStyle.setFont(bigFont);

    int cellRow = 0;
    HSSFRow row = sheet.createRow(cellRow++);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue(this.courseName);
    cell.setCellStyle(bigStyle);
    cell = row.createCell((short) 1);

    row = sheet.createRow(cellRow++);

    short iCell = 0;
    row = sheet.createRow(cellRow++);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("pre_care", "Pre care"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("post_care", "Post care"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("picked_up", "Picked up"));
    cell.setCellStyle(style);

    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("child.growth_deviation", "Growth deviation"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("child.allergies", "Allergies"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("child.other_information", "Other information"));
    cell.setCellStyle(style);

    User user;
    User owner;
    CourseChoice choice;
    CourseApplication application;

    Iterator iter = choices.iterator();
    while (iter.hasNext()) {
        row = sheet.createRow(cellRow++);
        choice = (CourseChoice) iter.next();
        application = choice.getApplication();
        owner = application.getOwner();
        user = choice.getUser();
        Child child = this.userBusiness.getMemberFamilyLogic().getChild(user);
        boolean preCare = choice.getDayCare() == CourseConstants.DAY_CARE_PRE
                || choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST;
        boolean postCare = choice.getDayCare() == CourseConstants.DAY_CARE_POST
                || choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST;

        Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName());
        row.createCell((short) 0).setCellValue(name.getName(this.locale, true));
        row.createCell((short) 1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale));
        row.createCell((short) 2).setCellValue(
                preCare ? iwrb.getLocalizedString("yes", "Yes") : iwrb.getLocalizedString("no", "No"));
        row.createCell((short) 3).setCellValue(
                postCare ? iwrb.getLocalizedString("yes", "Yes") : iwrb.getLocalizedString("no", "No"));
        row.createCell((short) 4).setCellValue(choice.isPickedUp() ? iwrb.getLocalizedString("yes", "Yes")
                : iwrb.getLocalizedString("no", "No"));

        Boolean hasGrowthDeviation = child
                .hasGrowthDeviation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
        if (hasGrowthDeviation == null) {
            hasGrowthDeviation = child.hasGrowthDeviation(CourseConstants.COURSE_PREFIX);
        }
        if (hasGrowthDeviation != null && hasGrowthDeviation.booleanValue()) {
            row.createCell((short) 5).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
        } else {
            row.createCell((short) 5).setCellValue(this.iwrb.getLocalizedString("no", "No"));
        }

        Boolean hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
        if (hasAllergies == null) {
            hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX);
        }
        if (hasAllergies != null && hasAllergies.booleanValue()) {
            row.createCell((short) 6).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
        } else {
            row.createCell((short) 6).setCellValue(this.iwrb.getLocalizedString("no", "No"));
        }

        if (child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()) != null) {
            row.createCell((short) 7).setCellValue(
                    child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()));
        } else if (child.getOtherInformation(CourseConstants.COURSE_PREFIX) != null) {
            row.createCell((short) 7).setCellValue(child.getOtherInformation(CourseConstants.COURSE_PREFIX));
        }
    }
    wb.write(mos);

    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.egov.course.business.CourseParticipantsWriter.java

License:Open Source License

public MemoryFileBuffer writeXLS(IWContext iwc, Collection choices) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30));
    sheet.setColumnWidth(0, (30 * 256));
    sheet.setColumnWidth(1, (14 * 256));
    sheet.setColumnWidth(2, (30 * 256));
    sheet.setColumnWidth(3, (14 * 256));
    sheet.setColumnWidth(4, (14 * 256));
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);/*from   w  w  w  . j  a va 2s . c  o  m*/

    HSSFFont bigFont = wb.createFont();
    bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    bigFont.setFontHeightInPoints((short) 13);
    HSSFCellStyle bigStyle = wb.createCellStyle();
    bigStyle.setFont(bigFont);

    int cellRow = 0;
    HSSFRow row = sheet.createRow(cellRow++);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(this.courseName);
    cell.setCellStyle(bigStyle);

    row = sheet.createRow(cellRow++);

    boolean showAll = iwc.getApplicationSettings().getBoolean(CourseConstants.PROPERTY_USE_BIRTHYEARS, true);
    if (showAll) {
        row = sheet.createRow(cellRow++);
        cell = row.createCell(0);
        cell.setCellValue(this.iwrb.getLocalizedString("participant", "Participant"));
        cell.setCellStyle(bigStyle);
        cell = row.createCell(13);
        cell.setCellValue(this.iwrb.getLocalizedString("custodians", "Custodians"));
        cell.setCellStyle(bigStyle);
        cell = row.createCell(43);
        cell.setCellValue(this.iwrb.getLocalizedString("relatives", "Relatives"));
        cell.setCellStyle(bigStyle);
    }

    int iCell = 0;
    row = sheet.createRow(cellRow++);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("address", "Address"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("postal_code", "Postal code"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone"));
    cell.setCellStyle(style);

    if (showAll) {
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("child_care.growth_deviation", "Growth deviation"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("child_care.allergies", "Allergies"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("child.other_information", "Other information"));
        cell.setCellStyle(style);

        /* XXX Picked up */
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("picked_up", "Picked up"));
        cell.setCellStyle(style);

        /* XXX Pre-care and post-care*/
        String cellValue = this.iwrb.getLocalizedString("pre_care", "Has pre care");
        cellValue = cellValue + CoreConstants.SLASH;
        cellValue = cellValue + this.iwrb.getLocalizedString("post_care", "Has post care");
        cell = row.createCell(iCell++);
        cell.setCellValue(cellValue);
        cell.setCellStyle(style);

        for (int a = 1; a <= 3; a++) {
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("relation", "Relation"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("address", "Address"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("zip_code", "Zip code"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("marital_status", "Marital status"));
            cell.setCellStyle(style);
        }
    } else {
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("register_date", "Register date"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("application.payer_personal_id", "Payer personal ID"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("application.payer_name", "Payer name"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("application.reference_number", "Reference number"));
        cell.setCellStyle(style);
    }

    if (showAll) {
        for (int a = 1; a <= 2; a++) {
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("relation", "Relation"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone"));
            cell.setCellStyle(style);
            cell = row.createCell(iCell++);
            cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail"));
            cell.setCellStyle(style);
        }
    }

    User user;
    User owner;
    Address address;
    PostalCode postalCode = null;
    Phone phone;
    CourseChoice choice;
    CourseApplication application;

    Iterator iter = choices.iterator();
    while (iter.hasNext()) {
        row = sheet.createRow(cellRow++);
        choice = (CourseChoice) iter.next();
        application = choice.getApplication();
        user = choice.getUser();
        owner = application.getOwner();
        Child child = this.userBusiness.getMemberFamilyLogic().getChild(user);
        address = this.userBusiness.getUsersMainAddress(user);
        if (address != null) {
            postalCode = address.getPostalCode();
        }
        phone = this.userBusiness.getChildHomePhone(user);

        Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName());
        row.createCell(0).setCellValue(name.getName(this.locale, true));
        row.createCell(1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale));
        if (address != null) {
            row.createCell(2).setCellValue(address.getStreetAddress());
            if (postalCode != null) {
                row.createCell(3).setCellValue(postalCode.getPostalAddress());
            }
        }
        if (phone != null) {
            row.createCell(4).setCellValue(phone.getNumber());
        }

        if (showAll) {
            Boolean hasGrowthDeviation = child
                    .hasGrowthDeviation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
            if (hasGrowthDeviation == null) {
                hasGrowthDeviation = child.hasGrowthDeviation(CourseConstants.COURSE_PREFIX);
            }
            if (hasGrowthDeviation != null && hasGrowthDeviation.booleanValue()) {
                row.createCell(5).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
            } else {
                row.createCell(5).setCellValue(this.iwrb.getLocalizedString("no", "No"));
            }

            Boolean hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
            if (hasAllergies == null) {
                hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX);
            }
            if (hasAllergies != null && hasAllergies.booleanValue()) {
                row.createCell(6).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
            } else {
                row.createCell(6).setCellValue(this.iwrb.getLocalizedString("no", "No"));
            }

            if (child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()) != null) {
                row.createCell(7).setCellValue(
                        child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()));
            } else if (child.getOtherInformation(CourseConstants.COURSE_PREFIX) != null) {
                row.createCell(7).setCellValue(child.getOtherInformation(CourseConstants.COURSE_PREFIX));
            }

            /* Picked up */
            if (choice.isPickedUp()) {
                row.createCell(8).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
            } else {
                row.createCell(8).setCellValue(this.iwrb.getLocalizedString("no", "No"));
            }

            /* Pre-care and post-care*/
            int dayCare = choice.getDayCare();
            if (dayCare > 0) {
                row.createCell(9).setCellValue(this.iwrb.getLocalizedString("yes", "Yes"));
            } else {
                row.createCell(9).setCellValue(this.iwrb.getLocalizedString("no", "No"));
            }

            iCell = 10;

            Collection custodians = new ArrayList();
            try {
                custodians = child.getCustodians();
            } catch (Exception e) {
            }
            Custodian extraCustodian = child.getExtraCustodian();
            if (extraCustodian != null) {
                custodians.add(extraCustodian);
            }

            Iterator iterator = custodians.iterator();
            while (iterator.hasNext()) {
                Custodian element = (Custodian) iterator.next();
                address = this.userBusiness.getUsersMainAddress(element);
                Phone work = null;
                Phone mobile = null;
                Email email = null;
                String relation = this.iwrb.getLocalizedString("relation." + child.getRelation(element),
                        "relation." + child.getRelation(element));
                String maritalStatus = this.iwrb.getLocalizedString(
                        "marital_status." + element.getMaritalStatus(),
                        "marital_status." + element.getMaritalStatus());

                try {
                    phone = this.userBusiness.getUsersHomePhone(element);
                } catch (NoPhoneFoundException npfe) {
                    phone = null;
                }

                try {
                    work = this.userBusiness.getUsersWorkPhone(element);
                } catch (NoPhoneFoundException npfe) {
                    work = null;
                }

                try {
                    mobile = this.userBusiness.getUsersMobilePhone(element);
                } catch (NoPhoneFoundException npfe) {
                    mobile = null;
                }

                try {
                    email = this.userBusiness.getUsersMainEmail(element);
                } catch (NoEmailFoundException nefe) {
                    email = null;
                }

                name = new Name(element.getFirstName(), element.getMiddleName(), element.getLastName());
                row.createCell(iCell++).setCellValue(relation);
                row.createCell(iCell++).setCellValue(name.getName(this.locale, true));
                row.createCell(iCell++)
                        .setCellValue(PersonalIDFormatter.format(element.getPersonalID(), this.locale));
                if (address != null) {
                    row.createCell(iCell++).setCellValue(address.getStreetAddress());
                    if (postalCode != null) {
                        row.createCell(iCell++).setCellValue(postalCode.getPostalAddress());
                    } else {
                        iCell++;
                    }
                } else {
                    iCell++;
                }
                if (phone != null) {
                    row.createCell(iCell++).setCellValue(phone.getNumber());
                } else {
                    iCell++;
                }
                if (work != null) {
                    row.createCell(iCell++).setCellValue(work.getNumber());
                } else {
                    iCell++;
                }
                if (mobile != null) {
                    row.createCell(iCell++).setCellValue(mobile.getNumber());
                } else {
                    iCell++;
                }
                if (email != null) {
                    row.createCell(iCell++).setCellValue(email.getEmailAddress());
                } else {
                    iCell++;
                }
                if (maritalStatus != null) {
                    row.createCell(iCell++).setCellValue(maritalStatus);
                } else {
                    iCell++;
                }
            }

            iCell = 40;

            List relatives = new ArrayList();
            Relative mainRelative = child
                    .getMainRelative(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
            if (mainRelative == null) {
                mainRelative = child.getMainRelative(CourseConstants.COURSE_PREFIX);
            }
            if (mainRelative != null) {
                relatives.add(mainRelative);
            }
            Collection otherRelatives = child
                    .getRelatives(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey());
            if (otherRelatives.isEmpty()) {
                otherRelatives = child.getRelatives(CourseConstants.COURSE_PREFIX);
            }
            relatives.addAll(otherRelatives);
            iterator = relatives.iterator();
            while (iterator.hasNext()) {
                Relative element = (Relative) iterator.next();
                String relation = this.iwrb.getLocalizedString("relation." + element.getRelation(),
                        "relation." + element.getRelation());

                row.createCell(iCell++).setCellValue(relation);
                row.createCell(iCell++).setCellValue(element.getName());
                row.createCell(iCell++).setCellValue(element.getHomePhone());
                row.createCell(iCell++).setCellValue(element.getWorkPhone());
                row.createCell(iCell++).setCellValue(element.getMobilePhone());
                row.createCell(iCell++).setCellValue(element.getEmail());
            }
        } else {
            iCell = 5;
            Phone work = null;
            Phone mobile = null;
            Email email = null;

            String payerName = null;
            String payerPersonalID = null;
            if (application.getPayerPersonalID() != null) {
                payerPersonalID = PersonalIDFormatter.format(application.getPayerPersonalID(), locale);
                payerName = application.getPayerName();
            } else {
                User payer = application.getOwner();
                payerName = new Name(payer.getFirstName(), payer.getMiddleName(), payer.getLastName())
                        .getName(locale);
                payerPersonalID = PersonalIDFormatter.format(payer.getPersonalID(), locale);
            }

            try {
                work = this.userBusiness.getUsersWorkPhone(child);
            } catch (NoPhoneFoundException npfe) {
                work = null;
            }

            try {
                mobile = this.userBusiness.getUsersMobilePhone(child);
            } catch (NoPhoneFoundException npfe) {
                mobile = null;
            }

            try {
                email = this.userBusiness.getUsersMainEmail(child);
            } catch (NoEmailFoundException nefe) {
                email = null;
            }

            if (work != null) {
                row.createCell(iCell++).setCellValue(work.getNumber());
            } else {
                iCell++;
            }
            if (mobile != null) {
                row.createCell(iCell++).setCellValue(mobile.getNumber());
            } else {
                iCell++;
            }
            if (email != null) {
                row.createCell(iCell++).setCellValue(email.getEmailAddress());
            } else {
                iCell++;
            }
            row.createCell(iCell++).setCellValue(
                    new IWTimestamp(application.getCreated()).getLocaleDate(locale, IWTimestamp.SHORT));
            row.createCell(iCell++).setCellValue(payerPersonalID);
            row.createCell(iCell++).setCellValue(payerName);
            if (application.getReferenceNumber() != null) {
                row.createCell(iCell++).setCellValue(application.getReferenceNumber());
            } else {
                iCell++;
            }
        }
    }
    wb.write(mos);
    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.egov.course.business.CourseParticipantsWriter.java

License:Open Source License

public MemoryFileBuffer writeAccountingXLS(IWContext iwc, Collection choices) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30));
    sheet.setColumnWidth(0, (30 * 256));
    sheet.setColumnWidth(1, (14 * 256));
    sheet.setColumnWidth(2, (30 * 256));
    sheet.setColumnWidth(3, (14 * 256));
    sheet.setColumnWidth(4, (14 * 256));
    sheet.setColumnWidth(4, (10 * 256));
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);/*  ww w.jav  a 2  s  . c om*/

    HSSFFont bigFont = wb.createFont();
    bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    bigFont.setFontHeightInPoints((short) 13);
    HSSFCellStyle bigStyle = wb.createCellStyle();
    bigStyle.setFont(bigFont);

    int cellRow = 0;
    HSSFRow row = sheet.createRow(cellRow++);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(this.courseName);
    cell.setCellStyle(bigStyle);

    row = sheet.createRow(cellRow++);

    int iCell = 0;
    row = sheet.createRow(cellRow++);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("name", "Name"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("address", "Address"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("postal_code", "Postal code"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("price", "Price"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("payer_personal_id", "Payer personal ID"));
    cell.setCellStyle(style);
    cell = row.createCell(iCell++);
    cell.setCellValue(this.iwrb.getLocalizedString("payer_name", "Payer name"));
    cell.setCellStyle(style);

    User user;
    Address address;
    PostalCode postalCode = null;
    Phone phone;
    CourseChoice choice;
    CourseApplication application;

    Iterator iter = choices.iterator();
    while (iter.hasNext()) {
        row = sheet.createRow(cellRow++);
        choice = (CourseChoice) iter.next();
        application = choice.getApplication();
        user = choice.getUser();
        address = this.userBusiness.getUsersMainAddress(user);
        if (address != null) {
            postalCode = address.getPostalCode();
        }
        phone = this.userBusiness.getChildHomePhone(user);
        Course course = choice.getCourse();
        User owner = application.getOwner();
        if (application.getPayerPersonalID() != null) {
            User payer = getUserBusiness(iwc).getUser(application.getPayerPersonalID());
            if (payer != null) {
                owner = payer;
            }
        }

        application = choice.getApplication();
        float userPrice = 0;
        if (choice.isNoPayment()) {
            userPrice = 0;
        } else {
            Map applicationMap = getCourseBusiness(iwc).getApplicationMap(application, new Boolean(false));
            SortedSet prices = getCourseBusiness(iwc).calculatePrices(applicationMap);
            Map discounts = getCourseBusiness(iwc).getDiscounts(prices, applicationMap);
            CoursePrice price = course.getPrice();

            float coursePrice = (price != null ? price.getPrice() : course.getCoursePrice())
                    * (1 - ((PriceHolder) discounts.get(user)).getDiscount());

            float carePrice = 0;
            if (choice.getDayCare() == CourseConstants.DAY_CARE_PRE) {
                carePrice = price.getPreCarePrice();
            } else if (choice.getDayCare() == CourseConstants.DAY_CARE_POST) {
                carePrice = price.getPostCarePrice();
            } else if (choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST) {
                carePrice = price.getPreCarePrice() + price.getPostCarePrice();
            }
            carePrice = carePrice * (1 - ((PriceHolder) discounts.get(user)).getDiscount());

            userPrice = carePrice + coursePrice;
        }

        Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName());
        row.createCell(0).setCellValue(name.getName(this.locale, true));
        row.createCell(1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale));
        if (address != null) {
            row.createCell(2).setCellValue(address.getStreetAddress());
            if (postalCode != null) {
                row.createCell(3).setCellValue(postalCode.getPostalAddress());
            }
        }
        if (phone != null) {
            row.createCell(4).setCellValue(phone.getNumber());
        }
        row.createCell(5).setCellValue(userPrice);

        if (owner != null) {
            row.createCell(6).setCellValue(owner.getPersonalID());
            row.createCell(7)
                    .setCellValue(new Name(user.getFirstName(), user.getMiddleName(), user.getLastName())
                            .getName(this.locale, true));
        }
    }
    wb.write(mos);
    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.egov.course.business.CourseWriter.java

License:Open Source License

public MemoryFileBuffer writeXLS(IWContext iwc, Collection courses) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);
    if (!courses.isEmpty()) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.schoolTypeName, 30));
        sheet.setColumnWidth((short) 0, (short) (24 * 256));
        sheet.setColumnWidth((short) 1, (short) (24 * 256));
        sheet.setColumnWidth((short) 2, (short) (8 * 256));
        sheet.setColumnWidth((short) 3, (short) (8 * 256));
        sheet.setColumnWidth((short) 4, (short) (12 * 256));
        sheet.setColumnWidth((short) 5, (short) (12 * 256));
        sheet.setColumnWidth((short) 6, (short) (8 * 256));
        sheet.setColumnWidth((short) 7, (short) (8 * 256));
        sheet.setColumnWidth((short) 8, (short) (20 * 256));
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 12);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);/* ww  w  . j  ava2 s  . c o m*/

        HSSFFont bigFont = wb.createFont();
        bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        bigFont.setFontHeightInPoints((short) 13);
        HSSFCellStyle bigStyle = wb.createCellStyle();
        bigStyle.setFont(bigFont);

        int cellRow = 0;
        HSSFRow row = sheet.createRow(cellRow++);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(this.schoolTypeName);
        cell.setCellStyle(bigStyle);
        cell = row.createCell((short) 1);

        row = sheet.createRow(cellRow++);

        short iCell = 0;
        row = sheet.createRow(cellRow++);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("type", "Type"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("course", "Course"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("from", "From"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("to", "To"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("date_from", "Date from"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("date_to", "Date to"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("max", "Max"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("free_places", "Free places"));
        cell.setCellStyle(style);
        cell = row.createCell(iCell++);
        cell.setCellValue(this.iwrb.getLocalizedString("employee", "Employee"));
        cell.setCellStyle(style);

        Iterator iter = courses.iterator();
        while (iter.hasNext()) {
            row = sheet.createRow(cellRow++);
            iCell = 0;

            Course course = (Course) iter.next();
            CourseType type = course.getCourseType();
            CoursePrice price = course.getPrice();
            IWTimestamp dateFrom = new IWTimestamp(course.getStartDate());
            IWTimestamp dateTo = new IWTimestamp(course.getStartDate());
            dateTo.addDays(price.getNumberOfDays());

            row.createCell(iCell++).setCellValue(type.getName());
            row.createCell(iCell++).setCellValue(course.getName());
            row.createCell(iCell++).setCellValue(String.valueOf(course.getBirthyearFrom()));
            row.createCell(iCell++).setCellValue(String.valueOf(course.getBirthyearTo()));
            row.createCell(iCell++)
                    .setCellValue(dateFrom.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT));
            row.createCell(iCell++)
                    .setCellValue(dateTo.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT));
            row.createCell(iCell++).setCellValue(String.valueOf(course.getMax()));
            row.createCell(iCell++).setCellValue(String.valueOf(business.getNumberOfFreePlaces(course)));
            row.createCell(iCell++).setCellValue("-");
        }
        wb.write(mos);
    }
    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.msi.business.RaceReportWriter.java

License:Open Source License

private HSSFCellStyle getHeaderStyle(HSSFWorkbook wb) {
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 12);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFont(headerFont);//from   w w w  . j av a  2  s  . com
    return headerStyle;
}

From source file:is.idega.idegaweb.msi.business.RaceReportWriter.java

License:Open Source License

private HSSFCellStyle getNormalStyle(HSSFWorkbook wb) {
    HSSFFont normalFont = wb.createFont();
    normalFont.setFontHeightInPoints((short) 12);
    HSSFCellStyle normalStyle = wb.createCellStyle();
    normalStyle.setFont(normalFont);// ww w .j  a va  2 s  . co m
    return normalStyle;
}

From source file:kr.co.blackducksoftware.rg.displayexcel.Style.java

public static void setFonts(HSSFWorkbook myWorkbook) {

    /**/*from  w w w  .j  a  va  2 s .c  o  m*/
     * written by byunghoon
     * ossw_cover font ?
     */
    osswCoverFont = myWorkbook.createFont();
    osswCoverFont.setFontName("Arial");
    osswCoverFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    osswCoverFont.setColor(HSSFColor.BLACK.index);
    osswCoverFont.setFontHeightInPoints((short) 20);

    /**
     * written by byunghoon
     * ossw_Summary font ?
     */
    osswSummaryFont_1 = myWorkbook.createFont();
    osswSummaryFont_1.setFontName("Arial");
    osswSummaryFont_1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    osswSummaryFont_1.setColor(HSSFColor.BLACK.index);
    osswSummaryFont_1.setFontHeightInPoints((short) 15);

    osswSummaryFont_2 = myWorkbook.createFont();
    osswSummaryFont_2.setFontName("Arial");
    osswSummaryFont_2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    osswSummaryFont_2.setColor(HSSFColor.BLACK.index);
    osswSummaryFont_2.setFontHeightInPoints((short) 12);

    /**
     * written by byunghoon
     * ossw_BOM font ?
     */
    osswBOMFont_1 = myWorkbook.createFont();
    osswBOMFont_1.setFontName("Arial");
    osswBOMFont_1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    osswBOMFont_1.setColor(HSSFColor.BLACK.index);
    osswBOMFont_1.setFontHeightInPoints((short) 15);

    osswBOMFont_2 = myWorkbook.createFont();
    osswBOMFont_2.setFontName("Arial");
    osswBOMFont_2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    osswBOMFont_2.setColor(HSSFColor.BLACK.index);
    osswBOMFont_2.setFontHeightInPoints((short) 12);

    /**
     * Set the font for my styles
     */
    blackFont = myWorkbook.createFont();
    // blackFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    blackFont.setFontName("???? ???");
    blackFont.setColor(HSSFColor.BLACK.index);
    blackFont.setFontHeightInPoints((short) 10);

    analFont = myWorkbook.createFont();
    analFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    analFont.setFontName("HY??????M");
    analFont.setColor(HSSFColor.BLACK.index);
    analFont.setFontHeightInPoints((short) 13);

    summaryFont = myWorkbook.createFont();
    summaryFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    summaryFont.setFontName("Arial");
    summaryFont.setColor(HSSFColor.BLACK.index);
    summaryFont.setFontHeightInPoints((short) 10);

    whiteFont = myWorkbook.createFont();
    whiteFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    whiteFont.setFontName("???? ???");
    whiteFont.setColor(HSSFColor.WHITE.index);
    whiteFont.setFontHeightInPoints((short) 10);

    mainHeaderFont = myWorkbook.createFont();
    mainHeaderFont.setFontName("???? ???");
    mainHeaderFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    mainHeaderFont.setColor(HSSFColor.BLUE.index);
    mainHeaderFont.setFontHeightInPoints((short) 14);

    linkFont = myWorkbook.createFont();
    linkFont.setItalic(true);
    linkFont.setFontName("Calibri");
    linkFont.setColor(HSSFColor.WHITE.index);
    linkFont.setFontHeightInPoints((short) 10);

    exampleFont = myWorkbook.createFont();
    // exampleFont.setItalic(true);
    exampleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    exampleFont.setFontName("???? ???");
    exampleFont.setColor(HSSFColor.BLUE.index);
    exampleFont.setFontHeightInPoints((short) 16);

    projectInformationFont = myWorkbook.createFont();
    // projectInformationFont.setItalic(true);
    projectInformationFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    projectInformationFont.setFontName("???? ???");
    projectInformationFont.setColor(HSSFColor.BLACK.index);
    projectInformationFont.setFontHeightInPoints((short) 14);

    greyFont = myWorkbook.createFont();
    // greyFont.setItalic(true);
    greyFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    greyFont.setFontName("???? ???");
    greyFont.setColor(HSSFColor.BLACK.index);
    greyFont.setFontHeightInPoints((short) 11);

}

From source file:mat.server.service.impl.XLSGenerator.java

/** Creates the sheet.
 * /*from   w  w w.  j ava2s  .  c o  m*/
 * @param wkbk -HSSFWorkbook.
 * @param style -HSSFCellStyle.
 * @param sheetName - String.
 * @return HSSFSheet. * */

protected final HSSFSheet createSheet(final HSSFWorkbook wkbk, final HSSFCellStyle style,
        final String sheetName) {

    HSSFSheet wkst = wkbk.createSheet(sheetName);
    int heightPoint = 10;
    HSSFFont font = wkbk.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints((short) heightPoint);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom((short) 1);

    return wkst;
}

From source file:matriz.core.GerarXLS.java

public static void expExcel(String nomeArquivo, String demanda, List<LineMatriz> linhastabela) {

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet firstSheet = workbook.createSheet("Aba1");

    HSSFFont fontBranca = workbook.createFont();
    fontBranca.setFontHeightInPoints((short) 16);
    fontBranca.setColor(HSSFColor.WHITE.index);
    fontBranca.setBoldweight(Font.BOLDWEIGHT_BOLD);

    HSSFFont fontBrancaMenor = workbook.createFont();
    fontBrancaMenor.setFontHeightInPoints((short) 10);
    fontBrancaMenor.setColor(HSSFColor.WHITE.index);
    fontBrancaMenor.setBoldweight(Font.BOLDWEIGHT_BOLD);

    FileOutputStream fos = null;//  w  w  w  . j  a  va2 s .c  om
    nomeArquivo = nomeArquivo + "/Matriz de Rastreabilidade " + demanda + ".xls";

    try {
        fos = new FileOutputStream(new File(nomeArquivo));

        HSSFRow rowa = firstSheet.createRow(0);
        rowa.createCell(1);

        // criar titulo
        HSSFRow row0 = firstSheet.createRow(1);
        HSSFCellStyle style = workbook.createCellStyle();

        style.setFont(fontBranca);
        style.setFillForegroundColor(HSSFColor.DARK_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);

        row0.createCell(0).setCellValue("Matriz de classes alteradas na " + demanda);
        row0.getCell(0).setCellStyle(style);

        //nome das colunas
        HSSFRow row1 = firstSheet.createRow(2);
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.DARK_BLUE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
        style2.setFont(fontBrancaMenor);

        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);

        row1.createCell(0).setCellValue("Sistema");
        row1.createCell(1).setCellValue("Mdulo");
        row1.createCell(2).setCellValue("Diretrio");
        row1.createCell(3).setCellValue("Arquivo");
        row1.createCell(4).setCellValue("Ambiente");
        row1.createCell(5).setCellValue("Tipo do Arquivo");
        row1.createCell(6).setCellValue("Ao");
        row1.createCell(7).setCellValue("Data");
        row1.createCell(8).setCellValue("Reviso SVN");
        row1.createCell(9).setCellValue("Autor");
        row1.createCell(10).setCellValue("Motivo da alterao");

        row1.getCell(0).setCellStyle(style2);
        row1.getCell(1).setCellStyle(style2);
        row1.getCell(2).setCellStyle(style2);
        row1.getCell(3).setCellStyle(style2);
        row1.getCell(4).setCellStyle(style2);
        row1.getCell(5).setCellStyle(style2);
        row1.getCell(6).setCellStyle(style2);
        row1.getCell(7).setCellStyle(style2);
        row1.getCell(8).setCellStyle(style2);
        row1.getCell(9).setCellStyle(style2);
        row1.getCell(10).setCellStyle(style2);

        firstSheet.setColumnWidth(0, 10000);
        firstSheet.setColumnWidth(1, 9000);
        firstSheet.setColumnWidth(2, 10000);
        firstSheet.setColumnWidth(3, 20000);
        firstSheet.setColumnWidth(4, 10000);
        firstSheet.setColumnWidth(5, 4000);
        firstSheet.setColumnWidth(6, 4000);
        firstSheet.setColumnWidth(7, 4000);
        firstSheet.setColumnWidth(8, 4000);
        firstSheet.setColumnWidth(9, 6000);
        firstSheet.setColumnWidth(10, 10000);

        HSSFCellStyle style3 = workbook.createCellStyle();
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);

        int i = 3;

        for (LineMatriz linha : linhastabela) {
            HSSFRow row = firstSheet.createRow(i);

            row.createCell(0).setCellValue(linha.getSistema());
            row.createCell(1).setCellValue(linha.getModulo());
            row.createCell(2).setCellValue(linha.getDiretorio());
            row.createCell(3).setCellValue(linha.getArquivo());
            row.createCell(4).setCellValue(linha.getAmbiente());
            row.createCell(5).setCellValue(linha.getTipoArquivo());
            row.createCell(6).setCellValue(linha.getAcao());
            row.createCell(7).setCellValue(linha.getData());
            row.createCell(8).setCellValue(linha.getRevisao());
            row.createCell(9).setCellValue(linha.getAutor());
            row.createCell(10).setCellValue(linha.getMotivo());

            row.getCell(0).setCellStyle(style3);
            row.getCell(1).setCellStyle(style3);
            row.getCell(2).setCellStyle(style3);
            row.getCell(3).setCellStyle(style3);
            row.getCell(4).setCellStyle(style3);
            row.getCell(5).setCellStyle(style3);
            row.getCell(6).setCellStyle(style3);
            row.getCell(7).setCellStyle(style3);
            row.getCell(8).setCellStyle(style3);
            row.getCell(9).setCellStyle(style3);
            row.getCell(10).setCellStyle(style3);

            i++;

        } // fim do for

        firstSheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based)
                1, //last row  (0-based)
                0, //first column (0-based)
                10 //last column  (0-based)
        ));

        workbook.write(fos);
        JOptionPane.showMessageDialog(null, "Arquivo criado em " + nomeArquivo);

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e.getMessage());
        System.out.println("Erro ao exportar arquivo");
    } finally {
        try {
            fos.flush();
            fos.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
    }
}