package test;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class TestExcelConditionalFormattingByFormula_2 {
	public static void main(String[] args) throws IOException {
		  FileOutputStream fos = null;
		  try {
			  Workbook workbook = new HSSFWorkbook();
			  Sheet sheet = workbook.createSheet("Conditional Formatting Test");
			  sheet.setColumnWidth(0, 256 * 10);
			  sheet.setColumnWidth(1, 256 * 10);
			  sheet.setColumnWidth(2, 256 * 10);

			  // Create some content.
			  // row 0
			  Row row = sheet.createRow(0);

			  Cell cell0 = row.createCell(0);
			  cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell0.setCellValue(100);

			  Cell cell1 = row.createCell(1);
			  cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell1.setCellValue(120);

			  Cell cell2 = row.createCell(2);
			  cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell2.setCellValue(130);

			  // row 1
			  row = sheet.createRow(1);

			  cell0 = row.createCell(0);
			  cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell0.setCellValue(200);

			  cell1 = row.createCell(1);
			  cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell1.setCellValue(220);

			  cell2 = row.createCell(2);
			  cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell2.setCellValue(230);

			  // row 2
			  row = sheet.createRow(2);

			  cell0 = row.createCell(0);
			  cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell0.setCellValue(300);

			  cell1 = row.createCell(1);
			  cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell1.setCellValue(320);

			  cell2 = row.createCell(2);
			  cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
			  cell2.setCellValue(330);
			  
			  // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank.
			  SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();

			  ConditionalFormattingRule rule = formatting.createConditionalFormattingRule("$A$1>75");

			  PatternFormatting pattern = rule.createPatternFormatting();
			  pattern.setFillBackgroundColor(IndexedColors.BLUE.index);
			  pattern.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

			  CellRangeAddress[] range = {CellRangeAddress.valueOf("B2:C2")};
			  CellRangeAddress[] range2 = {CellRangeAddress.valueOf("B1:C1")};

			  formatting.addConditionalFormatting(range, rule);
			  formatting.addConditionalFormatting(range2, rule);

			  // Write file.
			  fos = new FileOutputStream("conditional-sheet.xls");
			  workbook.write(fos);
		  } finally {
			  if (fos != null) {
				  try {
					  fos.close();
				  } catch (IOException x) {
				  }
			  }
		  }
		  System.out.println("ready.");
	  }
}