com.toolsverse.etl.connector.excel

Class ExcelXlsxConnector

Object

com.toolsverse.ext.BaseExtension

com.toolsverse.etl.connector.BaseDataSetConnector

com.toolsverse.etl.connector.excel.ExcelXlsxConnector

All Implemented Interfaces:

DataSetConnector<ExcelConnectorParams,ConnectorResult>, ExtensionModule, Serializable, Comparable<ExtensionModule>

public class ExcelXlsxConnector
extends BaseDataSetConnector
implements DataSetConnector<ExcelConnectorParams,ConnectorResult>

Reads and writes Microsoft xlsx files using apache.poi library. Supports data streaming.

Since:

2.0

Version:

2.0

See Also:

Serialized Form

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
(package private) static class	ExcelXlsxConnector.xssfDataType The type of the data value is indicated by an attribute on the cell.
(package private) class	ExcelXlsxConnector.XSSFSheetHandler The Class XSSFSheetHandler.

Field Summary

Fields inherited from class com.toolsverse.etl.connector.BaseDataSetConnector

DEFAULT_IMPORT

Fields inherited from interface com.toolsverse.ext.ExtensionModule

APP, COMMAND, ETL_DRIVER, FORMATTER, IDE_NODE, NO_RANK, WIDGET

Constructor Summary

Constructors

Constructor and Description
ExcelXlsxConnector()

Method Summary

Modifier and Type	Method and Description
boolean	canRead() Checks if connector can read data.
boolean	canWrite() Checks if connector can write data.
void	cleanUp(ExcelConnectorParams params, DataSet dataSet, Driver driver) Cleans up after DataSetConnector populated or persisted DataSet.
ExcelConnectorParams	getDataSetConnectorParams() Gets the DataSetConnectorParams.
String	getDisplayName() Gets the display name.
String	getExt() Gets the file ext.
String	getName() Gets the name.
String	getVendor() Gets the vendor.
String	getVersion() Gets the version of the extension module.
String	getXmlConfigFileName() Gets the path to the xml configuration file for the module.
String	initQueryLabel() The init query label.
void	inlinePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver, DataSetRecord record, int row, int records) Persists data set row.
boolean	isFileConnector() Checks if this is a connector to the file based data source.
boolean	isTextFormat() Checks if this is a connector for the text-based data format, such as JOSN, XML, etc.
ConnectorResult	persist(ExcelConnectorParams params, DataSet dataSet, Driver driver) Persists data set.
ConnectorResult	populate(ExcelConnectorParams params, DataSet dataSet, Driver driver) Populates data set.
void	postPersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) Called when DataSetConnector finished persisting data set.
void	prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) Called when DataSetConnector starts persisting data set.
void	process(org.apache.poi.openxml4j.opc.OPCPackage xlsxPackage, String sheetName, ExcelConnectorParams params, DataSet dataSet, Driver driver) Initiates the processing of the XLS workbook file.
void	processSheet(org.apache.poi.xssf.model.StylesTable styles, org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable strings, InputStream sheetInputStream, ExcelConnectorParams params, DataSet dataSet, Driver driver) Parses and shows the content of one sheet using the specified styles and shared-strings tables.
boolean	supportsNested() Supports nested.
boolean	supportsReadStreaming() Supports read streaming.
boolean	supportsWorksheets() Checks if connector supports worksheets.
boolean	supportsWriteStreaming() Supports write streaming.
ConnectorResult	writeMetaData(ExcelConnectorParams params, DataSet dataSet, Driver driver) Writes meta data.

Methods inherited from class com.toolsverse.etl.connector.BaseDataSetConnector

addValue, basePostPersist, getCommitPolicy, getInputStream, getIoProcessor, getLicensePropertyName, getReader, getType, postPersistWhenEmpty, setCommitPolicy, stream, testConnection

Methods inherited from class com.toolsverse.ext.BaseExtension

