package net.sf.csutils.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;

import org.apache.poi.hssf.record.cf.BorderFormatting;
import org.apache.poi.hssf.record.cf.FontFormatting;
import org.apache.poi.hssf.record.cf.PatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFFontFormatting;
import org.apache.poi.hssf.usermodel.HSSFPatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;


public class Test {
    private static Object getField(String pFieldName, Object pObject) throws Exception {
        if (pObject == null) {
            return null;
        }
        final Field field = pObject.getClass().getDeclaredField(pFieldName);
        field.setAccessible(true);
        return field.get(pObject);
    }

    private static FontFormatting getFontFormatting(HSSFFontFormatting pFontFormatting) throws Exception {
        return (FontFormatting) getField("fontFormatting", pFontFormatting);
    }

    private static BorderFormatting getBorderFormatting(HSSFBorderFormatting pBorderFormatting) throws Exception {
        return (BorderFormatting) getField("borderFormatting", pBorderFormatting);
    }

    private static PatternFormatting getPatternFormatting(HSSFPatternFormatting pPatternFormatting) throws Exception {
        return (PatternFormatting) getField("patternFormatting", pPatternFormatting);
    }

    private static void show(HSSFSheet pSheet) throws Exception {
        HSSFSheetConditionalFormatting conditionalFormatting = pSheet.getSheetConditionalFormatting();
        final int num = conditionalFormatting.getNumConditionalFormattings();
        for (int i = 0;  i < num;  i++) {
            HSSFConditionalFormatting cf = conditionalFormatting.getConditionalFormattingAt(i);
            System.out.println("cf: " + i);
            final CellRangeAddress[] cras = cf.getFormattingRanges();
            for (int j = 0;  j < cras.length;  j++) {
                final CellRangeAddress cra = cras[j]; 
                System.out.println("  CellRange: " + cra);
            }
            final int numRules = cf.getNumberOfRules();
            for (int j = 0;  j < numRules;  j++) {
                final HSSFConditionalFormattingRule rule = cf.getRule(j);
                System.out.println("  formula1 = " + rule.getFormula1());
                System.out.println("  formula2 = " + rule.getFormula2());
                System.out.println("  comparisonOperation = " + rule.getComparisonOperation());
                System.out.println("  conditionType = " + rule.getConditionType());
                System.out.println("  patternFormatting = " + getPatternFormatting(rule.getPatternFormatting()));
                System.out.println("  fontFormatting = " + getFontFormatting(rule.getFontFormatting()));
                System.out.println("  borderFormatting = " + getBorderFormatting(rule.getBorderFormatting()));
            }
        }
    }

    private static void show(HSSFWorkbook pWorkbook) throws Exception {
        final int sheets = pWorkbook.getNumberOfSheets();
        for (int i = 0;  i < sheets;  i++) {
            show(pWorkbook.getSheetAt(i));
        }
    }

    private static void show(File pFile) throws Exception {
        System.out.println(pFile.getPath());
        InputStream s = new FileInputStream(pFile);
        try {
            final HSSFWorkbook workbook = new HSSFWorkbook(s);
            show(workbook);
            s.close();
            s = null;
        } finally {
            if (s != null) { try { s.close(); } catch (Throwable t) { /* Ignore me */ } }
        }
    }
    
    public static void main(String[] pArgs) throws Exception {
        show(new File("C:/workspace/csutils/cs-utils-poi/target/ConditionalFormulaTest.xls"));
        show(new File("C:/workspace/csutils/cs-utils-poi/target/ConditionalFormulaTest2.xls"));
    }
}