Write Excel Formatting And Styles

This example demonstrates how to write Excel files with formatting and styles using the DataPipeline. In addition to exporting data, you can apply rich formatting such as bold text, underline, font color, background color, and conditional styling.

With ExcelWriter, styles can be applied to:

1. Header cells
2. Data cells
3. Specific rows
4. Specific columns
5. Specific conditions (e.g., negative numbers)
6. Custom conditions

Java Code

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.FieldLocationPredicate;
import com.northconcepts.datapipeline.excel.ExcelCellStyleDecorator;
import com.northconcepts.datapipeline.excel.ExcelColorPalette;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelWriter;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.json.JsonRecordReader;

public class WriteExcelFormattingAndStyles {

    public static void main(String[] args) {
        DataReader reader = new JsonRecordReader(new File("example/data/input/transactions_data.json"))
                .addRecordBreak("/array/object");
        
        ExcelDocument document = new ExcelDocument();
        ExcelWriter writer = new ExcelWriter(document)
                .setSheetName("transactions")
                .setAutofitColumns(true);
        
        // Add Formatting & Cell Style for Header
        writer.addHeaderCellStyle(FieldLocationPredicate.all(), ExcelCellStyleDecorator.bold()
                .and(ExcelCellStyleDecorator.backgroundColor(ExcelColorPalette.DARK_YELLOW))
                .and(ExcelCellStyleDecorator.fontColor(ExcelColorPalette.LIGHT_GREEN))
                .and(ExcelCellStyleDecorator.underline())
                );

        // Add Formatting & Cell Style for Data
        writer.addDataCellStyle(FieldLocationPredicate.negativeNumber(), ExcelCellStyleDecorator.fontColor(ExcelColorPalette.RED)
                .and(ExcelCellStyleDecorator.italic())
                .and(ExcelCellStyleDecorator.underline()));
        writer.addDataCellStyle(FieldLocationPredicate.evenRowIndex(), ExcelCellStyleDecorator.backgroundColor(ExcelColorPalette.GREY_25_PERCENT));

        Job.run(reader, writer);
        
        document.save(new File("example/data/output/transactions-with-formatting-and-styles.xlsx"));
    }

}

Code Walkthrough

1. JsonRecordReader is created to read data from transactions_data.json.
2. ExcelWriter is initialized with the ExcelDocument.
3. addHeaderCellStyle() is used to apply formatting to header cells, while addDataCellStyle() is used to apply formatting to data cells.
4. FieldLocationPredicate defines the condition to apply cell style to a specific cell based on various inbuilt methods or custom condition.
5. ExcelCellStyleDecorator is used to define the specific styling. Multiple decorators are combined using .and().
6. In this example, header has these many stylings:
   1. Bold font
   2. Dark yellow background color
   3. Light green font color
   4. Underline font
7. Data has these stylings:
   1. Negative numbers:
      1. Red font color
      2. Italic font
      3. underline font
   2. Even row index
      1. Grey background color
8. All these formatting and styles are applied to output excel file.
9. Data are transferred from JsonRecordReader to Excel file using ExcelWriter via Job.run() method. See how to compile and run data pipeline jobs

Output File