package accounts.web;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import accounts.Account;
import accounts.AccountManager;
@Controller
public class ReportPoiController {
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;
@RequestMapping("/accountPoiReport.xls")
public void generateExcelPoiReport(HttpServletResponse response) 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);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=\""+ "Accounts.xls" + "\"");
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}
}