List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle
void setCellStyle(CellStyle style);
Set the style for the cell.
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);// ww w . j ava 2s . co 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);// w w w . ja v a 2s . 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 = 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:co.com.runt.runistac.logica.ReporteLogica.java
public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(plantilla); XSSFSheet mySheet = wb.getSheetAt(0); for (int i = 0; i < mySheet.getLastRowNum(); i++) { Row row = mySheet.getRow(i);/*from w ww.jav a2 s.c om*/ if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) { for (String key : parametros.keySet()) { String valor = row.getCell(0).getStringCellValue(); valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key)); row.getCell(0).setCellValue(valor); } } } int rows = mySheet.getLastRowNum(); int i = 0; Row base = mySheet.getRow(rows); CellStyle[] cs = null; if (!datos.isEmpty()) { int cant = datos.get(0).length; cs = new CellStyle[cant]; for (int j = 0; j < cant; j++) { cs[j] = base.getCell(j).getCellStyle(); } } for (Object[] o : datos) { Row row = mySheet.createRow(rows + i); for (int j = 0; j < o.length; j++) { Cell c = row.createCell(j); String value = ""; if (o[j] != null) { if (o[j] instanceof String) { value = (String) o[j]; c.setCellValue(value); } else if (o[j] instanceof Integer) {//integer c.setCellValue((Integer) o[j]); } else if (o[j] instanceof Double) { c.setCellValue((Double) o[j]); } else if (o[j] instanceof Float) { c.setCellValue((Float) o[j]); } else if (o[j] instanceof BigDecimal) { c.setCellValue(((BigDecimal) o[j]).doubleValue()); } else if (o[j] instanceof Date) { c.setCellValue(((Date) o[j])); } else if (o[j] instanceof BigInteger) { c.setCellValue(((BigInteger) o[j]).intValue()); } else { c.setCellValue(o[j].toString()); System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass()); } } c.setCellStyle(cs[j]); } i++; } ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); return baos.toByteArray(); }
From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java
License:Open Source License
private void writeActorClassesTable(HSSFWorkbook workbook, HotspotsDataAnalyser analyzer) { HSSFSheet sheet = workbook.createSheet("ActorClasses"); // Actor classes Results Cell cell = sheet.createRow(0).createCell(0); HSSFRichTextString title = new HSSFRichTextString("ActorClasses Results"); title.applyFont(titleFont);//from www . j a v a 2s . c om cell.setCellValue(title); Row row = sheet.createRow(1); sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0)); row.createCell(0).setCellValue("Class"); sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1)); row.createCell(1).setCellValue("File"); sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 3)); row.createCell(2).setCellValue("Version"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 8)); row.createCell(4).setCellValue("Total Executions"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 13)); row.createCell(9).setCellValue("Non Deferrable Executions"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 18)); row.createCell(14).setCellValue("Critical Executions"); row = sheet.createRow(2); sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5)); row.createCell(4).setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 8)); row.createCell(6).setCellValue("Clock Cycles"); sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 10)); row.createCell(9).setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 13)); row.createCell(11).setCellValue("Clock Cycles"); sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 15)); row.createCell(14).setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(2, 2, 16, 18)); row.createCell(16).setCellValue("Clock Cycles"); row = sheet.createRow(3); row.createCell(2).setCellValue("ID"); row.createCell(3).setCellValue("Last Modification"); row.createCell(4).setCellValue("%"); row.createCell(5).setCellValue("abs"); row.createCell(6).setCellValue("%"); row.createCell(7).setCellValue("mean"); row.createCell(8).setCellValue("variance"); row.createCell(9).setCellValue("%"); row.createCell(10).setCellValue("abs"); row.createCell(11).setCellValue("%"); row.createCell(12).setCellValue("mean"); row.createCell(13).setCellValue("variance"); row.createCell(14).setCellValue("%"); row.createCell(15).setCellValue("abs"); row.createCell(16).setCellValue("%"); row.createCell(17).setCellValue("mean"); row.createCell(18).setCellValue("variance"); Map<ActorClass, ExtendExecData> cMap = analyzer.getSumDataMap(ActorClass.class, Key.CRITICAL_CLOCKCYCLES, Order.DECREASING); // row index int rowi = 4; for (Entry<ActorClass, ExtendExecData> entry : cMap.entrySet()) { ActorClass clazz = entry.getKey(); ExtendExecData exec = entry.getValue(); row = sheet.createRow(rowi); rowi++; row.createCell(0).setCellValue(clazz.getName()); row.createCell(1).setCellValue(clazz.getSourceFile()); row.createCell(2).setCellValue(clazz.getVersion().getId()); cell = row.createCell(3); cell.setCellValue(clazz.getVersion().getLastModificationDate()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellFormula("F" + rowi + "/Summary!A14*100"); row.createCell(5).setCellValue(exec.getTotalExec().getExecutions()); cell = row.createCell(6); cell.setCellFormula("H" + rowi + "/Summary!B14*100"); row.createCell(7).setCellValue(exec.getTotalExec().getClockCyclesMean()); row.createCell(8).setCellValue(exec.getTotalExec().getClockCyclesVariance()); cell = row.createCell(9); cell.setCellFormula("K" + rowi + "/Summary!E14*100"); row.createCell(10).setCellValue(exec.getNotDeferrableExec().getExecutions()); cell = row.createCell(11); cell.setCellFormula("M" + rowi + "/Summary!G14*100"); row.createCell(12).setCellValue(exec.getNotDeferrableExec().getClockCyclesMean()); row.createCell(13).setCellValue(exec.getNotDeferrableExec().getClockCyclesVariance()); cell = row.createCell(14); cell.setCellFormula("P" + rowi + "/Summary!J14*100"); row.createCell(15).setCellValue(exec.getCriticalExec().getExecutions()); cell = row.createCell(16); cell.setCellFormula("R" + rowi + "/Summary!L14*100"); row.createCell(17).setCellValue(exec.getCriticalExec().getClockCyclesMean()); row.createCell(18).setCellValue(exec.getCriticalExec().getClockCyclesVariance()); } }
From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java
License:Open Source License
private void writeSummary(HSSFWorkbook workbook, Network network, HotspotsDataAnalyser analyzer) { HSSFSheet sheet = workbook.createSheet("Summary"); // Network Results Cell cell = sheet.createRow(0).createCell(0); HSSFRichTextString title = new HSSFRichTextString("Hotspots and Algorithmic Bottlenecks Report Summary"); title.applyFont(titleFont);//from w w w. j a v a2 s .co m cell.setCellValue(title); // network name Row row = sheet.createRow(2); row.createCell(0).setCellValue("Network"); row.createCell(1).setCellValue(network.getName()); // network cal project row = sheet.createRow(3); row.createCell(0).setCellValue("CAL Project"); row.createCell(1).setCellValue(network.getCalProject()); // network source file row = sheet.createRow(4); row.createCell(0).setCellValue("Source File"); row.createCell(1).setCellValue(network.getSourceFile()); // network version sheet.addMergedRegion(new CellRangeAddress(5, 7, 0, 0)); row = sheet.createRow(5); row.createCell(0).setCellValue("Version"); row.createCell(1).setCellValue("Id"); row.createCell(2).setCellValue(network.getVersion().getId()); row = sheet.createRow(6); row.createCell(1).setCellValue("Last Modification Date"); cell = row.createCell(2); cell.setCellValue(network.getVersion().getLastModificationDate()); cell.setCellStyle(cellStyle); row = sheet.createRow(7); row.createCell(1).setCellValue("Versioning Date"); cell = row.createCell(2); cell.setCellValue(network.getVersion().getVersioningDate()); cell.setCellStyle(cellStyle); // [total | non deferrable | critical] row = sheet.createRow(10); sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 2)); cell = row.createCell(0); cell.setCellValue("Total Executions"); sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 7)); cell = row.createCell(3); cell.setCellValue("Non Deferrable Executions"); sheet.addMergedRegion(new CellRangeAddress(10, 10, 8, 12)); cell = row.createCell(8); cell.setCellValue("Critical Executions"); // [Executions | Workload | slack || Executions | workload || Executions // | workload ] row = sheet.createRow(11); sheet.addMergedRegion(new CellRangeAddress(11, 12, 0, 0)); cell = row.createCell(0); cell.setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(11, 11, 1, 2)); cell = row.createCell(1); cell.setCellValue("Clock Cycles"); sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 4)); cell = row.createCell(3); cell.setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(11, 11, 5, 7)); cell = row.createCell(5); cell.setCellValue("Clock Cycles"); sheet.addMergedRegion(new CellRangeAddress(11, 11, 8, 9)); cell = row.createCell(8); cell.setCellValue("Executions"); sheet.addMergedRegion(new CellRangeAddress(11, 11, 10, 12)); cell = row.createCell(10); cell.setCellValue("Clock Cycles"); // [* | mean , variance, % ... ] row = sheet.createRow(12); row.createCell(1).setCellValue("mean"); row.createCell(2).setCellValue("variance"); row.createCell(3).setCellValue("%"); row.createCell(4).setCellValue("abs"); row.createCell(5).setCellValue("%"); row.createCell(6).setCellValue("mean"); row.createCell(7).setCellValue("variance"); row.createCell(8).setCellValue("%"); row.createCell(9).setCellValue("abs"); row.createCell(10).setCellValue("%"); row.createCell(11).setCellValue("mean"); row.createCell(12).setCellValue("variance"); // write down data ExtendExecData sumData = analyzer.getSumData(); row = sheet.createRow(13); row.createCell(0).setCellValue(sumData.getTotalExec().getExecutions()); row.createCell(1).setCellValue(sumData.getTotalExec().getClockCyclesMean()); row.createCell(2).setCellValue(sumData.getTotalExec().getClockCyclesVariance()); cell = row.createCell(3); cell.setCellFormula("E14/A14*100"); row.createCell(4).setCellValue(sumData.getNotDeferrableExec().getExecutions()); cell = row.createCell(5); cell.setCellFormula("G14/B14*100"); row.createCell(6).setCellValue(sumData.getNotDeferrableExec().getClockCyclesMean()); row.createCell(7).setCellValue(sumData.getNotDeferrableExec().getClockCyclesVariance()); cell = row.createCell(8); cell.setCellFormula("J14/A14*100"); row.createCell(9).setCellValue(sumData.getCriticalExec().getExecutions()); cell = row.createCell(10); cell.setCellFormula("L14/B14*100"); row.createCell(11).setCellValue(sumData.getCriticalExec().getClockCyclesMean()); row.createCell(12).setCellValue(sumData.getCriticalExec().getClockCyclesVariance()); }
From source file:com.accenture.control.GerenciaPlanilhaTS.java
public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS) throws FileNotFoundException, IOException { FileInputStream arquivo = new FileInputStream(new File(dir)); HSSFWorkbook workbook = new HSSFWorkbook(arquivo); HSSFSheet sheetTS = workbook.getSheetAt(0); HSSFDataFormat format = workbook.createDataFormat(); HSSFCellStyle estilo = workbook.createCellStyle(); String formatData = "aaaa-mm-dd\"T12:00:00-03:00\""; int linha = 1; Row row = sheetTS.getRow(linha);/*from www. j a v a 2 s . c o m*/ Cell descriptionPlan = row.getCell(0); Cell prj = row.getCell(1); Cell fase = row.getCell(2); Cell testPhase = row.getCell(3); Cell testScriptName = row.getCell(4); Cell testScriptDescription = row.getCell(5); Cell stepNo = row.getCell(6); Cell stepDescription = row.getCell(7); Cell expectedResults = row.getCell(8); Cell product = row.getCell(9); Cell dataPlanejada = row.getCell(10); for (int i = 0; i < listTS.size(); i++) { estilo.setDataFormat(format.getFormat(formatData)); estilo.setFillBackgroundColor(HSSFColor.GREEN.index); row = sheetTS.getRow(linha); descriptionPlan = row.getCell(0); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); descriptionPlan.setCellValue(listTS.get(i).getTestPlan()); prj.setCellValue(listTS.get(i).getSTIPRJ()); fase.setCellValue(listTS.get(i).getFASE()); testPhase.setCellValue(listTS.get(i).getTestPhase()); testScriptName.setCellValue(listTS.get(i).getTestScriptName()); testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription()); stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO()); stepDescription.setCellValue(listTS.get(i).getStepDescription()); expectedResults.setCellValue(listTS.get(i).getExpectedResults()); product.setCellValue(listTS.get(i).getProduct()); dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada()); dataPlanejada.setCellStyle(estilo); linha = linha + 2; } FileOutputStream fileOut = new FileOutputStream(new File(dir)); workbook.write(fileOut); fileOut.close(); arquivo.close(); }
From source file:com.accenture.ts.dao.TesteCaseTSDAO.java
public boolean newTsSheet(String pathSheet, String nameSheet, List<TesteCaseTSBean> listTestCase) throws FileNotFoundException, IOException { boolean sucess = false; destinationSheet = new File(pathSheet); destinationSheet.mkdirs();// w ww . j a v a 2 s . c o m destinationSheet = new File(pathSheet + "\\" + nameSheet); sourceStheet = new File(sheetDefault); copySheet(sourceStheet, destinationSheet); FileInputStream fileSheet = new FileInputStream(destinationSheet); XSSFWorkbook workbook = new XSSFWorkbook(fileSheet); XSSFSheet sheetTS = workbook.getSheetAt(0); XSSFDataFormat format = workbook.createDataFormat(); XSSFCellStyle estilo = workbook.createCellStyle(); // String formatData = "aaaa-mm-dd\"T12:00:00-03:00\""; int linha = 1; Row row = sheetTS.getRow(linha); Cell descriptionPlan = row.getCell(0); Cell prj = row.getCell(1); Cell fase = row.getCell(2); Cell testPhase = row.getCell(3); Cell testScriptName = row.getCell(4); Cell testScriptDescription = row.getCell(5); Cell stepNo = row.getCell(6); Cell stepDescription = row.getCell(7); Cell expectedResults = row.getCell(8); Cell product = row.getCell(9); Cell dataPlanejada = row.getCell(10); if (listTestCase.size() > 0) { descriptionPlan.setCellValue(listTestCase.get(0).getTestPlan()); prj.setCellValue(listTestCase.get(0).getSTIPRJ()); fase.setCellValue(listTestCase.get(0).getFASE()); testPhase.setCellValue(listTestCase.get(0).getTestPhase()); } for (int i = 0; i < listTestCase.size(); i++) { // estilo.setDataFormat(format.getFormat(formatData)); // estilo.setFillBackgroundColor(HSSFColor.GREEN.index); row = sheetTS.getRow(linha); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); testScriptName.setCellValue(listTestCase.get(i).getTestScriptName()); testScriptDescription.setCellValue(listTestCase.get(i).getTestScriptDescription()); stepNo.setCellValue(listTestCase.get(i).getSTEP_NUMERO()); stepDescription.setCellValue(listTestCase.get(i).getStepDescription()); expectedResults.setCellValue(listTestCase.get(i).getExpectedResults()); product.setCellValue(listTestCase.get(i).getProduct()); estilo = (XSSFCellStyle) dataPlanejada.getCellStyle(); dataPlanejada.setCellValue(listTestCase.get(i).getDataPlanejada()); dataPlanejada.setCellStyle(estilo); linha = linha + 2; } FileOutputStream fileOut = new FileOutputStream(destinationSheet); workbook.write(fileOut); fileOut.close(); fileSheet.close(); sucess = true; return sucess; }
From source file:com.accenture.ts.dao.TesteCaseTSDAO.java
public boolean newTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase) throws FileNotFoundException, IOException { boolean sucess = false; destinationSheet = new File(pathSheet); destinationSheet.mkdirs();//from w w w. j a va 2 s . c o m destinationSheet = new File(pathSheet + "\\" + nameSheet + ".xlsx"); sourceStheet = new File(sheetDefault); copySheet(sourceStheet, destinationSheet); FileInputStream fileSheet = new FileInputStream(destinationSheet); XSSFWorkbook workbook = new XSSFWorkbook(fileSheet); XSSFSheet sheetTS = workbook.getSheetAt(0); XSSFDataFormat format = workbook.createDataFormat(); XSSFCellStyle estilo = workbook.createCellStyle(); // String formatData = "aaaa-mm-dd\"T12:00:00-03:00\""; int linha = 1; Row row = sheetTS.getRow(linha); Cell descriptionPlan = row.getCell(0); Cell prj = row.getCell(1); Cell fase = row.getCell(2); Cell testPhase = row.getCell(3); Cell testScriptName = row.getCell(4); Cell testScriptDescription = row.getCell(5); Cell stepNo = row.getCell(6); Cell stepDescription = row.getCell(7); Cell expectedResults = row.getCell(8); Cell product = row.getCell(9); Cell dataPlanejada = row.getCell(10); Cell complexidade = row.getCell(11); Cell automatizado = row.getCell(12); descriptionPlan.setCellValue(testCase.getTestPlan()); prj.setCellValue(testCase.getSTIPRJ()); fase.setCellValue(testCase.getFASE()); testPhase.setCellValue(testCase.getTestPhase()); // estilo.setDataFormat(format.getFormat(formatData)); // estilo.setFillBackgroundColor(HSSFColor.GREEN.index); row = sheetTS.getRow(linha); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); complexidade = row.getCell(11); automatizado = row.getCell(12); testScriptName.setCellValue(testCase.getTestScriptName()); testScriptDescription.setCellValue(testCase.getTestScriptDescription()); stepNo.setCellValue(testCase.getSTEP_NUMERO()); stepDescription.setCellValue(testCase.getStepDescription()); expectedResults.setCellValue(testCase.getExpectedResults()); product.setCellValue(testCase.getProduct()); estilo = (XSSFCellStyle) dataPlanejada.getCellStyle(); dataPlanejada.setCellValue(FunctiosDates.getDateActual()); dataPlanejada.setCellStyle(estilo); complexidade.setCellValue(testCase.getComplexidade()); automatizado.setCellValue(testCase.isAutomatizado()); linha = linha + 2; FileOutputStream fileOut = new FileOutputStream(destinationSheet); workbook.write(fileOut); fileOut.close(); fileSheet.close(); sucess = true; return sucess; }
From source file:com.accenture.ts.dao.TesteCaseTSDAO.java
public boolean createSpreadsheetTS(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception { boolean sucess = false; destinationSheet = new File(pathSheet); destinationSheet.mkdirs();//from www .j av a2s . c o m String sheetTI = pathSheet + "\\" + nameSheet; nameSheet = nameSheet.replace("xlsx", "xlsm"); ; destinationSheet = new File(pathSheet + "\\" + nameSheet); sourceStheet = new File("C:\\FastPlan\\sheets\\TS_NEW.xlsm"); logger.info("Realizando cpia da planilha"); boolean existInList = false; List<TesteCaseTSBean> testCasesAutomatizados = new ArrayList<TesteCaseTSBean>(); //run macro String cmd = "C:\\FastPlan\\runMacro.vbs"; Runtime.getRuntime().exec("cmd /c" + cmd); Thread.sleep(2000); copySheet(sourceStheet, destinationSheet); logger.info("Planilha copiada"); FileInputStream fileSheet = new FileInputStream(destinationSheet); XSSFWorkbook workbook = new XSSFWorkbook(fileSheet); XSSFSheet sheetTS = workbook.getSheetAt(0); XSSFCellStyle estilo = workbook.createCellStyle(); // workbook.setSheetName(workbook.getSheetIndex(sheetTS), FunctiosDates.dateToString(FunctiosDates.getDateActual(), "yyyy-MM-dd-HH-mm-ss")); int linha = 2; Row row = sheetTS.getRow(linha); Cell descriptionPlan = row.getCell(0); Cell release = row.getCell(1); Cell prj = row.getCell(2); Cell fase = row.getCell(3); Cell testPhase = row.getCell(4); Cell testScriptName = row.getCell(5); Cell testScriptDescription = row.getCell(6); Cell stepNo = row.getCell(7); Cell stepDescription = row.getCell(8); Cell expectedResults = row.getCell(9); Cell product = row.getCell(10); Cell dataPlanejada = row.getCell(11); Cell qtdSteps = row.getCell(12); Cell complexidade = row.getCell(13); Cell automatizado = row.getCell(14); logger.info("Inserindo dados do plano"); descriptionPlan.setCellValue(testPlan.getName()); release.setCellValue(testPlan.getRelease()); for (int i = 0; i < testPlan.getTestCase().size(); i++) { row = sheetTS.getRow(linha); descriptionPlan = row.getCell(0); release = row.getCell(1); prj = row.getCell(2); fase = row.getCell(3); testPhase = row.getCell(4); testScriptName = row.getCell(5); testScriptDescription = row.getCell(6); stepNo = row.getCell(7); stepDescription = row.getCell(8); expectedResults = row.getCell(9); product = row.getCell(10); dataPlanejada = row.getCell(11); qtdSteps = row.getCell(12); complexidade = row.getCell(13); automatizado = row.getCell(14); logger.info("Inserindo dados dos TCs"); System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - " + testPlan.getSti() + " - " + "row:" + linha); prj.setCellValue(testPlan.getSti()); fase.setCellValue(testPlan.getCrFase()); testPhase.setCellValue(testPlan.getTestPhase()); testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName()); testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription()); product.setCellValue(testPlan.getTestCase().get(i).getProduct()); estilo = (XSSFCellStyle) dataPlanejada.getCellStyle(); dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada()); dataPlanejada.setCellStyle(estilo); qtdSteps.setCellValue(testPlan.getTestCase().get(i).getListStep().size()); complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade()); //set colors if (i % 2 == 0) { System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - entrou"); // setColorCells(new Cell[]{descriptionPlan, release, prj, fase, testPhase, testScriptName, testScriptDescription, stepNo, // stepDescription, expectedResults, product, dataPlanejada, qtdSteps, complexidade}, workbook); XSSFCellStyle styleColor = (XSSFCellStyle) product.getCellStyle(); styleColor.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index); product.setCellStyle(styleColor); } for (int j = 0; j < testPlan.getTestCase().get(i).getListStep().size(); j++) { row = sheetTS.getRow(linha); stepNo = row.getCell(7); stepDescription = row.getCell(8); expectedResults = row.getCell(9); // // stepNo.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getNomeStep()); logger.info("Inserindo dados dos Steps"); stepNo.setCellValue(j + 1); stepDescription.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getDescStep()); expectedResults.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getResultadoStep()); linha = linha + 1; row = sheetTS.getRow(linha); stepNo = row.getCell(7); stepDescription = row.getCell(8); expectedResults = row.getCell(9); } linha = linha + 1; row = sheetTS.getRow(linha); descriptionPlan = row.getCell(0); release = row.getCell(1); prj = row.getCell(2); fase = row.getCell(3); testPhase = row.getCell(4); testScriptName = row.getCell(5); testScriptDescription = row.getCell(6); stepNo = row.getCell(7); stepDescription = row.getCell(8); expectedResults = row.getCell(9); product = row.getCell(10); dataPlanejada = row.getCell(11); qtdSteps = row.getCell(12); complexidade = row.getCell(13); automatizado = row.getCell(14); logger.info("Dados inseridos na planilha"); if (testPlan.getTestCase().get(i).isAutomatizado()) { for (int j = 0; j < testCasesAutomatizados.size(); j++) { if (testPlan.getTestCase().get(i).equals(testCasesAutomatizados.get(j).getTestScriptName())) { existInList = true; } } if (!existInList) { testCasesAutomatizados.add(testPlan.getTestCase().get(i)); existInList = true; } } } ExtraiPlanilha extraiPlanilha = new ExtraiPlanilha(); extraiPlanilha.exportTStoTI(testCasesAutomatizados, sheetTI); logger.info("Preparando para salvar planilha"); FileOutputStream fileOut = new FileOutputStream(destinationSheet); logger.info("Fim mtodo - new FileOutputStream(destinationSheet) "); logger.info("Tentando gravar na planilha."); workbook.write(fileOut); logger.info("Fim mtodo - workbook.write(fileOut)"); fileOut.close(); fileSheet.close(); sucess = true; logger.info("Planilha gerada."); return sucess; }
From source file:com.accenture.ts.dao.TesteCaseTSDAO.java
public boolean newTsSheet(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception { boolean sucess = false; destinationSheet = new File(pathSheet); destinationSheet.mkdirs();/*from w ww.j a v a 2s . c om*/ destinationSheet = new File(pathSheet + "\\" + nameSheet); sourceStheet = new File(sheetDefault); copySheet(sourceStheet, destinationSheet); FileInputStream fileSheet = new FileInputStream(destinationSheet); XSSFWorkbook workbook = new XSSFWorkbook(fileSheet); XSSFSheet sheetTS = workbook.getSheetAt(0); XSSFDataFormat format = workbook.createDataFormat(); XSSFCellStyle estilo = workbook.createCellStyle(); // String formatData = "aaaa-mm-dd\"T12:00:00-03:00\""; int linha = 1; Row row = sheetTS.getRow(linha); Cell descriptionPlan = row.getCell(0); Cell prj = row.getCell(1); Cell fase = row.getCell(2); Cell testPhase = row.getCell(3); Cell testScriptName = row.getCell(4); Cell testScriptDescription = row.getCell(5); Cell stepNo = row.getCell(6); Cell stepDescription = row.getCell(7); Cell expectedResults = row.getCell(8); Cell product = row.getCell(9); Cell dataPlanejada = row.getCell(10); Cell complexidade = row.getCell(11); descriptionPlan.setCellValue(testPlan.getName()); prj.setCellValue(testPlan.getSti()); fase.setCellValue(testPlan.getCrFase()); testPhase.setCellValue(testPlan.getTestPhase()); // estilo.setDataFormat(format.getFormat(formatData)); // estilo.setFillBackgroundColor(HSSFColor.GREEN.index); for (int i = 0; i < testPlan.getTestCase().size(); i++) { row = sheetTS.getRow(linha); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); complexidade = row.getCell(11); testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName()); testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription()); stepNo.setCellValue(testPlan.getTestCase().get(i).getSTEP_NUMERO()); stepDescription.setCellValue(testPlan.getTestCase().get(i).getStepDescription()); expectedResults.setCellValue(testPlan.getTestCase().get(i).getExpectedResults()); product.setCellValue(testPlan.getTestCase().get(i).getProduct()); estilo = (XSSFCellStyle) dataPlanejada.getCellStyle(); dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada()); dataPlanejada.setCellStyle(estilo); complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade()); linha = linha + 2; row = sheetTS.getRow(linha); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); complexidade = row.getCell(11); } FileOutputStream fileOut = new FileOutputStream(destinationSheet); workbook.write(fileOut); fileOut.close(); fileSheet.close(); sucess = true; return sucess; }