List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:org.openconcerto.erp.importer.DataImporter.java
License:Open Source License
public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException { final InputStream inputStream = new FileInputStream(xlsFile); final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream)); final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); final HSSFSheet sheet = workBook.getSheetAt(sheetNumber); Iterator<Row> rowsIterator = sheet.rowIterator(); int columnCount = 0; int rowCount = 0; while (rowsIterator.hasNext()) { Row row = rowsIterator.next();/*from w ww . ja v a2 s. co m*/ int i = row.getPhysicalNumberOfCells(); if (i > columnCount) { columnCount = i; } rowCount++; } // Extract data rowsIterator = sheet.rowIterator(); int start = 0; if (skipFirstLine) { start = 1; rowsIterator.next(); } final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start); FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator(); while (rowsIterator.hasNext()) { final Row row = rowsIterator.next(); final List<Object> rowData = new ArrayList<Object>(); for (int i = 0; i < columnCount; i++) { final Cell cell = row.getCell(i); if (cell == null) { rowData.add(""); } else { CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { rowData.add(""); } else { switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: rowData.add(Boolean.valueOf(cellValue.getBooleanValue())); break; case Cell.CELL_TYPE_NUMERIC: rowData.add(Double.valueOf(cellValue.getNumberValue())); break; case Cell.CELL_TYPE_STRING: rowData.add(cellValue.getStringValue()); break; case Cell.CELL_TYPE_FORMULA: rowData.add(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: rowData.add(""); break; default: rowData.add(cellValue.getStringValue()); break; } } } } rows.add(rowData); } inputStream.close(); return new ArrayTableModel(rows); }
From source file:org.opencrx.application.uses.com.auxilii.msgparser.MsgParser.java
License:Open Source License
/** * Parses a .msg file provided by an input stream. * /*from w w w .jav a 2 s . co m*/ * @param msgFileStream The .msg file as a InputStream. * @param closeStream Indicates whether the provided stream should * be closed after the message has been read. * @return A {@link Message} object representing the .msg file. * @throws IOException Thrown if the file could not be loaded or parsed. * @throws UnsupportedOperationException Thrown if the .msg file cannot * be parsed correctly. */ public Message parseMsg(InputStream msgFileStream, boolean closeStream) throws IOException, UnsupportedOperationException { // the .msg file, like a file system, contains directories // and documents within this directories // we now gain access to the root node // and recursively go through the complete 'filesystem'. Message msg = null; try { POIFSFileSystem fs = new POIFSFileSystem(msgFileStream); DirectoryEntry dir = fs.getRoot(); msg = new Message(); this.checkDirectoryEntry(dir, msg); } finally { if (closeStream) { try { msgFileStream.close(); } catch (Exception e) { // ignore } } } return msg; }
From source file:org.opencrx.kernel.text.ExcelToText.java
License:BSD License
/** * Extract text from XLS./*from www . j a va2 s. co m*/ * * @param document * @return * @throws ServiceException */ public Reader parse(InputStream document) throws ServiceException { try { this.text.setLength(0); POIFSFileSystem fs = new POIFSFileSystem(document); InputStream workbook = fs.createDocumentInputStream("Workbook"); HSSFRequest request = new HSSFRequest(); request.addListenerForAllRecords(this); HSSFEventFactory eventFactory = new HSSFEventFactory(); try { eventFactory.processEvents(request, workbook); } catch (Exception e) { throw new ServiceException(e); } catch (NoSuchMethodError e) { throw new ServiceException(BasicException.toExceptionStack(e)); } workbook.close(); return new StringReader(this.text.toString()); } catch (IOException e) { throw new ServiceException(e); } }
From source file:org.openmrs.module.kenyametadatatools.mflsync.MflSyncFromRemoteSpreadsheetTask.java
License:Open Source License
/** * Imports a MFL spreadsheet from a stream * @param stream the input stream/*from www . j a v a 2s .c o m*/ * @throws IOException if an error occurred */ protected void importXls(InputStream stream) throws IOException { POIFSFileSystem poifs = new POIFSFileSystem(stream); HSSFWorkbook wbook = new HSSFWorkbook(poifs); HSSFSheet sheet = wbook.getSheetAt(0); for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); ++r) { HSSFRow row = sheet.getRow(r); String code = String.valueOf(((Double) cellValue(row.getCell(0))).intValue()); String name = (String) cellValue(row.getCell(1)); String province = (String) cellValue(row.getCell(2)); String type = (String) cellValue(row.getCell(6)); if (StringUtils.isEmpty(name)) { TaskEngine.logError("Unable to import location " + code + " with empty name"); } else if (StringUtils.isEmpty(code)) { TaskEngine.logError("Unable to import location '" + name + "' with invalid code"); } else { importLocation(code, name.trim(), province, type); } } }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * @return an Excel Workbook for the given argument *//*from ww w .jav a 2 s. c o m*/ protected Workbook getExcelTemplate(ReportDesign design) throws IOException { Workbook wb = null; InputStream is = null; try { ReportDesignResource r = getTemplate(design); is = new ByteArrayInputStream(r.getContents()); POIFSFileSystem fs = new POIFSFileSystem(is); wb = WorkbookFactory.create(fs); } catch (Exception e) { log.warn("No template file found, will use default Excel output"); } finally { IOUtils.closeQuietly(is); } return wb; }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
/** * Read uploaded exel file and make entity object. * @param excelFile uploaded excel file. * @param startRow first row number to map entity. * @param values additional infomations to map entity. * @param callback validate and mapping template. *///from w w w .ja va 2 s .com public static void processExcelFile(String excelFile, int startRow, Map<String, Object> values, ExcelUploadTemplate callback) { POIFSFileSystem fs = null; HSSFWorkbook wb = null; FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream(excelFile); fs = new POIFSFileSystem(fileInputStream); wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); if (sheet.getLastRowNum() < 1) throw new ExcelUploadException("Invalid Excel File - empty rows"); if (!callback.checkColumnHeader(sheet.getRow(0))) throw new ExcelUploadException("Invalid Excel File - Invalid Column Header."); for (int i = startRow; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); callback.makeEntity(row, values); } } catch (Exception e) { throw new ExcelUploadException(e); } finally { try { if (fileInputStream != null) { fileInputStream.close(); fileInputStream = null; } } catch (IOException e) { throw new ExcelUploadException(e); } } }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
public static HSSFWorkbook getHSSFWorkbook(String file) { POIFSFileSystem fs = null;//from www.java2s .co m HSSFWorkbook formwb = null; FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream(file); fs = new POIFSFileSystem(fileInputStream); formwb = new HSSFWorkbook(fs); } catch (Exception e) { throw new ExcelUploadException(e); } finally { try { if (fileInputStream != null) { fileInputStream.close(); fileInputStream = null; } } catch (IOException e) { throw new RuntimeException(e); } } return formwb; }
From source file:org.openstreetmap.josm.plugins.opendata.core.io.tabular.XlsReader.java
License:GNU General Public License
@Override protected void initResources(InputStream in, ProgressMonitor progressMonitor) throws IOException { Main.info("Parsing XLS file"); try {// w w w .j a v a 2 s.com wb = new HSSFWorkbook(new POIFSFileSystem(in)); sheet = wb.getSheetAt(getSheetNumber()); rowIndex = 0; } catch (ExceptionInInitializerError e) { Throwable ex = e.getException(); if (ex != null && ex.getMessage() != null) { Main.error(ex.getClass() + ": " + ex.getMessage()); } throw new IOException(e); } catch (Throwable t) { throw new IOException(t); } }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Reads a simply formatted, single sheet Excel document into a list of <code>Map</code>. * @param stream an <code>InputStream</code> of the excel document * @param columnNames a List containing the keys to use when mapping the columns into the Map (column 1 goes in the Map key columnNames 1, etc ...) * @param skipRows number of rows to skip, typically 1 to skip the header row * @return the List of Map representing the rows * @throws IOException if an error occurs *//*from w w w . j av a 2 s. c om*/ public static List<Map<String, String>> readExcelFile(InputStream stream, List<String> columnNames, int skipRows) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int sheetLastRowNumber = sheet.getLastRowNum(); List<Map<String, String>> rows = new ArrayList<Map<String, String>>(); for (int j = skipRows; j <= sheetLastRowNumber; j++) { HSSFRow erow = sheet.getRow(j); Map<String, String> row = new HashMap<String, String>(); for (int i = 0; i < columnNames.size(); i++) { String columnName = columnNames.get(i); HSSFCell cell = erow.getCell(i); String s = ""; if (cell != null) { // check if cell contains a number BigDecimal bd = null; try { double d = cell.getNumericCellValue(); bd = BigDecimal.valueOf(d); } catch (Exception e) { // do nothing } if (bd == null) { s = cell.toString().trim(); } else { // if cell contains number trim the tailing zeros so that for example postal code string // does not appear as a floating point number s = bd.toPlainString(); // convert XX.XX000 into XX.XX s = s.replaceFirst("^(-?\\d+\\.0*[^0]+)0*\\s*$", "$1"); // convert XX.000 into XX s = s.replaceFirst("^(-?\\d+)\\.0*$", "$1"); } } Debug.logInfo("readExcelFile cell (" + j + ", " + i + ") as (" + columnName + ") == " + s, MODULE); row.put(columnName, s); } rows.add(row); } return rows; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Uploads an Excel file in the correct directory. * @exception ServiceException if an error occurs */// w ww . j a va2 s . com public void parseFileForDataImport() throws ServiceException { // Get the uploaded file File file = getUploadedExcelFile(getUploadedFileName()); // set it up as an Excel workbook POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { // this will auto close the FileInputStream when the constructor completes fs = new POIFSFileSystem(new FileInputStream(file)); wb = new HSSFWorkbook(fs); } catch (IOException e) { throw new ServiceException("Unable to read or create workbook from file [" + getUploadedFileName() + "] " + e.getMessage()); } // loop through the tabs and import them one by one try { // a collection of all the records from all the excel spreadsheet tabs FastList<EntityInterface> entitiesToCreate = FastList.newInstance(); for (String excelTab : EXCEL_TABS) { HSSFSheet sheet = wb.getSheet(excelTab); if (sheet == null) { Debug.logWarning("Did not find a sheet named " + excelTab + " in " + file.getName() + ". Will not be importing anything.", MODULE); } else { if (EXCEL_PRODUCT_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportProducts(sheet)); } else if (EXCEL_SUPPLIERS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportSuppliers(sheet)); } else if (EXCEL_CUSTOMERS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportCustomers(sheet)); } else if (EXCEL_INVENTORY_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportInventory(sheet)); } else if (EXCEL_GL_ACCOUNTS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportGlAccounts(sheet)); } // etc ... } } // create and store values from all the sheets in the workbook in database using the PartyRepositoryInterface // note we're just using the most basic repository method, so any repository could do here PartyRepositoryInterface partyRepo = this.getDomainsDirectory().getPartyDomain().getPartyRepository(); partyRepo.createOrUpdate(entitiesToCreate); } catch (RepositoryException e) { throw new ServiceException(e); } // remove the uploaded file now if (!file.delete()) { Debug.logWarning("Could not delete the file : " + file.getName(), MODULE); } }