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.epitech.oliver_f.astextexls.ReadXLSFiles.java

private List<ResultRow> parseAllFiles(List<Path> paths) {
    List<ResultRow> resultList = new ArrayList<ResultRow>();
    for (Path path : paths) {
        try {//from www . j  a  v a2 s.  c o  m
            System.out.println("file : " + path.toAbsolutePath());
            FileInputStream file = new FileInputStream(path.toFile());
            Workbook wb = WorkbookFactory.create(file);
            Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            boolean found = false;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    String res = null;
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        double inte = cell.getNumericCellValue();
                        res = Double.toString(inte);
                    }
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        res = cell.getStringCellValue();
                    }
                    if (res != null && res.trim().toLowerCase().equals("login \nvaluateur")) {
                        found = true;
                    }
                }
                if (found) {
                    System.out.println("found ! ");
                    ResultRow rr = new ResultRow();
                    Row rowFound = rowIterator.next();
                    Iterator<Cell> c = rowFound.cellIterator();
                    while (c.hasNext()) {
                        Cell cel = c.next();
                        String res = null;
                        if (cel.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            double inte = cel.getNumericCellValue();
                            res = Double.toString(inte);
                        }
                        if (cel.getCellType() == Cell.CELL_TYPE_STRING) {
                            res = cel.getStringCellValue();
                        }
                        rr.result.add(res);
                    }
                    resultList.add(rr);
                    found = false;
                    break;
                }
            }
            file.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }
    return resultList;
}

From source file:com.epitech.oliver_f.astextexls.WriteXLSFile.java

public void write() {
    FileInputStream file = null;//from  www.  jav  a 2s. c o m
    try {
        file = new FileInputStream(pathToFile);
        Workbook wb = WorkbookFactory.create(file);
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 0;
        int listIndex = 0;
        while (rowIterator.hasNext() && listIndex < results.size()) {
            Row row = rowIterator.next();
            if (i > 1) {
                Iterator<Cell> cellIterator = row.cellIterator();
                int cellIndex = 0;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String r = results.get(listIndex).result.get(cellIndex);
                    try {
                        if (r == null)
                            throw new NumberFormatException();
                        Double resDouble = Double.parseDouble(r);
                        Integer resInt = resDouble.intValue();
                        cell.setCellValue(resInt.toString());
                    } catch (NumberFormatException e) {
                        cell.setCellValue(results.get(listIndex).result.get(cellIndex));
                    }
                    cellIndex++;
                }
                listIndex++;
            }
            i++;
        }
        System.out.println("listindex " + listIndex);
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File(pathToFile));
        wb.write(outFile);
        outFile.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.eurodyn.qlack2.fuse.lexicon.impl.LanguageServiceImpl.java

License:EUPL

@Override
@Transactional(TxType.REQUIRED)//from w w w .  j a va 2s .c  o  m
public void uploadLanguage(String languageID, byte[] lgXL) {
    Map<String, String> translations = new HashMap<>();
    try {
        Workbook wb = WorkbookFactory.create(new BufferedInputStream(new ByteArrayInputStream(lgXL)));
        for (int si = 0; si < wb.getNumberOfSheets(); si++) {
            Sheet sheet = wb.getSheetAt(si);
            String groupName = sheet.getSheetName();
            String groupID = null;
            if (StringUtils.isNotBlank(groupName)) {
                groupID = Group.findByName(groupName, em).getId();
            }
            // Skip first row (the header of the Excel file) and start
            // parsing translations.
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                String keyName = sheet.getRow(i).getCell(0).getStringCellValue();
                String keyValue = sheet.getRow(i).getCell(1).getStringCellValue();
                translations.put(keyName, keyValue);
            }
            keyService.updateTranslationsForLanguageByKeyName(languageID, groupID, translations);
        }
    } catch (IOException | InvalidFormatException ex) {
        // Convert to a runtime exception in order to roll back transaction
        LOGGER.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        throw new QLanguageProcessingException("Error reading Excel file for language " + languageID);
    }

}

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read an Microsoft Workbook in DataCollection dc
 * supplied along with workbook. Each sheet will be a grid in dc with the
 * same name as sheet name.//from   w  ww . j  av a  2  s  . co m
 * 
 * @param wb
 *            This is an instance of MS excel workbook(i.e .xls or .xlsx)
 *            created by POI WorkbookFactory.
 * @param dc
 */
