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

Introduction

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

Prototype

public SXSSFWorkbook(int rowAccessWindowSize) 

Source Link

Document

Construct an empty workbook and specify the window for row access.

Usage

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

public static boolean createExcelExport2(final List<OutgoingLog> topups,
        final HashMap<String, String> networkHash, final HashMap<String, String> statusHash,
        final String delimiter, final String excelFile) {
    boolean success = true;

    int rowCount = 0; // To keep track of the row that we are on

    Row row;/*  w  w  w .  ja  v a  2 s  .  com*/
    Map<String, CellStyle> styles;

    SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk
    // Each line of the file is approximated to be 200 bytes in size, 
    // therefore 5000 lines are approximately 1 MB in memory
    // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk

    Sheet sheet = wb.createSheet("Airtime Topup");
    styles = createStyles(wb);

    PrintSetup printSetupTopup = sheet.getPrintSetup();
    printSetupTopup.setLandscape(true);
    sheet.setFitToPage(true);

    // Set up the heading to be seen in the Excel sheet
    row = sheet.createRow(rowCount);

    Cell titleCell;

    row.setHeightInPoints(45);
    titleCell = row.createCell(0);
    titleCell.setCellValue("Airtime Topups");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
    titleCell.setCellStyle(styles.get("title"));

    rowCount++;
    row = sheet.createRow(rowCount);
    row.setHeightInPoints(12.75f);

    for (int i = 0; i < TOPUP_TITLES.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(TOPUP_TITLES[i]);
        cell.setCellStyle(styles.get("header"));
    }

    rowCount++;

    FileUtils.deleteQuietly(new File(excelFile));
    FileOutputStream out;

    try {
        FileUtils.touch(new File(excelFile));

        Cell cell;

        for (OutgoingLog topup : topups) {
            row = sheet.createRow(rowCount);

            cell = row.createCell(0);
            cell.setCellValue(topup.getUuid());

            //cell = row.createCell(1);
            //cell.setCellValue(topup.getMessageid());

            cell = row.createCell(2);
            cell.setCellValue(topup.getDestination());

            cell = row.createCell(3);
            cell.setCellValue(networkHash.get(topup.getOrigin()));

            cell = row.createCell(4);
            cell.setCellValue(statusHash.get(topup.getMessage()));

            cell = row.createCell(5);
            cell.setCellValue(topup.getLogTime().toString());

            rowCount++;
        }

        out = new FileOutputStream(excelFile);
        wb.write(out);
        out.close();

    } catch (IOException e) {
        logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups.");
        logger.error(ExceptionUtils.getStackTrace(e));
        success = false;
    }

    wb.dispose(); // dispose of temporary files backup of this workbook on disk

    return success;
}

---

From source file:mask.logging.XLSLogger.java

@Override
public void start() {

    logFileName = this.getClass().getSimpleName()
            + new SimpleDateFormat("yyyyMMdd_HHmmss").format(Calendar.getInstance().getTime());
    wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in 
    wb.setCompressTempFiles(true);//from  w  ww  . j  av  a  2s.  co  m
}

---

From source file:net.sourceforge.squirrel_sql.fw.gui.action.exportData.DataExportExcelWriter.java

License:Open Source License

/**
 * @throws java.io.IOException//from   ww  w. j  a v a2s  .c o m
 * @see
 * net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#beforeWorking()
 */
@Override
protected void beforeWorking(File file) throws IOException {
    if (_exportFormat == ExportFormat.EXPORT_FORMAT_XLS) {
        this.workbook = new HSSFWorkbook(); // See https://gist.github.com/madan712/3912272
    } else {
        this.workbook = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    }
    this.file = file;
    this.sheet = workbook.createSheet("Squirrel SQL Export");
}

---

From source file:org.apache.jena.examples.ExampleARQ_06.java

License:Apache License

public static void main(String[] args) throws IOException {
    FileManager.get().addLocatorClassLoader(ExampleARQ_01.class.getClassLoader());
    Model model = FileManager.get().loadModel("data/data.ttl");
    Query query = QueryFactory.create("SELECT * WHERE { ?s ?p ?o }");
    QueryExecution qexec = QueryExecutionFactory.create(query, model);

    FileOutputStream out = new FileOutputStream("target/sxssf.xlsx");
    Workbook wb = new SXSSFWorkbook(100);
    Sheet sh = wb.createSheet();//  ww w .j  a v  a2  s  . c om

    int rows = 0;
    int columns = 0;
    try {
        ResultSet resultSet = qexec.execSelect();
        List<String> varNames = resultSet.getResultVars();
        List<Var> vars = new ArrayList<Var>(varNames.size());

        // first row with var names
        Row row = sh.createRow(rows++);
        for (String varName : varNames) {
            Var var = Var.alloc(varName);
            Cell cell = row.createCell(columns++);
            cell.setCellValue(var.toString());
            vars.add(var);
        }

        // other rows with bindings
        while (resultSet.hasNext()) {
            Binding bindings = resultSet.nextBinding();
            row = sh.createRow(rows++);
            columns = 0;
            for (Var var : vars) {
                Node n = bindings.get(var);
                if (n != null) {
                    Cell cell = row.createCell(columns++);
                    String value = FmtUtils.stringForNode(n, (SerializationContext) null);
                    cell.setCellValue(value);
                }
            }
        }
    } finally {
        qexec.close();
    }

    wb.write(out);
    out.close();
}

