import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule;
import org.junit.Test;

public class HarveyBallTest {

    @Test
    public void testFormatting() throws IOException, InvalidFormatException {
        InputStream inputStream = null;
        final Workbook workbook;
        try {
            inputStream = this.getClass().getResource("/Harvey.xlsx").openStream();
            workbook = WorkbookFactory.create(inputStream);
        } finally {
            IOUtils.closeQuietly(inputStream);
        }

        final Sheet sheet2 = workbook.getSheet("Sheet1");
        SheetConditionalFormatting scf = sheet2.getSheetConditionalFormatting();
        System.out.println(String.format("Having %s conditional formatting(s)", scf.getNumConditionalFormattings()));
        System.out.println("---------------\r\n");
        for (int i = 0; i < scf.getNumConditionalFormattings(); i++) {
            ConditionalFormatting cf = scf.getConditionalFormattingAt(i);

            System.out.println("-= Printing conditional formatting =-");

            System.out.print("Following range addresses are available: ");
            for (CellRangeAddress cr : cf.getFormattingRanges()) {
                System.out.print(cr.toString() + ", ");
            }
            System.out.println("\r\n");

            System.out.println(String.format("Following %s rules are available:", cf.getNumberOfRules()));
            for (int j = 0; j < cf.getNumberOfRules(); j++) {
                XSSFConditionalFormattingRule cfr = (XSSFConditionalFormattingRule) cf.getRule(0);
                System.out.println("Rule " + j + ": ");
                System.out.println(" formula1: " + cfr.getFormula1());
                System.out.println(" formula2: " + cfr.getFormula2());
                System.out.println(" borderfmt: " + cfr.getBorderFormatting());
                System.out.println(" comp.op: " + cfr.getComparisonOperation());
                System.out.println(" cond.type: " + cfr.getConditionType());
                System.out.println(" font.fmt: " + (cfr.getFontFormatting() != null ? cfr.getFontFormatting().toString() : ""));
                System.out.println(" patt.fmt: " + (cfr.getPatternFormatting() != null ? cfr.getPatternFormatting().toString() : ""));
            }
            System.out.println("\r\n");
        }
        System.out.println("---------------\r\n");
    }
}