import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;

public class ColorBugInPOI {
	
	private static final int NUM_COLUMNS = 7;
	private static final int CHARACTER_MULTIPLIER = 256;
	
	private static void setWidth(Sheet sheet, int row, int width) {
		sheet.setColumnWidth(row, CHARACTER_MULTIPLIER * width);
	}
	
	private static Sheet createSheet(HSSFWorkbook workbook, String worksheetName) {
		Sheet sheet = workbook.createSheet(worksheetName);
		for (int i = 0; i < NUM_COLUMNS; i++) {
			setWidth(sheet, i, 20);
		}
		return sheet;
	}
	
	private static void colorCell(HSSFWorkbook workbook, Cell cell, short color) {
	    HSSFCellStyle style = workbook.createCellStyle();
	    style.setFillForegroundColor(color);
	    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cell.setCellStyle(style);
	}
	
	private static void colorCellPaleBlue(HSSFWorkbook workbook, Cell cell) {
		colorCell(workbook, cell, HSSFColor.PALE_BLUE.index);
	}
	
	private static void colorCellLightYellow(HSSFWorkbook workbook, Cell cell) {
		colorCell(workbook, cell, HSSFColor.LEMON_CHIFFON.index);
	}
	
	private static void addEmptyPaleBlueCell(HSSFWorkbook workbook, Row row, int num) {
	    Cell cell = row.createCell(num);
	    cell.setCellValue("");
	    colorCellPaleBlue(workbook, cell);
	}
	
	private static void addSpanningRow(HSSFWorkbook workbook, Sheet sheet, int num, String text) {
		Row row = sheet.createRow(num);
	    Cell cell = row.createCell(0);
	    cell.setCellValue(text);
	    colorCellPaleBlue(workbook, cell);
	    for (int i = 1; i < NUM_COLUMNS; i++) {
		    addEmptyPaleBlueCell(workbook, row, i);
	    }
		sheet.addMergedRegion(new CellRangeAddress(num, num, 0, NUM_COLUMNS - 1));
	}
	
	private static void addExportRootPath(HSSFWorkbook workbook, Sheet sheet) {
		addSpanningRow(workbook, sheet, 0, "This is one of the header rows");
	}
	
	private static void addClassIdentifier(HSSFWorkbook workbook, Sheet sheet) {
		addSpanningRow(workbook, sheet, 1, "This is another header row");
	}
	
	private static void addHeaderCell(HSSFWorkbook workbook, Row row, int num, String text) {
	    Cell cell = row.createCell(num);
	    cell.setCellValue(text);
	    colorCellLightYellow(workbook, cell);
	}
	
	private static void addColumnHeaders(HSSFWorkbook workbook, Sheet sheet) {
		Row row = sheet.createRow(2);
		for (int i = 0; i < NUM_COLUMNS; i++) {
			addHeaderCell(workbook, row, i, "Column" + Integer.valueOf(i + 1).toString());
		}
	}
	
	private static void addInitializedSheet(HSSFWorkbook workbook, String worksheetName) {
		Sheet sheet = createSheet(workbook, worksheetName);
		addExportRootPath(workbook, sheet);
		addClassIdentifier(workbook, sheet);
		addColumnHeaders(workbook, sheet);
	}

	public static void main(String[] args) throws Exception {
		try (HSSFWorkbook workbook = new HSSFWorkbook(); FileOutputStream writer = new FileOutputStream(new File(args[0]))) {
			Font font = workbook.createFont();
		    font.setFontHeightInPoints((short)6);
		    font.setFontName("Arial");
		    addInitializedSheet(workbook, "Worksheet1");
		    addInitializedSheet(workbook, "Worksheet2");
		    addInitializedSheet(workbook, "Worksheet3");
		    addInitializedSheet(workbook, "Worksheet4");
		    addInitializedSheet(workbook, "Worksheet5");
		    addInitializedSheet(workbook, "Worksheet6");
			workbook.write(writer);
		}
	}
}