public void TestCantMixTypes()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
CellRangeAddress[] cellRanges =
{
new CellRangeAddress(0, 1, 0, 0),
new CellRangeAddress(0, 1, 2, 2),
};
CFRuleBase[] rules =
{
CFRuleRecord.Create(sheet, "7"),
CFRule12Record.Create(sheet, (byte)ComparisonOperator.Between,"2", "5"),
};
try
{
new CFRecordsAggregate(cellRanges, rules);
Assert.Fail("Shouldn't be able to mix between types");
}
catch (ArgumentException) { }
rules = new CFRuleBase[] { CFRuleRecord.Create(sheet, "7") };
CFRecordsAggregate agg = new CFRecordsAggregate(cellRanges, rules);
Assert.IsTrue(agg.Header.NeedRecalculation);
try
{
agg.AddRule(CFRule12Record.Create(sheet, "7"));
Assert.Fail("Shouldn't be able to mix between types");
}
catch (ArgumentException) { }
}
/**
* A factory method allowing the creation of conditional formatting
* rules using an Icon Set / Multi-State formatting.
* The thresholds for it will be created, but will be empty
* and require configuring with
* {@link HSSFConditionalFormattingRule#getMultiStateFormatting()}
* then
* {@link HSSFIconMultiStateFormatting#getThresholds()}
*/
public IConditionalFormattingRule CreateConditionalFormattingRule(
IconSet iconSet)
{
CFRule12Record rr = CFRule12Record.Create(_sheet, iconSet);
return(new HSSFConditionalFormattingRule(_sheet, rr));
}
public void TestCreateIconCFRule12Record()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
CFRule12Record record = CFRule12Record.Create(sheet, IconSet.GREY_5_ARROWS);
record.MultiStateFormatting.Thresholds[1].Type = (byte)(RangeType.PERCENT.id);
record.MultiStateFormatting.Thresholds[1].Value = (10d);
record.MultiStateFormatting.Thresholds[2].Type = (byte)(RangeType.NUMBER.id);
record.MultiStateFormatting.Thresholds[2].Value = (-4d);
// Check it
testCFRule12Record(record);
Assert.AreEqual(IconSet.GREY_5_ARROWS, record.MultiStateFormatting.IconSet);
Assert.AreEqual(5, record.MultiStateFormatting.Thresholds.Length);
// Serialize
byte[] serializedRecord = record.Serialize();
// Strip header
byte[] recordData = new byte[serializedRecord.Length - 4];
Array.Copy(serializedRecord, 4, recordData, 0, recordData.Length);
// Deserialize
record = new CFRule12Record(TestcaseRecordInputStream.Create(CFRule12Record.sid, recordData));
// Check it has the icon, and the right number of thresholds
Assert.AreEqual(IconSet.GREY_5_ARROWS, record.MultiStateFormatting.IconSet);
Assert.AreEqual(5, record.MultiStateFormatting.Thresholds.Length);
// Serialize again
byte[] output = record.Serialize();
// Compare
Assert.AreEqual(recordData.Length + 4, output.Length, "Output size"); //includes sid+recordlength
for (int i = 0; i < recordData.Length; i++)
{
Assert.AreEqual(recordData[i], output[i + 4], "CFRule12Record doesn't match");
}
}
/**
* @return <c>false</c> if this whole {@link CFHeaderRecord} / {@link CFRuleRecord}s should be deleted
*/
public bool UpdateFormulasAfterCellShift(FormulaShifter shifter, int currentExternSheetIx)
{
CellRangeAddress[] cellRanges = header.CellRanges;
bool changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
foreach (CellRangeAddress craOld in cellRanges)
{
CellRangeAddress craNew = ShiftRange(shifter, craOld, currentExternSheetIx);
if (craNew == null)
{
changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
changed = true;
}
}
if (changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
return(false);
}
CellRangeAddress[] newRanges = new CellRangeAddress[nRanges];
newRanges = temp.ToArray();
header.CellRanges = (newRanges);
}
foreach (CFRuleBase rule in rules)
{
Ptg[] ptgs;
ptgs = rule.ParsedExpression1;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule.ParsedExpression1 = (ptgs);
}
ptgs = rule.ParsedExpression2;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule.ParsedExpression2 = (ptgs);
}
if (rule is CFRule12Record)
{
CFRule12Record rule12 = (CFRule12Record)rule;
ptgs = rule12.ParsedExpressionScale;
if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx))
{
rule12.ParsedExpressionScale = (ptgs);
}
}
}
return(true);
}
private HSSFColorScaleFormatting GetColorScaleFormatting(bool create)
{
CFRule12Record cfRule12Record = GetCFRule12Record(create);
ColorGradientFormatting colorFormatting = cfRule12Record.ColorGradientFormatting;
if (colorFormatting != null)
{
return(new HSSFColorScaleFormatting(cfRule12Record, sheet));
}
else if (create)
{
colorFormatting = cfRule12Record.CreateColorGradientFormatting();
return(new HSSFColorScaleFormatting(cfRule12Record, sheet));
}
else
{
return(null);
}
}
private HSSFIconMultiStateFormatting GetMultiStateFormatting(bool create)
{
CFRule12Record cfRule12Record = GetCFRule12Record(create);
IconMultiStateFormatting iconFormatting = cfRule12Record.MultiStateFormatting;
if (iconFormatting != null)
{
return(new HSSFIconMultiStateFormatting(cfRule12Record, sheet));
}
else if (create)
{
iconFormatting = cfRule12Record.CreateMultiStateFormatting();
return(new HSSFIconMultiStateFormatting(cfRule12Record, sheet));
}
else
{
return(null);
}
}
private HSSFDataBarFormatting GetDataBarFormatting(bool create)
{
CFRule12Record cfRule12Record = GetCFRule12Record(create);
DataBarFormatting databarFormatting = cfRule12Record.DataBarFormatting;
if (databarFormatting != null)
{
return(new HSSFDataBarFormatting(cfRule12Record, sheet));
}
else if (create)
{
databarFormatting = cfRule12Record.CreateDataBarFormatting();
return(new HSSFDataBarFormatting(cfRule12Record, sheet));
}
else
{
return(null);
}
}
public void TestCreateCFRule12Record()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
CFRule12Record record = CFRule12Record.Create(sheet, "7");
testCFRule12Record(record);
// Serialize
byte[] serializedRecord = record.Serialize();
// Strip header
byte[] recordData = new byte[serializedRecord.Length - 4];
Array.Copy(serializedRecord, 4, recordData, 0, recordData.Length);
// Deserialize
record = new CFRule12Record(TestcaseRecordInputStream.Create(CFRule12Record.sid, recordData));
// Serialize again
byte[] output = record.Serialize();
// Compare
Assert.AreEqual(recordData.Length + 4, output.Length, "Output size"); //includes sid+recordlength
for (int i = 0; i < recordData.Length; i++)
{
Assert.AreEqual(recordData[i], output[i + 4], "CFRule12Record doesn't match");
}
}
/**
* Create a Color Scale / Color Gradient conditional formatting rule.
* <p>The thresholds and colours for it will be created, but will be
* empty and require configuring with
* {@link HSSFConditionalFormattingRule#getColorScaleFormatting()}
* then
* {@link HSSFColorScaleFormatting#getThresholds()}
* and
* {@link HSSFColorScaleFormatting#getColors()}
*/
public IConditionalFormattingRule CreateConditionalFormattingColorScaleRule()
{
CFRule12Record rr = CFRule12Record.CreateColorScale(_sheet);
return(new HSSFConditionalFormattingRule(_sheet, rr));
}
/**
* Create a Databar conditional formatting rule.
* <p>The thresholds and colour for it will be created, but will be
* empty and require configuring with
* {@link HSSFConditionalFormattingRule#getDataBarFormatting()}
* then
* {@link HSSFDataBarFormatting#getMinThreshold()}
* and
* {@link HSSFDataBarFormatting#getMaxThreshold()}
*/
public HSSFConditionalFormattingRule CreateConditionalFormattingRule(HSSFExtendedColor color)
{
CFRule12Record rr = CFRule12Record.Create(_sheet, color.ExtendedColor);
return(new HSSFConditionalFormattingRule(_sheet, rr));
}
protected internal HSSFColorScaleFormatting(CFRule12Record cfRule12Record, HSSFSheet sheet)
{
this.sheet = sheet;
this.cfRule12Record = cfRule12Record;
this.colorFormatting = this.cfRule12Record.ColorGradientFormatting;
}
protected internal HSSFDataBarFormatting(CFRule12Record cfRule12Record, HSSFSheet sheet)
{
this.sheet = sheet;
this.cfRule12Record = cfRule12Record;
this.databarFormatting = this.cfRule12Record.DataBarFormatting;
}
protected internal HSSFIconMultiStateFormatting(CFRule12Record cfRule12Record, HSSFSheet sheet)
{
this.sheet = sheet;
this.cfRule12Record = cfRule12Record;
this.iconFormatting = this.cfRule12Record.MultiStateFormatting;
}
private void testCFRule12Record(CFRule12Record record)
{
Assert.AreEqual(CFRule12Record.sid, record.GetFutureRecordType());
Assert.AreEqual("A1", record.GetAssociatedRange().FormatAsString());
testCFRuleBase(record);
}