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

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

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

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

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

License:Open Source License

/**
 * // 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 {//w  w  w  .  j  a va 2  s .com
            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);/*from   w w  w.  j ava2 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.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);/* ww  w. ja v  a2 s. com*/
    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.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//ww w.j  a  v a  2s  .  c  o m
        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/* w w w  . j a  v  a  2s  . c om*/
        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.alanwilliamson.openbd.plugin.spreadsheet.tags.cfSpreadSheetWrite.java

License:Open Source License

protected void writeQueryToSheet(cfQueryResultData queryData, cfSpreadSheetData spreadsheet, String sheetName)
        throws dataNotSupportedException {
    Workbook workbook = spreadsheet.getWorkBook();

    if (workbook.getSheet(sheetName) != null)
        workbook.removeSheetAt(workbook.getSheetIndex(sheetName));

    Sheet sheet = workbook.createSheet(sheetName);

    //WRITE THE SHEET: 1st row to be the columns
    String[] columnList = queryData.getColumnList();
    Row row = sheet.createRow(0);//ww  w.j a v a2 s. c  om
    Cell cell;
    for (int c = 0; c < columnList.length; c++) {
        cell = row.createCell(c, Cell.CELL_TYPE_STRING);
        cell.setCellValue(columnList[c]);
    }

    //WRITE THE SHEET: Write out all the rows
    int rowsToInsert = queryData.getSize();
    for (int x = 0; x < rowsToInsert; x++) {
        row = sheet.createRow(x + 1);

        for (int c = 0; c < columnList.length; c++) {
            cell = row.createCell(c);

            cfData value = queryData.getCell(x + 1, c + 1);

            if (value.getDataType() == cfData.CFNUMBERDATA) {
                cell.setCellValue(value.getDouble());
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            } else if (value.getDataType() == cfData.CFDATEDATA) {
                cell.setCellValue(new Date(value.getDateLong()));
            } else if (value.getDataType() == cfData.CFBOOLEANDATA) {
                cell.setCellValue(value.getBoolean());
                cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
            } else {
                cell.setCellValue(value.getString());
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }

    }
}

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  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.aludratest.app.excelwizard.WorkbookTracker.java

License:Apache License

private static void createConfigTab(Workbook workbook, Method testMethod) {
    Sheet configTab = workbook.createSheet("config");
    Row headerRow = configTab.createRow(0);
    headerRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("testConfiguration");
    headerRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("ignore");
    Row dataRow = configTab.createRow(1);
    String configName = testMethod.getDeclaringClass().getSimpleName();
    if (configName.startsWith("ID_")) {
        configName = "C" + configName + "1";
    }//from   w w  w.j  a v  a2 s  .co  m
    dataRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(configName);
}

From source file:org.ascent.deployment.excel.output.ExcelDeploymentPlan.java

License:Open Source License

public static void write(DeploymentPlan plan, File target) throws IOException {
    try {/*from w  w  w .  java  2s  .  co m*/

        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet(DeploymentPlanHandler.DEPLOYMENT_PLAN_SHEET);

        Component[] comps = plan.getDeploymentConfiguration().getComponents();
        Node[] nodes = plan.getDeploymentConfiguration().getNodes();

        Map<Node, Integer> indices = new HashMap<Node, Integer>();
        for (int i = 0; i < nodes.length; i++) {
            indices.put(nodes[i], i + 1);

            Cell c = get(sheet, 0, i + 1, Cell.CELL_TYPE_STRING);
            c.setCellValue(nodes[i].getLabel());
        }

        for (int i = 0; i < comps.length; i++) {

            Component c = comps[i];
            Cell cell = get(sheet, i + 1, 0, Cell.CELL_TYPE_STRING);
            cell.setCellValue(c.getLabel());

            Cell dc = get(sheet, i + 1, indices.get(plan.getHost(c)), Cell.CELL_TYPE_NUMERIC);
            dc.setCellValue(1);
        }

        FileOutputStream fout = new FileOutputStream(target);

        workbook.write(fout);
        fout.flush();
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}