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:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param resultsFile/*from ww  w  . j a v a 2s. co m*/
 * @return
 * @throws IllegalStateException
 */
protected boolean isValidSpreadsheet(File resultsFile) throws IllegalStateException {
    try {
        FileInputStream fi = new FileInputStream(resultsFile);
        Workbook workBook = WorkbookFactory.create(fi);
        fi.close();
        return isValidSpreadsheet(workBook);
    } catch (FileNotFoundException e) {
        log.error("Results file not found: {}", resultsFile.getAbsolutePath());
    } catch (InvalidFormatException e) {
        log.error("Results file has an invalid format.", e);
    } catch (IOException e) {
        log.error("Error reading results file.", e);
    } catch (IllegalStateException e) {
        log.error("Unexpected cell format.", e);
    } catch (Exception e) {
        log.error(e.getLocalizedMessage(), e);
    }
    return false;
}

From source file:edu.gatech.pmase.capstone.awesome.impl.database.AbstractDatabaseDriver.java

License:Open Source License

/**
 * Loads a database workbook file with the given filename from the workbook
 * directory specified in the project properties file.
 *
 * @param workbookName the file name of the workbook to load
 *
 * @return the loaded workbook, or null if cannot load
 *///from  w ww.  j  a  v a  2  s.  co  m
protected List<T> loadOptionsFromDatabase(final String workbookName) {
    List<T> options = new ArrayList<>();

    if (null != workbookName) {
        LOGGER.debug("Reading options from filename: " + workbookName);

        final Path path = Paths.get(
                DisasterResponseTradeStudyPropertiesSingleton.getInstance().getWorkbookDirectory(),
                workbookName);
        LOGGER.debug("Testing Workbok at " + path.toAbsolutePath());
        final File workbookFile = path.toFile();

        if (workbookFile.exists() && !workbookFile.isDirectory() && workbookFile.canRead()) {
            LOGGER.debug("Loading Workbok at " + path.toAbsolutePath());
            try (final Workbook workbook = WorkbookFactory.create(workbookFile)) {
                this.setCustomAttributes(workbook);

                // get options from workbook
                options.addAll(this.readOptionsFromWorkbook(workbook));
            } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
                LOGGER.error("Could not read Communication filename from properties.", ex);
            }
        } else {
            LOGGER.error("Unable to load Platform workbook with filename: " + workbookName);
        }
    } else {
        LOGGER.error("Could not read platform filename from properties.");
    }

    return options;
}

From source file:edu.isi.karma.imp.excel.ToCSV.java

License:Apache License

private void openWorkbook(InputStream is) throws InvalidFormatException, IOException {

    this.workbook = WorkbookFactory.create(is);
    this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter(true);
}

From source file:edu.jhu.pha.vospace.process.tika.ExcelParser.java

License:Apache License

