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

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

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

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java

License:Open Source License

public MemoryFileBuffer writeXLS(IWContext iwc, Map paymentMethod, Map products) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    if (paymentMethod != null && products != null) {
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(
                this.iwrb.getLocalizedString("accounting_statistics.statistics", "Statistics"), 30));
        sheet.setColumnWidth((short) 0, (short) (30 * 256));
        sheet.setColumnWidth((short) 1, (short) (14 * 256));

        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 12);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);//  ww  w .java 2  s  .c  o  m

        int cellRow = 0;

        HSSFRow row = sheet.createRow(cellRow++);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.item", "Item"));
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.total", "Total"));
        cell.setCellStyle(style);

        Collection keys = paymentMethod.keySet();
        Iterator iter = keys.iterator();
        while (iter.hasNext()) {
            row = sheet.createRow(cellRow++);
            String key = (String) iter.next();
            int count = ((Collection) paymentMethod.get(key)).size();

            cell = row.createCell((short) 0);
            cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.payment_method." + key, key));

            cell = row.createCell((short) 1);
            cell.setCellValue(String.valueOf(count));
        }

        keys = products.keySet();
        iter = keys.iterator();
        while (iter.hasNext()) {
            row = sheet.createRow(cellRow++);
            String key = (String) iter.next();
            int count = ((Collection) products.get(key)).size();

            cell = row.createCell((short) 0);
            cell.setCellValue(
                    this.iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key));

            cell = row.createCell((short) 1);
            cell.setCellValue(String.valueOf(count));
        }

        keys = products.keySet();
        iter = keys.iterator();
        while (iter.hasNext()) {
            String key = (String) iter.next();
            createNewSheet(iwc, wb,
                    iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key),
                    (List) products.get(key));
        }

        wb.write(mos);
    }
    buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2);
    return buffer;
}

From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java

License:Open Source License

private void createNewSheet(IWContext iwc, HSSFWorkbook workbook, String sheetName, List entries) {
    Collections.sort(entries, new AccountingEntryComparator(iwc.getCurrentLocale()));

    NumberFormat format = NumberFormat.getInstance();
    format.setMaximumFractionDigits(0);/*from  www .  ja  v a2 s .  c  o  m*/
    format.setMinimumFractionDigits(0);
    format.setGroupingUsed(false);

    HSSFSheet sheet = workbook.createSheet(StringHandler.shortenToLength(sheetName, 30));
    sheet.setColumnWidth((short) 0, (short) (30 * 256));
    sheet.setColumnWidth((short) 1, (short) (14 * 256));
    sheet.setColumnWidth((short) 2, (short) (14 * 256));
    sheet.setColumnWidth((short) 3, (short) (14 * 256));
    sheet.setColumnWidth((short) 4, (short) (14 * 256));

    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);

    int cellRow = 0;
    short cellColumn = 0;
    HSSFRow row = sheet.createRow(cellRow++);

    HSSFCell cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.name", "Name"));
    cell.setCellStyle(style);

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.personal_id", "Personal ID"));
    cell.setCellStyle(style);

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_from", "Valid from"));
    cell.setCellStyle(style);

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_to", "Valid to"));
    cell.setCellStyle(style);

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.units", "Units"));
    cell.setCellStyle(style);

    Iterator iter = entries.iterator();
    while (iter.hasNext()) {
        AccountingEntry entry = (AccountingEntry) iter.next();
        row = sheet.createRow(cellRow++);
        cellColumn = 0;

        IWTimestamp startDate = new IWTimestamp(entry.getStartDate());

        IWTimestamp endDate = null;
        if (entry.getEndDate() != null) {
            endDate = new IWTimestamp(entry.getEndDate());
        }

        float units = 0;
        if (entry.getUnits() > 0) {
            units = entry.getUnits();
        } else {
            units = entry.getAmount();
        }

        cell = row.createCell(cellColumn++);
        cell.setCellValue(entry.getName());

        cell = row.createCell(cellColumn++);
        cell.setCellValue(PersonalIDFormatter.format(entry.getPersonalId(), iwc.getCurrentLocale()));

        cell = row.createCell(cellColumn++);
        cell.setCellValue(startDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT));

        cell = row.createCell(cellColumn++);
        if (endDate != null) {
            cell.setCellValue(endDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT));
        } else {
            cell.setCellValue("-");
        }

        cell = row.createCell(cellColumn++);
        cell.setCellValue(format.format(units));
    }
}

From source file:is.idega.idegaweb.egov.cases.business.CasesWriter.java

License:Open Source License

