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.alibaba.ims.platform.util.ExcelUtil.java

License:Open Source License

private static Workbook createWorkbook(InputStream inputStream) {
    try {/*from  w  w  w.  j a v  a2 s  .  c o  m*/
        return WorkbookFactory.create(inputStream);
    } catch (Exception e) {
        logger.error("Read workbook from inputStream error.", e);
        return null;
    }
}

From source file:com.AllenBarr.CallSheetGenerator.CallSheetGeneratorControllerController.java

License:Open Source License

private void populateContributorList() {
    if (excelSheet.exists()) {
        try {//w  ww  . j  av  a 2 s .  co m
            wb = WorkbookFactory.create(excelSheet);
            wbSheet = wb.getSheetAt(0);
            Row wbRow = wbSheet.getRow(0);
            Integer vanIDColumnIndex = 0;
            Integer fNameColumnIndex = 0;
            Integer lNameColumnIndex = 0;
            for (Cell cell : wbRow) {
                if (null != cell.getStringCellValue()) {
                    switch (cell.getStringCellValue()) {
                    case "VANID":
                        vanIDColumnIndex = cell.getColumnIndex();
                        break;
                    case "LastName":
                        lNameColumnIndex = cell.getColumnIndex();
                        break;
                    case "FirstName":
                        fNameColumnIndex = cell.getColumnIndex();
                        break;
                    }
                }
            }
            final ObservableList<String> names = FXCollections.observableArrayList();
            for (Row row : wbSheet) {
                switch (row.getCell(vanIDColumnIndex).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    names.add(row.getCell(vanIDColumnIndex).getStringCellValue() + " "
                            + row.getCell(fNameColumnIndex).getStringCellValue() + " "
                            + row.getCell(lNameColumnIndex).getStringCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    names.add((int) row.getCell(vanIDColumnIndex).getNumericCellValue() + " "
                            + row.getCell(fNameColumnIndex).getStringCellValue() + " "
                            + row.getCell(lNameColumnIndex).getStringCellValue());
                    break;
                }
            }
            contributorSelector.setItems(names);
            contributorSelector.getSelectionModel().select(0);
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(CallSheetGeneratorControllerController.class.getName()).log(Level.SEVERE, null,
                    ex);
        }
    }
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public String getUniversity() throws Exception {

    String result = null;//from www .ja v a 2 s  .  c om

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(ROW_UNIVERSITY);
        Cell cell = row.getCell(COLUMN_UNIVERSITY);
        result = cell.getStringCellValue().split("-")[1].trim();
    } catch (Throwable ex) {
        String message = "  ?? ?  ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public List<User> getUsers() throws Exception {
    List<User> result = new ArrayList<>();
    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);//from w  ww . j av  a2s .  co  m

        int amountRows = sheet.getPhysicalNumberOfRows();

        for (int i = HEADER_HEIGHT; i < amountRows; i++) {
            User user = new User();

            Row row = sheet.getRow(i);
            Cell cell = row.getCell(COLUMN_FIO);
            String fio = cell.getStringCellValue().trim();
            if (fio != null && !fio.isEmpty()) {
                user.setFIO(fio);
                user.setRow(i);
                result.add(user);
            }

        }
    } catch (Throwable ex) {
        String message = "  ??   ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public void write(List<User> listUser) throws Exception {

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);// w w w.j  ava 2s  . co m

        for (User user : listUser) {

            int row = user.getRow();
            UserInfo info = user.getInfo();

            if (info != null) {
                Long amountLetters = info.getAMOUNT_LETTERS();
                Long hirsh = info.getHIRSH();
                Double impactPublish = info.getIMPACT_PUBLISH();

                if (amountLetters != null) {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS).setCellValue(amountLetters);
                } else {
                    sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }

                if (hirsh != null) {
                    sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(hirsh);
                } else {
                    sheet.getRow(row).getCell(COLUMN_HIRSH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }

                if (impactPublish != null) {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH).setCellValue(impactPublish);
                } else {
                    sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                            .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                }
            } else {
                sheet.getRow(row).getCell(COLUMN_AMOUNT_LETTERS)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_HIRSH).setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
                sheet.getRow(row).getCell(COLUMN_IMPACT_PUBLISH)
                        .setCellValue(DEFAULT_VALUE_FOR_NULL_INFO_PARAMETERS);
            }
        }

        try (FileOutputStream os = new FileOutputStream(filePath)) {
            wb.write(os);
        }
    } catch (Throwable ex) {
        String message = "  ?     ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelImportJob.java

License:Apache License

@Override
protected IStatus run(IProgressMonitor monitor) {

    logImportActivity(MessageFormat.format("Importing {0}s from excel file: {1}", getTerminologyName(),
            getImportFilePath()));/*from w w  w.  ja  v a  2  s  .c  om*/

    FileInputStream inputStream = null;
    final String fileName = getImportFilePath().substring(getImportFilePath().lastIndexOf(File.separator) + 1);

    try {
        inputStream = new FileInputStream(getImportFilePath());
        final Workbook workbook = WorkbookFactory.create(inputStream);

        initializeServices();

        final Set<Sheet> sheets = collectSheets(workbook);

        excelParser.parse(sheets, getPropertyIndex());
        getComponentsFromDatabase();
        validate();

        if (getTerminologyImportResult().hasValidationDefects()) {
            logImportActivity("Validation encountered one or more errors:");

            for (final TerminologyImportValidationDefect validationDefect : getTerminologyImportResult()
                    .getValidationDefects()) {
                logImportActivity(MessageFormat.format("Sheet name: {0}", validationDefect.getSheetName()));

                for (final Defect defect : validationDefect.getDefects()) {
                    logImportActivity(MessageFormat.format("Error: {0}", defect.getErrorMessage()));
                }
            }

            return Status.CANCEL_STATUS;
        }

        if (isImportTypeClear()) {
            clearDatabase();
        }

        final long lastCommitTime = getLastCommitTimeBeforeImport();
        latestSuccessfulCommitTime = lastCommitTime;

        commitSheets(fileName, sheets);

        final CDOCommitInfo commitInfo = CDOCommitInfoUtils.createEmptyCommitInfo(getRepositoryUuid(),
                getBranchPath(), getUserId(),
                String.format("Imported %ss from Excel file %s.", getTerminologyName(), fileName),
                getLatestSuccessfulCommitTime(), lastCommitTime);
        CDOServerUtils.sendCommitNotification(commitInfo);

        return Status.OK_STATUS;

    } catch (IOException e) {
        final String message = MessageFormat.format("Problem while reading file {0}", getImportFilePath());
        LOGGER.error(message, e);

        return new Status(IStatus.ERROR, "unknown", 1, message, e);
    } catch (InvalidFormatException e) {
        final String message = MessageFormat.format("Excel file is not valid: {0}", getImportFilePath());
        LOGGER.error(message, e);

        return new Status(IStatus.ERROR, "unknown", 1, message, e);
    } catch (SnowowlServiceException e) {
        final String message = MessageFormat.format("Error while committing {0}s", getTerminologyName());
        LOGGER.error(message, e);

        return new Status(IStatus.ERROR, "unknown", 1, message, e);
    } catch (Exception e) {
        final String message = MessageFormat.format("Error while importing {0}s", getTerminologyName());
        LOGGER.error(message, e);

        return new Status(IStatus.ERROR, "unknown", 1, message, e);
    } finally {
        if (null != getEditingContext()) {
            getEditingContext().close();
        }

        if (null != inputStream) {
            try {
                inputStream.close();
            } catch (IOException e) {
            }
        }

        monitor.done();
    }
}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

public void parse(int sheetNumber) throws SnowowlServiceException {
    fis = null;/* w w  w. j av  a  2s.co m*/
    try {
        fis = new FileInputStream(xlsFile);
        Workbook wb = WorkbookFactory.create(fis);
        Sheet sheet = wb.getSheetAt(sheetNumber);
        parse(sheet);
    } catch (final Exception e) {
        maxWidth = -1;
        throw new SnowowlServiceException(e);
    }
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private boolean processExcelFile(final SubsetEntry entry) throws InvalidFormatException, IOException {

    final FileInputStream inputStream = createFileInputStream(entry);
    final Workbook workbook = WorkbookFactory.create(inputStream);
    final List<Integer> list = getSheetAndFirstRowNumber(workbook, workbook.getNumberOfSheets());

    if (null != list) {
        final int sheetNumber = list.get(0);
        final int firstRowNumber = list.get(1);
        final Sheet sheet = workbook.getSheetAt(sheetNumber);
        final List<String> row = collectRowValues(sheet.getRow(firstRowNumber));

        entry.setHeadings(row);//from   ww w .j  a va  2s . com
        entry.setSheetNumber(sheetNumber);

        if (entry.isHasHeader()) {
            Optional<String> match = FluentIterable.from(row).firstMatch(new Predicate<String>() {
                @Override
                public boolean apply(String input) {
                    return input.contains("concept") && (input.contains("id") || input.contains("sctid"));
                }
            });
            entry.setIdColumnNumber(match.isPresent() ? row.indexOf(match.get()) : 0); // default to first?
        } else {
            for (int i = 0; i < row.size(); i++) {
                if (isConceptId(row.get(i).trim())) {
                    entry.setIdColumnNumber(i);
                }
            }
        }

        return true;
    } else {
        return false;
    }
}

From source file:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel//from  ww w.ja  va 2s .c  o m
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}

From source file:com.beyondb.io.ExcelControl.java

private org.apache.poi.ss.usermodel.Sheet getSheet() throws IOException, InvalidFormatException {
    org.apache.poi.ss.usermodel.Sheet sheet = null;

    try {/*from  w  ww . j a  v a 2s  .  c o m*/

        m_InputStream = new FileInputStream(m_File);

        if (!m_InputStream.markSupported()) {
            m_InputStream = new PushbackInputStream(m_InputStream, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
            POIFSFileSystem poifsfs = new POIFSFileSystem(m_InputStream);
            m_Workerbook = WorkbookFactory.create(poifsfs);

        } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
            m_Workerbook = WorkbookFactory.create(OPCPackage.open(m_File));
        } else {
            throw new IllegalArgumentException("excel????");
        }
        sheet = m_Workerbook.getSheetAt(0);
    } catch (FileNotFoundException ex) {

        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "EXCEL?", ex);
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "?EXCEL", ex);
        throw ex;
    }
    return sheet;
}