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

List of usage examples for org.apache.poi.ss.usermodel Workbook 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:com.qihang.winter.poi.excel.view.JeecgSingleExcelView.java

License:Apache License

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String codedFileName = "";
    Workbook workbook = null;
    if (model.containsKey(NormalExcelConstants.MAP_LIST)) {
        List<Map<String, Object>> list = (List<Map<String, Object>>) model.get(NormalExcelConstants.MAP_LIST);
        if (list.size() == 0) {
            throw new RuntimeException("MAP_LIST IS NULL");
        }/*from   w w w  . j ava2 s . c  o  m*/
        workbook = ExcelExportUtil.exportExcel((ExportParams) list.get(0).get(NormalExcelConstants.PARAMS),
                (Class<?>) list.get(0).get(NormalExcelConstants.CLASS),
                (Collection<?>) list.get(0).get(NormalExcelConstants.DATA_LIST));
        for (int i = 1; i < list.size(); i++) {
            new com.qihang.winter.poi.excel.export.ExcelExportServer().createSheet(workbook,
                    (ExportParams) list.get(i).get(NormalExcelConstants.PARAMS),
                    (Class<?>) list.get(i).get(NormalExcelConstants.CLASS),
                    (Collection<?>) list.get(i).get(NormalExcelConstants.DATA_LIST));
        }
    } else {
        workbook = ExcelExportUtil.exportExcel((ExportParams) model.get(NormalExcelConstants.PARAMS),
                (Class<?>) model.get(NormalExcelConstants.CLASS),
                (Collection<?>) model.get(NormalExcelConstants.DATA_LIST));
    }
    if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
        codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME);
    }
    if (workbook instanceof HSSFWorkbook) {
        codedFileName += HSSF;
    } else {
        codedFileName += XSSF;
    }
    if (isIE(request)) {
        codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
    } else {
        codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
    ServletOutputStream out = response.getOutputStream();
    workbook.write(out);
    out.flush();
}

From source file:com.qihang.winter.poi.excel.view.JeecgTemplateExcelView.java

License:Apache License

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String codedFileName = "";
    Workbook workbook = ExcelExportUtil.exportExcel(
            (com.qihang.winter.poi.excel.entity.TemplateExportParams) model.get(TemplateExcelConstants.PARAMS),
            (Class<?>) model.get(TemplateExcelConstants.CLASS),
            (List<?>) model.get(TemplateExcelConstants.LIST_DATA),
            (Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA));
    if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
        codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME);
    }/* w  w w .j av a  2 s . co  m*/
    if (workbook instanceof HSSFWorkbook) {
        codedFileName += HSSF;
    } else {
        codedFileName += XSSF;
    }
    if (isIE(request)) {
        codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
    } else {
        codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
    ServletOutputStream out = response.getOutputStream();
    workbook.write(out);
    out.flush();
}

From source file:com.qualogy.qafe.service.DocumentExporter.java

License:Apache License