compareTo, getConfigFileName, getExtensionRank, getIconPath, getLocalUnitClassPath

Methods inherited from class Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface com.toolsverse.etl.connector.DataSetConnector

supportsReordering, testConnection

Constructor Detail

ExcelXlsxConnector

public ExcelXlsxConnector()

Method Detail

canRead

public boolean canRead()

Description copied from interface: DataSetConnector

Checks if connector can read data.

Specified by:

canRead in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if connector can read data

canWrite

public boolean canWrite()

Description copied from interface: DataSetConnector

Checks if connector can write data.

Specified by:

canWrite in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if connector can write data

cleanUp

public void cleanUp(ExcelConnectorParams params,
                    DataSet dataSet,
                    Driver driver)
             throws Exception

Description copied from interface: DataSetConnector

Cleans up after DataSetConnector populated or persisted DataSet. Called regardless of exception. Usually used to free allocated resources such as streams, handlers, etc.

Specified by:

cleanUp in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Throws:

Exception - in case of any error

getDataSetConnectorParams

public ExcelConnectorParams getDataSetConnectorParams()

Description copied from interface: DataSetConnector

Gets the DataSetConnectorParams.

Specified by:

getDataSetConnectorParams in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

the DataSetConnectorParams

getDisplayName

public String getDisplayName()

Description copied from interface: ExtensionModule

Gets the display name. This name will be used to display module in the various UI controls.

Specified by:

getDisplayName in interface ExtensionModule

Returns:

the display name

getExt

public String getExt()

Description copied from class: BaseDataSetConnector

Gets the file ext.

Specified by:

getExt in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Overrides:

getExt in class BaseDataSetConnector

Returns:

the file ext

getName

public String getName()

Description copied from interface: DataSetConnector

Gets the name.

Specified by:

getName in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

the name

getVendor

public String getVendor()

Description copied from interface: ExtensionModule

Gets the vendor.

Specified by:

getVendor in interface ExtensionModule

Returns:

the vendor

getVersion

public String getVersion()

Description copied from interface: ExtensionModule

Gets the version of the extension module.

Specified by:

getVersion in interface ExtensionModule

Returns:

the version

getXmlConfigFileName

public String getXmlConfigFileName()

Description copied from interface: ExtensionModule

Gets the path to the xml configuration file for the module. The xml configuration file usually contains module specific UI elements, resides inside package and deployed with the code in the jar file.

Specified by:

getXmlConfigFileName in interface ExtensionModule

Overrides:

getXmlConfigFileName in class BaseExtension

Returns:

the path to the configuration name

inlinePersist

public void inlinePersist(ExcelConnectorParams params,
                          DataSet dataSet,
                          Driver driver,
                          DataSetRecord record,
                          int row,
                          int records)
                   throws Exception

Description copied from interface: DataSetConnector

Persists data set row. For example adds line to the text file.

Specified by:

inlinePersist in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

record - the record

row - the row

records - the total number of records

Throws:

Exception - in case of any error

isFileConnector

public boolean isFileConnector()

Description copied from interface: DataSetConnector

Checks if this is a connector to the file based data source.

Specified by:

isFileConnector in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if this is a connector to the file based data source.

isTextFormat

public boolean isTextFormat()

Description copied from interface: DataSetConnector

Checks if this is a connector for the text-based data format, such as JOSN, XML, etc.

Specified by:

isTextFormat in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if this is a connector for the text-based data format.

persist

public ConnectorResult persist(ExcelConnectorParams params,
                               DataSet dataSet,
                               Driver driver)
                        throws Exception

Description copied from interface: DataSetConnector

Persists data set.

Specified by:

persist in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Returns:

the ConnectorResult

Throws:

Exception - in case of any error

populate

public ConnectorResult populate(ExcelConnectorParams params,
                                DataSet dataSet,
                                Driver driver)
                         throws Exception

Description copied from interface: DataSetConnector

Populates data set.

