Example usage for org.apache.poi.ss.usermodel Workbook getCreationHelper

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getCreationHelper.

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * /*from   w  w  w  .jav  a2s.c  o  m*/
 * method name  : getExcelSurveyReport
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : Get Streaming excel object for valid/invalid survey report
 *
 * Date          :   Mar 16, 2016 1:23:57 PM
 */
public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response,
        Map<String, String> params, Locale locale) throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF);
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE);
    String titleRegion = null;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;
    List<ReportSummary> reportSummaries = (List<ReportSummary>) object;

    if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale));
    }
    if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale));
    }

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey + " - " + paramSemesterCode);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
            && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {

        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (ReportSummary reportSummary : reportSummaries) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
                && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
            row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank());
            row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank());
            row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank());
        }
        row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber()));
        row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode()));
        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
            row.createCell(colNum++)
                    .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName()));
        }
        row.createCell(colNum++).setCellValue(reportSummary.getCourseCode());
        row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo()));
        row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent());

        Cell cellStudentNoResponse = row.createCell(colNum++);
        cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse());
        cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingMean = row.createCell(colNum++);
        cellTeachingMean.setCellValue(reportSummary.getTeachingMean());
        cellTeachingMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingPercentageFavor = row.createCell(colNum++);
        cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor());
        cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionMean = row.createCell(colNum++);
        cellQuestionMean.setCellValue(reportSummary.getQuestionMean());
        cellQuestionMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionPercentageFavor = row.createCell(colNum++);
        cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor());
        cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

---

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * //from w  w w.j  av  a  2  s. c o  m
 * method name  : getExcelCollegeCoursesAsstDean
 * @param templateName
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : 
 *
 * Date          :   Jun 7, 2016 11:49:24 AM
 */
public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object,
        ResourceResponse response, Map<String, String> params, Locale locale)
        throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String titleRegion = null;
    List<StudentResponse> studentResponses = (List<StudentResponse>) object;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;

    sheet = workbook
            .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale));

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper
            .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (StudentResponse studentResponse : studentResponses) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode()));
        row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName()));
        row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken());
        row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse());
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude()));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

---

From source file:onl.netfishers.netshot.RestService.java

License:Open Source License

