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:DB.TopStockDescriptionList.java

public static void writeToFileExcel(String fileName, List<TopStockDescription> tsdData) {
    int SHORTNAME = 1;
    int LONGNAME = 0;
    int TEXT = 2;

    try {/*  www .  j  av  a  2s. co  m*/
        FileInputStream fileIn = new FileInputStream(fileName);

        Workbook wb = WorkbookFactory.create(fileIn);
        Sheet sheet = wb.getSheetAt(0);

        for (TopStockDescription tsd : tsdData) {
            if (tsd.getContentText().isEmpty())
                continue;
            //?  ? or Create
            int rowInt = 1;
            while (true) {
                Row row = sheet.getRow(rowInt);

                if (row == null) {// THE END OF THE ROW not found
                    //Create
                    row = sheet.createRow(rowInt);
                    Cell cellShortName = row.createCell(SHORTNAME);
                    cellShortName.setCellType(Cell.CELL_TYPE_STRING);
                    cellShortName.setCellValue(tsd.getShortName());

                    String longName = tsd.getShortName();
                    int endIndex = tsd.getContentText().indexOf(tsd.getShortName());
                    if (endIndex > 0 && endIndex < tsd.getContentText().length())
                        longName = tsd.getContentText().substring(0, endIndex - 1).trim();

                    Cell cellLongName = row.createCell(LONGNAME);
                    cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    cellLongName.setCellValue(longName);

                    Cell cellText = row.createCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                Cell cellShortName = row.getCell(SHORTNAME);
                String shortName = cellShortName.getRichStringCellValue().getString();
                if (shortName.equalsIgnoreCase(tsd.getShortName())) {//If Match, Update
                    //Cell cellLongName = row.getCell(LONGNAME);
                    //cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    //cellLongName.setCellValue(tsd.getLongName());

                    Cell cellText = row.getCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                rowInt++;
            }
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        fileIn.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java

License:Open Source License

@Override
public OutputStream createXlsData(String metricName, String rrdFilename, long startTime, long endTime)
        throws IOException, MetricsGraphException {
    LOGGER.trace("ENTERING: createXlsData");

    Workbook wb = new HSSFWorkbook();
    createSheet(wb, metricName, rrdFilename, startTime, endTime);

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    wb.write(bos);
    bos.close();/*  w ww .ja v a  2 s . co m*/

    LOGGER.trace("EXITING: createXlsData");

    return bos;
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java

License:Open Source License

@Override
public OutputStream createXlsReport(List<String> metricNames, String metricsDir, long startTime, long endTime,
        String summaryInterval) throws IOException, MetricsGraphException {
    LOGGER.trace("ENTERING: createXlsReport");

    Workbook wb = new HSSFWorkbook();
    Collections.sort(metricNames);

    if (StringUtils.isNotEmpty(summaryInterval)) {
        createSummary(wb, metricNames, metricsDir, startTime, endTime,
                SUMMARY_INTERVALS.valueOf(summaryInterval));
    } else {//from   ww  w  . j a va 2s  . co m
        for (int i = 0; i < metricNames.size(); i++) {
            String metricName = metricNames.get(i);
            String rrdFilename = getRrdFilename(metricsDir, metricName);
            String displayName = i + metricName;

            createSheet(wb, displayName, rrdFilename, startTime, endTime);
        }
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    wb.write(bos);
    bos.close();

    LOGGER.trace("EXITING: createXlsReport");

    return bos;
}

From source file:de.alpharogroup.export.excel.poi.ExcelPoiFactory.java

License:Open Source License

/**
 * Writes the given Workbook to the given file.
 *
 * @param workbook/*from  ww  w. ja  v a  2 s. c o  m*/
 *            the workbook
 * @param file
 *            the file
 * @return the Workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 */
public static Workbook writeWorkbook(final Workbook workbook, final File file) throws IOException {
    final FileOutputStream fileOutputStream = new FileOutputStream(file);
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    return workbook;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensionsTest.java

License:Open Source License

private File createWorkbookWithContent() throws IOException {
    final File emptyWorkbook = new File(PathFinder.getSrcTestResourcesDir(), "emptyWorkbook.xls");
    final Workbook workbook = ExcelPoiFactory.newHSSFWorkbook(emptyWorkbook);
    final Sheet sheet = ExcelPoiFactory.newSheet(workbook, "first sheet");
    int rownum = 0;
    Row row = sheet.createRow(rownum);// w ww. jav a 2 s .co m
    Cell cell0 = row.createCell(0);
    cell0.setCellValue("1");
    Cell cell1 = row.createCell(1);
    cell1.setCellValue("a");
    Cell cell2 = row.createCell(2);
    cell2.setCellValue("!");
    rownum++;
    row = sheet.createRow(rownum);
    cell0 = row.createCell(0);
    cell0.setCellValue("2");
    cell1 = row.createCell(1);
    cell1.setCellValue("b");
    cell2 = row.createCell(2);
    cell2.setCellValue("?");
    rownum++;
    row = sheet.createRow(rownum);
    cell0 = row.createCell(0);
    cell0.setCellValue("3");
    cell1 = row.createCell(1);
    cell1.setCellValue("c");
    cell2 = row.createCell(2);
    cell2.setCellValue("%");

    try {
        final OutputStream outputStream = StreamExtensions.getOutputStream(emptyWorkbook);
        workbook.write(outputStream);
        outputStream.close();
    } catch (final IOException e) {
        throw e;
    }
    return emptyWorkbook;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelUtilsTest.java

License:Open Source License

private File createWorkbookWithContent() throws IOException {
    final File emptyWorkbook = new File(PathFinder.getSrcTestResourcesDir(), "emptyWorkbook.xls");
    final Workbook workbook = ExcelPoiFactory.newHSSFWorkbook(emptyWorkbook);
    final Sheet sheet = ExcelPoiFactory.newSheet(workbook, "first sheet");
    int rownum = 0;
    Row row = sheet.createRow(rownum);//from w w w. ja v  a2s . c o m
    Cell cell0 = row.createCell(0);
    cell0.setCellValue("1");
    Cell cell1 = row.createCell(1);
    cell1.setCellValue("a");
    Cell cell2 = row.createCell(2);
    cell2.setCellValue("!");
    rownum++;
    row = sheet.createRow(rownum);
    cell0 = row.createCell(0);
    cell0.setCellValue("2");
    cell1 = row.createCell(1);
    cell1.setCellValue("b");
    cell2 = row.createCell(2);
    cell2.setCellValue("?");
    rownum++;
    row = sheet.createRow(rownum);
    cell0 = row.createCell(0);
    cell0.setCellValue("3");
    cell1 = row.createCell(1);
    cell1.setCellValue("c");
    cell2 = row.createCell(2);
    cell2.setCellValue("%");

    try {
        final OutputStream outputStream = StreamUtils.getOutputStream(emptyWorkbook);
        workbook.write(outputStream);
        outputStream.close();
    } catch (final IOException e) {
        throw e;
    }
    return emptyWorkbook;
}

From source file:de.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*ww w  .j  a v  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].*)");

    // 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() != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle();
                descriptions[i] = pd.getDescription();
            } 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, CSVStrategy.EXCEL_STRATEGY);
        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);
        try {
            sheet.createFreezePane(0, 1);
        } catch (IndexOutOfBoundsException e) {
            //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes
        }
        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:de.fraunhofer.sciencedataamanager.datamanager.SearchDefinitonExecutionDataManager.java

/**
 *
 * @param searchDefinitonExecutionList//from ww w .  ja  v a  2s .c  o m
 * @param outputStream
 * @throws Exception
 */
public void exportToExcel(LinkedList<SearchDefinitonExecution> searchDefinitonExecutionList,
        OutputStream outputStream) throws Exception {

    Workbook currentWorkBook = new HSSFWorkbook();
    int currenSheetCount = 0;
    for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) {

        Sheet currentSheet = currentWorkBook.createSheet();
        currentSheet.setFitToPage(true);
        currentSheet.setHorizontallyCenter(true);
        currentSheet.createFreezePane(0, 1);
        currentWorkBook.setSheetName(currenSheetCount, searchDefinitonExecution.getSystemInstance().getName());

        Row headerRow = currentSheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);

        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("Title");

        headerRow.createCell(2).setCellValue("Identifier 1");
        headerRow.createCell(3).setCellValue("Identifier 2");
        headerRow.createCell(4).setCellValue("Identifier 3");
        headerRow.createCell(5).setCellValue("Identifier 4");
        headerRow.createCell(6).setCellValue("Url 1");
        headerRow.createCell(7).setCellValue("Url 2");
        headerRow.createCell(8).setCellValue("Text 1");
        headerRow.createCell(9).setCellValue("Publication Name");
        headerRow.createCell(10).setCellValue("Issue Name");
        headerRow.createCell(11).setCellValue("Publish Date");
        headerRow.createCell(12).setCellValue("Volume");
        headerRow.createCell(13).setCellValue("Start Page");
        headerRow.createCell(14).setCellValue("Issue Identifier");

        CellStyle style = currentWorkBook.createCellStyle();
        Font headerFont = currentWorkBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);

        headerRow.setRowStyle(style);

        Row currentRow = null;
        int rowNum = 1;
        for (ScientificPaperMetaInformation scientificPaperMetaInformation : searchDefinitonExecution
                .getScientificPaperMetaInformation()) {
            currentRow = currentSheet.createRow(rowNum);
            currentRow.createCell(0).setCellValue(scientificPaperMetaInformation.getID());
            currentRow.createCell(1).setCellValue(scientificPaperMetaInformation.getTitle());
            currentRow.createCell(2).setCellValue(scientificPaperMetaInformation.getIdentifier_1());
            currentRow.createCell(3).setCellValue(scientificPaperMetaInformation.getIdentifier_2());
            currentRow.createCell(4).setCellValue(scientificPaperMetaInformation.getIdentifier_3());
            currentRow.createCell(5).setCellValue(scientificPaperMetaInformation.getIdentifier_4());
            currentRow.createCell(6).setCellValue(scientificPaperMetaInformation.getUrl_1());
            currentRow.createCell(7).setCellValue(scientificPaperMetaInformation.getUrl_2());
            currentRow.createCell(8).setCellValue(scientificPaperMetaInformation.getText_1());

            currentRow.createCell(9).setCellValue(scientificPaperMetaInformation.getSrcTitle());
            currentRow.createCell(10).setCellValue(scientificPaperMetaInformation.getScrPublisherName());
            currentRow.createCell(11).setCellValue(scientificPaperMetaInformation.getSrcPublicationDate());
            currentRow.createCell(12).setCellValue(scientificPaperMetaInformation.getSrcVolume());
            currentRow.createCell(13).setCellValue(scientificPaperMetaInformation.getSrcStartPage());
            currentRow.createCell(14).setCellValue(scientificPaperMetaInformation.getScrIdentifier_1());

            rowNum++;

        }
        currenSheetCount++;
    }
    currentWorkBook.write(outputStream);

    outputStream.close();

}

From source file:de.fraunhofer.sciencedataamanager.exampes.export.ExcelDataExport.java

/**
 *
 * @param dataToExport The objects gets all the values, which should
 * exported./*from w w  w  . j  a  v  a 2s . com*/
 * @param outputStream
 * @throws Exception
 */
@Override
public void export(Map<String, Map<String, List<Object>>> allConnectorsToExport, OutputStream outputStream)
        throws Exception {
    Workbook currentWorkBook = new HSSFWorkbook();
    int currenSheetCount = 0;

    for (String currentKey : allConnectorsToExport.keySet()) {
        Map<String, List<Object>> dataToExport = allConnectorsToExport.get(currentKey);
        List<String> columns = new ArrayList<String>(dataToExport.keySet());
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        int size = dataToExport.values().iterator().next().size();

        for (int i = 0; i < size; i++) {
            Map<String, Object> row = new HashMap<String, Object>();

            for (String column : columns) {
                row.put(column, dataToExport.get(column).get(i));
            }

            rows.add(row);
        }

        //for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) {
        Sheet currentSheet = currentWorkBook.createSheet();
        currentSheet.setFitToPage(true);
        currentSheet.setHorizontallyCenter(true);
        currentSheet.createFreezePane(0, 1);
        currentWorkBook.setSheetName(currenSheetCount, currentKey);

        Row headerRow = currentSheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);
        int headerColumnIndex = 0;
        for (String currentColumn : columns) {
            headerRow.createCell(headerColumnIndex).setCellValue(currentColumn);
            headerColumnIndex++;
        }
        CellStyle style = currentWorkBook.createCellStyle();
        Font headerFont = currentWorkBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);

        headerRow.setRowStyle(style);

        Row currentRow = null;
        int rowNum = 1;
        int currentColum = 0;

        for (Map<String, Object> currentRow2 : rows) {
            currentRow = currentSheet.createRow(rowNum);
            for (String column : columns) {
                if (currentRow2.get(column) != null) {
                    currentRow.createCell(currentColum).setCellValue(currentRow2.get(column).toString());
                }
                currentColum++;

            }
            currentColum = 0;
            rowNum++;
        }
        currenSheetCount++;
    }
    currentWorkBook.write(outputStream);

    outputStream.close();

}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.ExcelExportTestUtils.java

License:Open Source License

public static void persistWorkbook(Workbook workbook, File file) throws IOException {
    FileOutputStream fos = null;/*from w  w w .j av  a  2  s .com*/
    try {
        fos = new FileOutputStream(file);
        workbook.write(fos);
    } finally {
        IOUtils.closeQuietly(fos);
    }
}