Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook write

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. write

Introduction

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

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

From source file:org.rapidpm.modul.javafx.tableview.filtered.contextmenue.FilteredTableContextMenu.java

License:Apache License

private byte[] convertTable2Xls() throws IOException {
    //konvertiere
    final XSSFWorkbook workbook = new XSSFWorkbook();
    final XSSFSheet xssfSheet = workbook.createSheet("ExcelExport_" + sdf.format(new Date()));
    final XSSFRow xssfHeaderRow = xssfSheet.createRow(0);
    final ObservableList<TableColumn> columns = filteredTableView.getColumns();
    int colNr = 0;
    for (final TableColumn column : columns) {
        final String columnText = column.getText();
        final XSSFCell xssfCell = xssfHeaderRow.createCell(colNr);
        colNr = colNr + 1;// w  w  w .jav a 2s  .c  o m
        xssfCell.setCellValue(columnText);
        xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
    }

    final ObservableList<FilteredTableDataRow> rowList = filteredTableView.getItems();
    int rowNr = 0;
    for (final FilteredTableDataRow row : rowList) {
        final XSSFRow xssfRow = xssfSheet.createRow(rowNr);
        rowNr = rowNr + 1;
        final String csvRow = row.convertToCSV();
        final String[] split = csvRow.split(";");
        int cellNr = 0;
        for (final String s : split) {
            final XSSFCell xssfCell = xssfRow.createCell(cellNr);
            cellNr = cellNr + 1;
            xssfCell.setCellValue(s);
            xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
        }

    }
    final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
        workbook.write(outputStream);
    } catch (IOException e) {
        logger.error(e);
    }

    return outputStream.toByteArray();
}

From source file:org.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java

License:Open Source License

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    XSSFWorkbook workbook = new XSSFWorkbook();
    cs = workbook.createCellStyle();/*from w  ww.j  a v  a  2s  .co m*/
    XSSFFont f = workbook.createFont();
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    XSSFSheet sheet = workbook.createSheet(SHEET_LABEL);
    sheet.setDefaultColumnWidth((short) 20);
    XSSFRow row = sheet.createRow((short) 0);
    try {
        setParameter(request);
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //$NON-NLS-1$
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        fillHeader(row);
        fillSheet(sheet);
    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
    OutputStream out = response.getOutputStream();
    workbook.write(out);
    out.close();
}

From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java

/**
 * This is used for ExportPackage implementations
 * /*from   w  w w  .java  2s.  com*/
 * @param submission
 *            - the {@link Submission}
 * @return - the {@link ExportPackage} object
 */