@GET
@Path("reports/export")
@RolesAllowed("readonly")
@Produces({ "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" })
public Response getDataXLSX(@Context HttpServletRequest request,
        @DefaultValue("-1") @QueryParam("group") long group,
        @DefaultValue("false") @QueryParam("interfaces") boolean exportInterfaces,
        @DefaultValue("false") @QueryParam("inventory") boolean exportInventory,
        @DefaultValue("xlsx") @QueryParam("format") String fileFormat) throws WebApplicationException {
    logger.debug("REST request, export data.");
    User user = (User) request.getSession().getAttribute("user");

    if (fileFormat.compareToIgnoreCase("xlsx") == 0) {
        String fileName = String.format("netshot-export_%s.xlsx",
                (new SimpleDateFormat("yyyyMMdd-HHmmss")).format(new Date()));

        Session session = Database.getSession();
        try {/*from   w w w.jav a 2s .  c o  m*/
            Workbook workBook = new XSSFWorkbook();
            Row row;
            Cell cell;

            CreationHelper createHelper = workBook.getCreationHelper();
            CellStyle datetimeCellStyle = workBook.createCellStyle();
            datetimeCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm"));
            CellStyle dateCellStyle = workBook.createCellStyle();
            dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));

            Sheet summarySheet = workBook.createSheet("Summary");
            row = summarySheet.createRow(0);
            row.createCell(0).setCellValue("Netshot version");
            row.createCell(1).setCellValue(Netshot.VERSION);
            row = summarySheet.createRow(1);
            row.createCell(0).setCellValue("Exported by");
            row.createCell(1).setCellValue(user.getName());
            row = summarySheet.createRow(2);
            row.createCell(0).setCellValue("Date and time");
            cell = row.createCell(1);
            cell.setCellValue(new Date());
            cell.setCellStyle(datetimeCellStyle);
            row = summarySheet.createRow(4);
            row.createCell(0).setCellValue("Selected Group");
            Query query;
            if (group == -1) {
                query = session.createQuery("select d from Device d");
                row.createCell(1).setCellValue("None");
            } else {
                query = session.createQuery("select d from Device d join d.ownerGroups g where g.id = :id")
                        .setLong("id", group);
                DeviceGroup deviceGroup = (DeviceGroup) session.get(DeviceGroup.class, group);
                row.createCell(1).setCellValue(deviceGroup.getName());
            }

            Sheet deviceSheet = workBook.createSheet("Devices");
            row = deviceSheet.createRow(0);
            row.createCell(0).setCellValue("ID");
            row.createCell(1).setCellValue("Name");
            row.createCell(2).setCellValue("Management IP");
            row.createCell(3).setCellValue("Domain");
            row.createCell(4).setCellValue("Network Class");
            row.createCell(5).setCellValue("Family");
            row.createCell(6).setCellValue("Creation");
            row.createCell(7).setCellValue("Last Change");
            row.createCell(8).setCellValue("Software");
            row.createCell(9).setCellValue("End of Sale Date");
            row.createCell(10).setCellValue("End Of Life Date");

            int yDevice = 1;

            @SuppressWarnings("unchecked")
            List<Device> devices = query.list();
            for (Device device : devices) {
                row = deviceSheet.createRow(yDevice++);
                row.createCell(0).setCellValue(device.getId());
                row.createCell(1).setCellValue(device.getName());
                row.createCell(2).setCellValue(device.getMgmtAddress().getIp());
                row.createCell(3).setCellValue(device.getMgmtDomain().getName());
                row.createCell(4).setCellValue(device.getNetworkClass().toString());
                row.createCell(5).setCellValue(device.getFamily());
                cell = row.createCell(6);
                cell.setCellValue(device.getCreatedDate());
                cell.setCellStyle(datetimeCellStyle);
                cell = row.createCell(7);
                cell.setCellValue(device.getChangeDate());
                cell.setCellStyle(datetimeCellStyle);
                row.createCell(8).setCellValue(device.getSoftwareVersion());
                if (device.getEosDate() != null) {
                    cell = row.createCell(9);
                    cell.setCellValue(device.getEosDate());
                    cell.setCellStyle(dateCellStyle);
                }
                if (device.getEolDate() != null) {
                    cell = row.createCell(10);
                    cell.setCellValue(device.getEolDate());
                    cell.setCellStyle(dateCellStyle);
                }
            }

            if (exportInterfaces) {
                Sheet interfaceSheet = workBook.createSheet("Interfaces");
                row = interfaceSheet.createRow(0);
                row.createCell(0).setCellValue("Device ID");
                row.createCell(1).setCellValue("Virtual Device");
                row.createCell(2).setCellValue("Name");
                row.createCell(3).setCellValue("Description");
                row.createCell(4).setCellValue("VRF");
                row.createCell(5).setCellValue("MAC Address");
                row.createCell(6).setCellValue("Enabled");
                row.createCell(7).setCellValue("Level 3");
                row.createCell(8).setCellValue("IP Address");
                row.createCell(9).setCellValue("Mask Length");
                row.createCell(10).setCellValue("Usage");

                int yInterface = 1;
                for (Device device : devices) {
                    for (NetworkInterface networkInterface : device.getNetworkInterfaces()) {
                        if (networkInterface.getIpAddresses().size() == 0) {
                            row = interfaceSheet.createRow(yInterface++);
                            row.createCell(0).setCellValue(device.getId());
                            row.createCell(1).setCellValue(networkInterface.getVirtualDevice());
                            row.createCell(2).setCellValue(networkInterface.getInterfaceName());
                            row.createCell(3).setCellValue(networkInterface.getDescription());
                            row.createCell(4).setCellValue(networkInterface.getVrfInstance());
                            row.createCell(5).setCellValue(networkInterface.getMacAddress());
                            row.createCell(6).setCellValue(networkInterface.isEnabled());
                            row.createCell(7).setCellValue(networkInterface.isLevel3());
                            row.createCell(8).setCellValue("");
                            row.createCell(9).setCellValue("");
                            row.createCell(10).setCellValue("");
                        }
                        for (NetworkAddress address : networkInterface.getIpAddresses()) {
                            row = interfaceSheet.createRow(yInterface++);
                            row.createCell(0).setCellValue(device.getId());
                            row.createCell(1).setCellValue(networkInterface.getVirtualDevice());
                            row.createCell(2).setCellValue(networkInterface.getInterfaceName());
                            row.createCell(3).setCellValue(networkInterface.getDescription());
                            row.createCell(4).setCellValue(networkInterface.getVrfInstance());
                            row.createCell(5).setCellValue(networkInterface.getMacAddress());
                            row.createCell(6).setCellValue(networkInterface.isEnabled());
                            row.createCell(7).setCellValue(networkInterface.isLevel3());
                            row.createCell(8).setCellValue(address.getIp());
                            row.createCell(9).setCellValue(address.getPrefixLength());
                            row.createCell(10).setCellValue(address.getAddressUsage() == null ? ""
                                    : address.getAddressUsage().toString());
                        }
                    }
                }
            }

            if (exportInventory) {
                Sheet inventorySheet = workBook.createSheet("Inventory");
                row = inventorySheet.createRow(0);
                row.createCell(0).setCellValue("Device ID");
                row.createCell(1).setCellValue("Slot");
                row.createCell(2).setCellValue("Part Number");
                row.createCell(3).setCellValue("Serial Number");

                int yInventory = 1;
                for (Device device : devices) {
                    for (Module module : device.getModules()) {
                        row = inventorySheet.createRow(yInventory++);
                        row.createCell(0).setCellValue(device.getId());
                        row.createCell(1).setCellValue(module.getSlot());
                        row.createCell(2).setCellValue(module.getPartNumber());
                        row.createCell(3).setCellValue(module.getSerialNumber());
                    }
                }
            }

            ByteArrayOutputStream output = new ByteArrayOutputStream();
            workBook.write(output);
            workBook.close();
            return Response.ok(output.toByteArray())
                    .header("Content-Disposition", "attachment; filename=" + fileName).build();
        } catch (IOException e) {
            logger.error("Unable to write the resulting file.", e);
            throw new WebApplicationException("Unable to write the resulting file.",
                    javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR);
        } catch (Exception e) {
            logger.error("Unable to generate the report.", e);
            throw new WebApplicationException("Unable to generate the report.",
                    javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR);
        } finally {
            session.close();
        }
    }

    logger.warn("Invalid requested file format.");
    throw new WebApplicationException("The requested file format is invalid or not supported.",
            javax.ws.rs.core.Response.Status.BAD_REQUEST);

}

