Custom Excel Export

Custom Excel Export

By default, you can export data from tables in Comflow into an Excel document. Comflow now offers a framework to style Excel documents via a theme and/or a custom rule.

Theme

Theme properties are declared in the sitedef.xml. The theme should declare header and data background color, text color, font name and font size.
There are five standard themes bundled in Comflow. Mustard, forrest, ocean, rose and tan. You only need to declare the property

<Property name="excel.export.theme" value=“mustard”/>

to activate any of the standard themes. Header and data styling is already there by default.

If you want to declare your own theme, the following sitedef properties apply.

Example sitedef properties for theme stealth.

<Property name="excel.export.theme" value="stealth"/> <!— Theme name —>
<— Following properties have the theme name as a key to map each property to the corresponding theme —>
<Property name="excel.export.theme.stealth.header.background.color" value="#5e5e5e"/>
<Property name="excel.export.theme.stealth.header.text.color" value="#ffffff"/>
<Property name="excel.export.theme.stealth.header.font.name" value="Arial"/>
<Property name="excel.export.theme.stealth.header.font.size" value="14"/>
<Property name="excel.export.theme.stealth.data.background.color" value="#d3d3d3"/>
<Property name="excel.export.theme.stealth.data.text.color" value="#000000"/>
<Property name="excel.export.theme.stealth.data.font.name" value="Arial"/>
<Property name="excel.export.theme.stealth.data.font.size" value="12"/>

This setup will give the document its general style for header and data cells according to the theme.
There is no requirement to declare a theme since there is fallback theme.

Custom Rule

Comflow also offers a custom rule implementation where you can define a button anywhere in your workflow, connect an execution point to the button, and add a custom rule to that execution point. The button submit type property must be set to “excelExport”. The rule enables to create your own data to be rendered or use the data instance provided and Comflow provides an API to style cells by setting header and data background color, text color, font name, font size, font style, font weight and border color and thickness. Your custom rule must extend the AbstractExcelExportRule class. For each column in the Excel document you need to create an ExcelColumnInfo object. Any custom styling is set on that object. Header styling is set directly on that object and you use the ColumnDataCondition API to style individual data cells in your document. Cells that are not styled in the rule will get their default style according to the present theme.

Custom Rule Example

/**************************************************************************
 * Copyright (c) 2002, 2022 Corzia AB, Sweden.  All rights reserved.
 * CORZIA PROPRIETARY/CONFIDENTIAL.  Use is subject to license terms.
 * Redistribution in source and binary forms, with or without modification,
 * are not permitted unless explicit written permission is obtained.
 **************************************************************************/
package my.custom.excel.export;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import net.comactivity.core.datainstance.DataInstance;
import net.comactivity.core.omg.base.ProcessData;
import net.comactivity.core.renderer.RendererModel;
import net.comactivity.core.renderer.excel.util.ColumnDataCondition;
import net.comactivity.core.renderer.excel.util.ExcelColumnInfo;
import net.comactivity.core.renderer.excel.util.ExcelUtil;
import net.comactivity.core.renderer.excel.wb.ExcelWorkBook;
import net.comactivity.core.rule.excel.AbstractExcelExportRule;

public class CustomExcelExport extends AbstractExcelExportRule {