public static void exportExcel(DocumentExportInput input, ByteArrayOutputStream bout) {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Exported data");
    if (input.getData() != null) {
        if (bout != null) {
            short index = 0;

            List<String> headerColumns = input.getColumnSequence();
            if (headerColumns == null) {
                headerColumns = getHeaderColumns(input.getData());
            }/* w w  w .ja v a2s .  c  o  m*/
            if (headerColumns != null) {
                if (input.isGenerateColumnHeader()) {
                    Row row = sheet.createRow(index++);
                    for (int i = 0; i < headerColumns.size(); i++) {
                        String columnHeader = headerColumns.get(i);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(columnHeader);
                    }
                }

                for (Map<String, Object> map : input.getData()) {
                    Row row = sheet.createRow(index++);
                    for (int i = 0; i < headerColumns.size(); i++) {
                        String key = headerColumns.get(i);
                        Object value = map.get(key);
                        Cell cell = row.createCell(i);
                        setCellValue(cell, value);
                    }
                }
            }
        }
    }

    try {

        wb.write(bout);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from   w w  w  .  j  av a 2s  .  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(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, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } 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:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void excerpt(Workbook wb, List<Sheet> sheetsToKeep, OutputStream output) throws IOException {
    // Make the requested sheets be read only
    Set<String> keepNames = new HashSet<String>();
    for (Sheet s : sheetsToKeep) {
        keepNames.add(s.getSheetName());
        for (Row r : s) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    switch (c.getCachedFormulaResultType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        double vd = c.getNumericCellValue();
                        c.setCellType(Cell.CELL_TYPE_NUMERIC);
                        c.setCellValue(vd);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        RichTextString vs = c.getRichStringCellValue();
                        c.setCellType(Cell.CELL_TYPE_STRING);
                        c.setCellValue(vs);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean vb = c.getBooleanCellValue();
                        c.setCellType(Cell.CELL_TYPE_BOOLEAN);
                        c.setCellValue(vb);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        c.setCellType(Cell.CELL_TYPE_BLANK);
                        break;
                    }/*from  w w  w  .  j  a va 2  s  . co  m*/
                }
            }
        }
    }

    // Remove all the other sheets
    // Note - work backwards! Avoids order changing under us
    for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
        String name = wb.getSheetName(i);
        if (!keepNames.contains(name)) {
            wb.removeSheetAt(i);
        }
    }

    // Save
    wb.write(output);
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output)
        throws IOException {
    // Identify the sheets in both workbooks
    List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB);
    List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB);

    // Process each sheet from the excerpt in turn
    for (int i = 0; i < sheetsToMerge.length; i++) {
        Sheet source = sourceSheets.get(i);
        Sheet dest = destSheets.get(i);//from   ww  w .j a v  a2  s .com

        for (Row srcR : source) {
            for (Cell srcC : srcR) {
                if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA
                        || srcC.getCellType() == Cell.CELL_TYPE_ERROR) {
                    // Don't merge these kinds of cells
                } else {
                    Row destR = dest.getRow(srcR.getRowNum());
                    if (destR == null) {
                        // Newly added row to the excerpt file, skip this
                    } else {
                        Cell destC = destR.getCell(srcC.getColumnIndex());
                        if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                            // Both are empty, don't need to do anything
                        } else {
                            if (destC == null)
                                destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType());

                            // Sync contents
                            if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                                destC.setCellType(Cell.CELL_TYPE_BLANK);
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                destC.setCellValue(srcC.getBooleanCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                destC.setCellValue(srcC.getNumericCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) {
                                destC.setCellValue(srcC.getStringCellValue());
                            }

                            // Sync formatting rules
                            // TODO
                        }
                    }
                }
            }
        }
    }

    // Re-evaluate all the formulas in the destination workbook, now that
    //  we have updated cells in it
    FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();

    // Save the new file
    fullWB.write(output);
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void sheetListing() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        for (String sn : names) {
            wb.createSheet(sn);//from w  w w.  j  a  va  2 s . c  o  m
        }
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        String[] foundNames = excerpter.getSheetNames(tmp);
        assertEquals(names.length, foundNames.length);
        for (int i = 0; i < names.length; i++) {
            assertEquals(names[i], foundNames[i]);
        }
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);/*from w w  w .  j a v a 2  s  .c  om*/
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptRemovesUnUsed() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        // Create some dummy content
        for (String sn : names) {
            Sheet s = wb.createSheet(sn);
            s.createRow(0).createCell(0).setCellValue(sn);
        }//ww  w . j  av a  2s .c  om

        // Excerpt by index
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        int[] excI = new int[] { 0, 1, 2, 4, 5 };
        excerpter.excerpt(excI, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(5, newwb.getNumberOfSheets());
        assertEquals(names[excI[0]], newwb.getSheetName(0));
        assertEquals(names[excI[1]], newwb.getSheetName(1));
        assertEquals(names[excI[2]], newwb.getSheetName(2));
        assertEquals(names[excI[3]], newwb.getSheetName(3));
        assertEquals(names[excI[4]], newwb.getSheetName(4));

        assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue());

        // Excerpt by name
        String[] excN = new String[] { "b", "ccc", "f", "gg" };
        baos = new ByteArrayOutputStream();
        excerpter.excerpt(excN, tmp, baos);

        newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(4, newwb.getNumberOfSheets());
        assertEquals(excN[0], newwb.getSheetName(0));
        assertEquals(excN[1], newwb.getSheetName(1));
        assertEquals(excN[2], newwb.getSheetName(2));
        assertEquals(excN[3], newwb.getSheetName(3));

        assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());

        // Can't excerpt by invalid index
        try {
            excerpter.excerpt(new int[] { 0, 10 }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }

        // Can't excerpt by invalid name
        try {
            excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }
    }
}

From source file:com.rarediscovery.services.logic.Functions.java

/**
 *  FileName,//from w w  w.j  a v  a  2 s  . c  o m
 *  Map<worsksheetName,WorksheetData>
 *  WorksheetData  = columnName[] , dataGrid[][]
 * 
 * , Map<String,String[][]> tabTodataGridMap
 */
public static void saveAsExcelWorkbook(String filename, final Map<String, List<String>> sheetToColumns,
        final Models models, DefaultListModel reportAttributes) {

    Workbook wb = new HSSFWorkbook();
    FileOutputStream fileOut;
    try {
        fileOut = new FileOutputStream(filename);

        //CreationHelper createHelper = wb.getCreationHelper();

        // Create worksheets
        int sheetCount = sheetToColumns.size();
        Sheet[] sheets = new Sheet[sheetCount];
        int i = 0;
        for (String sheetName : sheetToColumns.keySet()) {
            sheets[i] = wb.createSheet(" " + sheetName);
            String[] reportHeaders = sheetToColumns.get(sheetName).toArray(new String[0]);
            addContent(sheets[i], reportHeaders, models, reportAttributes);
            i++;
        }

        /*
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet1.createRow((short)0);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue(1);
                
        // Or do it on one line.
        row.createCell(1).setCellValue(1.2);
        row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
        row.createCell(3).setCellValue(true);
        */

        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();

        Functions.log(" Completed generating excel report ! - " + filename);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex);
    }
}