Java : Excel : XLS : Example of excel generation (Apache POI)

Hello,

After my previous article Example of mail generation (MSGParser, Apache POI-HSMF) concerning the generation of a mail file on server side, I would expose you an example of XLS (Excel) generation by using the Apache POI.

Introduction
The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. The Apache POI project is celebrating its 10th anniversary.

The notice in Apache POI jar precises:

This product contains the DOM4J library (http://www.dom4j.org).
Copyright 2001-2005 (C) MetaStuff, Ltd. All Rights Reserved.

This product contains parts that were originally based on software from BEA.
Copyright (c) 2000-2003, BEA Systems, <http://www.bea.com/>.

This product contains W3C XML Schema documents. Copyright 2001-2003 (c)
World Wide Web Consortium (Massachusetts Institute of technology, European
Research Consortium for Informatics and Mathematics, Keio University)

This product contains the Piccolo XML Parser for Java
(http://piccolo.sourceforge.net/). Copyright 2002 Yuval Oren.

This product contains the chunks_parse_cmds.tbl file from the vsdump program.
Copyright (C) 2006-2007 Valek Filippov (frob@dl.ru)

…so, in our example we will export data Dvd entities in 2 formats : CSV and Excel (XLS). For the first format, we use directly
the java standard class (FileWriter), but for the second format, we use the Apache library POI.

Steps by steps

Create a new project named test_poi with the following libraries in test_poi\lib folder and added in the project’s build path:

• poi-3.10-FINAL.jar the POI library,
• log4j-1.2.15.jar for the logging needs,

…the Eclipse project will have the following structure:

…and the test_poi\src source folder with the log4j.properties:

! log4j.debug
log4j.rootCategory=INFO , console

! WRITE TO CONSOLE (stdout or stderr)
log4j.appender.console.Threshold=INFO
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%d{HH:mm:ss}]%-5p - %m (%F:%L) %n

!-----------------------------------------------------------------------------
!                       PATTERN FORMATS GLOSSARY
!-----------------------------------------------------------------------------
! %n - newline
! %m - your log message
! %p - message priority (FATAL, ERROR, WARN, INFO, DEBUG of custom)
! %r - millisecs since program started running
! %% - percent sign in output
!
! ------------------------- SOME MORE CLUTTER IN YOUR LOG --------------------
! %c - name of your category (logger), %c{2} will outputs last two components
! %t - name of current thread
! %x - Nested Diagnostic Context (NDC) (you supply it!)
!
! ------------------------- SLOW PERFORMANCE FORMATS -------------------------
! %d - date and time, also %d{ISO8601}, %d{DATE}, %d{ABSOLUTE},
!			%d{HH:mm:ss, SSS}, %d{dd MMM yyyy HH:mm:ss,SSS} and so on 
! %l - Shortcut for %F%L%C%M
! %F - Java source file name
! %L - Java source line number
! %C - Java class name, %C{1} will output the last one component
! %M - Java method name
!
! ------------------------- FORMAT MODIFIERS ---------------------------------
! %-any_letter_above - Left-justify in min. width (default is right-justify)
! %20any_letter_above - 20 char. min. width (pad with spaces if regd.)
! %.30any_letter_above - 30 char. max. width (truncate beginning if regd.)
! %-10.10r - Example. Left-justify time elapsed within 10-wide filed.
!		Truncate from beginning if wider than 10 characters.
! ----------------------------------------------------------------------------

POJO
Create a POJO class named DvdEntity in the new package com.ho.apache.poi.test. This class will contain the objects to export.

/**
 * Entity containing the Dvd object.
 * @author huseyin
 *
 */
public class DvdEntity {
	
	// -------------------------------------- ENUMERATION
	public static enum DvdCategory{
		POLAR,
		THRILLER,
		ANIMATION,
		SCIENCE_FICTION,
		;
	}
	
	// ------------------------------- PRIVATE ATTRIBUTES
	// ID
	private Integer id;
	
	// NAME
	private String name;
	
	// PRICE
	private Double price;
	
	// NB OF VIEWING
	private Integer numberOfViewing;
	
	// LAST VIEWING DATETIME
	private Calendar lastViewingDateTime;
	
	// ACTORS
	private String[] actors;
	
	// DVD CATEGORY
	private DvdCategory dvdCategory;
	
	// COMMENTS
	private String comments;
	
	
	// -------------------------------------- CONSTRUCTOR
	public DvdEntity(){}
	
	// ----------------------------------- PUBLIC METHODS
	@Override
	public String toString(){
		return this.getClass().getName() 
				+"["
				+ "id="+id 
				+ ", name="+name
				+ ", price="+price
				+ ", numberOfViewing="+numberOfViewing
				+ ", lastViewingDateTime="+lastViewingDateTime
				+ ", dvdCategory="+dvdCategory
				+ ", comments="+comments
				+"]";
	}
	
	
	public Color getColor(){
		Color color = null;
		
		if(dvdCategory == DvdEntity.DvdCategory.ANIMATION){
			color = Color.BRIGHT_GREEN;
		}else if(dvdCategory == DvdEntity.DvdCategory.POLAR){
			color = Color.ORANGE;
		}else if(dvdCategory == DvdEntity.DvdCategory.SCIENCE_FICTION){
			color = Color.BLUE;
		}else if(dvdCategory == DvdEntity.DvdCategory.THRILLER){
			color = Color.DARK_YELLOW;
		}else{
			color = Color.BLACK;
		}
		return color;
	}

	// ----------------------------------- GET / SET TERS
	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}
//...
}

