Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheetAt.

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

public List<LogMessage> readPurchaseOrders() {

    List<LogMessage> result = new ArrayList<LogMessage>();
    FileInputStream fis = null;/*from   w  w w .  j  av a  2s  .com*/
    Workbook wb = null;
    try {
        fis = new FileInputStream(file);
        // Get the workbook instance for XLS file

        if (".xls".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new HSSFWorkbook(fis);
        } else if ("xlsx".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("Received file does not have a standard excel extension.");
        }

        // Get each sheet from the workbook
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            result.addAll(readPurchaseOrders(wb.getSheetAt(i)));
        }

    } catch (Exception e) {
        log.error("Error al validar campos en excel: [" + e.getMessage() + "]", e);
        result.add(new LogMessage("Validacin de ordenes de compra",
                "Error al validar las Ordenes de compra: [" + e.getMessage() + "]"));
    } finally {
        try {
            fis.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        try {
            wb.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    return result;
}

From source file:com.jaspersoft.ireport.designer.connection.gui.XlsxDataSourceConnectionEditor.java

License:Open Source License

private void jButtonGetColumnsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGetColumnsActionPerformed
    try {// w w w  .  j av a2 s  .c  om

        if (jTextFieldFilename.getText().length() > 0) {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(jTextFieldFilename.getText())));
            Sheet sheet = workbook.getSheetAt(0);

            DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel();
            dtm.setRowCount(0);

            Row row = sheet.getRow(0);
            Map<String, Integer> columnNames = new HashMap<String, Integer>();
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                Cell cell = row.getCell(columnIndex);
                String columnName = "";
                if (cell != null) {
                    columnName = cell.toString();
                } else {
                    columnName = "COLUMN_" + columnIndex;
                }

                if (columnName != null && columnName.trim().length() > 0) {
                    dtm.addRow(new Object[] { columnName, new Integer(columnIndex) });
                }
            }

            jTable1.updateUI();

            jCheckBoxFirstRowAsHeader.setSelected(true);
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(this, ex.getMessage(),
                I18n.getString("XlsxDataSourceConnectionEditor.Message.Exception"), JOptionPane.ERROR_MESSAGE); //"message.title.exception"
    }
}

From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetNames.java

@Override
protected ObservableList<String> call() throws Exception {
    logger.log(Level.INFO, "call()");
    updateProgress(-1L, 1L);/*  w  w w  .j  a va  2s. c  om*/

    ObservableList<String> ol = null;
    Workbook wb = null;

    try {
        logger.log(Level.INFO, "creating workbook");
        wb = WorkbookFactory.create(xlsFile);
    } catch (IOException | InvalidFormatException ex) {
        logger.log(Level.SEVERE, null, ex);
        updateProgress(0L, 1L);
        throw ex;
    }

    if (wb != null) {
        logger.log(Level.INFO, "get sheet names");

        ol = FXCollections.observableArrayList();
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            ol.add(wb.getSheetAt(i).getSheetName());
        }

        logger.log(Level.INFO, "got {0} sheet names", ol.size());
        updateProgress(1L, 1L);
    } else {
        logger.log(Level.WARNING, "workbook *is* null");
        updateProgress(0L, 1L);
    }

    return ol;
}

From source file:com.khodev.sc2.quiz.Quiz.java

License:Open Source License

public Quiz(String xls) throws IOException {
    FileInputStream file = new FileInputStream(xls);
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);
    boolean headerSkipped = false;
    for (Row row : sheet) {
        if (!headerSkipped) {
            headerSkipped = true;//from   w w w  . j a  v  a 2s .  c  o m
            continue;
        }
        questions.add(new Question(row.getCell(2).toString(), row.getCell(3).toString()));
    }

}

From source file:com.kongwu.insweb.utils.ReadExcel.java

License:Apache License

/**
 * @param filepath excel/*  w w  w . ja  v a  2  s .  c  om*/
 *        
 *        
 *        
 * @return ??,:list<usrid,query,biz,semantic>
 *                        :
 *                           :
 * @throws IOException
 */
