List of usage examples for org.apache.poi.ss.usermodel Workbook close
@Override void close() throws IOException;
From source file:Opm_Package.OpenFileName.java
public List<String> readCommits(String file) throws Exception { // String excelFilePath = "Books.xlsx"; //FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); List<String> lists = new ArrayList<>(); Workbook workbook = readFileName(file); Sheet firstSheet = workbook.getSheetAt(1); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next();//from ww w . j a v a2 s . c o m Iterator<Cell> cellIterator = nextRow.cellIterator(); int y = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (y > 7 & !cell.getStringCellValue().equals("-")) { lists.add(cell.getStringCellValue()); } //System.out.print(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); break; } // System.out.print(" - "); y++; } //System.out.println(); } workbook.close(); // inputStream.close(); for (int x = 0; x < lists.size(); x++) { String[] splits = lists.get(x).split(":-"); //System.out.println(lists.get(x)+"\t length = "+splits.length); } return lists; }
From source file:Opm_Package.OpenFileName.java
public List<List<String>> readCommits2(String file, int count) throws Exception { // String excelFilePath = "Books.xlsx"; //FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); List<String> lists = new ArrayList<>(); List<String> datelists = new ArrayList<>(); List<List<String>> pullslists = new ArrayList<>(); List<List<String>> alllists = new ArrayList<>(); List<String> prOpen = new ArrayList<>(); List<String> prClosed = new ArrayList<>(); List<String> isOpen = new ArrayList<>(); List<String> isClosed = new ArrayList<>(); List<String> forks = new ArrayList<>(); List<String> watch = new ArrayList<>(); Workbook workbook = readFileName(file); Sheet firstSheet = workbook.getSheetAt(count); Iterator<Row> iterator = firstSheet.iterator(); int p = 0;/*from www . j a v a2 s . co m*/ while (iterator.hasNext()) { p++; Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); int y = 0, d = 0; List<String> cList = new ArrayList<>(); List<String> plist = new ArrayList<>(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (y == 0 && p > 1) { datelists.add(cell.getStringCellValue()); } if (y > 7 && p > 1) { d = 1; lists.add(cell.getStringCellValue()); } if (y > 8 && p > 1) { datelists.add(datelists.get(datelists.size() - 1)); } if (cell.getStringCellValue().equals(null)) { System.out.print(y + "\t null"); } if (y == 1 && p > 1) { prOpen.add(cell.getStringCellValue()); } if (y == 2 && p > 1) { prClosed.add(cell.getStringCellValue()); } if (y == 3 && p > 1) { isOpen.add(cell.getStringCellValue()); } if (y == 4 && p > 1) { isClosed.add(cell.getStringCellValue()); } if (y == 5 && p > 1) { forks.add(cell.getStringCellValue()); } if (y == 6 && p > 1) { watch.add(cell.getStringCellValue()); } cList.add(cell.getStringCellValue()); System.out.print(y + "\t " + cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: // System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: // System.out.print(y+"\t NoData "); break; } ///System.out.print(" - "); y++; } pullslists.add(plist); if (cList.size() == 8) { lists.add("-"); } //System.out.print("\t\t "+pullslists+"\t\t"+1); System.out.println(); } workbook.close(); // inputStream.close(); for (int x = 0; x < lists.size(); x++) { String[] splits = lists.get(x).split(":-"); //System.out.println(lists.get(x)+"\t length = "+splits.length); } /// Add all the Lists to the new List alllists.add(lists); alllists.add(datelists); alllists.add(prOpen); alllists.add(prClosed); alllists.add(isOpen); alllists.add(isClosed); alllists.add(forks); alllists.add(watch); /// Return the lists to the Merger_Class ... return alllists; }
From source file:org.apache.ranger.biz.ServiceDBStore.java
License:Apache License
private void writeExcel(List<RangerPolicy> policies, String excelFileName, HttpServletResponse response) throws IOException { Workbook workbook = null; OutputStream outStream = null; try {//from ww w . j ava 2 s. c om workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); createHeaderRow(sheet); int rowCount = 0; if (!CollectionUtils.isEmpty(policies)) { for (RangerPolicy policy : policies) { long serviceType = daoMgr.getXXService().findByName(policy.getService()).getType(); List<RangerPolicyItem> policyItems = policy.getPolicyItems(); List<RangerRowFilterPolicyItem> rowFilterPolicyItems = policy.getRowFilterPolicyItems(); List<RangerDataMaskPolicyItem> dataMaskPolicyItems = policy.getDataMaskPolicyItems(); if (CollectionUtils.isNotEmpty(policyItems)) { for (RangerPolicyItem policyItem : policyItems) { Row row = sheet.createRow(++rowCount); writeBookForPolicyItems(policy, policyItem, null, null, row); } } else if (CollectionUtils.isNotEmpty(dataMaskPolicyItems)) { for (RangerDataMaskPolicyItem dataMaskPolicyItem : dataMaskPolicyItems) { Row row = sheet.createRow(++rowCount); writeBookForPolicyItems(policy, null, dataMaskPolicyItem, null, row); } } else if (CollectionUtils.isNotEmpty(rowFilterPolicyItems)) { for (RangerRowFilterPolicyItem rowFilterPolicyItem : rowFilterPolicyItems) { Row row = sheet.createRow(++rowCount); writeBookForPolicyItems(policy, null, null, rowFilterPolicyItem, row); } } else if (serviceType == 100) { Row row = sheet.createRow(++rowCount); writeBookForTag(policy, row); } } } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); workbook.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); response.setHeader("Content-Disposition", "attachment; filename=" + excelFileName); outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (IOException ex) { LOG.error("Failed to create report file " + excelFileName, ex); } catch (Exception ex) { LOG.error("Error while generating report file " + excelFileName, ex); } finally { if (outStream != null) { outStream.close(); } if (workbook != null) { workbook.close(); } } }
From source file:org.bbreak.excella.reports.processor.ReportsParserInfoTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.processor.ReportsParserInfo#getMatchTagParser(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell)} ???? *//* w w w .j av a 2s .c o m*/ @Test public void testGetMatchTagParser() { ReportsParserInfo info = new ReportsParserInfo(); ReportBook reportBook = new ReportBook("", "", new ConvertConfiguration[] {}); info.setReportBook(reportBook); List<ReportsTagParser<?>> reportParsers = new ArrayList<ReportsTagParser<?>>( ReportCreateHelper.createDefaultParsers().values()); info.setReportParsers(reportParsers); Workbook hssfWb = new HSSFWorkbook(); Workbook xssfWb = new XSSFWorkbook(); Sheet hssfSheet = hssfWb.createSheet("new sheet"); Sheet xssfSheet = xssfWb.createSheet("new sheet"); Cell hssfCell0 = hssfSheet.createRow(0).createCell(0); Cell hssfCell1 = hssfSheet.createRow(1).createCell(0); hssfCell0.setCellValue("${HSSF}"); hssfCell1.setCellValue("$TEST{HSSF}"); Cell xssfCell0 = xssfSheet.createRow(0).createCell(0); Cell xssfCell1 = xssfSheet.createRow(1).createCell(0); xssfCell0.setCellValue("${XSSF}"); xssfCell1.setCellValue("$TEST{XSSF}"); TagParser<?> parser = null; try { parser = info.getMatchTagParser(hssfSheet, hssfCell0); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } assertEquals(SingleParamParser.class, parser.getClass()); try { parser = info.getMatchTagParser(hssfSheet, hssfCell1); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } assertNull(parser); try { parser = info.getMatchTagParser(xssfSheet, xssfCell0); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } assertEquals(SingleParamParser.class, parser.getClass()); try { parser = info.getMatchTagParser(xssfSheet, xssfCell1); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } assertNull(parser); try { hssfWb.close(); } catch (IOException e) { } try { xssfWb.close(); } catch (IOException e) { } }
From source file:org.bbreak.excella.reports.util.ReportsUtilTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.util.ReportsUtil#getMergedAddress(Sheet, int, int)} ???? *///from w ww . java2 s . c o m @Test public void testgetMergedAddress() { // ? Workbook hssfWb = new HSSFWorkbook(); // ? Sheet hssfSheet = hssfWb.createSheet("testsheet"); // ? Row hssfRow = hssfSheet.createRow(0); // ? hssfRow.createCell(0); hssfRow.createCell(1); hssfRow.createCell(2); // ?? CellRangeAddress address1 = new CellRangeAddress(0, 1, 1, 1); hssfSheet.addMergedRegion(address1); CellRangeAddress address2 = new CellRangeAddress(0, 0, 2, 3); hssfSheet.addMergedRegion(address2); // assertNull(ReportsUtil.getMergedAddress(hssfSheet, 0, 0)); assertEquals(address1.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 1).toString()); assertEquals(address2.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 2).toString()); try { hssfWb.close(); } catch (IOException e) { } // ? Workbook xssfWb = new XSSFWorkbook(); // ? Sheet xssfSheet = xssfWb.createSheet("testsheet"); // ? Row xssfRow = xssfSheet.createRow(0); // ? xssfRow.createCell(0); xssfRow.createCell(1); xssfRow.createCell(2); // ?? address1 = new CellRangeAddress(0, 1, 1, 1); xssfSheet.addMergedRegion(address1); address2 = new CellRangeAddress(0, 0, 2, 3); xssfSheet.addMergedRegion(address2); // assertNull(ReportsUtil.getMergedAddress(xssfSheet, 0, 0)); assertEquals(address1.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 1).toString()); assertEquals(address2.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 2).toString()); try { xssfWb.close(); } catch (IOException e) { } }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private Answer exportToXLS() { Answer ans = new Answer(); //Blank workbook Workbook workbook = new XSSFWorkbook(); if (data.equals("TestCaseWithExecution")) { createReportByTagExport(workbook); }//from ww w . j a va 2 s . co m FileOutputStream outputStream; try { String timeStamp = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss").format(new java.util.Date()); outputStream = new FileOutputStream(this.fileName + "_" + timeStamp + type.getFileExtension()); try { workbook.write(outputStream); outputStream.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(ExportServiceFactory.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(ExportServiceFactory.class.getName()).log(Level.SEVERE, null, ex); } //each country will be a page in the xls file return ans; }
From source file:org.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java
License:Apache License
/** * open document, create document graph and timeline in salt, print logging * infos, throw warning, if there are any problems while handling the * document/*w ww .j ava 2s . c o m*/ * * @param resource * string of the document path */ private void readSpreadsheetResource(String resource) { getDocument().setDocumentGraph(SaltFactory.createSDocumentGraph()); STimeline timeline = SaltFactory.createSTimeline(); getDocument().getDocumentGraph().setTimeline(timeline); SpreadsheetImporter.logger.debug("Importing the file {}.", resource); SpreadsheetImporter.logger.info(resource); // get the excel files here InputStream excelFileStream; File excelFile; Workbook workbook = null; try { excelFile = new File(resource); excelFileStream = new FileInputStream(excelFile); workbook = WorkbookFactory.create(excelFileStream); workbook.close(); } catch (IOException | EncryptedDocumentException | InvalidFormatException e) { SpreadsheetImporter.logger.warn("Could not open file '" + resource + "'."); } getPrimTextTiers(workbook, timeline); }
From source file:org.dash.valid.ars.AntigenRecognitionSiteLoader.java
License:Open Source License
private static HashMap<String, List<String>> loadARSData() throws InvalidFormatException, IOException { Workbook workbook = null; workbook = WorkbookFactory//from ww w.ja v a2s . c o m .create(AntigenRecognitionSiteLoader.class.getClassLoader().getResourceAsStream(DEFAULT_ARS_FILE)); // Return first sheet from the XLSX workbook Sheet mySheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); String gCode; String alleleString; List<String> alleles; HashMap<String, List<String>> arsMap = new HashMap<String, List<String>>(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { alleles = new ArrayList<String>(); Row row = rowIterator.next(); gCode = row.getCell(0).getStringCellValue(); if (gCode.contains(GLStringConstants.ASTERISK)) { alleleString = row.getCell(1).getStringCellValue(); String[] parts = alleleString.split(GLStringConstants.COMMA); for (String part : parts) { alleles.add(GLStringConstants.HLA_DASH + part); } arsMap.put(GLStringConstants.HLA_DASH + gCode, alleles); } } workbook.close(); return arsMap; }
From source file:org.dash.valid.freq.HLAFrequenciesLoader.java
License:Open Source License
private List<DisequilibriumElement> loadNMDPLinkageReferenceData(String filename, Locus[] locusPositions) throws IOException, InvalidFormatException { List<DisequilibriumElement> disequilibriumElements = new ArrayList<DisequilibriumElement>(); // Finds the workbook instance for XLSX file InputStream inStream = HLAFrequenciesLoader.class.getClassLoader().getResourceAsStream(filename); if (inStream == null) { throw new FileNotFoundException(); }//from ww w . j a v a 2 s .c om Workbook workbook = WorkbookFactory.create(inStream); // Return first sheet from the XLSX workbook Sheet mySheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int firstRow = mySheet.getFirstRowNum(); List<String> raceHeaders = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == firstRow) { raceHeaders = readHeaderElementsByRace(row); } else { disequilibriumElements.add(readDiseqilibriumElementsByRace(row, raceHeaders, locusPositions)); } } workbook.close(); return disequilibriumElements; }
From source file:org.dash.valid.freq.HLAFrequenciesLoader.java
License:Open Source License
private void loadIndividualLocusFrequency(Frequencies freq, Locus locus) throws IOException, InvalidFormatException { List<String> singleLocusFrequencies = new ArrayList<String>(); String extension = freq.equals(Frequencies.NMDP) ? ".xlsx" : ".xls"; InputStream inputStream = HLAFrequenciesLoader.class.getClassLoader().getResourceAsStream( "frequencies/" + freq.getShortName() + "/" + locus.getFrequencyName() + extension); if (inputStream == null) return;//from w ww . ja va 2s .co m Workbook workbook = WorkbookFactory.create(inputStream); // Return first sheet from the XLSX workbook Sheet mySheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int firstRow = mySheet.getFirstRowNum(); String cellValue = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == firstRow) { continue; } else { cellValue = row.getCell(0).getStringCellValue(); if (!cellValue.contains(GLStringConstants.ASTERISK)) { cellValue = locus.getShortName() + GLStringConstants.ASTERISK + cellValue.substring(0, 2) + GLStringUtilities.COLON + cellValue.substring(2); } singleLocusFrequencies.add(GLStringConstants.HLA_DASH + cellValue); } } individualLocusFrequencies.put(locus, singleLocusFrequencies); workbook.close(); }