gvSIG data provider for MS-Excel

>>> from  jxl import Workbook

>>> Workbook.getWorkbook

<java function getWorkbook 1>

>>> from java.io import File

>>> f=File("/home/usuario/datos/geodata/tables/torrent_4-es-excel2003.xls")

>>> f.exists()

1

>>> w=Workbook.getWorkbook(f)

>>> dir(w)

['XCTRecords', 'addInFunctionNames', 'buttonPropertySet', 'class', 'close', 'compoundFile', 'containsMacros', 'countryRecord', 'createWorkbook', 'drawingGroup', 'equals', 'externalSheetRecord', 'findByName', 'findCellByName', 'fonts', 'formattingRecords', 'getAddInFunctionNames', 'getButtonPropertySet', 'getCell', 'getClass', 'getCompoundFile', 'getCountryRecord', 'getDrawingGroup', 'getExternalSheetIndex', 'getExternalSheetName', 'getExternalSheetRecord', 'getFonts', 'getFormattingRecords', 'getIndex', 'getLastExternalSheetIndex', 'getLastExternalSheetName', 'getMsoDrawingGroupRecord', 'getName', 'getNameIndex', 'getNameRecords', 'getNumberOfSheets', 'getRangeNames', 'getReadSheet', 'getSettings', 'getSheet', 'getSheetNames', 'getSheets', 'getSupbookRecords', 'getVersion', 'getWorkbook', 'getWorkbookBof', 'getXCTRecords', 'hashCode', 'isProtected', 'msoDrawingGroupRecord', 'nameRecords', 'notify', 'notifyAll', 'numberOfSheets', 'protected', 'rangeNames', 'settings', 'sheetNames', 'sheets', 'supbookRecords', 'toString', 'wait', 'workbookBof']

>>> w.numberOfSheets

1

>>> w.sheetNames

array(java.lang.String,['Hoja1']) 

>>> w.sheets

array(jxl.Sheet,[jxl.read.biff.SheetImpl@3a4f51e8]) 

>>> s=w.sheets[0]

>>> s

jxl.read.biff.SheetImpl@3a4f51e8

>>> dir(s)

['PLS', 'autoFilter', 'buttonPropertySet', 'charts', 'class', 'columnInfos', 'columnPageBreaks', 'columns', 'conditionalFormats', 'dataValidation', 'drawingData', 'drawings', 'equals', 'findCell', 'findLabelCell', 'getAutoFilter', 'getButtonPropertySet', 'getCell', 'getCharts', 'getClass', 'getColumn', 'getColumnFormat', 'getColumnInfo', 'getColumnInfos', 'getColumnPageBreaks', 'getColumnView', 'getColumnWidth', 'getColumns', 'getConditionalFormats', 'getDataValidation', 'getDrawing', 'getDrawingData', 'getDrawings', 'getHyperlinks', 'getMaxColumnOutlineLevel', 'getMaxRowOutlineLevel', 'getMergedCells', 'getName', 'getNumberOfImages', 'getPLS', 'getRow', 'getRowHeight', 'getRowPageBreaks', 'getRowProperties', 'getRowView', 'getRows', 'getSettings', 'getSheetBof', 'getWorkbook', 'getWorkbookBof', 'getWorkspaceOptions', 'hashCode', 'hidden', 'hyperlinks', 'isHidden', 'isProtected', 'maxColumnOutlineLevel', 'maxRowOutlineLevel', 'mergedCells', 'name', 'notify', 'notifyAll', 'numberOfImages', 'protected', 'rowPageBreaks', 'rowProperties', 'rows', 'settings', 'sheetBof', 'toString', 'wait', 'workbook', 'workbookBof', 'workspaceOptions']

>>> 

>>> s.getRows()

382

>>> s.columns

5

>>> 

>>> c=s.getCell(0,0)

>>> dir(c)

['XFIndex', 'cellFeatures', 'cellFormat', 'class', 'column', 'contents', 'equals', 'getCellFeatures', 'getCellFormat', 'getClass', 'getColumn', 'getContents', 'getRow', 'getString', 'getType', 'getXFIndex', 'hashCode', 'hidden', 'isHidden', 'notify', 'notifyAll', 'row', 'setCellFeatures', 'string', 'toString', 'type', 'wait']

>>> 

>>> c.toString()

'jxl.read.biff.LabelSSTRecord@14a614b5'

>>> c.contents

'B1'

>>> 

>>> s.getCell(0,s.getRows()-1)

jxl.read.biff.NumberValue@3e803098

>>> s.getCell(s.columns-1,s.getRows()-1)

jxl.read.biff.LabelSSTRecord@56dc16be

>>> s.getCell(s.columns-1,s.getRows()-1).contents

'R'

>>> 

<?xml version="1.0" encoding="ISO-8859-1"?>

<plugin-config>

  <libraries library-dir="lib/"/>

  <depends plugin-name="org.gvsig.app.mainplugin"/>

  <resourceBundle name="text"/>

  <extensions>

     <extension class-name="org.gvsig.andami.LibraryExtension" active="false"/>

  </extensions>

</plugin-config>

<assembly>

  <id>gvsig-plugin-package</id>

  <formats>

    <format>zip</format>

  </formats>

  <baseDirectory>${project.artifactId}</baseDirectory>

  <includeBaseDirectory>true</includeBaseDirectory>

  <files>

    <file>

      <source>target/${project.artifactId}-${project.version}.jar</source>

      <outputDirectory>lib</outputDirectory>

    </file>

    <file>

      <source>target/package.info</source>

    </file>

  </files>

  <fileSets>

    <fileSet>

      <directory>src/main/resources-plugin</directory>

      <outputDirectory>.</outputDirectory>

    </fileSet>

  </fileSets>

  <dependencySets>

    <dependencySet>

      <useProjectArtifact>false</useProjectArtifact>

      <useTransitiveDependencies>false</useTransitiveDependencies>

      <outputDirectory>lib</outputDirectory>

      <includes>

        <include>net.sourceforge.jexcelapi:jxl</include>

        <include>org.gvsig:org.gvsig.jexcel.provider</include>        

      </includes>

    </dependencySet>

  </dependencySets>

</assembly>

jexcel plugin user manual v0.2

=================================

.. contents:: Contents

Introduction

--------------

The jexcel plugin add to gvSIG the following functionalities:

* Load Microsoft Excel spreadsheets as tables.

* Load Microsoft Excel spreadsheets as layers.

* Export a table to Excel spreadsheet format.

It supports the following Excel spreadsheet versions:

* Reading: Excel 95, 97, 2000, XP, and 2003 workbooks.

* Writing: Excel 2000

Installation

--------------

(TODO)

Loading a table from an Excel spreadsheet

------------------------------------------

From the gvSIG project manager, select the “Table” document (1) and click on “New” (2). In the “New table” window the “File” tab is selected by default. Click on “Add” (3) and find the Excel file that contains the spreadsheet you want to load (4). Finally click on “Open” (5).

|

.. image:: Selection_088.png

  :align: center

  :scale: 150

Once the file appears in the “New table” window, click on “Properties” (7) in order to set up the following parameters:

Data source properties

+++++++++++++++++++++++

General tab (default)

^^^^^^^^^^^^^^^^^^^^^^

 - **File:** File path.

 - **Locale:** Localization configuration. This determines the characters used as decimal and thousands separators.

 - **Sheet to load:** Position within the spreadsheet file of the sheet that we want to load. First position corresponds with the number "0", second with "1" and so on.

 - **Use first row as header:** In case this option is checked it will consider the first row values as the field names.

 - **CRS:** In case the sheet contains coordinates this parameter sets its Coordinate Reference System.

 - **Point (X,Y,Z):** Position of the fields that contains the coordinates. It should be provided at least two fields for X,Y coordinates.

|

.. image:: Selection_091.png

  :align: center

  :scale: 150

Advanced tab

^^^^^^^^^^^^^^

 - **ProviderName:** The provider name (internal use).

 - **Header:** In case the sheet doesn't have a header we can define it here. We have to type the name of the fields separated by a comma.

 - **Field types:** The is useful if we want to force the program to load the table with a specific field types. 

 - **Automatic types detection:** This options is checked by default. It tryes to figure out what the field types are. 

 - **Ignore errors:** This option forces the program to load the table even in case it finds erros.

|

.. image:: Selection_090.png

  :align: center

  :scale: 150

Once all the needed parameters have been set up click on “Accept” and finally on “Ok”. If everything worked fine the table should appear.

|

.. image:: Selection_092.png

  :align: center

  :scale: 150

Loading a layer from an Excel Spreadsheet

------------------------------------------

From the gvSIG project manager, select the “View” document (1) and click on “New” (2). In the “Add layer” window the “File” tab is selected by default. Click on “Add” (4) and find the Excel file that contains the spreadsheet you want to load (5). Finally click on “Open” (6).

|

.. image:: Selection_095.png

  :align: center

  :scale: 150

Once the file appears in the “Add layer” window, click on “Properties” (7) in order to set up the following parameters:

Data source properties

+++++++++++++++++++++++

General tab (default)

^^^^^^^^^^^^^^^^^^^^^^

 - **File:** File path.

 - **Locale:** Localization configuration. This determines the characters used as decimal and thousands separators.

 - **Sheet to load:** Position within the spreadsheet file of the sheet that we want to load. First position corresponds with the number "0", second with "1" and so on.

 - **Use first row as header:** In case this option is checked it will consider the first row values as the field names.

 - **CRS:** In case the sheet contains coordinates this parameter sets its Coordinate Reference System.

 - **Point (X,Y,Z):** Position of the fields that contains the coordinates. It should be provided at least two fields for X,Y coordinates.

|

.. image:: Selection_096.png

  :align: center

  :scale: 150

Advanced tab

^^^^^^^^^^^^^^

 - **ProviderName:** The provider name (internal use).

 - **Header:** In case the sheet doesn't have a header we can define it here. We have to type the name of the fields separated by a comma.

 - **Field types:** The is useful if we want to force the program to load the table with a specific field types. 

 - **Automatic types detection:** This options is checked by default. It tryes to figure out what the field types are. 

 - **Ignore errors:** This option forces the program to load the table even in case it finds erros.

