public CFHeaderRecord(CellRangeAddress[] regions, int nRules)
{
CellRangeAddress[] unmergedRanges = regions;
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges);
CellRanges= mergeCellRanges;
field_1_numcf = nRules;
}
/**
* Intersect this range with the specified range.
*
* @param crB - the specified range
* @return code which reflects how the specified range is related to this range.<br/>
* Possible return codes are:
* NO_INTERSECTION - the specified range is outside of this range;<br/>
* OVERLAP - both ranges partially overlap;<br/>
* INSIDE - the specified range is inside of this one<br/>
* ENCLOSES - the specified range encloses (possibly exactly the same as) this range<br/>
*/
public static int Intersect(CellRangeAddress crA, CellRangeAddress crB)
{
int firstRow = crB.FirstRow;
int lastRow = crB.LastRow;
int firstCol = crB.FirstColumn;
int lastCol = crB.LastColumn;
if
(
gt(crA.FirstRow, lastRow) ||
lt(crA.LastRow, firstRow) ||
gt(crA.FirstColumn, lastCol) ||
lt(crA.LastColumn, firstCol)
)
{
return NO_INTERSECTION;
}
else if (Contains(crA, crB))
{
return INSIDE;
}
else if (Contains(crB, crA))
{
return ENCLOSES;
}
else
{
return OVERLAP;
}
}
public CFHeaderRecord(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
/**
* Constructs a MergedCellsRecord and Sets its fields appropriately
* @param in the RecordInputstream to Read the record from
*/
public MergeCellsRecord(RecordInputStream in1)
{
int nRegions = in1.ReadUShort();
CellRangeAddress[] cras = new CellRangeAddress[nRegions];
for (int i = 0; i < nRegions; i++)
{
cras[i] = new CellRangeAddress(in1);
}
_numberOfRegions = nRegions;
_startIndex = 0;
_regions = cras;
}
/// <summary>
/// Sets the left border for a region of cells by manipulating the cell style
/// of the individual cells on the left
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderLeft(int border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
private static CellRangeAddress[] ToArray(ArrayList temp)
{
CellRangeAddress[] result = new CellRangeAddress[temp.Count];
result = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress));
return result;
}
/**
* @param crB never a full row or full column range
* @return an array including <b>this</b> <tt>CellRange</tt> and all parts of <tt>range</tt>
* outside of this range
*/
private static CellRangeAddress[] SliceUp(CellRangeAddress crA, CellRangeAddress crB)
{
ArrayList temp = new ArrayList();
// Chop up range horizontally and vertically
temp.Add(crB);
if (!crA.IsFullColumnRange)
{
temp = CutHorizontally(crA.FirstRow, temp);
temp = CutHorizontally(crA.LastRow + 1, temp);
}
if (!crA.IsFullRowRange)
{
temp = CutVertically(crA.FirstColumn, temp);
temp = CutVertically(crA.LastColumn + 1, temp);
}
CellRangeAddress[] crParts = ToArray(temp);
// form result array
temp.Clear();
temp.Add(crA);
for (int i = 0; i < crParts.Length; i++)
{
CellRangeAddress crPart = crParts[i];
// only include parts that are not enclosed by this
if (Intersect(crA, crPart) != ENCLOSES)
{
temp.Add(crPart);
}
}
return ToArray(temp);
}
public MergeCellsRecord(CellRangeAddress[] regions, int startIndex, int numberOfRegions)
{
_regions = regions;
_startIndex = startIndex;
_numberOfRegions = numberOfRegions;
}
/**
* Convert a List of CellRange objects to an array of regions
*
* @param List of CellRange objects
* @return regions
*/
public static Region[] ConvertCellRangesToRegions(CellRangeAddress[] cellRanges)
{
int size = cellRanges.Length;
if (size < 1)
{
return new Region[0];
}
Region[] result = new Region[size];
for (int i = 0; i != size; i++)
{
result[i] = ConvertToRegion(cellRanges[i]);
}
return result;
}
/**
* Add a cell range structure.
*
* @param firstRow - the upper left hand corner's row
* @param firstCol - the upper left hand corner's col
* @param lastRow - the lower right hand corner's row
* @param lastCol - the lower right hand corner's col
* @return the index of this ADDR structure
*/
public void AddCellRangeAddress(int firstRow, int firstCol, int lastRow, int lastCol)
{
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
AddCellRangeAddress(region);
}
/**
* Create an enclosing CellRange for the two cell ranges.
*
* @return enclosing CellRange
*/
public static CellRangeAddress CreateEnclosingCellRange(CellRangeAddress crA, CellRangeAddress crB)
{
if (crB == null)
{
return crA.Copy();
}
return
new CellRangeAddress(
lt(crB.FirstRow, crA.FirstRow) ? crB.FirstRow : crA.FirstRow,
gt(crB.LastRow, crA.LastRow) ? crB.LastRow : crA.LastRow,
lt(crB.FirstColumn, crA.FirstColumn) ? crB.FirstColumn : crA.FirstColumn,
gt(crB.LastColumn, crA.LastColumn) ? crB.LastColumn : crA.LastColumn
);
}
/// <summary>
/// 合并Gridview单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(HSSFSheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
/// <summary>
/// Sets the topBorderColor attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="color">The color of the border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetTopBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.FirstRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.TOP_BORDER_COLOR, color);
HSSFRow row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
/// <summary>
/// adds a merged region of cells (hence those cells form one)
/// </summary>
/// <param name="region">region (rowfrom/colfrom-rowto/colto) to merge</param>
/// <returns>index of this region</returns>
public int AddMergedRegion(CellRangeAddress region)
{
return sheet.AddMergedRegion(region.FirstRow,
region.FirstColumn,
region.LastRow,
region.LastColumn);
}
private static bool ContainsCell(CellRangeAddress cr, int rowIx, int colIx)
{
if (cr.FirstRow <= rowIx && cr.LastRow >= rowIx
&& cr.FirstColumn <= colIx && cr.LastColumn >= colIx)
{
return true;
}
return false;
}
public void AddCellRangeAddress(CellRangeAddress cra)
{
_list.Add(cra);
}
/**
* Check if the specified range is located inside of this cell range.
*
* @param crB
* @return true if this cell range Contains the argument range inside if it's area
*/
public static bool Contains(CellRangeAddress crA, CellRangeAddress crB)
{
int firstRow = crB.FirstRow;
int lastRow = crB.LastRow;
int firstCol = crB.FirstColumn;
int lastCol = crB.LastColumn;
return le(crA.FirstRow, firstRow) && ge(crA.LastRow, lastRow)
&& le(crA.FirstColumn, firstCol) && ge(crA.LastColumn, lastCol);
}
/**
* Do all possible cell merges between cells of the list so that:
* if a cell range is completely inside of another cell range, it s removed from the list
* if two cells have a shared border, merge them into one bigger cell range
* @param cellRangeList
* @return updated List of cell ranges
*/
public static CellRangeAddress[] MergeCellRanges(CellRangeAddress[] cellRanges)
{
if (cellRanges.Length < 1)
{
return cellRanges;
}
ArrayList temp = MergeCellRanges(NPOI.Util.Arrays.AsList(cellRanges));
return ToArray(temp);
}
/**
* Check if the specified cell range has a shared border with the current range.
*
* @return <c>true</c> if the ranges have a complete shared border (i.e.
* the two ranges toher make a simple rectangular region.
*/
public static bool HasExactSharedBorder(CellRangeAddress crA, CellRangeAddress crB)
{
int oFirstRow = crB.FirstRow;
int oLastRow = crB.LastRow;
int oFirstCol = crB.FirstColumn;
int oLastCol = crB.LastColumn;
if (crA.FirstRow > 0 && crA.FirstRow - 1 == oLastRow ||
oFirstRow > 0 && oFirstRow - 1 == crA.LastRow)
{
// ranges have a horizontal border in common
// make sure columns are identical:
return crA.FirstColumn == oFirstCol && crA.LastColumn == oLastCol;
}
if (crA.FirstColumn > 0 && crA.FirstColumn - 1 == oLastCol ||
oFirstCol > 0 && crA.LastColumn == oFirstCol - 1)
{
// ranges have a vertical border in common
// make sure rows are identical:
return crA.FirstRow == oFirstRow && crA.LastRow == oLastRow;
}
return false;
}
/// <summary>
/// Adds the conditional formatting.
/// </summary>
/// <param name="regions">The regions.</param>
/// <param name="rule1">The rule1.</param>
/// <param name="rule2">The rule2.</param>
/// <returns></returns>
public int AddConditionalFormatting(CellRangeAddress[] regions,
HSSFConditionalFormattingRule rule1,
HSSFConditionalFormattingRule rule2)
{
return AddConditionalFormatting(regions,
new HSSFConditionalFormattingRule[]
{
rule1, rule2
});
}
/**
* @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;
ArrayList temp = new ArrayList();
for (int i = 0; i < cellRanges.Length; i++)
{
CellRangeAddress craOld = cellRanges[i];
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 = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress));
header.CellRanges=(newRanges);
}
for (int i = 0; i < rules.Count; i++)
{
CFRuleRecord rule = (CFRuleRecord)rules[i];
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);
}
}
return true;
}
private static Region ConvertToRegion(CellRangeAddress cr)
{
return(new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn));
}
/// <summary>
/// Allows to Add a new Conditional Formatting Set to the sheet.
/// </summary>
/// <param name="regions">list of rectangular regions to apply conditional formatting rules</param>
/// <param name="cfRules">Set of up to three conditional formatting rules</param>
/// <returns>index of the newly Created Conditional Formatting object</returns>
public int AddConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules)
{
if (regions == null)
{
throw new ArgumentException("regions must not be null");
}
if (cfRules == null)
{
throw new ArgumentException("cfRules must not be null");
}
if (cfRules.Length == 0)
{
throw new ArgumentException("cfRules must not be empty");
}
if (cfRules.Length > 3)
{
throw new ArgumentException("Number of rules must not exceed 3");
}
CFRuleRecord[] rules = new CFRuleRecord[cfRules.Length];
for (int i = 0; i != cfRules.Length; i++)
{
rules[i] = cfRules[i].CfRuleRecord;
}
CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules);
return _conditionalFormattingTable.Add(cfra);
}
private static CellRangeAddress ShiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
// FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
AreaPtg aptg = new AreaPtg(cra.FirstRow, cra.LastRow, cra.FirstColumn, cra.LastColumn, false, false, false, false);
Ptg[] ptgs = { aptg, };
if (!shifter.AdjustFormula(ptgs, currentExternSheetIx)) {
return cra;
}
Ptg ptg0 = ptgs[0];
if (ptg0 is AreaPtg) {
AreaPtg bptg = (AreaPtg) ptg0;
return new CellRangeAddress(bptg.FirstRow, bptg.LastRow, bptg.FirstColumn, bptg.LastColumn);
}
if (ptg0 is AreaErrPtg) {
return null;
}
throw new InvalidCastException("Unexpected shifted ptg class (" + ptg0.GetType().Name + ")");
}
private static Region ConvertToRegion(CellRangeAddress cr)
{
return new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn);
}
public CFRecordsAggregate(CellRangeAddress[] regions, CFRuleRecord[] rules)
: this(new CFHeaderRecord(regions, rules.Length), rules)
{
}
public static CellRangeAddress[] ConvertRegionsToCellRanges(Region[] regions)
{
int size = regions.Length;
if (size < 1)
{
return new CellRangeAddress[0];
}
CellRangeAddress[] result = new CellRangeAddress[size];
for (int i = 0; i != size; i++)
{
result[i] = ConvertToCellRangeAddress(regions[i]);
}
return result;
}
/**
* @return the new range(s) to replace the supplied ones. <c>null</c> if no merge is possible
*/
private static CellRangeAddress[] MergeRanges(CellRangeAddress range1, CellRangeAddress range2)
{
int x = Intersect(range1, range2);
switch (x)
{
case CellRangeUtil.NO_INTERSECTION:
if (HasExactSharedBorder(range1, range2))
{
return new CellRangeAddress[] { CreateEnclosingCellRange(range1, range2), };
}
// else - No intersection and no shared border: do nothing
return null;
case CellRangeUtil.OVERLAP:
return ResolveRangeOverlap(range1, range2);
case CellRangeUtil.INSIDE:
// Remove range2, since it is completely inside of range1
return new CellRangeAddress[] { range1, };
case CellRangeUtil.ENCLOSES:
// range2 encloses range1, so replace it with the enclosing one
return new CellRangeAddress[] { range2, };
}
throw new InvalidOperationException("unexpected intersection result (" + x + ")");
}
// TODO - write junit test for this
static CellRangeAddress[] ResolveRangeOverlap(CellRangeAddress rangeA, CellRangeAddress rangeB)
{
if (rangeA.IsFullColumnRange)
{
if (rangeA.IsFullRowRange)
{
// Excel seems to leave these unresolved
return null;
}
return SliceUp(rangeA, rangeB);
}
if (rangeA.IsFullRowRange)
{
if (rangeB.IsFullColumnRange)
{
// Excel seems to leave these unresolved
return null;
}
return SliceUp(rangeA, rangeB);
}
if (rangeB.IsFullColumnRange)
{
return SliceUp(rangeB, rangeA);
}
if (rangeB.IsFullRowRange)
{
return SliceUp(rangeB, rangeA);
}
return SliceUp(rangeA, rangeB);
}
public override Object Clone()
{
int nRegions = _numberOfRegions;
CellRangeAddress[] clonedRegions = new CellRangeAddress[nRegions];
for (int i = 0; i < clonedRegions.Length; i++)
{
clonedRegions[i] = _regions[_startIndex + i].Copy();
}
return new MergeCellsRecord(clonedRegions, 0, nRegions);
}
