com.northconcepts.datapipeline.excel

Class ExcelWriter

java.lang.Object

com.northconcepts.datapipeline.core.DataObject

com.northconcepts.datapipeline.core.Endpoint

com.northconcepts.datapipeline.core.DataEndpoint

com.northconcepts.datapipeline.core.DataWriter

com.northconcepts.datapipeline.core.AbstractWriter

com.northconcepts.datapipeline.excel.ExcelWriter

public class ExcelWriter
extends AbstractWriter

Writes records to a Microsoft Excel document.

Nested Class Summary

Nested classes/interfaces inherited from class com.northconcepts.datapipeline.core.DataEndpoint

DataEndpoint.State

Field Summary

Fields

Modifier and Type	Field and Description
static int	MAX_CELL_LENGTH
static int	MAX_CELL_LENGTH_INDEX

Fields inherited from class com.northconcepts.datapipeline.core.AbstractWriter

currentRecord

Fields inherited from class com.northconcepts.datapipeline.core.DataEndpoint

lastRecord, PRODUCT, PRODUCT_VERSION, VENDOR, XML_INPUT_FACTORY_KEY

Fields inherited from class com.northconcepts.datapipeline.core.Endpoint

BUFFER_SIZE, captureElapsedTime, DEFAULT_READ_BUFFER_SIZE

Fields inherited from class com.northconcepts.datapipeline.core.DataObject

id, log, name, TIMESTAMP_FORMAT

Constructor Summary

Constructors

Constructor and Description
ExcelWriter(ExcelDocument document)

Method Summary

Modifier and Type	Method and Description
ExcelWriter	addDataCellStyle(FieldLocationPredicate predicate, ExcelCellStyleDecorator decorator) Adds cell styles to data cells matching the specified predicate.
ExcelWriter	addDataHyperlink(FieldLocationPredicate predicate, ExcelHyperlinkFunction function) Adds hyperlinks to data cells matching the specified predicate.
protected void	addDataMetadata(Record record)
DataException	addExceptionProperties(DataException exception) Adds this endpoint's current state to a DataException.
ExcelWriter	addHeaderCellStyle(FieldLocationPredicate predicate, ExcelCellStyleDecorator decorator) Adds cell styles to header cells matching the specified predicate.
ExcelWriter	addHeaderHyperlink(FieldLocationPredicate predicate, ExcelHyperlinkFunction function) Adds hyperlinks to header cells matching the specified predicate.
protected void	addHeaderMetadata(Record record)
void	close() Indicates that this endpoint has finished reading or writing.
ExcelDocument	getDocument()
int	getFirstColumnIndex()
int	getFirstRowIndex()
Integer	getFreezeColumns() Returns the number of columns to freeze at the left side of the Excel sheet. Supported ProviderTypes are:
Integer	getFreezeRows() Returns the number of rows to freeze at the top of the Excel sheet. Supported ProviderTypes are:
LargeCellHandler	getLargeCellHandler()
int	getSheetIndex()
String	getSheetName()
void	initStyles() Initializes default cell style formats for different field types.
boolean	isAutofilterColumns()
boolean	isAutofitColumns()
boolean	isWriteMetadata() Returns whether Excel metadata (such as hyperlinks and cell styles) should be written.
void	open() Makes this endpoint ready for reading or writing.
ExcelWriter	setAutoFilterColumns(boolean autoFilterColumns) Indicates if a row containing a drop-down filter with values from each column should be added above the data rows.
ExcelWriter	setAutofitColumns(boolean autofitColumns) Indicates if the columns should be expanded to fit the widest value.
ExcelWriter	setDataFormat(FieldType fieldType, String dataFormat) Sets the data format for cells containing values of the specified field type.
ExcelWriter	setDescription(String description)
ExcelWriter	setFieldNamesInFirstRow(boolean fieldNamesInFirstRow)
ExcelWriter	setFirstColumnIndex(int firstColumnIndex)
ExcelWriter	setFirstRowIndex(int firstRowIndex)
ExcelWriter	setFreezeColumns(Integer freezeColumns) Sets the number of columns to freeze at the left side of the Excel sheet. Supported ProviderTypes are:
ExcelWriter	setFreezeRows(Integer freezeRows) Sets the number of rows to freeze at the top of the Excel sheet.
ExcelWriter	setLargeCellHandler(LargeCellHandler largeCellHandler)
ExcelWriter	setSheetIndex(int sheetIndex)
ExcelWriter	setSheetName(String sheetName)
ExcelWriter	setStyleFormat(FieldType fieldType, String dataFormat) Deprecated. use setDataFormat(FieldType, String) instead. Sets the data format for cells containing values of the specified field type.
ExcelWriter	setWriteMetadata(boolean writeMetadata) Sets whether Excel metadata (such as hyperlinks and cell styles) should be written.
protected void	writeRecord(Record record)

