ReportPoiControllerTest.java

package accounts.web;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import accounts.Account;
import accounts.AccountManager;


/**
 * 
 * @author josediaz
 *
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:accounts/system-test-config.xml"})
public class ReportPoiControllerTest {
	
	 public static final short FONT_COLOR = new HSSFColor.BLACK().getIndex();
	 public static final short FONT_COLOR_TITLE = new HSSFColor.WHITE().getIndex();
	 public static final short BACKGROUND_COLOR = new HSSFColor.GREEN().getIndex();
	 public static final String FONT_TYPE = HSSFFont.FONT_ARIAL;
	 
	 @Autowired
	 private AccountManager accountManager;
	 
	@Test
	public void generateExcelPoiReport() throws IOException{
		
		Workbook wb = new HSSFWorkbook();
	    Sheet sheet = wb.createSheet("Accounts");
	    
	    int rowNum = 1;
	    Row row = sheet.createRow(rowNum++);
	    
	    Font font = wb.createFont();
	    font.setFontHeightInPoints((short)24);
	    font.setFontName(FONT_TYPE);
	    font.setColor(FONT_COLOR_TITLE);

	    /***  Header *****/
	    CellStyle style = wb.createCellStyle();
	    style.setFont(font);
	    style.setAlignment(CellStyle.ALIGN_CENTER);
	    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    style.setFillForegroundColor(BACKGROUND_COLOR);	     

	    Cell cell = row.createCell((short) 1);
	    cell.setCellValue("Account Report per Beneficiary");
	    cell.setCellStyle(style);
	    sheet.addMergedRegion(new CellRangeAddress(
	            1, //first row (0-based)
	            2, //last row  (0-based)
	            1, //first column (0-based)
	            16  //last column  (0-based)
	    ));

	    /***  Body *****/
	    font = wb.createFont();
	    font.setFontHeightInPoints((short)12);
	    font.setFontName(FONT_TYPE);
	    font.setColor(FONT_COLOR);
	    
	    style = wb.createCellStyle();
	    style.setFont(font);
	    
	    rowNum =  rowNum + 3;
	 	List<Account> accounts = accountManager.getAllAccounts();
	 	for (Account account : accounts) {
	 		
	 		row = sheet.createRow(rowNum++);
	 		
	 		cell = row.createCell((short) 1);
	 		cell.setCellStyle(style);
	 		cell.setCellValue(account.getName());
	 		
	 		cell = row.createCell((short) 2);
	 		cell.setCellValue(account.getNumber());
	 		cell.setCellStyle(style);
	 		
	 		cell = row.createCell((short) 3);
	 		cell.setCellValue(account.getEntityId());
	 		cell.setCellStyle(style);
	 	}

	 	sheet.autoSizeColumn(1);
	 	sheet.autoSizeColumn(2);
	 	sheet.autoSizeColumn(3);
	 	
	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls");
	    wb.write(fileOut);
	    fileOut.close();
	}

}