List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook
public SXSSFWorkbook()
From source file:org.omnaest.i18nbinder.internal.XLSFile.java
License:Apache License
private Workbook newWorkbookToWrite() { ////w w w. ja v a2 s.co m Workbook retval = null; // if (this.useXLSXFileFormat()) { retval = new SXSSFWorkbook(); } else { retval = new HSSFWorkbook(); } // return retval; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_FormulaRecalculationTest.java
License:Apache License
private void forcesToRecalculate_Sxssf(String property, boolean expectedFlag) throws Exception { step.setVariable(ExcelWriterStep.STREAMER_FORCE_RECALC_PROP_NAME, property); data.wb = spy(new SXSSFWorkbook()); step.recalculateAllWorkbookFormulas(); if (expectedFlag) { verify(data.wb).setForceFormulaRecalculation(true); } else {// w w w. java2 s .c om verify(data.wb, never()).setForceFormulaRecalculation(anyBoolean()); } }
From source file:org.sakaiproject.jsf.spreadsheet.SpreadsheetDataFileWriterXlsx.java
License:Educational Community License
private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) { Workbook wb = new SXSSFWorkbook(); Sheet sheet = wb.createSheet();// ww w .ja v a 2 s . c om CellStyle headerCs = wb.createCellStyle(); Iterator<List<Object>> dataIter = spreadsheetData.iterator(); // Set the header style headerCs.setBorderBottom(BorderStyle.THICK); headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex()); // Set the font CellStyle cellStyle = null; String fontName = ServerConfigurationService.getString("spreadsheet.font"); if (fontName != null) { Font font = wb.createFont(); font.setFontName(fontName); headerCs.setFont(font); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); } // By convention, the first list in the list contains column headers. Row headerRow = sheet.createRow((short) 0); List<Object> headerList = dataIter.next(); for (short i = 0; i < headerList.size(); i++) { Cell headerCell = createCell(headerRow, i); headerCell.setCellValue((String) headerList.get(i)); headerCell.setCellStyle(headerCs); //TODO //sheet.autoSizeColumn(i); } short rowPos = 1; while (dataIter.hasNext()) { List<Object> rowData = dataIter.next(); Row row = sheet.createRow(rowPos++); for (short i = 0; i < rowData.size(); i++) { Cell cell = createCell(row, i); Object data = rowData.get(i); if (data != null) { if (data instanceof Double) { cell.setCellValue(((Double) data).doubleValue()); } else { cell.setCellValue(data.toString()); } if (cellStyle != null) { cell.setCellStyle(cellStyle); } } } } return wb; }
From source file:org.springframework.web.servlet.view.document.AbstractXlsxStreamingView.java
License:Apache License
/** * This implementation creates a {@link SXSSFWorkbook} for streaming the XLSX format. *///from w w w .jav a2 s.c om @Override protected SXSSFWorkbook createWorkbook(Map<String, Object> model, HttpServletRequest request) { return new SXSSFWorkbook(); }
From source file:org.tinywind.springi18nconverter.converter.SinglePageExcel.java
License:Open Source License
public SinglePageExcel() { this.workbook = new SXSSFWorkbook(); this.sheet = workbook.createSheet(); }
From source file:pruebaimportarexcel.excel.ExcelUtils.java
public ExcelUtils(String path) { if (path.endsWith("xls")) wb = new HSSFWorkbook(); else/*from w w w .j av a 2s .c o m*/ wb = new SXSSFWorkbook(); this.path = path; }
From source file:sql.fredy.sqltools.XLSExport.java
License:Open Source License
/** * Create the XLS-File named fileName/* ww w . j av a2 s.com*/ * * @param fileName is the Name (incl. Path) of the XLS-file to create * * */ public int createXLS(String fileName) { // I need to have a query to process if ((getQuery() == null) && (getPstmt() == null)) { logger.log(Level.WARNING, "Need to have a query to process"); return 0; } // I also need to have a file to write into if (fileName == null) { logger.log(Level.WARNING, "Need to know where to write into"); return 0; } fileName = fixFileName(fileName); checkXlsx(fileName); // I need to have a connection to the RDBMS if (getCon() == null) { logger.log(Level.WARNING, "Need to have a connection to process"); return 0; } //Statement stmt = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { // first we have to create the Statement if (getPstmt() == null) { pstmt = getCon().prepareStatement(getQuery()); } //stmt = getCon().createStatement(); } catch (SQLException sqle1) { setException(sqle1); logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString()); return 0; } logger.log(Level.FINE, "FileName: " + fileName); logger.log(Level.FINE, "Query : " + getQuery()); logger.log(Level.FINE, "Starting export..."); // create an empty sheet Workbook wb; Sheet sheet; Sheet sqlsheet; CreationHelper createHelper = null; //XSSFSheet xsheet; //HSSFSheet sheet; if (isXlsx()) { wb = new SXSSFWorkbook(); createHelper = wb.getCreationHelper(); } else { wb = new HSSFWorkbook(); createHelper = wb.getCreationHelper(); } sheet = wb.createSheet("Data Export"); // create a second sheet just containing the SQL Statement sqlsheet = wb.createSheet("SQL Statement"); Row sqlrow = sqlsheet.createRow(0); Cell sqltext = sqlrow.createCell(0); try { if (getQuery() != null) { sqltext.setCellValue(getQuery()); } else { sqltext.setCellValue(pstmt.toString()); } } catch (Exception lex) { } CellStyle style = wb.createCellStyle(); style.setWrapText(true); sqltext.setCellStyle(style); Row r = null; int row = 0; // row number int col = 0; // column number int columnCount = 0; try { //resultSet = stmt.executeQuery(getQuery()); resultSet = pstmt.executeQuery(); logger.log(Level.FINE, "query executed"); } catch (SQLException sqle2) { setException(sqle2); logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString()); return 0; } // create Header in XLS-file ArrayList<String> head = new ArrayList(); try { rsmd = resultSet.getMetaData(); logger.log(Level.FINE, "Got MetaData of the resultset"); columnCount = rsmd.getColumnCount(); logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset"); r = sheet.createRow(row); // titlerow if ((!isXlsx()) && (columnCount > 255)) { columnCount = 255; } for (int i = 0; i < columnCount; i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue(rsmd.getColumnName(i + 1)); head.add(rsmd.getColumnName(i + 1)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString()); return 0; } // looping the resultSet int wbCounter = 0; try { while (resultSet.next()) { // this is the next row col = 0; // put column counter back to 0 to start at the next row row++; // next row // create a new sheet if more then 60'000 Rows and xls file if ((!isXlsx()) && (row % 65530 == 0)) { wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.INFO, "created a further page because of a huge amount of data"); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } try { r = sheet.createRow(row); } catch (Exception e) { logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage()); wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.WARNING, "created a further page in the hope it helps..."); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } col = 0; // put column counter back to 0 to start at the next row String previousMessage = ""; for (int i = 0; i < columnCount; i++) { try { // depending on the type, create the cell switch (rsmd.getColumnType(i + 1)) { case java.sql.Types.INTEGER: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.FLOAT: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.DOUBLE: r.createCell(col).setCellValue(resultSet.getDouble(i + 1)); break; case java.sql.Types.DECIMAL: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.NUMERIC: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.BIGINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.TINYINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.SMALLINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.DATE: // first we get the date java.sql.Date dat = resultSet.getDate(i + 1); java.util.Date date = new java.util.Date(dat.getTime()); r.createCell(col).setCellValue(date); break; case java.sql.Types.TIMESTAMP: // first we get the date java.sql.Timestamp ts = resultSet.getTimestamp(i + 1); Cell c = r.createCell(col); try { c.setCellValue(ts); // r.createCell(col).setCellValue(ts); // Date Format CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); c.setCellStyle(cellStyle); } catch (Exception e) { c.setCellValue(" "); } break; case java.sql.Types.TIME: // first we get the date java.sql.Time time = resultSet.getTime(i + 1); r.createCell(col).setCellValue(time); break; case java.sql.Types.BIT: boolean b1 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b1); break; case java.sql.Types.BOOLEAN: boolean b2 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b2); break; case java.sql.Types.CHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.NVARCHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.VARCHAR: try { r.createCell(col).setCellValue(resultSet.getString(i + 1)); } catch (Exception e) { r.createCell(col).setCellValue(" "); logger.log(Level.WARNING, "Exception while writing column {0} row {3} type: {1} Message: {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); } break; default: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; } } catch (Exception e) { //e.printStackTrace(); if (resultSet.wasNull()) { r.createCell(col).setCellValue(" "); } else { logger.log(Level.WARNING, "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); r.createCell(col).setCellValue(" "); } } col++; } } //pstmt.close(); } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString()); } try { // Write the output to a file FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); logger.log(Level.INFO, "File created"); logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row)); } catch (Exception e) { logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString()); } return row; }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
/** * Generate the Excel sheets based on entities and collections * TODO: Should the map be topologically ordered? * @param to root entity/*w w w. ja va2 s . com*/ * @param sheetBO map of the sheet name and the entities/relationships within that sheet * @return the generated Excel workbook */ private Workbook processSheetBO(BusinessObject to, Map<String, List<BusinessObject>> sheetBO) { SXSSFWorkbook wb = new SXSSFWorkbook(); wb.setCompressTempFiles(true); List<BusinessObject> entityBOList = new LinkedList<BusinessObject>(); entityBOList.add(to); createBOSheet(wb, Constants.XOR.EXCEL_ENTITY_SHEET, null, entityBOList, null); int sheetNo = 1; Map<String, String> sheetMap = new HashMap<String, String>(); for (Map.Entry<String, List<BusinessObject>> entry : sheetBO.entrySet()) { // Create a sheet String sheetName = Constants.XOR.EXCEL_SHEET_PREFIX + sheetNo++; sheetMap.put(entry.getKey(), sheetName); createBOSheet(wb, sheetName, entry.getKey(), entry.getValue(), null); } writeSheetMap(wb, sheetMap); return wb; }