@Override
public ExportPackage generatePackage(Submission submission) {
    if (attachmentTypes.size() == 0) {
        throw new IllegalArgumentException(
                "Unable to generate package because not attachment types have been defined.");
    }

    // Check that we have everything that we need.
    if (submission == null || submission.getId() == null)
        throw new IllegalArgumentException(
                "Unable to generate a package because the submission is null, or has not been persisted.");

    try {

        // Set String replacement parameters
        Map<String, String> parameters = new HashMap<String, String>();
        parameters = StringVariableReplacement.setParameters(submission);

        File pkg = null;
        List<Attachment> actionLogAttachments = new ArrayList<Attachment>();

        pkg = File.createTempFile("template-export-", ".dir");

        // The package has more than one file, so export as a directory.
        pkg.delete();
        pkg.mkdir();

        File xl = new File(pkg.getPath(), "data.xlsx");
        if (xl.exists() || xl.isDirectory()) {
            xl.delete();
            xl.createNewFile();
        }
        XSSFWorkbook wbook = writeWorkbook(submission, Arrays.asList(SearchOrder.values()));
        FileOutputStream os = new FileOutputStream(xl);
        wbook.write(os);
        os.close();

        // Add all the attachments
        List<Attachment> attachments = submission.getAttachments();
        List<Attachment> actionLogs = submission.getAttachmentsByType(AttachmentType.ACTIONLOG);
        // add them to this list to delete the temp files later
        actionLogAttachments.addAll(actionLogs);
        // add them to this list to add them to export
        attachments.addAll(actionLogs);
        for (Attachment attachment : attachments) {
            // Do we include this type?
            if (!attachmentTypes.contains(attachment.getType()))
                continue;

            /*
             * The string substitution only works on items we can retrieve from the submission so we have to get the file name for each attachment here in the attachment loop.
             */
            String shortFileName = attachment.getName()
                    .replaceAll("." + FilenameUtils.getExtension(attachment.getName()), "");

            String fileName = attachment.getName();

            // Customize Attachment Name
            if (attachmentAttributes.get(attachment.getType().name()).get("customName") != null) {
                fileName = attachmentAttributes.get(attachment.getType().name()).get("customName") + "."
                        + FilenameUtils.getExtension(attachment.getName());
                fileName = fileName.replace("{FILE_NAME}", shortFileName);
                fileName = StringVariableReplacement.applyParameterSubstitution(fileName, parameters);
            }

            // Check for Custom Directory Structure.
            String pkgPath = pkg.getPath();

            if (attachmentAttributes.get(attachment.getType().name()).get("directory") != null) {
                String dirName = (String) attachmentAttributes.get(attachment.getType().name())
                        .get("directory");
                dirName = dirName.replace("{FILE_NAME}", shortFileName);
                dirName = StringVariableReplacement.applyParameterSubstitution(dirName, parameters);
                pkgPath = pkgPath + File.separator + dirName;
            }

            File exportFile = new File(pkgPath, fileName);

            FileUtils.copyFile(attachment.getFile(), exportFile);
        } // End for loop

        // Create the actual package!
        return new ExcelFilePackage(submission, pkg, null, actionLogAttachments);
    } catch (Exception ioe) {
        throw new RuntimeException("Unable to generate package", ioe);
    }
}

From source file:org.tdl.vireo.model.jpa.JpaSubmissionImpl.java

private Attachment generateActionLogAttachment() {
    // create an Excel Workbook to store the action log as an attachment
    Attachment actionLogAttachment = null;
    String sheetName = "ActionLog";
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow header = sheet.createRow(0);
    int rowNum = 1;
    XSSFRow row = sheet.createRow(rowNum);
    int colNum = 0;
    for (ActionLog actionLog : actionLogs) {
        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Action Date");
        }/* ww w  .ja va 2 s  .  c  o  m*/
        row.createCell(colNum).setCellValue(actionLog.getActionDate());
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Type");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getType().name());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Date");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getDate());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Name");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getName());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Size");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getSize());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Action Entry");
        }
        row.createCell(colNum).setCellValue(actionLog.getEntry());
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Submission State");
        }
        row.createCell(colNum).setCellValue(actionLog.getSubmissionState().getBeanName());
        colNum++;

        rowNum++;
        row = sheet.createRow(rowNum);
        colNum = 0;
    }
    try {
        File actionLogFile = File.createTempFile("actionlog-", ".xlsx");
        actionLogFile.deleteOnExit();
        FileOutputStream actionLogFileOS = new FileOutputStream(actionLogFile);
        wb.write(actionLogFileOS);
        actionLogFileOS.flush();
        actionLogFileOS.close();
        actionLogAttachment = new ActionLogAttachment(this, actionLogFile);
    } catch (IOException e) {
        play.Logger.error("Error while generating Action Log Attachment! [%s]", e);
    }
    return actionLogAttachment;
}

From source file:org.tsukuba_bunko.lilac.service.impl.ExportServiceImpl.java

License:Open Source License

/**
 * @see org.tsukuba_bunko.lilac.service.ExportService#exportData(java.io.OutputStream, org.tsukuba_bunko.lilac.service.ExportService.ExportTarget[])
 *///from  www  .  j av  a  2  s .  c o  m
