Example usage for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook

List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.streaming.*
  5. SXSSFWorkbook
  6. SXSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook.

Prototype

public SXSSFWorkbook()

Source Link

Document

Construct a new workbook with default row window size

Usage

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

/**
 * Generates the Excel report as a temp file and returns the temp file.
 * //w w  w .j  av  a 2  s. c o m
 * @return File
 * @exception Exception
 */
private void generateReport(HttpServletRequest p_request, HttpServletResponse p_response, MyData p_data)
        throws Exception {
    bundle = PageHandler.getBundle(p_request.getSession());
    String EMEA = CompanyWrapper.getCurrentCompanyName();
    s_logger.debug("generateReport---, company name: " + EMEA);

    Workbook p_workbook = new SXSSFWorkbook();
    boolean recalculateFinishedWorkflow = false;
    String recalcParam = p_request.getParameter("recalc");
    if (recalcParam != null && recalcParam.length() > 0) {
        recalculateFinishedWorkflow = java.lang.Boolean.valueOf(recalcParam).booleanValue();
    }

    // get all the jobs that were originally imported with the wrong project
    // the users want to pretend that these jobs are in this project
    getJobsInWrongProject(p_data);
    HashMap projectMap = getProjectData(p_request, p_response, recalculateFinishedWorkflow, p_data);

    if (projectMap != null) {
        p_data.dellSheet = p_workbook.createSheet(EMEA + " " + bundle.getString("lb_matches"));
        p_data.tradosSheet = p_workbook.createSheet(bundle.getString("jobinfo.tradosmatches"));
        addTitle(p_workbook, p_data.dellSheet);
        addHeaderForDellMatches(p_workbook, p_data);
        addTitle(p_workbook, p_data.tradosSheet);
        addHeaderForTradosMatches(p_workbook, p_data);
        IntHolder row = new IntHolder(4);
        writeProjectDataForDellMatches(p_workbook, projectMap, row, p_data);
        row = new IntHolder(4);
        writeProjectDataForTradosMatches(p_workbook, projectMap, row, p_data);

        Sheet paramsSheet = p_workbook.createSheet(bundle.getString("lb_criteria"));
        writeParamsSheet(p_workbook, paramsSheet, p_data, p_request);

        ServletOutputStream out = p_response.getOutputStream();
        p_workbook.write(out);
        out.close();
        ((SXSSFWorkbook) p_workbook).dispose();

        // Set ReportsData.
        ReportHelper.setReportsData(userId, m_jobIDS, getReportType(), 100, ReportsData.STATUS_FINISHED);
    }
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Return the session report as an Excel file
 * //from  w  w w.j a  v a2 s .  com
 * @param sessionData
 * @return
 */
private ByteArrayOutputStream getSessionReportAsExcel(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData) throws AgileBaseException, IOException, SQLException {
    BaseReportInfo report = sessionData.getReport();
    if (report == null) {
        throw new ObjectNotFoundException("No report found in the session");
    }
    // create Excel spreadsheet
    Workbook workbook = new SXSSFWorkbook();
    // the pane 2 report
    String reportName = report.getReportName();
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    reportName = reportName.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    Sheet reportSheet;
    try {
        reportSheet = workbook.createSheet(reportName);
    } catch (IllegalArgumentException iaex) {
        reportSheet = workbook.createSheet(reportName + " " + report.getInternalReportName());
    }
    int rowNum = 0;
    // header
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Row row = reportSheet.createRow(rowNum);
    int columnNum = 0;
    Set<ReportFieldInfo> reportFields = report.getReportFields();
    for (ReportFieldInfo reportField : reportFields) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(reportField.getFieldName());
        cell.setCellStyle(boldCellStyle);
        BaseField field = reportField.getBaseField();
        if (field.equals(field.getTableContainingField().getPrimaryKey())) {
            reportSheet.setColumnHidden(columnNum, true);
        }
        columnNum++;
    }
    // data
    rowNum++;
    DataManagementInfo dataManagement = this.databaseDefn.getDataManagement();
    List<DataRowInfo> reportDataRows = dataManagement.getReportDataRows(company, report,
            sessionData.getReportFilterValues(), false, sessionData.getReportSorts(), -1, QuickFilterType.AND,
            false);
    String fieldValue = "";
    boolean defaultReport = (report.equals(report.getParentTable().getDefaultReport()));
    for (DataRowInfo dataRow : reportDataRows) {
        Map<BaseField, DataRowFieldInfo> dataRowFieldMap = dataRow.getDataRowFields();
        row = reportSheet.createRow(rowNum);
        columnNum = 0;
        for (ReportFieldInfo reportField : reportFields) {
            BaseField field = reportField.getBaseField();
            if (field instanceof TextField) {
                fieldValue = dataRowFieldMap.get(field).getKeyValue();
            } else {
                fieldValue = dataRowFieldMap.get(field).getDisplayValue();
            }
            if (!fieldValue.equals("")) {
                Cell cell;
                DatabaseFieldType dbFieldType = field.getDbType();
                if ((defaultReport) && (field instanceof RelationField)) {
                    dbFieldType = ((RelationField) field).getDisplayField().getDbType();
                }
                switch (dbFieldType) {
                case FLOAT:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Double.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                    }
                    break;
                case INTEGER:
                case SERIAL:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Integer.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        logger.debug(nfex.toString() + ": value " + fieldValue.replace(",", ""));
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                        logger.debug("Successfully set string instead");
                    }
                    break;
                case VARCHAR:
                default:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(Helpers.unencodeHtml(fieldValue));
                    break;
                }
            }
            columnNum++;
        }
        rowNum++;
    }
    // Export info worksheet
    addReportMetaDataWorksheet(company, user, sessionData, report, workbook);
    // one worksheet for each of the report summaries
    for (ChartInfo savedChart : report.getSavedCharts()) {
        this.addSummaryWorksheet(company, sessionData, savedChart, workbook);
    }
    // the default summary
    ChartInfo reportSummary = report.getChart();
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    if ((groupings.size() > 0) || (aggregateFunctions.size() > 0)) {
        this.addSummaryWorksheet(company, sessionData, reportSummary, workbook);
    }
    // write to output
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    workbook.write(outputStream);
    return outputStream;
}

From source file:com.hauldata.dbpa.file.book.XlsxTargetBook.java

License:Apache License

@Override
public void create() throws IOException {

    out = new FileOutputStream(getName());
    book = new SXSSFWorkbook();

    cellStyles = new CellStyle[XlsxCellStyle.values().length];
    createCellStyle(XlsxCellStyle.DATE, dateFormatString);
    createCellStyle(XlsxCellStyle.DATETIME, datetimeFormatString);
    createCellStyle(XlsxCellStyle.INTEGER, integerFormatString);
    createCellStyle(XlsxCellStyle.BIG_INTEGER, bigIntegerFormatString);
    createCellStyle(XlsxCellStyle.TWO_DECIMAL, twoDecimalFormatString);
    createCellStyle(XlsxCellStyle.BIG_TWO_DECIMAL, bigTwoDecimalFormatString);
    createCellStyle(XlsxCellStyle.TWO_DECIMAL_ONLY, twoDecimalOnlyFormatString);
    createCellStyle(XlsxCellStyle.FOUR_DECIMAL, fourDecimalFormatString);
    createCellStyle(XlsxCellStyle.BIG_FOUR_DECIMAL, bigFourDecimalFormatString);
    createCellStyle(XlsxCellStyle.FOUR_DECIMAL_ONLY, fourDecimalOnlyFormatString);
    createCellStyle(XlsxCellStyle.OTHER_DECIMAL, otherDecimalFormatString);
    createCellStyle(XlsxCellStyle.BIG_OTHER_DECIMAL, bigOtherDecimalFormatString);
    createCellStyle(XlsxCellStyle.OTHER_DECIMAL_ONLY, otherDecimalOnlyFormatString);
}

From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStream.java

License:Apache License

/**
 * Reads XSSF (XLXS) format excel file using Apache POI streaming SXSSF API.
 *
 * @param xlsxFile//  ww  w.  jav a  2s.  com
 *            excel XSSF format file to read
 *
 * @throws IOException
 *             if excel file or workbook can't be read
 * @throws SAXException
 *             if file contained XML reading fails
 * @throws OpenXML4JException
 *             if file contained XML reading fails
 */
protected void readXLXS(File xlsxFile) throws IOException, SAXException, OpenXML4JException {
    try (OPCPackage xlsxPackage = OPCPackage.open(xlsxFile, PackageAccess.READ)) {
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sIter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sIter.hasNext()) {
            try (InputStream sStream = sIter.next()) {
                String sheetName = sIter.getSheetName();
                boolean match = sheetNameMatcher == null || sheetNameMatcher.matcher(sheetName).matches();
                if (!match) {
                    continue;
                }

                SXSSFSheet sheet = new SXSSFSheet(new SXSSFWorkbook(), null);
                processSXSSFSheet(styles, strings, new XLSXSheetContentHandler(this, sheet), sStream);
            }
        }
    }
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

