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(int rowAccessWindowSize)

Source Link

Document

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

Usage

From source file:adams.data.io.output.ExcelStreamingSpreadSheetWriter.java

License:Open Source License

/**
 * Performs the actual writing. The caller must ensure that the writer gets
 * closed.//from  w w w. j  av  a 2 s.co m
 *
 * @param content   the spreadsheet to write
 * @param out      the writer to write the spreadsheet to
 * @return      true if successfully written
 */
@Override
protected boolean doWrite(SpreadSheet[] content, OutputStream out) {
    boolean result;
    SXSSFWorkbook workbook;
    Sheet sheet;
    Row row;
    adams.data.spreadsheet.Row spRow;
    adams.data.spreadsheet.Cell spCell;
    Cell cell;
    int i;
    int n;
    int count;
    CellStyle styleDate;
    CellStyle styleDateTime;
    CellStyle styleTime;
    HashSet<String> names;
    String name;

    result = true;

    try {
        workbook = new SXSSFWorkbook(m_MaxRows);
        styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT);
        styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT);
        styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT);

        count = 0;
        names = new HashSet<>();
        for (SpreadSheet cont : content) {
            if (m_Stopped)
                return false;

            sheet = workbook.createSheet();
            if (cont.getName() != null) {
                name = cont.getName().replace("'", "");
                if (names.contains(name))
                    name += (count + 1);
            } else {
                name = m_SheetPrefix + (count + 1);
            }
            names.add(name);
            workbook.setSheetName(count, name);

            // header
            row = sheet.createRow(0);
            for (i = 0; i < cont.getColumnCount(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(cont.getHeaderRow().getCell(i).getContent());
            }

            // data
            for (n = 0; n < cont.getRowCount(); n++) {
                if (m_Stopped)
                    return false;

                row = sheet.createRow(n + 1);
                spRow = cont.getRow(n);
                for (i = 0; i < cont.getColumnCount(); i++) {
                    cell = row.createCell(i);
                    spCell = spRow.getCell(i);
                    if ((spCell == null) || spCell.isMissing()) {
                        if (m_MissingValue.length() > 0)
                            cell.setCellValue(m_MissingValue);
                        else
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        continue;
                    }

                    if (spCell.isFormula() && !m_OutputAsDisplayed) {
                        cell.setCellFormula(spCell.getFormula().substring(1));
                    } else {
                        if (spCell.isDate()) {
                            cell.setCellValue(spCell.toDate());
                            cell.setCellStyle(styleDate);
                        } else if (spCell.isTime()) {
                            cell.setCellValue(spCell.toTime());
                            cell.setCellStyle(styleTime);
                        } else if (spCell.isDateTime()) {
                            cell.setCellValue(spCell.toDateTime());
                            cell.setCellStyle(styleDateTime);
                        } else if (spCell.isNumeric()) {
                            cell.setCellValue(Utils.toDouble(spCell.getContent()));
                        } else {
                            cell.setCellValue(spCell.getContent());
                        }
                    }
                }
            }

            // next sheet
            count++;
        }

        // save
        workbook.write(out);
    } catch (Exception e) {
        result = false;
        getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e);
    }

    return result;
}

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?//from  w ww .j av a  2s .  c o m
 * @param title ?
 * @param headerList 
 */
private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public Workbook processWorkbook(ITemplateSource itsCurrent, List<Spreadsheet> lstSP, FacesContext context,
        UIWorkbook uiWB) throws IOException, InvalidFormatException, POIException {
    Logger logCurrent = LoggerFactory.getLogger(this.getClass().getCanonicalName());

    InputStream is = itsCurrent.getFileStream();
    // Workbook wbCurrent = WorkbookFactory.create(is);
    // ;//from w w w .ja va 2s . c om
    Workbook wbCurrent1 = WorkbookFactory.create(is);
    Workbook wbCurrent = wbCurrent1;
    is.close();
    if (wbCurrent1 instanceof XSSFWorkbook && uiWB.isUseStreamingModel()) {
        logCurrent.info("Generation SXSSFWorkbook");
        wbCurrent = new SXSSFWorkbook((XSSFWorkbook) wbCurrent1);
    }
    itsCurrent.cleanUP();
    // Processing all Spreadsheets to the File
    logCurrent.finer("Push the Result to the HttpServlet");

    for (Spreadsheet spCurrent : lstSP) {
        processSpreadSheet(spCurrent, wbCurrent, context);
    }
    if (uiWB != null) {
        logCurrent.finer("Post Generation Process");
        uiWB.postGenerationProcess(context, wbCurrent);
    }
    return wbCurrent;
}