|

.. image:: Selection_097.png

  :align: center

  :scale: 150

Once all the needed parameters have been set up click on “Accept” and finally on “Ok”. If everything worked fine the layer should appear in the view.

|

.. image:: Selection_098.png

  :align: center

  :scale: 150

Exporting a table to Excel spreadsheet format

----------------------------------------------

(TODO)

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <artifactId>org.gvsig.jexcel.app.mainplugin</artifactId>

    <packaging>jar</packaging>

    <name>${project.artifactId}</name>

    <description>Excel file format support</description>

    <parent>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.jexcel.app</artifactId>

        <version>1.0.87</version>

    </parent>

    <dependencies>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.lib</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.andami</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.jexcel.provider</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>net.sourceforge.jexcelapi</groupId>

            <artifactId>jxl</artifactId>

            <scope>compile</scope>

        </dependency>

    </dependencies>

    <properties>

        <!-- Package info property values -->

        <!-- Default values in org.gvsig.desktop -->

        <gvsig.package.info.name>Formats: Excel file format support</gvsig.package.info.name>

        <gvsig.package.info.state>testing</gvsig.package.info.state>

        <gvsig.package.info.official>true</gvsig.package.info.official>

        <gvsig.package.info.dependencies>required: org.gvsig.app.mainplugin -ge 2.1.0-A</gvsig.package.info.dependencies>

        <gvsig.package.info.categories>Formats,Vector</gvsig.package.info.categories>

        <gvsig.package.info.poolURL>https://devel.gvsig.org/download/projects/gvsig-jexcel/pool/</gvsig.package.info.poolURL>

    </properties>

</project>

#Wed Jan 29 15:03:31 CET 2020

buildNumber=91

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <artifactId>org.gvsig.jexcel.app</artifactId>

  <packaging>pom</packaging>

  <name>${project.artifactId}</name>

  <parent>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.jexcel</artifactId>

      <version>1.0.87</version>

  </parent>

  <modules>

    <module>org.gvsig.jexcel.app.mainplugin</module>

  </modules>

</project>

org.gvsig.jexcel.JExcelLibrary

<?xml version="1.0"?>

<!--

Definitions of metadata fields of a shp file.  

 -->

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="JExcel" namespace="Metadata">

      <extends>

      	<class namespace="Metadata" name="SpatialProvider"/>

      </extends>

      <description>Metadata of a JExcel store</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version= "1.0 "?>

<definitions>

    <version>1.0.0</version>

    <classes>

        <class name="JExcel">

            <extends>

                <class namespace="dal" name="ProviderParameters"/>

            </extends>

            <description>Store the parameters need to open a Excel file</description>

            <fields>

                <field name="file" label="File" type="file" mandatory="true">

                    <description>Excel file</description>

                </field>

                <field name="locale" label="Locale" type="string" mandatory="true" defaultValue="DEFAULT">

                    <description>Locale used to parse double and float values (BCP47 language tag representing the locale)</description>

                    <availableValues>

                        <value label="Default (use system locale)">DEFAULT</value>

                        <value label="Canada (english)">en-CA</value>

                        <value label="Canada (french)">fr-CA</value>

                        <value label="Chinese">zh</value>

                        <value label="English">en</value>

                        <value label="French">fr</value>

                        <value label="German">de</value>

                        <value label="Italian">it</value>

                        <value label="Japanese">ja</value>

                        <value label="Korea">ko</value>

                        <value label="Spain">es</value>

                        <value label="Uk">en-GB</value>

                        <value label="Us">en-US</value>

                    </availableValues>          

                </field>

                <field name="sheet" label="Sheet to load" type="integer" subtype="jexcel.sheet.selector" mandatory="false" defaultValue="0">

                    <description>Sheet to use.</description>

                </field>

                <field name="isFirstRowHeadeer" label="Use first row as header" type="boolean" mandatory="false" defaultValue="false">

                    <description>Check for use first row as header.</description>

                </field>

                <field name="CRS" type="crs" mandatory="false">

                    <description>The coordinate reference system used when use the point parameter to generate a layer</description>

                </field>

                <field name="point" label="Point (X,Y,Z)" type="string" mandatory="false" defaultValue="" >

                    <description>This field allow to add a Geometry column to the table. It is a list of field names separated by commas used as dimensions for the attribute GEOM of type point to add to the table.</description>

                </field>

                <field name="header" label="Header" type="string" mandatory="false" defaultValue="" group="Advanced">

                    <description>The header or empty if parsed automatically</description>

                </field>

                <field name="fieldtypes" label="Field types" type="string" mandatory="false" defaultValue="" group="Advanced">

                    <description>Comma separated list of field types. If empty strings are assumed (Eg: integer,string,double,boolean,float,geometry).</description>

                </field>

                <field name="automaticTypesDetection" label="Automatic types detection" type="boolean" mandatory="false" defaultValue="true" group="Advanced">

                    <description>This flag activate the autodetection of types in the CSV. Can be slow.</description>

                </field>

                <field name="ignoreErrors" label="Ignore errors" type="boolean" mandatory="false" defaultValue="false" group="Advanced">

                    <description>Set to true to ignore errors during load of data.</description>

                </field>

            </fields>

        </class>

    </classes>

