package com.poi;
import java.io.File;
import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
import javax.swing.plaf.synth.Region;
import org.apache.poi.hssf.record.CFRuleRecord;
import org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator;
import org.apache.poi.hssf.record.formula.functions.Cell;
import org.apache.poi.hssf.record.formula.functions.Row;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFFontFormatting;
import org.apache.poi.hssf.usermodel.HSSFPalette;
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.CellRangeAddress;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.eventfilesystem.POIFSReader;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.jexcels.JxlPro;
public class POISheet {
private static HSSFPatternFormatting patternFormatting;
private int col;
private int row;
private ArrayList<HSSFConditionalFormattingRule> rules = new ArrayList();
private HSSFConditionalFormattingRule[] rule = null;
private HSSFConditionalFormattingRule rule1 =null;
private HSSFConditionalFormattingRule rule2 =null;
private HSSFConditionalFormattingRule rule3 =null;
HSSFSheet sheet2=null;
private boolean GE=false,LE=false,EE=false;
public POISheet()
{
rule=new HSSFConditionalFormattingRule[16];
}
@SuppressWarnings({ "null", "deprecation" })
public void export(File excelFile) throws FileNotFoundException, IOException
{
File file;
file=excelFile;
//HSSFWorkbook wb = new HSSFWorkbook();
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb=new HSSFWorkbook(fs);
FileOutputStream fileOut = new FileOutputStream(file);
HSSFSheet sheet = wb.getSheetAt(0);
sheet2=sheet;
HSSFSheetConditionalFormatting sheet1 = sheet.getSheetConditionalFormatting();
Vector vector=new Vector();
/***Note:- In Condition not Exceed 3, Only three condition allow to Declare **/
vector.add("A1 LT $B$# - 0.5 [paleBlue], GT $C$# + 0.5 [red], NE ISBLANK($A$#) [white]");
vector.add("D1 LT $C$# * 2 [organe], GT $F$# * -2 [paleBlue]");
String na="\"NA\" [red]";
String check="F1 EQ "+na;
vector.add(check);
// vector.add("C1 < $E$# / SQRT(12) * -2");, = $F$1 R
// vector.add("F1 != $F$1");
// vector.add("D1 > $F$# * -2");NE ISBLANK($A$#) Y
// vector.add("G1 = $F$1");
// vector.add("F1 = $F$1");
// vector.add("L1 < $R$# - 0.5");
// vector.add("L1 > $S$# + 0.5");
// vector.add("M1 < $S$# * 2");
// vector.add("N1 > $S$# * 2");
// vector.add("V1 < $L$# / SQRT(12) * -2");
// vector.add("V1 > $L$# / SQRT(12) * 2");
Enumeration e=vector.elements();
while(e.hasMoreElements()){
String s=(String) e.nextElement();
String trimformula = s.trim();
String colval=trimformula.substring(0, 2);
trimformula=trimformula.substring(2, trimformula.length());
CellReference val = new CellReference(colval);
int aa=val.getCol();
//Define to split the formulas
String[] result = trimformula.split("\\,");
int len= result.length;
String[] color = new String[len];
System.out.println("Totals:-"+result.length);
for (int x=0; x<result.length; x++)
{
System.out.println("Splited Formula :-"+x+" "+result[x]);
int ind=result[x].length();
int startIndex=result[x].indexOf("[");
int lastIndex=result[x].indexOf("]");
color[x]= result[x].substring(startIndex+1,lastIndex);
result[x]= result[x].substring(0,ind-color[x].length()-2);
//color[x]= result[x].substring(ind-1,ind).trim();
//result[x]= result[x].substring(0,ind-1);
System.out.println("Color:-"+color[x]);
System.out.println("Formula:-"+result[x]);
}
ArrayList<HSSFConditionalFormattingRule> collectFormula = new ArrayList();
int indexs=0;
//Start Each Formula valud
for(int re=0;re<result.length;re++)
{
//Each Rows Valud
for(int i=1;i<=15;i++)
{
String st=Integer.toString(i);
String formulaEach=result[re];
System.out.println("System Processing on this Formula:- ["+i+"]"+formulaEach);
String formula1= formulaEach.replaceAll("#", st);
//String converted in formula2
if(formula1.indexOf("LT")!=-1)
{
int index=formula1.indexOf("LT");
int size=formula1.length();
String conditional=formula1.substring(index+2,size);
System.out.println("Conditonal 1 \n Col value:-"+col+"\nFormula:-"+conditional);
rule[re]=sheet1.createConditionalFormattingRule(ComparisonOperator.LT,conditional,null);
collectFormula.add(rule[re]);
System.out.println("collectFormula Size:-" + collectFormula.size());
HSSFPatternFormatting patternFmt1 = rule[re].createPatternFormatting();
short colors=this.getColors(color[re]);
patternFmt1.setFillBackgroundColor(colors);
}
if(formula1.indexOf("GT")!=-1)
{
int index=formula1.indexOf("GT");
int size=formula1.length();
String conditional=formula1.substring(index+2,size);
System.out.println("Conditonal 2 \n Col value:-"+col+"\nFormula:-"+conditional);
rule[re]=sheet1.createConditionalFormattingRule(ComparisonOperator.GT,conditional,null);
collectFormula.add(rule[re]);
System.out.println("collectFormula Size:-" + collectFormula.size());
HSSFPatternFormatting patternFmt1 = rule[re].createPatternFormatting();
short colors=this.getColors(color[re]);
patternFmt1.setFillBackgroundColor(colors);
}
if(formula1.indexOf("EQ")!=-1)
{
int index=formula1.indexOf("EQ");
int size=formula1.length();
String conditional=formula1.substring(index+2,size);
System.out.println("Conditonal 3 \n Col value:-"+col+"\nFormula:-"+conditional);
rule[re]=sheet1.createConditionalFormattingRule(ComparisonOperator.EQUAL,conditional,null);
collectFormula.add(rule[re]);
System.out.println("collectFormula Size:-" + collectFormula.size());
HSSFPatternFormatting patternFmt1 = rule[re].createPatternFormatting();
short colors=this.getColors(color[re]);
patternFmt1.setFillBackgroundColor(colors);
}
if(formula1.indexOf("BT")!=-1)
{
int index=formula1.indexOf("BT");
int size=formula1.length();
String conditional=formula1.substring(index+2,size);
System.out.println("Conditonal 4 Col value:-"+aa+" Formula:-"+conditional);
//Conditional want to give the conditional values (conditional1,conditional2)
rule[re]=sheet1.createConditionalFormattingRule(ComparisonOperator.BETWEEN,conditional,conditional);
HSSFPatternFormatting patternFmt4 = rule[re].createPatternFormatting();
short colors=this.getColors(color[re]);
patternFmt4.setFillBackgroundColor(colors);
}
if(formula1.indexOf("NE")!=-1)
{
int index=formula1.indexOf("NE");
int size=formula1.length();
String conditional=formula1.substring(index+2,size);
System.out.println("Conditonal 5 \n Col value:-"+col+"\nFormula:-"+conditional);
rule[re]=sheet1.createConditionalFormattingRule(ComparisonOperator.NOT_EQUAL,conditional,null);
collectFormula.add(rule[re]);
System.out.println("collectFormula Size:-" + collectFormula.size());
HSSFPatternFormatting patternFmt1 = rule[re].createPatternFormatting();
short colors=this.getColors(color[re]);
patternFmt1.setFillBackgroundColor(colors);
}
indexs++;
}
}
System.out.println("Indexs Ranges:-"+collectFormula.size());
int totalRanges=(collectFormula.size()/result.length);
int ruleIndexs=(totalRanges*2);
int rows=15;
for(int ii=0; ii<rows;ii++)
{
CellRangeAddress[] regions = { new CellRangeAddress(0,(short)ii, aa,(short)aa) };
if(result.length == 1)
{
rule1=collectFormula.get(ii);
System.out.println("Regions 1");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule1 });
}else if(result.length== 2)
{
rule1=collectFormula.get(ii);
rule2=collectFormula.get(totalRanges+ii);
System.out.println("Regions 2");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule1,rule2 });
}else if(result.length == 3)
{
rule1=collectFormula.get(ii);
System.out.println("Indexs Starting values 1:-"+(totalRanges+ii));
rule2=collectFormula.get((totalRanges+ii));
System.out.println("Indexs Starting values 2:-"+collectFormula.get(29));
rule3=collectFormula.get((ruleIndexs+ii));
System.out.println("Regions 3");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule1,rule2,rule3 });
}else
{
System.out.println("Error:- No Conditional Formatting Done..!");
}
}
collectFormula.clear();
// Define a region containing first column
rules.clear();
result= null;
color=null;
}
wb.write(fileOut);
fileOut.close();
System.out.println("Your file has been created succesfully");
}
private void writeConditionFormat(int result, HSSFConditionalFormattingRule[] rule4 ,HSSFSheetConditionalFormatting sheet1, int aa)
{
int rows=15;
HSSFConditionalFormattingRule[] rules=null;
sheet1 = sheet2.getSheetConditionalFormatting();
for(int ii=0; ii<rows;ii++)
{
CellRangeAddress[] regions = { new CellRangeAddress(0,(short)ii, aa,(short)aa) };
if(result == 1)
{
System.out.println("Regions 1");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule4[0] });
}else if(result == 2)
{
System.out.println("Regions 2");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule4[0],rule4[1] });
}else if(result == 3)
{
System.out.println("Regions 3");
sheet1.addConditionalFormatting(regions, new HSSFConditionalFormattingRule[] { rule4[0],rule4[1],rule4[2] });
}else
{
System.out.println("Error:- No Conditional Formatting Done..!");
}
}
}
private String result(int re) {
// TODO Auto-generated method stub
return null;
}
public void setCol(int col)
{
this.col=col;
}public int getCol()
{
return col;
}
public void setrow(int row)
{
this.row=row;
}public int getrow()
{
return row;
}
//GetColors
private short translate(String color)
{
if(color.equals("black"))
{
return HSSFColor.BLACK.index;
}
else if(color.equals("blue"))
{
return HSSFColor.BLUE.index;
}
else if(color.equals("blueGray"))
{
return HSSFColor.BLUE_GREY.index;
}
else if(color.equals("brightGreen"))
{
return HSSFColor.BRIGHT_GREEN.index;
}
else if(color.equals("brown"))
{
return HSSFColor.BROWN.index;
}
else if(color.equals("darkBlue"))
{
return HSSFColor.DARK_BLUE.index;
}
else if(color.equals("darkGreen"))
{
return HSSFColor.DARK_GREEN.index;
}
else if(color.equals("darkRed"))
{
return HSSFColor.DARK_RED.index;
}
else if(color.equals("darkTeal"))
{
return HSSFColor.DARK_TEAL.index;
}
else if(color.equals("darkYellow"))
{
return HSSFColor.DARK_YELLOW.index;
}
else if(color.equals("gray"))
{
return HSSFColor.GREY_50_PERCENT.index;
}
else if(color.equals("green"))
{
return HSSFColor.GREEN.index;
}
else if(color.equals("lightBlue"))
{
return HSSFColor.LIGHT_BLUE.index;
}
else if(color.equals("lightGreen"))
{
return HSSFColor.LIGHT_GREEN.index;
}
else if(color.equals("lightOrange"))
{
return HSSFColor.LIGHT_ORANGE.index;
}
else if(color.equals("lime"))
{
return HSSFColor.LIME.index;
}
else if(color.equals("oliveGreen"))
{
return HSSFColor.OLIVE_GREEN.index;
}
else if(color.equals("orange"))
{
return HSSFColor.ORANGE.index;
}
else if(color.equals("paleBlue"))
{
return HSSFColor.PALE_BLUE.index;
}
else if(color.equals("pink"))
{
return HSSFColor.PINK.index;
}
else if(color.equals("red"))
{
return HSSFColor.RED.index;
}
else if(color.equals("rose"))
{
return HSSFColor.ROSE.index;
}
else if(color.equals("tan"))
{
return HSSFColor.TAN.index;
}
else if(color.equals("teal"))
{
return HSSFColor.TEAL.index;
}
else if(color.equals("violet"))
{
return HSSFColor.VIOLET.index;
}
else if(color.equals("black"))
{
return HSSFColor.BLACK.index;
}
else if(color.equals("yellow"))
{
return HSSFColor.YELLOW.index;
}
else
{
return HSSFColor.WHITE.index;
}
}
private short getColors(String fgcolor)
{
return translate(fgcolor);
}
}
//End