public static List<List<String>> readTestset(String filepath) throws IOException {
    FileInputStream fileIn = null;
    List<List<String>> testsetList = new ArrayList<List<String>>();
    /**?excel**/
    try {
        fileIn = new FileInputStream(filepath);
        /**excel 2007**/
        if (filepath.endsWith(".xlsx")) {
            Workbook wb = new XSSFWorkbook(fileIn);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                /**
                 * ?cell
                 */
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(i);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }

                testsetList.add(list);
            }
        } else {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;
            HSSFCell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)// ???
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }
                testsetList.add(list);
            }
        }
    } finally {
        if (fileIn != null)
            fileIn.close();
    }
    return testsetList;
}

From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java

@Override
public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException {
    List<Prize> prizes = null;
    List<Result> resultList = new ArrayList<>();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017
    try {/*ww w . j a v a2 s  .  c om*/
        Workbook workbook = new XSSFWorkbook(in);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Cell cell0 = currentRow.getCell(0); // get first cell.

            if (cell0.getCellTypeEnum() == CellType.NUMERIC) {
                int numberic = (int) cell0.getNumericCellValue();
                log.info("Numberic - " + numberic);

                // check lottery type
                if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result
                    Result result = new Result();
                    result.setType(numberic);
                    result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue());
                    //                        result.setResultFor(format.parse(currentRow.getCell(2).toString()));
                    result.setResultFor(currentRow.getCell(2).getDateCellValue());
                    result.setDataProvider(currentRow.getCell(3).getStringCellValue());
                    result.setCompanyName(currentRow.getCell(4).getStringCellValue());

                    prizes = new ArrayList<>();
                    result.setPrizes(prizes);
                    resultList.add(result);
                }

            } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data
                String character = cell0.getStringCellValue();
                log.info("character - " + character);

                // check validation for character.
                String value = charMap.get(character);
                if (value == null) {
                    throw new ParseException(
                            "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400);
                }
                Cell cell1 = currentRow.getCell(1);
                if (cell1.getCellTypeEnum() != CellType.NUMERIC) {
                    throw new ParseException(
                            "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400);
                }
                log.info("Cell Type " + cell1.getCellTypeEnum());
                int code = (int) cell1.getNumericCellValue();
                log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1);
                String prizeTitle = currentRow.getCell(2).getStringCellValue();
                log.info("prizeTitle - " + prizeTitle);
                String prizeDesc = currentRow.getCell(4).getStringCellValue();
                log.info("prizeDesc - " + prizeDesc);
                prizes.add(new Prize(character, code, prizeTitle, prizeDesc));
            }
        }
        log.info("resultList size: " + resultList.size());
        for (Result r : resultList) {
            log.info("prizeList size: " + r.getPrizes().size());
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        throw e;
    }
    return resultList;
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
public void judgeRepeatExcle(InputStream is, int firstRowNum, int colNum, String fileName) throws Exception {
    Map<String, Object> sheetValues = new HashedMap();
    Workbook workbook = null;
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }/*w  ww  .jav a2s . c  om*/
    Sheet sheet = workbook.getSheetAt(0);
    for (int i = firstRowNum; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i - 1);
        System.out.println(i);
        Cell cell = row.getCell(colNum - 1);
        if (null != cell && null != POIUtils.getStringCellValue(cell)
                && !POIUtils.getStringCellValue(cell).equals("")) {
            if (sheetValues.containsKey(POIUtils.getStringCellValue(cell))) {
                throw new RuntimeException(
                        POIUtils.getStringCellValue(cell) + "??????");
            } else {
                sheetValues.put(POIUtils.getStringCellValue(cell), i);
            }
        }
    }
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