CSV File Exporter
– Create a new package com.ho.standard.exporter.csv,
– Create a new class named NullSafeCSVFileWriter extending java.io.FileWriter which is a NUllSave implementation of FileWriter in order to calling write with a null argument won’t throw any exception, but nothing will be written.

This class also provides utility methods to write Calendar, formated as timestamp, time, date, according to the DateFormats provided when creating the instance. Same goes for Double / Integer values.

public class NullSafeCSVFileWriter extends FileWriter{

	// ---------------------------------------- VARIABLES
	private NumberFormat decimalFormat;
	private String separator;

	// -------------------------------------- CONSTRUCTOR
	public NullSafeCSVFileWriter (File file, NumberFormat decimalFormat, String separator) throws IOException{
		super(file);
		this.separator = separator;
		this.decimalFormat = decimalFormat;
	}
	
	// ----------------------------------- PUBLIC METHODS
	public void write(Calendar cal, DateFormat format) throws IOException{
		if(cal != null){
			super.write(format.format(cal.getTime()));
		}
	}

	public void writeField(Calendar cal, DateFormat format) throws IOException{
		if(cal!=null){
			this.write(cal, format);
		}
		this.write(separator);
	}
//....
}

– Create a new class named DvdCSVExporter in the package com.ho.apache.poi.test which provides utility methods specific to our example (write headers, write Dvd entity,..) by using the previous class NullSafeCSVFileWriter. So, this class handles the generation of the CSV file via private methods to write headers and dvd entity.

public class DvdCSVExporter {
	
	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(DvdCSVExporter.class);
	private final TimeZone timeZone = TimeZone.getTimeZone("GMT");
	public static final String NEW_LINE = "\n";
	public static final String SEPARATOR = ";";
	private static final String[] COLUMN_LIST = {"Id","Name","Price","Number of Viewing","Last Viewing Datetime","Actors/Actress","Category","Comment"};
	
	// ----------------------------------- PUBLIC METHODS
	/**
	 * Exports the list of entities into the specified file.
	 * @param dvdList
	 * @param exportFile
	 * @throws Exception
	 */
	public void exportFile(List<DvdEntity> dvdList, File exportFile) throws Exception{
		
		LOG.info("Exporting CSV File: "+ dvdList.size() + " entries to export to the file " + exportFile.getPath());
		
		// Set up the formatters
		final NumberFormat decimalFormat = DecimalFormat.getInstance(Locale.ENGLISH);
		decimalFormat.setMaximumFractionDigits(8);
		decimalFormat.setGroupingUsed(false);
		//
		final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		//
		final SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		timestampFormat.setTimeZone(timeZone);
		//
		final SimpleDateFormat timeFormat = new SimpleDateFormat("hh:mm:ss");
		timeFormat.setTimeZone(timeZone);
		
		NullSafeCSVFileWriter fw = null;
		try{
			fw = new NullSafeCSVFileWriter(exportFile, decimalFormat, SEPARATOR);
			
			//Write header
			writeHeader(fw, timestampFormat, Calendar.getInstance());
			
			//Write content
			for (DvdEntity dvd : dvdList) {
				writeDvd(dvd, fw, decimalFormat, dateFormat, timestampFormat, timeFormat);
			}//end-for
			
			//Write last line : number of lines
			fw.write("File generated with standard classes");
			fw.write(SEPARATOR);
			fw.write(NEW_LINE);
			
			LOG.info("Exporting CSV File: Done");
			
		}finally{
			if(fw != null){
				fw.close();
			}
		}
	}