---

From source file:opn.greenwebs.HyperlinkExample.java

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);//  www  .  j av  a  2 s .com

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream("hyperinks.xlsx");
    wb.write(out);
    out.close();

}

---

From source file:org.activityinfo.server.report.renderer.excel.BaseExcelRenderer.java

License:Open Source License

public BaseExcelRenderer(Workbook book, ElementT element) {

    this.element = element;
    this.book = book;
    this.factory = book.getCreationHelper();
    this.sheet = book.createSheet(composeSheetName());

    /* Create title line */

    Row titleRow = sheet.createRow(0);/*from  www  . j  av a  2s  .  co  m*/
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(factory.createRichTextString(element.getTitle()));

    /* Create filter descriptors */

    List<FilterDescription> descs = generateFilterDescriptions();

    rowIndex = 2;

    for (FilterDescription desc : descs) {

        Row filterRow = sheet.createRow(rowIndex++);
        Cell filterCell = filterRow.createCell(0);

        String label = desc.joinLabels(", ");
        if (label.length() > MAX_CELL_CONTENT_LENGTH) {
            LOGGER.severe("Huge filter label string: " + label);
            label = label.substring(0, MAX_CELL_CONTENT_LENGTH) + "...";
        }
        filterCell.setCellValue(factory.createRichTextString(label));
    }

    rowIndex++;

    generate();
}

---

From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*  ww w  .  j  a  v  a 2  s  .co  m*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");
    String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER);
    CSVStrategy reqCSVstrategy = null;
    if (delimiterParam != null && !delimiterParam.isEmpty()) {
        reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED);
    }
    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy());
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

---

From source file:org.apache.metamodel.excel.ExcelUpdateCallback.java

License:Apache License

/**
 * Gets the index identifier for the date format
 * /* w ww .  j  ava2 s.com*/
 * @return
 */
public short getDateCellFormat() {
    if (_dateCellFormat == null) {
        Workbook workbook = getWorkbook(true);
        _dateCellFormat = workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm");
    }
    return _dateCellFormat;
}

---

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

private static String getFormulaCellValue(Workbook wb, Cell cell) {
    // first try with a cached/precalculated value
    try {/*  w  w  w  .  j  a  va2  s  . co  m*/
        double numericCellValue = cell.getNumericCellValue();
        // TODO: Consider not formatting it, but simple using
        // Double.toString(...)
        return _numberFormat.format(numericCellValue);
    } catch (Exception e) {
        if (logger.isInfoEnabled()) {
            logger.info("Failed to fetch cached/precalculated formula value of cell: " + cell, e);
        }
    }

    // evaluate cell first, if possible
    try {
        if (logger.isInfoEnabled()) {
            logger.info("cell({},{}) is a formula. Attempting to evaluate: {}",
                    new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() });
        }

        final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // calculates the formula and puts it's value back into the cell
        final Cell evaluatedCell = evaluator.evaluateInCell(cell);

        return getCellValue(wb, evaluatedCell);
    } catch (RuntimeException e) {
        logger.warn("Exception occurred while evaluating formula at position ({},{}): {}",
                new Object[] { cell.getRowIndex(), cell.getColumnIndex(), e.getMessage() });
        // Some exceptions we simply log - result will be then be the
        // actual formula
        if (e instanceof FormulaParseException) {
            logger.error("Parse exception occurred while evaluating cell formula: " + cell, e);
        } else if (e instanceof IllegalArgumentException) {
            logger.error("Illegal formula argument occurred while evaluating cell formula: " + cell, e);
        } else {
            logger.error("Unexpected exception occurred while evaluating cell formula: " + cell, e);
        }
    }

    // last resort: return the string formula
    return cell.getCellFormula();
}

---

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ???//  w w  w.j a  va2s  . co m
 * 
 * @param cell 
 * @param type 
 * @param address ?
 * @see org.apache.poi.common.usermodel.Hyperlink
 */
public static void setHyperlink(Cell cell, HyperlinkType hyperlinkType, String address) {

    Workbook wb = cell.getRow().getSheet().getWorkbook();

    CreationHelper createHelper = wb.getCreationHelper();

    Hyperlink link = createHelper.createHyperlink(hyperlinkType);
    if (link instanceof HSSFHyperlink) {
        ((HSSFHyperlink) link).setTextMark(address);
    } else if (link instanceof XSSFHyperlink) {
        ((XSSFHyperlink) link).setAddress(address);
    }

    cell.setHyperlink(link);
}

---

From source file:org.bbreak.excella.reports.tag.ImageParamParser.java

License:Open Source License

/**
 * ??/*www.ja  va  2 s.  c  o  m*/
 * 
 * @param sheet ?
 * @param cell 
 * @param filePath ?
 * @param dx1 ??
 * @param dy1 ???
 * @param scale ???
 * @throws ParseException
 */
public void replaceImageValue(Sheet sheet, Cell cell, String filePath, Integer dx1, Integer dy1, Double scale)
        throws ParseException {

    Workbook workbook = sheet.getWorkbook();

    int format = -1;
    if (filePath.toLowerCase().endsWith(JPEG_SUFFIX) || filePath.toLowerCase().endsWith(JPG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_JPEG;
    } else if (filePath.toLowerCase().endsWith(PNG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_PNG;
    }
    if (format == -1) {
        throw new ParseException(cell,
                "????????" + filePath);
    }

    byte[] bytes = null;
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        bytes = IOUtils.toByteArray(is);
    } catch (Exception e) {
        throw new ParseException(cell, e);
    } finally {
        try {
            is.close();
        } catch (IOException e) {
            throw new ParseException(cell, e);
        }
    }

    int pictureIdx = workbook.addPicture(bytes, format);

    CreationHelper helper = workbook.getCreationHelper();

    @SuppressWarnings("rawtypes")
    Drawing drawing = drawingCash.get(sheet);
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
        drawingCash.put(sheet, drawing);
    }

    ClientAnchor anchor = helper.createClientAnchor();

    anchor.setRow1(cell.getRowIndex());
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setCol2(cell.getColumnIndex() + 1);
    if (dx1 != null) {
        anchor.setDx1(dx1);
    }
    if (dy1 != null) {
        anchor.setDy1(dy1);
    }

    Picture picture = drawing.createPicture(anchor, pictureIdx);
    picture.resize(scale);

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• »