List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java
protected static ArrayList<String> getListOfSheets(HSSFWorkbook workbook) { ArrayList<String> listOfSheets = new ArrayList<>(); int numberOfSheet = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { listOfSheets.add(workbook.getSheetName(i)); }// w ww .ja v a2 s .com return listOfSheets; }
From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java
License:Open Source License
@Override public void processWorkbook(HSSFWorkbook workbook, TopicMap topicMap) { int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); processSheet(sheet, topicMap);/*from w w w . ja va2 s. c o m*/ } }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
@Override public boolean _extractTopicsFrom(URL u, TopicMap tm) throws Exception { try {//ww w. j av a2 s .c om HSSFWorkbook workbook = new HSSFWorkbook(u.openStream()); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } log("Ok!"); } catch (FileNotFoundException ex) { log(ex); } catch (IOException ex) { log(ex); } catch (Exception ex) { log(ex); } setState(WAIT); return true; }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
@Override public boolean _extractTopicsFrom(File f, TopicMap tm) throws Exception { try {/*from w w w . ja va 2 s . co m*/ if (f != null) { String fn = f.getAbsolutePath(); if (fn.toLowerCase().endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(f)); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } } else { XSSFWorkbook workbook = new XSSFWorkbook(f.getAbsolutePath()); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } } log("Ok!"); } } catch (FileNotFoundException ex) { log(ex); } catch (IOException ex) { log(ex); } catch (Exception ex) { log(ex); } setState(WAIT); return true; }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
public Collection getSheets(HSSFWorkbook workbook) { ArrayList<String> sheets = new ArrayList(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { sheets.add(workbook.getSheetName(i)); }//from w ww .j a va 2 s .c om return sheets; }
From source file:org.yccheok.jstock.file.Statements.java
License:Open Source License
/** * Construct Statements based on given Excel File. * * @param file Given Excel File//w ww. ja v a 2s. c om * @return the List of constructed Statements. Empty list if fail. */ public static List<Statements> newInstanceFromExcelFile(File file) { FileInputStream fileInputStream = null; final List<Statements> statementsList = new ArrayList<Statements>(); try { fileInputStream = new FileInputStream(file); final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int k = 0; k < numberOfSheets; k++) { final HSSFSheet sheet = wb.getSheetAt(k); final int startRow = sheet.getFirstRowNum(); final int endRow = sheet.getLastRowNum(); // If there are 3 rows, endRow will be 2. // We must have at least 2 rows. (endRow = 1) if (startRow != 0 || endRow <= startRow) { continue; } final HSSFRow row = sheet.getRow(startRow); if (row == null) { continue; } final int startCell = row.getFirstCellNum(); final int endCell = row.getLastCellNum(); // If there are 2 cols, endCell will be 2. // We must have at least 1 col. (endCell = 1) if (startCell != 0 || endCell <= startCell) { continue; } final List<String> types = new ArrayList<String>(); for (int i = startCell; i < endCell; i++) { final HSSFCell cell = row.getCell(i); if (cell == null) { continue; } // Exception may be thrown here, as cell may be numerical value. final String type = cell.getRichStringCellValue().getString(); if (type != null) { types.add(type); } } if (types.isEmpty()) { continue; } if (types.size() != (endCell - startCell)) { continue; } final Statement.What what = Statement.what(types); Statements s = new Statements(what.type, what.guiBundleWrapper); for (int i = startRow + 1; i <= endRow; i++) { final HSSFRow r = sheet.getRow(i); if (r == null) { continue; } final List<Atom> atoms = new ArrayList<Atom>(); for (int j = startCell; j < endCell; j++) { final HSSFCell cell = r.getCell(j); if (cell == null) { continue; } Object value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { final HSSFRichTextString richString = cell.getRichStringCellValue(); if (richString != null) { value = richString.getString(); } else { value = ""; } } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { try { value = new Double(cell.getNumericCellValue()); } catch (NumberFormatException ex) { log.error(null, ex); value = new Double(0.0); } } else { } if (null == value) { continue; } atoms.add(new Atom(value, types.get(j - startCell))); } final Statement statement = new Statement(atoms); if (s.getType() != statement.getType()) { // Give up. s = null; break; } s.statements.add(statement); } // for (int i = startRow + 1; i <= endRow; i++) if (s != null) { statementsList.add(s); } } /* for(int k = 0; k < numberOfSheets; k++) */ } catch (Exception ex) { log.error(null, ex); } finally { org.yccheok.jstock.gui.Utils.close(fileInputStream); } return statementsList; }
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 w w.j av a 2s. c om*/ * @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. * /*from w w w . j av 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:parserspec.ParserSpec.java
public static void ParseFaculty(String path) throws Exception { InputStream in = new FileInputStream(path); HSSFWorkbook wb = new HSSFWorkbook(in); Sheet sheet = wb.getSheetAt(0);//from ww w . j av a 2s . c o m Cell tempCell = sheet.getRow(0).getCell(8); String curFaculty = tempCell.getStringCellValue(); for (int i = 0; i != wb.getNumberOfSheets(); ++i) { Sheet currSheet = wb.getSheetAt(i); Row currRow = currSheet.getRow(3); Iterator<Cell> cells = currRow.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String currValue = cell.getStringCellValue(); if ((currValue != "") && (currValue != " ")) { StringTokenizer st = new StringTokenizer(currValue, "\t\n\r,.()-"); ArrayList<String> tempList = new ArrayList<String>(); Speciality currSpec = new Speciality(); while (st.hasMoreTokens()) { String temp = st.nextToken(); if (temp != " ") tempList.add(temp); } currSpec.faculty = curFaculty; currSpec.name = tempList.get(0); currSpec.group = tempList.get(1); currSpec.code = tempList.get(2); currSpec.type = tempList.get(4); allSpec.add(currSpec); } } } } }
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main// w ww .j ava 2 s.co m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }