Excel Writter - SGajre/Tutorial GitHub Wiki

package com.citi.risk.pra.icg.writer;

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

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.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.batch.core.StepExecution; import org.springframework.batch.core.annotation.AfterStep; import org.springframework.batch.core.annotation.BeforeStep; import org.springframework.batch.item.ItemWriter;

import com.citi.risk.pra.icg.domain.CreditScenarioSummary; import com.citi.risk.pra.icg.domain.IConstants;

public class ScenarioExcelReportWriter implements ItemWriter {

private static Logger logger = LoggerFactory
		.getLogger(ScenarioExcelReportWriter.class);

private String filePath;
private String title;
private String cobDate;
private String tab;
private String rateTab;
private String[] headers;
private String outputFilename;
private SXSSFWorkbook workbook;
private CellStyle dataCellStyleEvenRow;
private CellStyle dataCellStyleOddRow;
private CellStyle numericCellStyleEvenRow;
private CellStyle numericCellStyleOddRow;
private int currRow = 0;
private static String ISSUER_COUNTRY_NAME = "Country Of Issuer";

@BeforeStep
public void beforeStep(StepExecution stepExecution) {
	logger.debug("Generating report for: " + tab);
	//stepExecution.getJobParameters().getString(key)
	
	workbook = new SXSSFWorkbook(1000);
	Sheet sheet = workbook.createSheet("Credit Scenario");
	sheet.setColumnWidth(0, 4000);
	sheet.setColumnWidth(1, 6000);
	sheet.setColumnWidth(2, 6000);		
	sheet.setColumnWidth(3, 6000);
	sheet.setColumnWidth(4, 6000);
	sheet.setColumnWidth(5, 6000);		
	sheet.setColumnWidth(6, 4000);
	sheet.setColumnWidth(7, 7000);
	sheet.setColumnWidth(8, 7000);
	sheet.setColumnWidth(9, 5000);
	sheet.setColumnWidth(10, 5000);
	sheet.setColumnWidth(11, 7000);
	sheet.setColumnWidth(12, 6000);

	if(IConstants.RATE_LABLE.equals(rateTab)){
		sheet.setColumnWidth(13, 6000);
	}
	addTitleToSheet(sheet);
	addHeaders(sheet);
	initDataStyle();

	setDataCellEvenRowStyle(dataCellStyleEvenRow);
	setDataCellOddRowStyle(dataCellStyleOddRow);
	setDataCellEvenRowStyle(numericCellStyleEvenRow);
	setDataCellOddRowStyle(numericCellStyleOddRow);
}

@Override
public void write(List<? extends CreditScenarioSummary> items)
		throws Exception {
	Sheet sheet = workbook.getSheetAt(0);
	for (CreditScenarioSummary scenario : items) {
		Row row = sheet.createRow(currRow++);
		createStringCell(row, scenario.getLegalEntity(), 0);
		createStringCell(row, scenario.getBusinessUnit(), 1);
		createStringCell(row, scenario.getSubBusinessUnit(), 2);
		createStringCell(row, scenario.getDesk(), 3);
        createStringCell(row, scenario.getBook(), 4);
        createStringCell(row, scenario.getCounterParty(), 5);    
		createStringCell(row, scenario.getCurrencyOfExposure(), 6);
		createStringCell(row, scenario.getScenarioShock(), 7);
		createStringCell(row, scenario.getSeverity(), 8);
		createStringCell(row, scenario.getIssuerCountryName(), 9);
		createStringCell(row, scenario.getProductType(), 10);
		createStringCell(row, scenario.getCreditReference(), 11);
		createNumericCell(row, scenario.getScenarioShockResultValue().doubleValue(), 12);
		
		if(IConstants.RATE_LABLE.equals(rateTab)){
			createStringCell(row, scenario.getRate(), 13);
		}
	}
}

@AfterStep
public void afterStep(StepExecution stepExecution) throws IOException {
	if (currRow > 2) {
		FileOutputStream fos = new FileOutputStream(filePath
				+ outputFilename);
		workbook.write(fos);
		fos.close();
	}
}

private void addHeaders(Sheet sheet) {
	Font font = workbook.createFont();
	font.setFontHeightInPoints((short) 11);
	font.setFontName("Calibri");
	font.setColor(IndexedColors.WHITE.getIndex());
	font.setBoldweight(Font.BOLDWEIGHT_BOLD);

	CellStyle style = workbook.createCellStyle();
	setCellStyle(style, font);

	CellStyle styleM = workbook.createCellStyle();
	setCellStyle(styleM, font);

	Row row = sheet.createRow(currRow++);
	row.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));
	int col = 0;

	for (String header : headers) {
		Cell cell = row.createCell(col);
		cell.setCellValue(header);
		if (ISSUER_COUNTRY_NAME.equals(header)) {
			cell.setCellStyle(styleM);
		} else {
			cell.setCellStyle(style);
		}
		col++;
	}
}

