Package 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.

  • Field Details

  • Constructor Details

    • ExcelWriter

      public ExcelWriter(ExcelDocument document)
      Creates a writer for the document and applies initStyles(), enabling metadata writing on POI providers.

  • Method Details

    • 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()
      Indicates if the columns should be expanded to fit the widest value (default is false).

    • setAutofitColumns

      public ExcelWriter setAutofitColumns(boolean autofitColumns)
      Indicates if the columns should be expanded to fit the widest value.

    • isAutofilterColumns

      public boolean isAutofilterColumns()
      Indicates if drop-down filters are added to the first written row (usually the header) on close (default is false).

    • 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()
      Returns the name of the sheet to write, reusing an existing sheet by that name or creating it (default is null).

    • setSheetName

      public ExcelWriter setSheetName(String sheetName)
      Sets the name of the sheet to write, reusing an existing sheet by that name or creating it (default is null).

    • getSheetIndex

      public int getSheetIndex()
      Returns the 0-based index of the sheet to write when no sheet name is set, or the position of a newly created sheet (default is -1, the end).

    • setSheetIndex

      public ExcelWriter setSheetIndex(int sheetIndex)
      Sets the 0-based index of the sheet to write when no sheet name is set, or the position of a newly created sheet (default is -1, the end).

    • getFirstColumnIndex

      public int getFirstColumnIndex()
      Returns the 0-based column that each record's first field is written to (defaults to 0).

    • setFirstColumnIndex

      public ExcelWriter setFirstColumnIndex(int firstColumnIndex)
      Sets the 0-based column that each record's first field is written to (defaults to 0).

    • getFirstRowIndex

      public int getFirstRowIndex()
      Returns the 0-based row that the first record (or the header row) is written to (defaults to 0).

    • setFirstRowIndex

      public ExcelWriter setFirstRowIndex(int firstRowIndex)
      Sets the 0-based row that the first record (or the header row) is written to (defaults to 0).

    • setFieldNamesInFirstRow

      public ExcelWriter setFieldNamesInFirstRow(boolean fieldNamesInFirstRow)
      Description copied from class: AbstractWriter
      Indicates if a header row of field names is written before the first record (default is true); must be called before AbstractWriter.open().
      Overrides:
      setFieldNamesInFirstRow in class AbstractWriter

    • getLargeCellHandler

      public LargeCellHandler getLargeCellHandler()
      Returns how text values longer than MAX_CELL_LENGTH characters are handled (defaults to LargeCellHandler.FAIL).

    • setLargeCellHandler

      public ExcelWriter setLargeCellHandler(LargeCellHandler largeCellHandler)
      Sets how text values longer than MAX_CELL_LENGTH characters are handled; null restores the default LargeCellHandler.FAIL.

    • 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:

    • 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:

    • setDescription

      public ExcelWriter setDescription(String description)
      Description copied from class: Endpoint
      Sets the optional text shown for this endpoint in Endpoint.toString() and exception properties.
      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
      Description copied from class: AbstractWriter
      Writes one row; called first for the header row (see AbstractWriter.isHeaderRow()) when field names are written, then for each record.
      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

      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:

    • 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:

    • 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:

    • addHeaderMetadata

      protected void addHeaderMetadata(Record record) throws Throwable
      Attaches the hyperlinks and cell styles registered for header cells to the header record's fields; called only when metadata writing is enabled.
      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:

    • 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:

    • addDataMetadata

      protected void addDataMetadata(Record record) throws Throwable
      Attaches the hyperlinks and cell styles registered for data cells to the record's fields; called only when metadata writing is enabled.
      Throws:
      Throwable