List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook SXSSFWorkbook
public SXSSFWorkbook(int rowAccessWindowSize)
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;// w w w. j av a 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 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;/*from w w w .ja v a2s. 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; 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;/*www. j a va 2 s. co m*/ 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; }