List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName
public static String createSafeSheetName(final String nameProposal)
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
/** * Access and output the grades spreadsheet for the reference, either for an assignment or all assignments in a context. * * @param out/* w w w . j a v a2s . co m*/ * The outputStream to stream the grades spreadsheet into. * @param ref * The reference, either to a specific assignment, or just to an assignment context. * @return Whether the grades spreadsheet is successfully output. * @throws IdUnusedException * if there is no object with this id. * @throws PermissionException * if the current user is not allowed to access this. */ public boolean getGradesSpreadsheet(final OutputStream out, final String ref) throws IdUnusedException, PermissionException { boolean retVal = false; String typeGradesString = REF_TYPE_GRADES + Entity.SEPARATOR; String context = ref.substring(ref.indexOf(typeGradesString) + typeGradesString.length()); // get site title for display purpose String siteTitle = ""; try { Site s = SiteService.getSite(context); siteTitle = s.getTitle(); } catch (Exception e) { // ignore exception M_log.debug(this + ":getGradesSpreadsheet cannot get site context=" + context + e.getMessage()); } // does current user allowed to grade any assignment? boolean allowGradeAny = false; List assignmentsList = getListAssignmentsForContext(context); for (int iAssignment = 0; !allowGradeAny && iAssignment < assignmentsList.size(); iAssignment++) { if (allowGradeSubmission(((Assignment) assignmentsList.get(iAssignment)).getReference())) { allowGradeAny = true; } } if (!allowGradeAny) { // not permitted to download the spreadsheet return false; } else { int rowNum = 0; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(siteTitle)); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.title")); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); // site title row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.site") + siteTitle); // download time row = sheet.createRow(rowNum++); row.createCell(0).setCellValue( rb.getString("download.spreadsheet.date") + TimeService.newTime().toStringLocalFull()); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); HSSFCellStyle style = wb.createCellStyle(); // this is the header row number int headerRowNumber = rowNum; // set up the header cells row = sheet.createRow(rowNum++); int cellNum = 0; // user enterprise id column HSSFCell cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.name")); // user name column cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.userid")); // starting from this row, going to input user data Iterator assignments = new SortedIterator(assignmentsList.iterator(), new AssignmentComparator("duedate", "true")); // site members excluding those who can add assignments List members = new ArrayList(); // hashmap which stores the Excel row number for particular user HashMap user_row = new HashMap(); List allowAddAnySubmissionUsers = allowAddAnySubmissionUsers(context); for (Iterator iUserIds = new SortedIterator(allowAddAnySubmissionUsers.iterator(), new AssignmentComparator("sortname", "true")); iUserIds.hasNext();) { String userId = (String) iUserIds.next(); try { User u = UserDirectoryService.getUser(userId); members.add(u); // create the column for user first row = sheet.createRow(rowNum); // update user_row Hashtable user_row.put(u.getId(), Integer.valueOf(rowNum)); // increase row rowNum++; // put user displayid and sortname in the first two cells cellNum = 0; row.createCell(cellNum++).setCellValue(u.getSortName()); row.createCell(cellNum).setCellValue(u.getDisplayId()); } catch (Exception e) { M_log.warn(" getGradesSpreadSheet " + e.getMessage() + " userId = " + userId); } } int index = 0; // the grade data portion starts from the third column, since the first two are used for user's display id and sort name while (assignments.hasNext()) { Assignment a = (Assignment) assignments.next(); int assignmentType = a.getContent().getTypeOfGrade(); // for column header, check allow grade permission based on each assignment if (!a.getDraft() && allowGradeSubmission(a.getReference())) { // put in assignment title as the column header rowNum = headerRowNumber; row = sheet.getRow(rowNum++); cellNum = (index + 2); cell = row.createCell(cellNum); // since the first two column is taken by student id and name cell.setCellStyle(style); cell.setCellValue(a.getTitle()); for (int loopNum = 0; loopNum < members.size(); loopNum++) { // prepopulate the column with the "no submission" string row = sheet.getRow(rowNum++); cell = row.createCell(cellNum); cell.setCellType(1); cell.setCellValue(rb.getString("listsub.nosub")); } // begin to populate the column for this assignment, iterating through student list for (Iterator sIterator = getSubmissions(a).iterator(); sIterator.hasNext();) { AssignmentSubmission submission = (AssignmentSubmission) sIterator.next(); String userId = submission.getSubmitterId(); if (a.isGroup()) { User[] _users = submission.getSubmitters(); for (int i = 0; _users != null && i < _users.length; i++) { userId = _users[i].getId(); if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } else { if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeDisplay(); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); // Setting grade display instead grade. cell.setCellValue(submission.getGradeDisplay()); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeDisplay()); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } } index++; } // output try { wb.write(out); retVal = true; } catch (IOException e) { M_log.warn(" getGradesSpreadsheet Can not output the grade spread sheet for reference= " + ref); } return retVal; } }
From source file:org.sakaiproject.lti.impl.ExporterExcel.java
License:Educational Community License
public ExporterExcel() { wb = new HSSFWorkbook(); sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(sheetName)); rowNum = 0; cellNum = 0; }
From source file:org.sakaiproject.sitestats.impl.report.ReportManagerImpl.java
License:Educational Community License
public byte[] getReportAsExcel(Report report, String sheetName) { List<Stat> statsObjects = report.getReportData(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(sheetName)); HSSFRow headerRow = sheet.createRow(0); // Add the column headers int ix = 0;//w ww .ja va 2 s . c o m if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_SITE)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_site")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_USER)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_id")); headerRow.createCell(ix++).setCellValue(msgs.getString("th_user")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOOL)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_tool")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_EVENT)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_event")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_resource")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE_ACTION)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_action")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATE) || isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEMONTH) || isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEYEAR)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_date")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_LASTDATE)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_lastdate")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOTAL)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_total")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_VISITS)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_visits")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_UNIQUEVISITS)) { headerRow.createCell(ix++).setCellValue(msgs.getString("th_uniquevisitors")); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DURATION)) { headerRow.createCell(ix++) .setCellValue(msgs.getString("th_duration") + " (" + msgs.getString("minutes_abbr") + ")"); } // Fill the spreadsheet cells Iterator<Stat> i = statsObjects.iterator(); while (i.hasNext()) { HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1); Stat se = i.next(); ix = 0; if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_SITE)) { try { Site site = M_ss.getSite(se.getSiteId()); row.createCell(ix++).setCellValue(site.getTitle()); } catch (IdUnusedException e) { logger.debug("can't find site with id: " + se.getSiteId()); row.createCell(ix++).setCellValue(se.getSiteId().toString()); } } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_USER)) { String userId = se.getUserId(); String userEid = null; String userName = null; if (userId != null) { if (("-").equals(userId)) { userEid = "-"; userName = msgs.getString("user_anonymous"); } else if (("?").equals(userId)) { userEid = "-"; userName = msgs.getString("user_anonymous_access"); } else { try { User user = M_uds.getUser(userId); userEid = user.getDisplayId(); userName = M_sm.getUserNameForDisplay(user); } catch (UserNotDefinedException e1) { userEid = userId; userName = msgs.getString("user_unknown"); } } } else { userName = msgs.getString("user_unknown"); } row.createCell(ix++).setCellValue(userEid); row.createCell(ix++).setCellValue(userName); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOOL)) { EventStat es = (EventStat) se; row.createCell(ix++).setCellValue(M_ers.getToolName(es.getToolId())); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_EVENT)) { EventStat es = (EventStat) se; row.createCell(ix++).setCellValue(M_ers.getEventName(es.getEventId())); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE)) { ResourceStat rs = (ResourceStat) se; row.createCell(ix++).setCellValue(rs.getResourceRef()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE_ACTION)) { ResourceStat rs = (ResourceStat) se; row.createCell(ix++).setCellValue(rs.getResourceAction()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATE)) { row.createCell(ix++).setCellValue(se.getDate().toString()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEMONTH)) { row.createCell(ix++).setCellValue(dateMonthFrmt.format(se.getDate())); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEYEAR)) { row.createCell(ix++).setCellValue(dateYearFrmt.format(se.getDate())); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_LASTDATE)) { row.createCell(ix++).setCellValue(se.getDate().toString()); } if (report.getReportDefinition().getReportParams().getSiteId() != null && !"".equals(report.getReportDefinition().getReportParams().getSiteId())) { } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOTAL)) { row.createCell(ix++).setCellValue(se.getCount()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_VISITS)) { SiteVisits sv = (SiteVisits) se; row.createCell(ix++).setCellValue(sv.getTotalVisits()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_UNIQUEVISITS)) { SiteVisits sv = (SiteVisits) se; row.createCell(ix++).setCellValue(sv.getTotalUnique()); } if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DURATION)) { SitePresence ss = (SitePresence) se; double durationInMin = ss.getDuration() == 0 ? 0 : Util.round((double) ss.getDuration() / 1000 / 60, 1); // in minutes row.createCell(ix++).setCellValue(durationInMin); } } ByteArrayOutputStream baos = null; try { baos = new ByteArrayOutputStream(); wb.write(baos); } catch (IOException e) { LOG.error("Error writing Excel bytes from SiteStats report", e); } finally { if (baos != null) { try { baos.close(); } catch (IOException e) { /* ignore */ } } } if (baos != null) { return baos.toByteArray(); } else { return new byte[0]; } }
From source file:org.traccar.reports.Events.java
License:Apache License
public static void getExcel(OutputStream outputStream, long userId, Collection<Long> deviceIds, Collection<Long> groupIds, Collection<String> types, Date from, Date to) throws SQLException, IOException { ReportUtils.checkPeriodLimit(from, to); ArrayList<DeviceReport> devicesEvents = new ArrayList<>(); ArrayList<String> sheetNames = new ArrayList<>(); HashMap<Long, String> geofenceNames = new HashMap<>(); HashMap<Long, String> maintenanceNames = new HashMap<>(); for (long deviceId : ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection<Event> events = Context.getDataManager().getEvents(deviceId, from, to); boolean all = types.isEmpty() || types.contains(Event.ALL_EVENTS); for (Iterator<Event> iterator = events.iterator(); iterator.hasNext();) { Event event = iterator.next(); if (all || types.contains(event.getType())) { long geofenceId = event.getGeofenceId(); long maintenanceId = event.getMaintenanceId(); if (geofenceId != 0) { if (Context.getGeofenceManager().checkItemPermission(userId, geofenceId)) { Geofence geofence = Context.getGeofenceManager().getById(geofenceId); if (geofence != null) { geofenceNames.put(geofenceId, geofence.getName()); }//from w ww . j ava 2 s. c om } else { iterator.remove(); } } else if (maintenanceId != 0) { if (Context.getMaintenancesManager().checkItemPermission(userId, maintenanceId)) { Maintenance maintenance = Context.getMaintenancesManager().getById(maintenanceId); if (maintenance != null) { maintenanceNames.put(maintenanceId, maintenance.getName()); } } else { iterator.remove(); } } } else { iterator.remove(); } } DeviceReport deviceEvents = new DeviceReport(); Device device = Context.getIdentityManager().getById(deviceId); deviceEvents.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceEvents.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId()); if (group != null) { deviceEvents.setGroupName(group.getName()); } } deviceEvents.setObjects(events); devicesEvents.add(deviceEvents); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); try (InputStream inputStream = new FileInputStream(templatePath + "/events.xlsx")) { org.jxls.common.Context jxlsContext = ReportUtils.initializeContext(userId); jxlsContext.putVar("devices", devicesEvents); jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("geofenceNames", geofenceNames); jxlsContext.putVar("maintenanceNames", maintenanceNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); ReportUtils.processTemplateWithSheets(inputStream, outputStream, jxlsContext); } }
From source file:org.traccar.reports.Route.java
License:Apache License
public static void getExcel(OutputStream outputStream, long userId, Collection<Long> deviceIds, Collection<Long> groupIds, Date from, Date to) throws SQLException, IOException { ReportUtils.checkPeriodLimit(from, to); ArrayList<DeviceReport> devicesRoutes = new ArrayList<>(); ArrayList<String> sheetNames = new ArrayList<>(); for (long deviceId : ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection<Position> positions = Context.getDataManager().getPositions(deviceId, from, to); DeviceReport deviceRoutes = new DeviceReport(); Device device = Context.getIdentityManager().getById(deviceId); deviceRoutes.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceRoutes.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId()); if (group != null) { deviceRoutes.setGroupName(group.getName()); }//w w w .ja v a 2 s . com } deviceRoutes.setObjects(positions); devicesRoutes.add(deviceRoutes); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); try (InputStream inputStream = new FileInputStream(templatePath + "/route.xlsx")) { org.jxls.common.Context jxlsContext = ReportUtils.initializeContext(userId); jxlsContext.putVar("devices", devicesRoutes); jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); ReportUtils.processTemplateWithSheets(inputStream, outputStream, jxlsContext); } }
From source file:org.traccar.reports.Stops.java
License:Apache License
public static void getExcel(OutputStream outputStream, long userId, Collection<Long> deviceIds, Collection<Long> groupIds, Date from, Date to) throws SQLException, IOException { ReportUtils.checkPeriodLimit(from, to); ArrayList<DeviceReport> devicesStops = new ArrayList<>(); ArrayList<String> sheetNames = new ArrayList<>(); for (long deviceId : ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection<StopReport> stops = detectStops(deviceId, from, to); DeviceReport deviceStops = new DeviceReport(); Device device = Context.getIdentityManager().getById(deviceId); deviceStops.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceStops.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId()); if (group != null) { deviceStops.setGroupName(group.getName()); }/* w w w .ja va 2 s. c o m*/ } deviceStops.setObjects(stops); devicesStops.add(deviceStops); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); try (InputStream inputStream = new FileInputStream(templatePath + "/stops.xlsx")) { org.jxls.common.Context jxlsContext = ReportUtils.initializeContext(userId); jxlsContext.putVar("devices", devicesStops); jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); ReportUtils.processTemplateWithSheets(inputStream, outputStream, jxlsContext); } }
From source file:org.traccar.reports.Trips.java
License:Apache License
public static void getExcel(OutputStream outputStream, long userId, Collection<Long> deviceIds, Collection<Long> groupIds, Date from, Date to) throws SQLException, IOException { ReportUtils.checkPeriodLimit(from, to); ArrayList<DeviceReport> devicesTrips = new ArrayList<>(); ArrayList<String> sheetNames = new ArrayList<>(); for (long deviceId : ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection<TripReport> trips = detectTrips(deviceId, from, to); DeviceReport deviceTrips = new DeviceReport(); Device device = Context.getIdentityManager().getById(deviceId); deviceTrips.setDeviceName(device.getName()); sheetNames.add(WorkbookUtil.createSafeSheetName(deviceTrips.getDeviceName())); if (device.getGroupId() != 0) { Group group = Context.getGroupsManager().getById(device.getGroupId()); if (group != null) { deviceTrips.setGroupName(group.getName()); }/*from w w w . java 2s .c o m*/ } deviceTrips.setObjects(trips); devicesTrips.add(deviceTrips); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); try (InputStream inputStream = new FileInputStream(templatePath + "/trips.xlsx")) { org.jxls.common.Context jxlsContext = ReportUtils.initializeContext(userId); jxlsContext.putVar("devices", devicesTrips); jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); ReportUtils.processTemplateWithSheets(inputStream, outputStream, jxlsContext); } }
From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java
License:Open Source License
/** * Writes the report as an XLS document//from w w w . j a v a 2 s.c o m */ private void writeSummaryReport(Map<QuestionGroupDto, List<QuestionDto>> questionMap, SummaryModel summaryModel, String sector, Workbook wb) throws Exception { String title = sector == null ? SUMMARY_LABEL.get(locale) : sector; Sheet sheet = null; int sheetCount = 2; String curTitle = WorkbookUtil.createSafeSheetName(title); while (sheet == null) { sheet = wb.getSheet(curTitle); if (sheet == null) { sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(curTitle)); } else { sheet = null; curTitle = title + " " + sheetCount; sheetCount++; } } CreationHelper creationHelper = wb.getCreationHelper(); Drawing patriarch = sheet.createDrawingPatriarch(); int curRow = 0; Row row = getRow(curRow++, sheet); if (sector == null) { createCell(row, 0, REPORT_HEADER.get(locale), headerStyle); } else { createCell(row, 0, sector + " " + REPORT_HEADER.get(locale), headerStyle); } for (QuestionGroupDto group : orderedGroupList) { if (questionMap.get(group) != null) { for (QuestionDto question : questionMap.get(group)) { if (!(QuestionType.OPTION == question.getType() || QuestionType.NUMBER == question.getType())) { continue; } else { if (summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector).size() == 0) { // if there is no data, skip the question continue; } } // for both options and numeric, we want a pie chart and // data table for numeric, we also want descriptive // statistics int tableTopRow = curRow++; int tableBottomRow = curRow; row = getRow(tableTopRow, sheet); // span the question heading over the data table sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 2)); createCell(row, 0, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); DescriptiveStats stats = summaryModel.getDescriptiveStatsForQuestion(question.getKeyId(), sector); if (stats != null && stats.getSampleCount() > 0) { sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 4, 5)); createCell(row, 4, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); } row = getRow(curRow++, sheet); createCell(row, 1, FREQ_LABEL.get(locale), headerStyle); createCell(row, 2, PCT_LABEL.get(locale), headerStyle); // now create the data table for the option count Map<String, Long> counts = summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector); int sampleTotal = 0; List<String> labels = new ArrayList<String>(); List<String> values = new ArrayList<String>(); int firstOptRow = curRow; for (Entry<String, Long> count : counts.entrySet()) { row = getRow(curRow++, sheet); String labelText = count.getKey(); if (labelText == null) { labelText = ""; } StringBuilder builder = new StringBuilder(); if (QuestionType.OPTION == question.getType() && !DEFAULT_LOCALE.equals(locale)) { String[] tokens = labelText.split("\\|"); // see if we have a translation for this option for (int i = 0; i < tokens.length; i++) { if (i > 0) { builder.append("|"); } if (question.getOptionContainerDto() != null && question.getOptionContainerDto().getOptionsList() != null) { boolean found = false; for (QuestionOptionDto opt : question.getOptionContainerDto() .getOptionsList()) { if (opt.getText() != null && opt.getText().trim().equalsIgnoreCase(tokens[i])) { builder.append(getLocalizedText(tokens[i], opt.getTranslationMap())); found = true; break; } } if (!found) { builder.append(tokens[i]); } } } } else { builder.append(labelText); } createCell(row, 0, builder.toString(), null); createCell(row, 1, count.getValue().toString(), null); labels.add(builder.toString()); values.add(count.getValue().toString()); sampleTotal += count.getValue(); } row = getRow(curRow++, sheet); createCell(row, 0, TOTAL_LABEL.get(locale), null); createCell(row, 1, sampleTotal + "", null); for (int i = 0; i < values.size(); i++) { row = getRow(firstOptRow + i, sheet); if (sampleTotal > 0) { createCell(row, 2, PCT_FMT.format((Double.parseDouble(values.get(i)) / sampleTotal)), null); } else { createCell(row, 2, PCT_FMT.format(0), null); } } tableBottomRow = curRow; if (stats != null && stats.getSampleCount() > 0) { int tempRow = tableTopRow + 1; row = getRow(tempRow++, sheet); createCell(row, 4, "N", null); createCell(row, 5, sampleTotal + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEAN_LABEL.get(locale), null); createCell(row, 5, stats.getMean() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_E_LABEL.get(locale), null); createCell(row, 5, stats.getStandardError() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEDIAN_LABEL.get(locale), null); createCell(row, 5, stats.getMedian() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MODE_LABEL.get(locale), null); createCell(row, 5, stats.getMode() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_D_LABEL.get(locale), null); createCell(row, 5, stats.getStandardDeviation() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, VAR_LABEL.get(locale), null); createCell(row, 5, stats.getVariance() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, RANGE_LABEL.get(locale), null); createCell(row, 5, stats.getRange() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MIN_LABEL.get(locale), null); createCell(row, 5, stats.getMin() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MAX_LABEL.get(locale), null); createCell(row, 5, stats.getMax() + "", null); if (tableBottomRow < tempRow) { tableBottomRow = tempRow; } } curRow = tableBottomRow; if (labels.size() > 0) { boolean hasVals = false; if (values != null) { for (String val : values) { try { if (val != null && new Double(val.trim()) > 0D) { hasVals = true; break; } } catch (Exception e) { // no-op } } } // only insert the image if we have at least 1 non-zero // value if (hasVals && generateCharts) { // now insert the graph int indx = wb.addPicture(JFreechartChartUtil.getPieChart(labels, values, getLocalizedText(question.getText(), question.getTranslationMap()), CHART_WIDTH, CHART_HEIGHT), Workbook.PICTURE_TYPE_PNG); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(0); anchor.setDy2(255); anchor.setCol1(6); anchor.setRow1(tableTopRow); anchor.setCol2(6 + CHART_CELL_WIDTH); anchor.setRow2(tableTopRow + CHART_CELL_HEIGHT); anchor.setAnchorType(2); patriarch.createPicture(anchor, indx); if (tableTopRow + CHART_CELL_HEIGHT > tableBottomRow) { curRow = tableTopRow + CHART_CELL_HEIGHT; } } } // add a blank row between questions getRow(curRow++, sheet); // flush the sheet so far to disk; we will not go back up ((SXSSFSheet) sheet).flushRows(0); // retain 0 last rows and // flush all others } } } }
From source file:piecework.export.concrete.ExportAsExcelWorkbookProvider.java
License:Educational Community License
public ExportAsExcelWorkbookProvider(String processDefinitionLabel, Map<String, String> headerMap, Pager<ProcessInstance> pager) { this.headerMap = headerMap; this.pager = pager; this.wb = new XSSFWorkbook(); Date now = new Date(); this.sheet = wb.createSheet( WorkbookUtil.createSafeSheetName(processDefinitionLabel + " Export - " + now.toString())); this.headerKeys = !headerMap.isEmpty() ? headerMap.keySet().toArray(new String[headerMap.size()]) : new String[0]; }
From source file:poi.hssf.usermodel.examples.NewSheet.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet(); // create with default name final String name = "second sheet"; wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name)); // setting sheet name later FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut);//from ww w. j av a 2 s . co m fileOut.close(); }