List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
From source file:net.mcnewfamily.rmcnew.writer.AbstractXlsxWriter.java
License:Open Source License
protected void writeInstructions(Manifest manifest) { if (manifest != null) { XSSFSheet destSheet = workbook.createSheet(Constants.INSTRUCTIONS_SHEET); XSSFSheet srcSheet = manifest.getInstructions(); Util.copyXSSFSheet(srcSheet, destSheet); for (int columnIndex = 0; columnIndex < 13; columnIndex++) { destSheet.autoSizeColumn(columnIndex); }// w ww.j a v a2 s . c o m } else { throw new IllegalArgumentException("Cannot create instructions sheet from null Manifest!"); } }
From source file:net.mcnewfamily.rmcnew.writer.FinalManifestXlsxWriter.java
License:Open Source License
private void writePrioritizedRecords(Hub hub) { PrioritizedRecords prioritizedRecords = hub.getPrioritizedRecords(); String sheetName = hub.getName(); if (prioritizedRecords != null && !prioritizedRecords.isEmpty()) { XSSFSheet finalManifestSheet = prioritizedRecords.toSheetEssence(sheetName).toXSSFSheet(workbook); for (int columnIndex = 0; columnIndex < 13; columnIndex++) { finalManifestSheet.autoSizeColumn(columnIndex); }//from www. ja v a2s .c o m //String text = hub.generateUlnUsageString() + hub.generateOnwardMovementString(); //createTextBox(finalManifestSheet, hub.getClientAnchor(), text); } else { throw new IllegalArgumentException("Cannot create XLSX sheet from null or empty Records!"); } }
From source file:ObjectRepository.EquipDown.java
public void TestScenario() throws InterruptedException, IOException { System.out.println("Starting Execution on location " + Constant.Location + " to Down Equipment"); //driver.manage().window().setPosition(new Point(1900, 0)); driver.manage().window().maximize(); //driver.manage().window().setPosition(new Point(1900, 0)); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[3]/div[2]")).click(); //click on Personnel menu driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[4]/div[2]")).click(); //click on tasks menu //driver.manage().window().setPosition(new Point(-1900, 0)); // List<String> equipmentCategory = new ArrayList<String>(); // equipmentCategory.add("RearLoaders"); // equipmentCategory.add("DualBins"); // equipmentCategory.add("MechanicalBrooms"); // equipmentCategory.add("RoRos"); // equipmentCategory.add("EzPacks"); // equipmentCategory.add("Miscellaneous"); // equipmentCategory.add("Snow"); String[] equipmentCategory = new String[8]; equipmentCategory[0] = ("RearLoaders"); equipmentCategory[1] = ("DualBins"); equipmentCategory[2] = ("MechanicalBrooms"); equipmentCategory[3] = ("AlleyTrucks"); equipmentCategory[4] = ("RoRos"); equipmentCategory[5] = ("EzPacks"); equipmentCategory[6] = ("Miscellaneous"); equipmentCategory[7] = ("Snow"); //opening existing excel file call Data FileInputStream file = new FileInputStream( "C:\\Users\\skashem\\Desktop\\Excel_Selenium_Framework\\431_Down_Equipment.xlsx"); @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null;/*from w ww . jav a2s .com*/ int n = 1; SoftAssert softassert = new SoftAssert(); for (int itrCategory = 0; itrCategory < equipmentCategory.length; itrCategory++) { //equipmentCategory.get(itrCategory) //this.j = j; for (int j = 0; j <= Constant.equipIteration - 1; j++) //number of equipment to be down //this.j = j; { //Thread.sleep(300); if (driver .findElement(By.xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .isDisplayed()) { String equipInnertext = driver .findElement( By.xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .getText(); //this.variableName can be used if two elements have similar functioanlity and you want to split it and call the element from a specific category. That variable can be used outside of if statement and loop statement //String equipInnertext = driver.findElement(By.xpath("//*[@id='equipRearLoaders']/div[2]/div")).getText(); if (equipInnertext.length() > 3) { String[] Innertext = equipInnertext.split("\n"); int i = Constant.GetAllEquipmentCount(Innertext); //@SuppressWarnings("unchecked") List<String> equpNames = Constant.GetAllEquipmentNames(Innertext); // String random = (equpNames[new Random().nextInt(equpNames.length)]); Random rn = new Random(); int randomEqup = rn.nextInt(i); row = sheet.createRow(n); System.out.println("number of total equipment in " + equipmentCategory[itrCategory] + " Before Down is " + i); row.createCell(0).setCellValue(Constant.Location); //location sheet.autoSizeColumn(0); row.createCell(1).setCellValue(Constant.GetDateAndformat(0)); //date sheet.autoSizeColumn(1); String strEquipCount = driver.findElement(By.xpath( "//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[1]/h4/a/span[2]")) .getText(); String intEquipCount = Constant.EquipmentCount(strEquipCount); int intEquipCountBeforeDown = Double.valueOf(intEquipCount).intValue(); System.out.println("Equipment to down is " + equpNames.get(randomEqup)); row.createCell(2).setCellValue(equpNames.get(randomEqup)); //Equipment name from ops-board sheet.autoSizeColumn(2); row.createCell(3).setCellValue(equipmentCategory[itrCategory]); //Equipment category name sheet.autoSizeColumn(3); if (intEquipCountBeforeDown == i) { row.createCell(4).setCellValue(i); //number of equipment within a category Reporter.log( "Passed-Correct number of Equipment displayed on OPS Before down for category " + equipmentCategory[itrCategory] + " is " + i + " for iteration " + (j + 1)); //number of equipment within a category); sheet.autoSizeColumn(4); } else { row.createCell(4).setCellValue("Ops-Board category displays Count as: " + intEquipCountBeforeDown + " but actual equipment count is " + i); //number of equipment within a category //Assert.fail("Failed- InCorrect number of Equipment displayed on OPS after down for category " + equipmentCategory.get(itrCategory) + " is " + i + " for iteration " + (j + 1)); Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS before down for category " + equipmentCategory[itrCategory] + " is " + intEquipCountBeforeDown + " for iteration " + (j + 1)); softassert.assertEquals("Equipment count before down should be " + i, " But equipment count before down is " + intEquipCountBeforeDown); sheet.autoSizeColumn(4); //driver.close(); } String downEquipInnertext = driver.findElement(By.xpath("//*[@id='equipDown']")).getText(); String[] downInnertext = downEquipInnertext.split("\n"); int downItr = Constant.GetAllEquipmentCount(downInnertext); String strDownCount = driver .findElement(By.xpath("//*[@id='allUnavailableEquipment']/div[3]/a/div/span[2]")) .getText(); String intDownCount = Constant.EquipmentCount(strDownCount); int intDownCountBeforeDown = Double.valueOf(intDownCount).intValue(); if (intDownCountBeforeDown == downItr) { row.createCell(5).setCellValue(downItr); //number of equipment within a down category Reporter.log( "Passed-Correct number of Equipment displayed on OPS Before down for Down Category is " + downItr + " for iteration " + (j + 1)); //number of equipment within Down category); System.out.println( "Passed-Correct number of Equipment displayed on OPS Before down for Down Category is " + downItr + " for iteration " + (j + 1)); //number of equipment within Down category); sheet.autoSizeColumn(5); } else { System.out.println( "Failed-Incorrect number of Equipment displayed on OPS Before down for Down Category is " + intDownCountBeforeDown + " for iteration " + (j + 1)); row.createCell(5).setCellValue("Ops-Board category displays Count as: " + intDownCountBeforeDown + " but actual equipment count is " + downItr); //number of equipment within Down category Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS before down for Down category is " + intDownCountBeforeDown + " for iteration " + (j + 1)); softassert.assertEquals( "Equipment count before down in down category should be " + downItr, " But equipment count before down in Down category is " + intDownCountBeforeDown); sheet.autoSizeColumn(5); //driver.close(); } driver.findElement( By.xpath("//span[contains(@class,'piece-of-equipment') and contains(text(),'" + equpNames.get(randomEqup) + "')]")) .click(); String equipDown = driver .findElement(By.xpath("//*[@id='details']/div[1]/div/table/tbody/tr[1]/td[1]/h4")) .getText(); row.createCell(6).setCellValue(equipDown); //Equipment condition before down System.out.println("Equipment Condition is " + equipDown); if (equipDown == "Down") { //j--; //continue; row.createCell(6).setCellValue("Down"); //Equipment condition before down sheet.autoSizeColumn(6); } else { // Thread.sleep(350); driver.findElement( By.xpath("//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/button[2]")) .click(); //click on action drop down if (driver .findElement(By.xpath( "//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/ul/li[5]")) .isDisplayed()) //if down action button is displayed { driver.findElement(By.xpath( "//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/ul/li[5]")) .click(); //clicking on Down action Thread.sleep(1800); String strEquipmentType = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[1]/div[1]")).getText(); //equipment type modal window row.createCell(7).setCellValue(strEquipmentType); //Equipment Repair Location sheet.autoSizeColumn(7); String strEquipmentOwner = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[1]/div[2]")).getText(); //equipment Owner modal window row.createCell(8).setCellValue(strEquipmentOwner); //Equipment Repair Location sheet.autoSizeColumn(8); String strEquipmentCurrentLocation = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[2]/div[1]")).getText(); //equipment Current Location modal window row.createCell(9).setCellValue(strEquipmentCurrentLocation); //Equipment Repair Location modal window sheet.autoSizeColumn(9); String strEquipmentLicense = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[2]/div[2]")).getText(); //equipment License modal window row.createCell(10).setCellValue(strEquipmentLicense); //Equipment Repair Location sheet.autoSizeColumn(10); //click and enter a down code 1 from drop down driver.findElement(By.xpath("//*[@id='s2id_downCode1']")).click(); String downCodesInnertext = driver .findElement(By.xpath("//div[@id='select2-drop']")).getText(); String[] arrayDownCodeInnertext = downCodesInnertext.split("\n"); List<String> downCodes = Constant.GetAllDropDowns(arrayDownCodeInnertext); int j1 = Constant.GetAllDropDownCount(arrayDownCodeInnertext); int randomDownCode = rn.nextInt(j1); System.out.println( "Down code selected for down code 1 is " + downCodes.get(randomDownCode)); row.createCell(11).setCellValue(downCodes.get(randomDownCode)); //Equipment down code sheet.autoSizeColumn(11); List<WebElement> a = driver.findElements(By .xpath("//input[contains(@id,'s2id_autogen') and contains(@id,'search')]")); Actions actions = new Actions(driver); actions.moveToElement(a.get(0)); actions.click(); actions.sendKeys(downCodes.get(randomDownCode)).perform(); driver.findElement(By.xpath("//*[contains(@id,'select2-results')]/li[1]")).click(); //click and enter a Repair Location from drop down driver.findElement(By.xpath("//*[@id='s2id_repairLocation1']")).click(); String repairLocationInnertext = driver .findElement(By.xpath("//div[@id='select2-drop']")).getText(); String[] arrayRepairLocation = repairLocationInnertext.split("\n"); List<String> repairLocation = Constant.GetAllDropDowns(arrayRepairLocation); //System.out.println("Down code " + repairLocation.get(0)); int k = Constant.GetAllDropDownCount(arrayRepairLocation); int randomRepairLocation = rn.nextInt(k); System.out.println("Repair Location selected for down code 1 is " + repairLocation.get(randomRepairLocation)); row.createCell(12).setCellValue((repairLocation.get(randomRepairLocation))); //Equipment Repair Location sheet.autoSizeColumn(12); actions.moveToElement(a.get(1)); actions.click(); actions.sendKeys(repairLocation.get(randomRepairLocation)).perform(); driver.findElement(By.xpath("//*[contains(@id,'select2-results')]/li[1]")).click(); //Enter Reporter driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[1]/input")) .sendKeys("Reporter1"); row.createCell(13).setCellValue("Reporter1"); //Equipment Reporter sheet.autoSizeColumn(13); //Thread.sleep(300); //Enter Mechanic driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[2]/input")) .sendKeys("Mechanic1"); row.createCell(14).setCellValue("Mechanic1"); //Equipment Mechanic sheet.autoSizeColumn(14); //Enter Remarks driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[3]/input")) .sendKeys("Test"); //Click on Down Button driver.findElement(By.xpath("//*[@id='modal']/div[3]/button[2]")).click(); Thread.sleep(4000); driver.findElement(By.xpath("//*[contains(@ng-click,'closeEquipmentDetailsPane')]")) .click(); //Thread.sleep(1200); } // end of if down action button is displayed } // if equipment is down //Thread.sleep(500); driver.findElement( By.xpath("//span[contains(@class,'piece-of-equipment') and contains(text(),'" + equpNames.get(randomEqup) + "')]")) .click(); Thread.sleep(300); String equipAfterDown = driver .findElement(By.xpath("//*[@id='details']/div[1]/div/table/tbody/tr[1]/td[1]/h4")) .getText(); row.createCell(17).setCellValue(equipAfterDown); //Equipment condition after down driver.findElement(By.xpath("//*[contains(@ng-click,'closeEquipmentDetailsPane')]")) .click(); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[12]/div[2]")).click(); Thread.sleep(1000); String strDownRecentActivity0 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[1]")) .getText(); String strDownRecentActivity1 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[2]")) .getText(); String strDownRecentActivity2 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[3]")) .getText(); String strDownRecentActivity3 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[4]")) .getText(); String strDownRecentActivity4 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[6]")) .getText(); String strDownActivity = strDownRecentActivity0 + " " + strDownRecentActivity1 + " " + strDownRecentActivity2 + " " + strDownRecentActivity3 + " on " + strDownRecentActivity4; //System.out.println(strDownActivity); if (strDownActivity .matches("(?i).*Downed Equipment.*" + equpNames.get(randomEqup) + ".*")) { System.out.println("Equipment displays in recent activity for iteration " + (j + 1) + " after Down: " + strDownActivity); row.createCell(18).setCellValue(strDownActivity); //Recent Activity after down sheet.autoSizeColumn(18); } else { System.out.println("no records for Equipment appears in recent acitivity for iteration " + (j + 1) + " after Down"); row.createCell(18).setCellValue( "No records for Equipment appears in recent acitivity for iteration "); //Recent Activity after down sheet.autoSizeColumn(18); } Thread.sleep(200); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[12]/div[2]")).click(); String equipInnertextAfter = driver .findElement(By .xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .getText(); String[] InnertextAfter = equipInnertextAfter.split("\n"); int iAfter = Constant.GetAllEquipmentCount(InnertextAfter); System.out.println("number of total equipment in " + equipmentCategory[itrCategory] + " After Down is " + iAfter); String strEquipCountAfterDown = driver.findElement(By.xpath( "//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[1]/h4/a/span[2]")) .getText(); String intEquipCountAfterDown = Constant.EquipmentCount(strEquipCountAfterDown); int intEquipCountAfter = Double.valueOf(intEquipCountAfterDown).intValue(); if (intEquipCountAfter != iAfter) { row.createCell(15).setCellValue("Ops-Board category displays Count as: " + intEquipCountAfter + " but actual equipment count is " + iAfter); //number of equipment within a category sheet.autoSizeColumn(15); //Assert.fail("Failed- InCorrect number of Equipment displayed on OPS after down for category " + equipmentCategory.get(itrCategory) + " is " + intEquipCountAfter + " for iteration " + (j + 1)); //driver.close(); // try // { // Softassert.assertTrue(false); softassert.assertEquals("Equipment count after down should be " + iAfter, " But equipment count after down is " + intEquipCountAfter); //intEquipCountAfter // catch(AssertionError e) // { Reporter.log( "Failed- incorrect number of Equipment displayed on OPS after down for category " + equipmentCategory[itrCategory] + " is " + intEquipCountAfter + " for iteration " + (j + 1)); //number of equipment within a category; // } } else { row.createCell(15).setCellValue(iAfter); //number of equipment within a category Reporter.log( "Passed- correct number of Equipment displayed on OPS after down for category " + equipmentCategory[itrCategory] + " is " + iAfter + " for iteration " + (j + 1)); //number of equipment within a category; sheet.autoSizeColumn(15); } String downEquipInnertextAfter = driver.findElement(By.xpath("//*[@id='equipDown']")) .getText(); String[] downInnertextAfter = downEquipInnertextAfter.split("\n"); int downItrAfter = Constant.GetAllEquipmentCount(downInnertextAfter); String strDownCountAfter = driver .findElement(By.xpath("//*[@id='allUnavailableEquipment']/div[3]/a/div/span[2]")) .getText(); String intDownCountAfter = Constant.EquipmentCount(strDownCountAfter); int intDownCountBeforeDownAfter = Double.valueOf(intDownCountAfter).intValue(); if (intDownCountBeforeDownAfter == downItrAfter) { row.createCell(16).setCellValue(downItrAfter); //number of equipment within a down category Reporter.log( "Passed-Correct number of Equipment displayed on OPS After down for Down Category is " + downItrAfter + " for iteration " + (j + 1)); //number of equipment within Down category); System.out.println( "Passed-Correct number of Equipment displayed on OPS After down for Down Category is " + downItrAfter + " for iteration " + (j + 1)); //number of equipment within Down category); sheet.autoSizeColumn(16); } else { System.out.println( "Failed-Incorrect number of Equipment displayed on OPS After down for Down Category is " + intDownCountBeforeDownAfter + " for iteration " + (j + 1)); row.createCell(16).setCellValue( "Ops-Board category displays Count as: " + intDownCountBeforeDownAfter + " but actual equipment count is " + downItrAfter); //number of equipment within Down category Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS After down for Down category is " + intDownCountBeforeDownAfter + " for iteration " + (j + 1)); softassert.assertEquals( "Equipment count After down in down category should be " + downItrAfter, " But equipment count After down in Down category is " + intDownCountBeforeDownAfter); sheet.autoSizeColumn(16); //driver.close(); } Thread.sleep(500); n = n + 1; } else { break; } // end of equipment greater than 3 if } // end of is displayed } // End of number of equipment per category loop } // End of Category loop driver.close(); file.close(); //saving the file as different name FileOutputStream outFile = new FileOutputStream( "C:\\Users\\skashem\\Desktop\\Excel_Selenium_Framework\\431_Down_Equipment_" + Constant.Location + "_" + Constant.GetDateAndformat(0) + ".xlsx"); workbook.write(outFile); outFile.close(); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Create a 'Summary' sheet containing the table of averages *//*from w w w . ja v a 2 s. c o m*/ private void createSummarySheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj = getTestService().getTestRunMetadata(test, run); // Create the sheet XSSFSheet sheet = workbook.createSheet("Summary"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle summaryDataStyle = sheet.getWorkbook().createCellStyle(); summaryDataStyle.setAlignment(HorizontalAlignment.RIGHT); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Name:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(title); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { String description = (String) testRunObj.get(FIELD_DESCRIPTION); description = description == null ? "" : description; row.getCell(0).setCellValue("Description:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(description); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Progress (%):"); row.getCell(0).setCellStyle(headerStyle); Double progress = (Double) testRunObj.get(FIELD_PROGRESS); progress = progress == null ? 0.0 : progress; row.getCell(1).setCellValue(progress * 100); row.getCell(1).setCellType(XSSFCell.CELL_TYPE_NUMERIC); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("State:"); row.getCell(0).setCellStyle(headerStyle); String state = (String) testRunObj.get(FIELD_STATE); if (state != null) { row.getCell(1).setCellValue(state); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Started:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_STARTED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Finished:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_COMPLETED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Duration:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_DURATION); if (time > 0) { row.getCell(1).setCellValue(DurationFormatUtils.formatDurationHMS(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } rowCount++; rowCount++; // Create a header row row = sheet.createRow(rowCount++); // Header row String[] headers = new String[] { "Event Name", "Total Count", "Success Count", "Failure Count", "Success Rate (%)", "Min (ms)", "Max (ms)", "Arithmetic Mean (ms)", "Standard Deviation (ms)" }; int columnCount = 0; for (String header : headers) { XSSFCell cell = row.getCell(columnCount++); cell.setCellStyle(headerStyle); cell.setCellValue(header); } // Grab results and output them columnCount = 0; TreeMap<String, ResultSummary> summaries = collateResults(true); for (Map.Entry<String, ResultSummary> entry : summaries.entrySet()) { // Reset column count columnCount = 0; row = sheet.createRow(rowCount++); String eventName = entry.getKey(); ResultSummary summary = entry.getValue(); SummaryStatistics statsSuccess = summary.getStats(true); SummaryStatistics statsFail = summary.getStats(false); // Event Name row.getCell(columnCount++).setCellValue(eventName); // Total Count row.getCell(columnCount++).setCellValue(summary.getTotalResults()); // Success Count row.getCell(columnCount++).setCellValue(statsSuccess.getN()); // Failure Count row.getCell(columnCount++).setCellValue(statsFail.getN()); // Success Rate (%) row.getCell(columnCount++).setCellValue(summary.getSuccessPercentage()); // Min (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMin()); // Max (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMax()); // Arithmetic Mean (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMean()); // Standard Deviation (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getStandardDeviation()); } // Auto-size the columns for (int i = 0; i < 10; i++) { sheet.autoSizeColumn(i); } sheet.setColumnWidth(1, 5120); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createPropertiesSheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj;// w ww .j a va 2s . c o m try { testRunObj = services.getTestDAO().getTestRun(test, run, true); } catch (ObjectNotFoundException e) { logger.error("Test run not found!", e); return; } // Ensure we don't leak passwords testRunObj = AbstractRestResource.maskValues(testRunObj); BasicDBList propertiesList = (BasicDBList) testRunObj.get(FIELD_PROPERTIES); if (propertiesList == null) { logger.error("Properties not found!"); return; } // Order the properties, nicely TreeMap<String, DBObject> properties = new TreeMap<String, DBObject>(); for (Object propertyObj : propertiesList) { DBObject property = (DBObject) propertyObj; String key = (String) property.get(FIELD_NAME); properties.put(key, property); } XSSFSheet sheet = workbook.createSheet("Properties"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle propertyStyle = sheet.getWorkbook().createCellStyle(); propertyStyle.setAlignment(HorizontalAlignment.RIGHT); propertyStyle.setWrapText(true); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; XSSFCell cell = null; int cellCount = 0; row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue("Property"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Value"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Origin"); cell.setCellStyle(headerStyle); } cellCount = 0; // Iterate all the properties for the test run for (Map.Entry<String, DBObject> entry : properties.entrySet()) { DBObject property = entry.getValue(); String key = (String) property.get(FIELD_NAME); String value = (String) property.get(FIELD_VALUE); String origin = (String) property.get(FIELD_ORIGIN); row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue(key); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(value); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(origin); cell.setCellStyle(propertyStyle); } // Back to first column cellCount = 0; } // Size the columns sheet.autoSizeColumn(0); sheet.setColumnWidth(1, 15360); sheet.autoSizeColumn(2); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);// ww w . ja v a2s . c o m final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.gaia.gui.reports.ExcelReportExporter.java
License:Open Source License
/** * generate file EXCEL/*from w w w.java2 s . c om*/ * * @param table * @param template */ public static void generateExcel(SortableTreeTable table, ReportTemplate template) { FileOutputStream fileOut = null; XSSFWorkbook wb = new XSSFWorkbook(); try { String excelFilename = generateFileName(template); fileOut = new FileOutputStream(excelFilename); List<TemplateColumnItem> items = ReportBuilder.orderColumns(template.getTemplateColumnItems()); TemplateColumnItem item; int colMax = table.getColumnModel().getColumnCount(); if (items.size() < colMax) { colMax = items.size(); } XSSFSheet bomSheet = (XSSFSheet) wb.createSheet(template.getTemplateName()); XSSFRow headerRow = (XSSFRow) bomSheet.createRow(0); XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("Tahoma"); headerStyle.setFont(font); headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) headerRow.createCell(i); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(table.getColumnName(i)); cell.setCellStyle(headerStyle); } XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); font = wb.createFont(); font.setFontName("Tahoma"); cellStyle.setFont(font); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); for (int j = 0; j < table.getRowCount(); j++) { XSSFRow row = (XSSFRow) bomSheet.createRow(j + 1); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) row.createCell(i); cell.setCellStyle(cellStyle); Object value = table.getValueAt(j, i); item = (TemplateColumnItem) items.get(i); if (value != null && !value.equals(StringUtils.EMPTY_STRING)) { Class<?> clazz = Class.forName(item.getReturnType()); //used for Snapshot Export if (clazz == String.class && NumberUtils.isInteger(value.toString())) { clazz = Integer.class; } else if (clazz == String.class && NumberUtils.isNumber(value.toString())) { clazz = BigDecimal.class; } if (short.class.isAssignableFrom(clazz) || Short.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Short) value).intValue()); } else if (int.class.isAssignableFrom(clazz) || Integer.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(value.toString())); } else if (long.class.isAssignableFrom(clazz) || Long.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Long) value).intValue()); } else if (float.class.isAssignableFrom(clazz) || Float.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Float) value).doubleValue()); } else if (BigDecimal.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(value.toString())); } else if (double.class.isAssignableFrom(clazz) || Double.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Double) value); } else if (Date.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(HSSFDateUtil.getExcelDate((java.sql.Date) value)); } else { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(value.toString()); } } } } for (int i = 0; i < table.getColumnModel().getColumnCount(); i++) { bomSheet.autoSizeColumn(i); } SortableTreeTableModel model = (SortableTreeTableModel) table.getTreeTableModel(); AbstractSortableTreeTableNode root = (AbstractSortableTreeTableNode) model.getRoot(); groupNode(root, bomSheet); wb.write(fileOut); fileOut.flush(); fileOut.close(); nodeList.clear(); groupList.clear(); allNodeList.clear(); openExcel(excelFilename); } catch (ClassNotFoundException | IOException ex) { Exceptions.printStackTrace(ex); } finally { try { fileOut.close(); } catch (IOException ex) { logger.error(ex); } } }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;/*from w w w . j a v a2s .com*/ //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java
private static synchronized File createExportFile(String fileName, List<Object[]> materialeRows, List<Object[]> oreLavorateRows, List<Object[]> lavoriEseguitiRows) { //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Add general style for title headers to the workbook XSSFCellStyle titleStyle = createTitleFont(workbook); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Dati Pratica"); int nextRow = 0; Row r = sheet.createRow(nextRow++);/*w ww. ja va 2 s . co m*/ Cell c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.MATERIALE.getTitle()); nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.MATERIALE, sheet, materialeRows); r = sheet.createRow(nextRow++); c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.LAVORI_ESEGUITI.getTitle()); nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.LAVORI_ESEGUITI, sheet, lavoriEseguitiRows); r = sheet.createRow(nextRow++); c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.ORE_LAVORATE.getTitle()); AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.ORE_LAVORATE, sheet, oreLavorateRows); //adjust some known column size sheet.autoSizeColumn(1); try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); File f = new File(fileName); if (f.exists()) { return f; } } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java
License:Open Source License
private <T extends Number> void writeTable(XSSFSheet sheet, List<List<T>> tmp, String title, List<String> headers, List<String> values, boolean isContinuous) { writeTableHeaders(sheet, title, headers); ArrayList<String> rowHeaders = Lists.newArrayList(values); if (!isContinuous) rowHeaders.add("Total"); int counter = 0; int rownum = 4; for (String k : rowHeaders) { int cellnum = 0; XSSFRow row = sheet.createRow(rownum++); XSSFCell cell = row.createCell(cellnum++); cell.setCellValue(k);//from ww w. ja va 2s. c o m cell.setCellStyle(headerStyle); for (T obj : tmp.get(counter)) { cell = row.createCell(cellnum++); cell.setCellStyle(tableStyle); if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else cell.setCellValue(String.valueOf(obj)); } counter++; } sheet.autoSizeColumn(0); }