List of usage examples for org.apache.poi.ss.usermodel Workbook close
@Override void close() throws IOException;
From source file:twitterprojectwithoutmaven.Statistics.java
private void CreateFile(String name) { try {/* w w w . j a va 2 s. co m*/ Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet1 = wb.createSheet(sheet1Name);//create sheets Sheet sheet2 = wb.createSheet(sheet2Name); Sheet sheet3 = wb.createSheet(sheet3Name); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(name); //save file } catch (FileNotFoundException ex) { Logger.getLogger(Statistics.class.getName()).log(Level.SEVERE, null, ex); } wb.write(fileOut); wb.close(); } catch (IOException ex) { Logger.getLogger(Statistics.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:utilities.ExchangeManager.java
License:Open Source License
public ArrayList<String> getFormsFromXLSX(InputStream inputStream) throws Exception { ArrayList<String> forms = new ArrayList<String>(); Workbook wb = null; try {// ww w.j av a 2 s. c o m wb = new XSSFWorkbook(inputStream); int sheetCount = wb.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { String name = wb.getSheetName(i); if (name.startsWith("d_")) { // Legacy forms remove prefix added by older results exports 30th January 2018 name = name.substring(2); } forms.add(name); } } finally { try { wb.close(); } catch (Exception e) { } } return forms; }
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;/*from w w w.ja v a 2s. com*/ 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; }
From source file:utilities.XLSXAdminReportsManagerFormAccess.java
License:Open Source License
public Response getNewReport(Connection sd, HttpServletRequest request, HttpServletResponse response, String filename, int oId, String formIdent) { Response responseVal = null;//from www . j a va 2s . 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 Workbook wb = null; Sheet dataSheet = null; CellStyle errorStyle = null; int rowNumber = 0; int colNumber = 0; int hasAccessCol = 0; int hasNoAccessReasonCol = 0; try { /* * Create XLSX File */ GeneralUtilityMethods.setFilenameInResponse(filename + "." + "xlsx", response); // Set file name wb = new SXSSFWorkbook(10); // Serialised output dataSheet = wb.createSheet("data"); Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); CellStyle headerStyle = styles.get("header"); errorStyle = styles.get("errorStyle"); good = styles.get("good"); bad = styles.get("bad"); /* * Write the overview data */ SurveyManager sm = new SurveyManager(localisation, "UTC"); int sId = GeneralUtilityMethods.getSurveyId(sd, formIdent); Survey survey = sm.getById(sd, null, // cResults request.getRemoteUser(), sId, true, // full details null, // basePath null, // instance id false, // get results false, // generate dummmy values false, // get property type true, // get soft deleted false, // get hrk "internal", // get external options false, // get changed history true, // get roles true, // Pretend to be super user null, // geom format false, // Child surveys false // launched only ); /* * Write the form overview */ Row row = dataSheet.createRow(rowNumber++); Cell cell = row.createCell(0); // Ident cell.setCellValue(localisation.getString("rep_form_ident")); cell = row.createCell(1); cell.setCellValue(formIdent); row = dataSheet.createRow(rowNumber++); cell = row.createCell(0); // Found cell.setCellValue(localisation.getString("rep_found")); cell = row.createCell(1); if (survey != null) { cell.setCellStyle(good); cell.setCellValue(localisation.getString("rep_yes")); } else { cell.setCellStyle(bad); cell.setCellValue(localisation.getString("rep_no")); } if (survey != null) { row = dataSheet.createRow(rowNumber++); cell = row.createCell(0); // Survey Name cell.setCellValue(localisation.getString("name")); cell = row.createCell(1); cell.setCellValue(survey.displayName); row = dataSheet.createRow(rowNumber++); cell = row.createCell(0); // Project Name cell.setCellValue(localisation.getString("ar_project")); cell = row.createCell(1); cell.setCellValue(survey.projectName); } /* * Add the headings */ colNumber = 0; Row preHeaderRow = dataSheet.createRow(rowNumber++); row = dataSheet.createRow(rowNumber++); cell = row.createCell(colNumber++); // User Ident cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("ar_ident")); cell = row.createCell(colNumber++); // User Name cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("ar_user_name")); cell = row.createCell(colNumber++); // Has Access cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_has_access")); cell = row.createCell(colNumber++); // No Access reason cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_reason")); cell = row.createCell(colNumber++); // In Organisation cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_current_org")); cell = row.createCell(colNumber++); // Has Project cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_has_project")); // Add a marker that security groups have stated cell = preHeaderRow.createCell(colNumber); cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_sec_groups")); cell = row.createCell(colNumber++); // Admin cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_admin")); cell = row.createCell(colNumber++); // Analyst cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_analyst")); cell = row.createCell(colNumber++); // Enum cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_enum")); cell = row.createCell(colNumber++); // View Data cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_view")); int idx = 0; for (String roleName : survey.roles.keySet()) { // Role if (idx++ == 0) { // Add a marker that roles have stated cell = preHeaderRow.createCell(colNumber); cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("rep_roles")); } cell = row.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(roleName); } /* * Process the users */ UserManager um = new UserManager(localisation); ArrayList<User> users = um.getUserList(sd, oId, true, true); for (User u : users) { colNumber = 0; boolean hasProject = false; boolean isInOrg = false; boolean hasAdmin = false; boolean hasAnalyst = false; boolean hasEnum = false; boolean hasView = false; row = dataSheet.createRow(rowNumber++); cell = row.createCell(colNumber++); // User Ident cell.setCellValue(u.ident); cell = row.createCell(colNumber++); // User Name cell.setCellValue(u.name); hasAccessCol = colNumber++; // Come back to the overall yes/no has access hasNoAccessReasonCol = colNumber++; // Come back to the reason for no access cell = row.createCell(colNumber++); // Current Organisation isInOrg = u.current_org_id == survey.o_id; if (isInOrg ? setCellGood(cell) : setCellBad(cell)) ; cell = row.createCell(colNumber++); // Has Project for (Project p : u.projects) { if (p.id == survey.p_id) { hasProject = true; break; } } if (hasProject ? setCellGood(cell) : setCellBad(cell)) ; /* * Add security group cells */ for (UserGroup ug : u.groups) { if (ug.id == Authorise.ADMIN_ID) { hasAdmin = true; } else if (ug.id == Authorise.ANALYST_ID) { hasAnalyst = true; } else if (ug.id == Authorise.ENUM_ID) { hasEnum = true; } else if (ug.id == Authorise.VIEW_DATA_ID) { hasView = true; } } cell = row.createCell(colNumber++); // Has Admin if (hasAdmin ? setCellGood(cell) : setCellBad(cell)) ; cell = row.createCell(colNumber++); // Has Analyst if (hasAnalyst ? setCellGood(cell) : setCellBad(cell)) ; cell = row.createCell(colNumber++); // Has Enum if (hasEnum ? setCellGood(cell) : setCellBad(cell)) ; cell = row.createCell(colNumber++); // Has View if (hasView ? setCellGood(cell) : setCellBad(cell)) ; boolean hasRole = false; if (survey.roles.size() == 0) { hasRole = true; // No roles to worry about } else { for (String roleName : survey.roles.keySet()) { // Role boolean hasThisRole = false; for (Role r : u.roles) { if (r.name.equals(roleName)) { hasThisRole = true; hasRole = true; break; } } cell = row.createCell(colNumber++); // Role if (hasThisRole ? setCellGood(cell) : setCellBad(cell)) ; } } /* * Set the overall assessment of whether or not the user has access */ cell = row.createCell(hasAccessCol); boolean hasAccess = isInOrg && hasProject && (hasAdmin || hasAnalyst || hasEnum || hasView) && hasRole; if (hasAccess ? setCellGood(cell) : setCellBad(cell)) ; /* * Set the reason for no access */ if (!hasAccess) { StringBuffer reason = new StringBuffer(""); if (!isInOrg) { reason.append(localisation.getString("rep_reason_org")).append(". "); } if (!hasProject) { reason.append(localisation.getString("rep_reason_project")).append(". "); } if (!hasAdmin && !hasAnalyst && !hasEnum && !hasView) { reason.append(localisation.getString("rep_reason_sec_group")).append(". "); } if (!hasRole) { reason.append(localisation.getString("rep_reason_role")).append(". "); } cell = row.createCell(hasNoAccessReasonCol); cell.setCellValue(reason.toString()); } } } 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; }
From source file:utilities.XLSXAdminReportsNotifications.java
License:Open Source License
public Response getNewReport(Connection sd, HttpServletRequest request, HttpServletResponse response, String filename, int oId) { Response responseVal = null;/* w ww .ja v a2 s .c om*/ 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 Workbook wb = null; Sheet dataSheet = null; CellStyle errorStyle = null; int rowNumber = 0; int colNumber; PreparedStatement pstmt = null; try { /* * Create XLSX File */ GeneralUtilityMethods.setFilenameInResponse(filename + "." + "xlsx", response); // Set file name wb = new SXSSFWorkbook(10); // Serialised output dataSheet = wb.createSheet("data"); Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); CellStyle headerStyle = styles.get("header"); errorStyle = styles.get("errorStyle"); good = styles.get("good"); bad = styles.get("bad"); /* * Get the data */ NotificationManager nm = new NotificationManager(localisation); ArrayList<Notification> nList = nm.getAllNotifications(sd, pstmt, oId); /* * Add the headings */ colNumber = 0; Row row = dataSheet.createRow(rowNumber++); Cell cell = null; cell = row.createCell(colNumber++); // Project cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("ar_project")); cell = row.createCell(colNumber++); // Notification name cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("name")); cell = row.createCell(colNumber++); // Survey cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("a_name")); cell = row.createCell(colNumber++); // Trigger cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("a_trigger")); cell = row.createCell(colNumber++); // Target cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("a_target")); cell = row.createCell(colNumber++); // Details cell.setCellStyle(headerStyle); cell.setCellValue(localisation.getString("a_details")); /* * Add the data */ for (Notification n : nList) { colNumber = 0; row = dataSheet.createRow(rowNumber++); cell = row.createCell(colNumber++); // Project cell.setCellValue(n.project); cell = row.createCell(colNumber++); // Notification name cell.setCellValue(n.name); cell = row.createCell(colNumber++); // Survey cell.setCellValue(n.s_name); cell = row.createCell(colNumber++); // Trigger cell.setCellValue(n.trigger); cell = row.createCell(colNumber++); // Target cell.setCellValue(n.target); cell = row.createCell(colNumber++); // Details StringBuffer details = new StringBuffer(""); if (n.target.equals("email") && n.notifyDetails != null) { if (n.notifyDetails.emails != null && n.notifyDetails.emails.size() > 0) { for (String e : n.notifyDetails.emails) { if (details.length() > 0) { details.append(", "); } details.append(e); } } if (n.notifyDetails.emailQuestionName != null && !n.notifyDetails.emailQuestionName.equals("-1")) { if (details.length() > 0) { details.append(", "); } details.append(localisation.getString("a_eq")); } if (n.notifyDetails.emailMeta != null && !n.notifyDetails.emailMeta.equals("-1")) { if (details.length() > 0) { details.append(", "); } details.append(localisation.getString("a_eq2")); } } else if (n.target.equals("forward")) { details.append(n.remote_host).append(" : ").append(n.remote_s_name); } else if (n.target.equals("sms") && n.notifyDetails != null) { if (n.notifyDetails.emails != null && n.notifyDetails.emails.size() > 0) { details.append(localisation.getString("a_sms_1")); int count = 0; for (String e : n.notifyDetails.emails) { if (count++ > 0) { details.append(", "); } details.append(e); } } if (n.notifyDetails.emailQuestionName != null && !n.notifyDetails.emailQuestionName.equals("-1")) { if (details.length() > 0) { details.append(", "); } details.append(localisation.getString("a_sms_2")); } } cell.setCellValue(details.toString()); } } 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 { if (pstmt != null) { pstmt.close(); } } catch (SQLException e) { } 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; }
From source file:utilities.XLSXReportsManager.java
License:Open Source License
public Response getNewReport(Connection sd, Connection cResults, String username, HttpServletRequest request, HttpServletResponse response, int sId, String filename, boolean split_locn, boolean merge_select_multiple, String language, boolean exp_ro, boolean embedImages, boolean excludeParents, boolean hxl, int fId, Date startDate, Date endDate, int dateId, String filter, Transform transform, boolean meta, String tz) { Response responseVal = null;//from w w w . ja v a 2s . c om HashMap<ArrayList<OptionDesc>, String> labelListMap = new HashMap<ArrayList<OptionDesc>, String>(); log.info("userevent: " + username + " Export " + sId + " as an xlsx file to " + filename + " starting from form " + fId); String urlprefix = request.getScheme() + "://" + request.getServerName() + "/"; lm.writeLog(sd, sId, username, "view", "Export as: xlsx"); 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 (sId != 0) { PreparedStatement pstmt = null; Workbook wb = null; int rowNumber = 0; Sheet dataSheet = null; Sheet settingsSheet = null; CellStyle errorStyle = null; try { if (language == null) { // ensure a language is set language = "none"; } String surveyName = GeneralUtilityMethods.getSurveyName(sd, sId); /* * Get the list of forms and surveys to be exported */ ArrayList<QueryForm> queryList = null; QueryManager qm = new QueryManager(); queryList = qm.getFormList(sd, sId, fId); // Get a form list for this survey / form combo QueryForm startingForm = qm.getQueryTree(sd, queryList); // Convert the query list into a tree /* * Create XLSX File */ GeneralUtilityMethods.setFilenameInResponse(filename + "." + "xlsx", response); // Set file name wb = new SXSSFWorkbook(10); // Serialised output Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); CellStyle headerStyle = styles.get("header"); CellStyle wideStyle = styles.get("wide"); errorStyle = styles.get("error"); dataSheet = wb.createSheet(localisation.getString("rep_data")); settingsSheet = wb.createSheet(localisation.getString("rep_settings")); /* * Populate settings sheet */ int settingsRowIdx = 0; Row settingsRow = settingsSheet.createRow(settingsRowIdx++); Cell sk = settingsRow.createCell(0); Cell sv = settingsRow.createCell(1); sk.setCellStyle(headerStyle); sk.setCellValue(localisation.getString("a_tz")); sv.setCellValue(tz); settingsRow = settingsSheet.createRow(settingsRowIdx++); sk = settingsRow.createCell(0); sv = settingsRow.createCell(1); sk.setCellValue(localisation.getString("a_dfq")); sv.setCellStyle(headerStyle); sv.setCellValue(GeneralUtilityMethods.getQuestionNameFromId(sd, sId, dateId)); settingsRow = settingsSheet.createRow(settingsRowIdx++); sk = settingsRow.createCell(0); sv = settingsRow.createCell(1); sk.setCellValue(localisation.getString("a_st")); sv.setCellStyle(styles.get("date")); if (startDate != null) { sv.setCellValue(startDate); } settingsRow = settingsSheet.createRow(settingsRowIdx++); sk = settingsRow.createCell(0); sv = settingsRow.createCell(1); sk.setCellValue(localisation.getString("a_et")); sv.setCellStyle(styles.get("date")); if (endDate != null) { sv.setCellValue(endDate); } // Get the SQL for this query SqlDesc sqlDesc = QueryGenerator.gen(sd, cResults, localisation, sId, fId, language, SmapExportTypes.XLSX, urlprefix, true, exp_ro, excludeParents, labelListMap, false, false, null, null, null, username, startDate, endDate, dateId, false, // Super user - always apply filters startingForm, filter, transform, meta, false, tz); String basePath = GeneralUtilityMethods.getBasePath(request); // Populate data sheet rowNumber = 0; /* * Write the labels if language has been set */ if (language != null && !language.equals("none")) { Row headerRow = dataSheet.createRow(rowNumber++); int colNumber = 0; int dataColumn = 0; while (dataColumn < sqlDesc.column_details.size()) { ColValues values = new ColValues(); ColDesc item = sqlDesc.column_details.get(dataColumn); dataColumn = GeneralUtilityMethods.getColValues(null, values, dataColumn, sqlDesc.column_details, merge_select_multiple, surveyName); // Wide columns in a long to wide transformation are replaced by repeating versions of themselves so don't write the label here if (isWideColumn(transform, values.name)) { continue; } int tdIndex = getTransformIndex(transform, values.name); if (tdIndex >= 0) { /* * Replace this question with the wide labels */ for (String tc : transform.transforms.get(tdIndex).wideColumns) { for (String tv : transform.transforms.get(tdIndex).values) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(wideStyle); cell.setCellValue(tc + " - " + tv); } } } else if (split_locn && values.name.equals("the_geom")) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.label); cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.label); } else if (item.qType != null && item.qType.equals("select") && !merge_select_multiple && item.choices != null && item.compressed) { for (int i = 0; i < item.choices.size(); i++) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); String label = item.choices.get(i).k; if (i < item.optionLabels.size()) { label = item.optionLabels.get(i).label; } cell.setCellValue(values.label + " - " + label); } } else if (item.qType != null && item.qType.equals("rank") && !merge_select_multiple && item.choices != null) { for (int i = 0; i < item.choices.size(); i++) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.label + " - " + (i + 1)); } } else if (item.qType != null && item.qType.equals("select1") && item.optionLabels != null) { StringBuffer label = null; if (values.label == null) { label = new StringBuffer(""); } else { label = new StringBuffer(values.label); } label.append(" ("); for (OptionDesc o : item.optionLabels) { label.append(" ").append(o.value).append("=").append(o.label); } label.append(")"); Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(label.toString()); } else { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.label); } } } /* * Write Question Name Header */ Row headerRow = dataSheet.createRow(rowNumber++); int colNumber = 0; int dataColumn = 0; while (dataColumn < sqlDesc.column_details.size()) { ColValues values = new ColValues(); ColDesc item = sqlDesc.column_details.get(dataColumn); dataColumn = GeneralUtilityMethods.getColValues(null, values, dataColumn, sqlDesc.column_details, merge_select_multiple, surveyName); // Wide columns in a long to wide transformation are replaced by repeating versions of themselves so don't write the label here if (isWideColumn(transform, values.name)) { continue; } int tdIndex = getTransformIndex(transform, values.name); if (tdIndex >= 0) { /* * Replace this question with the wide question names */ for (String tc : transform.transforms.get(tdIndex).wideColumns) { String displayName = GeneralUtilityMethods.getDisplayName(sd, sId, tc); for (String tv : transform.transforms.get(tdIndex).values) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(wideStyle); cell.setCellValue(displayName + " - " + tv); } } } else if (split_locn && values.name.equals("the_geom")) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue("Latitude"); cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue("Longitude"); } else if (item.qType != null && item.qType.equals("select") && !merge_select_multiple && item.choices != null && item.compressed) { for (int i = 0; i < item.choices.size(); i++) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); if (item.selectDisplayNames) { cell.setCellValue(item.choices.get(i).v); // Just show the choice display name } else { cell.setCellValue(values.name + " - " + item.choices.get(i).v); } } } else if (item.qType != null && item.qType.equals("rank") && !merge_select_multiple && item.choices != null) { for (int i = 0; i < item.choices.size(); i++) { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.name + " - " + (i + 1)); } } else { Cell cell = headerRow.createCell(colNumber++); cell.setCellStyle(headerStyle); cell.setCellValue(values.name); } } /* * Accumulate data to be written into an array and write it out in a second pass * This supports functionality such as long to wide transforms where fewer records are written than read */ String key = ""; // transforms String previousKey = null; // transforms HashMap<String, HashMap<String, String>> transformData = null; // transforms pstmt = cResults.prepareStatement(sqlDesc.sql); cResults.setAutoCommit(false); // page the results to reduce memory usage pstmt.setFetchSize(100); log.info("Get results: " + pstmt.toString()); ResultSet rs = pstmt.executeQuery(); ArrayList<ReadData> dataItems = null; Row dataRow = null; while (rs.next()) { // If we are doing a transform then get the key of this record if (transform != null && transform.enabled) { key = getKeyValue(rs, transform); if (previousKey == null) { previousKey = key; } } /* * Write out the previous record if this report does not use transforms or the key has changed */ if (dataItems != null && (transform == null || !transform.enabled || (transform.enabled && !key.equals(previousKey)))) { previousKey = key; dataRow = dataSheet.createRow(rowNumber++); writeOutData(dataItems, transform, transformData, dataRow, wb, dataSheet, styles, embedImages, basePath, rowNumber); transformData = null; } dataItems = new ArrayList<>(); dataColumn = 0; while (dataColumn < sqlDesc.column_details.size()) { ColValues values = new ColValues(); ColDesc item = sqlDesc.column_details.get(dataColumn); dataColumn = GeneralUtilityMethods.getColValues(rs, values, dataColumn, sqlDesc.column_details, merge_select_multiple, surveyName); // Wide columns in a long to wide transformation are replaced by repeating versions of themselves so don't add the data if (isWideColumn(transform, values.name)) { continue; } int tdIndex = getTransformIndex(transform, values.name); if (tdIndex >= 0) { ReadData rd = new ReadData(values.name, true, "string"); dataItems.add(rd); if (transformData == null) { transformData = new HashMap<>(); } HashMap<String, String> itemTransform = transformData.get(values.name); if (itemTransform == null) { itemTransform = new HashMap<String, String>(); transformData.put(values.name, itemTransform); } for (String tv : transform.transforms.get(tdIndex).values) { if (tv.equals(values.value)) { // Valid value for (String tc : transform.transforms.get(tdIndex).wideColumns) { itemTransform.put(tc + " - " + values.value, rs.getString(sqlDesc.colNameLookup.get(tc))); } break; } } } else if (split_locn && values.value != null && values.value.startsWith("POINT")) { String coords[] = GeneralUtilityMethods.getLonLat(values.value); ReadData rd = new ReadData(values.name, false, values.type); dataItems.add(rd); if (coords.length > 1) { rd.values.add(coords[1]); rd.values.add(coords[0]); } else { rd.values.add(values.value); rd.values.add(values.value); } } else if (split_locn && values.value != null && (values.value.startsWith("POLYGON") || values.value.startsWith("LINESTRING"))) { // Can't split linestrings and polygons, leave latitude and longitude as blank ReadData rd = new ReadData(values.name, false, "string"); dataItems.add(rd); rd.values.add(values.value); rd.values.add(values.value); } else if (split_locn && values.type != null && values.type.equals("geopoint")) { // Geopoint that needs to be split but there is no data ReadData rd = new ReadData(values.name, false, "string"); dataItems.add(rd); rd.values.add(""); rd.values.add(""); } else if (item.qType != null && item.qType.equals("select") && !merge_select_multiple && item.choices != null && item.compressed) { String[] vArray = null; if (values.value != null) { vArray = values.value.split(" "); } ReadData rd = new ReadData(values.name, false, values.type); dataItems.add(rd); for (int i = 0; i < item.choices.size(); i++) { String v = "0"; if (vArray != null) { String choiceValue = item.choices.get(i).k; for (int k = 0; k < vArray.length; k++) { if (vArray[k].equals(choiceValue)) { v = "1"; break; } } } rd.values.add(v); } } else if (item.qType != null && item.qType.equals("rank") && !merge_select_multiple && item.choices != null) { String[] vArray = { "" }; if (values.value != null) { vArray = values.value.split(" "); } ReadData rd = new ReadData(values.name, false, values.type); dataItems.add(rd); for (int i = 0; i < item.choices.size(); i++) { if (i < vArray.length) { rd.values.add(vArray[i]); } else { rd.values.add(""); // Just write spaces } } } else if (item.qType != null && item.qType.equals("select1") && item.selectDisplayNames) { String value = values.value; // Convert the value into the display name for (int i = 0; i < item.choices.size(); i++) { String choiceValue = item.choices.get(i).k; if (choiceValue != null && choiceValue.equals(value)) { value = item.choices.get(i).v; } } ReadData rd = new ReadData(values.name, false, values.type); dataItems.add(rd); rd.values.add(value); rd.type = values.type; } else { ReadData rd = new ReadData(values.name, false, values.type); dataItems.add(rd); rd.values.add(values.value); rd.type = values.type; } } } cResults.setAutoCommit(true); // Write the last row dataRow = dataSheet.createRow(rowNumber++); writeOutData(dataItems, transform, transformData, dataRow, wb, dataSheet, styles, embedImages, basePath, rowNumber); } catch (Exception e) { try { cResults.setAutoCommit(true); } catch (Exception ex) { } log.log(Level.SEVERE, "Error", e); response.setHeader("Content-type", "text/html; charset=UTF-8"); lm.writeLog(sd, sId, username, "error", e.getMessage()); 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); } try { if (pstmt != null) { pstmt.close(); } } catch (SQLException e) { } } } return responseVal; }
From source file:wuliu.test.POITest2.java
License:Open Source License
public String export(String folderPath, List<WuliuMergedOrderModel> mergedOrders) throws EncryptedDocumentException, InvalidFormatException, IOException { File folder = createFolder(folderPath); List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders); if (CollectionUtils.isEmpty(mergedOrderLists)) { return null; }// ww w.j a v a 2 s.co m for (List<WuliuMergedOrderModel> item : mergedOrderLists) { InputStream inp = new FileInputStream( "/Users/admin/Workspace/workspace/wuliu/code/src/test/resources/template.xlsx"); Workbook wb = WorkbookFactory.create(inp); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Sheet sheet = wb.getSheetAt(0); fillSheet(sheet, item); evaluate(sheet, evaluator); File file = new File(folder, getName(item)); try { FileOutputStream outputStream = new FileOutputStream(file); wb.write(outputStream); outputStream.flush(); outputStream.close(); wb.close(); System.out.println("success"); } catch (Exception e) { System.out.println("It cause Error on WRITTING excel workbook: "); e.printStackTrace(); } } return folder.getAbsolutePath(); }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static boolean saveElfData(String filename, ElfDb db, double sensibilita, boolean save_grafico) { final DateTime[] times = db.getPeriods(); final ElfValue[][] dayvalues = db.getSampledData(); final int[] mediane = db.getOpValues(); final int[] maxs = db.getOpMaxDay(); final int[] counts = db.getOpValueCount(); final int maxi = db.getMaxidx(); Workbook wb = null; try {/*ww w . j a va 2s. co m*/ if (Utils.abortOnExistingAndDontOverwrite(filename)) return false; wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(Config.getResource("TitleStats")); int rown = 0; Row row = sheet.createRow(rown++); Cell cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitleMaxM")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleNumberOfData")); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); for (int i = 0; i < mediane.length; i++) { row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime())); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(3); cell.setCellValue(counts[i]); } // line with DataFunction max row = sheet.createRow(rown++); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - " + Utils.toDateString(times[maxi])); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi])); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // line with max final ElfValue maxvalue = db.getSelectedElfValue(new Comparator<ElfValue>() { @Override public int compare(ElfValue o1, ElfValue o2) { return o1.getValue() - o2.getValue(); } }); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")"); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue())); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getMax())); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // sheet containing all raw data Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas")); CellStyle dateTimeStyle2 = wb.createCellStyle(); dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); CellStyle doubleFormat2 = wb.createCellStyle(); DataFormat format2 = wb.createDataFormat(); doubleFormat2.setDataFormat(format2.getFormat("0.00")); rown = 0; row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleValue")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitlePeak")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(4); cell.setCellValue(Config.getResource("TitleDayMax")); cell = row.createCell(5); cell.setCellValue(Config.getResource("TitleMedianaMax")); cell = row.createCell(6); cell.setCellValue(Config.getResource("TitleSens")); cell = row.createCell(7); cell.setCellValue(Config.getResource("TitleQualityTarget")); cell = row.createCell(8); cell.setCellValue(Config.getResource("TitleAttentionValue")); for (int i = 0; i < dayvalues.length; i++) { final ElfValue[] day = dayvalues[i]; for (int k = 0; k < day.length; k++) { final ElfValue value = day[k]; final DateTime time = value.getTime(); row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateTimeStyle2); cell.setCellValue(Utils.toDateString(time)); cell = row.createCell(1); cell.setCellStyle(doubleFormat2); if (value.isValid()) cell.setCellValue(ElfValue.valueIntToDouble(value.getValue())); else cell.setCellValue(""); cell = row.createCell(2); cell.setCellStyle(doubleFormat2); if (value.isValid()) cell.setCellValue(ElfValue.valueIntToDouble(value.getMax())); else cell.setCellValue(""); cell = row.createCell(3); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(4); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(5); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(6); cell.setCellStyle(doubleFormat2); cell.setCellValue(sensibilita); cell = row.createCell(7); cell.setCellStyle(doubleFormat2); cell.setCellValue(3); cell = row.createCell(8); cell.setCellStyle(doubleFormat2); cell.setCellValue(10); } } if (save_grafico) { final int maxline = rown - 1; sheet = wb.createSheet(Config.getResource("TitleChart")); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); // LineChartData data = chart.getChartDataFactory().createLineChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setMinimum(0.0); leftAxis.setMaximum(10.0); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata, new CellRangeAddress(1, maxline, 0, 0)); ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 1, 1)); ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 6, 6)); ChartDataSource<Number> ys_qual = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 7, 7)); ChartDataSource<Number> ys_att = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 8, 8)); ScatterChartSeries data_val = data.addSerie(xs, ys_val); data_val.setTitle(Config.getResource("TitleMeasuredValues")); ScatterChartSeries data_sens = data.addSerie(xs, ys_sens); data_sens.setTitle(Config.getResource("TitleInstrumentSens")); ScatterChartSeries data_qual = data.addSerie(xs, ys_qual); data_qual.setTitle(Config.getResource("TitleQualityTarget")); ScatterChartSeries data_att = data.addSerie(xs, ys_att); data_att.setTitle(Config.getResource("TitleAttentionValue")); chart.plot(data, bottomAxis, leftAxis); } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); return true; } catch (Exception e) { Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(), Config.getResource("TitleError")); return false; } finally { if (wb != null) try { wb.close(); } catch (IOException e) { } } }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static void saveCorrelationsCurrents(String filename, CurrentElfDb db, final double imax, final double ui, final double ub) throws IOException { if (Utils.abortOnExistingAndDontOverwrite(filename)) return;/* w w w. ja v a2 s . com*/ Workbook wb = new XSSFWorkbook(); final String nDati = Config.getResource("TitleSheetDatas"); final String nDelta = Config.getResource("TitleSheetDelta"); final String nCalcs = Config.getResource("TitleSheetCalcs"); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(nDati); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle timeStyle1 = wb.createCellStyle(); timeStyle1.setDataFormat(createHelper.createDataFormat().getFormat("HH:mm")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); CellStyle percFormat1 = wb.createCellStyle(); percFormat1.setDataFormat(format1.getFormat("0.00%")); int rown = 0; Row row = sheet.createRow(rown++); final String[] CorrentiColonne = Config.getInstance().getXLSColumnNames(); for (int i = 0; i < CorrentiColonne.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(CorrentiColonne[i]); } final List<ElfValue> elfs = db.getElfDb(); final List<CurrentValue> currs = db.getCurrentDb(); for (int i = 0, maxi = Math.max(elfs.size(), currs.size()); i < maxi; i++) { final ElfValue e = i <= elfs.size() ? elfs.get(i) : null; final CurrentValue c = i <= currs.size() ? currs.get(i) : null; row = sheet.createRow(rown++); int columnnn = 0; if (c == null) { Cell cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); } else { Cell cell = row.createCell(columnnn++); cell.setCellValue(Utils.toDateString(c.getTime())); // data corrente cell.setCellStyle(dateStyle1); cell = row.createCell(columnnn++); cell.setCellStyle(timeStyle1); cell.setCellValue(Utils.toTimeString(c.getTime())); // ora corrente cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(c.getValue())); } if (e == null) { Cell cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); } else { Cell cell = row.createCell(columnnn++); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(e.getTime())); // data corrente cell = row.createCell(columnnn++); cell.setCellStyle(timeStyle1); cell.setCellValue(Utils.toTimeString(e.getTime())); // ora corrente cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(e.getValue())); // ora corrente } } final int total_rown = rown; // intermedi { sheet = wb.createSheet(nDelta); rown = 0; int columnnn; columnnn = 0; row = sheet.createRow(rown++); Cell cell = row.createCell(columnnn++); cell.setCellValue("dI"); cell = row.createCell(columnnn++); cell.setCellValue("dB"); cell = row.createCell(columnnn++); cell.setCellValue("dIdB"); cell = row.createCell(columnnn++); cell.setCellValue("dI^2"); cell = row.createCell(columnnn++); cell.setCellValue("dB^2"); cell = row.createCell(columnnn++); cell.setCellValue("Ri = Bi/Ii"); cell = row.createCell(columnnn++); cell.setCellValue("Ri^2"); for (int i = 2; i <= total_rown; i++) { columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!C" + i + "-" + nCalcs + "!$B$2"); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!F" + i + "-" + nCalcs + "!$B$3"); cell = row.createCell(columnnn++); cell.setCellFormula("A" + i + "*B" + i); cell = row.createCell(columnnn++); cell.setCellFormula("A" + i + "*A" + i); cell = row.createCell(columnnn++); cell.setCellFormula("B" + i + "*B" + i); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!F" + i + "/" + nDati + "!C" + i); cell = row.createCell(columnnn++); cell.setCellFormula("F" + i + "*F" + i); } } // correlazioni e calcoli finali { sheet = wb.createSheet(nCalcs); rown = 0; int columnnn; columnnn = 0; row = sheet.createRow(rown++); Cell cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleNumberOfData")); cell = row.createCell(columnnn++); cell.setCellFormula("COUNT(" + nDati + "!C:C)"); // B1 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleCurAvg")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDati + "!C:C)"); // B2 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleFieldAvg")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDati + "!F:F)"); // B3 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleRm")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDelta + "!F:F)"); // B4 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleImax")); cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(imax); // B5 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUI")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellValue(ui); // B6 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUB")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellValue(ub); // B7 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleURm")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellFormula("$B$6*$B$6+$B$7*$B$7-$B$6*$B$7*$B$9"); // B8 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleCorrelation")); cell = row.createCell(columnnn++); cell.setCellFormula("SUM(" + nDelta + "!C:C)/SQRT(SUM(" + nDelta + "!D:D)*SUM(" + nDelta + "!E:E))"); // B9 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleBmax")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$4*$B$5"); // B10 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleEperc")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$8*SUM(delta!G:G)/$B$1/$B$1"); // B11 = u(Rm)^2 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUBmax")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$5 * SQRT($B$11 + $B$4*$B$4*$B$6*$B$6)"); // B12 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleEBmax")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellFormula("2*$B$12/$B$10"); // B13 } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); wb.close(); fileOut.close(); }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static boolean saveCurrentsData(String filename, CurrentDb db, boolean save_grafico) { final DateTime[] times = db.getPeriods(); final CurrentValue[][] dayvalues = db.getSampledData(); final int[] mediane = db.getOpValues(); final int[] maxs = db.getOpMaxDay(); final int[] counts = db.getOpValueCount(); final int maxi = db.getMaxidx(); Workbook wb = null; try {//w w w . j a va 2 s. c o m if (Utils.abortOnExistingAndDontOverwrite(filename)) return false; wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(Config.getResource("TitleStats")); int rown = 0; Row row = sheet.createRow(rown++); Cell cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitleMaxM")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleNumberOfData")); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); for (int i = 0; i < mediane.length; i++) { row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime())); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(3); cell.setCellValue(counts[i]); } // line with DataFunction max row = sheet.createRow(rown++); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - " + Utils.toDateString(times[maxi])); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi])); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // line with max final CurrentValue maxvalue = db.getSelectedCurrentValue(new Comparator<CurrentValue>() { @Override public int compare(CurrentValue o1, CurrentValue o2) { return o1.getValue() - o2.getValue(); } }); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")"); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue())); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(""); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // sheet containing all raw data Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas")); CellStyle dateTimeStyle2 = wb.createCellStyle(); dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); CellStyle doubleFormat2 = wb.createCellStyle(); DataFormat format2 = wb.createDataFormat(); doubleFormat2.setDataFormat(format2.getFormat("0.00")); rown = 0; row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleValue")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitlePeak")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(4); cell.setCellValue(Config.getResource("TitleDayMax")); cell = row.createCell(5); cell.setCellValue(Config.getResource("TitleMedianaMax")); for (int i = 0; i < dayvalues.length; i++) { final CurrentValue[] day = dayvalues[i]; for (int k = 0; k < day.length; k++) { final CurrentValue value = day[k]; final DateTime time = value.getTime(); row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateTimeStyle2); cell.setCellValue(Utils.toDateString(time)); cell = row.createCell(1); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(value.getValue())); cell = row.createCell(2); // cell.setCellStyle(doubleFormat2); cell.setCellValue(""); cell = row.createCell(3); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(4); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(5); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); } } if (save_grafico) { final int maxline = rown - 1; sheet = wb.createSheet(Config.getResource("TitleChart")); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); // LineChartData data = chart.getChartDataFactory().createLineChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setMinimum(0.0); leftAxis.setMaximum(10.0); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata, new CellRangeAddress(1, maxline, 0, 0)); ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 1, 1)); ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 6, 6)); ScatterChartSeries data_val = data.addSerie(xs, ys_val); data_val.setTitle(Config.getResource("TitleMeasuredValues")); ScatterChartSeries data_sens = data.addSerie(xs, ys_sens); data_sens.setTitle(Config.getResource("TitleInstrumentSens")); chart.plot(data, bottomAxis, leftAxis); } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); return true; } catch (Exception e) { Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(), Config.getResource("TitleError")); return false; } finally { if (wb != null) try { wb.close(); } catch (IOException e) { } } }