@Override
public void parse(InputStream stream, ContentHandler handler, Metadata metadata, ParseContext context)
        throws IOException, SAXException, TikaException {

    XHTMLContentHandler xhtml = new XHTMLContentHandler(handler, metadata);
    xhtml.startDocument();/*from ww w .j a  va2  s.  c  o m*/

    Workbook wb;

    try {
        wb = WorkbookFactory.create(stream);
    } catch (InvalidFormatException e) {
        throw new TikaException("Invalid format");
    }

    Sheet sheet = wb.getSheetAt(0);
    int nRows = sheet.getLastRowNum();
    int nCols = sheet.getRow(0).getLastCellNum();
    xhtml.startElement("section", "id", String.valueOf(1));

    AttributesImpl attributes = new AttributesImpl();
    //attributes.addAttribute("", "id", "id", "CDATA", String.valueOf(1));
    attributes.addAttribute("", "columns", "columns", "CDATA", String.valueOf(nCols));
    xhtml.startElement("table", attributes);

    Row headerRow = sheet.getRow(0);
    xhtml.startElement("th", "info", "columnNames");
    for (int j = 0; j < nCols; j++) {
        Cell cell = headerRow.getCell(j);
        String columnName = cell.getStringCellValue();
        xhtml.element("td", columnName);
    }
    xhtml.endElement("th");
    Row firstDataRow = sheet.getRow(1);
    xhtml.startElement("th", "info", "columnTypes");
    for (int j = 0; j < nCols; j++) {
        Cell cell = firstDataRow.getCell(j);
        int type = cell.getCellType();
        String columnType = null;
        switch (type) {
        case Cell.CELL_TYPE_NUMERIC:
            columnType = "D";
            break;
        case Cell.CELL_TYPE_STRING:
            columnType = "A";
            break;
        }
        xhtml.element("td", columnType);
    }
    xhtml.endElement("th");

    for (int i = 1; i <= nRows; i++) {
        Row row = sheet.getRow(i);
        xhtml.startElement("tr");
        for (int j = 0; j < nCols; j++) {
            Cell cell = row.getCell(j);
            int type = cell.getCellType();
            switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                xhtml.element("td", String.valueOf(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_STRING:
                xhtml.element("td", cell.getStringCellValue());
                break;
            }
        }
        xhtml.endElement("tr");

    }
    xhtml.endElement("table");
    xhtml.endElement("section");
    xhtml.endDocument();

    metadata.add(TikaCoreProperties.TYPE, "EXCEL");
}

From source file:edu.si.services.beans.excel.ExcelToCSV.java

License:Apache License

/**
 * Process the contents of a stream, convert the contents of the Excel
 * workbook into CSV format and write the result to the specified stream.
 * Workbooks with the .xls or * .xlsx formats are supported. This method
 * will ensure that the CSV file created contains the comma field separator
 * and that embedded characters such as the field separator, the EOL and
 * double quotes are escaped in accordance with Excel's convention.
 *
 * @param inStream An instance of the InputStream class that encapsulates the
 *        Excel workbook that is to be converted.
 * @param separator A String that contains the value, usually one character,
 *        that is used to separate the cells on a row.  Can be null.
 * @param formattingConvention An int that determines if Excel style escaping
 *        or Unix style escaping should be used.
 * @throws java.io.IOException Thrown if the stream handling encounters any
 *         problems during processing./*w ww.j  a  v a 2s  .c om*/
 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
 *         if the spreadsheet format cannot be processed
 * @throws org.apache.poi.openxml4j.exceptions Thrown if the input is not a
 *         supported Excel format.
 */
public OutputStream convertExcelToCSV(InputStream inStream, String separator, String formattingConvention)
        throws IOException, InvalidFormatException {
    OutputStream outStream = new ByteArrayOutputStream();
    if (separator == null) {
        separator = ExcelToCSV.DEFAULT_SEPARATOR;
    }

    // Ensure the value passed to the formattingConvention parameter is
    // within range.
    if (formattingConvention == null) {
        formattingConvention = ExcelToCSV.EXCEL_STYLE_ESCAPING;
    } else if (!formattingConvention.equals(ExcelToCSV.EXCEL_STYLE_ESCAPING)
            && !formattingConvention.equals(ExcelToCSV.UNIX_STYLE_ESCAPING)) {
        logger.warn("ExcelToCSV: Improper formatting convention provided");
    }

    // Copy the seperator character and formatting convention into local
    // variables for use in other methods.
    this.separator = separator;
    this.formattingConvention = formattingConvention;
    workbook = WorkbookFactory.create(inStream);
    this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter(true);

    // Convert its contents into a CSV file.
    this.convertToCSV();

    // Return the converted CSV.
    return this.getCSVStream(outStream);
}

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.ImportTextTableDialog.java

License:Open Source License

/**
 * Load from the data source.<br>/*  w ww  . jav  a 2  s. c om*/
 *
 * @param evt
 * @throws Exception
 */
private void importButtonActionPerformed(ActionEvent evt) throws Exception {
    if (checkDataSourceError() == false)
        return;

    boolean importAll = importAllCheckBox.isSelected();

    /*
     * Get start line number. If "transfer" check box is true, then start
     * reading from the second line.
     */
    int startLineNumber;
    final int spinnerNumber = Integer.parseInt(startRowSpinner.getValue().toString());

    if (transferNameCheckBox.isSelected()) {
        startLineNumber = spinnerNumber;
    } else {
        startLineNumber = spinnerNumber - 1;
    }

    final String commentChar = commentLineTextField.getText();

    /*
     * Get import flags
     */
    final int colCount = previewPanel.getPreviewTable().getColumnModel().getColumnCount();
    importFlag = new boolean[colCount];

    for (int i = 0; i < colCount; i++) {
        importFlag[i] = ((AttributePreviewTableCellRenderer) previewPanel.getPreviewTable().getCellRenderer(0,
                i)).getImportFlag(i);
    }

    /*
     * Get Attribute Names
     */
    final String[] attributeNames;
    final List<String> attrNameList = new ArrayList<String>();

    Object curName = null;

    for (int i = 0; i < colCount; i++) {
        curName = previewPanel.getPreviewTable().getColumnModel().getColumn(i).getHeaderValue();

        if (attrNameList.contains(curName)) {
            int dupIndex = 0;

            for (int idx = 0; idx < attrNameList.size(); idx++) {
                if (curName.equals(attrNameList.get(idx))) {
                    dupIndex = idx;

                    break;
                }
            }

            if (importFlag[i] && importFlag[dupIndex]) {
                final JLabel label = new JLabel("Duplicate Attribute Name Found: " + curName);
                label.setForeground(Color.RED);
                JOptionPane.showMessageDialog(this, label);

                return;
            }
        }

        if (curName == null) {
            attrNameList.add("Column " + i);
        } else {
            attrNameList.add(curName.toString());
        }
    }

    attributeNames = attrNameList.toArray(new String[0]);

    /*
     * Get attribute data types
     */

    // final byte[] attributeTypes = new byte[previewPanel.getPreviewTable()
    // .getColumnCount()];
    final Byte[] test = previewPanel.getDataTypes(previewPanel.getSelectedSheetName());
    final Byte[] attributeTypes = new Byte[test.length];

    for (int i = 0; i < test.length; i++) {
        attributeTypes[i] = test[i];
    }

    // for (int i = 0; i < attributeTypes.length; i++) {
    // attributeTypes[i] = attributeDataTypes.get(i);
    // }
    final List<Integer> aliasList = new ArrayList<Integer>();
    String mappingAttribute = ID;

    if (dialogType != NETWORK_IMPORT) {
        /*
         * Get column indecies for alias
         */
        JTable curTable = aliasTableMap.get(previewPanel.getSelectedSheetName());

        if (curTable != null) {
            for (int i = 0; i < curTable.getModel().getRowCount(); i++) {
                if ((Boolean) curTable.getModel().getValueAt(i, 0) == true) {
                    aliasList.add(i);
                }
            }
        }

        /*
         * Get mapping attribute
         */
        mappingAttribute = mappingAttributeComboBox.getSelectedItem().toString();
    }

    ObjectType objType = null;

    if (dialogType != NETWORK_IMPORT) {
        if (nodeRadioButton.isSelected()) {
            objType = NODE;
        } else if (edgeRadioButton.isSelected()) {
            objType = EDGE;
        } else {
            objType = NETWORK;
        }
    }

    /*
     * Switch readers based on the dialog type.
     */
    switch (dialogType) {
    case SIMPLE_ATTRIBUTE_IMPORT:

        /*
         * Case 1: Attribute table import.
         */
        // Extract URL from the text table.
        final URL source = new URL(targetDataSourceTextField.getText());
        // Make sure primary key index is up-to-date.
        keyInFile = primaryKeyComboBox.getSelectedIndex();

        // Build mapping parameter object.
        final AttributeMappingParameters mapping;
        final List<String> del;

        System.out.println("IsCytoscapeAttributeFile " + previewPanel.isCytoscapeAttributeFile(source));
        if (previewPanel.isCytoscapeAttributeFile(source)) {
            del = new ArrayList<String>();
            del.add(" += +");
        } else {
            del = checkDelimiter();
        }
        mapping = new AttributeMappingParameters(objType, del, listDelimiter, keyInFile, mappingAttribute,
                aliasList, attributeNames, attributeTypes, listDataTypes, importFlag, caseSensitive);

        if (source.toString().endsWith(SupportedFileType.EXCEL.getExtension())
                || source.toString().endsWith(SupportedFileType.OOXML.getExtension())) {
            /*
             * Read one sheet at a time
             */
            InputStream is = null;
            Workbook wb = null;

            try {
                is = source.openStream();
                wb = WorkbookFactory.create(is);
            } finally {
                if (is != null) {
                    is.close();
                }
            }

            // Load all sheets in the table
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                final Sheet sheet = wb.getSheetAt(i);

                loadAnnotation(new ExcelAttributeSheetReader(sheet, mapping, startLineNumber, importAll),
                        source.toString());
            }
        } else {
            loadAnnotation(new DefaultAttributeTableReader(source, mapping, startLineNumber, null, importAll),
                    source.toString());
        }

        break;

    case ONTOLOGY_AND_ANNOTATION_IMPORT:

        /*
         * Case 2: Import Ontology and its annotation.
         */
        final String selectedOntologyName = ontologyComboBox.getSelectedItem().toString();
        final String ontologySourceLocation = ontologyUrlMap.get(selectedOntologyName);

        /*
         * If selected ontology is not loaded, load it first.
         */
        if (Cytoscape.getOntologyServer().getOntologyNames().contains(selectedOntologyName) == false)
            loadOntology(ontologySourceLocation, selectedOntologyName);

        /*
         * Now, load & map annotation.
         */
        final String annotationSource = annotationUrlMap.get(annotationComboBox.getSelectedItem());
        final URL annotationSourceUrl = new URL(annotationSource);

        if (previewPanel.getFileType() == FileTypes.GENE_ASSOCIATION_FILE) {
            /*
             * This is a Gene Association file.
             */
            GeneAssociationReader gaReader = null;
            keyInFile = this.primaryKeyComboBox.getSelectedIndex();

            InputStream is = null;
            try {
                is = URLUtil.getInputStream(annotationSourceUrl);
                gaReader = new GeneAssociationReader(selectedOntologyName, is, mappingAttribute, importAll,
                        keyInFile, caseSensitive);
            } catch (Exception e) {
                if (is != null) {
                    is.close();
                }
                throw e;
            }

            loadGeneAssociation(gaReader, selectedOntologyName, annotationSource);
        } else {
            /*
             * This is a custom annotation file.
             */
            final int ontologyIndex = ontologyInAnnotationComboBox.getSelectedIndex();

            final AttributeAndOntologyMappingParameters aoMapping = new AttributeAndOntologyMappingParameters(
                    objType, checkDelimiter(), listDelimiter, keyInFile, mappingAttribute, aliasList,
                    attributeNames, attributeTypes, listDataTypes, importFlag, ontologyIndex,
                    selectedOntologyName, caseSensitive);
            final OntologyAnnotationReader oaReader = new OntologyAnnotationReader(annotationSourceUrl,
                    aoMapping, commentChar, startLineNumber);

            loadAnnotation(oaReader, annotationSource);
        }

        break;

    case NETWORK_IMPORT:

        /*
         * Case 3: read as network table (Network + Edge Attributes)
         */

        // Extract URL from the text table.
        /*
         * Now multiple files are supported.
         */
        URL[] sources = new URL[inputFiles.length];

        for (int i = 0; i < sources.length; i++) {
            sources[i] = inputFiles[i].toURI().toURL();
        }

        //final URL networkSource = new URL(targetDataSourceTextField.getText());
        final int sourceColumnIndex = networkImportPanel.getSourceIndex();
        final int targetColumnIndex = networkImportPanel.getTargetIndex();

        final String defaultInteraction = defaultInteractionTextField.getText();

        final int interactionColumnIndex = networkImportPanel.getInteractionIndex();

        final NetworkTableMappingParameters nmp = new NetworkTableMappingParameters(checkDelimiter(),
                listDelimiter, attributeNames, attributeTypes, null, importFlag, sourceColumnIndex,
                targetColumnIndex, interactionColumnIndex, defaultInteraction);

        NetworkTableReader reader;
        String networkName;
        boolean multi = true;

        if (sources.length == 1)
            multi = false;

        for (int i = 0; i < sources.length; i++) {
            if (sources[i].toString().endsWith(SupportedFileType.EXCEL.getExtension())
                    || sources[i].toString().endsWith(SupportedFileType.OOXML.getExtension())) {
                // Extract name from the sheet name.
                InputStream is = null;
                Workbook wb = null;
                try {
                    is = sources[i].openStream();
                    wb = WorkbookFactory.create(is);
                } finally {
                    if (is != null) {
                        is.close();
                    }
                }

                Sheet sheet = wb.getSheetAt(0);
                networkName = wb.getSheetName(0);

                reader = new ExcelNetworkSheetReader(networkName, sheet, nmp, startLineNumber);
            } else {
                // Get name from URL.
                if ((commentChar != null) && (commentChar.length() != 0) && transferNameCheckBox.isSelected()) {
                    startLineNumber++;
                }

                final String[] parts = sources[i].toString().split("/");
                networkName = parts[parts.length - 1];
                reader = new NetworkTableReader(networkName, sources[i], nmp, startLineNumber, commentChar);
            }

            loadNetwork(networkName, reader, sources[i], multi);
        }

        if (multi) {
            StringBuilder builder = new StringBuilder();
            builder.append("The following networks are loaded:\n\n");

            for (File f : inputFiles) {
                builder.append(f.getName() + "\n");
            }

            JOptionPane.showMessageDialog(this, builder.toString(), "Multiple Networks Loaded",
                    JOptionPane.INFORMATION_MESSAGE);
        }

        break;

    default:
        return;
    }

    Cytoscape.firePropertyChange(Cytoscape.ATTRIBUTES_CHANGED, null, null);

    dispose();
}

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java