public Map<String, List> readExcelData(Workbook wb, List<Tab> tabsList) {
    logger.info("Class - ReadExcelFile - readExcelData()");
    int sheetCount = wb.getNumberOfSheets();
    Map<String, List> populatedData = new HashMap<String, List>();

    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.EXPLORATION.toString())) {
            Tab tab = new Tab();
            List<Exploration> explorationList = populateExplorationData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);//  w w  w.j a  v  a2s. c o m
            populatedData.put(sheet.getSheetName().toLowerCase(), explorationList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.REFINERIES.toString())) {
            Tab tab = new Tab();
            List<Refinery> refineryList = populateRefineryData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), refineryList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.STORAGE.toString())) {
            Tab tab = new Tab();
            List<Storage> storageList = populateStorageData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), storageList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.LNG.toString())) {
            Tab tab = new Tab();
            List<Lng> lngList = populateLngData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), lngList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.PIPELINES.toString())) {
            Tab tab = new Tab();
            List<PipeLine> pipeLineList = populatePipeLinesData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), pipeLineList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.CRUDEOIL.toString())) {
            Tab tab = new Tab();
            List<CrudeOil> crudeOilList = populateCrudeOilData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), crudeOilList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.NATURALGAS.toString())) {
            Tab tab = new Tab();
            List<NaturalGas> naturalGasList = populateNaturalGasData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName().toLowerCase(), naturalGasList);

        } else if (sheet.getSheetName().toLowerCase().equalsIgnoreCase(LngData.SUPPLYDEMAND.toString())) {
            Tab tab = new Tab();
            List<SupplyDemand> supplyDemandList = populateSupplyDemandData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName(), supplyDemandList);
        } else if (sheet.getSheetName().equalsIgnoreCase(LngData.CONTRACTS.toString())) {
            Tab tab = new Tab();
            List<Contracts> contractsList = populateContractsData(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName(), contractsList);
        } else if (sheet.getSheetName().equalsIgnoreCase(LngData.PRODUCTION_COMPANY_OILGAS.toString())) {
            Tab tab = new Tab();
            List<CompanyOilGas> companyOilGasList = populateProductionCompanyOilGas(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName(), companyOilGasList);
        } else if (sheet.getSheetName().equalsIgnoreCase(LngData.SMALLSCALELNG.toString())) {
            Tab tab = new Tab();
            List<SmallScaleLng> smallScaleLngList = populateSmallScaleLng(sheet, tab);
            if (tab != null && tab.getTotalRecords() > 0)
                tabsList.add(tab);
            populatedData.put(sheet.getSheetName(), smallScaleLngList);
        }
    }

    return populatedData;
}

From source file:com.lulu.ofarm.test.ImportControllerTest.java

