List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook close
@Override
public void close() throws IOException
From source file:com.svi.main.logic.ExtractAndPrint.java
private void writeLogFile(List<Nodes> nodesHolder) { DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm"); Date date = new Date(); Sheet mainSheet;/*from ww w.j av a 2s. c o m*/ Sheet dataSheet; Sheet elementSheet; Row dataSheetRow; Row elementSheetRow; InputStream fis; XSSFWorkbook workbook; File outputPath = new File(logPath + "\\Logs"); File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name int dataSheetRowCount = 1; int elementSheetRowCount = 1; int totalElementException = 0; try { if (!Files.exists(outputPath.toPath())) { Files.createDirectories(outputPath.toPath()); } fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx"); workbook = new XSSFWorkbook(fis); //Style for exception sheet XSSFCellStyle style = workbook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //Get data Sheet mainSheet = workbook.getSheetAt(0); writeProjectDetails(mainSheet); dataSheet = workbook.getSheetAt(4); dataSheetRow = dataSheet.createRow(0); elementSheet = workbook.getSheetAt(1); elementSheetRow = elementSheet.createRow(0); //Write excel headers writeDataSheetHeaders(dataSheetRow); writeElementSheetHeaders(elementSheetRow); //Set progress bar values progress = new AtomicInteger(0); total = new AtomicInteger(nodesHolder.size()); mf.setJprogressValues(total, progress); // Sort the nodes per Node ID Collections.sort(nodesHolder, new Comparator<Nodes>() { public int compare(Nodes o1, Nodes o2) { return o1.getNodeId().compareTo(o2.getNodeId()); } }); //Write Data Sheet for (Nodes node : nodesHolder) { mf.loader(); dataSheetRow = dataSheet.createRow(dataSheetRowCount++); writeDataSheet(node, dataSheetRow); } for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { totalElementException++; } } } progress = new AtomicInteger(0); total = new AtomicInteger(totalElementException); mf.setJprogressValues(total, progress); //Write exception sheet for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { elementSheetRow = elementSheet.createRow(elementSheetRowCount++); writeElementSheet(e, elementSheetRow); mf.elementLoader(); } } } XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook); try (FileOutputStream outputStream = new FileOutputStream(outputFile)) { workbook.write(outputStream);//Write the Excel File outputStream.close(); } workbook.close(); fis.close(); mf.generateMessage(); } catch (Exception ex) { Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.unicomer.oer.harvester.reader.ExcelBrokerRemoteReader.java
public List<Set<Entity>> read() throws Exception { List<Set<Entity>> list = new ArrayList<Set<Entity>>(); HashMap<String, Entity> servicesMap = new HashMap<String, Entity>(); String defEnvironment = prop.getProperty("default.environment"); String defaultAssetType = prop.getProperty("broker.service.asset-type"); String resourceEnvironmentXPath = prop.getProperty("broker.custom-data.environment"); String resourceNameXPath = prop.getProperty("broker.custom-data.endpoint"); // String defServiceToAppRelation = prop.getProperty("broker.service-to-app-relation"); String harvestType = prop.getProperty("broker.harvest-type"); String transportProtocolXpath = prop.getProperty("broker.custom-data.transport-protocol"); String authenticationMethod = prop.getProperty("broker.custom-data.authentication"); String authorizationMethod = prop.getProperty("broker.custom-data.authentication"); boolean hasHeader = Boolean.valueOf(prop.getProperty("broker.has-header")); try {/*from w w w. j av a 2 s . c o m*/ FileInputStream file = new FileInputStream(new File(templateFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (hasHeader) { hasHeader = false; } else { Iterator<Cell> cellIterator = row.cellIterator(); String name = cellIterator.next().getStringCellValue(); String description = cellIterator.next().getStringCellValue(); String originProtocol = cellIterator.next().getStringCellValue(); String resourceName = cellIterator.next().getStringCellValue(); // String location = cellIterator.next().getStringCellValue(); // String originSystem = cellIterator.next().getStringCellValue(); // String targetProtocol = cellIterator.next().getStringCellValue(); // String targetSystem = cellIterator.next().getStringCellValue(); String version = getVersion(name); logger.info("Asset: " + name + " - " + version); UnicomerEntity entity = new UnicomerEntity(defaultAssetType, name, name, description, version, ArtifactAlgorithm.DEFAULT); entity.addCategorization("LineOfBusiness", prop.getProperty("broker.line-of-business")); entity.addCategorization("AssetLifecycleStage", prop.getProperty("default.asset-lifecycle-stage")); entity.addCategorization("Technology", prop.getProperty("broker.technology")); entity.addCategorization("Region", prop.getProperty("default.region")); entity.addHarvesterProperty("Modulo", harvestType); entity.addHarvesterProperty("Harvester Description", prop.getProperty("default.harvester-description")); entity.addCustomData("acquisition-method", prop.getProperty("default.acquisition-method")); entity.addCustomData(resourceEnvironmentXPath, defEnvironment); entity.addCustomData(resourceNameXPath, resourceName); entity.addCustomData(transportProtocolXpath, originProtocol); entity.addCustomData("authentication-method", authenticationMethod); entity.addCustomData("authorization-method", authorizationMethod); entity.addCustomData("has-test-scripts", "true"); entity.addCustomData("needs-performance-testing", "false"); entity.addCustomData("has-automated-testing", "false"); entity.addCustomData("consistent-with-business-mission", "true"); entity.addCustomData("passes-legal-review", "true"); entity.addCustomData("approved-for-internal-use", "true"); entity.addCustomData("approved-for-external-use", "false"); entity.addCustomData("passes-technical-review", "true"); entity.addCustomData("downtime-impact", prop.getProperty("broker.downtime-impact")); entity.addCustomData("license-terms", prop.getProperty("broker.license-terms")); servicesMap.put(name, entity); } } workbook.close(); file.close(); list.add(new HashSet<Entity>(servicesMap.values())); for (Set<Entity> entitySet : list) { if (entitySet != null && entitySet.size() > 0) { YamlWriter.writeToYaml(entitySet, harvestType); } } } catch (Exception e) { e.printStackTrace(); } return list; }
From source file:com.yanglb.utilitys.codegen.core.reader.BaseReader.java
License:Apache License
/** * ?//from www. j a va 2s.c om * @throws CodeGenException */ private void doReader() throws CodeGenException { // XSSFWorkbook wb = null; try { // jar??new File if (this.excelFile.startsWith("jar:")) { String path = this.excelFile.substring(4); InputStream is = this.getClass().getResourceAsStream(path); wb = new XSSFWorkbook(is); } else { File file = new File(this.excelFile); BufferedInputStream in = new BufferedInputStream(new FileInputStream(file)); wb = new XSSFWorkbook(in); } // ? HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); // ?? if (this.sheets == null || this.sheets.length == 0) { // ? for (int i = 0; i < wb.getNumberOfSheets(); i++) { XSSFSheet sheet = wb.getSheetAt(i); // ???Sheet if (!this.isReadable(sheet.getSheetName())) { continue; } this.results.add(this.onReader(sheet)); } } else { // ?Sheet for (String sheetName : this.sheets) { XSSFSheet sheet = wb.getSheet(sheetName); if (sheet == null) { throw new CodeGenException(String.format(MsgUtility.getString("E_004"), sheetName)); } this.results.add(this.onReader(sheet)); } } } catch (FileNotFoundException e) { // ??? throw new CodeGenException(e.getMessage()); } catch (UnImplementException e) { this.results.clear(); e.printStackTrace(); } catch (IOException e) { throw new CodeGenException(MsgUtility.getString("E_005")); } finally { try { if (wb != null) wb.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:controller.DAORequest.java
public void saveResolution() { XSSFWorkbook reqWB = new XSSFWorkbook(); XSSFSheet sheet = reqWB.createSheet(); Row rowZ = sheet.createRow(0);/*from w w w . j a v a2s.co m*/ sheet.getRow(0).createCell(0).setCellValue("Id"); sheet.getRow(0).createCell(1).setCellValue("Attention"); sheet.getRow(0).createCell(2).setCellValue("Title"); sheet.getRow(0).createCell(3).setCellValue("Intro"); sheet.getRow(0).createCell(4).setCellValue("Result"); sheet.getRow(0).createCell(5).setCellValue("Resolve"); sheet.getRow(0).createCell(6).setCellValue("Notify"); sheet.getRow(0).createCell(7).setCellValue("Considerations"); ArrayList<Resolution> resolutions = new ArrayList<>(); for (Object o : School.getInstance().selectAllRequests()) { Request r = (Request) o; if (r.getResolution() != null) { resolutions.add(r.getResolution()); } } int rowI = 1; for (Resolution r : resolutions) { Row row = sheet.createRow(rowI); // Cell cellId = row.createCell(0); cellId.setCellValue(r.getId()); Cell cellAttention = row.createCell(1); cellAttention.setCellValue(r.getAttention()); Cell cellTitle = row.createCell(2); cellTitle.setCellValue(r.getTitle()); Cell cellIntro = row.createCell(3); cellIntro.setCellValue(r.getIntro()); Cell cellResult = row.createCell(4); cellResult.setCellValue(r.getResult()); Cell cellResolve = row.createCell(5); cellResolve.setCellValue(r.getResolve()); Cell cellNotify = row.createCell(6); cellNotify.setCellValue(r.getNotify()); Cell cellCons = row.createCell(7); cellCons.setCellValue(r.getConsider()); rowI++; } // Save to excel file try { FileOutputStream out = new FileOutputStream(new File("src//files//DatosResolucion.xlsx")); reqWB.write(out); reqWB.close(); out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultTasks() { String path = "/Creator/textFiles/tasks.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedTasks = new ArrayList<>(); try {//from w w w . ja v a 2 s . co m XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: System.out.println("default @ [" + i + "][" + j + "]"); rowData[j] = "default @ [" + i + "][" + j + "]"; break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } rowData[5] = "'" + rowData[5] + "'"; importedTasks.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultAlerts() { String path = "/Creator/textFiles/alerts.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedAlerts = new ArrayList<>(); try {/*from w w w . j a v a 2s . c o m*/ XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: //System.out.println("default @ [" + i + "][" + j + "] = " + String.valueOf(cell.getRawValue())); rowData[j] = String.valueOf(cell.getRawValue()); break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } importedAlerts.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:dbchubreast_web.service.util.FileService.java
License:Open Source License
/** ====================================================================================== */ public void writeExcelFile(OutputStream outputStream, List<String> header, List<Object> listData) { // === Blank workbook === XSSFWorkbook workbook = new XSSFWorkbook(); // === Create a blank sheet === XSSFSheet sheet = workbook.createSheet("EpiMed data " + dateFormat.format(new Date())); // === Nb of rows and cells === int rownum = 0; // === Header === if (header != null && !header.isEmpty()) { Row row = sheet.createRow(rownum++); int cellnum = 0; for (int i = 0; i < header.size(); i++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(header.get(i)); }/*from ww w. j a va2s .c o m*/ } // === Data === if (listData != null) { for (Iterator<Object> iterator = listData.iterator(); iterator.hasNext();) { Object data[] = (Object[]) iterator.next(); logger.trace(rownum + " " + Arrays.toString(data)); Row row = sheet.createRow(rownum++); int cellnum = 0; for (int j = 0; j < data.length; j++) { Cell cell = row.createCell(cellnum++); cell.setCellType(CellType.STRING); boolean isNull = (data[j] == null); if (!isNull) { cell.setCellValue(data[j].toString()); } } } } try { workbook.write(outputStream); workbook.close(); outputStream.flush(); outputStream.close(); } catch (IOException e) { logger.debug("XLS error"); e.printStackTrace(); } }
From source file:dbchubreast_web.service.util.FileService.java
License:Open Source License
/** ================================================================================= */ public void writeWorkbook(OutputStream outputStream, XSSFWorkbook workbook) { try {//from www .j a va 2s . c o m workbook.write(outputStream); workbook.close(); outputStream.flush(); outputStream.close(); } catch (IOException e) { logger.debug("XLS error"); e.printStackTrace(); } }
From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java
License:Open Source License
@Test public void generateDataDictionary() throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); createEntitySheet(workbook, PersonDto.class, PersonDto.I18N_PREFIX); createEntitySheet(workbook, LocationDto.class, LocationDto.I18N_PREFIX); createEntitySheet(workbook, CaseDataDto.class, CaseDataDto.I18N_PREFIX); createEntitySheet(workbook, HospitalizationDto.class, HospitalizationDto.I18N_PREFIX); createEntitySheet(workbook, SymptomsDto.class, SymptomsDto.I18N_PREFIX); createEntitySheet(workbook, EpiDataDto.class, EpiDataDto.I18N_PREFIX); createEntitySheet(workbook, HealthConditionsDto.class, HealthConditionsDto.I18N_PREFIX); createEntitySheet(workbook, PrescriptionDto.class, PrescriptionDto.I18N_PREFIX); createEntitySheet(workbook, TreatmentDto.class, TreatmentDto.I18N_PREFIX); createEntitySheet(workbook, ClinicalVisitDto.class, ClinicalVisitDto.I18N_PREFIX); createEntitySheet(workbook, ContactDto.class, ContactDto.I18N_PREFIX); createEntitySheet(workbook, VisitDto.class, VisitDto.I18N_PREFIX); createEntitySheet(workbook, SampleDto.class, SampleDto.I18N_PREFIX); createEntitySheet(workbook, PathogenTestDto.class, PathogenTestDto.I18N_PREFIX); createEntitySheet(workbook, AdditionalTestDto.class, AdditionalTestDto.I18N_PREFIX); createEntitySheet(workbook, TaskDto.class, TaskDto.I18N_PREFIX); createEntitySheet(workbook, EventDto.class, EventDto.I18N_PREFIX); createEntitySheet(workbook, EventParticipantDto.class, EventParticipantDto.I18N_PREFIX); createEntitySheet(workbook, FacilityDto.class, FacilityDto.I18N_PREFIX); createEntitySheet(workbook, RegionDto.class, RegionDto.I18N_PREFIX); createEntitySheet(workbook, DistrictDto.class, DistrictDto.I18N_PREFIX); createEntitySheet(workbook, CommunityDto.class, CommunityDto.I18N_PREFIX); createEntitySheet(workbook, UserDto.class, UserDto.I18N_PREFIX); XssfHelper.addAboutSheet(workbook);/* w w w.j a v a2s . c o m*/ String filePath = "src/main/resources/doc/SORMAS_Data_Dictionary.xlsx"; try (OutputStream fileOut = new FileOutputStream(filePath)) { workbook.write(fileOut); } workbook.close(); }
From source file:de.symeda.sormas.api.doc.UserRightsGenerator.java
License:Open Source License
@Test public void generateUserRights() throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); // Create User Rights sheet String safeName = WorkbookUtil.createSafeSheetName("User Rights"); XSSFSheet sheet = workbook.createSheet(safeName); // Initialize cell styles // Authorized style XSSFCellStyle authorizedStyle = workbook.createCellStyle(); authorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); authorizedStyle.setFillForegroundColor(new XSSFColor(new Color(0, 153, 0))); authorizedStyle.setBorderBottom(BorderStyle.THIN); authorizedStyle.setBorderLeft(BorderStyle.THIN); authorizedStyle.setBorderTop(BorderStyle.THIN); authorizedStyle.setBorderRight(BorderStyle.THIN); authorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Unauthorized style XSSFCellStyle unauthorizedStyle = workbook.createCellStyle(); unauthorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); unauthorizedStyle.setFillForegroundColor(new XSSFColor(Color.RED)); unauthorizedStyle.setBorderBottom(BorderStyle.THIN); unauthorizedStyle.setBorderLeft(BorderStyle.THIN); unauthorizedStyle.setBorderTop(BorderStyle.THIN); unauthorizedStyle.setBorderRight(BorderStyle.THIN); unauthorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Bold style XSSFFont boldFont = workbook.createFont(); boldFont.setBold(true);/* w w w.java 2s. com*/ XSSFCellStyle boldStyle = workbook.createCellStyle(); boldStyle.setFont(boldFont); int rowCounter = 0; // Header Row headerRow = sheet.createRow(rowCounter++); Cell userRightHeadlineCell = headerRow.createCell(0); userRightHeadlineCell.setCellValue("User Right"); userRightHeadlineCell.setCellStyle(boldStyle); Cell descHeadlineCell = headerRow.createCell(1); descHeadlineCell.setCellValue("Description"); descHeadlineCell.setCellStyle(boldStyle); sheet.setColumnWidth(0, 256 * 35); sheet.setColumnWidth(1, 256 * 50); for (UserRole userRole : UserRole.values()) { String columnCaption = userRole.toString(); Cell headerCell = headerRow.createCell(userRole.ordinal() + 2); headerCell.setCellValue(columnCaption); headerCell.setCellStyle(boldStyle); sheet.setColumnWidth(userRole.ordinal() + 2, 256 * 14); } // User right rows for (UserRight userRight : UserRight.values()) { Row row = sheet.createRow(rowCounter++); // User right name Cell nameCell = row.createCell(0); nameCell.setCellValue(userRight.name()); nameCell.setCellStyle(boldStyle); // User right description Cell descCell = row.createCell(1); descCell.setCellValue(userRight.toString()); // Add styled cells for all user roles for (UserRole userRole : UserRole.values()) { Cell roleRightCell = row.createCell(userRole.ordinal() + 2); if (userRole.hasDefaultRight(userRight)) { roleRightCell.setCellStyle(authorizedStyle); roleRightCell.setCellValue("Yes"); } else { roleRightCell.setCellStyle(unauthorizedStyle); roleRightCell.setCellValue("No"); } } } XssfHelper.addAboutSheet(workbook); String filePath = "src/main/resources/doc/SORMAS_User_Rights.xlsx"; try (OutputStream fileOut = new FileOutputStream(filePath)) { workbook.write(fileOut); } workbook.close(); // Desktop.getDesktop().open(new File(filePath)); }