License:Open Source License

/**
 * Load file and show preview./*ww  w .  jav a 2  s . co m*/
 * 
 * @param sourceURL
 * @param delimiters
 * @param renderer
 *            renderer for this table. Can be null.
 * @param size
 * @param commentLineChar
 *            TODO
 * @param startLine
 *            TODO
 * @throws IOException
 */
public void setPreviewTable(URL sourceURL, List<String> delimiters, TableCellRenderer renderer, int size,
        final String commentLineChar, final int startLine) throws IOException {
    TableCellRenderer curRenderer = renderer;

    if ((commentLineChar != null) && (commentLineChar.trim().length() != 0))
        this.commentChar = commentLineChar;
    else
        this.commentChar = null;
    /*
     * If rendrer is null, create default one.
     */
    if (curRenderer == null) {
        curRenderer = new AttributePreviewTableCellRenderer(0, new ArrayList<Integer>(),
                AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST,
                AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, null,
                TextFileDelimiters.PIPE.toString());
    }

    /*
     * Reset current state
     */
    for (int i = 0; i < tableTabbedPane.getTabCount(); i++)
        tableTabbedPane.removeTabAt(i);

    previewTables = new HashMap<String, JTable>();

    TableModel newModel;

    fileTypeLabel.setVisible(true);

    if (sourceURL.toString().endsWith(SupportedFileType.EXCEL.getExtension())
            || sourceURL.toString().endsWith(SupportedFileType.OOXML.getExtension())) {

        fileTypeLabel.setIcon(SPREADSHEET_ICON.getIcon());
        fileTypeLabel.setText("Excel" + '\u2122' + " Workbook");

        InputStream is = null;
        final Workbook wb;

        try {
            is = sourceURL.openStream();
            wb = WorkbookFactory.create(is);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new IllegalArgumentException("Could not read Excel file.  Maybe the file is broken?");
        } finally {
            if (is != null)
                is.close();
        }

        if (wb.getNumberOfSheets() == 0)
            throw new IllegalStateException("No sheet found in the workbook.");

        /*
         * Load each sheet in the workbook.
         */
        logger.debug("# of Sheets = " + wb.getNumberOfSheets());

        Sheet sheet = wb.getSheetAt(0);
        logger.debug("Sheet name = " + wb.getSheetName(0) + ", ROW = " + sheet.rowIterator().hasNext());

        newModel = parseExcel(sourceURL, size, curRenderer, sheet, startLine);

        if (newModel.getRowCount() == 0)
            throw new IllegalStateException("No data found in the Excel sheet.");

        DataTypeUtil.guessTypes(newModel, wb.getSheetName(0), dataTypeMap);
        listDataTypeMap.put(wb.getSheetName(0), initListDataTypes(newModel));
        addTableTab(newModel, wb.getSheetName(0), curRenderer);
    } else {
        if (isCytoscapeAttributeFile(sourceURL)) {
            fileTypeLabel.setText("Cytoscape Attribute File");
            fileTypeLabel.setIcon(new ImageIcon(Cytoscape.class.getResource("images/icon48.png")));
            newModel = parseText(sourceURL, size, curRenderer, null, 1);
        } else {
            fileTypeLabel.setText("Text File");
            fileTypeLabel.setIcon(TEXT_FILE_ICON.getIcon());
            newModel = parseText(sourceURL, size, curRenderer, delimiters, startLine);
        }

        String[] urlParts = sourceURL.toString().split("/");
        final String tabName = urlParts[urlParts.length - 1];
        DataTypeUtil.guessTypes(newModel, tabName, dataTypeMap);
        listDataTypeMap.put(tabName, initListDataTypes(newModel));
        addTableTab(newModel, tabName, curRenderer);
    }

    if (getFileType() == FileTypes.GENE_ASSOCIATION_FILE) {
        fileTypeLabel.setText("Gene Association");
        fileTypeLabel.setToolTipText("This is a fixed-format Gene Association file.");
    }

    loadFlag = true;
}