	// ---------------------------------- PRIVATE METHODS
	/**
	 * Write the headers in the CSV file.
	 * @param fw
	 * @param timestampFormat
	 * @param creationDate
	 * @throws IOException
	 */
	private void writeHeader(final NullSafeCSVFileWriter fw, final DateFormat timestampFormat, final Calendar creationDate) throws IOException{
		fw.write("File created at "+timestampFormat.format(creationDate.getTime()));
		fw.write(NEW_LINE);
		
		// Write CSV header now
		for (int i = 0; i < COLUMN_LIST.length - 1; i++) {
			fw.write(COLUMN_LIST[i]);
			fw.write(SEPARATOR);
		}//end-for
		
		// Write last column "Comment" without trailing seperator
		fw.write(COLUMN_LIST[COLUMN_LIST.length-1]);
		
		// Write the new line seperator
		fw.write(NEW_LINE);

	}
//...
}

XLS File Exporter
– Create a new package com.ho.apache.poi.exporter.xls,
– Create new interface IXLSFileBuilder provides utility methods to write headers, Calendar, formatted as timestamp, time, date to an excel file.

public interface IXLSFileBuilder {

	/**
	 * Define the headers of the file. This does not prevent you from calling addHeader() in addition
	 * (before, after or both).
	 * 
	 * @param headers : Header labels
	 */
	public void setHeaders(String... headers);

	/**
	 * Add a single header, after the last one defined
	 * @param header : Header label
	 */
	public void addHeader(String header);

	/**
	 * Set value at the given column, for the current row of data.
	 * @param col : Index of the column (0-based)
	 * @param value : Value to store.
	 */
	public void setDatavalue(int col, Calendar value);
	public void setDatavalue(int col, String value);
	public void setDatavalue(int col, Double value);
	public void setDatavalue(int col, Integer value);
	public void setDatavalue(int col, boolean value);

	/**
	 * Change the current row of data.
	 */
	public void nextRow();
//...
}

– Create a new class XLSFileBuilder implementing previous interface IXLSFileBuilder.
This class is the heart class of using Apache POI in order to create new sheet, add headers, add new row, set values in cell, set style to cell…etc.

public class XLSFileBuilder implements IXLSFileBuilder {

	// ---------------------------------------- VARIABLES
	protected final Workbook workbook = new HSSFWorkbook();
	protected Sheet sheet;
	protected final CellStyle normalStyle;
	protected final CellStyle dateStyle;
	protected final CellStyle timestampStyle;
	protected final CellStyle integerStyle;
	protected final CellStyle doubleStyle;
	protected final CellStyle boolStyle;
	protected final CellStyle headerStyle;
	protected int rowNum = 1;
	protected int headerCol = 0;
	
	// ------------------------------- PRIVATE ATTRIBUTES
	private Map<Class<?>, HashMap<Color, CellStyle>> cellStyleMap;
	private HSSFPatriarch drawingPatriach;
	private CreationHelper createHelper;
	
	// -------------------------------- PUBLIC ATTRIBUTES
	public enum Color{
		BLACK(HSSFColor.BLACK.index),
		RED(HSSFColor.RED.index),
		ORANGE(HSSFColor.ORANGE.index),
		LIGHT_ORANGE(HSSFColor.LIGHT_ORANGE.index),
		BLUE(HSSFColor.BLUE.index),
		YELLOW(HSSFColor.YELLOW.index),
		TURQUOISE(HSSFColor.TURQUOISE.index),
		DARK_YELLOW(HSSFColor.DARK_YELLOW.index),
		BRIGHT_GREEN(HSSFColor.BRIGHT_GREEN.index),
		;
		
		private short index;
		private Color(short index){
			this.index = index;
		}
		public short getIndex(){
			return this.index;
		}
	}
	