private void setCellStyle(CellStyle style, Font font) {
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setFont(font);
	style.setFillForegroundColor(IndexedColors.RED.getIndex());
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setWrapText(true);
}

private void addTitleToSheet(Sheet sheet) {
	CellStyle style = workbook.createCellStyle();
	Font font = workbook.createFont();
	font.setFontHeightInPoints((short) 20);
	font.setFontName("Calibri");
	font.setColor(IndexedColors.WHITE.getIndex());
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setFont(font);
	style.setFillForegroundColor((short) 0);
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);

	Row row = sheet.createRow(currRow++);
	row.setHeightInPoints((short) 40);

	Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);
	cell.setCellValue(title + " " + cobDate);
	cell.setCellStyle(style);

	CellRangeAddress range = new CellRangeAddress(0, 0, 0,headers.length - 1);
	sheet.addMergedRegion(range);
}

private void setDataCellEvenRowStyle(CellStyle cellStyle) {
	cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	cellStyle.setBorderRight(CellStyle.BORDER_THIN);
	cellStyle.setBorderTop(CellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
	cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
			.getIndex());
	cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
}

private void setDataCellOddRowStyle(CellStyle cellStyle) {
	cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	cellStyle.setBorderRight(CellStyle.BORDER_THIN);
	cellStyle.setBorderTop(CellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
	cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
	cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
}

private void initDataStyle() {
	dataCellStyleEvenRow = workbook.createCellStyle();
	Font font = workbook.createFont();

	font.setFontHeightInPoints((short) 11);
	font.setFontName("Calibri");
	dataCellStyleEvenRow.setAlignment(CellStyle.ALIGN_LEFT);
	dataCellStyleEvenRow.setFont(font);
	dataCellStyleOddRow = workbook.createCellStyle();
	dataCellStyleOddRow.setAlignment(CellStyle.ALIGN_LEFT);
	dataCellStyleOddRow.setFont(font);
	numericCellStyleEvenRow = workbook.createCellStyle();
	numericCellStyleEvenRow.setAlignment(CellStyle.ALIGN_RIGHT);
	numericCellStyleEvenRow.setFont(font);
	numericCellStyleOddRow = workbook.createCellStyle();
	numericCellStyleOddRow.setAlignment(CellStyle.ALIGN_RIGHT);
	numericCellStyleOddRow.setFont(font);
}	

private void createStringCell(Row row, String val, int col) {
	Cell cell = row.createCell(col);
	
	if (currRow % 2 == 0) {
		cell.setCellStyle(dataCellStyleEvenRow);
	} else {
		cell.setCellStyle(dataCellStyleOddRow);
	}
	
	cell.setCellType(Cell.CELL_TYPE_STRING);
	cell.setCellValue(val);

}

private void createNumericCell(Row row, Double val, int col) {
	Cell cell = row.createCell(col);
	if (currRow % 2 == 0) {
		cell.setCellStyle(numericCellStyleEvenRow);
	} else {
		cell.setCellStyle(numericCellStyleOddRow);
	}
	if (val == null) {
		cell.setCellValue("");
	} else {
		cell.setCellValue(val);
	}
}

public void setFilePath(String filePath) {
	this.filePath = filePath;
}

public void setOutputFilename(String outputFilename) {
	this.outputFilename = outputFilename;
}

public void setTitle(String title) {
	this.title = title;
}

public void setCobDate(String cobDate) {
	this.cobDate = cobDate;
}

public void setTab(String tab) {
	this.tab = tab;
}

public void setHeaders(String[] headers) {
	this.headers = headers;
}

public void setRateTab(String rateTab) {
	this.rateTab = rateTab;
}	

}