From source file:edu.vt.owml.saurav.raininterpolation.debug.NewMain.java

License:Open Source License

/**
 * @param args the command line arguments
 *//*from w  w w .j a  v  a2s  . c  o m*/
public static void main(String[] args) {
    try {

        Workbook wb;
        wb = WorkbookFactory.create(NewMain.class.getResourceAsStream("/unit_test.xlsx"));

        // retrieve the named range
        String cellname = "stations";
        int namedCellIdx = wb.getNameIndex(cellname);
        Name aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
        CellReference[] crefs = (CellReference[]) aref.getAllReferencedCells();
        int index = 0;
        int columns = 2;
        double[][] stations = new double[(int) crefs.length / columns][2];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            stations[(int) (index / columns)][index % columns] = c.getNumericCellValue();
            index++;
        }
        printArray(stations);

        //rain
        cellname = "gridpts";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        columns = 2;
        double[][] locations = new double[(int) crefs.length / columns][2];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            locations[(int) (index / columns)][index % columns] = c.getNumericCellValue();
            index++;
        }
        printArray(locations);

        //rain
        cellname = "rainVal";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        double[] rainValues = new double[crefs.length];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            rainValues[index] = c.getNumericCellValue();
            index++;
        }
        printArray(rainValues);

        //vals
        cellname = "estimates";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        double[] vals = new double[crefs.length];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            vals[index] = c.getNumericCellValue();
            index++;
        }
        printArray(vals);

        //distances
        cellname = "distances";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        columns = stations.length;
        double[] d = new double[stations.length];
        List<double[]> distances = new ArrayList();
        for (CellReference cref : crefs) {

            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            d[index % columns] = c.getNumericCellValue();
            if (index % columns == columns - 1) {
                distances.add(d);
                d = new double[stations.length];
            }
            index++;

        }
        printArray(distances);

        IDWInterpolator idw = new IDWInterpolator();
        // printArray(idw.getDistances(stations, locations));

    } catch (FileNotFoundException ex) {
        Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:edu.vt.vbi.patric.common.ExcelHelper.java

License:Apache License

/**
 * Reads in an Excel file via a filename
 * // www  . java 2  s  .com
 * @param fileName = name of the file to read in
 * @return true/false for success/failure
 */
public boolean readFile(String fileName) {

    InputStream inp;
    try {
        inp = new FileInputStream(fileName);
    } catch (FileNotFoundException e) {
        inp = null;
        LOGGER.error(e.getMessage(), e);
    }

    if (fileName.substring(fileName.length() - 1, fileName.length()).equals("x")) {
        try {
            xwb = (XSSFWorkbook) WorkbookFactory.create(inp);
            return true;
        } catch (FileNotFoundException e) {
            LOGGER.error(
                    "File Not Found Exception thrown. Uncomment in readFile(String fileName) to see stack trace.",
                    e);
            return false;
        } catch (InvalidFormatException e) {
            LOGGER.error(
                    "Invalid Format Exception thrown. Uncomment in readFile(String fileName) to see stack trace.",
                    e);
            return false;
        } catch (IOException e) {
            LOGGER.error("I/O Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e);
            return false;
        } catch (Exception e) {
            return false;
        }
    } else {
        try {
            wb = WorkbookFactory.create(inp);
            return true;
        } catch (FileNotFoundException e) {
            LOGGER.error(
                    "File Not Found Exception thrown. Uncomment in readFile(String fileName) to see stack trace.",
                    e);
            return false;
        } catch (InvalidFormatException e) {
            LOGGER.error(
                    "Invalid Format Exception thrown. Uncomment in readFile(String fileName) to see stack trace.",
                    e);
            return false;
        } catch (IOException e) {
            LOGGER.error("I/O Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e);
            return false;
        } catch (Exception e) {
            return false;
        }
    }
}

From source file:eionet.gdem.conversion.excel.ExcelUtils.java

License:Mozilla Public License

/**
 * Determines if stream is Excel 2007 file.
 * @param input InputStream//ww  w. j  av  a2s.  c om
 * @return True if InputStream is Excel 2007 file.
 */
public static boolean isExcel2007File(InputStream input) {
    try {
        OPCPackage p = OPCPackage.open(input);
        Workbook wb = WorkbookFactory.create(p);
        return true;
    } catch (Exception e) {
        return false;
    } finally {
        IOUtils.closeQuietly(input);
    }
}