	// -------------------------------------- CONSTRUCTOR
	public XLSFileBuilder(){
		// Create styles for cell values
		normalStyle = workbook.createCellStyle();
		//
		timestampStyle = workbook.createCellStyle();
		timestampStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		//
		dateStyle = workbook.createCellStyle();
		dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
		//
		integerStyle = workbook.createCellStyle();
		integerStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		//
		doubleStyle = workbook.createCellStyle();
		doubleStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
		//
		boolStyle = workbook.createCellStyle();
		
		// Style for the header
		headerStyle = workbook.createCellStyle();
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
		
		// Header font
		final Font font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(font);
		
		// Error font
		final Font errorFont = workbook.createFont();
		errorFont.setColor(HSSFColor.WHITE.index);
		errorFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		errorFont.setFontHeightInPoints((short)10);
		
		// Initialize the cellStyleMap
		cellStyleMap = new HashMap<Class<?>, HashMap<Color, CellStyle>>();
		cellStyleMap.put(String.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Calendar.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Integer.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Double.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Boolean.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Timestamp.class, new HashMap<Color, CellStyle>());
		
		
		// Store the mapping for normal fields
		cellStyleMap.get(String.class).put(null, this.normalStyle);
		cellStyleMap.get(Calendar.class).put(null, this.normalStyle);
		cellStyleMap.get(Integer.class).put(null, this.normalStyle);
		cellStyleMap.get(Double.class).put(null, this.normalStyle);
		cellStyleMap.get(Boolean.class).put(null, this.normalStyle);

		// Add format for datetime
		{
			final CellStyle timestampStyle = workbook.createCellStyle();
			timestampStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
			cellStyleMap.get(Timestamp.class).put(null, timestampStyle);
		}
		
		
		// Store the mapping for colored fields
		for(Color color : Color.values()){
			CellStyle normalColorStyle = copyAndColor(normalStyle, errorFont, color);
			cellStyleMap.get(String.class).put(color, normalColorStyle);
			
			CellStyle dateColorStyle = copyAndColor(dateStyle, errorFont, color);
			cellStyleMap.get(Calendar.class).put(color, dateColorStyle);
			
			CellStyle integerColorStyle = copyAndColor(integerStyle, errorFont, color);
			cellStyleMap.get(Integer.class).put(color, integerColorStyle);

			CellStyle doubleColorStyle = copyAndColor(doubleStyle, errorFont, color);
			cellStyleMap.get(Double.class).put(color, doubleColorStyle);

			CellStyle boolColorStyle = copyAndColor(boolStyle, errorFont, color);
			cellStyleMap.get(Boolean.class).put(color, boolColorStyle);

			CellStyle timestampColorStyle = copyAndColor(timestampStyle, errorFont, color);
			cellStyleMap.get(Timestamp.class).put(color, timestampColorStyle);
		}//end-for
		
		createHelper = workbook.getCreationHelper();
		if(sheet!=null){
			drawingPatriach = (HSSFPatriarch) sheet.createDrawingPatriarch();
		}
	}
	
	/**
	 * Constructs a new excel file builder, adding automatically a new sheet.
	 * The given sheet name is cleaned (removing forbidden characters: /,\,*,?,[,],:,!) 
	 * and truncated if necessary (max length = 31 characters).
	 * 
	 * @param sheetName
	 */
	public XLSFileBuilder(String sheetName){
		this();
		newSheet(sheetName);
		if(sheet!=null){
			drawingPatriach = (HSSFPatriarch) sheet.createDrawingPatriarch();
		}
	}
//...
}

– Create a new class named DvdXLSExporter in the package com.ho.apache.poi.test which provides utility methods specific to our example (write headers, write Dvd entity,..) by using the previous class XLSFileBuilder. So, this class handles the generation of the XLS file via private methods to write headers and dvd entity.

public class DvdXLSExporter {

	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(DvdXLSExporter.class);
	private static final String[] COLUMN_LIST = {"Id","Name","Price","Number of Viewing","Last Viewing Datetime","Actors/Actress","Category","Comment"};
	
	// ----------------------------------- PUBLIC METHODS
	/**
	 * Exports the list of entities into the specified file.
	 * @param dvdList
	 * @param exportFile
	 * @throws Exception
	 */
	public void exportFile(List<DvdEntity> dvdList, File exportFile) throws Exception{
		
		LOG.info("Exporting XLS File: "+ dvdList.size() + " entries to export to the file " + exportFile.getPath());

		final XLSFileBuilder excelFileBuilder = new XLSFileBuilder("My Dvds");
		excelFileBuilder.setHeaders(COLUMN_LIST);
		
		for(DvdEntity dvd : dvdList){
			writeDvd(dvd, excelFileBuilder);
			excelFileBuilder.nextRow();
		}//end-for
		
		// Resize the columns to fit the content
		excelFileBuilder.autoSizeColumns();
		
		excelFileBuilder.save(exportFile);
		LOG.info("Exporting XLS File: Done");
	}