From source file:br.com.itfox.test.Excel.java

public void gerarExcel(boolean simple) {
    try {/*from   ww w  .ja v a  2  s .  c  o  m*/
        String ini = "01/01/2015";
        String fim = "22/06/2016";
        String seg = "'21','22','23'";
        String areaOper = "'47','24','23','29','4','18','5','48','10','31','43','35','36','7','33','45','3','32','9','39','13','38','16','44','30','15','2','17','12','6','42','41','34','40','1','19','14','26','22','51','46','49','27','25','8','50','52','28','11','20','37','21'";
        BusinessDelegate bd = new BusinessDelegate();
        String path = "/Users/belchiorpalma/Desktop/template/";
        String pathTemplate = "/Users/belchiorpalma/NetBeansProjects/Quest_Iveco/src/br/com/itfox/generator/";
        InputStream is = null;
        try {
            is = new FileInputStream(pathTemplate + "TemplateGic.xlsx");
        } catch (FileNotFoundException ex) {
            // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }
        //try(InputStream is = GeneratorObjectCollection.class.getResourceAsStream(pathTemplate+"TemplateGic.xlsx"))
        // {
        SimpleDateFormat sdf = new SimpleDateFormat("dd_M_yyyy_hh_mm_ss");
        String date = sdf.format(new Date());

        Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();

        ini = (Utils.dateFormat(ini));
        fim = (Utils.dateFormat(fim));

        List<Gic> gics = bd.selectGic(ini, fim, seg, areaOper);
        int i = 0;
        for (Gic g : gics) {
            Row row = sh.createRow(i);
            for (int cellnum = 0; cellnum < 153; cellnum++) {
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(g.getC_nomeproprietario());
            }
            i++;
        }
        /*
        for (int rownum = 0; rownum < 1000000; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 2; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
        }*/

        FileOutputStream out;
        try {
            out = new FileOutputStream(path + "object_collection_output.xlsx");
            wb.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            //Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }

    } catch (IOException ex) {
        // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    }
}

From source file:cfdi.clases.db.DerbyUtilities.java

License:Open Source License

/**
 * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se
 * haya utilizado en la interface grfica
 * //from  w w w  . jav a 2s  .co  m
 * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE
 * @param nombre nombre del archivo 
 * @param path directorio donde se va a crear el archivo de excel
 * @return the boolean
 */
public boolean exportarExcel(String query, String nombre, String path) {
    Connection connection = null;
    Statement st = null;
    ResultSet rs = null;
    boolean respuesta = false;
    BoneCP connectionPool = null;
    try {
        Class.forName(propiedades.getProperty("DB_DRIVER"));
        // setup the connection pool
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
        config.setUsername(propiedades.getProperty("DB_USER"));
        config.setPassword(propiedades.getProperty("DB_PASSWORD"));
        config.setMinConnectionsPerPartition(5);
        config.setMaxConnectionsPerPartition(10);
        config.setPartitionCount(1);
        connectionPool = new BoneCP(config); // setup the connection pool
        FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx");
        connection = connectionPool.getConnection(); // fetch a connection

        if (connection != null) {
            st = connection.createStatement();
            rs = st.executeQuery(query);
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
            Sheet sheet = workbook.createSheet(nombre);
            int rownum = 0;
            Row row = sheet.createRow(rownum++);
            CellStyle stylec = workbook.createCellStyle();
            stylec.setBorderBottom(CellStyle.BORDER_THIN);
            stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            Font fontc = workbook.createFont();
            fontc.setBoldweight(Font.BOLDWEIGHT_BOLD);
            stylec.setFont(fontc);
            for (int i = 1; i <= count; i++) {
                row.createCell(i).setCellValue(metaData.getColumnName(i));
                row.getCell(i).setCellStyle(stylec);
            }
            while (rs.next()) {
                Row rowh = sheet.createRow(rownum++);
                for (int i = 1; i <= count; i++) {
                    if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT")
                            || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED"))
                        rowh.createCell(i).setCellValue(rs.getInt(i));
                    else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE"))
                        rowh.createCell(i).setCellValue(rs.getDouble(i));
                    else
                        rowh.createCell(i).setCellValue(rs.getString(i));
                }
            }
            /*if(rownum<5000){
            for (int i = 1; i <= count; i++)
                sheet.autoSizeColumn(i); 
            }*/
            try {
                workbook.write(fileOut);
                fileOut.flush();
                fileOut.close();

            } catch (FileNotFoundException e) {
                System.out.println("Error: export 1");
            } catch (IOException e) {
                System.out.println("Error: export 2");
            }
            respuesta = true;
            connectionPool.shutdown();
        }
    } catch (SQLException e) {
        System.out.println("Error: insertDatos 3");
        logger.log(Level.SEVERE, null, e);
    } catch (ClassNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (Exception ex) {
        System.out.println("Error: insertDatos 5");
        logger.log(Level.SEVERE, null, ex);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("Error: insertDatos 4");
                logger.log(Level.SEVERE, null, e);
            }
        }
    }
    return respuesta;
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void createFileAndWorkbook(File exportDir, String exportName) {
    if (excelFile == null && zipFile == null && workbook == null) {
        excelFile = new File(exportDir, exportName + EXTENSION);
        zipFile = new File(exportDir, exportName + EXTENSION + ZIP_EXTENSION);

        workbook = new SXSSFWorkbook(rowAccessWindowSize);

        dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy h:mm"));

        headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor((short) 1);
        headerStyle.setFillBackgroundColor((short) 41);
        headerStyle.setFillPattern((short) 17);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        leftSeparatorStyle = workbook.createCellStyle();
        leftSeparatorStyle.setBorderLeft(CellStyle.BORDER_THIN);
    }/*from  w w w . jav  a  2s  .c  o  m*/
}

From source file:com.catdog.common.util.ExcelReadAndWrite.java

License:Apache License

public void testWorkBook() {
    try {/*from w ww  .  ja  v a 2 s .c  o m*/
        long curr_time = System.currentTimeMillis();

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

        int sheet_num = 3;//?3SHEET
        for (int i = 0; i < sheet_num; i++) {
            Sheet sh = wb.createSheet();
            //?SHEET60000ROW
            for (int rownum = 0; rownum < 60000; rownum++) {
                Row row = sh.createRow(rownum);
                //?10CELL
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }

                //??,?
                if (rownum % rowaccess == 0) {
                    ((SXSSFSheet) sh).flushRows();
                }
            }
        }

        /*?*/
        FileOutputStream os = new FileOutputStream("d:/biggrid.xlsx");
        wb.write(os);
        os.close();

        /**/
        System.out.println(":" + (System.currentTimeMillis() - curr_time) / 1000);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.cms.utils.ExportExcell.java

public ExportExcell(String fileName, String sheetName) throws FileNotFoundException {
    this.fileName = fileName;
    fileOut = new FileOutputStream(fileName);
    wb = new SXSSFWorkbook(500); // keep 100 rows in memory, exceeding rows will be flushed to disk
    wb.setCompressTempFiles(true);/* ww  w.j  a  v  a 2  s .co m*/
    sh = wb.createSheet(sheetName);
}

From source file:com.cms.utils.ExportExcell.java

public ExportExcell(String fileName) throws FileNotFoundException {
    this.fileName = fileName;
    fileOut = new FileOutputStream(fileName);
    wb = new SXSSFWorkbook(500); // keep 100 rows in memory, exceeding rows will be flushed to disk
    wb.setCompressTempFiles(true);/*w  w  w  . jav  a 2 s  .c o  m*/
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * ???/*from   www  . ja v  a 2  s.  com*/
 * excel 2007 ?sheet1048576
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2007(final User user, final String contextRootPath, final Searchable searchable) {

    int rowAccessWindowSize = 1000; //???
    int perSheetRows = 100000; //?sheet 10w?
    int totalRows = 0; //
    Long maxId = 0L;//??id 

    String fileName = generateFilename(user, contextRootPath, "xlsx");
    File file = new File(fileName);
    BufferedOutputStream out = null;
    SXSSFWorkbook wb = null;
    try {
        long beginTime = System.currentTimeMillis();

        wb = new SXSSFWorkbook(rowAccessWindowSize);
        wb.setCompressTempFiles(true);//?gzip

        while (true) {

            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;

            Page<ExcelData> page = null;

            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            if (!page.hasNext()) {
                break;
            }
        }
        out = new BufferedOutputStream(new FileOutputStream(file));
        wb.write(out);

        IOUtils.closeQuietly(out);

        if (needCompress(file)) {
            fileName = compressAndDeleteOriginal(fileName);
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    } finally {
        // ?
        wb.dispose();
    }
}