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:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

@Override
public void initReader(File inputFile) throws GDEMException {
    if (inputFile == null) {
        throw new GDEMException("Input file is missing");
    }/* w  w  w  .  ja v  a 2s . com*/
    try {
        if (!isExcel2007) {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile));
            wb = new HSSFWorkbook(fs);
        } else {
            OPCPackage p = OPCPackage.open(new FileInputStream(inputFile));
            wb = WorkbookFactory.create(p);
        }
    } catch (Exception e) {
        throw new GDEMException("ErrorConversionHandler - couldn't open Excel file: " + e.toString());
    }
    inputFileLength = inputFile.length();
    evaluator = wb.getCreationHelper().createFormulaEvaluator();

}

From source file:es.us.isa.jdataset.loader.ExcelToCSV.java

License:Apache License

/**
 * Open an Excel workbook ready for conversion.
 *
 * @param file An instance of the File class that encapsulates a handle
 *        to a valid Excel workbook. Note that the workbook can be in
 *        either binary (.xls) or SpreadsheetML (.xlsx) format.
 * @throws java.io.FileNotFoundException Thrown if the file cannot be located.
 * @throws java.io.IOException Thrown if a problem occurs in the file system.
 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
 *         if invalid xml is found whilst parsing an input SpreadsheetML
 *         file./*from  www.  jav  a 2 s .  com*/
 */
