List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook
public SXSSFWorkbook(int rowAccessWindowSize)
From source file:org.talend.dataprep.transformation.format.XlsWriter.java
License:Open Source License
public XlsWriter(final OutputStream output, Map<String, String> parameters) { try {// w w w. j ava2 s. c o m this.outputStream = output; // we limit to only 50 rows in memory this.workbook = new SXSSFWorkbook(50); // TODO sheet name as an option? this.sheet = this.workbook.createSheet("sheet1"); bufferFile = File.createTempFile("xlsWriter", ".csv"); recordsWriter = new au.com.bytecode.opencsv.CSVWriter(new FileWriter(bufferFile), BUFFER_CSV_SEPARATOR); } catch (IOException e) { throw new TDPException(TransformationErrorCodes.UNABLE_TO_USE_EXPORT, e); } }
From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java
License:Open Source License
@Override public void export(Map<String, String> criteria, File fileName, String serverBase, Map<String, String> options) { processOptions(options);// w w w. j av a2 s .co m if (!GraphicsEnvironment.isHeadless()) { progressDialog = new ProgressDialog(maxSteps, locale); progressDialog.setVisible(true); } questionsById = new HashMap<Long, QuestionDto>(); currentStep = 1; this.serverBase = serverBase; PrintWriter pw = null; boolean useQuestionId = "true".equals(options.get("useQuestionId")); String from = options.get("from"); String to = options.get("to"); String limit = options.get("maxDataReportRows"); try { SwingUtilities.invokeLater(new StatusUpdater(currentStep++, LOADING_QUESTIONS.get(locale))); List<SurveyGroupDto> sgs = fetchSurveyGroup(criteria.get(SurveyRestRequest.SURVEY_ID_PARAM), serverBase, criteria.get("apiKey")); if (sgs != null && !sgs.isEmpty()) { monitoringGroup = sgs.get(0).getMonitoringGroup(); } Map<QuestionGroupDto, List<QuestionDto>> questionMap = loadAllQuestions( criteria.get(SurveyRestRequest.SURVEY_ID_PARAM), performGeoRollup, serverBase, criteria.get("apiKey")); if (questionMap != null) { for (List<QuestionDto> qList : questionMap.values()) { for (QuestionDto q : qList) { questionsById.put(q.getKeyId(), q); if (q.getLocaleNameFlag() != null && q.getLocaleNameFlag()) { displayNameQuestionIds.add(q.getKeyId()); } } } } if (!DEFAULT_LOCALE.equals(locale) && questionMap.size() > 0) { // if we are using some other locale, we need to check for // translations SwingUtilities.invokeLater(new StatusUpdater(currentStep++, LOADING_DETAILS.get(locale))); loadFullQuestions(questionMap, criteria.get("apiKey")); } else { currentStep++; } Workbook wb = new SXSSFWorkbook(100); if (questionMap != null && questionMap.size() > 0) { headerStyle = wb.createCellStyle(); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); SummaryModel model = fetchAndWriteRawData(criteria.get(SurveyRestRequest.SURVEY_ID_PARAM), serverBase, questionMap, wb, isFullReport, fileName, criteria.get("apiKey"), lastCollection, useQuestionId, from, to, limit); if (isFullReport) { SwingUtilities.invokeLater(new StatusUpdater(currentStep++, WRITING_SUMMARY.get(locale))); writeSummaryReport(questionMap, model, null, wb); SwingUtilities.invokeLater(new StatusUpdater(currentStep++, WRITING_ROLLUPS.get(locale))); } if (model.getSectorList() != null && model.getSectorList().size() > 0) { Collections.sort(model.getSectorList(), new Comparator<String>() { @Override public int compare(String o1, String o2) { if (o1 != null && o2 != null) { return o1.toLowerCase().compareTo(o2.toLowerCase()); } else { return 0; } } }); for (String sector : model.getSectorList()) { writeSummaryReport(questionMap, model, sector, wb); } } FileOutputStream fileOut = new FileOutputStream(fileName); wb.setActiveSheet(isFullReport ? 1 : 0); wb.write(fileOut); fileOut.close(); SwingUtilities.invokeLater(new StatusUpdater(currentStep++, COMPLETE.get(locale))); } else { log.info("No questions for survey: " + criteria.get(SurveyRestRequest.SURVEY_ID_PARAM) + " - instance: " + serverBase); } } catch (Exception e) { log.error("Error generating report: " + e.getMessage(), e); } finally { if (pw != null) { pw.close(); } } }
From source file:OutputStyles.DiffExcelDefault.java
/** * The output generating class. Creates an excel file at the path indicated * by the String, file/* www . j a va2 s . c om*/ * @param file The String representation of the output path location * @param skip A string "true or false" value derived from my command line * option interpreter class. */ public void OutputDiffToExcel(String file, String skip) { // Set boolean skip flag if zero values should be avoided if (skip.equals("true")) exclude = true; Workbook wb = new SXSSFWorkbook(1000); // Get the information that we need from the diff file before proceeding TreeSet<String> sampleSet = new TreeSet<>(data.GetRpkmSamples()); TreeSet<String> comparisonSet = new TreeSet<>(data.GetComp()); TreeSet<String> locSet = new TreeSet<>(data.GetCoordLocs()); // Create important styles CreateHeaderStyles(comparisonSet, wb); highlightStyles.put("yellow", HighlightStyle.YellowBoldHighlight(wb)); Sheet sheet = GenerateSheetFromWb(wb); // Create spreadsheet header SetHeaderRow(sheet, sampleSet, comparisonSet); // I think that to minimize the memory overhead, I'm going to have to create // a tab delimited text file and read that to generate the excel workbook String[] base = file.split("\\."); String outTab = base[0] + ".tab"; try (BufferedWriter out = Files.newBufferedWriter(Paths.get(outTab), Charset.defaultCharset())) { CreateTabFileFromData(out, sampleSet, comparisonSet, locSet); // Dereferencing for garbage collection this.data = null; } catch (IOException ex) { ex.printStackTrace(); } try (BufferedReader in = Files.newBufferedReader(Paths.get(outTab), Charset.defaultCharset())) { String line = null; int row = 2; while ((line = in.readLine()) != null) { CreateRowFromTab(line, sampleSet, sheet, row); row++; if (row % 1000 == 0) { System.err.print("[DIFF EXCEL] Finished with row: " + row + "\r"); } } } catch (IOException ex) { Logger.getLogger(DiffExcelDefault.class.getName()).log(Level.SEVERE, null, ex); } System.err.print(System.lineSeparator()); System.err.println("[DIFF EXCEL] Printing to output file!"); /*int row = 2; for(String l : locSet){ CreateRowFromData(sheet, l, sampleSet, comparisonSet, row); row++; }*/ // Freeze the top two panes sheet.createFreezePane(0, 2); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); out.close(); } catch (IOException ex) { ex.printStackTrace(); } SXSSFWorkbook b = (SXSSFWorkbook) wb; b.dispose(); }
From source file:pe.gob.mef.gescon.util.ExcelUtil.java
/** * Mtodo que crea un libro excel con una hoja. * @param nombre Nombre de la hoja, tipo String. *//*w w w. j a v a 2 s. c om*/ private void creaHoja(String nombre) { this.libroExcel = new SXSSFWorkbook(100); this.hojaExcel = (Sheet) this.libroExcel.createSheet(nombre); }
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);/*from w w w. j a va 2 s.c o 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:stroom.dashboard.server.download.ExcelTarget.java
License:Apache License
@Override public void start() throws IOException { // Create a workbook with 100 rows in memory. Exceeding rows will be // flushed to disk. wb = new SXSSFWorkbook(100); sh = wb.createSheet();/*from w w w . j a v a 2 s . c om*/ }
From source file:utilities.ExchangeManager.java
License:Open Source License
public ArrayList<FileDescription> createExchangeFiles(Connection sd, Connection connectionResults, String user, int sId, HttpServletRequest request, String dirPath, boolean superUser, boolean incMedia) throws Exception { wb = new SXSSFWorkbook(10); Sheet sheet = null;/* w w w . ja v a2 s . c om*/ ArrayList<FileDescription> files = new ArrayList<FileDescription>(); String filename = "data.xlsx"; String filePath = dirPath + "/" + filename; OutputStream outputStream = new FileOutputStream(dirPath + "/data.xlsx"); files.add(new FileDescription(filename, filePath)); HashMap<String, String> selMultChoiceNames = new HashMap<String, String>(); Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); surveyNames = new HashMap<String, String>(); String basePath = null; String language = "none"; String dateName = null; int dateForm = 0; if (sId != 0) { PreparedStatement pstmt2 = null; PreparedStatement pstmtSSC = null; PreparedStatement pstmtQType = null; PreparedStatement pstmtDateFilter = null; try { basePath = GeneralUtilityMethods.getBasePath(request); // Prepare statement to get server side includes String sqlSSC = "select ssc.name, ssc.function, ssc.type, ssc.units from ssc ssc, form f " + " where f.f_id = ssc.f_id " + " and f.table_name = ? " + " order by ssc.id;"; pstmtSSC = sd.prepareStatement(sqlSSC); // Prepare the statement to get the question type and read only attribute String sqlQType = "select q.qtype, q.readonly from question q, form f " + " where q.f_id = f.f_id " + " and f.table_name = ? " + " and q.qname = ?;"; pstmtQType = sd.prepareStatement(sqlQType); ArrayList<FormDesc> formList = getFormList(sd, sId); /* * Create a work sheet for each form */ String surveyIdent = GeneralUtilityMethods.getSurveyIdent(sd, sId); for (FormDesc f : formList) { sheet = wb.createSheet("d_" + f.name); TableColumn c; int parentId = 0; if (f.parent > 0) { parentId = f.parent; } HashMap<String, String> selectMultipleColumnNames = new HashMap<String, String>(); // Get the list of table columns f.columnList = GeneralUtilityMethods.getColumnsInForm(sd, connectionResults, localisation, language, sId, surveyIdent, user, null, // Roles to apply parentId, f.f_id, f.table_name, false, // Don't include Read Only true, // Include parent key false, // Don't include "bad" columns false, // Don't include instance id true, // Include prikey true, // Include other meta data true, // Include preloads true, // instancename false, // Survey duration superUser, false, false, // Don't include audit data tz, false // mgmt ); // Get the list of spreadsheet columns ArrayList<ExchangeColumn> cols = new ArrayList<>(); for (int j = 0; j < f.columnList.size(); j++) { c = f.columnList.get(j); //String name = c.column_name; String qType = c.type; String questionName; String optionName = null; // Hack for meta values use the column name as the question name may have been translated if (c.isMeta) { questionName = c.column_name; } else { questionName = c.question_name; } if (qType.equals("select")) { optionName = c.option_name; selMultChoiceNames.put(c.column_name, optionName); // Add the name of sql column to a look up table for the get data stage String n = selectMultipleColumnNames.get(questionName); if (n == null) { // New Select multiple selectMultipleColumnNames.put(questionName, questionName); // Record that we have this select multiple addToHeader(sd, cols, "none", questionName, c.column_name, qType, sId, f, true); } } else { addToHeader(sd, cols, "none", questionName, c.column_name, qType, sId, f, true); } // Set the sql selection text for this column String selName = null; if (c.isGeometry()) { selName = "ST_AsTEXT(" + c.column_name + ") "; } else if (qType.equals("dateTime")) { // Return all timestamps at UTC with no time zone selName = "timezone('UTC', " + c.column_name + ") as " + c.column_name; } else { selName = c.column_name; } if (f.columns == null) { f.columns = selName; } else { f.columns += "," + selName; } } createHeader(cols, sheet, styles); try { getData(sd, connectionResults, formList, f, selMultChoiceNames, cols, sheet, styles, sId, null, null, dateName, dateForm, basePath, dirPath, files, incMedia); } catch (Exception e) { // Ignore errors if the only problem is that the tables have not been created yet if (e.getMessage() != null) { if (e.getMessage().contains("ERROR: relation") && e.getMessage().contains("does not exist")) { // all good } else { throw e; } } else { throw e; } } } } finally { try { if (pstmt2 != null) { pstmt2.close(); } } catch (SQLException e) { } try { if (pstmtSSC != null) { pstmtSSC.close(); } } catch (SQLException e) { } try { if (pstmtQType != null) { pstmtQType.close(); } } catch (SQLException e) { } try { if (pstmtDateFilter != null) { pstmtDateFilter.close(); } } catch (SQLException e) { } } } wb.write(outputStream); outputStream.close(); // XLSX temporary streaming files need to be deleted ((SXSSFWorkbook) wb).dispose(); return files; }
From source file:utilities.XLSCustomReportsManager.java
License:Open Source License
public void writeOversightDefinition(Connection sd, Connection cResults, int oId, String type, OutputStream outputStream, ReportConfig config, ResourceBundle localisation) throws Exception { boolean isXLSX; if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(); isXLSX = false;/*www. j a v a2 s .c o m*/ } else { wb = new SXSSFWorkbook(10); isXLSX = true; } Sheet sheet = wb.createSheet("definition"); sheet.createFreezePane(2, 1); Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); ArrayList<Column> cols = getColumnList(localisation); createHeader(cols, sheet, styles); processCustomReportListForXLS(config, sheet, styles, cols); wb.write(outputStream); outputStream.close(); // If XLSX then temporary streaming files need to be deleted if (isXLSX) { ((SXSSFWorkbook) wb).dispose(); } }
From source file:utilities.XLSResultsManager.java
License:Open Source License
public XLSResultsManager(String type) { if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(); isXLSX = false;// w w w.j a v a 2 s. co m } else { wb = new SXSSFWorkbook(10); isXLSX = true; } }
From source file:utilities.XLSXAdminReportsManager.java
License:Open Source License
public Response getNewReport(Connection sd, HttpServletRequest request, HttpServletResponse response, ArrayList<String> header, ArrayList<AR> report, String filename, boolean byProject, boolean bySurvey, boolean byDevice, int year, int month) { Response responseVal = null;/* w w w.java 2 s .c o m*/ String escapedFileName = null; try { escapedFileName = URLDecoder.decode(filename, "UTF-8"); escapedFileName = URLEncoder.encode(escapedFileName, "UTF-8"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } escapedFileName = escapedFileName.replace("+", " "); // Spaces ok for file name within quotes escapedFileName = escapedFileName.replace("%2C", ","); // Commas ok for file name within quotes if (header != null) { Workbook wb = null; int rowNumber = 0; Sheet dataSheet = null; CellStyle errorStyle = null; try { /* * Create XLSX File */ GeneralUtilityMethods.setFilenameInResponse(filename + "." + "xlsx", response); // Set file name wb = new SXSSFWorkbook(10); // Serialised output dataSheet = wb.createSheet("data"); rowNumber = 0; Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); CellStyle headerStyle = styles.get("header"); errorStyle = styles.get("error"); /* * Write the headers */ Row yearRow = dataSheet.createRow(rowNumber++); Cell cell = yearRow.createCell(0); // Year cell.setCellValue(localisation.getString("bill_year")); cell = yearRow.createCell(1); cell.setCellValue(year); Row monthRow = dataSheet.createRow(rowNumber++); cell = monthRow.createCell(0); // Month cell.setCellValue(localisation.getString("bill_month")); cell = monthRow.createCell(1); cell.setCellValue(month); rowNumber++; // blank row Row headerRow = dataSheet.createRow(rowNumber++); int colNumber = 0; while (colNumber < header.size()) { cell = headerRow.createCell(colNumber); cell.setCellStyle(headerStyle); cell.setCellValue(header.get(colNumber)); colNumber++; } int monthlyCol = 0; int allTimeCol = 0; int firstDataRow = rowNumber + 1; for (AR ar : report) { if (ar.usageInPeriod > 0 || ar.allTimeUsage > 0) { colNumber = 0; Row row = dataSheet.createRow(rowNumber++); cell = row.createCell(colNumber++); // ident cell.setCellValue(ar.userIdent); cell = row.createCell(colNumber++); // Name cell.setCellValue(ar.userName); cell = row.createCell(colNumber++); // User created if (ar.created != null) { cell.setCellStyle(styles.get("date")); cell.setCellValue(ar.created); } if (byProject || bySurvey) { cell = row.createCell(colNumber++); // Project cell.setCellValue(ar.p_id); cell = row.createCell(colNumber++); cell.setCellValue(ar.project); } if (bySurvey) { cell = row.createCell(colNumber++); // Survey cell.setCellValue(ar.s_id); cell = row.createCell(colNumber++); cell.setCellValue(ar.survey); } if (byDevice) { cell = row.createCell(colNumber++); // Device cell.setCellValue(ar.device); } monthlyCol = colNumber; cell = row.createCell(colNumber++); // Monthly Usage cell.setCellValue(ar.usageInPeriod); allTimeCol = colNumber; cell = row.createCell(colNumber++); // All time Usage cell.setCellValue(ar.allTimeUsage); } } // Add totals Row row = dataSheet.createRow(rowNumber++); // Monthly cell = row.createCell(monthlyCol); String colAlpha = getColAlpha(monthlyCol); String formula = "SUM(" + colAlpha + firstDataRow + ":" + colAlpha + (rowNumber - 1) + ")"; cell.setCellType(CellType.FORMULA); cell.setCellStyle(styles.get("bold")); cell.setCellFormula(formula); // All time cell = row.createCell(allTimeCol); colAlpha = getColAlpha(allTimeCol); formula = "SUM(" + colAlpha + firstDataRow + ":" + colAlpha + (rowNumber - 1) + ")"; cell.setCellType(CellType.FORMULA); cell.setCellStyle(styles.get("bold")); cell.setCellFormula(formula); } catch (Exception e) { log.log(Level.SEVERE, "Error", e); response.setHeader("Content-type", "text/html; charset=UTF-8"); String msg = e.getMessage(); if (msg.contains("does not exist")) { msg = localisation.getString("msg_no_data"); } Row dataRow = dataSheet.createRow(rowNumber + 1); Cell cell = dataRow.createCell(0); cell.setCellStyle(errorStyle); cell.setCellValue(msg); responseVal = Response.status(Status.OK).entity("Error: " + e.getMessage()).build(); } finally { try { OutputStream outputStream = response.getOutputStream(); wb.write(outputStream); wb.close(); outputStream.close(); ((SXSSFWorkbook) wb).dispose(); // Dispose of temporary files } catch (Exception ex) { log.log(Level.SEVERE, "Error", ex); } } } return responseVal; }