List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:org.apache.ofbiz.product.spreadsheetimport.ImportProductServices.java
License:Apache License
/** * This method is responsible to import spreadsheet data into "Product" and * "InventoryItem" entities into database. The method uses the * ImportProductHelper class to perform its operation. The method uses "Apache * POI" api for importing spreadsheet (xls files) data. * * Note : Create the spreadsheet directory in the ofbiz home folder and keep * your xls files in this folder only.// w ww. j a va 2 s . com * * @param dctx the dispatch context * @param context the context * @return the result of the service execution * @throws IOException */ public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx, Map<String, ? extends Object> context) throws IOException { Delegator delegator = dctx.getDelegator(); Locale locale = (Locale) context.get("locale"); // System.getProperty("user.dir") returns the path upto ofbiz home // directory String path = System.getProperty("user.dir") + "/spreadsheet"; List<File> fileItems = new LinkedList<File>(); if (UtilValidate.isNotEmpty(path)) { File importDir = new File(path); if (importDir.isDirectory() && importDir.canRead()) { File[] files = importDir.listFiles(); // loop for all the containing xls file in the spreadsheet // directory for (int i = 0; i < files.length; i++) { if (files[i].getName().toUpperCase().endsWith("XLS")) { fileItems.add(files[i]); } } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale)); } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale)); } if (fileItems.size() < 1) { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale) + path); } for (File item : fileItems) { // read all xls file and create workbook one by one. List<Map<String, Object>> products = new LinkedList<Map<String, Object>>(); List<Map<String, Object>> inventoryItems = new LinkedList<Map<String, Object>>(); POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(new FileInputStream(item)); wb = new HSSFWorkbook(fs); } catch (IOException e) { Debug.logError("Unable to read or create workbook from file", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotCreateWorkbookFromFile", locale)); } // get first sheet HSSFSheet sheet = wb.getSheetAt(0); wb.close(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (row != null) { // read productId from first column "sheet column index // starts from 0" HSSFCell cell2 = row.getCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); String productId = cell2.getRichStringCellValue().toString(); // read QOH from ninth column HSSFCell cell5 = row.getCell(5); BigDecimal quantityOnHand = BigDecimal.ZERO; if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) quantityOnHand = new BigDecimal(cell5.getNumericCellValue()); // check productId if null then skip creating inventory item // too. boolean productExists = ImportProductHelper.checkProductExists(productId, delegator); if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) { products.add(ImportProductHelper.prepareProduct(productId)); if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0) inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand, delegator.getNextSeqId("InventoryItem"))); else inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO, delegator.getNextSeqId("InventoryItem"))); } int rowNum = row.getRowNum() + 1; if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) { Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module); } } } // create and store values in "Product" and "InventoryItem" entity // in database for (int j = 0; j < products.size(); j++) { GenericValue productGV = delegator.makeValue("Product", products.get(j)); GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j)); if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) { try { delegator.create(productGV); delegator.create(inventoryItemGV); } catch (GenericEntityException e) { Debug.logError("Cannot store product", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotStoreProduct", locale)); } } } int uploadedProducts = products.size() + 1; if (products.size() > 0) Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module); } return ServiceUtil.returnSuccess(); }
From source file:org.apache.slide.extractor.MSExcelExtractor.java
License:Apache License
public Reader extract(InputStream content) throws ExtractorException { try {//from w w w .j av a2 s .c o m CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(content); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) writer.write(num + " "); break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) writer.write(text + " "); break; } } } } return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { throw new ExtractorException(e.getMessage()); } }
From source file:org.apache.tika.parser.microsoft.ooxml.AbstractOOXMLExtractor.java
License:Apache License
/** * Handles an embedded OLE object in the document *///from ww w. j av a 2 s . c o m private void handleEmbeddedOLE(PackagePart part, ContentHandler handler, String rel) throws IOException, SAXException { // A POIFSFileSystem needs to be at least 3 blocks big to be valid if (part.getSize() >= 0 && part.getSize() < 512 * 3) { // Too small, skip return; } // Open the POIFS (OLE2) structure and process POIFSFileSystem fs = new POIFSFileSystem(part.getInputStream()); try { Metadata metadata = new Metadata(); TikaInputStream stream = null; metadata.set(Metadata.EMBEDDED_RELATIONSHIP_ID, rel); DirectoryNode root = fs.getRoot(); POIFSDocumentType type = POIFSDocumentType.detectType(root); if (root.hasEntry("CONTENTS") && root.hasEntry("\u0001Ole") && root.hasEntry("\u0001CompObj") && root.hasEntry("\u0003ObjInfo")) { // TIKA-704: OLE 2.0 embedded non-Office document? stream = TikaInputStream.get(fs.createDocumentInputStream("CONTENTS")); if (embeddedExtractor.shouldParseEmbedded(metadata)) { embeddedExtractor.parseEmbedded(stream, new EmbeddedContentHandler(handler), metadata, false); } } else if (POIFSDocumentType.OLE10_NATIVE == type) { // TIKA-704: OLE 1.0 embedded document Ole10Native ole = Ole10Native.createFromEmbeddedOleObject(fs); if (ole.getLabel() != null) { metadata.set(Metadata.RESOURCE_NAME_KEY, ole.getLabel()); } byte[] data = ole.getDataBuffer(); if (data != null) { stream = TikaInputStream.get(data); } if (stream != null && embeddedExtractor.shouldParseEmbedded(metadata)) { embeddedExtractor.parseEmbedded(stream, new EmbeddedContentHandler(handler), metadata, false); } } else { handleEmbeddedFile(part, handler, rel); } } catch (FileNotFoundException e) { // There was no CONTENTS entry, so skip this part } catch (Ole10NativeException e) { // Could not process an OLE 1.0 entry, so skip this part } }
From source file:org.apache.tika.parser.wordperfect.QPWTextExtractor.java
License:Apache License
@SuppressWarnings("resource") public void extract(InputStream input, XHTMLContentHandler xhtml, Metadata metadata) throws IOException, SAXException, TikaException { POIFSFileSystem pfs = new POIFSFileSystem(input); DirectoryNode rootNode = pfs.getRoot(); if (rootNode == null || !rootNode.hasEntry(OLE_DOCUMENT_NAME)) { throw new UnsupportedFormatException("Unsupported QuattroPro file format. " + "Looking for OLE entry \"" + OLE_DOCUMENT_NAME + "\". Found: " + rootNode.getEntryNames()); }/*from ww w . j a va 2s .c om*/ //TODO shall we validate and throw warning/error if the file does not //start with a BOF and ends with a EOF? xhtml.startElement("p"); try (WPInputStream in = new WPInputStream(pfs.createDocumentInputStream(OLE_DOCUMENT_NAME))) { Context ctx = new Context(in, xhtml, metadata); while (hasNext(in)) { ctx.type = in.readWPShort(); ctx.bodyLength = in.readWPShort(); Extractor extractor = EXTRACTORS.get(ctx.type); if (extractor != null) { extractor.extract(ctx); } else { // Use DEBUG to find out what we are ignoring // Extractor.DEBUG.extract(ctx); Extractor.IGNORE.extract(ctx); } } } xhtml.endElement("p"); }
From source file:org.bbreak.excella.core.BookController.java
License:Open Source License
/** * <BR>/* w w w.j av a 2s . c om*/ * ????Workbook?? * * @param filepath * @throws IOException ??????? */ public BookController(String filepath) throws IOException { if (log.isInfoEnabled()) { log.info(filepath + "??????"); } if (filepath.endsWith(XSSF_SUFFIX)) { // XSSF? workbook = new XSSFWorkbook(filepath); } else { // HSSF? FileInputStream stream = new FileInputStream(filepath); POIFSFileSystem fs = new POIFSFileSystem(stream); workbook = new HSSFWorkbook(fs); stream.close(); } // ??? int numOfSheets = workbook.getNumberOfSheets(); for (int sheetCnt = 0; sheetCnt < numOfSheets; sheetCnt++) { String sheetName = workbook.getSheetName(sheetCnt); sheetNames.add(sheetName); } }
From source file:org.bbreak.excella.core.WorkbookTest.java
License:Open Source License
protected Workbook getWorkbook() { Workbook workbook = null;//ww w .j a v a2 s . c o m String filename = this.getClass().getSimpleName(); if (version.equals("2007")) { filename = filename + ".xlsx"; } else if (version.equals("2003")) { filename = filename + ".xls"; } URL url = this.getClass().getResource(filename); try { filepath = URLDecoder.decode(url.getFile(), "UTF-8"); if (filepath.endsWith(".xlsx")) { try { workbook = new XSSFWorkbook(filepath); } catch (IOException e) { Assert.fail(); } } else if (filepath.endsWith(".xls")) { FileInputStream stream = null; try { stream = new FileInputStream(filepath); } catch (FileNotFoundException e) { Assert.fail(); } POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(stream); } catch (IOException e) { Assert.fail(); } try { workbook = new HSSFWorkbook(fs); } catch (IOException e) { Assert.fail(); } try { stream.close(); } catch (IOException e) { Assert.fail(); } } } catch (UnsupportedEncodingException e) { Assert.fail(); } return workbook; }
From source file:org.bbreak.excella.reports.processor.ReportsWorkbookTest.java
License:Open Source License
protected Workbook getWorkbook(String filepath) { Workbook workbook = null;/* ww w . j a va 2 s. c o m*/ if (filepath.endsWith(".xlsx")) { try { workbook = new XSSFWorkbook(filepath); } catch (IOException e) { Assert.fail(); } } else if (filepath.endsWith(".xls")) { FileInputStream stream = null; try { stream = new FileInputStream(filepath); } catch (FileNotFoundException e) { Assert.fail(); } POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(stream); } catch (IOException e) { Assert.fail(); } try { workbook = new HSSFWorkbook(fs); } catch (IOException e) { Assert.fail(); } try { stream.close(); } catch (IOException e) { Assert.fail(); } } return workbook; }
From source file:org.bbreak.excella.trans.processor.TransProcessor.java
License:Open Source License
/** * ?????/*from w w w . ja v a2 s . co m*/ * * @param filePath Excel * @return workbook * @throws IOException ??????? */ private Workbook getWorkbook(String filePath) throws IOException { Workbook workbook = null; if (filePath.endsWith(BookController.XSSF_SUFFIX)) { // XSSF? workbook = new XSSFWorkbook(filePath); } else { // HSSF? FileInputStream stream = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(stream); workbook = new HSSFWorkbook(fs); stream.close(); } return workbook; }
From source file:org.cirdles.squid.tasks.squidTask25.TaskSquid25.java
License:Apache License
public static TaskSquid25 importSquidTaskFile(File squidTaskFile) { taskSquid25 = null;/*from w ww . j ava 2s . co m*/ try { InputStream inp = new FileInputStream(squidTaskFile); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new org.apache.poi.hssf.extractor.ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); String text = extractor.getText(); String[] lines = text.split("\n"); boolean isSquid2_20 = false; if (lines[0].startsWith("Created by SQUID")) { taskSquid25 = new TaskSquid25(); taskSquid25.constantNames = new ArrayList<>(); taskSquid25.constantValues = new ArrayList<>(); taskSquid25.squidVersion = lines[0].split("\t")[1]; // July 2018 detect if version 2.20 isSquid2_20 = (taskSquid25.squidVersion.startsWith("2.20")); int firstRow = Integer.parseInt(lines[1].split("\t")[1]) - 1; taskSquid25.squidTaskFileName = lines[firstRow].split("\t")[1]; taskSquid25.taskType = TaskTypeEnum .valueOf(lines[firstRow + 1].split("\t")[1].toUpperCase(Locale.ENGLISH)); taskSquid25.taskName = lines[firstRow + 2].split("\t")[1]; taskSquid25.taskDescription = lines[firstRow + 3].split("\t")[1]; taskSquid25.authorName = ""; taskSquid25.labName = lines[firstRow + 7].split("\t")[1]; String[] nominalMasses = lines[firstRow + 13].split("\t"); int countOfMasses = Integer.valueOf(nominalMasses[1]); taskSquid25.nominalMasses = new ArrayList<>(); for (int i = 0; i < countOfMasses; i++) { taskSquid25.nominalMasses.add(nominalMasses[i + 2]); } // July 2018 // decrement first row to handle missing line for Hidden Masses in Squid 2.20 if (isSquid2_20) { firstRow--; } String[] ratioStrings = lines[firstRow + 15].split("\t"); if (isSquid2_20 && ratioStrings[0].toUpperCase(Locale.ENGLISH).startsWith("HIDDEN")) { // special case of 2.2 where this row exists firstRow++; ratioStrings = lines[firstRow + 15].split("\t"); } int countOfRatios = Integer.valueOf(ratioStrings[1]); taskSquid25.ratioNames = new ArrayList<>(); for (int i = 0; i < countOfRatios; i++) { taskSquid25.ratioNames.add(ratioStrings[i + 2]); } String[] backgroundStrings = lines[firstRow + 19].split("\t"); taskSquid25.backgroundMass = backgroundStrings[1]; String[] parentNuclideStrings = lines[firstRow + 20].split("\t"); taskSquid25.parentNuclide = parentNuclideStrings[1]; String[] directAltPDStrings = lines[firstRow + 21].split("\t"); taskSquid25.directAltPD = Boolean.valueOf(directAltPDStrings[1]); taskSquid25.task25Equations = new ArrayList<>(); // determine where uranium or thorium is primary or secondary String primaryUThEqnName = UNCOR206PB238U_CALIB_CONST; String primaryUThEqnOtherName = UNCOR208PB232TH_CALIB_CONST; taskSquid25.specialSquidFourExpressionsMap = new TreeMap<>(); String[] primaryUThPbEqn = lines[firstRow + 22].split("\t"); if (primaryUThPbEqn.length > 1) { if (taskSquid25.parentNuclide.contains("232")) { primaryUThEqnName = UNCOR208PB232TH_CALIB_CONST; primaryUThEqnOtherName = UNCOR206PB238U_CALIB_CONST; } taskSquid25.specialSquidFourExpressionsMap.put(primaryUThEqnName, prepareSquid25ExcelEquationStringForSquid3(primaryUThPbEqn[1])); } String[] secondaryUThPbEqn = lines[firstRow + 23].split("\t"); if (secondaryUThPbEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(primaryUThEqnOtherName, prepareSquid25ExcelEquationStringForSquid3(secondaryUThPbEqn[1])); } String[] ThUEqn = lines[firstRow + 24].split("\t"); if (ThUEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(TH_U_EXP_DEFAULT, prepareSquid25ExcelEquationStringForSquid3(ThUEqn[1])); } String[] ppmParentEqn = lines[firstRow + 25].split("\t"); if (ppmParentEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(PARENT_ELEMENT_CONC_CONST, prepareSquid25ExcelEquationStringForSquid3(ppmParentEqn[1])); } String[] equations = lines[firstRow + 26].split("\t"); int countOfEquations = Integer.valueOf(equations[1]); String[] equationNames = lines[firstRow + 27].split("\t"); // June 2019 skip Hidden row 34 String[] switchST = lines[firstRow + 28].split("\t"); if (switchST[0].toUpperCase(Locale.ENGLISH).startsWith("HIDDEN")) { firstRow++; } // these switches split into a//n array of length equations mius 1 in Squid2.20 due to missing count entry switchST = lines[firstRow + 28].split("\t"); String[] switchSA = lines[firstRow + 29].split("\t"); String[] switchSC = lines[firstRow + 30].split("\t"); String[] switchNU = lines[firstRow + 32].split("\t"); // run constants first so can detect in equations String[] constantNamesSource = lines[firstRow + 40].split("\t"); String[] constantValuesSource = lines[firstRow + 41].split("\t"); int countOfConstants = 0; if (constantNamesSource.length > 1) { countOfConstants = Integer.valueOf(constantNamesSource[1]); } for (int i = 0; i < countOfConstants; i++) { taskSquid25.constantNames.add(constantNamesSource[i + 2].replaceFirst("_", "")); taskSquid25.constantValues.add(constantValuesSource[i + 2]); } for (int i = 0; i < countOfEquations; i++) { // handle backwards logic of Squid25 where both ST, SA false, means both True boolean switchRM = Boolean.parseBoolean(switchST[i + (isSquid2_20 ? 1 : 2)]); boolean switchUN = Boolean.parseBoolean(switchSA[i + (isSquid2_20 ? 1 : 2)]); if (!switchRM && !switchUN) { switchRM = true; switchUN = true; } String excelExpression = prepareSquid25ExcelEquationStringForSquid3(equations[i + 2]); if (excelExpression.length() > 0) { //detect if name contains "Age" or "abs" - undo change to % for errors if ((equationNames[i + 2].toUpperCase(Locale.ENGLISH).contains("ABS")) || (equationNames[i + 2].toUpperCase(Locale.ENGLISH).contains("AGE"))) { if (excelExpression.startsWith("[%\"")) { excelExpression = excelExpression.replaceFirst("\\[%\"", "\\[\""); } } taskSquid25.task25Equations.add(new TaskSquid25Equation(excelExpression, prepareSquid25ExcelEquationNameForSquid3(equationNames[i + 2]), switchRM, switchUN, Boolean.parseBoolean(switchSC[i + (isSquid2_20 ? 1 : 2)]), Boolean.parseBoolean(switchNU[i + (isSquid2_20 ? 1 : 2)]), false, false)); } } } } catch (IOException iOException) { } return taskSquid25; }
From source file:org.ddt.OfficeDDT.java
License:Apache License
/** * Processes the given file/directory./*from ww w . ja va 2s. c om*/ * <p/> * If the file is a directory, the files in it are processed and if the * recursive flag is true subdirectories are processed too. * * @todo some sort of file detection beforehand, don't think catching * exceptions is the ideal way of doing this... * * @todo tidy up exception handling, seems to throw exceptions that are * handled inside the method. * * @param f */ private void processFile(File f) throws IOException { if (f.isDirectory()) { for (File subFile : f.listFiles()) { if (subFile.isFile() || recursive) { processFile(subFile); } } } else { //check file types. If the -i option has been selected, just check //the file extension, otherwise attempt to read it as a POI doc. int filetype = this.getFileType(f); if (filetype == Constants.FILETYPE_ALL && ignoreNonOffice) { logger.log(Level.WARNING, "Unknown file type {0}, ignoring it.", f.getName()); return; } else { // this next bit is simply to check if we have a vaguely usable file // (i.e. some sort of office file) FileInputStream is = new FileInputStream(f); try { POIFSFileSystem pfs = new POIFSFileSystem(is); } catch (IOException e) { logger.log(Level.WARNING, "Failed to read {0}, skipping it", f.getName()); is.close(); return; } catch (OfficeXmlFileException e) { logger.log(Level.WARNING, "XML files not supported: skipping file {0}", f.getName()); is.close(); return; } is.close(); } count++; //process the actual file. //There's a problem here, if the last processor doesn't accept the //file type then the file in the output module can be null. //seems to be fixed, but need to look at it a bit closer. try { FileProcessor p = null; for (int i = 0; i < processors.size(); i++) { //IOExceptions need to be caught inside the loop, as they //can happen to one file processor, but not others, so it //still makes sense to keep processing with the rest of them. //they are not fatal for the file, just the file/processor //combination. try { p = processors.get(i); boolean accepted = p.acceptsFileType(filetype); if (accepted) { logger.log(Level.FINE, "processing file {0} with {1}", new Object[] { f.getName(), p.getClass().getCanonicalName() }); outputModule.addLinks(f, p.process(f)); } } catch (IOException e) { logger.log(Level.INFO, "Couldn''t process file {0} with processor {1}\n Error: {2}", new Object[] { f.getName(), p.toString(), e.getLocalizedMessage() }); continue; } } } catch (OfficeXmlFileException ex) //zip files/ooxml files { logger.log(Level.WARNING, "zip files and ooxml files not supported: {0}", f.getName()); return; } } }