/**
* 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 FeatRecord(RecordInputStream in1)
{
futureHeader = new FtrHeader(in1);
isf_sharedFeatureType = in1.ReadShort();
reserved1 = (byte)in1.ReadByte();
reserved2 = in1.ReadInt();
int cref = in1.ReadUShort();
cbFeatData = in1.ReadInt();
reserved3 = in1.ReadShort();
cellRefs = new CellRangeAddress[cref];
for (int i = 0; i < cellRefs.Length; i++)
{
cellRefs[i] = new CellRangeAddress(in1);
}
switch (isf_sharedFeatureType)
{
case FeatHdrRecord.SHAREDFEATURES_ISFPROTECTION:
sharedFeature = new FeatProtection(in1);
break;
case FeatHdrRecord.SHAREDFEATURES_ISFFEC2:
sharedFeature = new FeatFormulaErr2(in1);
break;
case FeatHdrRecord.SHAREDFEATURES_ISFFACTOID:
sharedFeature = new FeatSmartTag(in1);
break;
default:
System.Console.WriteLine("Unknown Shared Feature " + isf_sharedFeatureType + " found!");
break;
}
}
public CFHeaderRecord(CellRangeAddress[] regions, int nRules)
{
CellRangeAddress[] unmergedRanges = regions;
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges);
CellRanges= mergeCellRanges;
field_1_numcf = nRules;
}
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;
}
/**
* Sets the left border for a region of cells by manipulating the cell style of the individual
* cells on the left
*
* @param border The new border
* @param region The region that should have the border
* @param workbook The workbook that the region is on.
* @param sheet The sheet that the region is on.
*/
public static void SetBorderLeft(int border, CellRangeAddress region, ISheet sheet,
IWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, CellUtil.BORDER_LEFT, border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(CellUtil.GetRow(i, sheet), column);
}
}
//[Obsolete]
//public static void SetBorderLeft(Zephyr.Utils.NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderLeft(border, toCRA(region), sheet, workbook);
//}
/// <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(Zephyr.Utils.NPOI.SS.UserModel.BorderStyle 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, (int)border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
//[Obsolete]
//public static void SetRightBorderColor(short color, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetRightBorderColor(color, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the rightBorderColor 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 workbook that the region is on.</param>
/// <param name="workbook">The sheet that the region is on.</param>
public static void SetRightBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.LastColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.RIGHT_BORDER_COLOR, color);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
/**
* @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 void ValidateArrayFormulas(CellRangeAddress region)
{
int firstRow = region.FirstRow;
int firstColumn = region.FirstColumn;
int lastRow = region.LastRow;
int lastColumn = region.LastColumn;
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
{
for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
{
HSSFRow row = (HSSFRow)GetRow(rowIn);
if (row == null) continue;
HSSFCell cell = (HSSFCell)row.GetCell(colIn);
if (cell == null) continue;
if (cell.IsPartOfArrayFormulaGroup)
{
CellRangeAddress arrayRange = cell.GetArrayFormulaRange();
if (arrayRange.NumberOfCells > 1 &&
(arrayRange.IsInRange(region.FirstRow, region.FirstColumn) ||
arrayRange.IsInRange(region.FirstRow, region.FirstColumn)))
{
String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " +
"You cannot merge cells of an array.";
throw new InvalidOperationException(msg);
}
}
}
}
}
/// <summary>
/// Checks if the provided region is part of the merged regions.
/// </summary>
/// <param name="mergedRegion">Region searched in the merged regions</param>
/// <returns><c>true</c>, when the region is contained in at least one of the merged regions</returns>
public bool IsMergedRegion(CellRangeAddress mergedRegion)
{
foreach (CellRangeAddress range in _sheet.MergedRecords.MergedRegions)
{
if (range.FirstColumn <= mergedRegion.FirstColumn
&& range.LastColumn >= mergedRegion.LastColumn
&& range.FirstRow <= mergedRegion.FirstRow
&& range.LastRow >= mergedRegion.LastRow)
{
return true;
}
}
return false;
}
/// <summary>
/// Also creates cells if they don't exist.
/// </summary>
private ICellRange<ICell> GetCellRange(CellRangeAddress range)
{
int firstRow = range.FirstRow;
int firstColumn = range.FirstColumn;
int lastRow = range.LastRow;
int lastColumn = range.LastColumn;
int height = lastRow - firstRow + 1;
int width = lastColumn - firstColumn + 1;
List<ICell> temp = new List<ICell>(height * width);
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
{
for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
{
IRow row = GetRow(rowIn);
if (row == null)
{
row = CreateRow(rowIn);
}
ICell cell = row.GetCell(colIn);
if (cell == null)
{
cell = row.CreateCell(colIn);
}
temp.Add(cell);
}
}
return SSCellRange<ICell>.Create(firstRow, firstColumn, height, width, temp, typeof(HSSFCell));
}
/// <summary>
/// Sets the enclosed border of region.
/// </summary>
/// <param name="region">The region.</param>
/// <param name="borderType">Type of the border.</param>
/// <param name="color">The color.</param>
public void SetEnclosedBorderOfRegion(CellRangeAddress region, Zephyr.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color)
{
HSSFRegionUtil.SetRightBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderRight(borderType, region, this, _workbook);
HSSFRegionUtil.SetLeftBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderLeft(borderType, region, this, _workbook);
HSSFRegionUtil.SetTopBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderTop(borderType, region, this, _workbook);
HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook);
}
public CellWalk(ISheet sheet, CellRangeAddress range)
{
this.sheet = sheet;
this.range = range;
this.traverseEmptyCells = false;
}
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;
}
/**
* 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;
}
private static Region ConvertToRegion(CellRangeAddress cr)
{
return new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn);
}
public void MergeCell(int x1,int y1,int x2,int y2)
{
CellRangeAddress range = new CellRangeAddress(y1, y2, x1, x2);
sheet.AddMergedRegion(range);
}
public CFRecordsAggregate(CellRangeAddress[] regions, CFRuleRecord[] rules)
: this(new CFHeaderRecord(regions, rules.Length), rules)
{
}
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 + ")");
}
//[Obsolete]
//public static void SetBorderTop(Zephyr.Utils.NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderTop(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the borderBottom attribute of the HSSFRegionUtil object
/// </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 SetBorderTop(Zephyr.Utils.NPOI.SS.UserModel.BorderStyle border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.FirstRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_TOP, (int)border);
Zephyr.Utils.NPOI.SS.UserModel.IRow row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
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);
}
public MergeCellsRecord(CellRangeAddress[] regions, int startIndex, int numberOfRegions)
{
_regions = regions;
_startIndex = startIndex;
_numberOfRegions = numberOfRegions;
}
/// <summary>
/// Sets the bottom border of region.
/// </summary>
/// <param name="region">The region.</param>
/// <param name="borderType">Type of the border.</param>
/// <param name="color">The color.</param>
public void SetBorderBottomOfRegion(CellRangeAddress region, Zephyr.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color)
{
HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="sheet">the sheet where data located.</param>
/// <param name="range">the range within that sheet.</param>
public DataMarker(ISheet sheet, CellRangeAddress range)
{
this.sheet = sheet;
this.range = range;
}
/// <summary>
/// Sets array formula to specified region for result.
/// </summary>
/// <param name="formula">text representation of the formula</param>
/// <param name="range">Region of array formula for result</param>
/// <returns>the <see cref="ICellRange{ICell}"/> of cells affected by this change</returns>
public ICellRange<ICell> SetArrayFormula(String formula, CellRangeAddress range)
{
// make sure the formula parses OK first
int sheetIndex = _workbook.GetSheetIndex(this);
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, _workbook, FormulaType.ARRAY, sheetIndex);
ICellRange<ICell> cells = GetCellRange(range);
foreach (HSSFCell c in cells)
{
c.SetCellArrayFormula(range);
}
HSSFCell mainArrayFormulaCell = (HSSFCell)cells.TopLeftCell;
FormulaRecordAggregate agg = (FormulaRecordAggregate)mainArrayFormulaCell.CellValueRecord;
agg.SetArrayFormula(range, ptgs);
return cells;
}
/**
* 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);
}
/// <summary>
/// Enable filtering for a range of cells
/// </summary>
/// <param name="range">the range of cells to filter</param>
public IAutoFilter SetAutoFilter(CellRangeAddress range)
{
InternalWorkbook workbook = _workbook.Workbook;
int sheetIndex = _workbook.GetSheetIndex(this);
NameRecord name = workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
if (name == null)
{
name = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
}
// The built-in name must consist of a single Area3d Ptg.
Area3DPtg ptg = new Area3DPtg(range.FirstRow, range.LastRow,
range.FirstColumn, range.LastColumn,
false, false, false, false, sheetIndex);
name.NameDefinition = (new Ptg[] { ptg });
AutoFilterInfoRecord r = new AutoFilterInfoRecord();
// the number of columns that have AutoFilter enabled.
int numcols = 1 + range.LastColumn - range.FirstColumn;
r.NumEntries = (short)numcols;
int idx = _sheet.FindFirstRecordLocBySid(DimensionsRecord.sid);
_sheet.Records.Insert(idx, r);
//create a combobox control for each column
HSSFPatriarch p = (HSSFPatriarch)CreateDrawingPatriarch();
for (int col = range.FirstColumn; col <= range.LastColumn; col++)
{
p.CreateComboBox(new HSSFClientAnchor(0, 0, 0, 0,
(short)col, range.FirstRow, (short)(col + 1), range.FirstRow + 1));
}
return new HSSFAutoFilter(this);
}
public void AddCellRangeAddress(CellRangeAddress cra)
{
_list.Add(cra);
}
/**
* Sets the topBorderColor attribute of the RegionUtil object
*
* @param color The color of the border
* @param region The region that should have the border
* @param workbook The workbook that the region is on.
* @param sheet The sheet that the region is on.
*/
public static void SetTopBorderColor(int color, CellRangeAddress region, ISheet sheet,
IWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.FirstRow;
CellPropertySetter cps = new CellPropertySetter(workbook, CellUtil.TOP_BORDER_COLOR, color);
IRow row = CellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
public 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 SetArrayFormula(CellRangeAddress r, Ptg[] ptgs)
{
ArrayRecord arr = new ArrayRecord(Zephyr.Utils.NPOI.SS.Formula.Formula.Create(ptgs), new CellRangeAddress8Bit(r.FirstRow, r.LastRow, r.FirstColumn, r.LastColumn));
_sharedValueManager.AddArrayRecord(arr);
}
private static Region ConvertToRegion(CellRangeAddress cr)
{
return(new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn));
}
internal void SetCellArrayFormula(CellRangeAddress range)
{
int row = record.Row;
int col = record.Column;
short styleIndex = record.XFIndex;
SetCellType(CellType.FORMULA, false, row, col, styleIndex);
// Billet for formula in rec
Ptg[] ptgsForCell = { new ExpPtg(range.FirstRow, range.FirstColumn) };
FormulaRecordAggregate agg = (FormulaRecordAggregate)record;
agg.SetParsedExpression(ptgsForCell);
}
/**
* @return the total size of for the specified number of ranges,
* including the initial 2 byte range count
*/
public static int GetEncodedSize(int numberOfRanges)
{
return(2 + CellRangeAddress.GetEncodedSize(numberOfRanges));
}
