Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet.

Prototype

@Override
public HSSFSheet getSheet(String name) 

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processUpdates(Sheet sheet) {
    System.out.println("Process updates on sheet: " + sheet.getSheetName());
    if (hasPrevious()) {
        FileInputStream previousStream = null;
        HSSFWorkbook prevWorkbook = null;
        try {/*from  w w w . j av a 2s. c  o m*/
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet != null) {
                // Bestaande mensen: eventuele updates
                for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
                    Row row = it.next();
                    if (row.getRowNum() > 0) {
                        String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                        Row previousRow = getLidFromSheet(lidnummer, prevSheet);
                        processPersonUpdates(row, previousRow);
                    }

                }
            }
        } catch (FileNotFoundException ex) {
            System.out.println("Could not locate file: " + ex.getLocalizedMessage());
        } catch (IOException ex) {
            System.out.println("Problems reading file: " + ex.getLocalizedMessage());
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                    if (prevWorkbook != null) {
                        FileOutputStream out = new FileOutputStream(previous);

                        prevWorkbook.write(out);
                        out.close();
                    }
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
    }
}

---

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processQuitters() {
    if (hasPrevious()) {
        Sheet sheet = workbook.getSheet(SorterFactory.GROUP_NAME_ALL);
        List<Row> quitters = new ArrayList<Row>();
        FileInputStream previousStream = null;
        try {//  w  w  w .  ja  va2  s  .c  om
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            HSSFWorkbook prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet == null) {
                return;
            }
            // Check of er mensen vertrokken zijn
            for (Row row : prevSheet) {
                if (row.getRowNum() > 0) {
                    String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                    Row currentRow = getLidFromSheet(lidnummer, sheet);
                    if (currentRow == null) {
                        quitters.add(row);
                    }
                }
            }
        } catch (IOException ex) {
            System.out.println("Error Reading the previous file: " + ex.getLocalizedMessage());
            return;
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
        if (quitters.isEmpty()) {
            return;
        }
        Sheet removedSheet = workbook.createSheet(SHEET_REMOVED_PERSONS);

        // Create header
        Row header = removedSheet.createRow(0);
        //Lidnummer   Achternaam   Tussenvoegsel   Voornaam   Geslacht    Telefoonnummer   Mobiel  Geboortedatum

        Cell lidnummer = header.createCell(0);
        lidnummer.setCellValue("Lidnummer");
        lidnummer.setCellStyle(headingStyle);
        Cell achternaam = header.createCell(1);
        achternaam.setCellValue("Achternaam");
        achternaam.setCellStyle(headingStyle);
        Cell tussenvoegsel = header.createCell(2);
        tussenvoegsel.setCellValue("Tussenvoegsel");
        tussenvoegsel.setCellStyle(headingStyle);
        Cell voornaam = header.createCell(3);
        voornaam.setCellValue("Voornaam");
        voornaam.setCellStyle(headingStyle);
        Cell geslacht = header.createCell(4);
        geslacht.setCellValue("Geslacht");
        geslacht.setCellStyle(headingStyle);
        Cell telefoonnummer = header.createCell(5);
        telefoonnummer.setCellValue("Telefoonnummer");
        telefoonnummer.setCellStyle(headingStyle);
        Cell geboortedatum = header.createCell(6);
        geboortedatum.setCellValue("Geboortedatum");
        geboortedatum.setCellStyle(headingStyle);

        // Iterate over quitters
        int index = 1;
        for (Row quitter : quitters) {
            Row r = removedSheet.createRow(index);
            r.createCell(0).setCellValue(quitter.getCell(0).getStringCellValue());
            r.createCell(1).setCellValue(quitter.getCell(1).getStringCellValue());
            r.createCell(2).setCellValue(quitter.getCell(2).getStringCellValue());
            r.createCell(3).setCellValue(quitter.getCell(3).getStringCellValue());
            r.createCell(4).setCellValue(quitter.getCell(5).getStringCellValue());
            r.createCell(5).setCellValue(quitter.getCell(10).getStringCellValue());
            r.createCell(6).setCellValue(quitter.getCell(21).getStringCellValue());
            index++;
        }

        removedSheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 6));
        int numcells = removedSheet.getRow(0).getLastCellNum();
        for (int i = 0; i < numcells; i++) {
            removedSheet.autoSizeColumn(i);
        }
    }
}