Specified by:

populate in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Returns:

the ConnectorResult

Throws:

Exception - in case of any error

postPersist

public void postPersist(ExcelConnectorParams params,
                        DataSet dataSet,
                        Driver driver)
                 throws Exception

Description copied from interface: DataSetConnector

Called when DataSetConnector finished persisting data set. Never called if there was an exception.

Specified by:

postPersist in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Throws:

Exception - in case of any error

prePersist

public void prePersist(ExcelConnectorParams params,
                       DataSet dataSet,
                       Driver driver)
                throws Exception

Description copied from interface: DataSetConnector

Called when DataSetConnector starts persisting data set.

Specified by:

prePersist in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Throws:

Exception - in case of any error

process

public void process(org.apache.poi.openxml4j.opc.OPCPackage xlsxPackage,
                    String sheetName,
                    ExcelConnectorParams params,
                    DataSet dataSet,
                    Driver driver)
             throws IOException,
                    org.apache.poi.openxml4j.exceptions.OpenXML4JException,
                    ParserConfigurationException,
                    org.xml.sax.SAXException

Initiates the processing of the XLS workbook file.

Parameters:

xlsxPackage - the xlsx package

sheetName - the sheet name

params - the params

dataSet - the data set

driver - the driver

Throws:

IOException - Signals that an I/O exception has occurred.

org.apache.poi.openxml4j.exceptions.OpenXML4JException - the open xm l4 j exception

ParserConfigurationException - the parser configuration exception

org.xml.sax.SAXException - the sAX exception

processSheet

public void processSheet(org.apache.poi.xssf.model.StylesTable styles,
                         org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable strings,
                         InputStream sheetInputStream,
                         ExcelConnectorParams params,
                         DataSet dataSet,
                         Driver driver)
                  throws IOException,
                         ParserConfigurationException,
                         org.xml.sax.SAXException

Parses and shows the content of one sheet using the specified styles and shared-strings tables.

Parameters:

styles - the styles

strings - the strings

sheetInputStream - the sheet input stream

params - the params

dataSet - the data set

driver - the driver

Throws:

IOException - Signals that an I/O exception has occurred.

ParserConfigurationException - the parser configuration exception

org.xml.sax.SAXException - the sAX exception

writeMetaData

public ConnectorResult writeMetaData(ExcelConnectorParams params,
                                     DataSet dataSet,
                                     Driver driver)
                              throws Exception

Description copied from interface: DataSetConnector

Writes meta data. For example along with delimited text file the DataSetConnector can create a xml file with the same name which contains field definitions.

Specified by:

writeMetaData in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Parameters:

params - the DataSetConnectorParams

dataSet - the data set

driver - the driver

Returns:

the ConnectorResult

Throws:

Exception - in case of any error

initQueryLabel

public String initQueryLabel()

Description copied from interface: DataSetConnector

The init query label. If not empty the init query, for example XML XQuery, will be enabled and label in editor will be set to the value of this field

Specified by:

initQueryLabel in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

the string

supportsReadStreaming

public boolean supportsReadStreaming()

Description copied from interface: DataSetConnector

Supports read streaming.

Specified by:

supportsReadStreaming in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if successful

supportsWriteStreaming

public boolean supportsWriteStreaming()

Description copied from interface: DataSetConnector

Supports write streaming.

Specified by:

supportsWriteStreaming in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if successful

supportsNested

public boolean supportsNested()

Description copied from interface: DataSetConnector

Supports nested.

Specified by:

supportsNested in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Returns:

true, if connector supports nested data sets

supportsWorksheets

public boolean supportsWorksheets()

Description copied from interface: DataSetConnector

Checks if connector supports worksheets.

Specified by:

supportsWorksheets in interface DataSetConnector<ExcelConnectorParams,ConnectorResult>

Overrides:

supportsWorksheets in class BaseDataSetConnector

Returns:

true, if connector supports worksheets