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:de.jpaw.bonaparte.poi.GenericExcelComposer.java

License:Apache License

public GenericExcelComposer(ExcelFormat fmt) {
    super(fmt == ExcelFormat.XLSX_STREAMING ? new SXSSFWorkbook()
            : fmt == ExcelFormat.XLSX ? new XSSFWorkbook() : new HSSFWorkbook());
    this.fmt = fmt;
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporter.java

License:Apache License

public XlsxExporter(ExecutorService executorService) {
    workbook = new SXSSFWorkbook();
    workbook.setCompressTempFiles(true);
    this.executorService = executorService;
    provider = new ColumnProvider(CDI.current().select(DependencyGraph.class).get());
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-1]  ? ? :   ?? ?//from   w w  w . jav a 2  s  .  co m
 */
@Test
public void testWriteExcelFile() throws Exception {

    try {
        log.debug("testWriteExcelFile start....");

        String sheetName1 = "first sheet";
        String sheetName2 = "second sheet";
        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFile.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));
            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        wb.createSheet(sheetName1);
        wb.createSheet(sheetName2);
        wb.createSheet();

        //  ? ?
        SXSSFWorkbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        // ?  ?
        assertTrue(EgovFileUtil.isExistsFile(sb.toString()));

        // ? Sheet ? ?
        assertEquals(sheetName1, tmp.getSheetName(0));
        assertEquals(sheetName2, tmp.getSheetName(1));

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFile end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from  w  w  w .  j a v a2 s.  c  o  m
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        log.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
        assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        log.debug("getFillPattern : " + cs1.getFillPattern());
        assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
        log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:eu.alpinweiss.filegen.service.impl.GenerateXlsxFileServiceImpl.java

License:Apache License

public void generateExcel(String excelFilename, int rowCount, List<FieldDefinition> fieldDefinitionList,
        int sheetCount) {

    long startTime = new Date().getTime();

    outputWriterHolder.writeValueInLine("Excel data generation started");

    Workbook wb = new SXSSFWorkbook();

    try {//from   w w w  .  jav a2 s.c  om
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
        cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f.setFontHeightInPoints((short) 12);
        cs.setFont(f);

        SXSSFSheet sheet1 = (SXSSFSheet) wb.createSheet("dataSheet");

        int columnCount = fieldDefinitionList.size();

        Map<Integer, Input2TableInfo> input2TableInfoMap = new LinkedHashMap<>(columnCount);

        for (int i = 0; i < columnCount; i++) {
            Input2TableInfo input2TableInfo = new Input2TableInfo();
            FieldDefinition fieldDefinition = fieldDefinitionList.get(i);
            input2TableInfo.setFieldText(fieldDefinition.getFieldName());
            input2TableInfo.setFieldDefinition(fieldDefinition);
            input2TableInfo.initCellStyle(wb);
            input2TableInfo.initGenerator();
            input2TableInfoMap.put(i, input2TableInfo);
        }

        if (sheetCount > 1) {
            CountDownLatch startSignal = new CountDownLatch(1);
            CountDownLatch doneSignal;

            doneSignal = new CountDownLatch(sheetCount);

            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            SheetProcessor stringProcessorSheet1 = new SheetProcessor(parameterVault, startSignal, doneSignal,
                    cs, sheet1, columnCount, input2TableInfoMap, outputWriterHolder);
            new Thread(stringProcessorSheet1, "Processor-" + sheetCount).start();

            for (int i = 0; i < sheetCount - 1; i++) {
                SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("dataSheet_" + i);
                ParameterVault parameterVaultRest = new DefaultParameterVault(i + 1, rowCount);
                SheetProcessor stringProcessor = new SheetProcessor(parameterVaultRest, startSignal, doneSignal,
                        cs, sheet, columnCount, input2TableInfoMap, outputWriterHolder);
                new Thread(stringProcessor, "Processor-" + i).start();
            }

            startSignal.countDown();
            doneSignal.await();
        } else {
            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            new SheetProcessor(outputWriterHolder).generateSheetData(parameterVault, cs, sheet1, columnCount,
                    input2TableInfoMap);
        }

        outputWriterHolder.writeValueInLine("Excel data generation finished.");
        long generationTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((generationTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Writing to file.");

        FileOutputStream fileOut = new FileOutputStream(excelFilename.trim());

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

        long writeTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((writeTime - generationTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Total time used " + ((writeTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Done");
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            wb.close();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

}

From source file:excelmasivo.ExcelMasivo.java

/**
 * @param args the command line arguments
 *///from  w  w w  . j  a v a 2  s  .c  om
public static void main(String[] args) {
    String driver = "oracle.jdbc.OracleDriver";
    String user = "DRKL";
    String pass = "DRKL";
    String url = "jdbc:oracle:thin:@localhost:1521:XE";
    String query = "SELECT * FROM PRODUCTOS ORDER BY TO_NUMBER(SUBSTR(CODIGO_PRODUCTO,7))";
    Connection con;
    Statement st;
    ResultSet rs;
    ResultSetMetaData rsm;

    SXSSFWorkbook libro = new SXSSFWorkbook();
    SXSSFSheet hoja = libro.createSheet("Reporte");
    SXSSFRow fila;
    SXSSFCell celda;
    FileOutputStream out;
    int x = 0;

    CellStyle cs = libro.createCellStyle();
    cs.getFillForegroundColor();
    Font f = libro.createFont();
    //f.setBoldweight(Font.BOLDWEIGHT_BOLD);
    f.setFontHeightInPoints((short) 12);
    cs.setFont(f);

    try {
        Class.forName(driver);
        con = DriverManager.getConnection(url, user, pass);
        st = con.createStatement();
        rs = st.executeQuery(query);
        rsm = rs.getMetaData();
        while (rs.next()) {
            //crear la fila
            fila = hoja.createRow(x++);
            for (int i = 1; i <= rsm.getColumnCount(); i++) {
                //recorrer las columnas
                celda = fila.createCell(i);
                celda.setCellStyle(cs);
                celda.setCellValue(rs.getString(i));
                //System.out.print(rs.getString(i));
            }
            //System.out.println();                
            if (x % 50000 == 0) {
                System.out.println("Se procesaron:" + x);
            }
        }

        out = new FileOutputStream(new File("D:\\java\\Productos_" + GregorianCalendar.MILLISECOND + ".xlsx"));
        libro.write(out);
        out.close();
        System.out.println("Archivo generado con exito");
    } catch (ClassNotFoundException | SQLException | FileNotFoundException ex) {
        Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:exporter.ExcelExporter.java

public static void writeXlsx(OutputStream outputStream, String dbResource, String queryString, String... args)
        throws NamingException {
    Context initContext = null;// ww w  . j  a  v  a2 s.  com
    try {
        initContext = new InitialContext();
    } catch (NamingException ex) {
        Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex);
    }
    Context envContext = (Context) initContext.lookup("java:/comp/env/");
    DataSource datasource = (DataSource) envContext.lookup(dbResource);
    try {
        PreparedStatement ps = datasource.getConnection().prepareStatement(queryString);
        ResultSet rSet = ps.executeQuery();
        ResultSetMetaData rsMetaData = rSet.getMetaData();
        int columnCount = rsMetaData.getColumnCount();
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        SXSSFSheet sheet = (SXSSFSheet) workBook.createSheet("cics");
        String currentLine = null;
        int rowNum = 0;
        int types[] = new int[columnCount];
        Row intestazione = sheet.createRow(rowNum);
        for (int i = 0; i < columnCount; i++) {
            intestazione.createCell(i).setCellValue(rsMetaData.getColumnLabel(i + 1));
            types[i] = rsMetaData.getColumnType(i + 1);
        }
        rowNum++;
        while (rSet.next()) {
            rowNum++;
            Row currentRow = sheet.createRow(rowNum);
            for (int k = 0; k < columnCount; k++) {
                switch (types[k]) {
                case Types.INTEGER:
                    currentRow.createCell(k).setCellValue(rSet.getInt(k + 1));
                    break;
                case Types.FLOAT:
                    currentRow.createCell(k).setCellValue(rSet.getFloat(k + 1));
                    break;
                case Types.BIGINT:
                    currentRow.createCell(k).setCellValue(rSet.getInt(k + 1));
                    break;

                case Types.DOUBLE:
                    currentRow.createCell(k).setCellValue(rSet.getDouble(k + 1));
                    break;
                case Types.DATE:
                    currentRow.createCell(k).setCellValue(rSet.getDate(k + 1));
                    break;

                case Types.TIMESTAMP:
                    currentRow.createCell(k).setCellValue(rSet.getTimestamp(k + 1));
                    break;

                default:
                    currentRow.createCell(k).setCellValue(rSet.getString(k + 1));
                    break;

                }
                currentRow.createCell(k).setCellValue(rSet.getString(k + 1));
            }
        }
        try {
            workBook.write(outputStream);
        } catch (IOException ex) {
            Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex);
        }
    } catch (SQLException ex) {
        Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:exporter.XlsxExporter.java

public static void writeXLSX(OutputStream outputStream, String resourceDbPath, String queryString,
        String... args) {//from  w ww.  j  av a 2s.  c  om
    try {
        ResourceBundle rb = ResourceBundle.getBundle(resourceDbPath);
        Class.forName(rb.getString("driver"));
        Connection conn = DriverManager.getConnection(rb.getString("url"), rb.getString("user"),
                rb.getString("password"));
        PreparedStatement pStatement = conn.prepareStatement(queryString, java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);

        int paramCount = 1;

        for (String arg : args) {
            pStatement.setString(paramCount++, arg);

        }
        ResultSet rSet = pStatement.executeQuery();
        ResultSetMetaData rsMetaData = rSet.getMetaData();
        int columnCount = rsMetaData.getColumnCount();
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        SXSSFSheet sheet = (SXSSFSheet) workBook.createSheet("cics");
        String currentLine = null;
        int rowNum = 0;
        int types[] = new int[columnCount];
        Row intestazione = sheet.createRow(rowNum);
        for (int i = 0; i < columnCount; i++) {
            intestazione.createCell(i).setCellValue(rsMetaData.getColumnLabel(i + 1));
            types[i] = rsMetaData.getColumnType(i + 1);
        }
        rowNum++;
        while (rSet.next()) {
            rowNum++;
            Row currentRow = sheet.createRow(rowNum);
            for (int k = 0; k < columnCount; k++) {
                switch (types[k]) {
                case Types.INTEGER:
                    currentRow.createCell(k).setCellValue(rSet.getInt(k + 1));
                    break;
                case Types.FLOAT:
                    currentRow.createCell(k).setCellValue(rSet.getFloat(k + 1));
                    break;
                case Types.BIGINT:
                    currentRow.createCell(k).setCellValue(rSet.getInt(k + 1));
                    break;

                case Types.DOUBLE:
                    currentRow.createCell(k).setCellValue(rSet.getDouble(k + 1));
                    break;
                case Types.DATE:
                    currentRow.createCell(k).setCellValue(rSet.getDate(k + 1));
                    break;

                case Types.TIMESTAMP:
                    currentRow.createCell(k).setCellValue(rSet.getTimestamp(k + 1));
                    break;

                default:
                    currentRow.createCell(k).setCellValue(rSet.getString(k + 1));
                    break;

                }
                currentRow.createCell(k).setCellValue(rSet.getString(k + 1));
            }
        }
        rSet.close();
        pStatement.close();
        conn.close();
        workBook.write(outputStream);

        System.out.println("Done");
    } catch (ClassNotFoundException ex) {
        System.out.println(ex.getMessage() + "Exception in try");
    } catch (SQLException ex) {
        System.out.println(ex.getMessage() + "Exception in try");
    } catch (IOException ex) {
        System.out.println(ex.getMessage() + "Exception in try");
    }
}

From source file:info.informationsea.java.excel2csv.Converter.java

License:Open Source License

private void doConvertAllSheets(List<File> inputFiles, File outputFile) throws Exception {
    Workbook workbook;/*from   w  ww. j a va2s  .  com*/

    if (outputFile.isFile() && outputFile.length() > 512) {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX:
            workbook = WorkbookFactory.create(outputFile);
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    } else {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
            workbook = new HSSFWorkbook();
            break;
        case FILETYPE_XLSX:
            if (largeExcelMode)
                workbook = new SXSSFWorkbook();
            else
                workbook = new XSSFWorkbook();
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    }

    if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
        log.warn("Streaming output mode is disabled");
    }
    //log.info("workbook: {}", workbook.getClass());

    for (File oneInput : inputFiles) {
        switch (Utilities.suggestFileTypeFromName(oneInput.getName())) {
        case FILETYPE_XLSX:
        case FILETYPE_XLS: {
            Workbook inputWorkbook = WorkbookFactory.create(oneInput);
            int sheetNum = inputWorkbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                try (TableReader reader = new ExcelSheetReader(inputWorkbook.getSheetAt(i))) {
                    ExcelSheetWriter sheetWriter = new ExcelSheetWriter(
                            Utilities.createUniqueNameSheetForWorkbook(workbook, inputWorkbook.getSheetName(i),
                                    overwriteSheet));
                    sheetWriter.setPrettyTable(prettyTable);
                    try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes,
                            fistCount)) {
                        Utilities.copyTable(reader, tableWriter, useHeader);
                    }
                }
            }
            break;
        }
        default: {
            try (TableReader reader = Utilities.openReader(oneInput, inputSheetIndex, inputSheetName)) {
                ExcelSheetWriter sheetWriter = new ExcelSheetWriter(Utilities
                        .createUniqueNameSheetForWorkbook(workbook, oneInput.getName(), overwriteSheet));
                sheetWriter.setPrettyTable(prettyTable);
                try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes, fistCount)) {
                    Utilities.copyTable(reader, tableWriter, useHeader);
                }
            }
            break;
        }
        }
    }

    workbook.write(new FileOutputStream(outputFile));
}

From source file:info.informationsea.java.excel2csv.Utilities.java

License:Open Source License

public static TableWriter openWriter(final File outputFile, String sheetName, boolean overWrite,
        boolean enablePretty, boolean largeExcelMode) throws IOException {
    if (outputFile == null) {
        return new TableCSVWriter(new OutputStreamWriter(System.out), new TabDelimitedFormat());
    } else {/*from w  ww  .ja va2s .  com*/
        FileType type = suggestFileTypeFromName(outputFile.getName());
        switch (type) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX: {
            final Workbook workbook;
            if (outputFile.exists() && outputFile.length() > 512) {
                if (type == FileType.FILETYPE_XLSX)
                    workbook = new XSSFWorkbook(new FileInputStream(outputFile));
                else
                    workbook = new HSSFWorkbook(new FileInputStream(outputFile));
            } else {
                if (type == FileType.FILETYPE_XLSX) {
                    if (largeExcelMode)
                        workbook = new SXSSFWorkbook();
                    else
                        workbook = new XSSFWorkbook();
                } else {
                    workbook = new HSSFWorkbook();
                }
            }

            //log.info("workbook: {}", workbook.getClass());

            if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
                log.warn("Streaming output mode is disabled");
            }

            Sheet sheet = createUniqueNameSheetForWorkbook(workbook, sheetName, overWrite);
            final ExcelSheetWriter excelSheetWriter = new ExcelSheetWriter(sheet);
            excelSheetWriter.setPrettyTable(enablePretty);
            return new AbstractTableWriter() {
                @Override
                public void printRecord(Object... values) throws Exception {
                    for (int i = 0; i < values.length; i++) {
                        if (values[i] == null)
                            values[i] = "";
                    }
                    excelSheetWriter.printRecord(values);
                }

                @Override
                public void close() throws Exception {
                    excelSheetWriter.close();
                    try (OutputStream os = new FileOutputStream(outputFile)) {
                        workbook.write(os);
                    }
                }
            };
        }
        case FILETYPE_CSV:
            return new TableCSVWriter(new FileWriter(outputFile));
        case FILETYPE_TAB:
        default:
            return new TableCSVWriter(new FileWriter(outputFile), new TabDelimitedFormat());
        }
    }
}