</definitions>  

package org.gvsig.jexcel;

import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

import java.util.Locale;

import java.util.Objects;

import org.apache.commons.lang3.StringUtils;

import java.awt.Rectangle;

import java.util.Arrays;

import jxl.Cell;

import jxl.Workbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 *

 * @author jjdelcerro

 */

@SuppressWarnings("UseSpecificCatch")

public class JExcelSpread implements Spread {

    private static final Logger LOGGER = LoggerFactory.getLogger(JExcelSpread.class);

    private static class SheetJExcel implements Sheet {

        private final Workbook workbook;        

        private final File spreadFile;

        private final int sheetIndex;

        private final jxl.Sheet sheetExcel;

        public SheetJExcel(File spreadFile, int sheetIndex) throws IOException {

            try {

                this.spreadFile = spreadFile;

                this.sheetIndex = sheetIndex;

                this.workbook = Workbook.getWorkbook(spreadFile);

                this.sheetExcel = this.workbook.getSheet(sheetIndex);

            } catch (Exception ex) {

                throw new IOException("Can't open sheet "+sheetIndex+" from '"+Objects.toString(spreadFile)+"'.", ex);

            }

        }

        @Override

        public int getColumnCount() {

            return this.sheetExcel.getColumns();

        }

        @Override

        public Rectangle getUsedRange() {

            return null;

        }

        @Override

        public List<String> getColumnNames(boolean isFirstLineHedaer) {

            int columns = this.getColumnCount();

            List<String> columnNames = new ArrayList<>(columns);

            String abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            if (columns > abc.length()) {

                for (int i = 0; i < columns; i++) {

                    columnNames.add("C" + i);

                }

            } else {

                for (int i = 0; i < columns; i++) {

                    columnNames.add(abc.substring(i, i + 1));

                }

            }

            if (isFirstLineHedaer) {

                for (int col = 0; col < columns; col++) {

                    Object value = this.getValueAt(col, 0);

                    String s = Objects.toString(value);

                    if (!StringUtils.isBlank(s)) {

                        columnNames.set(col, s);

                    }

                }

            }

            return columnNames;

        }

        @Override

        public File getFile() {

            return this.spreadFile;

        }

        @Override

        public String getName() {

            return this.sheetExcel.getName();

        }

        @Override

        public int getRowCount() {

            return this.sheetExcel.getRows();

        }

        @Override

        public int getSheetIndex() {

            return this.sheetIndex;

        }

        @Override

        public Object getValueAt(int col, int row) {

            Cell cell = this.sheetExcel.getCell(col, row);

            Object value = cell.getContents();

            return value;

        }

    }

    private File spreadFile;

    public JExcelSpread() {

    }

    @Override

    public void open(File spreadFile) {

        this.spreadFile = spreadFile;

    }

    @Override

    public File getFile() {

        return this.spreadFile;

    }

    @Override

    public Sheet getSheet(int sheetIndex) {

        try {

            if (spreadFile == null || sheetIndex < 0) {

                return null;

            }

            return new SheetJExcel(spreadFile, sheetIndex);

        } catch (IOException ex) {

            return null;

        }

    }

    @Override

    public List<String> getSheetNames() {

        try {

            List<String> r = new ArrayList<>();

            Workbook workbook = Workbook.getWorkbook(spreadFile);

            r.addAll(Arrays.asList(workbook.getSheetNames()));

            return r;

        } catch (Exception ex) {

            return Collections.EMPTY_LIST;

        }

    }

}

package org.gvsig.jexcel;

import java.awt.Rectangle;

import java.io.File;

import java.util.List;

/**

 *

 * @author jjdelcerro

 */

public interface Spread {

    public static String NAME = "Excel";

    public static Spread create(File file, Object... args) {

        JExcelSpread spread = new JExcelSpread();

        spread.open(file);

        return spread;

    }

    public interface Sheet {

        int getColumnCount();

        List<String> getColumnNames(boolean isFirstLineHedaer);

        File getFile();

        String getName();

        int getRowCount();

        int getSheetIndex();

        Object getValueAt(int col, int row);

        public Rectangle getUsedRange();

    }

    public void open(File spreadFile);

    public File getFile();

    public Sheet getSheet(int sheetIndex);

