gvSIG data provider for MS-Excel

<?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.provider</artifactId>

    <packaging>jar</packaging>

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

    <description>DAL provider for Excel files</description>

    <parent>

        <groupId>org.gvsig</groupId>

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

        <version>1.0.175</version>

    </parent>

    <dependencies>

        <dependency>

            <groupId>commons-io</groupId>

            <artifactId>commons-io</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.apache.commons</groupId>

            <artifactId>commons-lang3</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

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

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.projection.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.file.lib</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.spi</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.metadata.lib.basic.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.geometry.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.timesupport.lib.api</artifactId>

            <scope>compile</scope>

        </dependency>  

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.utils</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.dynform.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.dynform.spi</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.dynform.services</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>com.jgoodies</groupId>

            <artifactId>jgoodies-forms</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

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

            <artifactId>jxl</artifactId>

            <scope>compile</scope>

        </dependency>

        <!-- TESTS -->

        <dependency>

            <groupId>org.gvsig</groupId>

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

            <type>test-jar</type>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.compat.se</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.impl</artifactId>

            <type>test-jar</type>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.projection.cresques.impl</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.timesupport.lib.impl</artifactId>

            <scope>test</scope>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

                <!-- Skip compilation tests -->

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-compiler-plugin</artifactId>

                <executions>

                    <execution>

                        <id>default-testCompile</id>

                        <phase>process-test-sources</phase>

                        <goals>

                            <goal>testCompile</goal>

                        </goals>

                        <configuration>

                            <skip>true</skip>

                        </configuration>

                    </execution>

                </executions>

            </plugin>

        </plugins>

    </build>

</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 org.gvsig.fmap.dal.DataParameters;

import org.gvsig.fmap.dal.DataStoreProvider;

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

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

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

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

import org.gvsig.tools.dynobject.DynObject;

public class JExcelStoreProviderFactory extends AbstractFeatureStoreProviderFactory implements FeatureStoreProviderFactory{

	public JExcelStoreProviderFactory(String name, String description) {

		super(name, description);

	}

	public DataStoreProvider createProvider(DataParameters parameters,

			DataStoreProviderServices providerServices)

			throws InitializeException {

		return new JExcelStoreProvider((JExcelStoreParameters) parameters, providerServices);

	}

	public DynObject createParameters() {

		return new JExcelStoreParameters();

	}

	public int allowCreate() {

		return NO;

	}

	public int allowWrite() {

		return NO;

	}

	public int allowRead() {

		return YES;

	}

	public int hasRasterSupport() {

		return NO;

	}

	public int hasTabularSupport() {

		return YES;

	}

	public int hasVectorialSupport() {

		return YES;

	}

	public int allowMultipleGeometryTypes() {

		return YES;

	}

	public int allowEditableFeatureType() {

		return NO;

	}

}

