Read Excel Formatting And Styles

This example demonstrates how to read excel cell styles embedded in Excel worksheets using DataPipeline. In addition to retrieving cell values, you can access metadata such as bold, italic, underline, font & background color, wrap text. This feature is disabled by default and can be enabled by setting readMetadata flag as true to ExcelReader.

Using ExcelReader, DataPipeline can read both the cell value and the formatting & cell style. This is useful for data ingestion pipelines, reporting systems, data lineage, or spreadsheet-driven workflows.

Java Code

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelDocument.ProviderType;
import com.northconcepts.datapipeline.excel.ExcelReader;
import com.northconcepts.datapipeline.job.Job;

public class ReadExcelFormattingAndStyles {

    public static void main(String[] args) throws Throwable {
        ExcelDocument document = new ExcelDocument(ProviderType.POI_XSSF_SAX)
                .open(new File("example/data/input/transactions-with-formatting-and-styles.xlsx"));
        DataReader reader = new ExcelReader(document)
                .setAutoCloseDocument(true)
                .setFieldNamesInFirstRow(true)
                .setReadMetadata(true); // only read excel cell styles if this flag is true

        // read from excel and write all excel records & cell styles on console.
        Job.run(reader, new StreamWriter(System.out).setFormat(StreamWriter.Format.RECORD_WITH_SESSION_PROPERTIES));
    }
}

Code Walkthrough

1. First, ExcelDocument is created to open transactions-with-formatting-and-styles.xlsx, which contains 
2. ExcelDocument.ProviderType indicates an Excel provider which is used to open the input Excel file. For this example POI_XSSF_SAX provider is selected.
3. Set readMetadata as true, then only ExcelReader will read these formatting & styles
4. ExcelDocument.open() opens the specified File or InputStream.
5. ExcelReader is created to read from the input Excel file.
6. Data are transferred from ExcelReader to the console via Job.run() method. See how to compile and run data pipeline jobs.
7. StreamWriter.Format.RECORD_WITH_SESSION_PROPERTIES is used to print all the session properties on console.

Console Output

-----------------------------------------------
0 - Record {
    0:[transactionId]:INT=[1001]:Integer
    1:[customerName]:STRING=[Alice Johnson]:String
    2:[category]:STRING=[Refund]:String
    3:[amount]:DOUBLE=[-120.5]:Double
    4:[transactionDate]:STRING=[2025-01-05]:String
}

Record Session Properties:

Field Session Properties:
    [amount.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0.00, excelFont=ExcelFont [bold=false, italic=true, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=2, red=255, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=64, red=null, green=null, blue=null]]]:ExcelCellStyle
-----------------------------------------------
1 - Record {
    0:[transactionId]:INT=[1002]:Integer
    1:[customerName]:STRING=[Bob Smith]:String
    2:[category]:STRING=[Purchase]:String
    3:[amount]:INT=[250]:Integer
    4:[transactionDate]:STRING=[2025-01-06]:String
}

Record Session Properties:

Field Session Properties:
    [transactionId.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [customerName.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [category.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [amount.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0.00, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [transactionDate.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
-----------------------------------------------
2 - Record {
    0:[transactionId]:INT=[1003]:Integer
    1:[customerName]:STRING=[Charlie Brown]:String
    2:[category]:STRING=[Refund]:String
    3:[amount]:INT=[-75]:Integer
    4:[transactionDate]:STRING=[2025-01-07]:String
}

Record Session Properties:

Field Session Properties:
    [amount.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0.00, excelFont=ExcelFont [bold=false, italic=true, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=2, red=255, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=64, red=null, green=null, blue=null]]]:ExcelCellStyle
-----------------------------------------------
3 - Record {
    0:[transactionId]:INT=[1004]:Integer
    1:[customerName]:STRING=[Diana Prince]:String
    2:[category]:STRING=[Purchase]:String
    3:[amount]:DOUBLE=[430.25]:Double
    4:[transactionDate]:STRING=[2025-01-08]:String
}

Record Session Properties:

Field Session Properties:
    [transactionId.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [customerName.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [category.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [amount.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=0.00, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
    [transactionDate.com.northconcepts.datapipeline.excel.ExcelCellStyle]=[ExcelCellStyle [pattern=General, excelFont=ExcelFont [bold=false, italic=false, underline=false, fontColor=ExcelColor [excelColorPaletteIndex=0, red=0, green=0, blue=0]], wrapText=false, backgroundColor=ExcelColor [excelColorPaletteIndex=22, red=192, green=192, blue=192]]]:ExcelCellStyle
-----------------------------------------------
4 records