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:functions.excels.ImportExcel.java

License:Apache License

/**
 * Crer une nouvelle instance d'importExcel pour grer l'import
 * de tmoignages./*  w  w  w  . ja  v a  2s  .  c o m*/
 * @param fis
 * @throws InvalidFormatException
 * @throws IOException
 */
public ImportExcel(FileInputStream fis) throws InvalidFormatException, IOException {
    errorReport = new StringBuilder();
    wb = WorkbookFactory.create(fis);
    sheet = wb.getSheetAt(0);
}

From source file:functions.excels.ImportExcelEdit.java

License:Apache License

/**
 * Crer une nouvelle instance d'importExcel pour grer l'import
 * de tmoignages.// w ww .  j  a  va  2  s. com
 * @param fis
 * @throws InvalidFormatException
 * @throws IOException
 */
public ImportExcelEdit(FileInputStream fis) throws InvalidFormatException, IOException {
    creationTime = Calendar.getInstance();
    errorReport = new StringBuilder();
    wb = WorkbookFactory.create(fis);
    sheet = wb.getSheetAt(0);
}

From source file:github.srlee309.lessWrongBookCreator.excelReader.SummaryFileReader.java

License:Open Source License

/**
 * Builds list of {@link BookSummarySection}s from a given excel file
 * @param file to use to extract the BookSummarySections
 * @return bookSummarySections/*from   ww w . j  a  va 2s  .c  o  m*/
 * @throws ExcelExtractionException if unable to read from the given file
 */
public ArrayList<BookSummarySection> getSummarySections(File file) throws ExcelExtractionException {
    if (file == null) {
        throw new ExcelExtractionException("Input file was null and cannot be read");
    } else {
        Workbook wb = null;
        ArrayList<BookSummarySection> bookSummarySections = null;
        try {
            wb = WorkbookFactory.create(file);
            bookSummarySections = getBookSummarySections(wb);
        } catch (FileNotFoundException e) {
            logger.error("", e);
            throw new ExcelExtractionException(file.getName() + " not found");
        } catch (IOException e) {
            logger.error("", e);
            throw new ExcelExtractionException("IOException reading from " + file.getName());
        } catch (EncryptedDocumentException e) {
            logger.error("", e);
            throw new ExcelExtractionException("EncryptedDocumentException reading from " + file.getName());
        } catch (InvalidFormatException e) {
            logger.error("", e);
            throw new ExcelExtractionException("InvalidFormatException reading from " + file.getName());
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException ex) {
                    logger.error("", ex);
                    throw new ExcelExtractionException(
                            "IOException with " + file.getName() + ". If the file is open, close it.");
                }
            }
        }

        return bookSummarySections;
    }
}

From source file:gob.dp.sid.registro.controller.ImportarController.java

private void importar(File archivo) {
    List<Object[]> listaObjetos = new ArrayList<>();
    try {/*from   w w  w. ja  v a  2s  .  c o  m*/
        wb = WorkbookFactory.create(new FileInputStream(archivo));
        Sheet hoja = wb.getSheetAt(0);
        Iterator filaIterator = hoja.rowIterator();
        int indiceFila = -1;
        while (filaIterator.hasNext()) {
            indiceFila++;
            Row fila = (Row) filaIterator.next();
            Iterator columnaIterator = fila.cellIterator();
            Object[] listaColumna = new Object[7];

            int indiceColumna = -1;
            while (columnaIterator.hasNext()) {
                indiceColumna++;
                Cell celda = (Cell) columnaIterator.next();
                if (indiceFila == 0) {

                } else {
                    if (celda != null && indiceColumna < 7) {
                        switch (celda.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            //listaColumna[indiceColumna]= (int)Math.round(celda.getNumericCellValue());
                            listaColumna[indiceColumna] = celda.getDateCellValue();
                            break;
                        case Cell.CELL_TYPE_STRING:
                            listaColumna[indiceColumna] = celda.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            listaColumna[indiceColumna] = celda.getBooleanCellValue();
                            break;
                        default:
                            listaColumna[indiceColumna] = null;
                            break;
                        }
                    }
                }
            }
            if (indiceFila != 0) {
                listaObjetos.add(listaColumna);
            }

        }
        cargarGestiones(listaObjetos);
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
        log.error("importar" + e);
    }
}

From source file:gov.nih.nci.evs.app.neopl.ExcelToCSV.java

License:Open Source License