	private void writeDvd(final DvdEntity dvd, final XLSFileBuilder excelFileBuilder) throws IOException{
		int col = 0;
		// Integer id
		excelFileBuilder.setDataValue(col++, dvd.getId(), dvd.getColor(), null);
		// String name
		excelFileBuilder.setDataValue(col++, dvd.getName(), dvd.getColor(), dvd.getComments());
		// Double price
		excelFileBuilder.setDataValue(col++, dvd.getPrice(), dvd.getColor(), null);
		// Integer numberOfViewing
		excelFileBuilder.setDataValue(col++, dvd.getNumberOfViewing(), dvd.getColor(), null);
		// Calendar lastViewingDateTime
		excelFileBuilder.setDataValue(col++, dvd.getLastViewingDateTime(), true, dvd.getColor(), null);
		// String[] actors
		excelFileBuilder.setDataValue(col++, dvd.getActors(), dvd.getColor(), null);
		// DvdCategory dvdCategory
		excelFileBuilder.setDataValue(col++, dvd.getDvdCategory().toString(), dvd.getColor(), null);
		// String comments
		excelFileBuilder.setDataValue(col++, dvd.getComments(), dvd.getColor(), null);
	}
}

Main method
Finally, create a new class named TestExporter in the package com.ho.apache.poi.test containing the main method to launch the DvdCSVExporter and DvdXLSExporter in order to create the files CSV and XLS of DVD entities.

public class TestExporter {

	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(TestExporter.class);

	// -------------------------------------- CONSTRUCTOR
	private TestExporter(){ }
	

	// -------------------------------------- MAIN METHOD
	public static void main(String[] args) {
		int exitStatus = -1;
		try{
			// Parse parameters, load properties and mark the batch as started.
			String defaultFilename = "data/test_poi_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(Calendar.getInstance().getTime());
			File xlsExport = new File(defaultFilename + ".xls");
			File csvExport = new File(defaultFilename + ".csv");
			List<DvdEntity> entities = new ArrayList<DvdEntity>();
			//
			int idCounter = 1;
			for (int i = 0; i < DvdCategory.values().length; i++) {
				for (int j = 0; j < 5; j++, idCounter++) {
					DvdEntity dvd = new DvdEntity();
					dvd.setId(idCounter);
					dvd.setName("Dvd "+idCounter);
					dvd.setPrice((Math.random()%10));
					dvd.setNumberOfViewing((int)(Math.random()%10));
					dvd.setLastViewingDateTime(GregorianCalendar.getInstance());
					dvd.setActors(new String[]{"Actor "+idCounter+j, "Actress "+idCounter+j});
					dvd.setDvdCategory(DvdCategory.values()[i]);
					dvd.setComments("a comment for the dvd "+idCounter);
					entities.add(dvd);
				}//end-for
			}//end-for
			
			// Run the batch
			{
				// Export the dvds as Excel file
				final DvdXLSExporter dvdXLSExporter = new DvdXLSExporter();
				dvdXLSExporter.exportFile(entities, xlsExport);
				
				// Export the dvds as CSV file
				final DvdCSVExporter dvdCSVExporter = new DvdCSVExporter();
				dvdCSVExporter.exportFile(entities, csvExport);
			}
			
			exitStatus = 0;
			
		}catch(Exception ex){
			exitStatus = 2;
			LOG.error("Exception caught:", ex);
		}
		System.exit(exitStatus);
	}	
}

The outputs are:

[00:52:19]INFO  - Exporting XLS File: 20 entries to export to the file data\test_poi_20140521_005219.xls (DvdXLSExporter.java:32) 
[00:52:21]INFO  - Exporting XLS File: Done (DvdXLSExporter.java:46) 
[00:52:21]INFO  - Exporting CSV File: 20 entries to export to the file data\test_poi_20140521_005219.csv (DvdCSVExporter.java:41) 
[00:52:21]INFO  - Exporting CSV File: Done (DvdCSVExporter.java:73) 

That’s all!!!

Download: test_poi.zip

Best regards,

Huseyin OZVEREN