/** * 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)); }