import com.jniwrapper.win32.automation.types.Variant;
import com.jniwrapper.win32.excel.*;
import com.jniwrapper.win32.jexcel.Application;
import com.jniwrapper.win32.jexcel.Range;
import com.jniwrapper.win32.jexcel.Workbook;
import com.jniwrapper.win32.jexcel.Worksheet;

import java.awt.*;
import java.io.File;

/**
 * This sample demonstrates how to apply conditional formatting to a range using its native peer.
 * The conditional formatting is applied to "A1:A10". If the cell value is greater than 1000,
 * cell interior color is set to red.
 * 
 */
public class RangeConditionalFormattingSample {
    public static void main(String[] args) {
        Application application = null;
        Workbook workbook = null;
        try {
            application = new Application();
            workbook = application.openWorkbook(new File("test.xls"));
            Worksheet worksheet = workbook.getWorksheet(1);
            Range range = worksheet.getRange("A1:A10");
            final com.jniwrapper.win32.excel.Range rangePeer = range.getPeer();
            range.getOleMessageLoop().doInvokeAndWait(new Runnable() {
                @Override
                public void run() {
                    FormatConditions formatConditions = rangePeer.getFormatConditions();
                    //clean up formatting
                    formatConditions.delete();
                    //creating format condition
                    Variant operator = new Variant(new XlFormatConditionOperator(XlFormatConditionOperator.xlGreater));
                    Variant formula1 = new Variant(1000);
                    Variant formula2 = Variant.createUnspecifiedParameter();
                    FormatCondition formatCondition = formatConditions.add(new XlFormatConditionType(XlFormatConditionType.xlCellValue), operator, formula1, formula2);
                    //specifying format
                    Interior interior = formatCondition.getInterior();
                    int redRGB = convertToRGB(Color.RED);
                    Variant color = new Variant(redRGB);
                    interior.setColor(color);
                    //cleanup
                    interior.setAutoDelete(false);
                    interior.release();
                    formatCondition.setAutoDelete(false);
                    formatCondition.release();
                    formatConditions.setAutoDelete(false);
                    formatConditions.release();
                }

                private int convertToRGB(Color color) {
                    int red = color.getBlue();
                    int green = color.getGreen();
                    int blue = color.getRed();
                    int alpha = color.getAlpha();
                    return new Color(red, green, blue, alpha).getRGB();
                }
            });
            workbook.save();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                workbook.close(false);
            }
            if (application != null) {
                application.close(true);
            }
        }
    }
}