List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook
public SXSSFWorkbook(int rowAccessWindowSize)
From source file:com.repository2excel.Main.java
License:Apache License
/** * @param args// www.j av a 2s .c om */ @SuppressWarnings("deprecation") public static void main(String[] args) { String xmlRepositoryDefFilePath = ""; /** Read user input */ Scanner scnr = new Scanner(System.in); System.out.println("Enter fully qualified path to customCatalog.xml:"); try { xmlRepositoryDefFilePath = scnr.next(); } catch (InputMismatchException e) { // TODO: } finally { scnr.close(); } RepositoryDefinitionReader reader = new RepositoryDefinitionReader(); System.out.println("Begin reading XML Repository definition file..."); HashSet<Item> items = reader.loadRepositoryDefinition(new File(xmlRepositoryDefFilePath)); System.out.println("Finished reading XML file!"); if (items != null && items.size() > 0) { System.out.println("Preparing to export " + items.size() + " items into Excel Spreadsheet..."); SXSSFWorkbook wb = new SXSSFWorkbook(100); Sheet sh = wb.createSheet(); /** Create cell styles */ CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); Iterator<Item> iter = items.iterator(); int rownum = 0; while (iter.hasNext()) { Item item = iter.next(); Row row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Item"); row.createCell(1, CellType.STRING).setCellValue(item.getName()); rownum++; row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Query Cache Size"); row.createCell(1, CellType.STRING).setCellValue(item.getQueryCacheSize()); rownum++; row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Item Cache Size"); row.createCell(1, CellType.STRING).setCellValue(item.getItemCacheSize()); rownum++; HashSet<Property> properties = item.getProperties(); if (properties != null && properties.size() > 0) { Cell cell; row = sh.createRow(rownum); cell = row.createCell(0, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Property"); cell = row.createCell(1, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Type"); cell = row.createCell(2, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Readable"); cell = row.createCell(3, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Writable"); cell = row.createCell(4, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Hidden"); cell = row.createCell(5, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Table"); cell = row.createCell(6, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Column"); Iterator<Property> pIter = properties.iterator(); while (pIter.hasNext()) { rownum++; row = sh.createRow(rownum); Property property = pIter.next(); /** 0. Name */ cell = row.createCell(0, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getName()); /** 1. Data Type */ cell = row.createCell(1, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getDataType()); /** 2. Is Readable */ cell = row.createCell(2, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isReadable()); /** 3. Is Writable */ cell = row.createCell(3, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isWriteable()); /** 4. Is Hidden */ cell = row.createCell(4, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isHidden()); /** 5. Table */ cell = row.createCell(5, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getTable()); /** 6. Column */ cell = row.createCell(6, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getColumn()); } } rownum++; rownum++; } try { File f = new File("test.xlsx"); FileOutputStream out = new FileOutputStream(f); wb.write(out); out.close(); wb.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { // dispose of temporary files backing this workbook on disk wb.dispose(); } } }
From source file:com.sitech.chn.s98800.s98820.s882q.util.XLSXCovertCSVReader.java
License:Apache License
public static void writeToXlSX(HttpServletResponse response, List data, String FileName) throws Exception { String filePath = ServletActionContext.getServletContext().getRealPath("/upload") + "/" + FileName; SXSSFWorkbook wb = new SXSSFWorkbook(10000); File exprotFile = new File(filePath); if (exprotFile.exists()) { exprotFile.delete();//from ww w.j a v a 2 s .c o m exprotFile = new File(filePath); } System.out.println("" + new Date()); FileOutputStream fos = new FileOutputStream(exprotFile); Sheet sh = wb.createSheet(); String[] temp = null; for (int i = 0; i < data.size(); i++) { temp = (String[]) data.get(i); Row row = sh.createRow(i); for (int j = 0; j < temp.length; j++) { Cell cell = row.createCell(j); cell.setCellValue(temp[j] == null ? "" : temp[j].replaceAll("\"", "") + " "); } } wb.write(fos); wb.close();// fos.close(); System.out.println("" + new Date() + exprotFile.getAbsolutePath()); InputStream inStream = new BufferedInputStream(new FileInputStream(exprotFile)); response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(FileName, "UTF-8")); response.addHeader("Content-Length", exprotFile.length() + ""); byte[] b = new byte[1024 * 10]; int len; while ((len = inStream.read(b)) > 0) { response.getOutputStream().write(b, 0, len); } inStream.close(); }
From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;//from www.j a v a 2s. c o m OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new SXSSFWorkbook(100); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:de.unioninvestment.eai.portal.portlet.crud.export.ExcelExportTask.java
License:Apache License
@Override protected TableExport createExport() { SXSSFWorkbook wkbk = new SXSSFWorkbook(50); wkbk.setCompressTempFiles(true);/* w w w . ja v a 2 s .co m*/ ExcelExportWithProgress excelExport = new ExcelExportWithProgress(vaadinTable, wkbk, "ExportCallback", null, getFilename(), false); excelExport.setExportWindow("_blank"); excelExport.setMimeType(EXCEL_XSLX_MIMETYPE); excelExport.setRowHeaders(true); excelExport.excludeCollapsedColumns(); applyExcelFormatForColumns(excelExport); return excelExport; }
From source file:de.unioninvestment.eai.portal.portlet.crud.export.streaming.ExcelExporter.java
License:Apache License
private Workbook createWorkbook() { SXSSFWorkbook workbook = new SXSSFWorkbook(excelRowAccessWindowSize); workbook.setCompressTempFiles(true); return workbook; }
From source file:edu.harvard.hms.dbmi.bd2k.irct.ws.rs.resultconverter.XSLXTabularDataConverter.java
License:Mozilla Public License
@Override public StreamingOutput createStream(final Result result) { StreamingOutput stream = new StreamingOutput() { @Override/*ww w .j a v a 2 s. c o m*/ public void write(OutputStream outputStream) throws IOException, WebApplicationException { ResultSet rs = null; SXSSFWorkbook wb = null; try { rs = (ResultSet) result.getData(); rs.load(result.getResultSetLocation()); wb = new SXSSFWorkbook(100); // Create Sheet Sheet sh = wb.createSheet("Results"); // Create Header CellStyle headerStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(font); Row headerRow = sh.createRow(0); for (int i = 0; i < rs.getColumnSize(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(rs.getColumn(i).getName()); } // Add data rs.beforeFirst(); int rowNum = 1; while (rs.next()) { Row row = sh.createRow(rowNum); for (int i = 0; i < rs.getColumnSize(); i++) { String value = rs.getString(i); Cell cell = row.createCell(i); if (value != null) { cell.setCellValue(rs.getString(i)); } } rowNum++; } wb.write(outputStream); } catch (ResultSetException | PersistableException e) { log.info("Error creating XSLX Stream: " + e.getMessage()); } finally { if (wb != null) { wb.close(); } if (rs != null && !rs.isClosed()) { try { rs.close(); } catch (ResultSetException e) { e.printStackTrace(); } } if (outputStream != null) { outputStream.close(); } } } }; return stream; }
From source file:gov.nih.nci.ncicb.tcga.dcc.common.web.view.TCGAExcelSXSSView.java
/** * Renders the Excel view, given the specified model. *//*from w w w. jav a 2 s . co m*/ @Override protected void renderMergedOutputModel(final Map model, final HttpServletRequest request, final HttpServletResponse response) throws Exception { final SXSSFWorkbook workbook = new SXSSFWorkbook(1000); logger.debug("Created Excel Workbook from scratch"); buildExcelDocument(model, workbook, request, response); response.setContentType(getContentType()); final ServletOutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); }
From source file:gov.nih.nci.ncicb.tcga.dcc.common.web.view.TCGAExcelSXSSViewFastTest.java
@Before public void before() throws Exception { xlView = new TCGAExcelSXSSView(); request = new MockHttpServletRequest(); response = new MockHttpServletResponse(); wb = new SXSSFWorkbook(1000); wb.write(response.getOutputStream()); response.getOutputStream().close();//from w ww . ja v a 2 s.c om model = new ModelMap() { { put("exportType", "xl"); put("title", "domi"); put("fileName", "domi.xls"); put("cols", mockMapCols); put("data", makeMockArchive()); } }; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Creates a Microsoft Excel Workbook containing Topup activity provided in * a CSV text file. The format of the created file will be Office Open XML * (OOXML).// ww w . ja va 2 s. c o m * <p> * It expects the CSV to have the following columns from left to right:<br * /> * topup.uuid, topup.msisdn, topup.amount, network.name, topupStatus.status, * topup.topupTime * <p> * This method has been created to allow for large Excel files to be created * without overwhelming memory. * * * @param topupCSVFile a valid CSV text file. It should contain the full * path and name of the file e.g. "/tmp/export/topups.csv" * @param delimiter the delimiter used in the CSV file * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final String topupCSVFile, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); // Read the CSV file and populate the Excel sheet with it LineIterator lineIter = FileUtils.lineIterator(new File(topupCSVFile)); String line; String[] lineTokens; int size; while (lineIter.hasNext()) { row = sheet.createRow(rowCount); line = lineIter.next(); lineTokens = StringUtils.split(line, delimiter); size = lineTokens.length; for (int cellnum = 0; cellnum < size; cellnum++) { Cell cell = row.createCell(cellnum); cell.setCellValue(lineTokens[cellnum]); } rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (FileNotFoundException e) { logger.error("FileNotFoundException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Used to create a MS Excel file from a list of * * @param topups/*from w ww. ja v a 2 s . c o m*/ * @param networkHash a map with an UUID as the key and the name of the * network as the value * @param statusHash a map with an UUID as the key and the name of the * transaction status as the value * @param delimiter * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final List<IncomingLog> topups, final HashMap<String, String> networkHash, final HashMap<String, String> statusHash, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); Cell cell; for (IncomingLog topup : topups) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(topup.getUuid()); //cell = row.createCell(1); //cell.setCellValue(topup.getMessageid()); cell = row.createCell(2); cell.setCellValue(topup.getDestination()); cell = row.createCell(3); cell.setCellValue(networkHash.get(topup.getOrigin())); cell = row.createCell(4); cell.setCellValue(statusHash.get(topup.getMessage())); cell = row.createCell(5); cell.setCellValue(topup.getLogTime().toString()); rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }