List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook
public SXSSFWorkbook(int rowAccessWindowSize)
From source file:org.geoserver.wfs.response.Excel2007OutputFormat.java
License:Open Source License
/** * Returns a new SXSSFWorkbook workbook */ @Override protected Workbook getNewWorkbook() { return new SXSSFWorkbook(1); }
From source file:org.jaffa.qm.finder.apis.ExcelExportService.java
License:Open Source License
public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName) throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { Workbook wb = null;/* www . ja v a2 s . c o m*/ String legacyExport = (String) ContextManagerFactory.instance() .getProperty("jaffa.widgets.exportToExcel.legacy"); if (legacyExport != null && legacyExport.equals("T")) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(100); } try { // Creating worksheet Sheet sheet = null; if (sheetName != null) sheet = wb.createSheet(sheetName); else sheet = wb.createSheet(); // creating a custom palette for the workbook CellStyle style = wb.createCellStyle(); style = wb.createCellStyle(); // setting the foreground color to gray style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); // Setting the border for the cells style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); // setting font weight Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); int rowNum = 0; Row headerRow = sheet.createRow(rowNum); int colIndex = 0; for (Object o : master.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } // Generate the Excel output by creating a simple HTML table if (child != null) { for (Object o : child.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } } // Invoke the query and obtain an array of Graph objects Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(), master.getServiceClassName(), master.getServiceClassMethodName()); // Add the data rows if (queryOutput != null) { for (Object row : queryOutput) { Object[] detailQueryOutput = new Object[0]; if (child == null) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object o : master.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; if (mapping.startsWith("appFields.")) { mapping = mapping.substring(10); try { Object[] appFields = (Object[]) PropertyUtils.getProperty(row, "applicationFields"); for (Object field : appFields) { String name = (String) PropertyUtils.getProperty(field, "name"); if (name.equals(mapping)) { value = (String) PropertyUtils.getProperty(field, "value"); } } } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } } else { try { value = PropertyUtils.getProperty(row, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } else { //child is not null // load the child rows String detailCriteriaObject = child.getCriteriaObject(); for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) { String kfn = child.getMasterKeyFieldNames()[i]; try { String keyValue = (String) PropertyUtils.getProperty(row, kfn); detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Key property not found: " + kfn, e); } } detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject, child.getServiceClassName(), "query"); // add the child columns if (detailQueryOutput != null && detailQueryOutput.length > 0) { for (Object detailRow : detailQueryOutput) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object obj : master.getColumnModel()) { String masterMapping = (String) ((DynaBean) obj).get("mapping"); Object masterValue = null; try { masterValue = PropertyUtils.getProperty(row, masterMapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + masterMapping, e); } dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj)); colNum += 1; } for (Object o : child.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(detailRow, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found in child result: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } } } } } } catch (Exception e) { e.printStackTrace(); } return wb; }
From source file:org.jaffa.ria.finder.apis.ExcelExportService.java
License:Open Source License
public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName) throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { Workbook wb = null;/*from ww w. j a va 2s . c o m*/ String legacyExport = (String) ContextManagerFactory.instance() .getProperty("jaffa.widgets.exportToExcel.legacy"); if (legacyExport != null && legacyExport.equals("T")) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(100); } try { // Creating worksheet Sheet sheet = null; if (sheetName != null) { if (sheetName.length() > 31) sheetName = sheetName.substring(0, 31); char replaceChar = '_'; sheetName = sheetName.replace('\u0003', replaceChar).replace(':', replaceChar) .replace('/', replaceChar).replace("\\\\", Character.toString(replaceChar)) .replace('?', replaceChar).replace('*', replaceChar).replace(']', replaceChar) .replace('[', replaceChar); sheet = wb.createSheet(sheetName); } else sheet = wb.createSheet(); // creating a custom palette for the workbook CellStyle style = wb.createCellStyle(); style = wb.createCellStyle(); // setting the foreground color to gray style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); // Setting the border for the cells style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); // setting font weight Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); int rowNum = 0; Row headerRow = sheet.createRow(rowNum); int colIndex = 0; for (Object o : master.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } // Generate the Excel output by creating a simple HTML table if (child != null) { for (Object o : child.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } } // Invoke the query and obtain an array of Graph objects Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(), master.getServiceClassName(), master.getServiceClassMethodName()); // Add the data rows if (queryOutput != null) { for (Object row : queryOutput) { Object[] detailQueryOutput = new Object[0]; if (child == null) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object o : master.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(row, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } else { //child is not null // load the child rows String detailCriteriaObject = child.getCriteriaObject(); for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) { String kfn = child.getMasterKeyFieldNames()[i]; try { String keyValue = (String) PropertyUtils.getProperty(row, kfn); detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Key property not found: " + kfn, e); } } detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject, child.getServiceClassName(), "query"); // add the child columns if (detailQueryOutput != null && detailQueryOutput.length > 0) { for (Object detailRow : detailQueryOutput) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object obj : master.getColumnModel()) { String masterMapping = (String) ((DynaBean) obj).get("mapping"); Object masterValue = null; try { masterValue = PropertyUtils.getProperty(row, masterMapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + masterMapping, e); } dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj)); colNum += 1; } for (Object o : child.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(detailRow, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found in child result: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } } } } } } catch (Exception e) { e.printStackTrace(); } return wb; }
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 . j a va 2 s . co 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.jkiss.dbeaver.data.office.export.DataExporterXLSX.java
License:Apache License
@Override public void init(IStreamDataExporterSite site) throws DBException { Object nullStringProp = site.getProperties().get(PROP_NULL_STRING); nullString = nullStringProp == null ? null : nullStringProp.toString(); try {//from ww w . ja v a 2 s. com printHeader = (Boolean) site.getProperties().get(PROP_HEADER); } catch (Exception e) { printHeader = false; } try { rowNumber = (Boolean) site.getProperties().get(PROP_ROWNUMBER); } catch (Exception e) { rowNumber = false; } try { boolTrue = (String) site.getProperties().get(PROP_TRUESTRING); } catch (Exception e) { boolTrue = "true"; } try { boolFalse = (String) site.getProperties().get(PROP_FALSESTRING); } catch (Exception e) { boolFalse = "false"; } if (!"true".equals(boolTrue) || !"false".equals(boolFalse)) { booleRedefined = true; } try { exportSql = (Boolean) site.getProperties().get(PROP_EXPORT_SQL); } catch (Exception e) { exportSql = false; } try { splitSqlText = (Boolean) site.getProperties().get(PROP_SPLIT_SQLTEXT); } catch (Exception e) { splitSqlText = false; } try { splitByRowCount = (Integer) site.getProperties().get(PROP_SPLIT_BYROWCOUNT); } catch (Exception e) { splitByRowCount = EXCEL2007MAXROWS; } try { splitByCol = (Integer) site.getProperties().get(PROP_SPLIT_BYCOL); } catch (Exception e) { splitByCol = -1; } wb = new SXSSFWorkbook(ROW_WINDOW); worksheets = new HashMap<>(1); styleHeader = (XSSFCellStyle) wb.createCellStyle(); BorderStyle border; try { border = BorderStyle.valueOf((String) site.getProperties().get(PROP_BORDER)); } catch (Exception e) { border = BorderStyle.NONE; } FontStyleProp fontStyle; try { fontStyle = FontStyleProp.valueOf((String) site.getProperties().get(PROP_HEADER_FONT)); } catch (Exception e) { fontStyle = FontStyleProp.NONE; } styleHeader.setBorderTop(border); styleHeader.setBorderBottom(border); styleHeader.setBorderLeft(border); styleHeader.setBorderRight(border); XSSFFont fontBold = (XSSFFont) wb.createFont(); switch (fontStyle) { case BOLD: fontBold.setBold(true); break; case ITALIC: fontBold.setItalic(true); break; case STRIKEOUT: fontBold.setStrikeout(true); break; case UNDERLINE: fontBold.setUnderline((byte) 3); break; default: break; } styleHeader.setFont(fontBold); style = (XSSFCellStyle) wb.createCellStyle(); style.setBorderTop(border); style.setBorderBottom(border); style.setBorderLeft(border); style.setBorderRight(border); this.rowCount = 0; super.init(site); }
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);//from ww w . j a va 2 s .com 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.primefaces.component.export.ExcelXStreamExporter.java
License:Apache License
@Override protected Workbook createWorkBook() { SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100); sxssfWorkbook.setCompressTempFiles(true); return sxssfWorkbook; }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_// w w w. j a v a 2 s .c om * * @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_//w ww .ja v a 2 s .com * * @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 w ww.j ava2 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")); }