private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream fis = null;/*  w  ww .ja  v a 2 s .  co m*/
    try {
        fis = new FileInputStream(file);
        this.workbook = WorkbookFactory.create(fis);
        this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter(true);
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:gov.nih.nci.evs.app.neopl.XLSXMetadataUtils.java

License:Open Source License

public static boolean freezeRow(String filename, int sheetNumber, int rowNum) {
    FileOutputStream fileOut = null;
    boolean status = false;
    try {//from  w  w  w .ja v  a  2s . c om
        InputStream inp = new FileInputStream(filename);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetNumber);
        sheet.createFreezePane(0, rowNum); // this will freeze first rowNum rows
        fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        status = true;
        System.out.println("File modified " + filename);

    } catch (Exception ex) {
        //ex.printStackTrace();
        System.out.println("ERROR: freezeRow " + filename);

    } finally {
        try {
            fileOut.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    return status;
}

From source file:gov.nij.er.ui.EntityResolutionDemo.java

License:Apache License

private void loadExcelData(File file) throws Exception {

    LOG.debug("Loading Excel data file " + file.getAbsolutePath());

    InputStream inp = new FileInputStream(file);
    Workbook wb = WorkbookFactory.create(inp);

    // note that we read all the data out of the spreadsheet first, then
    // update the models. this way if there is
    // an error, we don't wipe out what the user already has.

    Sheet sheet = wb.getSheetAt(0);//from  www.  j  a v a 2s . c  om
    Row parametersRow = sheet.getRow(0);
    List<String> parameterNames = new ArrayList<String>();
    for (Cell cell : parametersRow) {
        String v = cell.getStringCellValue();
        if (parameterNames.contains(v)) {
            error("Duplicate field: " + v);
            return;
        }
        parameterNames.add(v);
        LOG.debug("Adding parameter " + v);
    }

    int parameterCount = parameterNames.size();

    LOG.debug("Excel loading read " + parameterCount + " parameters");

    List<ExternallyIdentifiableRecord> records = new ArrayList<ExternallyIdentifiableRecord>();

    int rowCount = sheet.getLastRowNum();
    LOG.debug("Loading " + (rowCount - 1) + " rows from " + sheet.getSheetName());

    int digits = (int) (Math.floor(Math.log10(rowCount)) + 1);

    DataFormatter dataFormatter = new DataFormatter();

    for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
        List<Attribute> attributes = new ArrayList<Attribute>(parameterCount);
        Row row = sheet.getRow(rowIndex);
        for (int i = 0; i < parameterCount; i++) {
            Cell cell = row.getCell(i);
            String v = dataFormatter.formatCellValue(cell);
            String parameterName = parameterNames.get(attributes.size());
            attributes.add(new Attribute(parameterName, v));
            // LOG.debug("Adding attribute, name=" + parameterName + ", v="
            // + (v==null ? "null" : "'" + v + "'"));
        }
        records.add(new ExternallyIdentifiableRecord(makeAttributes(attributes.toArray(new Attribute[] {})),
                String.format("%0" + digits + "d", rowIndex)));
    }

    LOG.debug("Read " + records.size() + " records from Excel");

    List<RecordWrapper> recordWrappers = EntityResolutionConversionUtils.convertRecords(records);
    rawDataTreeModel.init(recordWrappers);

    parametersTableModel.clear();
    parametersTableModel.addParameters(parameterNames);

}

From source file:gov.opm.scrd.services.impl.DeductionCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate deduction_table.xls based on deduction rates queried from database.
 *//* w w w .j av  a2s  .co  m*/
private void generateDeductionTable() throws OPMConfigurationException {
    OutputStream templateOutput = null;
    try {
        int currentRow = this.deductionTableTemplate.getStartCellRow();
        int startColumn = this.deductionTableTemplate.getStartCellColumn();
        DateFormat df = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.deductionTableTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        // Query deduction rates
        TypedQuery<DeductionRate> query = entityManager.createQuery(
                "SELECT r FROM DeductionRate r JOIN FETCH r.retirementType WHERE r.deleted = false ORDER BY r.id",
                DeductionRate.class);

        for (DeductionRate rate : query.getResultList()) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Service Type column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getServiceType());
            // Retirement Type column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getRetirementType().getName());
            // Date range column
            cell = row.getCell(startColumn + 2);
            if (cell == null) {
                cell = row.createCell(startColumn + 2);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()));
            // Date range column
            cell = row.getCell(startColumn + 3);
            if (cell == null) {
                cell = row.createCell(startColumn + 3);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(
                    df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()) + "," + rate.getRate());
        }
        templateOutput = new FileOutputStream(this.deductionTableTemplate.getDecisionTableFile());
        workbook.write(templateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate deduction rates decision table.", ex);
    } finally {
        if (templateOutput != null) {
            try {
                templateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:gov.opm.scrd.services.impl.InterestCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate interest tables.//from   w  ww  .j  ava2s  . c o  m
 * @throws OPMConfigurationException
 */
private void generateInterestTables() throws OPMConfigurationException {
    OutputStream csrsInterestTemplateOutput = null;
    OutputStream csrsPeaceCorpsInterestTemplateOutput = null;
    OutputStream csrsRedepositInterestTemplateOutput = null;
    OutputStream fersInterestTemplateOutput = null;
    OutputStream fersPeaceCorpsInterestTemplateOutput = null;
    OutputStream fersRedepositInterestTemplateOutput = null;
    try {
        // Query interest rates
        TypedQuery<InterestRate> query = entityManager.createQuery(
                "SELECT r FROM InterestRate r WHERE r.deleted = false ORDER BY r.interestYear",
                InterestRate.class);
        List<InterestRate> interestRates = query.getResultList();

        // csrs_interest.xls
        int currentRow = this.csrsInterestTemplate.getStartCellRow();
        int startColumn = this.csrsInterestTemplate.getStartCellColumn();

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsInterestTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsInterestTemplateOutput = new FileOutputStream(this.csrsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsInterestTemplateOutput);

        // csrs_peacecorps_interest.xls
        currentRow = this.csrsPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.csrsPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        csrsPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.csrsPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsPeaceCorpsInterestTemplateOutput);

        // csrs_redeposit_interest.xls
        currentRow = this.csrsRedepositInterestTemplate.getStartCellRow();
        startColumn = this.csrsRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsRedepositInterestTemplateOutput = new FileOutputStream(
                this.csrsRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(csrsRedepositInterestTemplateOutput);

        // fers_interest.xls
        currentRow = this.fersInterestTemplate.getStartCellRow();
        startColumn = this.fersInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory.create(new FileInputStream(this.fersInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersInterestTemplateOutput = new FileOutputStream(this.fersInterestTemplate.getDecisionTableFile());
        workbook.write(fersInterestTemplateOutput);

        // fers_peacecorps_interest.xls
        currentRow = this.fersPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.fersPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        fersPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.fersPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(fersPeaceCorpsInterestTemplateOutput);

        // fers_redeposit_interest.xls
        currentRow = this.fersRedepositInterestTemplate.getStartCellRow();
        startColumn = this.fersRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersRedepositInterestTemplateOutput = new FileOutputStream(
                this.fersRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(fersRedepositInterestTemplateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate interest rates decision table.", ex);
    } finally {
        if (csrsInterestTemplateOutput != null) {
            try {
                csrsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsPeaceCorpsInterestTemplateOutput != null) {
            try {
                csrsPeaceCorpsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsRedepositInterestTemplateOutput != null) {
            try {
                csrsRedepositInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersPeaceCorpsInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersRedepositInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:hrytsenko.gscripts.io.XlsFiles.java

License:Apache License

/**
 * Loads record from file./*from  www  .  j av a  2s .  com*/
 * 
 * @param args
 *            the named arguments.
 * 
 * @return the list of records.
 * 
 * @throws IOException
 *             if file could not be loaded.
 */
public static List<Map<String, String>> loadXls(Map<String, ?> args) {
    Path path = NamedArgs.findPath(args);
    LOGGER.info("Load {}.", path.getFileName());

    try (InputStream stream = Files.newInputStream(path); Workbook workbook = WorkbookFactory.create(stream);) {
        Sheet sheet = workbook.getSheetAt(0);
        List<Row> rows = ImmutableList.copyOf(sheet.rowIterator());
        if (rows.isEmpty()) {
            return Collections.emptyList();
        }

        List<String> columns = rows.stream().findFirst().map(XlsFiles::cellValues)
                .orElseThrow(() -> new IllegalStateException("Cannot read columns."));

        return rows.stream().skip(1).map(toRecord(columns)).collect(Collectors.toList());
    } catch (Exception exception) {
        throw new AppException(String.format("Could not load file %s.", path.getFileName()), exception);
    }
}