/**

 * 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.util.Locale;

import org.apache.commons.lang3.StringEscapeUtils;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.FileHelper;

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

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

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

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

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.DataTypesManager;

import org.gvsig.tools.dynobject.DelegatedDynObject;

import org.gvsig.tools.dynobject.DynObject;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class JExcelStoreParameters extends AbstractDataStoreParameters implements

        OpenFeatureStoreParameters, FilesystemStoreParameters {

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

    public static final String PARAMETERS_DEFINITION_NAME = "JExcel";

    private static final String FILE = "file";

    private static final String IGNOREERRORS = "ignoreErrors";

    private static final String CRS = "CRS";

    private static final String FIELDTYPES = "fieldtypes";

    private static final String LOCALE = "locale";

    private static final String SHEET = "sheet";

    private static final String ISFIRSTROWHEADER = "isFirstRowHeadeer";

    private static final String AUTOMATICTYPESDETECTION = "automaticTypesDetection";

    private static final String HEADER = "header";

    private DelegatedDynObject parameters;

    public JExcelStoreParameters() {

        this(PARAMETERS_DEFINITION_NAME);

    }

    protected JExcelStoreParameters(String parametersDefinitionName) {

        this(parametersDefinitionName, JExcelStoreProvider.NAME);

    }

    public JExcelStoreParameters(String parametersDefinitionName, String name) {

        super();

        this.parameters = (DelegatedDynObject) FileHelper.newParameters(parametersDefinitionName);

        this.setDynValue(DataStoreProviderServices.PROVIDER_PARAMTER_NAME, name);

    }

    public String getDataStoreName() {

        return (String) this.getDynValue(DataStoreProviderServices.PROVIDER_PARAMTER_NAME);

    }

    public String getDescription() {

        return this.getDynClass().getDescription();

    }

    protected DelegatedDynObject getDelegatedDynObject() {

        return parameters;

    }

    public boolean isValid() {

        if ( getFileName(this) == null ) {

            return false;

        }

        return true;

    }

    public File getFile() {

        return (File) this.getDynValue(FILE);

    }

    public void setFile(File file) {

        this.setDynValue(FILE, file);

    }

    static IProjection getCRS(DynObject dynobj) {

        return (IProjection) dynobj.getDynValue(CRS);

    }

    static String getFileName(DynObject dynobj) {

        File f = (File) dynobj.getDynValue(FILE);

        if ( f == null ) {

            return null;

        }

        return f.getPath();

    }

    static File getFile(DynObject dynobj) {

        File f = (File) dynobj.getDynValue(FILE);

        return f;

    }

    static Locale getLocale(DynObject dynobj) {

        try {

            String s = (String) dynobj.getDynValue(LOCALE);

            if ( s.trim().length() == 0 ) {

                return null;

            }

            if ( "DEFAULT".equalsIgnoreCase(s.trim()) ) {

                return Locale.getDefault();

            }

            Locale locale = null;

            // locale = Locale.forLanguageTag(s); // Since java 1.7

            String[] ss = s.split("-");

            switch (ss.length) {

            case 1:

                locale = new Locale(ss[0]);

                break;

            case 2:

                locale = new Locale(ss[0], ss[1]);

                break;

            case 3:

            default:

                locale = new Locale(ss[0], ss[1], ss[2]);

                break;

            }

            return locale;

        } catch (Exception ex) {

            logger.warn("Can't get locale from JExcel parameters.", ex);

            return null;

        }

    }

    private static String getDelimiter(String line) {

        String sep = null;

        // Cuiaddo con los ":", los he puesto al final a proposito

        // ya que podian estar en la cadena para separar el size

        // size de cada tipo.

        String seps = ",;-|@#/+$%&!:";

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

            sep = seps.substring(i, 1);

            if ( line.contains(seps.substring(i, 1)) ) {

                break;

            }

            sep = null;

        }

        return sep;

    }

    static String[] getPointDimensionNames(DynObject dynobj) {

        String s = (String) dynobj.getDynValue("point");

        if ( StringUtils.isEmpty(s) ) {

            return null;

        }

        return s.split(",");

    }

    static boolean getIgnoreErrors(DynObject dynobj) {

        Boolean b = (Boolean) dynobj.getDynValue(IGNOREERRORS);

        if ( b == null ) {

            return false;

        }

        return b.booleanValue();

    }

    static int[] getFieldTypes(DynObject dynobj) {

        String s = (String) dynobj.getDynValue(FIELDTYPES);

        if ( StringUtils.isEmpty(s) ) {

            return null;

        }

        String sep = getDelimiter(s);

        if ( sep == null ) {

            return null;

        }

        DataTypesManager dataTypeManager = ToolsLocator.getDataTypesManager();

        String fieldTypeNames[] = s.split("[" + sep + "]");

        int fieldTypes[] = new int[fieldTypeNames.length];

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

            s = fieldTypeNames[i].trim();

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

                s = s.split(":")[0];

            }

            fieldTypes[i] = dataTypeManager.getType(s);

        }

        return fieldTypes;

    }

    static int[] getFieldSizes(DynObject dynobj) {

        String s = (String) dynobj.getDynValue(FIELDTYPES);

        if ( StringUtils.isEmpty(s) ) {

            return null;

        }

        String sep = getDelimiter(s);

        if ( sep == null ) {

            return null;

        }

        String fieldTypeNames[] = s.split("[" + sep + "]");

        int fieldSizes[] = new int[fieldTypeNames.length];

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

            String fieldtype = fieldTypeNames[i].trim();

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

                try {

                    s = fieldtype.split(":")[1];

                    fieldSizes[i] = Integer.parseInt(s);

                } catch (Exception ex) {

                    logger.warn("Can't get size of field " + i + " (" + fieldtype + ").", ex);

                }

            } else {

                fieldSizes[i] = 0;

            }

        }

        return fieldSizes;

    }

    static int getSheetIndex(DynObject dynobj) {

        Integer sheet = (Integer) dynobj.getDynValue(SHEET);

        if ( sheet == null ) {

            return 0;

        }

        return sheet.intValue();

    }

    static String getHeader(DynObject dynobj) {

        String s = (String) dynobj.getDynValue(HEADER);

        s = StringEscapeUtils.unescapeJava(s);

        if ( StringUtils.isBlank(s) ) {

            return null;

        }

        return s;

    }

    static String[] getHeaders(DynObject dynobj) {

        String s = getHeader(dynobj);

        if ( StringUtils.isBlank(s) ) {

            return null;

        }

        String sep = getDelimiter(s);

        if ( sep == null ) {

            // Chungo

            return null;

        }

        String[] ss = s.split("[" + sep + "]");

        return ss;

    }

    static boolean getAutomaticTypesDetection(DynObject dynobj) {

        Boolean b = (Boolean) dynobj.getDynValue(AUTOMATICTYPESDETECTION);

        if ( b == null ) {

            return false;

        }

        return b.booleanValue();

    }

    static boolean isFirstRowHeader(DynObject dynobj) {

        Boolean b = (Boolean) dynobj.getDynValue(ISFIRSTROWHEADER);

        if ( b == null ) {

            return false;

        }

        return b.booleanValue();

    }

}

/**

 * 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 javax.json.JsonObject;

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.json.Json;

import org.gvsig.json.JsonObjectBuilder;

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.logger.FilteredLogger;

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;

@SuppressWarnings("UseSpecificCatch")

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++ ) {