Methods inherited from class com.northconcepts.datapipeline.core.AbstractWriter

createHeaderRecord, isFieldNamesInFirstRow, isHeaderRow, write, writeImpl

Methods inherited from class com.northconcepts.datapipeline.core.DataWriter

available, getNestedEndpoint, getNestedWriter, getRootEndpoint, getRootWriter, getWriter

Methods inherited from class com.northconcepts.datapipeline.core.DataEndpoint

decrementRecordCount, enableJmx, getLastRecord, getRecordCount, getRecordCountAsBigInteger, getRecordCountAsString, incrementRecordCount, isRecordCountBigInteger, resetRecordCount, toString

Methods inherited from class com.northconcepts.datapipeline.core.Endpoint

addElapsedtime, assertClosed, assertNotOpened, assertOpened, finalize, getClosedOn, getDescription, getElapsedTime, getElapsedTimeAsString, getOpenedOn, getOpenElapsedTime, getOpenElapsedTimeAsString, getSelfTime, getSelfTimeAsString, getState, isCaptureElapsedTime, isClosed, isOpen, setCaptureElapsedTime

Methods inherited from class com.northconcepts.datapipeline.core.DataObject

exception, exception, exception, getId, getName, resetID

Methods inherited from class java.lang.Object

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

Field Detail

MAX_CELL_LENGTH

public static final int MAX_CELL_LENGTH

See Also:

Constant Field Values

MAX_CELL_LENGTH_INDEX

public static final int MAX_CELL_LENGTH_INDEX

See Also:

Constant Field Values

Constructor Detail

ExcelWriter

public ExcelWriter(ExcelDocument document)

Method Detail

initStyles

public void initStyles()

Initializes default cell style formats for different field types. This method is called automatically and sets formats like "m/d/yy" for dates, "0.00" for decimals, etc.

addExceptionProperties

public DataException addExceptionProperties(DataException exception)

Description copied from class: Endpoint

Adds this endpoint's current state to a DataException. Since this method is called whenever an exception is thrown, subclasses should override it to add their specific information.

Overrides:

addExceptionProperties in class AbstractWriter

isAutofitColumns

public boolean isAutofitColumns()

setAutofitColumns

public ExcelWriter setAutofitColumns(boolean autofitColumns)

Indicates if the columns should be expanded to fit the widest value.

isAutofilterColumns

public boolean isAutofilterColumns()

setAutoFilterColumns

public ExcelWriter setAutoFilterColumns(boolean autoFilterColumns)

Indicates if a row containing a drop-down filter with values from each column should be added above the data rows.

getSheetName

public String getSheetName()

setSheetName

public ExcelWriter setSheetName(String sheetName)

getSheetIndex

public int getSheetIndex()

setSheetIndex

public ExcelWriter setSheetIndex(int sheetIndex)

getFirstColumnIndex

public int getFirstColumnIndex()

setFirstColumnIndex

public ExcelWriter setFirstColumnIndex(int firstColumnIndex)

getFirstRowIndex

public int getFirstRowIndex()

setFirstRowIndex

public ExcelWriter setFirstRowIndex(int firstRowIndex)

setFieldNamesInFirstRow

public ExcelWriter setFieldNamesInFirstRow(boolean fieldNamesInFirstRow)

Overrides:

setFieldNamesInFirstRow in class AbstractWriter

getLargeCellHandler

public LargeCellHandler getLargeCellHandler()

setLargeCellHandler

public ExcelWriter setLargeCellHandler(LargeCellHandler largeCellHandler)

getFreezeRows

public Integer getFreezeRows()

Returns the number of rows to freeze at the top of the Excel sheet.

Supported ProviderTypes are:

 - ExcelDocument.ProviderType.POI,
 - ExcelDocument.ProviderType.POI_XSSF,
 - ExcelDocument.ProviderType.POI_SXSSF,
 - ExcelDocument.ProviderType.POI_XSSF_SAX
 

setFreezeRows

public ExcelWriter setFreezeRows(Integer freezeRows)

Sets the number of rows to freeze at the top of the Excel sheet.

Supported ProviderTypes are:

 - ExcelDocument.ProviderType.POI,
 - ExcelDocument.ProviderType.POI_XSSF,
 - ExcelDocument.ProviderType.POI_SXSSF,
 - ExcelDocument.ProviderType.POI_XSSF_SAX
 

getFreezeColumns

public Integer getFreezeColumns()

Returns the number of columns to freeze at the left side of the Excel sheet.

Supported ProviderTypes are:

 - ExcelDocument.ProviderType.POI,
 - ExcelDocument.ProviderType.POI_XSSF,
 - ExcelDocument.ProviderType.POI_SXSSF,
 - ExcelDocument.ProviderType.POI_XSSF_SAX
 

setFreezeColumns

public ExcelWriter setFreezeColumns(Integer freezeColumns)

