public void TestNRules()
{
HSSFWorkbook workbook = new HSSFWorkbook();
CellRangeAddress[] cellRanges =
{
new CellRangeAddress(0, 1, 0, 0),
new CellRangeAddress(0, 1, 2, 2),
};
CFRuleRecord[] rules =
{
CFRuleRecord.Create(workbook, "7"),
CFRuleRecord.Create(workbook, ComparisonOperator.BETWEEN,"2", "5"),
};
CFRecordsAggregate agg = new CFRecordsAggregate(cellRanges, rules);
byte[] serializedRecord = new byte[agg.RecordSize];
agg.Serialize(0, serializedRecord);
int nRules = NPOI.Util.LittleEndian.GetUShort(serializedRecord, 4);
if (nRules == 0)
{
throw new AssertFailedException("Identified bug 45682 b");
}
Assert.AreEqual(rules.Length, nRules);
}
public void TestConstructors()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
CFRuleRecord rule1 = CFRuleRecord.Create((HSSFSheet)sheet, "7");
Assert.AreEqual(CFRuleRecord.CONDITION_TYPE_FORMULA, rule1.ConditionType);
Assert.AreEqual((byte)ComparisonOperator.NoComparison, rule1.ComparisonOperation);
Assert.IsNotNull(rule1.ParsedExpression1);
Assert.AreSame(Ptg.EMPTY_PTG_ARRAY, rule1.ParsedExpression2);
CFRuleRecord rule2 = CFRuleRecord.Create((HSSFSheet)sheet, (byte)ComparisonOperator.Between, "2", "5");
Assert.AreEqual(CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS, rule2.ConditionType);
Assert.AreEqual((byte)ComparisonOperator.Between, rule2.ComparisonOperation);
Assert.IsNotNull(rule2.ParsedExpression1);
Assert.IsNotNull(rule2.ParsedExpression2);
CFRuleRecord rule3 = CFRuleRecord.Create((HSSFSheet)sheet, (byte)ComparisonOperator.Equal, null, null);
Assert.AreEqual(CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS, rule3.ConditionType);
Assert.AreEqual((byte)ComparisonOperator.Equal, rule3.ComparisonOperation);
Assert.AreSame(Ptg.EMPTY_PTG_ARRAY, rule3.ParsedExpression2);
Assert.AreSame(Ptg.EMPTY_PTG_ARRAY, rule3.ParsedExpression2);
}
public void TestCreateCFRuleRecord()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
CFRuleRecord record = CFRuleRecord.Create(sheet, "7");
TestCFRuleRecord1(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 CFRuleRecord(TestcaseRecordInputStream.Create(CFRuleRecord.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], "CFRuleRecord doesn't match");
}
}
/// <summary>
/// A factory method allowing to Create a conditional formatting rule with a formula.
/// The formatting rules are applied by Excel when the value of the formula not equal to 0.
/// TODO - formulas containing cell references are currently not Parsed properly
/// </summary>
/// <param name="formula">formula for the valued, Compared with the cell</param>
/// <returns></returns>
public IConditionalFormattingRule CreateConditionalFormattingRule(String formula)
{
HSSFWorkbook wb = (HSSFWorkbook)_sheet.Workbook;
CFRuleRecord rr = CFRuleRecord.Create(_sheet, formula);
return(new HSSFConditionalFormattingRule(wb, rr));
}
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) { }
}
public IConditionalFormattingRule CreateConditionalFormattingRule(
ComparisonOperator comparisonOperation,
String formula1)
{
CFRuleRecord rr = CFRuleRecord.Create(_sheet, (byte)comparisonOperation, formula1, null);
return(new HSSFConditionalFormattingRule(_sheet, rr));
}
public IConditionalFormattingRule CreateConditionalFormattingRule(
ComparisonOperator comparisonOperation,
string formula1)
{
HSSFWorkbook wb = (HSSFWorkbook)_sheet.Workbook;
CFRuleRecord rr = CFRuleRecord.Create(_sheet, (byte)comparisonOperation, formula1, null);
return(new HSSFConditionalFormattingRule(wb, rr));
}
/// <summary>
/// A factory method allowing to Create a conditional formatting rule
/// with a cell comparison operator
/// TODO - formulas containing cell references are currently not Parsed properly
/// </summary>
/// <param name="comparisonOperation">a constant value from HSSFConditionalFormattingRule.ComparisonOperator</param>
/// <param name="formula1">formula for the valued, Compared with the cell</param>
/// <param name="formula2">second formula (only used with HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN
/// and HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN operations)</param>
/// <returns></returns>
public HSSFConditionalFormattingRule CreateConditionalFormattingRule(
ComparisonOperator comparisonOperation,
String formula1,
String formula2)
{
HSSFWorkbook wb = _workbook;
CFRuleRecord rr = CFRuleRecord.Create(wb, comparisonOperation, formula1, formula2);
return(new HSSFConditionalFormattingRule(wb, rr));
}
public void TestBug53691()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
CFRuleRecord record = CFRuleRecord.Create(sheet, (byte)ComparisonOperator.Between, "2", "5") as CFRuleRecord;
CFRuleRecord clone = (CFRuleRecord)record.Clone();
byte[] SerializedRecord = record.Serialize();
byte[] SerializedClone = clone.Serialize();
Assert.That(SerializedRecord, new EqualConstraint(SerializedClone));
}
public void TestWrite()
{
HSSFWorkbook workbook = new HSSFWorkbook();
CFRuleRecord rr = CFRuleRecord.Create(workbook, ComparisonOperator.BETWEEN, "5", "10");
PatternFormatting patternFormatting = new PatternFormatting();
patternFormatting.FillPattern = (PatternFormatting.BRICKS);
rr.PatternFormatting = (patternFormatting);
byte[] data = rr.Serialize();
Assert.AreEqual(26, data.Length);
Assert.AreEqual(3, LittleEndian.GetShort(data, 6));
Assert.AreEqual(3, LittleEndian.GetShort(data, 8));
int flags = LittleEndian.GetInt(data, 10);
Assert.AreEqual(0x00380000, flags & 0x00380000, "unused flags should be 111");
Assert.AreEqual(0, flags & 0x03C00000, "undocumented flags should be 0000"); // Otherwise Excel s unhappy
// check all remaining flag bits (some are not well understood yet)
Assert.AreEqual(0x203FFFFF, flags);
}
/// <summary>
/// A factory method allowing to Create a conditional formatting rule with a formula.
/// The formatting rules are applied by Excel when the value of the formula not equal to 0.
/// TODO - formulas containing cell references are currently not Parsed properly
/// </summary>
/// <param name="formula">formula for the valued, Compared with the cell</param>
/// <returns></returns>
public IConditionalFormattingRule CreateConditionalFormattingRule(String formula)
{
CFRuleRecord rr = CFRuleRecord.Create(_sheet, formula);
return(new HSSFConditionalFormattingRule(_sheet, rr));
}
public void TestCFRecordsAggregate()
{
HSSFWorkbook workbook = new HSSFWorkbook();
IList recs = new ArrayList();
CFHeaderRecord header = new CFHeaderRecord();
CFRuleRecord rule1 = CFRuleRecord.Create(workbook, "7");
CFRuleRecord rule2 = CFRuleRecord.Create(workbook, ComparisonOperator.BETWEEN, "2", "5");
CFRuleRecord rule3 = CFRuleRecord.Create(workbook, ComparisonOperator.GE, "100", null);
header.NumberOfConditionalFormats = (3);
CellRangeAddress[] cellRanges =
{
new CellRangeAddress(0, 1, 0, 0),
new CellRangeAddress(0, 1, 2, 2),
};
header.CellRanges = (cellRanges);
recs.Add(header);
recs.Add(rule1);
recs.Add(rule2);
recs.Add(rule3);
CFRecordsAggregate record;
record = CFRecordsAggregate.CreateCFAggregate(recs, 0);
// Serialize
byte [] serializedRecord = new byte[record.RecordSize];
record.Serialize(0, serializedRecord);
Stream in1 = new MemoryStream(serializedRecord);
//Parse
recs = RecordFactory.CreateRecords(in1);
// Verify
Assert.IsNotNull(recs);
Assert.AreEqual(4, recs.Count);
header = (CFHeaderRecord)recs[0];
rule1 = (CFRuleRecord)recs[1];
rule2 = (CFRuleRecord)recs[2];
rule3 = (CFRuleRecord)recs[3];
cellRanges = header.CellRanges;
Assert.AreEqual(2, cellRanges.Length);
Assert.AreEqual(3, header.NumberOfConditionalFormats);
record = CFRecordsAggregate.CreateCFAggregate(recs, 0);
record = record.CloneCFAggregate();
Assert.IsNotNull(record.Header);
Assert.AreEqual(3, record.NumberOfRules);
header = record.Header;
rule1 = record.GetRule(0);
rule2 = record.GetRule(1);
rule3 = record.GetRule(2);
cellRanges = header.CellRanges;
Assert.AreEqual(2, cellRanges.Length);
Assert.AreEqual(3, header.NumberOfConditionalFormats);
}
public void TestCFRecordsAggregate1()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
List <Record> recs = new List <Record>();
CFHeaderBase header = new CFHeaderRecord();
CFRuleBase rule1 = CFRuleRecord.Create(sheet, "7");
CFRuleBase rule2 = CFRuleRecord.Create(sheet, (byte)ComparisonOperator.Between, "2", "5");
CFRuleBase rule3 = CFRuleRecord.Create(sheet, (byte)ComparisonOperator.GreaterThanOrEqual, "100", null);
header.NumberOfConditionalFormats = (3);
CellRangeAddress[] cellRanges =
{
new CellRangeAddress(0, 1, 0, 0),
new CellRangeAddress(0, 1, 2, 2),
};
header.CellRanges = (cellRanges);
recs.Add(header);
recs.Add(rule1);
recs.Add(rule2);
recs.Add(rule3);
CFRecordsAggregate record = CFRecordsAggregate.CreateCFAggregate(new RecordStream(recs, 0));
// Serialize
byte [] serializedRecord = new byte[record.RecordSize];
record.Serialize(0, serializedRecord);
Stream in1 = new MemoryStream(serializedRecord);
//Parse
recs = RecordFactory.CreateRecords(in1);
// Verify
Assert.IsNotNull(recs);
Assert.AreEqual(4, recs.Count);
header = (CFHeaderRecord)recs[0];
rule1 = (CFRuleRecord)recs[1];
Assert.IsNotNull(rule1);
rule2 = (CFRuleRecord)recs[2];
Assert.IsNotNull(rule2);
rule3 = (CFRuleRecord)recs[3];
Assert.IsNotNull(rule3);
cellRanges = header.CellRanges;
Assert.AreEqual(2, cellRanges.Length);
Assert.AreEqual(3, header.NumberOfConditionalFormats);
Assert.IsFalse(header.NeedRecalculation);
record = CFRecordsAggregate.CreateCFAggregate(new RecordStream(recs, 0));
record = record.CloneCFAggregate();
Assert.IsNotNull(record.Header);
Assert.AreEqual(3, record.NumberOfRules);
header = record.Header;
rule1 = record.GetRule(0);
Assert.IsNotNull(rule1);
rule2 = record.GetRule(1);
Assert.IsNotNull(rule2);
rule3 = record.GetRule(2);
Assert.IsNotNull(rule3);
cellRanges = header.CellRanges;
Assert.AreEqual(2, cellRanges.Length);
Assert.AreEqual(3, header.NumberOfConditionalFormats);
Assert.IsFalse(header.NeedRecalculation);
}