---

From source file:org.apache.metamodel.excel.ExcelUpdateCallback.java

License:Apache License

protected Workbook getWorkbook(boolean streamingAllowed) {
    if (_workbook == null || (!streamingAllowed && _workbook instanceof SXSSFWorkbook)) {
        if (_workbook != null) {
            ExcelUtils.writeAndCloseWorkbook(_dataContext, _workbook);
        }//from   w w  w  .  j  a  v a  2s.c o m
        _workbook = ExcelUtils.readWorkbook(_dataContext);
        if (streamingAllowed && _workbook instanceof XSSFWorkbook) {
            _workbook = new SXSSFWorkbook((XSSFWorkbook) _workbook);
        }
    }
    return _workbook;
}

---

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

public static Workbook readWorkbook(Resource resource) {
    if (!resource.isExists()) {
        // resource does not exist- create a blank workbook
        if (isXlsxFile(resource)) {
            return new SXSSFWorkbook(1000);
        } else {//from w  w  w . j  a  va  2s .  c  o m
            return new HSSFWorkbook();
        }
    }

    if (resource instanceof FileResource) {
        final File file = ((FileResource) resource).getFile();
        try {
            return WorkbookFactory.create(file);
        } catch (Exception e) {
            logger.error("Could not open workbook", e);
            throw new IllegalStateException("Could not open workbook", e);
        }
    }

    return resource.read(new Func<InputStream, Workbook>() {
        @Override
        public Workbook eval(InputStream inputStream) {
            try {
                return WorkbookFactory.create(inputStream);
            } catch (Exception e) {
                logger.error("Could not open workbook", e);
                throw new IllegalStateException("Could not open workbook", e);
            }
        }
    });
}

---

From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java

License:Apache License

@Override
public String exportDataSetExcel(DataSet dataSet) {
    // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
    if (dataSet == null)
        throw new IllegalArgumentException("Null dataSet specified!");
    int columnCount = dataSet.getColumns().size();
    int rowCount = dataSet.getRowCount() + 1; //Include header row;
    int row = 0;//from   w  w  w.j a  v a  2  s . c  o m

    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sh = wb.createSheet("Sheet 1");

    // General setup
    sh.setDisplayGridlines(true);
    sh.setPrintGridlines(false);
    sh.setFitToPage(true);
    sh.setHorizontallyCenter(true);
    PrintSetup printSetup = sh.getPrintSetup();
    printSetup.setLandscape(true);

    // Create header
    Row header = sh.createRow(row++);
    header.setHeightInPoints(20f);
    for (int i = 0; i < columnCount; i++) {
        Cell cell = header.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(dataSet.getColumnByIndex(i).getId());
    }

    // Create data rows
    for (; row < rowCount; row++) {
        Row _row = sh.createRow(row);
        for (int cellnum = 0; cellnum < columnCount; cellnum++) {
            Cell cell = _row.createCell(cellnum);
            Object value = dataSet.getValueAt(row - 1, cellnum);
            if (value instanceof Short || value instanceof Long || value instanceof Integer
                    || value instanceof BigInteger) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("integer_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("decimal_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("date_cell"));
                cell.setCellValue((Date) value);
            } else if (value instanceof Interval) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(((Interval) value).getName());
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(value.toString());
            }
        }
    }

    // Adjust column size
    for (int i = 0; i < columnCount; i++) {
        sh.autoSizeColumn(i);
    }

    Path tempExcelFilePath = null;
    try {
        tempExcelFilePath = ioService.createTempFile("export", "xlsx", null);
        OutputStream os = Files.newOutputStream(tempExcelFilePath);
        wb.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {
        log.error("Error in excel export: ", e);
    }

    // Dispose of temporary files backing this workbook on disk
    if (!wb.dispose())
        log.warn("Could not dispose of temporary file associated to data export!");

    return tempExcelFilePath.toString();
}

---

From source file:org.dashbuilder.dataset.backend.DataSetExportServicesImpl.java

License:Apache License