public MemoryFileBuffer writeXLS(IWContext iwc, Collection<Case> cases) throws Exception {
    MemoryFileBuffer buffer = new MemoryFileBuffer();
    MemoryOutputStream mos = new MemoryOutputStream(buffer);

    HSSFWorkbook workbook = new HSSFWorkbook();

    short cellColumn = 0;
    HSSFSheet sheet = workbook.createSheet(StringHandler
            .shortenToLength(iwrb.getLocalizedString("cases_fetcher.statistics", "Statistics"), 30));
    sheet.setColumnWidth(cellColumn++, (short) (8 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (14 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (30 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (14 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (18 * 256));
    if (getBusiness(iwc).useTypes()) {
        sheet.setColumnWidth(cellColumn++, (short) (14 * 256));
    }//  www.  java 2s .c om
    sheet.setColumnWidth(cellColumn++, (short) (14 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (30 * 256));
    sheet.setColumnWidth(cellColumn++, (short) (50 * 256));

    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);

    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    style2.setWrapText(true);

    int cellRow = 0;
    cellColumn = 0;
    HSSFRow row = sheet.createRow(cellRow++);

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

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

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

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

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

    if (getBusiness(iwc).useTypes()) {
        cell = row.createCell(cellColumn++);
        cell.setCellValue(this.iwrb.getLocalizedString("case_type", "Case type"));
        cell.setCellStyle(style);
    }

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

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

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

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("message", "Message"));
    cell.setCellStyle(style);

    cell = row.createCell(cellColumn++);
    cell.setCellValue(this.iwrb.getLocalizedString("reply", "Reply"));
    cell.setCellStyle(style);

    User currentUser = iwc.getCurrentUser();

    for (Iterator<Case> iter = cases.iterator(); iter.hasNext();) {
        Case theCase = iter.next();
        if (!(theCase instanceof GeneralCase)) {
            continue;
        }

        GeneralCase element = (GeneralCase) theCase;
        CaseCategory category = element.getCaseCategory();
        if (category != null) {
            Group handlerGroup = category.getHandlerGroup();
            if (handlerGroup != null && !currentUser.hasRelationTo(handlerGroup)) {
                continue;
            }
        }
        CaseType type = element.getCaseType();
        CaseStatus status = element.getCaseStatus();
        if (status != null && status.equals(getBusiness(iwc).getCaseStatusDeleted())) {
            continue;
        }
        User user = element.getOwner();
        IWTimestamp created = new IWTimestamp(element.getCreated());

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

        cell = row.createCell(cellColumn++);
        cell.setCellValue(element.getPrimaryKey().toString());
        cell.setCellStyle(style2);

        cell = row.createCell(cellColumn++);
        cell.setCellValue(created.getLocaleDateAndTime(locale, IWTimestamp.SHORT, IWTimestamp.SHORT));
        cell.setCellStyle(style2);

        if (user != null) {
            Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName());
            cell = row.createCell(cellColumn++);
            cell.setCellValue(name.getName(locale));
            cell.setCellStyle(style2);

            cell = row.createCell(cellColumn++);
            cell.setCellValue(PersonalIDFormatter.format(user.getPersonalID(), locale));
            cell.setCellStyle(style2);
        } else {
            cell = row.createCell(cellColumn++);
            cell.setCellValue("");
            cell = row.createCell(cellColumn++);
            cell.setCellValue("");
        }

        cell = row.createCell(cellColumn++);
        cell.setCellValue(category == null ? CoreConstants.EMPTY : category.getLocalizedCategoryName(locale));
        cell.setCellStyle(style2);

        if (type != null && getBusiness(iwc).useTypes()) {
            cell = row.createCell(cellColumn++);
            cell.setCellValue(type.getName());
            cell.setCellStyle(style2);
        }

        cell = row.createCell(cellColumn++);
        cell.setCellValue(element.getReference() != null ? element.getReference() : "");
        cell.setCellStyle(style2);

        cell = row.createCell(cellColumn++);
        cell.setCellValue(status == null ? CoreConstants.MINUS
                : getBusiness(iwc).getLocalizedCaseStatusDescription(element, status, locale));
        cell.setCellStyle(style2);

        cell = row.createCell(cellColumn++);
        cell.setCellValue(element.getSubject() != null ? element.getSubject() : "-");
        cell.setCellStyle(style2);

        cell = row.createCell(cellColumn++);
        cell.setCellValue(element.getMessage());
        cell.setCellStyle(style2);

        Collection<CaseLog> logs = getBusiness(iwc).getCaseLogs(element);
        if (!logs.isEmpty()) {
            for (CaseLog log : logs) {
                cell = row.createCell(cellColumn++);
                cell.setCellValue(log.getComment());
                cell.setCellStyle(style2);
            }
        } else if (element.getReply() != null) {
            cell = row.createCell(cellColumn++);
            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.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);/*  w  ww. j av a  2  s  .co  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();//from  w ww.  j  av  a2 s.  c  o  m
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    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();//from   w ww .  j av  a 2  s .c  om
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    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();//from   w w  w .  ja v  a 2s  . c  o  m
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    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);//  w ww. j  a v a2  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  ww w  .ja  v  a 2 s . co m
    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);//  w  w w.  j a  v a2 s .  c o m
    return normalStyle;
}