@Override
public void exportData(OutputStream target, ExportTarget... exportTargets) {
    if (exportTargets.length == 0) {
        exportData(target, ExportTarget.All);
    }

    XSSFWorkbook book = new XSSFWorkbook();

    for (ExportTarget exportTarget : exportTargets) {
        switch (exportTarget) {
        case Label:
            exportLabelHelper.exportData(book);
            break;
        case Author:
            exportAuthorHelper.exportData(book);
            break;
        case Bibliography:
            exportBibliographyHelper.exportData(book);
            break;
        case Bookshelf:
            exportBookshelfHelper.exportData(book);
            break;
        case Book:
            exportBookHelper.exportData(book);
            break;
        case ReadingRecord:
            exportReadingRecordHelper.exportData(book);
            break;
        case All:
            exportLabelHelper.exportData(book);
            exportAuthorHelper.exportData(book);
            exportBibliographyHelper.exportData(book);
            exportBookshelfHelper.exportData(book);
            exportBookHelper.exportData(book);
            exportReadingRecordHelper.exportData(book);
            break;
        }
    }

    try {
        book.write(target);
    } catch (IOException ioe) {
        throw new IORuntimeException(ioe);
    }
}

From source file:org.zafritech.zidingorms.io.excel.ExcelController.java

@RequestMapping("/excel/download/{id}")
public void downloadExcelArtifact(@PathVariable Long id, HttpServletResponse response) throws IOException {

    Artifact artifact = artifactRepository.findOne(id);

    DateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd-HHmmss");
    String fileName = timeFormat.format(System.currentTimeMillis()) + "_" + artifact.getIdentifier()
            + "_Requirements.xlsx";

    XSSFWorkbook workbook = artifactService.DownloadExcel(id);
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    workbook.write(baos);
    baos.close();/*ww w .ja  va 2  s.  c  o m*/

    response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setContentLength(baos.size());

    ServletOutputStream fout = response.getOutputStream();
    fout.write(baos.toByteArray());
    fout.flush();
    fout.close();
}

From source file:packtest.AligningCells.java

License:Apache License

public static void main(String[] args) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();//from   w ww  .jav  a  2s  . com
    XSSFRow row = sheet.createRow((short) 2);
    row.setHeightInPoints(30);
    for (int i = 0; i < 8; i++) {
        //column width is set in units of 1/256th of a character width
        sheet.setColumnWidth(i, 256 * 15);
    }

    createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);

    //center text over B4, C4, D4
    row = sheet.createRow((short) 3);
    centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}

From source file:packtest.BigGridDemo.java

License:Apache License

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

    // Step 1. Create a template file. Setup sheets and workbook-level objects such as
    // cell styles, number formats, etc.

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("Big Grid");

    Map<String, XSSFCellStyle> styles = createStyles(wb);
    //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
    String sheetRef = sheet.getPackagePart().getPartName().getName();

    //save the template
    FileOutputStream os = new FileOutputStream("template.xlsx");
    wb.write(os);
    os.close();/*  w  w w . java 2 s  .  c om*/

    //Step 2. Generate XML file.
    File tmp = File.createTempFile("sheet", ".xml");
    Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
    generate(fw, styles);
    fw.close();

    //Step 3. Substitute the template entry with the generated data
    FileOutputStream out = new FileOutputStream(Utils.getPath("big-grid.xlsx"));
    substitute(new File(Utils.getPath("template.xlsx")), tmp, sheetRef.substring(1), out);
    out.close();

    wb.close();
}

From source file:packtest.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    if (args.length > 0)
        calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));

    int year = calendar.get(Calendar.YEAR);

    XSSFWorkbook wb = new XSSFWorkbook();
    Map<String, XSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        XSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);//from   ww w  . ja va 2s  .  c o  m
        XSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setOrientation(PrintOrientation.LANDSCAPE);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        //the header row: centered text in 48pt font
        XSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        XSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        XSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //for compatibility with HSSF we have to set column width in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            XSSFCell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            XSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                XSSFCell dayCell_1 = row.createCell(i * 2);
                XSSFCell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx");
    wb.write(out);
    out.close();

    wb.close();
}

From source file:packtest.CreatePivotTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();/*from  w w w  .  j av  a 2 s.co m*/

    //Create some data to build the pivot table on
    setCellData(sheet);

    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);

    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-pivottable.xlsx"));
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}