private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream fis = null;
    try {
        System.out.println("Opening workbook [" + file.getName() + "]");

        fis = new FileInputStream(file);

        // Open the workbook and then create the FormulaEvaluator and
        // DataFormatter instances that will be needed to, respectively,
        // force evaluation of forumlae found in cells and create a
        // formatted String encapsulating the cells contents.
        this.workbook = WorkbookFactory.create(fis);
        this.evaluator = this.getWorkbook().getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter();
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:es.us.isa.jdataset.loader.ExcelToCSV.java

License:Apache License

/**
 * Open an Excel workbook ready for conversion.
 *
 * @param file An instance of the File class that encapsulates a handle
 *        to a valid Excel workbook. Note that the workbook can be in
 *        either binary (.xls) or SpreadsheetML (.xlsx) format.
 * @throws java.io.FileNotFoundException Thrown if the file cannot be located.
 * @throws java.io.IOException Thrown if a problem occurs in the file system.
 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
 *         if invalid xml is found whilst parsing an input SpreadsheetML
 *         file.//from  w  w  w. j a v a  2 s. c om
 */
public void openWorkbook(InputStream fis) throws IOException, InvalidFormatException {
    try {
        System.out.println("Opening workbook [from input stream]");

        // Open the workbook and then create the FormulaEvaluator and
        // DataFormatter instances that will be needed to, respectively,
        // force evaluation of forumlae found in cells and create a
        // formatted String encapsulating the cells contents.
        this.workbook = WorkbookFactory.create(fis);
        this.evaluator = this.getWorkbook().getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter();
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:eu.esdihumboldt.hale.app.bgis.ade.common.AbstractAnalyseTable.java

License:Open Source License

/**
 * Load table to analyse from an Excel file.
 * /*from   ww  w . ja  va  2s .c om*/
 * @param location the file location
 * @throws Exception if an error occurs loading the file
 */
protected void analyse(URI location) throws Exception {
    InputStream inp = new BufferedInputStream(location.toURL().openStream());

    try {
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // the first row represents the header
        analyseHeader(sheet);

        // load configuration entries
        analyseContent(sheet);
    } finally {
        // reset evaluator reference
        evaluator = null;

        // unclear whether the POI API closes the stream
        inp.close();
    }
}

From source file:eu.esdihumboldt.hale.io.xls.AbstractAnalyseTable.java

License:Open Source License

/**
 * Load table to analyse from an Excel file.
 * //www.  j a v  a2s. c  o  m
 * @param location the file location
 * @param sheetNum number of the sheet that should be loaded (0-based)
 * @throws Exception if an error occurs loading the file
 */
protected void analyse(URI location, int sheetNum) throws Exception {
    InputStream inp = new BufferedInputStream(location.toURL().openStream());

    try {
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetNum);
        evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // the first row represents the header
        analyseHeader(sheet);

        // load configuration entries
        analyseContent(sheet);
    } finally {
        // reset evaluator reference
        evaluator = null;

        // unclear whether the POI API closes the stream
        inp.close();
    }
}

From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java

License:Open Source License

/**
 * Test - write data of complex schema and analyze result
 * //from   ww w .  j a  va 2 s .  c  o  m
 * @throws Exception , if an error occurs
 */
@Test
public void testWriteComplexSchema() throws Exception {

    TransformationExample example = TransformationExamples.getExample(TransformationExamples.SIMPLE_COMPLEX);
    // alternative the data could be generated by iterating through the
    // exempleproject's sourcedata
    List<String> header = Arrays.asList("id", "name", "details.age", "details.income", "details.address.street",
            "details.address.city");
    List<String> firstDataRow = Arrays.asList("id0", "name0", "age0", "income0", "street0", "city0");

    // set instances to xls instance writer
    XLSInstanceWriter writer = new XLSInstanceWriter();
    IContentType contentType = Platform.getContentTypeManager()
            .getContentType("eu.esdihumboldt.hale.io.xls.xls");
    writer.setParameter(InstanceTableIOConstants.SOLVE_NESTED_PROPERTIES, Value.of(true));

    File tmpFile = tmpFolder.newFile("excelTestWriteComplexSchema.xls");

    writer.setInstances(example.getSourceInstances());
    // write instances to a temporary XLS file
    writer.setTarget(new FileIOSupplier(tmpFile));
    writer.setContentType(contentType);
    IOReport report = writer.execute(null);
    assertTrue(report.isSuccess());

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

    checkHeader(sheet, header);

    checkSheetName(sheet, "person");

    checkFirstDataRow(sheet, firstDataRow);
}

From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java

License:Open Source License

/**
 * Test - write data of complex schema and analyze result The
 * implementation, this test based on, does not work correctly at the
 * moment.//  w  ww.ja  va2s.co  m
 * 
 * @throws Exception , if an error occurs
 */
@Test
public void testWriteNotNestedProperties() throws Exception {

    TransformationExample example = TransformationExamples.getExample(TransformationExamples.SIMPLE_COMPLEX);
    // alternative the data could be generated by iterating through the
    // exempleproject's sourcedata
    List<String> header = Arrays.asList("id", "name");
    List<String> firstDataRow = Arrays.asList("id0", "name0");

    // set instances to xls instance writer
    XLSInstanceWriter writer = new XLSInstanceWriter();
    IContentType contentType = Platform.getContentTypeManager()
            .getContentType("eu.esdihumboldt.hale.io.xls.xls");
    writer.setParameter(InstanceTableIOConstants.SOLVE_NESTED_PROPERTIES, Value.of(false));

    File tmpFile = tmpFolder.newFile("excelNotNestedProperties.xls");

    writer.setInstances(example.getSourceInstances());
    // write instances to a temporary XLS file
    writer.setTarget(new FileIOSupplier(tmpFile));
    writer.setContentType(contentType);
    IOReport report = writer.execute(null);
    assertTrue(report.isSuccess());

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

    checkHeader(sheet, header);

    checkSheetName(sheet, "person");

    checkFirstDataRow(sheet, firstDataRow);
}

From source file:eu.esdihumboldt.hale.io.xls.ui.XLSInstanceImportConfigurationPage.java

License:Open Source License

/**
 * @see eu.esdihumboldt.hale.ui.HaleWizardPage#onShowPage(boolean)
 *//*from ww  w  .j a v  a 2s . c o  m*/
@Override
protected void onShowPage(boolean firstShow) {

    if (!firstShow) {
        setErrorMessage(null);
    }

    try {
        Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput());
        int numberOfSheets = wb.getNumberOfSheets();
        String[] items = new String[numberOfSheets];
        for (int i = 0; i < numberOfSheets; i++) {
            items[i] = wb.getSheetAt(i).getSheetName();
        }
        sheetSelection.setItems(items);
    } catch (Exception e) {
        setErrorMessage("Cannot load Excel file!");
        setPageComplete(false);
        return;
    }
    super.onShowPage(firstShow);
    sheetSelection.select(0);
    setPageComplete(true);
}

From source file:eu.esdihumboldt.hale.io.xls.ui.XLSSchemaTypePage.java

License:Open Source License

@Override
protected void onShowPage(boolean firstShow) {

    URI newLocation = getWizard().getProvider().getSource().getLocation();
    if (!firstShow && newLocation != null && !newLocation.equals(oldLocation)) {
        sheetNum = 0;//  w w w  . java 2 s .co  m
    }

    try {
        Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput());

        int numberOfSheets = wb.getNumberOfSheets();
        if (sheetNum >= numberOfSheets) {
            sheetNum = 0;
        }
        ArrayList<String> items = new ArrayList<String>();
        for (int i = 0; i < numberOfSheets; i++) {
            items.add(wb.getSheetAt(i).getSheetName());
            // only add items if there is a header (no empty sheet)
            Row row = wb.getSheetAt(i).getRow(0);
            if (row == null && newLocation != null && !newLocation.equals(oldLocation)) {
                sheetNum++;
            }
        }

        sheet.setItems(items.toArray(new String[items.size()]));
        sheet.select(sheetNum);

        // try to update
        update(sheetNum);

        super.onShowPage(firstShow);

        // Overwrite super string field editor value
        Sheet sheet = wb.getSheetAt(sheetNum);
        setStringFieldEditorValue(sheet.getSheetName());

        oldLocation = newLocation;

    } catch (OldExcelFormatException e) {
        // the setup is not in a valid state
        clearPage();
        clearSuperPage();
        setErrorMessage(
                "Old excel format detected (format 5.0/7.0 (BIFF5)). Please convert the excel file to BIFF8 from Excel versions 97/2000/XP/2003.");
        setPageComplete(false);
    } catch (Exception e) {
        clearPage();
        clearSuperPage();
        setErrorMessage("Excel file cannot be loaded!");
        setPageComplete(false);
    }
}

From source file:eu.impact_project.resultsrepository.report.Report.java

License:Apache License

private Workbook openExcel(String urlString) throws InvalidFormatException, IOException {
    URL url;/*from w w  w.  j a  v  a 2s .  c o m*/
    Workbook excelWorkbook = null;
    url = new URL(urlString);
    InputStream is = url.openStream();
    excelWorkbook = WorkbookFactory.create(is);
    is.close();

    return excelWorkbook;
}