	public static final String METADATA_ID = "MAINTENANCE";
	public static final String TABLE = “AnyTable”;
	public static final String SIMPLE_MODEL_ID = "DM64541";
	@Override
	public ExcelWorkBook buildWorkBook(ProcessData processData, DataInstance dataInstance, String segmentId,
			String simpleModelId, RendererModel rendererModel, String excelExtension) {

		Map<Integer, ExcelColumnInfo> columns = new HashMap<Integer, ExcelColumnInfo>();
		// In this example we create two header rows. Make sure every header row has the same no of column captions, in this case 11.
		String[] headerRow1 = new String[] {"Custom", "Excel", "Export", "with", "Two", "Header", "Rows", "", "", "", ""};
		String[] headerRow2 = new String[] {"CaString", "CaInteger", "CaDate", "CaColor", "ConstantCombo", "ConstantCombo2", "ConstantCombo3", "ConstantComboString", "CaDecimal", "CaTime", "CaTimeStamp"};
		// Declare the actual simple model column names.
		String[] columnNames = new String[] {"CaString", "CaInteger", "CaDate", "CaColor", "ConstantCombo", "ConstantCombo2", "ConstantCombo3", "ConstantComboString", "CaDecimal", "CaTime", "CaTimeStamp"};
		// Declare data type for each column.
		String[] dataTypes = new String[] {"CaString", "CaInteger", "CaDate", "CaColor", "CaInteger", "CaInteger", "CaInteger", "CaString", "CaDecimal", "CaTime", "CaTimeStamp"};
		// Declare column widths.
		int[] displayLengths = new int[] {10, 10, 20, 15, 5, 5, 5, 5, 10, 20, 30};

		for (int columnIndex=0; columnIndex<11; columnIndex++) {
			ExcelColumnInfo column = new ExcelColumnInfo(METADATA_ID, TABLE, columnNames[columnIndex], dataTypes[columnIndex], columnIndex, displayLengths[columnIndex], false);
            // You can set a theme on each column if you want to override the current theme.
			column.setTheme(ExcelExportTheme.THEME_ROSE);
			column.addHeader(headerRow1[columnIndex], 0);
			column.addHeader(headerRow2[columnIndex], 1);
			column.setHeaderBackgroundColor("#007ec1", 0, columnIndex);//Either hex “#007ec1” or rgb “0, 126, 193”
			column.setHeaderTextColor("#ffffff", 0, columnIndex);//Either hex “#ffffff or rgb “255, 255, 255”
			column.setHeaderBackgroundColor("0, 126, 193", 1, columnIndex);
			column.setHeaderTextColor("255, 255, 255", 1, columnIndex);

			ColumnDataCondition condition = null;
            // Example conditions
			if (columnIndex == 1) {
				// String condition. If data has value 0, apply condition.
				condition = new ColumnDataCondition();
				condition.setTextColor("255, 0, 0");//Either hex “#ff0000” or rgb “255, 0, 0”
				condition.setLeftBorderStyle(ExcelUtil.BORDER_STYLE_THICK);
				condition.setLeftBorderColor("#ff0000");//Either hex “#ff0000” or rgb “255, 0, 0”
				column.addCondition("0", condition);
			}
			if (columnIndex == 2) {
                // This particular column is set to theme mustard.
                column.setTheme(ExcelExportTheme.THEME_MUSTARD);
				// Date condition.
				condition = new ColumnDataCondition();
				condition.setTextColor("34, 139, 34");//Either hex “#228b22” or rgb “34, 139, 34”
                // Format date to match the present date pattern.
				Date date = parseDate("2020-09-27", column.getPattern(rendererModel.getRendererInfo()));
				column.addCondition(date, condition);
			}
			if (columnIndex == 4) {
                // Set entire column background color and font size.
				column.setCellDataBackgroundColorDefault("171, 205, 239");//Either hex “#abcdef” or rgb “171, 205, 239”
				column.setCellDataFontSizeDefault(20);
			}
			if (columnIndex == 5) {
				// Integer
				condition = new ColumnDataCondition();
				condition.setBorderColor("34, 139, 34");
				condition.setBorderStyle(ExcelUtil.BORDER_STYLE_THICK);
				column.addCondition("2", condition);
			}
			if (columnIndex == 8) {
				// Decimal
				condition = new ColumnDataCondition();
				condition.setBorderColor("34, 139, 34");
				condition.setBorderStyle(ExcelUtil.BORDER_STYLE_THICK);
				BigDecimal value = new BigDecimal("12.565650");
				column.addCondition(value.toString(), condition);
			}
			columns.put(columnIndex, column);
		}
        // Build your workbook and return it.
		ExcelWorkBook workBook = ExcelUtil.buildWorkBook(processData, dataInstance, SIMPLE_MODEL_ID, rendererModel, excelExtension, columns, 2);
		return workBook;
	}
	
	private Date parseDate(String date, String format) {
	     try {
	         return new SimpleDateFormat(format).parse(date);
	     } catch (ParseException e) {
	         return new Date();
	     }
	  }
}