List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java
License:Educational Community License
/** * method converts an input stream to an List consisting of strings * representing a line. The input stream must be for an xls file. * * @param inputStream//from ww w . j a va2 s. com * @return contents */ private List excelToArray(InputStream inputStreams) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(inputStreams); //Convert an Excel file to csv HSSFSheet sheet = wb.getSheetAt(0); List array = new ArrayList(); Iterator it = sheet.rowIterator(); while (it.hasNext()) { HSSFRow row = (HSSFRow) it.next(); String rowAsString = fromHSSFRowtoCSV(row); if (rowAsString.replaceAll(",", "").replaceAll("\"", "").equals("")) { continue; } array.add(fromHSSFRowtoCSV(row)); } return array; }
From source file:org.silverpeas.core.index.indexing.parser.excelParser.ExcelParser.java
License:Open Source License
/** * Read the text content of a pdf file and store it in out to be ready to be indexed. * @param out//w w w. java 2 s. c om * @param path * @param encoding * @throws IOException */ @Override public void outPutContent(Writer out, String path, String encoding) throws IOException { FileInputStream file = new FileInputStream(path); try { POIFSFileSystem fs = new POIFSFileSystem(file); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet sheet; for (int nbSheet = 0; nbSheet < workbook.getNumberOfSheets(); nbSheet++) { // extract sheet's name out.write(workbook.getSheetName(nbSheet)); sheet = workbook.getSheetAt(nbSheet); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = cells.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { out.write(cell.getStringCellValue()); out.write(' '); } } } } } catch (IOException ioe) { SilverTrace.error("indexing", "ExcelParser.outPutContent()", "indexing.MSG_IO_ERROR_WHILE_READING", path, ioe); } finally { IOUtils.closeQuietly(file); } }
From source file:org.silverpeas.search.indexEngine.parser.excelParser.ExcelParser.java
License:Open Source License
/** *Read the text content of a pdf file and store it in out to be ready to be indexed. * @param out// ww w . j a va 2 s. co m * @param path * @param encoding * @throws IOException */ @Override public void outPutContent(Writer out, String path, String encoding) throws IOException { FileInputStream file = new FileInputStream(path); try { POIFSFileSystem fs = new POIFSFileSystem(file); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet sheet = null; for (int nbSheet = 0; nbSheet < workbook.getNumberOfSheets(); nbSheet++) { // extract sheet's name out.write(workbook.getSheetName(nbSheet)); SilverTrace.debug("indexEngine", "ExcelParser.outputContent", "root.MSG_GEN_PARAM_VALUE", "sheetName = " + workbook.getSheetName(nbSheet)); sheet = workbook.getSheetAt(nbSheet); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = cells.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { out.write(cell.getStringCellValue()); out.write(' '); SilverTrace.debug("indexEngine", "ExcelParser.outputContent", "root.MSG_GEN_PARAM_VALUE", "cellValue = " + cell.getStringCellValue()); } } } } } catch (IOException ioe) { SilverTrace.error("indexEngine", "ExcelParser.outPutContent()", "indexEngine.MSG_IO_ERROR_WHILE_READING", path, ioe); } finally { IOUtils.closeQuietly(file); } }
From source file:org.terrier.indexing.MSExcelDocument.java
License:Mozilla Public License
/** Get the reader appropriate for this InputStream. This involves converting the Excel document to a stream of words. On failure returns null and sets EOD to true, so no terms can be read from the object. // ww w. j av a2 s . com Uses the property <tt>indexing.excel.maxfilesize.mb</tt> to determine if the file is too big to open @param docStream */ @SuppressWarnings("unchecked") //poi version used is for Java 1.4. protected Reader getReader(InputStream docStream) { if (MAXFILESIZE > 0 && (filename == null || new File(filename).length() > MAXFILESIZE)) { logger.warn("WARNING: Excel document " + filename + " is too large for POI. Ignoring."); EOD = true; return null; } try { CharArrayWriter writer = new CharArrayWriter(); //opening the file system POIFSFileSystem fs = new POIFSFileSystem(docStream); //opening the work book HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //got the i-th sheet from the work book 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) { logger.warn("WARNING: Problem converting excel document" + e); EOD = true; return null; } }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * Returns generated TestData object.//ww w . ja v a 2 s. c om * * @param file * excel file for generating TestData. * @return TestData * @throws ExcelFileImportException * catch oldFileexceptions */ @SuppressWarnings("rawtypes") @Override public TestData getTestData(File file) throws ExcelFileImportException { TestData testData = new TestData(); InputStream input = null; try { input = new BufferedInputStream(new FileInputStream(file)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); TestDataRow testDataRow = getTestDataRow(row); if (testDataRow.getList().size() > 0 && !dataRowIsEmpty(testDataRow)) { testData.addRow(testDataRow); } else { break; } } } catch (OldExcelFormatException e) { throw new ExcelFileImportException(e); } catch (FileNotFoundException e) { LOGGER.error("getTestData :: " + e.getMessage()); } catch (IOException e) { LOGGER.error("getTestData" + e.getMessage()); } finally { try { if (input != null) { input.close(); } } catch (IOException e) { LOGGER.error(e); } } return testData; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * /*from w ww .j a va 2s .c o m*/ * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final Reader getContent(final File f) { Reader reader = null; setType("EXCEL"); try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); 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; default: // skip } } } } setSummary(getSummaryFromContent(writer.toString())); return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents for: " + f.getName(), e); } return reader; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * //w w w . j a v a 2 s .c o m * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final String getContent(final InputStream is) { try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(is); 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; default: // skip } } } } return new String(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents", e); } return ""; }
From source file:poi.hssf.view.SVTableModel.java
License:Apache License
public SVTableModel(HSSFSheet st) { this.st = st; Iterator i = st.rowIterator(); while (i.hasNext()) { HSSFRow row = (HSSFRow) i.next(); if (maxcol < (row.getLastCellNum() + 1)) { this.maxcol = row.getLastCellNum(); }//from ww w. j ava 2 s . co m } }
From source file:POS.migrate.Excel_to_db_inventory_items.java
public static void main(String[] args) { System.setProperty("pool_db", "db_smis_dumaguete_refreshments"); String file = "C:\\Users\\Ronescape\\Documents\\Excel Files\\Ray Buenavista\\encode.xls"; if (file == null || file.isEmpty()) { return;// w w w . j av a2 s . c o m } FileInputStream fis = null; final List sheetData = new ArrayList(); try { fis = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { throw new RuntimeException(e); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Excel_to_db_inventory_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<Excel_to_db_inventory_items> datas = Excel_to_db_inventory_items.showExcelData(sheetData, file); int with_qty = 0; for (Excel_to_db_inventory_items encoded : datas) { if (FitIn.toDouble(encoded.qty) > 0) { with_qty++; } int id = -1; String barcode = "" + FitIn.toInt(encoded.item_code); if (barcode.equalsIgnoreCase("n/a")) { barcode = ""; } String description = encoded.description; String generic_name = ""; String category = encoded.category; if (category.equalsIgnoreCase("n/a")) { category = ""; } String category_id = ""; String classification = encoded.classification; if (classification.equalsIgnoreCase("n/a")) { classification = ""; } String classification_id = ""; String sub_classification = encoded.sub_classification; if (sub_classification.equalsIgnoreCase("n/a")) { sub_classification = ""; } String sub_classification_id = ""; double product_qty = 0; double conversion = 1; double selling_price = FitIn.toDouble(encoded.selling_price); String date_added = DateType.now(); String user_name = ""; String item_type = "Regular"; int status = 1; String supplier = ""; int fixed_price = 0; double cost = FitIn.toDouble(encoded.cost); String supplier_id = ""; int multi_level_pricing = 0; int vatable = 0; double reorder_level = 0; double markup = 0; String barcodes = encoded.barcode; if (barcodes.equalsIgnoreCase("n/a")) { barcodes = ""; } String brand = encoded.brand; if (brand.equalsIgnoreCase("n/a")) { brand = ""; } String brand_id = ""; String model = encoded.model; if (brand.equalsIgnoreCase("n/a")) { model = ""; } String model_id = ""; int selling_type = 1; String branch = "Dumaguete-Main Branch"; String branch_code = "1"; String location = "Warehouse"; String location_id = "1"; String unit = "[" + encoded.unit + ":" + encoded.selling_price + "/1.0^1]"; // System.out.println("Unit: "+unit); int is_uploaded = 0; int allow_negative_inventory = 0; int auto_order = 1; Inventory.to_inventory to = new Inventory.to_inventory(id, barcode, description, generic_name, category, category_id, classification, classification_id, sub_classification, sub_classification_id, product_qty, unit, conversion, selling_price, date_added, user_name, item_type, status, supplier, fixed_price, cost, supplier_id, multi_level_pricing, vatable, reorder_level, markup, barcodes, brand, brand_id, model, model_id, selling_type, branch, branch_code, location, location_id, false, is_uploaded, allow_negative_inventory, auto_order); Inventory.add_inventory(to); //encoding String item_code = "" + FitIn.toInt(encoded.item_code); String branch_id = "1"; double qty = FitIn.toDouble(encoded.qty); String screen_name = "administrator"; String sheet_no = "1"; String counted_by = "admin"; String checked_by = "admin"; String user_id = MyUser.getUser_id(); String user_screen_name = MyUser.getUser_screen_name(); String remarks = ""; Encoding_inventory.to_encoding_inventory en = new Encoding_inventory.to_encoding_inventory(id, item_code, barcodes, description, branch, branch_id, location, location_id, qty, date_added, user_name, screen_name, sheet_no, 0, counted_by, checked_by, cost, selling_price, user_id, user_screen_name, remarks); Encoding_inventory.add_encoding_inventory(en); } System.out.println("Count: " + with_qty); Alert.set(1, ""); }
From source file:POS.test2.pisps_items.java
public static List<items> showItems() { String path = "C:\\\\Users\\\\Guinness\\\\Documents\\\\Projects\\\\Algorithm\\\\pisps records\\\\items.xls"; FileInputStream fis = null;/*from www . j a va 2 s .co m*/ List sheetData = new ArrayList(); try { fis = new FileInputStream(path); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<items> datas = new ArrayList(); try { fis = new FileInputStream(path); int r = 0; int r_set = 1; String prev_no = ""; for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); int size = list.size(); List<String> record = new ArrayList(); for (int j = 0; j < list.size(); j++) { HSSFCell cell = (HSSFCell) list.get(j); HSSFDataFormatter hdf = new HSSFDataFormatter(); String data = ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mydata = cell.getStringCellValue(); data = data + "" + mydata + ""; record.add(data); } else { data = data + cell.getNumericCellValue() + ""; record.add(data); } } String[] aw = new String[size]; int jj = 0; for (String s : record) { aw[jj] = s; jj++; } String item_code = FitIn.fmt_woc(aw[7]); String description = aw[1]; String category = aw[2]; String classification = aw[3]; String sub_classification = aw[4]; String brand = aw[5]; String model = aw[6]; items item = new items(item_code, description, category, classification, sub_classification, brand, model); datas.add(item); } return datas; } catch (FileNotFoundException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } return datas; }