List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createSheet
@Override
public SXSSFSheet createSheet(String sheetname)
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 w w . j av a 2s . co 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!");
}//from www.j av a 2 s . c o m
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.eclipse.sw360.exporter.ExcelExporter.java
License:Open Source License
public InputStream makeExcelExport(List<T> documents) throws IOException, SW360Exception { final SXSSFWorkbook workbook = new SXSSFWorkbook(); final ByteArrayInputStream stream; try {//from ww w. ja v a 2 s . c o m SXSSFSheet sheet = workbook.createSheet("Data"); /** Adding styles to cells */ CellStyle cellStyle = createCellStyle(workbook); CellStyle headerStyle = createHeaderStyle(workbook); /** Create header row */ Row headerRow = sheet.createRow(0); List<String> headerNames = helper.getHeaders(); fillRow(headerRow, headerNames, headerStyle); /** Create data rows */ fillValues(sheet, documents, cellStyle); // removed autosizing of spreadsheet columns for performance reasons /** Copy the streams */ final ByteArrayOutputStream out = new ByteArrayOutputStream(); workbook.write(out); stream = new ByteArrayInputStream(out.toByteArray()); } finally { workbook.dispose(); } return stream; }
From source file:org.jboss.dashboard.displayer.table.ExportTool.java
License:Apache License
public InputStream exportExcel(Table table) { // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (table == null) throw new IllegalArgumentException("Null table specified!"); int columnCount = table.getColumnCount(); int rowCount = table.getRowCount() + 1; //Include header row int row = 0;/*from w w w . java2 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(table.getColumnName(i)); } // 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 = table.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).getDescription(LocaleManager.currentLocale())); } else if (value == null) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(""); } 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); } ByteArrayInputStream bis = null; try { ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); bis = new ByteArrayInputStream(bos.toByteArray()); bos.close(); } catch (IOException e) { log.error("Data export error: ", 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 bis; }
From source file:org.ohdsi.whiteRabbit.scan.SourceDataScan.java
License:Apache License
private void generateReport(Map<String, List<FieldInfo>> tableToFieldInfos, String filename) { System.out.println("Generating scan report"); removeEmptyTables(tableToFieldInfos); List<String> tables = new ArrayList<String>(tableToFieldInfos.keySet()); Collections.sort(tables);/* ww w . j a v a 2s .c om*/ SXSSFWorkbook workbook = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk // Create overview sheet Sheet sheet = workbook.createSheet("Overview"); if (!scanValues) { addRow(sheet, "Table", "Field", "Type", "N rows"); for (String table : tables) { for (FieldInfo fieldInfo : tableToFieldInfos.get(table)) addRow(sheet, table, fieldInfo.name, fieldInfo.getTypeDescription(), Long.valueOf(fieldInfo.rowCount)); addRow(sheet, ""); } } else { addRow(sheet, "Table", "Field", "Type", "Max length", "N rows", "N rows checked", "Fraction empty"); for (String table : tables) { for (FieldInfo fieldInfo : tableToFieldInfos.get(table)) addRow(sheet, table, fieldInfo.name, fieldInfo.getTypeDescription(), Integer.valueOf(fieldInfo.maxLength), Long.valueOf(fieldInfo.rowCount), Long.valueOf(fieldInfo.nProcessed), fieldInfo.getFractionEmpty()); addRow(sheet, ""); } // Create per table sheets for (String table : tables) { sheet = workbook.createSheet(table); List<FieldInfo> fieldInfos = tableToFieldInfos.get(table); List<List<Pair<String, Integer>>> valueCounts = new ArrayList<List<Pair<String, Integer>>>(); Object[] header = new Object[fieldInfos.size() * 2]; int maxCount = 0; for (int i = 0; i < fieldInfos.size(); i++) { FieldInfo fieldInfo = fieldInfos.get(i); header[i * 2] = fieldInfo.name; if (fieldInfo.isFreeText) header[(i * 2) + 1] = "Word count"; else header[(i * 2) + 1] = "Frequency"; List<Pair<String, Integer>> counts = fieldInfo.getSortedValuesWithoutSmallValues(); valueCounts.add(counts); if (counts.size() > maxCount) maxCount = counts.size(); } addRow(sheet, header); for (int i = 0; i < maxCount; i++) { Object[] row = new Object[fieldInfos.size() * 2]; for (int j = 0; j < fieldInfos.size(); j++) { List<Pair<String, Integer>> counts = valueCounts.get(j); if (counts.size() > i) { row[j * 2] = counts.get(i).getItem1(); row[(j * 2) + 1] = counts.get(i).getItem2() == -1 ? "" : counts.get(i).getItem2(); } else { row[j * 2] = ""; row[(j * 2) + 1] = ""; } } addRow(sheet, row); } // Save some memory by derefencing tables already included in the report: tableToFieldInfos.remove(table); } } try { FileOutputStream out = new FileOutputStream(new File(filename)); workbook.write(out); out.close(); StringUtilities.outputWithTime("Scan report generated: " + filename); } catch (IOException e) { throw new RuntimeException(e.getMessage()); } }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_/* w ww .j a v a 2s . co m*/ * * @param request _more_ * @param service _more_ * @param input _more_ * @param args _more_ * * * @return _more_ * @throws Exception _more_ */ public boolean extractSheet(Request request, Service service, ServiceInput input, List args) throws Exception { Entry entry = null; for (Entry e : input.getEntries()) { if (isTabular(e)) { entry = e; break; } } if (entry == null) { throw new IllegalArgumentException("No tabular entry found"); } HashSet<Integer> sheetsToShow = getSheetsToShow((String) args.get(0)); final SXSSFWorkbook wb = new SXSSFWorkbook(100); // final Workbook wb = new XSSFWorkbook(); String name = getStorageManager().getFileTail(entry); if (!Utils.stringDefined(name)) { name = entry.getName(); } name = IOUtil.stripExtension(name); File newFile = new File(IOUtil.joinDir(input.getProcessDir(), name + ".xlsx")); TabularVisitor visitor = new TabularVisitor() { public boolean visit(TextReader info, String sheetName, List<List<Object>> rows) { sheetName = sheetName.replaceAll("[/]+", "-"); Sheet sheet = wb.createSheet(sheetName); int rowCnt = 0; for (List<Object> cols : rows) { Row row = sheet.createRow(rowCnt++); for (int colIdx = 0; colIdx < cols.size(); colIdx++) { Object col = cols.get(colIdx); Cell cell = row.createCell(colIdx); if (col instanceof Double) { cell.setCellValue(((Double) col).doubleValue()); } else if (col instanceof Date) { cell.setCellValue((Date) col); } else if (col instanceof Boolean) { cell.setCellValue(((Boolean) col).booleanValue()); } else { cell.setCellValue(col.toString()); } } } return true; } }; TabularVisitInfo visitInfo = new TabularVisitInfo(request, entry, getSkipRows(request, entry), getRowCount(request, entry, Integer.MAX_VALUE), sheetsToShow); TextReader info = new TextReader(); info.setSkip(getSkipRows(request, entry)); info.setMaxRows(getRowCount(request, entry, MAX_ROWS)); // http:://localhost:8080/repository/entry/show?entryid=740ae258-805d-4a1f-935d-289d0a6e5519&output=media_tabular_extractsheet&serviceform=true&execute=Execute visit(request, entry, info, visitor); FileOutputStream fileOut = new FileOutputStream(newFile); wb.write(fileOut); fileOut.close(); wb.dispose(); return true; }
From source file:org.ramadda.plugins.media.TabularOutputHandler.java
License:Open Source License
/** * _more_/*from w w w. java2s .c o m*/ * * @param request _more_ * @param service _more_ * @param input _more_ * @param args _more_ * * * @return _more_ * @throws Exception _more_ */ public boolean extractSheet(Request request, Service service, ServiceInput input, List args) throws Exception { Entry entry = null; for (Entry e : input.getEntries()) { if (isTabular(e)) { entry = e; break; } } if (entry == null) { throw new IllegalArgumentException("No tabular entry found"); } HashSet<Integer> sheetsToShow = getSheetsToShow((String) args.get(0)); final SXSSFWorkbook wb = new SXSSFWorkbook(100); // final Workbook wb = new XSSFWorkbook(); String name = getStorageManager().getFileTail(entry); if (!Utils.stringDefined(name)) { name = entry.getName(); } name = IOUtil.stripExtension(name); File newFile = new File(IOUtil.joinDir(input.getProcessDir(), name + ".xlsx")); TabularVisitor visitor = new TabularVisitor() { public boolean visit(Visitor info, String sheetName, List<List<Object>> rows) { sheetName = sheetName.replaceAll("[/]+", "-"); Sheet sheet = wb.createSheet(sheetName); int rowCnt = 0; for (List<Object> cols : rows) { Row row = sheet.createRow(rowCnt++); for (int colIdx = 0; colIdx < cols.size(); colIdx++) { Object col = cols.get(colIdx); Cell cell = row.createCell(colIdx); if (col instanceof Double) { cell.setCellValue(((Double) col).doubleValue()); } else if (col instanceof Date) { cell.setCellValue((Date) col); } else if (col instanceof Boolean) { cell.setCellValue(((Boolean) col).booleanValue()); } else { cell.setCellValue(col.toString()); } } } return true; } }; TabularVisitInfo visitInfo = new TabularVisitInfo(request, entry, getSkipRows(request, entry), getRowCount(request, entry, Integer.MAX_VALUE), sheetsToShow); Visitor info = new Visitor(); info.setSkip(getSkipRows(request, entry)); info.setMaxRows(getRowCount(request, entry, MAX_ROWS)); // http:://localhost:8080/repository/entry/show?entryid=740ae258-805d-4a1f-935d-289d0a6e5519&output=media_tabular_extractsheet&serviceform=true&execute=Execute visit(request, entry, info, visitor); FileOutputStream fileOut = new FileOutputStream(newFile); wb.write(fileOut); fileOut.close(); wb.dispose(); return true; }
From source file:org.riflemansd.businessprofit.excel.ExcelExamplePOI.java
License:Open Source License
public static void main(String[] args) throws Throwable { SXSSFWorkbook wb = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows will be flushed to disk if (wb.getNumberOfSheets() == 0) { wb.createSheet("MySheet"); }/*from www . j a va 2 s . c o m*/ Sheet sh = wb.getSheetAt(0); Row row = sh.createRow(3); for (int i = 0; i < 10; i++) { Cell cell = row.createCell(i); //String address = new CellReference(cell).formatAsString(); cell.setCellValue("? " + i); //row.setHeightInPoints(50); //sh.setColumnWidth(5, 1200); //4, 33 pixels wb.getSheetAt(0).autoSizeColumn(i); } FileOutputStream out = new FileOutputStream("test.xlsx"); wb.write(out); out.close(); // dispose of temporary files backing this workbook on disk wb.dispose(); Desktop.getDesktop().open(new File("test.xlsx")); }
From source file:poi.xssf.streaming.examples.Outlining.java
License:Apache License
private void collapseRow() throws Exception { SXSSFWorkbook wb2 = new SXSSFWorkbook(100); SXSSFSheet sheet2 = (SXSSFSheet) wb2.createSheet("new sheet"); int rowCount = 20; for (int i = 0; i < rowCount; i++) { sheet2.createRow(i);/*w ww . j a va 2 s . co m*/ } sheet2.groupRow(4, 9); sheet2.groupRow(11, 19); sheet2.setRowGroupCollapsed(4, true); FileOutputStream fileOut = new FileOutputStream("outlining_collapsed.xlsx"); wb2.write(fileOut); fileOut.close(); wb2.dispose(); }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
private void writeSheetMap(SXSSFWorkbook wb, Map<String, String> sheetMap) { SXSSFSheet sh = (SXSSFSheet) wb.createSheet(Constants.XOR.EXCEL_INDEX_SHEET); int rowNo = 0; for (Map.Entry<String, String> entry : sheetMap.entrySet()) { Row row = sh.createRow(rowNo++); Cell sheetNameCell = row.createCell(0); Cell propertyNameCell = row.createCell(1); sheetNameCell.setCellValue(entry.getValue()); propertyNameCell.setCellValue(entry.getKey()); }//from w w w . j a v a 2s .c om sh.autoSizeColumn(0); sh.autoSizeColumn(1); wb.setSheetOrder(Constants.XOR.EXCEL_INDEX_SHEET, 1); }