List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetName
String getSheetName(int sheet);
From source file:com.movielabs.availslib.AvailSS.java
License:Open Source License
/** * Dump the contents (sheet-by-sheet) of an Excel spreadsheet * @param file name of the Excel .xlsx spreadsheet * @throws Exception if any error is encountered (e.g. non-existant or corrupt file) *//*w ww. ja v a2 s.c o m*/ public static void dumpFile(String file) throws Exception { Workbook wb = new XSSFWorkbook(new FileInputStream(file)); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("Sheet <" + wb.getSheetName(i) + ">"); for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { System.out.println(" | " + cell.toString()); } } } wb.close(); }
From source file:com.ostrichemulators.semtool.poi.main.POIReader.java
License:Open Source License
public static ImportData readNonloadingSheet(Workbook workbook) { ImportData id = new ImportData(); int sheets = workbook.getNumberOfSheets(); for (int sheetnum = 0; sheetnum < sheets; sheetnum++) { Sheet sheet = workbook.getSheetAt(sheetnum); String sheetname = workbook.getSheetName(sheetnum); // we need to shoehorn the arbitrary data from a spreadsheet into our // ImportData class, which has restrictions on the data...we're going // to do it by figuring out the row with the most columns, and then // naming all the columns with A, B, C...AA, AB... // then load everything as if it was plain data // first, figure out our max number of columns int rows = sheet.getLastRowNum(); int maxcols = Integer.MIN_VALUE; for (int r = 0; r <= rows; r++) { Row row = sheet.getRow(r);//from w w w .j a v a2 s .com if (null != row) { int cols = (int) row.getLastCellNum(); if (cols > maxcols) { maxcols = cols; } } } // second, make "properties" for each column LoadingSheetData nlsd = new LoadingSheetData(sheetname, "A"); for (int c = 1; c < maxcols; c++) { nlsd.addProperty(Integer.toString(c)); } // lastly, fill the sheets for (int r = 0; r <= rows; r++) { Row row = sheet.getRow(r); if (null != row) { Map<String, Value> propmap = new HashMap<>(); int lastpropcol = row.getLastCellNum(); for (int c = 1; c <= lastpropcol; c++) { String val = getString(row.getCell(c)); if (!val.isEmpty()) { propmap.put(Integer.toString(c), VF.createLiteral(val)); } } nlsd.add(getString(row.getCell(0)), propmap); } } if (!nlsd.isEmpty()) { id.add(nlsd); } } return id; }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java
License:Apache License
@Override public String[] getSheetNames(File f) throws IOException { Workbook wb = open(f); String[] names = new String[wb.getNumberOfSheets()]; for (int i = 0; i < names.length; i++) { names[i] = wb.getSheetName(i); }/*from w w w . j a v a 2 s . c o m*/ return names; }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java
License:Apache License
private void excerpt(Workbook wb, List<Sheet> sheetsToKeep, OutputStream output) throws IOException { // Make the requested sheets be read only Set<String> keepNames = new HashSet<String>(); for (Sheet s : sheetsToKeep) { keepNames.add(s.getSheetName()); for (Row r : s) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { switch (c.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: double vd = c.getNumericCellValue(); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(vd); break; case Cell.CELL_TYPE_STRING: RichTextString vs = c.getRichStringCellValue(); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(vs); break; case Cell.CELL_TYPE_BOOLEAN: boolean vb = c.getBooleanCellValue(); c.setCellType(Cell.CELL_TYPE_BOOLEAN); c.setCellValue(vb); break; case Cell.CELL_TYPE_ERROR: c.setCellType(Cell.CELL_TYPE_BLANK); break; }// w w w. j av a2 s . co m } } } } // Remove all the other sheets // Note - work backwards! Avoids order changing under us for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) { String name = wb.getSheetName(i); if (!keepNames.contains(name)) { wb.removeSheetAt(i); } } // Save wb.write(output); }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java
License:Apache License
@Test public void excerptRemovesUnUsed() throws Exception { String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" }; for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) { // Create some dummy content for (String sn : names) { Sheet s = wb.createSheet(sn); s.createRow(0).createCell(0).setCellValue(sn); }//from ww w .j av a 2 s . c om // Excerpt by index File tmp = File.createTempFile("test", ".xls"); wb.write(new FileOutputStream(tmp)); ByteArrayOutputStream baos = new ByteArrayOutputStream(); int[] excI = new int[] { 0, 1, 2, 4, 5 }; excerpter.excerpt(excI, tmp, baos); // Check Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(5, newwb.getNumberOfSheets()); assertEquals(names[excI[0]], newwb.getSheetName(0)); assertEquals(names[excI[1]], newwb.getSheetName(1)); assertEquals(names[excI[2]], newwb.getSheetName(2)); assertEquals(names[excI[3]], newwb.getSheetName(3)); assertEquals(names[excI[4]], newwb.getSheetName(4)); assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue()); // Excerpt by name String[] excN = new String[] { "b", "ccc", "f", "gg" }; baos = new ByteArrayOutputStream(); excerpter.excerpt(excN, tmp, baos); newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(4, newwb.getNumberOfSheets()); assertEquals(excN[0], newwb.getSheetName(0)); assertEquals(excN[1], newwb.getSheetName(1)); assertEquals(excN[2], newwb.getSheetName(2)); assertEquals(excN[3], newwb.getSheetName(3)); assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue()); // Can't excerpt by invalid index try { excerpter.excerpt(new int[] { 0, 10 }, tmp, null); fail(); } catch (IllegalArgumentException e) { } // Can't excerpt by invalid name try { excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null); fail(); } catch (IllegalArgumentException e) { } } }
From source file:com.runwaysdk.dataaccess.io.ExcelImporter.java
License:Open Source License
/** * Opens the stream, parses the types from the sheets and set up context objects for them * /*from w w w . j av a2s. com*/ * @param stream * @return * @throws IOException */ private void openStream(InputStream stream) { try { Workbook workbook = ExcelUtil.getWorkbook(stream); this.errorWorkbook = ExcelUtil.createWorkbook(workbook); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); // Skip the error sheet if (this.isValidSheet(sheet, sheetName)) { Row row = sheet.getRow(0); Cell cell = row.getCell(0); String type = ExcelUtil.getString(cell); contexts.add(builder.createContext(sheet, sheetName, errorWorkbook, type)); } } errorWorkbook.createSheet(ERROR_SHEET); } catch (IOException e) { throw new SystemException(e); } }
From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java
License:Open Source License
public List<String> listSheets(InputStream is) throws IOException { ArrayList<String> sheets = new ArrayList<>(); Workbook wb = createWorkbook(is); for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheets.add(wb.getSheetName(i)); }// w ww . j a v a2 s . co m return sheets; }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
@Override public List<String> getSheets(MultipartFile file) throws FileNotFoundException, InvalidFormatException, IOException { xlsxFile = File.createTempFile("fichier", "xslx"); file.transferTo(xlsxFile);/*w w w . j a v a 2s . c o m*/ FileInputStream inp = new FileInputStream(xlsxFile); Workbook workbook = WorkbookFactory.create(inp); List<String> list = new ArrayList<>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { list.add(workbook.getSheetName(i)); } return list; }
From source file:com.wantdo.stat.excel.poi_src.formula.CheckFunctionsSupported.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length < 1) { System.err.println("Use:"); System.err.println(" CheckFunctionsSupported <filename>"); return;/*from w w w. j av a 2s.c o m*/ } Workbook wb = WorkbookFactory.create(new File(args[0])); CheckFunctionsSupported check = new CheckFunctionsSupported(wb); // Fetch all the problems List<FormulaEvaluationProblems> problems = new ArrayList<FormulaEvaluationProblems>(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { problems.add(check.getEvaluationProblems(sn)); } // Produce an overall summary Set<String> unsupportedFunctions = new TreeSet<String>(); for (FormulaEvaluationProblems p : problems) { unsupportedFunctions.addAll(p.unsupportedFunctions); } if (unsupportedFunctions.isEmpty()) { System.out.println("There are no unsupported formula functions used"); } else { System.out.println("Unsupported formula functions:"); for (String function : unsupportedFunctions) { System.out.println(" " + function); } System.out.println("Total unsupported functions = " + unsupportedFunctions.size()); } // Report sheet by sheet for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { String sheetName = wb.getSheetName(sn); FormulaEvaluationProblems probs = problems.get(sn); System.out.println(); System.out.println("Sheet = " + sheetName); if (probs.unevaluatableCells.isEmpty()) { System.out.println(" All cells evaluated without error"); } else { for (CellReference cr : probs.unevaluatableCells.keySet()) { System.out.println( " " + cr.formatAsString() + " - " + probs.unevaluatableCells.get(cr).toString()); } } } }
From source file:de.bund.bfr.knime.pmm.common.XLSReader.java
License:Open Source License
public List<String> getSheets(File file) throws Exception { List<String> sheets = new ArrayList<>(); Workbook workbook = getWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheets.add(workbook.getSheetName(i)); }/*from w w w . ja va2s . c om*/ return sheets; }