package net.sf.csutils.poi;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;

import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.record.CFRuleRecord;
import org.apache.poi.hssf.record.ExternSheetRecord;
import org.apache.poi.hssf.record.RecalcIdRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RecordInputStream;
import org.apache.poi.hssf.record.StandardRecord;
import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate;
import org.apache.poi.hssf.record.cf.PatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFPatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.hssf.util.CellReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.LittleEndianOutput;
import org.apache.poi.util.LittleEndianOutputStream;


public class ConditionalFormulaTest {
    private static <M> M getFieldValue(Object pObject, String pFieldName) throws Exception {
        final Field field = pObject.getClass().getDeclaredField(pFieldName);
        field.setAccessible(true);
        @SuppressWarnings("unchecked")
        final M m = (M) field.get(pObject);
        return m;
    }

    private static void setFieldValue(Object pObject, String pFieldName, Object pValue) throws Exception {
        final Field field = pObject.getClass().getDeclaredField(pFieldName);
        field.setAccessible(true);
        field.set(pObject, pValue);
    }

    private static void createCell(HSSFSheet pSheet, String pCellRef, double pValue) {
        final CellReference ref = new CellReference(pCellRef);
        HSSFRow row = pSheet.getRow(ref.getRow());
        if (row == null) {
            row = pSheet.createRow(ref.getRow());
        }
        HSSFCell cell = row.getCell((int) ref.getCol());
        if (cell == null) {
            cell = row.createCell((int) ref.getCol());
        }
        cell.setCellValue(pValue);
    }

    private static HSSFConditionalFormattingRule createRule(HSSFSheet pSheet, int pRow) throws Exception {
        final HSSFSheetConditionalFormatting conditionalFormatting = pSheet.getSheetConditionalFormatting();
        final HSSFConditionalFormattingRule cf = conditionalFormatting.createConditionalFormattingRule("$B1<$C1");
        final HSSFPatternFormatting patternFormatting = cf.createPatternFormatting();
        patternFormatting.setFillBackgroundColor((short) 12);
        patternFormatting.setFillForegroundColor((short) 0);
        patternFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
        final CFRuleRecord cfRuleRecord = getFieldValue(cf, "cfRuleRecord");
        cfRuleRecord.setPatternStyleModified(false);
        return cf;
    }

    private static Record newRecalcIdRecord() throws Exception {
        final ByteArrayOutputStream baos = new ByteArrayOutputStream();
        final LittleEndianOutputStream leos = new LittleEndianOutputStream(baos);
        leos.writeShort(RecalcIdRecord.sid);
        leos.writeShort(RecalcIdRecord.sid);
        leos.writeShort(0);
        leos.writeInt(Integer.valueOf("00013880", 16));
        final byte[] buffer = baos.toByteArray();
        final ByteArrayInputStream bis = new ByteArrayInputStream(buffer);
        final RecordInputStream ris = new RecordInputStream(bis);
        setFieldValue(ris, "_currentDataLength", buffer.length);
        return new RecalcIdRecord(ris);
    }
    
    public static void main(String[] pArgs) throws Exception {
        final HSSFWorkbook workbook = new HSSFWorkbook();
        final InternalWorkbook internalWorkbook = getFieldValue(workbook, "workbook");
        final List<Record> records = internalWorkbook.getRecords();
        boolean found = false;
        for (int i = 0;  i < records.size();  i++) {
            if (records.get(i).getSid() == ExternSheetRecord.sid) {
                found = true;
                records.add(i+1, newRecalcIdRecord());
            }
        }
        if (!found) {
            records.add(newRecalcIdRecord());
        }
        final HSSFSheet sheet = workbook.createSheet("Conditional Formula Test");
        createCell(sheet, "B3", 1.0);
        createCell(sheet, "C3", 2.0);
        createCell(sheet, "B4", 1.0);
        createCell(sheet, "C4", 1.0);
        createCell(sheet, "B5", 1.5);
        createCell(sheet, "C5", 1.0);
        createCell(sheet, "B6", 1.0);
        createCell(sheet, "C6", 1.1);
        final CellRangeAddress[] cellRangeAddresses = new CellRangeAddress[]{
            CellRangeAddress.valueOf("D3:D6")
        };
        final HSSFConditionalFormattingRule[] rules = new HSSFConditionalFormattingRule[]{
            createRule(sheet, 3),
        };
        final HSSFSheetConditionalFormatting sheetConditionalFormatting = sheet.getSheetConditionalFormatting();
        sheetConditionalFormatting.addConditionalFormatting(cellRangeAddresses, rules);
        final HSSFConditionalFormatting conditionalFormatting = sheetConditionalFormatting.getConditionalFormattingAt(0);
        final CFRecordsAggregate cfRecordsAggregate = getFieldValue(conditionalFormatting, "cfAggregate");
        cfRecordsAggregate.getHeader().setNeedRecalculation(true);
        OutputStream os = new FileOutputStream(new File("C:/workspace/csutils/cs-utils-poi/target/ConditionalFormulaTest.xls"));
        try {
            workbook.write(os);
            os.close();
            os = null;
        } finally {
            if (os != null) { try { os.close(); } catch (Throwable t) { /* Ignore me */ } }
        }
    }
}