Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException 

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

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 {/* w  ww.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   w w  w  . j  a va  2 s  . 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.jav a2 s. c  om*/
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.XLSHandler.java

License:Open Source License

/**
 * //from ww w . ja  v  a2  s  . c  o m
 * @param file
 *            name of the file with path available onto the disk
 * @return instance of ss.usermodel.Workbook which is either HSSFWorkbook or
 *         XSSFWorkbook instance
 */
public static Workbook getXLSHandler(String file) {
    Workbook wb = null;
    try {
        InputStream is = XLSHandler.getStream(file);

        try {
            wb = WorkbookFactory.create(is);
        } catch (Exception e) {
            Spit.out(e);
        }

        is.close();
    } catch (Exception e) {
        Spit.out(e);
    }
    return wb;
}

---

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

License:Open Source License

/**
 * /*from w w  w . j a  v  a  2  s. co  m*/
 * @param inputStream
 * @return instance of ss.usermodel.Workbook which is either HSSFWorkbook or
 *         XSSFWorkbook instance
 * @throws IOException
 * @throws InvalidFormatException
 */

public static Workbook getXLSHandler(InputStream inputStream) throws IOException, InvalidFormatException {
    if (inputStream == null) {
        Spit.out("Parameter(InputStream inputStream) being supplied in getXLSXHandler() cant be null");
        return null;
    }

    Workbook wb;
    try {
        wb = WorkbookFactory.create(inputStream);
    } catch (InvalidFormatException e) {
        Spit.out(e);
        return null;
    } catch (IOException e) {
        Spit.out(e);
        return null;
    }
    return wb;

}

---

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

License:Open Source License

/**
 * //from w w w.j a  v  a  2  s . 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 w w . j av  a 2s. c o m
 * 
 * @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 w w .  ja  v a  2  s. c  o  m
 * @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 {/*from  www  .  ja v a  2s  . 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.faizod.aem.component.core.servlets.datasources.impl.ExcelDatasourceParser.java

License:Apache License

@Override
public boolean validate(InputStream inputStream) {
    if (inputStream == null) {
        return false;
    }//from   ww  w .  j  a  v a2s.  c om

    boolean valid = true;
    try {
        Workbook workbook = WorkbookFactory.create(inputStream);
        int firstVisibleTab = workbook.getFirstVisibleTab();
        valid = firstVisibleTab >= 0;
    } catch (IOException e) {
        valid = false;
    } catch (InvalidFormatException e) {
        valid = false;
    } catch (EncryptedDocumentException e) {
        valid = false;
    }
    return valid;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »