Excel Report Via Simple Java Annotations

Introduction

This is the second part of the two part series on creating Excel spreadsheets from objects in generic collection classes. In the first part  Excel read-writeable Reports through Generics, Reflection and Apache POI we discussed the core aspects of creating the Excel spreadsheets through the Apache POI library as well as leveraging the benefits of generics and reflection.  In this second part we are adding more control of what is persisted to the spread sheet and how it is displayed through the use of simple annotations.

Annotations

Annotations became available in the Java SDK in version 1.5.  They have become very popular with good reason.  They provide efficiently add function to programs.  We  are adding runtime annotations that will be able to analyse our generic collection for reporting.

We are adding the following two annotations:

@ExcelReport

 Use the @ExcelReport annotation to designate the class as available for excel reporting.  This annotation immediately marks all accessor methods in the class available for persistence to the Excel spreadsheet.  The @ExcelColumn annotation, described below, allows the user to refine the reporting.

@ExcelReport Attributes

Attribute Required Description
reportName true The name of the Excel file the report is written to.
 Example

@ExcelReport(reportName="test.xls")
public class TestClass { … }

 

@ExcelColumn

The @ExcelColumn annotation can be used in a class that has been annotated by @ExcelReport .  @ExcelColumn allows the user to ignore an accessor method or provide a custom description of the field the accessor returns.  The getter method must have a symantically equivlent field.  So for instance if the accessor is getFoo () there must be a field foo.

@ExcelColumn Attributes

Attribute Required Description
ignore false false by default.  This attribute will make the accessor unavailable to reporting
label false The value for label will be used as the column heading in the spreadsheet
 Example

@ExcelColumn(label="Test Name")
    public String getName() {…}

or

@ExcelColumn(ignore=true)
    public Double getDoubleField() {…}

The code for the to Annotations are below:

@Retention(RetentionPolicy.RUNTIME)
@Target(value=ElementType.TYPE)
public @interface ExcelReport {
	String reportName();
}
@Retention(RetentionPolicy.RUNTIME)
@Target(value=ElementType.METHOD)
public @interface ExcelColumn {
	boolean ignore() default false;
	String label() default "";
}

Expand the section below for an example of a domain class with Excel Annotations:

package com.persistent.utils.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

@ExcelReport(reportName="test.xls")
public class TestClass {
	private String name;
	private String path;
	private String directory;
	private Integer containedFiles;
	private Double doubleField;

	public TestClass() {
	}

	public TestClass(String name, String path, String directory,
			Integer containedFiles, Double doubleField) {
		super();
		this.name = name;
		this.path = path;
		this.directory = directory;
		this.containedFiles = containedFiles;
		this.doubleField = doubleField;
	}

	@ExcelColumn(label="Test Name")
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@ExcelColumn(label="Path")
	public String getPath() {
		return path;
	}

	public void setPath(String path) {
		this.path = path;
	}

	@ExcelColumn(label="Directory")
	public String getDirectory() {
		return directory;
	}

	public void setDirectory(String directory) {
		this.directory = directory;
	}

	@ExcelColumn(label="Number of Files in Directory")
	public Integer getContainedFiles() {
		return containedFiles;
	}

	public void setContainedFiles(Integer containedFiles) {
		this.containedFiles = containedFiles;
	}

	@ExcelColumn(ignore=true)
	public Double getDoubleField() {
		return doubleField;
	}

	public void setDoubleField(Double doubleField) {
		this.doubleField = doubleField;
	}

	@Override
	public String toString() {
		return "TestClass [containedFiles=" + containedFiles + ", directory="
				+ directory + ", doubleField=" + doubleField + ", name=" + name
				+ ", path=" + path + "]";
	}

	public static List<TestClass> getTestData() {
		List<TestClass> testClasses = new ArrayList<TestClass>();

		String currentDirectory = System.getProperty("user.dir");
		File currentDirectoryFile = new File(currentDirectory);
		File[] files = currentDirectoryFile.listFiles();
		double dbl = 0;
		for (File file : files) {
			TestClass testClass = null;
			if (file.isDirectory()) {
				testClass = new TestClass(file.getName(), file.getPath(),
						"directory", file.list().length,dbl++);
			} else {
				testClass = new TestClass(file.getName(), file.getPath(),
						"file", 0,dbl++);
			}
			testClasses.add(testClass);
		}
		return testClasses;
	}

	public static void main(String[] args) {
		String currentDirectory = System.getProperty("user.dir");
		File currentDirectoryFile = new File(currentDirectory);
		File[] files = currentDirectoryFile.listFiles();

		System.out.println("Is there anything in the list:" + files.length);
		double dbl = 0;
		for (File file : files) {
			System.out.println(file.getName());
			System.out.println(file.getAbsoluteFile());
			System.out.println(file.getPath());
			if (file.isDirectory()) {
				System.out.println("Directory");
				new TestClass(file.getName(), file.getPath(),
						"directory", file.list().length,dbl++);
			} else {
				new TestClass(file.getName(), file.getPath(),
						"file", 0,dbl++);
			}
		}
	}

}

 

Annotations and Reflection

The change over the first implementation is the annotations.  This information is resolved in the reflection process just as before but now we have that extra mark-up information to aid in presentation.  Internally the report program does some simple manipulations and comparisons of the fields with the collected annotated methods.

Here is the method that extracts the information from the annotations:

	private <T> void processAnnotations(T object) {
		Class<?> clazz = object.getClass();
		ExcelReport reportAnnotation = (ExcelReport) clazz.getAnnotation(ExcelReport.class);
		logger.info("Report Name  : " + reportAnnotation.reportName());
		String reportName = reportAnnotation.reportName();
		if((reportName==null)||(reportName.trim().length()<1)){
			// should never get here.
			logger.error("Invalid Worksheet Name");
		}
		setWorkbookName(reportAnnotation.reportName());
		for (Method method : clazz.getMethods()) {
			ExcelColumn excelColumn = method.getAnnotation(ExcelColumn.class);
			if ((excelColumn != null) && !excelColumn.ignore()) {
				getFieldLabelMap().put(excelColumn.label(),method.getName());
				getOrderLabels().add(excelColumn.label());
				logger.info("Annotation on method: " + method.getName());
				logger.info("Ignore: " + excelColumn.ignore() + "  label: "
						+ excelColumn.label());
			}
		}
	}

 Additionally the Apache POI software does allow one to go deeply into the Excel standard markup to add style to cells here we have added some style for the column headers:

 

private CellStyle createColumnHeaderCellStyle() {
	CellStyle cellStyle = getWorkbook().createCellStyle();
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	cellStyle.setFillBackgroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	return cellStyle;
}

Ultimately are newly marked up domain object produces a spreadsheet that looks like the following:

Spreadsheet

 

Installing and Running

The program requires the following jar files:

  • poi-3.5-FINAL-20090928.jar
  • poi-ooxml-3.5-FINAL-20090928.jar
  • log4j-1.2.15.jar
  • JUnit 4

The POI file are available here.  Log4J is available here.

The source code for this project is here.

 The program runs as a JUnit test from the class AnnotatedExcelReportTest located under the test directory.

About The Author

David Sells is a computer consultant in Toronto, Ontario who specializes in Java Enterprise Development. He has  provided innovative solutions for clients including: IBM, Goldman Sachs, Merrill Lynch and Deutsche Bank.

He holds the following certifications:

  • Sun Certified Enterprise Architect for the Java Platform, Enterprise Edition 5 (2009)
  • Sun Certified Web Component Developer
  • Sun Certified Java Programmer.

Contact: david@persistentdesigns.com

 

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>