    public List<String> getSheetNames();

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.jexcel;

import java.util.ArrayList;

import java.util.List;

import org.gvsig.fmap.dal.DALFileLibrary;

import org.gvsig.fmap.dal.DALFileLocator;

import org.gvsig.fmap.dal.DALLibrary;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.FileHelper;

import org.gvsig.fmap.dal.spi.DataManagerProviderServices;

import org.gvsig.jexcel.dal.provider.JExcelFilesystemServerProvider;

import org.gvsig.jexcel.dal.provider.JExcelStoreParameters;

import org.gvsig.jexcel.dal.provider.JExcelStoreProvider;

import org.gvsig.jexcel.dal.provider.JExcelStoreProviderFactory;

import org.gvsig.jexcel.dynformfield.point.JDynFormFieldExcelPointFieldsFactory;

import org.gvsig.jexcel.dynformfield.sheet.JDynFormFieldExcelSheetFactory;

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.dynform.spi.DynFormSPILocator;

import org.gvsig.tools.dynform.spi.DynFormSPIManager;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

public class JExcelLibrary extends AbstractLibrary {

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(DALFileLibrary.class);

    }

    @Override

    protected void doInitialize() throws LibraryException {

    }

    @Override

    protected void doPostInitialize() throws LibraryException {

        List<Throwable> exs = new ArrayList<Throwable>();

        FileHelper.registerParametersDefinition(

                JExcelStoreParameters.PARAMETERS_DEFINITION_NAME,

                JExcelStoreParameters.class, "JExcelParameters.xml");

        try {

            FileHelper.registerMetadataDefinition(

                    JExcelStoreProvider.METADATA_DEFINITION_NAME,

                    JExcelStoreProvider.class, "JExcelMetadata.xml");

        } catch (MetadataException e) {

            exs.add(e);

        }

        DataManagerProviderServices dataman = (DataManagerProviderServices) DALLocator

                .getDataManager();

        try {

            if (!dataman.getStoreProviders().contains(JExcelStoreProvider.NAME)) {

                dataman.registerStoreProviderFactory(new JExcelStoreProviderFactory(JExcelStoreProvider.NAME, JExcelStoreProvider.DESCRIPTION));

            }

        } catch (RuntimeException e) {

            exs.add(e);

        }

        try {

            DALFileLocator.getFilesystemServerExplorerManager()

                    .registerProvider(JExcelStoreProvider.NAME,

                            JExcelStoreProvider.DESCRIPTION,

                            JExcelFilesystemServerProvider.class);

        } catch (RuntimeException e) {

            exs.add(e);

        }

        DynFormSPIManager manager = DynFormSPILocator.getDynFormSPIManager();

        manager.registerDynFieldFactory(new JDynFormFieldExcelSheetFactory());

        manager.registerDynFieldFactory(new JDynFormFieldExcelPointFieldsFactory());

        if (exs.size() > 0) {

            throw new LibraryException(this.getClass(), exs);

        }

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.jexcel.dal.provider;

import java.io.File;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.NewDataStoreParameters;

import org.gvsig.fmap.dal.exception.CreateException;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.FileNotFoundException;

import org.gvsig.fmap.dal.exception.RemoveException;

import org.gvsig.fmap.dal.resource.spi.ResourceConsumer;

import org.gvsig.fmap.dal.resource.spi.ResourceProvider;

import org.gvsig.fmap.dal.serverexplorer.filesystem.impl.AbstractFilesystemServerExplorerProvider;

import org.gvsig.fmap.dal.serverexplorer.filesystem.spi.FilesystemServerExplorerProvider;

import org.gvsig.fmap.dal.serverexplorer.filesystem.spi.FilesystemServerExplorerProviderServices;

public class JExcelFilesystemServerProvider extends AbstractFilesystemServerExplorerProvider 

	implements FilesystemServerExplorerProvider, ResourceConsumer {

	private FilesystemServerExplorerProviderServices serverExplorer;

	public String getDataStoreProviderName() {

		return JExcelStoreProvider.NAME;

	}

	public int getMode() {

		return DataServerExplorer.MODE_FEATURE | DataServerExplorer.MODE_GEOMETRY;

	}

	public boolean accept(File pathname) {

		return (pathname.getName().toLowerCase().endsWith(".xls"));

	}

	public String getDescription() {

		return JExcelStoreProvider.DESCRIPTION;

	}

	public DataStoreParameters getParameters(File file) throws DataException {

		DataManager manager = DALLocator.getDataManager();

		JExcelStoreParameters params = (JExcelStoreParameters) manager

				.createStoreParameters(this

				.getDataStoreProviderName());

		params.setFile(file);

		return params;

	}

	public boolean canCreate() {

		return false;

	}

	public boolean canCreate(NewDataStoreParameters parameters) {

			throw new UnsupportedOperationException(); 

	}

	public void create(NewDataStoreParameters parameters, boolean overwrite)

			throws CreateException {

		throw new UnsupportedOperationException(); 

	}

	public NewDataStoreParameters getCreateParameters() throws DataException {

		throw new UnsupportedOperationException(); 

	}

	public void initialize(

			FilesystemServerExplorerProviderServices serverExplorer) {

		this.serverExplorer = serverExplorer;

	}

	public void remove(DataStoreParameters parameters) throws RemoveException {

		File file = ((JExcelStoreParameters) parameters).getFile();

		if (!file.exists()) {

			throw new RemoveException(this.getDataStoreProviderName(),

					new FileNotFoundException(file));

		}

		if (!file.delete()) {

			// FIXME throws ???

		}

	}

	public boolean closeResourceRequested(ResourceProvider resource) {

		// while it is using a resource anyone can't close it

		return false;

	}

	public void resourceChanged(ResourceProvider resource) {

		//Do nothing

	}

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA 02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.jexcel.dal.provider;

import java.io.File;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Locale;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import org.apache.commons.io.FilenameUtils;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.DataStoreNotification;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.FileHelper;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.exception.OpenException;

import org.gvsig.fmap.dal.exception.ReadException;

import org.gvsig.fmap.dal.exception.ValidateDataParametersException;

import org.gvsig.fmap.dal.feature.EditableFeature;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.feature.Feature;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureAttributeEmulator;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.exception.PerformEditingException;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureStoreProviderServices;

import org.gvsig.fmap.dal.feature.spi.memory.AbstractMemoryStoreProvider;

import org.gvsig.fmap.dal.resource.file.FileResource;

import org.gvsig.fmap.dal.resource.spi.ResourceConsumer;

import org.gvsig.fmap.dal.resource.spi.ResourceProvider;

import org.gvsig.fmap.dal.serverexplorer.filesystem.FilesystemServerExplorer;

import org.gvsig.fmap.dal.serverexplorer.filesystem.FilesystemServerExplorerParameters;

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.aggregate.MultiPoint;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.fmap.geom.primitive.Point;

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.Coercion;

import org.gvsig.tools.dataTypes.CoercionException;

import org.gvsig.tools.dataTypes.DataType;

import org.gvsig.tools.dataTypes.DataTypesManager;

import org.gvsig.tools.dynobject.exception.DynFieldNotFoundException;

import org.gvsig.tools.exception.BaseException;

import org.gvsig.tools.exception.NotYetImplemented;

import org.gvsig.tools.persistence.PersistentState;

import org.gvsig.tools.persistence.exception.PersistenceException;

import org.gvsig.tools.task.SimpleTaskStatus;

import org.gvsig.tools.task.TaskStatusManager;

import org.gvsig.tools.visitor.VisitCanceledException;

import org.gvsig.tools.visitor.Visitor;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class JExcelStoreProvider extends AbstractMemoryStoreProvider implements

        ResourceConsumer {

    private static final Logger logger = LoggerFactory.getLogger(JExcelStoreProvider.class);

    public static final String NAME = "JExcel";

    public static final String DESCRIPTION = "JExcel file";

    public static final String METADATA_DEFINITION_NAME = NAME;

    private ResourceProvider resource;

    private long counterNewsOIDs = 0;

    private Envelope envelope;

    private boolean need_calculate_envelope = false;

    private SimpleTaskStatus taskStatus;

    private String sheetName = "";

    public JExcelStoreProvider(JExcelStoreParameters parameters,

            DataStoreProviderServices storeServices) throws InitializeException {

        super(

                parameters,

                storeServices,

                FileHelper.newMetadataContainer(METADATA_DEFINITION_NAME)

        );

        TaskStatusManager manager = ToolsLocator.getTaskStatusManager();

        this.taskStatus = manager.createDefaultSimpleTaskStatus("JExcel");

        counterNewsOIDs = 0;

        File file = getJExcelParameters().getFile();

        resource = this.createResource(

                FileResource.NAME,

                new Object[]{file.getAbsolutePath()}

        );

        resource.addConsumer(this);

        initializeFeatureTypes();

    }

    private JExcelStoreParameters getJExcelParameters() {

        return (JExcelStoreParameters) this.getParameters();

    }

    public String getProviderName() {

        return NAME;

    }

    public boolean allowWrite() {

        return false;

    }

    private String getFullFileName() {

        // Usar solo para mostrar mensajes en el logger.

        String s = "(unknow)";

        try {

            s = getJExcelParameters().getFile().getAbsolutePath();

        } catch (Exception e2) {

            s = "(unknow)";

        }

        return s;

    }

    @Override

    public void open() throws OpenException {

        if ( this.data != null ) {

            return;

        }

        this.data = new ArrayList<>();

        resource.setData(new HashMap());

        counterNewsOIDs = 0;

        try {

            loadFeatures();

        } catch (RuntimeException e) {

            logger.warn("Can't load features from JExcel '" + getFullFileName() + "'.", e);

            throw e;

        } catch (Exception e) {

            logger.warn("Can't load features from JExcel '" + getFullFileName() + "'.", e);

            throw new RuntimeException(e);

        }

    }

    public DataServerExplorer getExplorer() throws ReadException {

        DataManager manager = DALLocator.getDataManager();

        FilesystemServerExplorerParameters params;

        try {

            params = (FilesystemServerExplorerParameters) manager

                    .createServerExplorerParameters(FilesystemServerExplorer.NAME);

            params.setRoot(this.getJExcelParameters().getFile().getParent());

            return manager.openServerExplorer(FilesystemServerExplorer.NAME, params);

        } catch (DataException e) {

            throw new ReadException(this.getProviderName(), e);

        } catch (ValidateDataParametersException e) {

            throw new ReadException(this.getProviderName(), e);

        }

    }

    public void performChanges(Iterator deleteds, Iterator inserteds, Iterator updateds, Iterator originalFeatureTypesUpdated) throws PerformEditingException {

        throw new UnsupportedOperationException();

    }

    public boolean closeResourceRequested(ResourceProvider resource) {

        return true;

    }

    public int getOIDType() {

        return DataTypes.LONG;

    }

    public boolean supportsAppendMode() {

        return false;

    }

    public void append(FeatureProvider featureProvider) {

        throw new UnsupportedOperationException();

    }

    public void beginAppend() {

        throw new UnsupportedOperationException();

    }

    public void endAppend() {

        throw new UnsupportedOperationException();

    }

    public void saveToState(PersistentState state) throws PersistenceException {

        throw new NotYetImplemented();

    }

    public void loadFromState(PersistentState state) throws PersistenceException {

        throw new NotYetImplemented();

    }

    public Object createNewOID() {

        return new Long(counterNewsOIDs++);

    }

    protected void initializeFeatureTypes() throws InitializeException {

        try {

            this.open();

        } catch (OpenException e) {

            throw new InitializeException(this.getProviderName(), e);

        }

    }

    public Envelope getEnvelope() throws DataException {

        this.open();

        if ( this.envelope != null ) {

            return this.envelope;

        }

        if ( !this.need_calculate_envelope ) {

            return null;

        }

        FeatureStore fs = this.getFeatureStore();

        FeatureType ft = fs.getDefaultFeatureType();

        FeatureAttributeDescriptor fad = ft.getAttributeDescriptor(ft.getDefaultGeometryAttributeIndex());

        try {

            this.envelope = GeometryLocator.getGeometryManager().createEnvelope(fad.getGeomType().getSubType());

            fs.accept(new Visitor() {

                public void visit(Object obj) throws VisitCanceledException, BaseException {

                    Feature f = (Feature) obj;

                    Geometry geom = f.getDefaultGeometry();

                    if ( geom != null ) {

                        envelope.add(geom.getEnvelope());

                    }

                }

            });

        } catch (BaseException e) {

            logger.warn("Can't calculate the envelope of JExcel file '" + this.getFullName() + "'.", e);

            this.envelope = null;

        }

        this.need_calculate_envelope = false;

        return this.envelope;

    }

    public Object getDynValue(String name) throws DynFieldNotFoundException {

        if ( DataStore.METADATA_ENVELOPE.equalsIgnoreCase(name) ) {

            try {

                return this.getEnvelope();

            } catch (DataException e) {

                return null;

            }

        } else {

            if ( DataStore.METADATA_CRS.equalsIgnoreCase(name) ) {

                IProjection pro = JExcelStoreParameters.getCRS(this.getJExcelParameters());

                if ( pro != null ) {

                    return pro;

                }

            }

        }

        return super.getDynValue(name);

    }

    public void resourceChanged(ResourceProvider resource) {

        this.getStoreServices().notifyChange(

                DataStoreNotification.RESOURCE_CHANGED,

                resource);

    }

    public Object getSourceId() {

        return this.getJExcelParameters().getFile();

    }

    public String getName() {

        String name = this.getJExcelParameters().getFile().getName();

        if( StringUtils.isBlank(this.sheetName) ) {

            return FilenameUtils.getBaseName(name);

        }

        return FilenameUtils.getBaseName(name)+"."+this.sheetName;

    }

    public String getFullName() {

        return this.getJExcelParameters().getFile().getAbsolutePath();

    }

    public ResourceProvider getResource() {

        return resource;

    }

    private class FieldTypeParser {

        public String name = null;

        public int type = DataTypes.STRING;

        public int size = 0;

        public boolean allowNulls = true;

        private String typename = "string";

        FieldTypeParser() {

        }

        private int getType(String value) {

            DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager();

            return dataTypesManager.getType(typename);

        }

        // El formato seria:

        //   name[:typename[:size[:notnull|null]]]

        //   name[__typename[__size[__notnull|null]]]

        //

        public boolean parse(String value) {

            String typename = null;

            String[] ss = null;

            if ( value.contains(":") ) {

                ss = value.split(":");

            } else if ( value.contains("__") ) {

                ss = value.split("__");

            }

            if ( ss == null ) {

                this.name = value;

                return true;

            }

            switch (ss.length) {

            case 4:

                if ( ss[3].length() > 0 ) {

                    if ( "notnull".equalsIgnoreCase(ss[3]) ) {

                        this.allowNulls = false;

                    } else {

                        this.allowNulls = true;

                    }

                }

            case 3:

                if ( ss[2].length() > 0 ) {

                    try {

                        this.size = Integer.parseInt(ss[2]);

                    } catch (Exception ex) {

                        logger.warn("Ignore incorrect field size for field " + value + " in JExcel header of '" + getFullFileName() + "'.", ex);

                    }

                }

            case 2:

                if ( ss[1].length() > 0 ) {

                    this.typename = ss[1];

                    this.type = this.getType(this.typename);

                    if ( this.type == DataTypes.INVALID ) {

                        this.type = DataTypes.STRING;

                        logger.info("Type '" + typename + "' not valid for attribute '" + value + "' in JExcel file '" + getFullFileName() + "'.");

                    }

                }

            case 1:

                this.name = ss[0];

                break;

            }

            if ( this.type != DataTypes.STRING ) {

                this.size = 0;

            }

            return true;

        }

    }

    private EditableFeatureType getFeatureType(String headers[], int automaticTypes[]) {

        EditableFeatureType fType = getStoreServices().createFeatureType(this.getName());

        fType.setHasOID(true);

        DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager();

        FieldTypeParser[] fieldTypes = new FieldTypeParser[headers.length];

        //

        // Calculamos cuales pueden ser los tipos de datos

        //

        for ( int i = 0; i < fieldTypes.length; i++ ) {

            fieldTypes[i] = new FieldTypeParser();

        }

        // Asuminos los tipos pasados por parametro, que se supone

        // son los detectados automaticamente.

        if ( automaticTypes != null ) {

            for ( int i = 0; i < fieldTypes.length && i < automaticTypes.length; i++ ) {

                fieldTypes[i].type = automaticTypes[i];

            }

        }

        // Luego probamos con lo que diga las cabezeras del CVS, sobreescribiendo

        // los tipos anteriores en caso de definirse en la cabezara.

        for ( int i = 0; i < fieldTypes.length; i++ ) {

            if ( !fieldTypes[i].parse(headers[i]) ) {

                continue;

            }

        }

        // Y por ultimo hacemos caso a lo que se haya especificado en los parametros

        // de apertura del JExcel, teniendo esto prioridad sobre todo.

        int[] param_types = JExcelStoreParameters.getFieldTypes(this.getParameters());

        if ( param_types != null ) {

            for ( int i = 0; i < fieldTypes.length && i < param_types.length; i++ ) {

                fieldTypes[i].type = param_types[i];

            }

        }

        int[] param_sizes = JExcelStoreParameters.getFieldSizes(this.getParameters());

        if ( param_sizes != null ) {

            for ( int i = 0; i < param_sizes.length; i++ ) {

                if ( param_sizes[i] > 0 ) {

                    fieldTypes[i].size = param_sizes[i];

                }

            }

        }

        //

        // Una vez ya sabemos los tipos de datos rellenamos el feature-type

        //

        for ( int i = 0; i < fieldTypes.length; i++ ) {

            EditableFeatureAttributeDescriptor fad = fType.add(

                    fieldTypes[i].name,

                    fieldTypes[i].type

            );

            fad.setSize(fieldTypes[i].size);

            fad.setAllowNull(fieldTypes[i].allowNulls);

            if ( fieldTypes[i].type == DataTypes.GEOMETRY

                    && fType.getDefaultGeometryAttributeName() == null ) {

                fType.setDefaultGeometryAttributeName(fieldTypes[i].name);

            }

        }

        String[] pointDimensionNames = JExcelStoreParameters.getPointDimensionNames(this.getParameters());

        if ( pointDimensionNames != null ) {

//            ToPointEvaluaror evaluator = new ToPointEvaluaror(pointDimensionNames);

            PointAttributeEmulator emulator = new PointAttributeEmulator(pointDimensionNames);

            EditableFeatureAttributeDescriptor attr = fType.add("GEOM", DataTypes.GEOMETRY, emulator);

            GeometryManager geommgr = GeometryLocator.getGeometryManager();

            GeometryType gt;

            try {

                if ( emulator.fieldNames != null && emulator.fieldNames.length <= 2 ) {

                	gt = geommgr.getGeometryType(Geometry.TYPES.GEOMETRY, Geometry.SUBTYPES.GEOM2D);

                } else {

                	gt = geommgr.getGeometryType(Geometry.TYPES.GEOMETRY, Geometry.SUBTYPES.GEOM3D);

                }

                attr.setGeometryType(gt);

            } catch (Exception e) {

                logger.warn("Can't set geometry type for the calculated field in JExcel file '" + getFullFileName() + "'.", e);

            }

        }

        return fType;

    }

    static class PointAttributeEmulator implements FeatureAttributeEmulator {

        private static final Logger logger = LoggerFactory.getLogger(PointAttributeEmulator.class);

        private static final int XNAME = 0;

        private static final int YNAME = 1;

        private static final int ZNAME = 2;

        private final GeometryManager geommgr;

        private final String[] fieldNames;

        private final Coercion toDouble;

        private final DataType dataType;

        private int errorcount = 0;

        public PointAttributeEmulator(String[] pointDimensionNames) {

            if ( pointDimensionNames.length > 2 ) {

                this.fieldNames = new String[3];

                this.fieldNames[ZNAME] = pointDimensionNames[2];

            } else {

                this.fieldNames = new String[2];

            }

            this.fieldNames[XNAME] = pointDimensionNames[0];

            this.fieldNames[YNAME] = pointDimensionNames[1];

            this.geommgr = GeometryLocator.getGeometryManager();

            DataTypesManager datatypeManager = ToolsLocator.getDataTypesManager();

            this.toDouble = datatypeManager.getCoercion(DataTypes.DOUBLE);