@Test
public void getBeanFromExcel() throws FileNotFoundException, IOException {
    //1.Excel  //from  w  w w  . ja v a2 s . c o m
    //      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));  

    //2.Excel  
    Workbook wb = new HSSFWorkbook(new FileInputStream("F:/reposibility_new.xls"));
    //3.Excel  
    Sheet sheet = wb.getSheetAt(0);
    //  
    int trLength = sheet.getLastRowNum();
    //4.Excel  
    Row row = sheet.getRow(0);
    //  
    int tdLength = row.getLastCellNum();
    //5.Excel?  
    Cell cell = row.getCell((short) 1);
    //6.??  
    //CellStyle cellStyle = cell.getCellStyle();  
    for (int i = 2; i < trLength; i++) {
        //Excel  
        Row row1 = sheet.getRow(i);

        for (int j = 0; j < tdLength; j++) {
            Cell cell1 = row1.getCell(j);
            /** 
             * ?ExcelCannot get a text value from a numeric cell 
             * ?String? 
             */
            if (cell1 != null) {
                cell1.setCellType(Cell.CELL_TYPE_STRING);
            }
            System.out.println(cell1.getStringCellValue());
        }

        //Excel?  

        //?  
        //      OutfallPolluateResourceBean resource = new OutfallPolluateResourceBean();
        //      resource.setRivername(row1.getCell(1).getStringCellValue());
        //      resource.setArea(row1.getCell(2).getStringCellValue());
        //      resource.setLeftorrightbank(row1.getCell(3).getStringCellValue());
        //      resource.setOutfalltype(row1.getCell(4).getStringCellValue());
        //      resource.setOutfallcode(row1.getCell(5).getStringCellValue());
        //      resource.setSecondaryunit(row1.getCell(6).getStringCellValue());
        //      resource.setStreetname(row1.getCell(7).getStringCellValue());
        //      resource.setStreetmanager(row1.getCell(8).getStringCellValue());
        //      resource.setVillage(row1.getCell(9).getStringCellValue());
        //      resource.setVillagemanager(row1.getCell(10).getStringCellValue());
        //      resource.setPosition(row1.getCell(11).getStringCellValue());
        //      resource.setCoordinate(row1.getCell(12).getStringCellValue());
        //      resource.setOutfallsize(row1.getCell(13).getStringCellValue());
        //      resource.setOutfallshape(row1.getCell(14).getStringCellValue());
        //      resource.setPolldescription(row1.getCell(15).getStringCellValue());
        //      
        //      resource.setRectificationmeasures(row1.getCell(16).getStringCellValue());
        //      resource.setDrainageTo(row1.getCell(17).getStringCellValue());
        //      resource.setTherectificationresponsibilityunit(row1.getCell(18).getStringCellValue());
        //      resource.setTimeofcompletion(row1.getCell(19).getStringCellValue());
        //      resource.setRemark(row1.getCell(20).getStringCellValue());
        //      System.err.println(resource);
        //      service.save(resource);

        //       PollutantSourceBean source = new PollutantSourceBean();
        //      source.setRivername(row1.getCell(1).getStringCellValue());
        //      source.setArea(row1.getCell(2).getStringCellValue());
        //      source.setPollsourcename(row1.getCell(3).getStringCellValue());
        //      source.setStreetname(row1.getCell(4).getStringCellValue());
        //      source.setStreetmanager(row1.getCell(5).getStringCellValue());
        //      source.setVillage(row1.getCell(6).getStringCellValue());
        //      source.setVillagemanager(row1.getCell(7).getStringCellValue());
        //      source.setPollsourcetype(row1.getCell(8).getStringCellValue());
        //      source.setOutfalltype(row1.getCell(9).getStringCellValue());
        //      source.setOutfallcode(row1.getCell(10).getStringCellValue());
        //      source.setPosition(row1.getCell(11).getStringCellValue());
        //      source.setCoordinate(row1.getCell(12).getStringCellValue());
        //      source.setPolldescription(row1.getCell(13).getStringCellValue());
        //      source.setDrainageto(row1.getCell(14).getStringCellValue());
        //      source.setPolldischarginglicense(row1.getCell(15).getStringCellValue());
        //      source.setDrainaglicense(row1.getCell(16).getStringCellValue());
        //      source.setHasmeasures(row1.getCell(17).getStringCellValue());
        //      source.setRectificationmeasures(row1.getCell(18).getStringCellValue());
        //      source.setTherectificationresponsibilityunit(row1.getCell(19).getStringCellValue());
        //      source.setTimeofcompletion(row1.getCell(20).getStringCellValue());
        //      source.setRemark(row1.getCell(21).getStringCellValue());
        //      System.out.println(source);

        //      service.save(source);

    }
}

From source file:com.lw.common.utils.ExcelUtil.java

/**
 * ???//from w w w .ja v  a 2  s. c  om
 * @param <T>
 * @throws Exception
 */
public <T> void parseImportFile(InputStream inputStream, String fileName, List<T> modelList,
        Class<T> modelClass, Map<String, String> columnMap) throws Exception {
    Workbook workbook = null;
    // ????Workbook??HSSFWorkbookXSSFWorkbook
    if (fileName.endsWith(SUFFIX_XLS)) {
        workbook = new HSSFWorkbook(inputStream);
    } else if (fileName.endsWith(SUFFIX_XLSX)) {
        workbook = new XSSFWorkbook(inputStream);
    }

    Sheet sheet = workbook.getSheetAt(0);
    if (sheet != null) {
        parseSheet(sheet, modelList, modelClass, columnMap);
    }
}