---

From source file:org.databene.benerator.main.DBSnaphotToolTest.java

License:Open Source License

@Test
public void testHsqlXlsSnapshot() throws Exception {
    // prepare DB
    String db = getClass().getSimpleName();
    Connection connection = HSQLUtil.connectInMemoryDB(db);
    DBUtil.executeScriptFile(CREATION_SCRIPT, ENCODING, connection, true, new ErrorHandler(getClass()));
    // prepare snapshot
    setSystemProperties(HSQLUtil.IN_MEMORY_URL_PREFIX + db, HSQLUtil.DRIVER, HSQLUtil.DEFAULT_USER,
            HSQLUtil.DEFAULT_PASSWORD, HSQLUtil.DEFAULT_SCHEMA, DBSnapshotTool.XLS_FORMAT, ENCODING);
    // create snapshot
    DBSnapshotTool.main(new String[] { XLS_SNAPSHOT_FILE });
    File file = new File(XLS_SNAPSHOT_FILE);
    assertTrue("Snapshot file was not created: " + file, file.exists());

    HSSFWorkbook workbook = new HSSFWorkbook(IOUtil.getInputStreamForURI(XLS_SNAPSHOT_FILE));
    HSSFSheet sheet = workbook.getSheet("T1");
    assertNotNull("Sheet T1 not found", sheet);
    HSSFRow headerRow = sheet.getRow(0);
    assertEquals("ID", headerRow.getCell(0).getStringCellValue());
    assertEquals("NAME", headerRow.getCell(1).getStringCellValue());
    HSSFRow dataRow1 = sheet.getRow(1);//from  w ww. ja  v  a  2  s. co m
    assertEquals(1., dataRow1.getCell(0).getNumericCellValue());
    assertEquals("R&B", dataRow1.getCell(1).getStringCellValue());
}

---

From source file:org.drools.scorecards.parser.xls.XLSScorecardParser.java

License:Apache License

@Override
public List<ScorecardError> parseFile(InputStream inStream, String worksheetName)
        throws ScorecardParseException {
    try {//from  w w w . j  a v a 2  s .  c o  m
        excelDataCollector = new XLSEventDataCollector();
        excelDataCollector.setParser(this);
        HSSFWorkbook workbook = new HSSFWorkbook(inStream);
        HSSFSheet worksheet = workbook.getSheet(worksheetName);
        if (worksheet != null) {
            currentWorksheet = worksheet;
            excelDataCollector.sheetStart(worksheetName);
            excelDataCollector.setMergedRegionsInSheet(getMergedCellRangeList(worksheet));
            processSheet(worksheet);
            excelDataCollector.sheetComplete();
            parseErrors = excelDataCollector.getParseErrors();
            scorecard = excelDataCollector.getScorecard();
        } else {
            throw new ScorecardParseException("No worksheet found with name '" + worksheetName + "'.");
        }
    } catch (IOException e) {
        throw new ScorecardParseException(e);
    }
    return parseErrors;
}

---

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

@Override
public int getNewId(String fileName) {
    HSSFWorkbook workBook = new HSSFWorkbook();
    try {//from   w w w  .  j  a  v a2 s  . c o m
        FileInputStream in = new FileInputStream(fileName);
        workBook = (HSSFWorkbook) WorkbookFactory.create(in);
        in.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }

    HSSFSheet sheet = workBook.getSheet(DEFAULT_SHEET_NAME);

    return sheet.getLastRowNum() + 1;
}

---

From source file:org.forzaframework.util.XlsUtils.java

License:Apache License

public static HSSFSheet getSheet(HSSFWorkbook wb, String sheetName, Boolean createNewSheet,
        Integer indexSheet) {//from www .  j av a  2s .co m
    HSSFSheet sheet = null;//Revisamos si vamos a crear una hoja nueva o con una ya existente.
    if ((createNewSheet != null && createNewSheet) || wb.getNumberOfSheets() == 0) {
        //Creamos una hoja nueva
        if (sheetName != null) {
            sheet = wb.createSheet(sheetName);
        } else {
            sheet = wb.createSheet();
        }
    } else {
        //Revisamos si existe la hoja con el nombre especificado
        if (indexSheet == null && sheetName != null) {
            sheet = wb.getSheet(sheetName);
        }

        if (sheet == null) {
            //Trabajamos con una hoja ya existente
            if (indexSheet == null) {
                indexSheet = 0;
            }
            if (sheetName != null) {
                wb.setSheetName(indexSheet, sheetName);
            }
            sheet = wb.getSheetAt(indexSheet);
        }
    }
    return sheet;
}

