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

List of usage examples for org.apache.poi.ss.usermodel Workbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. write

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

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

License:Open Source License

/**
 * //from  w w  w  .jav a  2  s . 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 ww  . ja v  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   ww w.  ja v  a2 s. c om*/
            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);/*ww  w.j  a  va 2 s.c  o  m*/

    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.ExcelMapDataExporter.java

License:Open Source License

@Override
public void render(ReportElement element, OutputStream stm) throws IOException {

    if (!(element instanceof MapReportElement)) {
        throw new RuntimeException("ExcelMapDataExporter accepts only MapElements");
    }/*from  w  w w . j  a v  a  2  s . c  o  m*/

    MapContent content = ((MapReportElement) element).getContent();

    Workbook book = new HSSFWorkbook();
    Sheet sheet = book.createSheet();

    Helper helper = new Helper(book);

    Row headerRow = sheet.createRow(0);
    helper.addCell(headerRow, 0, "Latitude");
    helper.addCell(headerRow, 1, "Longitude");
    helper.addCell(headerRow, 2, "Value");
    helper.addCell(headerRow, 3, "Color");
    helper.addCell(headerRow, 4, "Icon");

    int rowIndex = 1;

    for (MapMarker marker : content.getMarkers()) {

        Row dataRow = sheet.createRow(rowIndex++);
        helper.addCell(dataRow, 0, marker.getLat());
        helper.addCell(dataRow, 1, marker.getLng());
        if (marker instanceof BubbleMapMarker) {
            BubbleMapMarker bmarker = (BubbleMapMarker) marker;
            helper.addCell(dataRow, 2, bmarker.getValue());
            helper.addCell(dataRow, 3, bmarker.getColor());
        }
        if (marker instanceof IconMapMarker) {
            IconMapMarker imarker = (IconMapMarker) marker;
            if (imarker.getIcon() != null) {
                helper.addCell(dataRow, 4, imarker.getIcon().getName());
            }
        }

    }

    book.write(stm);

}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else/*from ww w . j  a v  a2s  .  c om*/
        wb = new XSSFWorkbook();

    sameCell(wb.createSheet("Same Cell"));
    multiCell(wb.createSheet("MultiCell"));
    errors(wb.createSheet("Errors"));
    hideDupplicates(wb.createSheet("Hide Dups"));
    formatDuplicates(wb.createSheet("Duplicates"));
    inList(wb.createSheet("In List"));
    expiry(wb.createSheet("Expiry"));
    shadeAlt(wb.createSheet("Shade Alt"));
    shadeBands(wb.createSheet("Shade Bands"));

    // Write the output to a file
    String file = "e:/cf-poi.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

}

From source file:org.aio.handy.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else//from w  w w .  j  av  a2 s .  c o m
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                // the 10th cell contains sum over week days, e.g.
                // SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "e:/timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

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./*from www . j a  v a2s.  c o 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.jena.examples.ExampleARQ_06.java

License:Apache License

public static void main(String[] args) throws IOException {
    FileManager.get().addLocatorClassLoader(ExampleARQ_01.class.getClassLoader());
    Model model = FileManager.get().loadModel("data/data.ttl");
    Query query = QueryFactory.create("SELECT * WHERE { ?s ?p ?o }");
    QueryExecution qexec = QueryExecutionFactory.create(query, model);

    FileOutputStream out = new FileOutputStream("target/sxssf.xlsx");
    Workbook wb = new SXSSFWorkbook(100);
    Sheet sh = wb.createSheet();/*from  ww  w .ja  v  a2  s . c  om*/

    int rows = 0;
    int columns = 0;
    try {
        ResultSet resultSet = qexec.execSelect();
        List<String> varNames = resultSet.getResultVars();
        List<Var> vars = new ArrayList<Var>(varNames.size());

        // first row with var names
        Row row = sh.createRow(rows++);
        for (String varName : varNames) {
            Var var = Var.alloc(varName);
            Cell cell = row.createCell(columns++);
            cell.setCellValue(var.toString());
            vars.add(var);
        }

        // other rows with bindings
        while (resultSet.hasNext()) {
            Binding bindings = resultSet.nextBinding();
            row = sh.createRow(rows++);
            columns = 0;
            for (Var var : vars) {
                Node n = bindings.get(var);
                if (n != null) {
                    Cell cell = row.createCell(columns++);
                    String value = FmtUtils.stringForNode(n, (SerializationContext) null);
                    cell.setCellValue(value);
                }
            }
        }
    } finally {
        qexec.close();
    }

    wb.write(out);
    out.close();
}

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

License:Apache License

/**
 * Writes the {@link Workbook} to a {@link Resource}. The {@link Workbook}
 * will be closed as a result of this operation!
 * // w w  w .  ja v a2  s.  c om
 * @param dataContext
 * @param wb
 */
public static void writeAndCloseWorkbook(ExcelDataContext dataContext, final Workbook wb) {
    // first write to a temp file to avoid that workbook source is the same
    // as the target (will cause read+write cyclic overflow)

    final Resource realResource = dataContext.getResource();
    final Resource tempResource = new InMemoryResource(realResource.getQualifiedPath());

    tempResource.write(new Action<OutputStream>() {
        @Override
        public void run(OutputStream outputStream) throws Exception {
            wb.write(outputStream);
        }
    });

    if (wb instanceof HSSFWorkbook && realResource instanceof FileResource && realResource.isExists()) {
        // TODO POI has a problem with closing a file-reference/channel
        // after wb.write() is invoked. See POI issue to be fixed:
        // https://bz.apache.org/bugzilla/show_bug.cgi?id=58480
        System.gc();
        System.runFinalization();
        try {
            Thread.sleep(800);
        } catch (InterruptedException e) {
        }
    }

    FileHelper.safeClose(wb);

    FileHelper.copy(tempResource, realResource);
}