package t5750.poi.export;

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

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import t5750.poi.util.Globals;
import t5750.poi.util.TestUtil;

public class TestExcelStylingDemo {
	public static final String EXCEL_NAME = "testExcelStylingDemo";

	public static void main(String[] args) throws Exception {
		Workbook wb = new XSSFWorkbook();
		basedOnValue(wb.createSheet("Value Based formatting"));
		formatDuplicates(wb.createSheet("Duplicates formatting"));
		shadeAlt(wb.createSheet("Alternate rows"));
		expiryInNext30Days(wb.createSheet("Soon Expired Payments"));
		// Write the output to a file
		FileOutputStream out = new FileOutputStream(TestUtil.DOC_PATH
				+ File.separator + EXCEL_NAME + Globals.SUFFIX_XLSX);
		wb.write(out);
		out.close();
		System.out.println(EXCEL_NAME + Globals.SUFFIX_XLSX + TestUtil.SUCCESS);
	}

	// Cell value is in between a certain range
	static void basedOnValue(Sheet sheet) {
		// Creating some random values
		sheet.createRow(0).createCell(0).setCellValue(84);
		sheet.createRow(1).createCell(0).setCellValue(74);
		sheet.createRow(2).createCell(0).setCellValue(50);
		sheet.createRow(3).createCell(0).setCellValue(51);
		sheet.createRow(4).createCell(0).setCellValue(49);
		sheet.createRow(5).createCell(0).setCellValue(41);
		SheetConditionalFormatting sheetCF = sheet
				.getSheetConditionalFormatting();
		// Condition 1: Cell Value Is greater than 70 (Blue Fill)
		ConditionalFormattingRule rule1 = sheetCF
				.createConditionalFormattingRule(ComparisonOperator.GT, "70");
		PatternFormatting fill1 = rule1.createPatternFormatting();
		fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
		fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
		// Condition 2: Cell Value Is less than 50 (Green Fill)
		ConditionalFormattingRule rule2 = sheetCF
				.createConditionalFormattingRule(ComparisonOperator.LT, "50");
		PatternFormatting fill2 = rule2.createPatternFormatting();
		fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
		fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
		CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };
		sheetCF.addConditionalFormatting(regions, rule1, rule2);
	}

	/**
	 * Use Excel conditional formatting to highlight duplicate entries in a
	 * column.
	 */
	static void formatDuplicates(Sheet sheet) {
		sheet.createRow(0).createCell(0).setCellValue("Code");
		sheet.createRow(1).createCell(0).setCellValue(4);
		sheet.createRow(2).createCell(0).setCellValue(3);
		sheet.createRow(3).createCell(0).setCellValue(6);
		sheet.createRow(4).createCell(0).setCellValue(3);
		sheet.createRow(5).createCell(0).setCellValue(5);
		sheet.createRow(6).createCell(0).setCellValue(8);
		sheet.createRow(7).createCell(0).setCellValue(0);
		sheet.createRow(8).createCell(0).setCellValue(2);
		sheet.createRow(9).createCell(0).setCellValue(8);
		sheet.createRow(10).createCell(0).setCellValue(6);
		SheetConditionalFormatting sheetCF = sheet
				.getSheetConditionalFormatting();
		// Condition 1: Formula Is =A2=A1 (White Font)
		ConditionalFormattingRule rule1 = sheetCF
				.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
		FontFormatting font = rule1.createFontFormatting();
		font.setFontStyle(false, true);
		font.setFontColorIndex(IndexedColors.BLUE.index);
		CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };
		sheetCF.addConditionalFormatting(regions, rule1);
	}

	/**
	 * Use Excel conditional formatting to shade alternating rows on the
	 * worksheet
	 */
	static void shadeAlt(Sheet sheet) {
		SheetConditionalFormatting sheetCF = sheet
				.getSheetConditionalFormatting();
		// Condition 1: Formula Is =A2=A1 (White Font)
		ConditionalFormattingRule rule1 = sheetCF
				.createConditionalFormattingRule("MOD(ROW(),2)");
		PatternFormatting fill1 = rule1.createPatternFormatting();
		fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
		fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
		CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") };
		sheetCF.addConditionalFormatting(regions, rule1);
	}

	/**
	 * Highlight payments that are due in the next thirty days
	 */
	static void expiryInNext30Days(Sheet sheet) {
		CellStyle style = sheet.getWorkbook().createCellStyle();
		style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));
		sheet.createRow(0).createCell(0).setCellValue("Date");
		sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
		sheet.createRow(2).createCell(0).setCellFormula("A2+1");
		sheet.createRow(3).createCell(0).setCellFormula("A3+1");
		for (int rownum = 1; rownum <= 3; rownum++)
			sheet.getRow(rownum).getCell(0).setCellStyle(style);
		SheetConditionalFormatting sheetCF = sheet
				.getSheetConditionalFormatting();
		// Condition 1: Formula Is =A2=A1 (White Font)
		ConditionalFormattingRule rule1 = sheetCF
				.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
		FontFormatting font = rule1.createFontFormatting();
		font.setFontStyle(false, true);
		font.setFontColorIndex(IndexedColors.BLUE.index);
		CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };
		sheetCF.addConditionalFormatting(regions, rule1);
		sheet.getRow(0).createCell(1)
				.setCellValue("Dates within the next 30 days are highlighted");
	}
}