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

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. 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.streamsets.pipeline.lib.parser.excel.WorkbookParserFactory.java

License:Apache License

private Workbook open(InputStream is) throws DataParserException {
    try {/*from   w  ww . java2 s  . c  o m*/
        return WorkbookFactory.create(is);
    } catch (IOException e) {
        throw new DataParserException(Errors.EXCEL_PARSER_01, e);
    } catch (InvalidFormatException e) {
        throw new DataParserException(Errors.EXCEL_PARSER_02, e);
    } catch (EncryptedDocumentException e) {
        throw new DataParserException(Errors.EXCEL_PARSER_03, e);
    }
}

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

public PoiExcelReader(String filename, ReaderRowMapper<T> mapper) throws IOException {
    is = new FileInputStream(filename);
    Workbook wb;/*from  ww w.j  a  va2s.  co m*/
    try {
        wb = WorkbookFactory.create(is);
    } catch (InvalidFormatException e) {
        throw new IOException(e);
    }
    currentSheet = wb.getSheetAt(0);
    this.rowMapper = mapper;
    this.skipLines = DEFAULT_SKIP_LINES;
}

From source file:com.teeznar.poi.test.TestPoi.java

License:Open Source License

public void test() {
    InputStream inp;/*from  w ww .  j  a va  2  s  . co m*/
    try {
        inp = new FileInputStream("sample-file.xlsx");

        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row arow : sheet) {

            for (Cell acell : arow) {
                System.out.print(" cell: " + acell);
            }
            System.out.println("\n");
        }

        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);

        System.out.println(cell.toString());

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Reloads the Spreadsheet component from the given file.
 *
 * @param spreadsheet//w w w .j av a  2 s. c  om
 *            Target Spreadsheet
 * @param spreadsheetFile
 *            Source file. Should be of XLS or XLSX format.
 * @throws IOException
 *             If file has invalid format
 */
static void reloadSpreadsheetComponent(Spreadsheet spreadsheet, final File spreadsheetFile) throws IOException {
    try {
        Workbook workbook = WorkbookFactory.create(spreadsheetFile);
        reloadSpreadsheetComponent(spreadsheet, workbook);
    } catch (POIXMLException e) {
        throw new IOException(e);
    } catch (InvalidFormatException e) {
        throw new IOException("Invalid file format.", e);
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Reloads the Spreadsheet component from the given InputStream.
 *
 * @param spreadsheet/*from  w  ww .j a  va  2s. com*/
 *            Target Spreadsheet
 * @param inputStream
 *            Source stream. Stream content be of XLS or XLSX format.
 * @throws IOException
 *             If data in the stream has invalid format
 */
static void reloadSpreadsheetComponent(Spreadsheet spreadsheet, final InputStream inputStream)
        throws IOException {
    try {
        reloadSpreadsheetComponent(spreadsheet, WorkbookFactory.create(inputStream));
    } catch (InvalidFormatException e) {
        throw new IOException("Invalid file format.", e);
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Writes the current Workbook state from the given Spreadsheet to the given
 * file./*from   w  ww.java 2s.  c o  m*/
 *
 * @param spreadsheet
 *            Source Spreadsheet
 * @param fileName
 *            Target file name
 * @return File handle to the written file
 * @throws FileNotFoundException
 *             If file was not found
 * @throws IOException
 *             If some other IO error happened
 */
static File write(Spreadsheet spreadsheet, String fileName) throws FileNotFoundException, IOException {
    final Workbook workbook = spreadsheet.getWorkbook();
    if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
        if (workbook instanceof HSSFWorkbook) {
            fileName += ".xls";
        } else {
            fileName += ".xlsx";
        }
    }
    final File file = new File(fileName);
    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(file);
        workbook.write(fos);
        fos.close();
        if (workbook instanceof SXSSFWorkbook) {
            ((SXSSFWorkbook) workbook).dispose();
        }
    } catch (Exception e) {
        LOGGER.log(Level.WARNING, e.getMessage(), e);
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
    try {
        Workbook wb = WorkbookFactory.create(file);
        spreadsheet.setInternalWorkbook(wb);
    } catch (InvalidFormatException e) {
        LOGGER.log(Level.WARNING, e.getMessage(), e);
    }
    return file;
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

/**
 *
 * @param file//ww w. j a va2s .c om
 * @return
 * @throws FileNotFoundException
 * @throws InvalidFormatException
 * @throws IOException
 */
/*this method convert a multipart file to json object */
@Override
public JSONObject convert(MultipartFile file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();
            // System.out.println(cell.getCellType());
            //           cells.put(cell.getDateCellValue());
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cells.put(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else {
                    cells.put(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cells.put(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cells.put(cell.getCellFormula());
                break;
            default:
                System.out.println();
            }
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

@Override
public JSONObject convert(String file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    byte[] data = Base64.decodeBase64(file);
    FileOutputStream fos = new FileOutputStream(filez);
    fos.write(data);//from   w ww.j a v a 2  s  .  co m
    fos.close();
    //file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();

            if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else
                    cells.put(cell.getNumericCellValue());
            } else
                cells.put(cell.getStringCellValue());
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

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);/*from w w  w  .java  2  s.c om*/
    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.virtusa.isq.vtaf.runtime.SeleniumTestBase.java

License:Apache License

/**
 * Adds the values from excel.//from  w w w  .  j a va  2  s . c o m
 * 
 * @param path
 *            the path
 * @param index
 *            the index
 * @return the string[][]
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws InvalidFormatException
 *             the invalid format exception
 */
public final String[][] addValuesFromExcel(final String path, final String index)
        throws IOException, InvalidFormatException {

    String cellStringValue = null;
    double cellDoubleValue = 0;
    Boolean cellBooleanValue;
    byte cellErrorValue = 0;
    String[][] arrExcelContent;
    FileInputStream file = null;
    Workbook workbook = null;

    Sheet sheet = null;
    try {
        file = new FileInputStream(new File(path));
        workbook = WorkbookFactory.create(file);
        sheet = workbook.getSheetAt(Integer.parseInt(index));
        Iterator<Row> rowIterator = sheet.iterator();
        arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][];
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNumber = row.getRowNum();
            Iterator<Cell> cellIterator = row.cellIterator();
            arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()];
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int cellNumber = cell.getColumnIndex();
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    cellStringValue = cell.getStringCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    cellBooleanValue = cell.getBooleanCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString();
                } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    cellErrorValue = cell.getErrorCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue);
                } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    cellStringValue = cell.getCellFormula();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;

                } else {

                    cellDoubleValue = cell.getNumericCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue);
                }
            }

        }
    } finally {
        if (((InputStream) workbook) != null) {
            ((InputStream) workbook).close();
        }
    }
    return arrExcelContent;
}