public static void writeFile(String directory, String fileName, Map<String, List<List<ExcelCell>>> sheets,
        int headerRowHeight, int commentRowHeight) {

    if (StringUtils.isNotBlank(directory) && StringUtils.isNotBlank(fileName) && sheets != null
            && !sheets.isEmpty()) {

        SXSSFWorkbook workbook = new SXSSFWorkbook();

        Font invisibleFont = workbook.createFont();

        for (Entry<String, List<List<ExcelCell>>> entry : sheets.entrySet()) {
            // TODO: remove and logging
            // log.info("writeFile","Started writing sheet: "+entry.getKey());

            SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(entry.getKey());
            int totalColumn = 0;
            if (entry.getValue() != null && !entry.getValue().isEmpty()) {
                int rowNumber = 0;
                Font dataFont = null;
                for (List<ExcelCell> rows : entry.getValue()) {
                    // Row row = sheet.getRow(rowNumber)!=null ? sheet.getRow(rowNumber) : rowMap.get(rowNumber);
                    Row row = sheet.createRow(rowNumber);
                    int rowHeight = rowNumber == 0 ? headerRowHeight : commentRowHeight;
                    if (rowNumber == 0 || rowNumber == 1) {
                        if (rowHeight > 0) {
                            row.setHeight((short) rowHeight);
                        }//from www .j  ava 2 s .c  o m
                        addDataValidation(rowNumber, sheet);
                    }
                    rowNumber++;
                    if (rows != null && !rows.isEmpty()) {
                        int cellNum = 0;
                        Font font = null;
                        if (rowNumber > 3 && dataFont != null) {
                            font = dataFont;
                        } else {
                            font = workbook.createFont();
                            dataFont = font;
                        }
                        // as each row requires different syle with separate font
                        Map<IndexedColors, CellStyle> s_cellStyle = new HashMap<IndexedColors, CellStyle>();
                        for (ExcelCell cellValue : rows) {
                            Cell cell = row.createCell(cellNum);
                            updateCell(cell, cellValue, s_cellStyle, workbook, font, invisibleFont);
                            ++cellNum;
                        }
                        totalColumn = cellNum;
                    }
                    if (rowNumber == 2) {/*
                                          * auto size after DOCUMENTATION-ROW (row=2) so, we don't have to do
                                          * multiple times
                                          */
                        autoSize(sheet, totalColumn, false);
                        // rowMap = createRows(workbook, sheet, rowNumber+1, excelConfig.getMaxInputRows());
                    }
                }

            }
            autoSize(sheet, totalColumn, true);
        }

        // addMetaSheet(workbook);

        writeWorkBook(directory, fileName, workbook);
    }

}

From source file:com.qihang.winter.poi.excel.ExcelExportUtil.java

License:Apache License

/**
 * @param entity/*  w  w  w.ja  v a  2 s  .  co  m*/
 *            
 * @param pojoClass
 *            ExcelClass
 * @param dataSet
 *            Excel?List
 */
public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
    Workbook workbook;
    if (com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF.equals(entity.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new com.qihang.winter.poi.excel.export.ExcelExportServer().createSheet(workbook, entity, pojoClass,
            dataSet);
    return workbook;
}

From source file:com.qihang.winter.poi.excel.ExcelExportUtil.java

License:Apache License

/**
 * ?MapExcel//from w  w w.  j  av a2  s  . c o  m
 * @param entity
 *            
 * @param pojoClass
 *            ExcelClass
 * @param dataSet
 *            Excel?List
 */
public static Workbook exportExcel(ExportParams entity,
        List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> entityList,
        Collection<? extends Map<?, ?>> dataSet) {
    Workbook workbook;
    if (com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF.equals(entity.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new com.qihang.winter.poi.excel.export.ExcelExportServer().createSheetForMap(workbook, entity, entityList,
            dataSet);
    return workbook;
}

From source file:com.romeikat.datamessie.core.base.util.ExcelSheet.java

License:Open Source License

public ExcelSheet(final String sheetname) {
    this(new SXSSFWorkbook(), sheetname);
}

From source file:com.runwaysdk.dataaccess.io.excel.ExcelUtil.java

License:Open Source License

public static Workbook createWorkbook(Workbook workbook) throws IOException {
    if (workbook instanceof XSSFWorkbook) {
        return new XSSFWorkbook();
    } else if (workbook instanceof SXSSFWorkbook) {
        return new SXSSFWorkbook();
    } else if (workbook instanceof HSSFWorkbook) {
        return new HSSFWorkbook();
    }//from   w  w  w.  j  av a 2  s  . c  o  m

    throw new FileReadException("Unknown workbook type [" + workbook.getClass().getSimpleName() + "]", null);
}

From source file:de.jpaw.bonaparte.poi.ExcelSXComposer.java

License:Apache License

public ExcelSXComposer() {
    super(new SXSSFWorkbook());
}