---

From source file:org.gageot.excel.core.ExcelTemplate.java

License:Apache License

/**
 * Read the content of an Excel file for a given sheet name.
 * The content of the sheet is extracted using SheetExtractor.
 * @param sheetName name of the excel sheet
 * @param sheetExtractor object that will extract results
 * @return an arbitrary result object, as returned by the ResultSetExtractor
 * @throws DataAccessException if there is any problem
 *///from w ww .ja  v a  2 s.c  o m
public <T> T read(final String sheetName, final SheetExtractor<T> sheetExtractor) throws DataAccessException {
    checkNotNull(sheetExtractor, "SheetExtractor must not be null");
    checkNotNull(sheetName, "sheetName must not be null");

    return read(new Function<HSSFWorkbook, T>() {
        @Override
        public T apply(HSSFWorkbook workbook) {
            HSSFSheet sheet = workbook.getSheet(sheetName);
            try {
                return sheetExtractor.extractData(sheet);
            } catch (IOException e) {
                throw new DataAccessResourceFailureException("Problem reading file", e);
            }
        }
    });
}

---

From source file:org.geoserver.monitor.rest.RequestResourceTest.java

License:Open Source License

@Test
public void testGetAllExcel() throws Exception {
    RequestResource.ExcelFormat format = new RequestResource.ExcelFormat(
            new String[] { "id", "path", "startTime" }, monitor);

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    format.toRepresentation(monitor.getDAO().getRequests()).write(out);

    HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
    HSSFSheet sheet = wb.getSheet("requests");

    Iterator<Row> rows = sheet.iterator();
    Iterator<RequestData> it = monitor.getDAO().getRequests().iterator();

    assertTrue(rows.hasNext());//from ww  w . ja  v a2 s .com
    Row row = rows.next();
    assertEquals("id", row.getCell(0).getStringCellValue());
    assertEquals("path", row.getCell(1).getStringCellValue());
    assertEquals("startTime", row.getCell(2).getStringCellValue());

    while (rows.hasNext()) {
        row = rows.next();

        assertTrue(it.hasNext());
        RequestData data = it.next();

        assertEquals((double) data.getId(), row.getCell(0).getNumericCellValue(), 0.1);
        assertEquals(data.getPath(), row.getCell(1).getStringCellValue());
        assertEquals(data.getStartTime(), row.getCell(2).getDateCellValue());
    }

    assertFalse(it.hasNext());
}

---

From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

public void mergeIDs() {
    System.err.println("Merging...");
    try {/*from www . j ava2s. c o  m*/
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls");
        FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls");
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        HSSFSheet mergeSheet = wb.getSheet("mergeList");
        int numRows = mergeSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = mergeSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    if (cell != null) {
                        Integer oldEfsaID = (int) cell.getNumericCellValue();
                        if (cell != null) {
                            cell = row.getCell(1);
                            Integer newEfsaID = (int) cell.getNumericCellValue();
                            DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID);
                        }
                    }
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }
    System.err.println("Merging...Fin!");
}

---

From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private HashMap<String, Integer> loadNodeIDs10000() {
    System.err.println("loadNodeIDs10000...");

    nodeIds = new HashMap<String, Integer>();
    try {//  w ww  . j a  v a2 s.co  m
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls");
        FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls");
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        HSSFSheet defaultSheet = wb.getSheet("default");
        int numRows = defaultSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = defaultSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    Integer id = (int) cell.getNumericCellValue();
                    if (id > maxNodeID)
                        maxNodeID = id;
                    cell = row.getCell(1);
                    String name = cell.getStringCellValue();
                    nodeIds.put(name, id);
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }

    System.err.println("loadNodeIDs10000...Fin!");
    return nodeIds;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »