List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName
public static String createSafeSheetName(final String nameProposal)
From source file:bad.robot.excel.workbook.PoiWorkbook.java
License:Apache License
@Override public Editable insertSheet(String name) { workbook.createSheet(WorkbookUtil.createSafeSheetName(name)); return this; }
From source file:cn.edu.pku.lib.dataverse.ManageUserGroupPage.java
private File generateExcelRequestJoinGroupLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("Groups' user member")); Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle.getBundle("Bundle", locale) .getString("dataverse.permissions.groups.member.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);/*from w w w . j a v a2s. c om*/ for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs Set<AuthenticatedUser> authUsersSet = explicitGroup.getContainedAuthenticatedUsers(); int j = 1; Cell cell; for (AuthenticatedUser user : authUsersSet) { row = sheet.createRow(j); if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(b.getUserName()); cell = row.createCell(1); cell.setCellValue(b.getLastName()); cell = row.createCell(2); cell.setCellValue(b.getFirstName()); cell = row.createCell(3); if (b.getUserType() == UserType.ORDINARY) cell.setCellValue("ORDINARY"); else if (b.getUserType() == UserType.ADVANCE) cell.setCellValue("ADVANCE"); else cell.setCellValue(""); cell = row.createCell(4); cell.setCellValue(b.getAffiliation()); cell = row.createCell(5); cell.setCellValue(b.getPosition()); cell = row.createCell(6); cell.setCellValue(b.getDepartment()); cell = row.createCell(7); cell.setCellValue(b.getEmail()); cell = row.createCell(8); cell.setCellValue(b.getSpeciality()); cell = row.createCell(9); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(10); cell.setCellValue(b.getGender()); cell = row.createCell(11); cell.setCellValue(b.getEducation()); cell = row.createCell(12); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(13); cell.setCellValue(b.getSupervisor()); cell = row.createCell(14); cell.setCellValue(b.getCertificateType()); cell = row.createCell(15); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(16); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(17); cell.setCellValue(b.getCellphone()); cell = row.createCell(18); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(19); cell.setCellValue(b.getCountry()); cell = row.createCell(20); cell.setCellValue(b.getProvince()); cell = row.createCell(21); cell.setCellValue(b.getCity()); cell = row.createCell(22); cell.setCellValue(b.getAddress()); cell = row.createCell(23); cell.setCellValue(b.getZipCode()); cell = row.createCell(24); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(p.getUserName()); cell = row.createCell(1); cell.setCellValue(p.getLastName()); cell = row.createCell(2); cell.setCellValue(p.getFirstName()); cell = row.createCell(3); if (p.getUserType() == UserType.ORDINARY) cell.setCellValue("ORDINARY"); else if (p.getUserType() == UserType.ADVANCE) cell.setCellValue("ADVANCE"); else cell.setCellValue(""); cell = row.createCell(4); cell.setCellValue(p.getAffiliation()); cell = row.createCell(5); cell.setCellValue(p.getPosition()); cell = row.createCell(6); cell.setCellValue(p.getDepartment()); cell = row.createCell(7); cell.setCellValue(p.getEmail()); cell = row.createCell(8); cell.setCellValue(p.getSpeciality()); cell = row.createCell(9); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(10); cell.setCellValue(p.getGender()); cell = row.createCell(11); cell.setCellValue(p.getEducation()); cell = row.createCell(12); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(13); cell.setCellValue(p.getSupervisor()); cell = row.createCell(14); cell.setCellValue(p.getCertificateType()); cell = row.createCell(15); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(16); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(17); cell.setCellValue(p.getCellphone()); cell = row.createCell(18); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(19); cell.setCellValue(p.getCountry()); cell = row.createCell(20); cell.setCellValue(p.getProvince()); cell = row.createCell(21); cell.setCellValue(p.getCity()); cell = row.createCell(22); cell.setCellValue(p.getAddress()); cell = row.createCell(23); cell.setCellValue(p.getZipCode()); cell = row.createCell(24); cell.setCellValue("PKU IAAA"); } j++; } String filesRootDirectory = System.getProperty("dataverse.files.directory"); if (filesRootDirectory == null || filesRootDirectory.equals("")) { filesRootDirectory = "/tmp/files"; } File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID()); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); return file; } catch (IOException ioe) { logger.log(Level.SEVERE, null, ioe); } if (file.exists()) { file.delete(); } return null; }
From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java
private File generateExcelDownloadLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("File Download Statistic")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); Locale local = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle .getBundle("Bundle", FacesContext.getCurrentInstance().getViewRoot().getLocale()) .getString("log.filedownload.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);/*from w ww .ja v a 2 s .c o m*/ for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); final long size = 100L; UsageLogSearchQuery query = queryForFile.clone(); query.setSize(size); query.setDateHistogramInterval(null); UsageLogSearchResult searchResult = null; int i = 0; int j = 1; Cell cell; do { query.setFrom(i * size); searchResult = usageLogSearchService.search(query); List<EventLog> logs = searchResult.getEventLogs(); for (EventLog log : logs) { row = sheet.createRow(j); AuthenticatedUser user; if (log.getUserId().equals(":guest") || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) { cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(log.getAffiliation()); cell = row.createCell(9); cell.setCellValue(log.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); } else { if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(b.getAffiliation()); cell = row.createCell(9); cell.setCellValue(b.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); cell = row.createCell(12); cell.setCellValue(b.getDepartment()); cell = row.createCell(13); cell.setCellValue(b.getEmail()); cell = row.createCell(14); cell.setCellValue(b.getSpeciality()); cell = row.createCell(15); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(b.getGender()); cell = row.createCell(17); cell.setCellValue(b.getEducation()); cell = row.createCell(18); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(b.getSupervisor()); cell = row.createCell(20); cell.setCellValue(b.getCertificateType()); cell = row.createCell(21); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(b.getCellphone()); cell = row.createCell(24); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(b.getCountry()); cell = row.createCell(26); cell.setCellValue(b.getProvince()); cell = row.createCell(27); cell.setCellValue(b.getCity()); cell = row.createCell(28); cell.setCellValue(b.getAddress()); cell = row.createCell(29); cell.setCellValue(b.getZipCode()); cell = row.createCell(30); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(p.getAffiliation()); cell = row.createCell(9); cell.setCellValue(p.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); cell = row.createCell(12); cell.setCellValue(p.getDepartment()); cell = row.createCell(13); cell.setCellValue(p.getEmail()); cell = row.createCell(14); cell.setCellValue(p.getSpeciality()); cell = row.createCell(15); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(p.getGender()); cell = row.createCell(17); cell.setCellValue(p.getEducation()); cell = row.createCell(18); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(p.getSupervisor()); cell = row.createCell(20); cell.setCellValue(p.getCertificateType()); cell = row.createCell(21); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(p.getCellphone()); cell = row.createCell(24); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(p.getCountry()); cell = row.createCell(26); cell.setCellValue(p.getProvince()); cell = row.createCell(27); cell.setCellValue(p.getCity()); cell = row.createCell(28); cell.setCellValue(p.getAddress()); cell = row.createCell(29); cell.setCellValue(p.getZipCode()); cell = row.createCell(30); cell.setCellValue("PKU IAAA"); } } j++; } i++; } while (i < searchResult.getPages()); String filesRootDirectory = System.getProperty("dataverse.files.directory"); if (filesRootDirectory == null || filesRootDirectory.equals("")) { filesRootDirectory = "/tmp/files"; } File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID()); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); return file; } catch (IOException ioe) { logger.log(Level.SEVERE, null, ioe); } if (file.exists()) { file.delete(); } return null; }
From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java
private File generateExcelRequestJoinGroupLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("User Join Group Statistic")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle.getBundle("Bundle", locale).getString("log.requestjoingroup.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);/* ww w.j a v a 2s .c om*/ for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); final long size = 100L; UsageLogSearchQuery query = queryForGroup.clone(); query.setSize(size); query.setDateHistogramInterval(null); UsageLogSearchResult searchResult = null; int i = 0; int j = 1; Cell cell; do { query.setFrom(i * size); searchResult = usageLogSearchService.search(query); List<EventLog> logs = searchResult.getEventLogs(); for (EventLog log : logs) { row = sheet.createRow(j); AuthenticatedUser user; if (log.getUserId().equals(":guest") || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) { cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(log.getAffiliation()); cell = row.createCell(9); cell.setCellValue(log.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); } else { if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(b.getAffiliation()); cell = row.createCell(9); cell.setCellValue(b.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); cell = row.createCell(12); cell.setCellValue(b.getDepartment()); cell = row.createCell(13); cell.setCellValue(b.getEmail()); cell = row.createCell(14); cell.setCellValue(b.getSpeciality()); cell = row.createCell(15); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(b.getGender()); cell = row.createCell(17); cell.setCellValue(b.getEducation()); cell = row.createCell(18); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(b.getSupervisor()); cell = row.createCell(20); cell.setCellValue(b.getCertificateType()); cell = row.createCell(21); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(b.getCellphone()); cell = row.createCell(24); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(b.getCountry()); cell = row.createCell(26); cell.setCellValue(b.getProvince()); cell = row.createCell(27); cell.setCellValue(b.getCity()); cell = row.createCell(28); cell.setCellValue(b.getAddress()); cell = row.createCell(29); cell.setCellValue(b.getZipCode()); cell = row.createCell(30); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(p.getAffiliation()); cell = row.createCell(9); cell.setCellValue(p.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); cell = row.createCell(12); cell.setCellValue(p.getDepartment()); cell = row.createCell(13); cell.setCellValue(p.getEmail()); cell = row.createCell(14); cell.setCellValue(p.getSpeciality()); cell = row.createCell(15); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(p.getGender()); cell = row.createCell(17); cell.setCellValue(p.getEducation()); cell = row.createCell(18); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(p.getSupervisor()); cell = row.createCell(20); cell.setCellValue(p.getCertificateType()); cell = row.createCell(21); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(p.getCellphone()); cell = row.createCell(24); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(p.getCountry()); cell = row.createCell(26); cell.setCellValue(p.getProvince()); cell = row.createCell(27); cell.setCellValue(p.getCity()); cell = row.createCell(28); cell.setCellValue(p.getAddress()); cell = row.createCell(29); cell.setCellValue(p.getZipCode()); cell = row.createCell(30); cell.setCellValue("PKU IAAA"); } } j++; } i++; } while (i < searchResult.getPages()); String filesRootDirectory = System.getProperty("dataverse.files.directory"); if (filesRootDirectory == null || filesRootDirectory.equals("")) { filesRootDirectory = "/tmp/files"; } File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID()); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); return file; } catch (IOException ioe) { logger.log(Level.SEVERE, null, ioe); } if (file.exists()) { file.delete(); } return null; }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
/** * Creates a new sheet, ensuring that the name is safe and unique * @param workbook//from w w w . j a va2s . co m * @param sheetName * @return */ public Sheet createSheet(Workbook workbook, String sheetName) { Set<String> names = new HashSet<String>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { names.add(workbook.getSheetName(i)); } String safe = WorkbookUtil.createSafeSheetName(sheetName); String name; for (int i = 0;; i++) { name = safe + (i == 0 ? "" : " (" + i + ")"); if (!names.contains(name)) break; } Sheet sheet = wb.createSheet(name); sheet.setAutobreaks(true); sheet.setMargin(Sheet.LeftMargin, 1); sheet.setMargin(Sheet.RightMargin, 1); sheet.setMargin(Sheet.BottomMargin, .5); sheet.setMargin(Sheet.TopMargin, .5); sheet.setFitToPage(true); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 99); // Footer footer = sheet.getFooter(); // footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() ); return sheet; }
From source file:com.actelion.research.spiritapp.report.WeighingPerPhaseReport.java
License:Open Source License
@Override protected void populateWorkBook() throws Exception { List<Biosample> animals = study.getParticipantsSorted(); Map<Biosample, Map<String, Biosample>> bio2history = new HashMap<>(); //Load Weighings Test weightingTest = DAOTest.getTest(DAOTest.WEIGHING_TESTNAME); if (weightingTest == null) throw new Exception("Error test " + DAOTest.WEIGHING_TESTNAME + " not found"); DAOResult.attachOrCreateStudyResultsToTops(study, animals, null, null); for (Biosample b : animals) { bio2history.put(b, new HashMap<>()); List<Biosample> history = DAORevision.getHistory(b); for (Biosample b2 : history) { if (!study.equals(b2.getInheritedStudy())) continue; ActionTreatment t = getTreatment(b2); if (t != null) { //Add the treatment, if it is the most recent if (!bio2history.get(b).containsKey(t.getPhaseName())) { bio2history.get(b).put(t.getPhaseName(), b2); }/*from w w w.j a v a 2 s .c o m*/ } } } //Check phases where we have results, tratments Set<Phase> phases = new TreeSet<>(); for (Biosample b : animals) { for (Result r : b.getAuxResults(weightingTest.getName(), null)) { phases.add(r.getInheritedPhase()); } } for (Phase p : study.getPhases()) { for (StudyAction a : study.getStudyActions(p)) { if (a.getNamedTreatment() != null || a.isMeasureWeight()) { phases.add(p); break; } } } for (Phase phase : phases) { //Check if we need to display a compound boolean hasCompound2 = false; for (Biosample animal : animals) { Biosample h = bio2history.get(animal).get(phase.getShortName()); ActionTreatment t = getTreatment(h); if (t != null && t.getEff2().length() > 0) { hasCompound2 = true; } } //Print headers Phase after = phase.getNextPhase(); Sheet sheet = createSheet(wb, "BW " + phase.getShortName()); createHeadersWithTitle(sheet, study, "BW used for given dose " + (after == null ? "at " + phase.getShortName() : "from " + phase.getShortName() + " to " + after.getShortName())); //Create Header for Weighing & Doses int line = 3; int col = 0; set(sheet, line, col++, "Group", Style.S_TH_CENTER); set(sheet, line, col++, "ContainerId", Style.S_TH_CENTER); set(sheet, line, col++, "ParticipantId", Style.S_TH_CENTER); set(sheet, line, col++, "No", Style.S_TH_CENTER); set(sheet, line, col++, "Weight [g]", Style.S_TH_CENTER); set(sheet, line, col++, "Increase [%]", Style.S_TH_CENTER); set(sheet, line, col++, "Treatment", Style.S_TH_CENTER); set(sheet, line, col++, "Given Dose" + (hasCompound2 ? "1" : ""), Style.S_TH_CENTER); if (hasCompound2) { set(sheet, line, col++, "Given Dose2", Style.S_TH_CENTER); } set(sheet, line, col++, "Formulation", Style.S_TH_CENTER); set(sheet, line, col++, "Comments", Style.S_TH_CENTER); set(sheet, line, col++, "Date", Style.S_TH_CENTER); int maxCol = col - 1; line++; //Add Biosample Weighing Group lastGroup = null; ListHashMap<Group, Integer> group2Lines = new ListHashMap<>(); for (Biosample animal : animals) { //Gets Weight + Increase Result weighResult = animal == null ? null : animal.getAuxResult(weightingTest, phase); Result prevWeighResult = animal == null || weighResult == null ? null : Result.getPrevious(weighResult, animal.getAuxResults(DAOTest.WEIGHING_TESTNAME, null)); Double weight = weighResult == null ? null : weighResult.getOutputResultValues().get(0).getDoubleValue(); Double increase = null; if (weight != null && prevWeighResult != null && prevWeighResult.getOutputResultValues().get(0).getDoubleValue() != null && prevWeighResult.getOutputResultValues().get(0).getDoubleValue() > 0) { Double prev = prevWeighResult.getOutputResultValues().get(0).getDoubleValue(); increase = 100 * (weight - prev) / prev; } //Memorize line to compute averages group2Lines.add(animal.getInheritedGroup(), line); //Display data Biosample h = bio2history.get(animal).get(phase.getShortName()); ActionTreatment t = getTreatment(h); col = 0; set(sheet, line, col++, animal == null ? null : animal.getInheritedGroupString(SpiritFrame.getUsername()), Style.S_TD_LEFT); set(sheet, line, col++, animal == null ? null : animal.getContainerId(), Style.S_TD_CENTER); set(sheet, line, col++, animal == null ? null : animal.getTopParent().getSampleId(), Style.S_TD_CENTER); set(sheet, line, col++, animal == null ? null : animal.getSampleName(), Style.S_TD_CENTER); set(sheet, line, col++, weight, Style.S_TD_DOUBLE1_BLUE); set(sheet, line, col++, increase, Style.S_TD_DOUBLE1); set(sheet, line, col++, t == null ? null : t.getTreatmentName(), Style.S_TD_LEFT); set(sheet, line, col++, t == null ? null : t.getEff1(), Style.S_TD_DOUBLE1); if (hasCompound2) { set(sheet, line, col++, t == null ? null : t.getEff2(), Style.S_TD_DOUBLE1); } set(sheet, line, col++, t == null ? null : t.getFormulation(), Style.S_TD_LEFT); set(sheet, line, col++, t == null ? null : t.getComments(), Style.S_TD_LEFT); set(sheet, line, col++, weighResult == null ? (h == null ? null : h.getUpdDate()) : weighResult.getUpdDate(), Style.S_TD_DATE); //Separator if we change group Group group = animal.getInheritedGroup(); if (lastGroup != null && !lastGroup.equals(group)) { drawLineAbove(sheet, line, 0, maxCol, (short) 1); } lastGroup = animal.getInheritedGroup(); line++; } //Add weighing averages col = 4; line += 2; set(sheet, line, col++, "Weight [g]", (Style.S_TH_CENTER)); set(sheet, line, col++, "Increase [%]", (Style.S_TH_CENTER)); line++; for (Group group : study.getGroups()) { if (study.getParticipants(group).size() == 0) continue; List<Integer> lines = group2Lines.get(group); col = 0; set(sheet, line, col++, group.getBlindedName(SpiritFrame.getUsername()), Style.S_TD_LEFT); set(sheet, line, col++, "", Style.S_TD_LEFT); set(sheet, line, col++, "", Style.S_TD_LEFT); set(sheet, line, col++, "", Style.S_TD_LEFT); if (lines != null && lines.size() > 0) { setAverage(sheet, line, col, convertLinesToCells(lines, col++), Style.S_TD_DOUBLE1); setAverage(sheet, line, col, convertLinesToCells(lines, col++), Style.S_TD_DOUBLE1); } line++; } wb.setSelectedTab(wb.getSheetIndex(sheet)); wb.setActiveSheet(wb.getSheetIndex(sheet)); POIUtils.autoSizeColumns(sheet); } ///////////////////////////////////////////////////////////////////////////////// //Add a sheet for the summary weighing (i==0 --> "Weighing", i==1 --> "Increase") for (int i = 0; i < 2; i++) { Sheet sheet = createSheet(wb, WorkbookUtil.createSafeSheetName((i == 0 ? "BW Summary" : "BW Increase"))); createHeadersWithTitleSubtitle(sheet, study, "Summary", "Weighing " + (i == 0 ? "Summary [g]" : "Increase [%]")); int line = 3; int col = 0; set(sheet, line, col++, "Group", Style.S_TH_CENTER); set(sheet, line, col++, "Cage", Style.S_TH_CENTER); set(sheet, line, col++, "AnimalId", Style.S_TH_CENTER); set(sheet, line, col++, "No", Style.S_TH_CENTER); for (Phase phase : phases) { set(sheet, line, col++, " " + phase.getShortName() + " ", Style.S_TH_CENTER); } int maxCol = col - 1; line++; Group lastGroup = null; ListHashMap<Group, Integer> group2Lines = new ListHashMap<>(); for (Biosample biosample : animals) { //Memorize line to compute averages group2Lines.add(biosample.getInheritedGroup(), line); //Display data col = 0; set(sheet, line, col++, biosample.getInheritedGroupString(SpiritFrame.getUsername()), Style.S_TD_LEFT); set(sheet, line, col++, biosample.getContainerId(), Style.S_TD_CENTER); set(sheet, line, col++, biosample.getTopParent().getSampleId(), Style.S_TD_CENTER); set(sheet, line, col++, biosample.getSampleName(), Style.S_TD_CENTER); for (Phase phase : phases) { String cell = "\'" + WorkbookUtil.createSafeSheetName("BW " + phase.getShortName()) + "\'" + "!" + convertToCell(line, 4 + i); String formula = "IF(LEN(" + cell + ")>0, " + cell + ", \"\")"; try { setFormula(sheet, line, col, formula, Style.S_TD_DOUBLE1); } catch (Exception e) { e.printStackTrace(); set(sheet, line, col, formula, Style.S_TD_DOUBLE1); } col++; } Group group = biosample.getInheritedGroup(); if (lastGroup != null && !lastGroup.equals(group)) { drawLineAbove(sheet, line, 0, maxCol, (short) 1); } lastGroup = biosample.getInheritedGroup(); line++; } //Group Summary averages line++; for (Group group : study.getGroups()) { List<Integer> linesForGroup = group2Lines.get(group); col = 0; set(sheet, line, col++, group.getName(), Style.S_TD_LEFT); set(sheet, line, col++, "", Style.S_TD_CENTER); set(sheet, line, col++, "", Style.S_TD_CENTER); set(sheet, line, col++, "", Style.S_TD_CENTER); if (linesForGroup != null && linesForGroup.size() > 0) { for (int j = 0; j < phases.size(); j++) { List<Integer> linesForGroupPhase = new ArrayList<Integer>(linesForGroup); try { setAverage(sheet, line, col, convertLinesToCells(linesForGroupPhase, col), Style.S_TD_DOUBLE1); } catch (Exception e) { e.printStackTrace(); set(sheet, line, col, "IF(COUNT(" + convertLinesToCells(linesForGroupPhase, col) + "), AVERAGE(" + convertLinesToCells(linesForGroupPhase, col) + "), \"\")", Style.S_TD_DOUBLE1); } col++; } } line++; } wb.setSelectedTab(wb.getSheetIndex(sheet)); wb.setActiveSheet(wb.getSheetIndex(sheet)); POIUtils.autoSizeColumns(sheet); } }
From source file:com.base2.kagura.core.ExportHandler.java
License:Apache License
/** * Takes the output and transforms it into a Excel file. * @param out Output stream.//from w w w . j av a 2 s. com * @param rows Rows of data from reporting-core * @param columns Columns to list on report */ public void generateXls(OutputStream out, List<Map<String, Object>> rows, List<ColumnDef> columns) { try { Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName("Report"); // returns " O'Brien's sales " Sheet reportSheet = wb.createSheet(safeName); short rowc = 0; Row nrow = reportSheet.createRow(rowc++); short cellc = 0; if (rows == null) return; if (columns == null && rows.size() > 0) { columns = new ArrayList<ColumnDef>(CollectionUtils.collect(rows.get(0).keySet(), new Transformer() { @Override public Object transform(final Object input) { return new ColumnDef() { { setName((String) input); } }; } })); } if (columns != null) { for (ColumnDef column : columns) { Cell cell = nrow.createCell(cellc++); cell.setCellValue(column.getName()); } } for (Map<String, Object> row : rows) { nrow = reportSheet.createRow(rowc++); cellc = 0; for (ColumnDef column : columns) { Cell cell = nrow.createCell(cellc++); cell.setCellValue(String.valueOf(row.get(column.getName()))); } } wb.write(out); } catch (IOException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
@Override public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, boolean subTable) throws IOException { wb = new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName(filename); Sheet sheet = wb.createSheet(safeName); cellStyle = wb.createCellStyle();//from w ww . j ava 2 s . co m facetStyle = wb.createCellStyle(); titleStyle = wb.createCellStyle(); createCustomFonts(); int maxColumns = 0; StringTokenizer st = new StringTokenizer(tableId, ","); while (st.hasMoreElements()) { String tableName = (String) st.nextElement(); UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(), tableName); if (component == null) { throw new FacesException("Cannot find component \"" + tableName + "\" in view."); } if (!(component instanceof DataTable || component instanceof DataList)) { throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName() + "\", exporter must target a PrimeFaces DataTable/DataList."); } DataList list = null; DataTable table = null; int cols = 0; if (preProcessor != null) { preProcessor.invoke(context.getELContext(), new Object[] { wb }); } if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) { Row titleRow = sheet.createRow(sheet.getLastRowNum()); int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum(); Cell cell = titleRow.createCell(cellIndex); cell.setCellValue(new XSSFRichTextString(tableTitle)); Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle.setFont(titleFont); cell.setCellStyle(titleStyle); sheet.createRow(sheet.getLastRowNum() + 3); } if (component instanceof DataList) { list = (DataList) component; if (list.getHeader() != null) { tableFacet(context, sheet, list, "header"); } if (pageOnly) { exportPageOnly(context, list, sheet); } else { exportAll(context, list, sheet); } cols = list.getRowCount(); } else { table = (DataTable) component; int columnsCount = getColumnsCount(table); if (table.getHeader() != null && !subTable) { tableFacet(context, sheet, table, columnsCount, "header"); } if (!subTable) { tableColumnGroup(sheet, table, "header"); } addColumnFacets(table, sheet, ColumnType.HEADER); if (pageOnly) { exportPageOnly(context, table, sheet); } else if (selectionOnly) { exportSelectionOnly(context, table, sheet); } else { exportAll(context, table, sheet, subTable); } if (table.hasFooterColumn() && !subTable) { addColumnFacets(table, sheet, ColumnType.FOOTER); } if (!subTable) { tableColumnGroup(sheet, table, "footer"); } table.setRowIndex(-1); if (postProcessor != null) { postProcessor.invoke(context.getELContext(), new Object[] { wb }); } cols = table.getColumnsCount(); if (maxColumns < cols) { maxColumns = cols; } } sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding)); } if (!subTable) for (int i = 0; i < maxColumns; i++) { sheet.autoSizeColumn((short) i); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setPrintGridlines(true); writeExcelToResponse(context.getExternalContext(), wb, filename); }
From source file:com.eyeq.pivot4j.export.poi.ExcelExporter.java
License:Common Public License
/** * @param workbook/* w ww . ja v a2 s .co m*/ * @return */ protected Sheet createSheet(RenderContext context, Workbook workbook) { Sheet newSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(getSheetName(context, sheetIndex))); sheetIndex++; return newSheet; }
From source file:com.github.camaral.sheeco.Sheeco.java
License:Apache License
private Sheet createSheet(final HSSFWorkbook wb, final String payloadName) { String sheetName = WorkbookUtil.createSafeSheetName(payloadName); final Sheet sheet = wb.createSheet(sheetName); return sheet; }