@Override
public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) {
    try {/*from   w ww.j  a v a2 s  .c  o m*/
        // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
        if (dataSet == null)
            throw new IllegalArgumentException("Null dataSet specified!");
        int columnCount = dataSet.getColumns().size();
        int rowCount = dataSet.getRowCount() + 1; //Include header row;
        int row = 0;

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Map<String, CellStyle> styles = createStyles(wb);
        Sheet sh = wb.createSheet("Sheet 1");

        // General setup
        sh.setDisplayGridlines(true);
        sh.setPrintGridlines(false);
        sh.setFitToPage(true);
        sh.setHorizontallyCenter(true);
        PrintSetup printSetup = sh.getPrintSetup();
        printSetup.setLandscape(true);

        // Create header
        Row header = sh.createRow(row++);
        header.setHeightInPoints(20f);
        for (int i = 0; i < columnCount; i++) {
            Cell cell = header.createCell(i);
            cell.setCellStyle(styles.get("header"));
            cell.setCellValue(dataSet.getColumnByIndex(i).getId());
        }

        // Create data rows
        for (; row < rowCount; row++) {
            Row _row = sh.createRow(row);
            for (int cellnum = 0; cellnum < columnCount; cellnum++) {
                Cell cell = _row.createCell(cellnum);
                Object value = dataSet.getValueAt(row - 1, cellnum);
                if (value instanceof Short || value instanceof Long || value instanceof Integer
                        || value instanceof BigInteger) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("integer_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("decimal_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("date_cell"));
                    cell.setCellValue((Date) value);
                } else if (value instanceof Interval) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(((Interval) value).getName());
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(value.toString());
                }
            }
        }

        // Adjust column size
        for (int i = 0; i < columnCount; i++) {
            sh.autoSizeColumn(i);
        }

        String tempXlsFile = uuidGenerator.newUuid() + ".xlsx";
        Path tempXlsPath = gitStorage.createTempFile(tempXlsFile);
        OutputStream os = Files.newOutputStream(tempXlsPath);
        wb.write(os);
        os.flush();
        os.close();

        // Dispose of temporary files backing this workbook on disk
        if (!wb.dispose()) {
            log.warn("Could not dispose of temporary file associated to data export!");
        }
        return Paths.convert(tempXlsPath);
    } catch (Exception e) {
        throw exceptionManager.handleException(e);
    }
}

---

From source file:org.dashbuilder.dataset.service.DataSetExportServicesImpl.java

License:Apache License

SXSSFWorkbook dataSetToWorkbook(DataSet dataSet) {
    // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
    if (dataSet == null) {
        throw new IllegalArgumentException("Null dataSet specified!");
    }//www  .  j  a v  a  2 s.  c  om
    int columnCount = dataSet.getColumns().size();
    int rowCount = dataSet.getRowCount() + 1; //Include header row;
    int row = 0;

    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Map<String, CellStyle> styles = createStyles(wb);
    SXSSFSheet sh = wb.createSheet("Sheet 1");

    // General setup
    sh.setDisplayGridlines(true);
    sh.setPrintGridlines(false);
    sh.setFitToPage(true);
    sh.setHorizontallyCenter(true);
    sh.trackAllColumnsForAutoSizing();
    PrintSetup printSetup = sh.getPrintSetup();
    printSetup.setLandscape(true);

    // Create header
    Row header = sh.createRow(row++);
    header.setHeightInPoints(20f);
    for (int i = 0; i < columnCount; i++) {
        Cell cell = header.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(dataSet.getColumnByIndex(i).getId());
    }

    // Create data rows
    for (; row < rowCount; row++) {
        Row _row = sh.createRow(row);
        for (int cellnum = 0; cellnum < columnCount; cellnum++) {
            Cell cell = _row.createCell(cellnum);
            Object value = dataSet.getValueAt(row - 1, cellnum);
            if (value instanceof Short || value instanceof Long || value instanceof Integer
                    || value instanceof BigInteger) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellStyle(styles.get("integer_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellStyle(styles.get("decimal_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(styles.get("date_cell"));
                cell.setCellValue((Date) value);
            } else if (value instanceof Interval) {
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(styles.get(TEXT_CELL));
                cell.setCellValue(((Interval) value).getName());
            } else {
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(styles.get(TEXT_CELL));
                String val = value == null ? "" : value.toString();
                cell.setCellValue(val);
            }
        }
    }

    // Adjust column size
    for (int i = 0; i < columnCount; i++) {
        sh.autoSizeColumn(i);
    }
    return wb;
}

---

From source file:org.fl.modules.excel.poi.exportExcel.multi.SXSSFWorkBookOperation.java

License:Apache License

/**
 *  SXSSFWorkBookUtil./*from www. j  a v a 2 s  .  c o m*/
 */
public SXSSFWorkBookOperation() {
    // TODO Auto-generated constructor stub

    /* keep 100 rowsin memory,exceeding rows will be flushed to disk */
    wb = new SXSSFWorkbook(rowaccess);
    wb.setCompressTempFiles(false);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• »