public void readAWorkbook(Workbook wb, DataCollection dc) {
    if (wb == null || dc == null) {
        throw new IllegalArgumentException(XLSReader.ILLEGAL_ARGUMENT);
    }

    int nbrSheets = wb.getNumberOfSheets();
    String sheetName = null;
    String gridName = dc.getTextValue("gridName", null);
    Sheet sheet = null;

    int nbrColumns = -1;
    int nbrPhysicalRows = 0;

    for (int k = 0; k < nbrSheets; k++) {

        sheet = wb.getSheetAt(k);
        sheetName = sheet.getSheetName();
        nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
        if (nbrPhysicalRows < 2) {
            Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
            // dc.addMessage(XLSReader.INSUFFICIENT_ROWS, sheetName +
            // XLSReader.INSUFFICIENT_DATA_ROWS);
            continue;
        }

        try {
            nbrColumns = this.readASheet(sheet);
            /**
             * swallow all the exceptions during excel sheet reading and put
             * appropriate message. While reading excel following exceptions
             * can come: 1. IllegalStateExcetion if column data type
             * mismatch in excel sheet. 2. ExilityException etc.
             */
        } catch (ExilityException e) {
            String msg = this.replaceMessageParams(XLSReader.EXCEPTION_MSG,
                    new String[] { sheetName, e.getMessage() });
            dc.addError(msg);
            Spit.out(e);
        }

        if (nbrColumns == -1) {
            continue;
        }

        /**
         * This is for little more flexibility to user if they have only one
         * sheet to be read and has supplied a gridName along with service
         * then let set first sheet one as given gridName(In case of simple
         * file upload and read content as grid)
         */
        if (gridName != null) {
            sheetName = gridName;
            gridName = null;
        }

        dc.addGrid(sheetName, this.getGrid());
        Spit.out(sheetName + " added to dc with " + this.rows.size() + " row(s)");
        this.columnsData.clear();
        this.rows.clear();

        // this.printXlSRec(dc.getGrid(sheetName).getRawData());

    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/**
 * /*from w  w w  .jav a  2s  .c  o m*/
 * @param inputStream
 * @param dc
 * @return
 */
private List<Sheet> getSheets(InputStream inputStream, DataCollection dc) {
    List<Sheet> sheets = new ArrayList<Sheet>();
    Workbook workbook = null;
    boolean valuesSheetFound = false;
    try {
        workbook = WorkbookFactory.create(inputStream);
        int n = workbook.getNumberOfSheets();
        for (int i = 0; i < n; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int nbrRows = sheet.getPhysicalNumberOfRows();
            String sheetName = sheet.getSheetName();
            if (nbrRows > 0) {
                sheets.add(sheet);

                if (!valuesSheetFound && sheetName.equals(CommonFieldNames.VALUES_TABLE_NAME)) {
                    /*
                     * this is supposed to be the first one. swap it if
                     * required
                     */
                    if (i != 0) {
                        sheets.add(i, sheets.get(0));
                        sheets.add(0, sheet);
                    }
                    valuesSheetFound = true;
                }
            }

        }
    } catch (Exception e) {
        String msg = "Error while reading spread sheet. " + e.getMessage();
        Spit.out(msg);
        if (dc != null) {
            dc.addError(msg);
        }
    }
    return sheets;
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/**
 * Very specific requirement for saving labels. If the file exists, append
 * only missing labels/*from   w  ww. j a va 2s  . c om*/
 * 
 * @param fileName
 * @param rows
 * @return true if we are able to save the file
 */
public boolean appendMissingOnes(String fileName, String[][] rows) {
    File file = new File(fileName);
    Workbook workbook;
    Sheet sheet;
    if (file.exists()) {
        /**
         * read spreadsheet
         */
        try {
            InputStream is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            is.close();
            Spit.out(fileName + " read into a workbook.");
        } catch (Exception e) {
            Spit.out(fileName + " is not saved because of an error while reading existing contents. "
                    + e.getMessage());
            Spit.out(e);
            return false;
        }
        sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            sheet = workbook.createSheet();
        }

    } else {
        Spit.out(fileName + " does not exist. New file will be created.");
        /**
         * first time this is being saved.
         */
        workbook = this.getWorkbookForFile(fileName);
        sheet = workbook.createSheet();
    }
    if (sheet.getLastRowNum() > 0) {
        this.addMissingRows(sheet, rows);
    } else {
        this.addRows(sheet, rows);
    }
    return this.save(workbook, fileName);
}

From source file:com.eyeq.pivot4j.export.poi.ExcelExporterIT.java

License:Common Public License

/**
 * @param format/*from  w  ww  . jav a  2 s.  c om*/
 * @param showParentMember
 * @param showDimensionTitle
 * @param hideSpans
 * @param rows
 * @param mergedRegions
 * @throws IOException
 * @throws InvalidFormatException
 */
protected void testExport(Format format, boolean showParentMember, boolean showDimensionTitle,
        boolean hideSpans, int rows, int mergedRegions) throws IOException, InvalidFormatException {
    OutputStream out = null;

    File file = File.createTempFile("pivot4j-", "." + format.getExtension());

    if (deleteTestFile) {
        file.deleteOnExit();
    }

    try {
        out = new FileOutputStream(file);
        ExcelExporter exporter = new ExcelExporter(out);

        exporter.setFormat(format);
        exporter.setShowParentMembers(showParentMember);
        exporter.setShowDimensionTitle(showDimensionTitle);
        exporter.setHideSpans(hideSpans);

        exporter.render(getPivotModel());
    } finally {
        out.flush();
        IOUtils.closeQuietly(out);
    }

    Workbook workbook = WorkbookFactory.create(file);

    assertThat("Workbook cannot be null.", workbook, is(notNullValue()));

    Sheet sheet = workbook.getSheetAt(0);
    assertThat("Worksheet cannot be null.", sheet, is(notNullValue()));

    assertThat("Invalid worksheet name.", sheet.getSheetName(), is(equalTo("Sales")));

    assertThat("Wrong number of rows.", sheet.getLastRowNum(), is(equalTo(rows)));
    assertThat("Wrong number of merged regions.", sheet.getNumMergedRegions(), is(equalTo(mergedRegions)));
}

From source file:com.faizod.aem.component.core.servlets.datasources.impl.ExcelDatasourceParser.java

License:Apache License

@Override
public Map<Object, List<Object>> parseMultiColumn(InputStream inputStream) {
    Map<Object, List<Object>> map = new LinkedHashMap<Object, List<Object>>();

    // read in the Excel file
    try {/*  ww w. j  av a2s . c  om*/
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rows = sheet.iterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            List<Cell> cells = new ArrayList<Cell>();
            short lineMin = row.getFirstCellNum();
            short lineMax = row.getLastCellNum();

            for (short index = lineMin; index < lineMax; index++)
                cells.add(row.getCell(index));

            Object label = "";
            switch (cells.get(0).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                label = cells.get(0).getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                label = "" + (cells.get(0).getStringCellValue());
                break;
            default:
                break;
            }

            List<Object> values = new ArrayList<Object>();

            for (short index = 1; index < (lineMax - lineMin); index++) {
                Object value;

                switch (cells.get(index).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cells.get(index).getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = cells.get(index).getNumericCellValue();
                    break;
                default:
                    value = new Object();
                    break;
                }
                values.add(value);
            }
            map.put(label, values);
        }
    } catch (IOException e) {
        LOG.error("Unable to read datasource.", e);
        throw new DatasourceException("Unable to read datasource.", e);
    } catch (InvalidFormatException e) {
        LOG.error("File Format not supported.", e);
        throw new DatasourceException("File Format not supported.", e);
    }
    return map;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<Xtmpinddl> analizeXlsIndi(UploadedFile file, final DcsUsuario usuario, List<DcsCatPais> paises,
        List<DcsCatIndicadores> indicadores) throws DCSException {
    Workbook excelXLS = null;
    List<Xtmpinddl> listaCarga = null;
    try {/*from   w  w  w  . j  av a 2  s.  c o m*/
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetIndi(rowIterator, usuario, sheet.getSheetName(), paises,
                        indicadores);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<XtmpinddlFlota> analizeXlsFlota(UploadedFile file, final DcsUsuario usuario,
        List<DcsCatPais> paises) throws DCSException {
    Workbook excelXLS = null;
    List<XtmpinddlFlota> listaCarga = null;
    try {/*  ww w.j  av  a 2  s  .c  om*/
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetFlota(rowIterator, usuario, sheet.getSheetName(), paises);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}