Sets the number of columns to freeze at the left side of the Excel sheet.

Supported ProviderTypes are:

 - ExcelDocument.ProviderType.POI,
 - ExcelDocument.ProviderType.POI_XSSF,
 - ExcelDocument.ProviderType.POI_SXSSF,
 - ExcelDocument.ProviderType.POI_XSSF_SAX
 

isWriteMetadata

public boolean isWriteMetadata()

Returns whether Excel metadata (such as hyperlinks and cell styles) should be written. When enabled, metadata from field session properties is written to the Excel file.

Returns:

true if metadata writing is enabled, false otherwise

See Also:

ExcelFieldMetadata

setWriteMetadata

public ExcelWriter setWriteMetadata(boolean writeMetadata)

Sets whether Excel metadata (such as hyperlinks and cell styles) should be written. When enabled, metadata from field session properties is written to the Excel file.

Parameters:

writeMetadata - true to enable metadata writing, false to disable (default is false)

Returns:

this ExcelWriter instance for method chaining

See Also:

ExcelFieldMetadata

setDescription

public ExcelWriter setDescription(String description)

Overrides:

setDescription in class Endpoint

open

public void open()

Description copied from class: DataEndpoint

Makes this endpoint ready for reading or writing.

Overrides:

open in class AbstractWriter

close

public void close()

Description copied from class: DataEndpoint

Indicates that this endpoint has finished reading or writing.

Overrides:

close in class DataEndpoint

getDocument

public ExcelDocument getDocument()

writeRecord

protected void writeRecord(Record record)
                    throws Throwable

Specified by:

writeRecord in class AbstractWriter

Throws:

Throwable

setStyleFormat

@Deprecated
public ExcelWriter setStyleFormat(FieldType fieldType,
                                               String dataFormat)

Deprecated. use setDataFormat(FieldType, String) instead.

Sets the data format for cells containing values of the specified field type.

Parameters:

fieldType - the field type to set the data format for

dataFormat - the Excel data format string (e.g. "m/d/yy" for dates, "0.00" for decimals)

Returns:

this ExcelWriter instance for method chaining

See Also:

setDataFormat(FieldType, String)

setDataFormat

public ExcelWriter setDataFormat(FieldType fieldType,
                                 String dataFormat)

Sets the data format for cells containing values of the specified field type.

Parameters:

fieldType - the field type to set the data format for

dataFormat - the Excel data format string (e.g. "m/d/yy" for dates, "0.00" for decimals)

Returns:

this ExcelWriter instance for method chaining

See Also:

FieldType

addHeaderHyperlink

public ExcelWriter addHeaderHyperlink(FieldLocationPredicate predicate,
                                      ExcelHyperlinkFunction function)

Adds hyperlinks to header cells matching the specified predicate. Automatically enables metadata writing and field names in the first row.

Parameters:

predicate - the predicate to match header cell locations, or null to apply to all headers

function - the function that creates hyperlinks based on field location

Returns:

this ExcelWriter instance for method chaining

See Also:

FieldLocationPredicate, ExcelHyperlinkFunction

addHeaderCellStyle

public ExcelWriter addHeaderCellStyle(FieldLocationPredicate predicate,
                                      ExcelCellStyleDecorator decorator)

Adds cell styles to header cells matching the specified predicate. Automatically enables metadata writing and field names in the first row.

Parameters:

predicate - the predicate to match header cell locations, or null to apply to all headers

decorator - the decorator that applies cell styles based on field location

Returns:

this ExcelWriter instance for method chaining

See Also:

FieldLocationPredicate, ExcelCellStyleDecorator

addHeaderMetadata

protected void addHeaderMetadata(Record record)
                          throws Throwable

Throws:

Throwable

addDataHyperlink

public ExcelWriter addDataHyperlink(FieldLocationPredicate predicate,
                                    ExcelHyperlinkFunction function)

Adds hyperlinks to data cells matching the specified predicate. Automatically enables metadata writing.

Parameters:

predicate - the predicate to match data cell locations, or null to apply to all data cells

function - the function that creates hyperlinks based on field location

Returns:

this ExcelWriter instance for method chaining

See Also:

FieldLocationPredicate, ExcelHyperlinkFunction

addDataCellStyle

public ExcelWriter addDataCellStyle(FieldLocationPredicate predicate,
                                    ExcelCellStyleDecorator decorator)

Adds cell styles to data cells matching the specified predicate. Automatically enables metadata writing.

Parameters:

predicate - the predicate to match data cell locations, or null to apply to all data cells

decorator - the decorator that applies cell styles based on field location

Returns:

this ExcelWriter instance for method chaining

See Also:

FieldLocationPredicate, ExcelCellStyleDecorator

addDataMetadata

protected void addDataMetadata(Record record)
                        throws Throwable

Throws:

Throwable