public static FormulaValue Sum(Cell cell, FormulaValue[] args) { double val = 0; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: if (cell == null || cell.Worksheet == null) { throw new FormulaTypeMismatchException(cell); } cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) => { double data; if (CellUtility.TryGetNumberData(inCell.InnerData, out data)) { val += data; } return(true); }); break; case FormulaValueType.Number: val += (double)arg.value; break; } } return(val); }
/// <summary> /// Clone this cell instance by copying its all of properties /// </summary> /// <returns>new cell instance copied from this cell</returns> public Cell Clone() { var cell = new Cell(this.Worksheet); CellUtility.CopyCell(cell, this); return(cell); }
private double GetCellsDifference(List <CellPosition> fromCells) { double diff = 1; if (fromCells.Count > 1) { for (var i = 0; i < fromCells.Count - 1; i++) { var cell1 = Cells[fromCells[i]]; var cell2 = Cells[fromCells[i + 1]]; if (cell1 == null || cell2 == null) { continue; } double value1, value2; if (CellUtility.TryGetNumberData(cell1, out value1) && CellUtility.TryGetNumberData(cell2, out value2)) { if (i == 0) { diff = value2 - value1; } else { diff = (diff + (value2 - value1)) / 2; } } } } return(diff); }
public static FormulaValue Sum(Cell cell, FormulaValue[] args) { double val = 0; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: ReferenceRange range = RangeFrom(cell, arg); Worksheet worksheet = WorksheetFrom(cell, range); worksheet.IterateCells(range.Position, (r, c, inCell) => { double data; if (CellUtility.TryGetNumberData(worksheet, inCell, out data)) { val += data; } return(true); }); break; case FormulaValueType.Number: val += (double)arg.value; break; } } return(val); }
public static FormulaValue Average(Cell cell, FormulaValue[] args) { double val = 0, count = 0; double data; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: ReferenceRange range = RangeFrom(cell, arg); Worksheet worksheet = WorksheetFrom(cell, range); worksheet.IterateCells(range.Position, (r, c, inCell) => { if (CellUtility.TryGetNumberData(worksheet, inCell, out data)) { val += data; count++; } return(true); }); break; case FormulaValueType.Number: val += (double)arg.value; count++; break; case FormulaValueType.Nil: continue; } } return(count > 0 ? (val / count) : 0); }
// 統計を行う private void btnCalcTotal_Click(object sender, System.EventArgs e) { var sheet = grid.CurrentWorksheet; var range = new RangePosition("B2:E4"); double sumVal = 0, countVal = 0; sheet.IterateCells(range, (r, c, inCell) => { double data; // 数値を取得 if (CellUtility.TryGetNumberData(inCell, out data)) { sumVal += data; countVal++; } // trueを返して反復を続ける return(true); }); // 計算結果を表示 sheet["F6:G6"] = new object[] { "合計(SUM):", sumVal }; sheet["F7:G7"] = new object[] { "合計(COUNT):", countVal }; }
public static FormulaValue Min(Cell cell, FormulaValue[] args) { double min = 0; double data; bool first = true; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: if (cell == null || cell.Worksheet == null) { throw new FormulaTypeMismatchException(cell); } cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) => { if (CellUtility.TryGetNumberData(inCell.InnerData, out data)) { if (first) { min = data; first = false; } else if (min > data) { min = data; } } return(true); }); break; case FormulaValueType.Number: data = (double)arg.value; if (first) { min = data; first = false; } else if (min > data) { min = data; } break; default: return(null); } } return(min); }
/// <summary> /// Try get number data from cell at specified position. If the data is string, /// this method will try to convert the string into number value. /// </summary> /// <param name="row">Number of row of the cell to get data.</param> /// <param name="col">Number of column of the cell to get data.</param> /// <param name="val">Number data returned and converted from cell.</param> /// <returns>True if data can be get and converted; Otherwise return false.</returns> public bool TryGetNumberData(int row, int col, out double val) { var cell = this.cells[row, col]; if (cell == null) { val = 0; return(false); } return(CellUtility.TryGetNumberData(cell.Data, out val)); }
public static FormulaValue Min(Cell cell, FormulaValue[] args) { double min = 0; double data; bool first = true; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: ReferenceRange range = RangeFrom(cell, arg); Worksheet worksheet = WorksheetFrom(cell, range); worksheet.IterateCells(range.Position, (r, c, inCell) => { if (CellUtility.TryGetNumberData(worksheet, inCell, out data)) { if (first) { min = data; first = false; } else if (min > data) { min = data; } } return(true); }); break; case FormulaValueType.Number: data = (double)arg.value; if (first) { min = data; first = false; } else if (min > data) { min = data; } break; default: return(null); } } return(min); }
public static FormulaValue Count(Cell cell, FormulaValue[] args, bool includeEmpty = false) { double count = 0; double data; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: if (cell == null || cell.Worksheet == null) { return(null); } cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) => { if (includeEmpty) { if (inCell.InnerData != null) { count++; } } else { if (CellUtility.TryGetNumberData(inCell.InnerData, out data)) { count++; } } return(true); }); break; case FormulaValueType.Number: count++; break; case FormulaValueType.String: if (includeEmpty && !string.IsNullOrEmpty((string)arg.value)) { count++; } break; } } return(count); }
public static IAutoFillSectionEntry Create(object value) { if (value == null) { return new NullAutoFillSectionEntry(); } if (CellUtility.TryGetNumberData(value, out var number)) { return new NumericalAutoFillSectionEntry(number); } return new TextAutoFillSectionEntry(value.ToString()); }
public static FormulaValue Count(Cell cell, FormulaValue[] args, bool includeEmpty = false) { double count = 0; double data; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: ReferenceRange range = RangeFrom(cell, arg); Worksheet worksheet = WorksheetFrom(cell, range); worksheet.IterateCells(range.Position, (r, c, inCell) => { if (includeEmpty) { if (inCell.InnerData != null) { count++; } } else { if (CellUtility.TryGetNumberData(worksheet, inCell, out data)) { count++; } } return(true); }); break; case FormulaValueType.Number: count++; break; case FormulaValueType.String: if (includeEmpty && !string.IsNullOrEmpty((string)arg.value)) { count++; } break; } } return(count); }
public static void AssertApproximatelySame(object value, object expect, string msg = null) { if (CellUtility.IsNumberData(value) && CellUtility.IsNumberData(expect)) { AssertTrue(Math.Abs((double)Convert.ChangeType(value, typeof(double)) - (double)Convert.ChangeType(expect, typeof(double))) < 0.00001, msg); } //else if (value is System.Drawing.Color && expect is System.Drawing.Color) //{ // AssertEquals(((System.Drawing.Color)value).ToArgb(), ((System.Drawing.Color)expect).ToArgb(), msg); //} else if (double.TryParse(Convert.ToString(value), out double v1) && double.TryParse(Convert.ToString(expect), out double v2)) { AssertTrue(Math.Abs(v1 - v2) < 0.00001, msg); } else { AssertEquals(value, expect, msg); } }
public static FormulaValue Average(Cell cell, FormulaValue[] args) { double val = 0, count = 0; double data; foreach (var arg in args) { switch (arg.type) { case FormulaValueType.Range: if (cell == null || cell.Worksheet == null) { return(null); } cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) => { if (CellUtility.TryGetNumberData(inCell.InnerData, out data)) { val += data; count++; } return(true); }); break; case FormulaValueType.Number: val += (double)arg.value; count++; break; case FormulaValueType.Nil: continue; } } return(count > 0 ? (val / count) : 0); }
private void AutoFillSerialCells(List <CellPosition> fromCells, List <CellPosition> toCells) { if (!fromCells.Any() || !toCells.Any()) { return; } double diff = GetCellsDifference(fromCells); for (var toCellIndex = 0; toCellIndex < toCells.Count; toCellIndex++) { var fromCellIndex = toCellIndex % fromCells.Count; var fromCellPosition = fromCells[fromCellIndex]; var fromCell = Cells[fromCellPosition]; var toCellPosition = toCells[toCellIndex]; var toCell = Cells[toCellPosition]; if (fromCell == null) { continue; } if (!string.IsNullOrEmpty(fromCell.InnerFormula)) { FormulaRefactor.Reuse(this, fromCellPosition, new RangePosition(toCellPosition)); } else { double refValue = 0; if (CellUtility.TryGetNumberData(fromCell.Data, out refValue)) { toCell.Data = refValue + diff * (fromCells.Count + toCellIndex - fromCellIndex); } } } }
/// <summary> /// Get data in specified type from a cell /// </summary> /// <typeparam name="T">type of data will be converted into</typeparam> /// <param name="row">number of row to locate a cell</param> /// <param name="col">number of column to locate a cell</param> /// <returns></returns> public T GetCellData <T>(int row, int col) { return(CellUtility.ConvertData <T>(this.GetCellData(row, col))); }
/// <summary> /// Get data from specified cell /// </summary> /// <param name="pos">position to locate the cell</param> /// <returns>data of cell</returns> public T GetCellData <T>(CellPosition pos) { return(CellUtility.ConvertData <T>(this.GetCellData(pos))); }
public void OnImportsSatisfied() { if (FormulaExtension.CustomFunctions.ContainsKey("qEuler")) { return; } FormulaExtension.CustomFunctions["qEuler"] = (cell, args) => { if (args.Length < 2) { return(null); } var eulerangles = new float[3]; if (args[1] is RangePosition range) { var i = 0; cell.Worksheet.IterateCells(range, (r, c, icell) => { if (i >= 3) { return(false); } var isval = CellUtility.TryGetNumberData(icell, out var comp); eulerangles[i] = isval ? (float)(comp * Math.PI * 2) : 0; i++; return(true); }); } else { if (args.Length < 4) { return(null); } for (int i = 0; i < 3; i++) { eulerangles[i] = (float)(double)args[i + 1]; } } var res = Quaternion.CreateFromYawPitchRoll(eulerangles[1], eulerangles[0], eulerangles[2]); return(GetQuatComponent(args, res)); }; FormulaExtension.CustomFunctions["qEulerOrdered"] = (cell, args) => { if (args.Length < 5) { return(null); } var eulerangles = new float[3]; if (args[4] is RangePosition range) { var i = 0; cell.Worksheet.IterateCells(range, (r, c, icell) => { if (i >= 3) { return(false); } var isval = CellUtility.TryGetNumberData(icell, out var comp); eulerangles[i] = isval ? (float)(comp * Math.PI * 2) : 0; i++; return(true); }); } else { if (args.Length < 7) { return(null); } for (int i = 0; i < 3; i++) { eulerangles[i] = (float)(double)args[i + 4]; } } var res = Quaternion.Identity; for (int i = 0; i < 3; i++) { var selcomp = (int)(double)args[i + 1] % 3; switch (selcomp) { case 0: res = res * Quaternion.CreateFromYawPitchRoll(0, eulerangles[i], 0); break; case 1: res = res * Quaternion.CreateFromYawPitchRoll(eulerangles[i], 0, 0); break; case 2: res = res * Quaternion.CreateFromYawPitchRoll(0, 0, eulerangles[i]); break; } } return(GetQuatComponent(args, res)); }; }
/// <summary> /// Format cell /// </summary> /// <param name="cell">cell to be formatted</param> /// <returns>Formatted text used to display as cell content</returns> public string FormatCell(Cell cell) { object data = cell.InnerData; bool isFormat = false; double number; DateTime value = baseStartDate; string formattedText = null; if (data is DateTime) { value = (DateTime)data; isFormat = true; } else if (CellUtility.TryGetNumberData(data, out number)) { try { var val = (double)number; // Excel/Lotus 2/29/1900 bug // original post: http://stackoverflow.com/questions/727466/how-do-i-convert-an-excel-serial-date-number-to-a-net-datetime if (val > 59) { val -= 1; } value = BaseStartDate.AddDays(val - 1); isFormat = true; } catch { } } else { string strdata = (data is string?(string)data : Convert.ToString(data)); double days = 0; if (double.TryParse(strdata, out days)) { try { value = value.AddDays(days); isFormat = true; } catch { } } else { isFormat = (DateTime.TryParse(strdata, out value)); } } if (isFormat) { if (cell.InnerStyle.HAlign == ReoGridHorAlign.General) { cell.RenderHorAlign = ReoGridRenderHorAlign.Right; } CultureInfo culture = null; string pattern = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern; if (cell.DataFormatArgs != null && cell.DataFormatArgs is DateTimeFormatArgs) { DateTimeFormatArgs dargs = (DateTimeFormatArgs)cell.DataFormatArgs; pattern = dargs.Format; culture = (dargs.CultureName == null || string.Equals(dargs.CultureName, Thread.CurrentThread.CurrentCulture.Name)) ? Thread.CurrentThread.CurrentCulture : new CultureInfo(dargs.CultureName); } else { culture = System.Threading.Thread.CurrentThread.CurrentCulture; cell.DataFormatArgs = new DateTimeFormatArgs { Format = pattern, CultureName = culture.Name }; } if (culture.Name.StartsWith("ja") && pattern.Contains("g")) { culture = new CultureInfo("ja-JP", true); culture.DateTimeFormat.Calendar = new JapaneseCalendar(); } try { switch (pattern) { case "d": formattedText = value.Day.ToString(); break; default: formattedText = value.ToString(pattern, culture); break; } } catch { formattedText = Convert.ToString(value); } } return(isFormat ? formattedText : null); }
internal RangePosition SetPartialGrid(RangePosition toRange, PartialGrid data, PartialGridCopyFlag flag, ExPartialGridCopyFlag exFlag) { if (toRange.IsEmpty) { return(toRange); } toRange = FixRange(toRange); int rows = data.Rows; int cols = data.Columns; if (rows + toRange.Row > this.rows.Count) { rows = this.rows.Count - toRange.Row; } if (cols + toRange.Col > this.cols.Count) { cols = this.cols.Count - toRange.Col; } if (((flag & PartialGridCopyFlag.CellData) == PartialGridCopyFlag.CellData || (flag & PartialGridCopyFlag.CellStyle) == PartialGridCopyFlag.CellStyle)) { for (int r = 0; r < rows; r++) { for (int c = 0; c < cols; c++) { Cell fromCell = data.Cells == null ? null : data.Cells[r, c]; int tr = toRange.Row + r; int tc = toRange.Col + c; bool processed = false; if (fromCell != null) { #region Merge from right side // from cell copied as part of merged cell if ( // is part of merged cell !fromCell.MergeStartPos.IsEmpty && fromCell.MergeStartPos.Col < toRange.Col // is right side -------+-- (undo from delete column at end of merged range) && (fromCell.InternalCol - fromCell.MergeStartPos.Col > tc - toRange.Col // not inside --+----+-- && fromCell.MergeEndPos.Col <= toRange.EndCol)) { // from cell inside existed merged range // these two ranges should be merged // the original range must be expanded Cell fromMergedStart = CreateAndGetCell(fromCell.MergeStartPos); fromMergedStart.MergeEndPos = new CellPosition(fromMergedStart.MergeEndPos.Row, tc); fromMergedStart.Colspan = (short)(tc - fromMergedStart.InternalCol + 1); for (int ic = fromMergedStart.InternalCol; ic < fromMergedStart.InternalCol + fromMergedStart.Colspan; ic++) { var insideCell = cells[tr, ic]; if (insideCell != null) { insideCell.MergeEndPos = new CellPosition(insideCell.MergeEndPos.Row, tc); } } Cell tocell = CreateAndGetCell(tr, tc); tocell.MergeStartPos = fromMergedStart.InternalPos; tocell.MergeEndPos = new CellPosition(fromMergedStart.MergeEndPos.Row, tc); tocell.Colspan = 0; tocell.Rowspan = 0; if (tocell.IsEndMergedCell) { fromMergedStart.Bounds = GetRangePhysicsBounds(new RangePosition( fromMergedStart.InternalPos, fromMergedStart.MergeEndPos)); } processed = true; } #endregion #region Merge from left side // usually used when undo action: when deleting column from left side of merged cell else if ( !fromCell.MergeEndPos.IsEmpty // added 3/15/2016: check two unrelated ranges && toRange.ContainsRow(fromCell.Row) && fromCell.MergeEndPos.Col > toRange.EndCol && fromCell.MergeStartPos.Col <= toRange.EndCol ) { // target partial range will override exsited range // need to update existed range at right side int rightCol = Math.Min(fromCell.MergeEndPos.Col, this.cols.Count - 1); Cell tocell = CreateAndGetCell(tr, tc); tocell.MergeStartPos = new CellPosition(fromCell.MergeStartPos.Row, fromCell.MergeStartPos.Col + tc - fromCell.InternalCol); tocell.MergeEndPos = new CellPosition(fromCell.MergeEndPos.Row, rightCol); for (int ic = toRange.EndCol + 1; ic <= rightCol; ic++) { var existedEndCell = CreateAndGetCell(tr, ic); existedEndCell.MergeStartPos = tocell.MergeStartPos; existedEndCell.Rowspan = 0; existedEndCell.Colspan = 0; } if (tocell.IsStartMergedCell) { tocell.Rowspan = (short)(tocell.MergeEndPos.Row - tocell.MergeStartPos.Row + 1); tocell.Colspan = (short)(tocell.MergeEndPos.Col - tocell.MergeStartPos.Col + 1); tocell.Bounds = GetRangeBounds(tocell.InternalPos, tocell.MergeEndPos); // copy cell content CellUtility.CopyCellContent(tocell, fromCell); UpdateCellFont(tocell); } else { tocell.Rowspan = 0; tocell.Colspan = 0; } processed = true; } #endregion // Merge from left side #region Merge from bottom else if ( !fromCell.MergeStartPos.IsEmpty // above && fromCell.MergeStartPos.Row < toRange.Row // merged start row in the above of target fill range && fromCell.InternalRow - fromCell.MergeStartPos.Row > tr - toRange.Row // not inside current merged range && fromCell.MergeEndPos.Row <= toRange.EndRow) { var mergedStartCell = CreateAndGetCell(fromCell.MergeStartPos); mergedStartCell.Rowspan = (short)(tr - mergedStartCell.InternalRow + 1); for (int ir = fromCell.MergeStartPos.Row; ir < tr; ir++) { var existedCell = CreateAndGetCell(ir, tc); existedCell.MergeEndPos = new CellPosition(tr, fromCell.MergeEndPos.Col); } var tocell = CreateAndGetCell(tr, tc); tocell.MergeStartPos = mergedStartCell.InternalPos; tocell.MergeEndPos = new CellPosition(tr, fromCell.MergeEndPos.Col); tocell.Rowspan = 0; tocell.Colspan = 0; if (tocell.IsEndMergedCell) { mergedStartCell.Bounds = GetRangeBounds(mergedStartCell.InternalPos, mergedStartCell.MergeEndPos); } processed = true; } #endregion // Merge from bottom #region Merge from top // usually used when undo action: when deleting column from top side of merged cell else if ( !fromCell.MergeEndPos.IsEmpty // added 3/15/2016: check two unrelated ranges && toRange.ContainsColumn(fromCell.Column) && fromCell.MergeEndPos.Row > toRange.EndRow && fromCell.MergeStartPos.Row <= toRange.EndRow) { // target partial range will override exsited range // need to update existed range at right side int bottomRow = Math.Min(fromCell.MergeEndPos.Row, this.rows.Count - 1); for (int ir = toRange.EndRow + 1; ir <= bottomRow; ir++) { var existedEndCell = CreateAndGetCell(ir, tc); existedEndCell.MergeStartPos = new CellPosition(fromCell.MergeStartPos.Row, existedEndCell.MergeStartPos.Col); existedEndCell.Rowspan = 0; existedEndCell.Colspan = 0; } Cell tocell = CreateAndGetCell(tr, tc); tocell.MergeStartPos = fromCell.MergeStartPos; tocell.MergeEndPos = new CellPosition(bottomRow, fromCell.MergeEndPos.Col); if (tocell.IsStartMergedCell) { tocell.Rowspan = (short)(tocell.MergeEndPos.Row - tocell.MergeStartPos.Row + 1); tocell.Colspan = (short)(tocell.MergeEndPos.Col - tocell.MergeStartPos.Col + 1); tocell.Bounds = GetRangeBounds(tocell.InternalPos, tocell.MergeEndPos); // copy cell content CellUtility.CopyCellContent(tocell, fromCell); UpdateCellFont(tocell); } else { tocell.Rowspan = 0; tocell.Colspan = 0; } processed = true; } #endregion // Merge from top } if (!processed) { Cell toCell = CreateAndGetCell(tr, tc); if (toCell.Rowspan == 0 && toCell.Colspan == 0) { continue; } if (fromCell != null) { #region Copy Data if ((flag & PartialGridCopyFlag.CellData) == PartialGridCopyFlag.CellData) { CellUtility.CopyCellContent(toCell, fromCell); } #endregion // Copy Data #region Format Formula #if FORMULA if ((flag & PartialGridCopyFlag.CellFormula) == PartialGridCopyFlag.CellFormula) { if (fromCell.HasFormula) { if (fromCell.formulaTree == null) { try { fromCell.formulaTree = Formula.Parser.Parse(this.workbook, fromCell, fromCell.InnerFormula); } catch { fromCell.formulaStatus = FormulaStatus.SyntaxError; } } if (fromCell.formulaTree != null) { var rs = new ReplacableString(fromCell.InnerFormula); Stack <List <Cell> > dirtyCells = new Stack <List <Cell> >(); FormulaRefactor.CopyFormula(fromCell.Position, fromCell.formulaTree, toCell, rs, dirtyCells); } toCell.FontDirty = true; } } else { toCell.InnerFormula = null; } #endif // FORMULA #endregion // Formula Formula #region Copy Merged info // is single cell if (toCell.Rowspan == 1 && toCell.Colspan == 1) { // then copy span info toCell.Rowspan = fromCell.Rowspan; toCell.Colspan = fromCell.Colspan; if (!fromCell.MergeStartPos.IsEmpty) { toCell.MergeStartPos = fromCell.MergeStartPos.Offset(tr - fromCell.InternalRow, tc - fromCell.InternalCol); #if DEBUG Debug.Assert(toCell.MergeStartPos.Row >= 0 && toCell.MergeStartPos.Row < this.rows.Count); Debug.Assert(toCell.MergeStartPos.Col >= 0 && toCell.MergeStartPos.Col < this.cols.Count); #endif } if (!fromCell.MergeEndPos.IsEmpty) { toCell.MergeEndPos = fromCell.MergeEndPos.Offset(tr - fromCell.InternalRow, tc - fromCell.InternalCol); #if DEBUG Debug.Assert(toCell.MergeEndPos.Row >= 0 && toCell.MergeEndPos.Row < this.rows.Count); Debug.Assert(toCell.MergeEndPos.Col >= 0 && toCell.MergeEndPos.Col < this.cols.Count); #endif } } else { UpdateCellFont(toCell); } #endregion // Copy Merged info #region Cell Styles if (((flag & PartialGridCopyFlag.CellStyle) == PartialGridCopyFlag.CellStyle) && fromCell.InnerStyle != null) { if (fromCell.StyleParentKind == StyleParentKind.Own) { // from cell has own style, need copy the style toCell.InnerStyle = new WorksheetRangeStyle(fromCell.InnerStyle); } else { // from cell doesn't have own style, copy the reference of style toCell.InnerStyle = fromCell.InnerStyle; } // copy style parent flag toCell.StyleParentKind = fromCell.StyleParentKind; // TODO: render alignment is not contained in cell's style // copy the style may also need copy the render alignment // or we need to update the cell format again? if (fromCell.InnerStyle.HAlign == ReoGridHorAlign.General) { toCell.RenderHorAlign = fromCell.RenderHorAlign; } } #endregion // Cell Styles if (toCell.IsEndMergedCell) { Cell cell = GetCell(toCell.MergeStartPos); Debug.Assert(cell != null); UpdateCellBounds(cell); } else if (toCell.Rowspan == 1 && toCell.Colspan == 1) { UpdateCellFont(toCell); } } else { cells[tr, tc] = null; } } } } } // h-borders if ((flag & PartialGridCopyFlag.HBorder) == PartialGridCopyFlag.HBorder) { if (data.HBorders == null) { // cut left side border if (toRange.Col > 0) { for (int r = toRange.Row; r <= toRange.EndRow; r++) { this.CutBeforeHBorder(r, toRange.Col); } } // set borders to null this.hBorders.Iterate(toRange.Row, toRange.Col, rows, cols, true, (r, c, fromHBorder) => { this.hBorders[r, c] = null; return(1); } ); } else { // TODO: need to improve performance for (int r = 0; r < rows + 1; r++) { for (int c = 0; c < cols; c++) { int tr = toRange.Row + r; int tc = toRange.Col + c; this.CutBeforeHBorder(tr, tc); var fromHBorder = data.HBorders[r, c]; if (fromHBorder == null) { hBorders[tr, tc] = null; } else { RangeBorderStyle style = fromHBorder.Style; int hcols = fromHBorder.Span; if (hcols > cols - c) { hcols = cols - c; } this.GetHBorder(tr, tc).Span = hcols; if (data.HBorders[r, c].Style != null) { // in last col //if (c == cols - 1) // SetHBorders(tr, tc, hcols, style, fromHBorder.Pos); //else // hBorders[tr, tc].Border = style; SetHBorders(tr, tc, hcols, style, fromHBorder.Pos); } else { hBorders[tr, tc].Style = RangeBorderStyle.Empty; } } } } } } // v-borders if ((flag & PartialGridCopyFlag.VBorder) == PartialGridCopyFlag.VBorder) { if (data.VBorders == null) { // cut top side border if (toRange.Row > 0) { for (int c = toRange.Col; c <= toRange.EndCol; c++) { CutBeforeVBorder(toRange.Row, c); } } // set border to null this.vBorders.Iterate(toRange.Row, toRange.Col, rows, cols, true, (r, c, fromVBorder) => { this.vBorders[r, c] = null; return(1); } ); } else { // TODO: need to improve performance for (int r = 0; r < rows; r++) { for (int c = 0; c < cols + 1; c++) { int tr = toRange.Row + r; int tc = toRange.Col + c; this.CutBeforeVBorder(tr, tc); var fromVBorder = data.VBorders[r, c]; if (fromVBorder == null) { vBorders[tr, tc] = null; } else { RangeBorderStyle style = fromVBorder.Style; int vrows = fromVBorder.Span; if (vrows > rows - r) { vrows = rows - r; } GetVBorder(tr, tc).Span = vrows; if (data.VBorders[r, c].Style != null) { // is last row //if (r == rows - 1) // SetVBorders(tr, tc, vrows, style, fromVBorder.Pos); //else // vBorders[tr, tc].Border = fromVBorder.Border; this.SetVBorders(tr, tc, vrows, style, fromVBorder.Pos); } else { this.vBorders[tr, tc].Style = RangeBorderStyle.Empty; } } } } } } return(new RangePosition(toRange.Row, toRange.Col, rows, cols)); }
internal PartialGrid GetPartialGrid(RangePosition range, PartialGridCopyFlag flag, ExPartialGridCopyFlag exFlag, bool checkIntersectedRange = false) { range = FixRange(range); if (checkIntersectedRange) { var intersectedRange = CheckIntersectedMergingRange(range); if (intersectedRange != RangePosition.Empty) { throw new RangeIntersectionException(intersectedRange); } } int rows = range.Rows; int cols = range.Cols; PartialGrid data = new PartialGrid() { Columns = cols, Rows = rows, }; if ((flag & PartialGridCopyFlag.CellData) == PartialGridCopyFlag.CellData || (flag & PartialGridCopyFlag.CellStyle) == PartialGridCopyFlag.CellStyle) { data.Cells = new CellArray(); for (int r = range.Row; r <= range.EndRow; r++) { for (int c = range.Col; c <= range.EndCol; c++) { var cell = this.cells[r, c]; int toRow = r - range.Row; int toCol = c - range.Col; //if (cell == null && data.Cells[toRow, toCol] == null) //{ // c++; // continue; //} Cell toCell = null; if (cell != null) { toCell = new Cell(this); CellUtility.CopyCell(toCell, cell); } else { StyleParentKind pKind = StyleParentKind.Own; var style = StyleUtility.FindCellParentStyle(this, r, c, out pKind); style = StyleUtility.DistinctStyle(style, Worksheet.DefaultStyle); if (style != null) { toCell = new Cell(this); toCell.Colspan = 1; toCell.Rowspan = 1; toCell.InnerStyle = style; toCell.StyleParentKind = StyleParentKind.Own; } } if (toCell != null) { data.Cells[toRow, toCol] = toCell; } //c += (cell == null || cell.Colspan < 1) ? 1 : cell.Colspan; } } } if ((flag & PartialGridCopyFlag.HBorder) == PartialGridCopyFlag.HBorder) { data.HBorders = new HBorderArray(); hBorders.Iterate(range.Row, range.Col, rows + 1, cols, true, (r, c, hBorder) => { // only copy borders they belong to the cell (unless BorderOutsideOwner is specified) if (((exFlag & ExPartialGridCopyFlag.BorderOutsideOwner) == ExPartialGridCopyFlag.BorderOutsideOwner) || (hBorder != null && hBorder.Pos == HBorderOwnerPosition.None) || ( (r != range.Row || (hBorder != null && (hBorder.Pos & HBorderOwnerPosition.Top) == HBorderOwnerPosition.Top)) && (r != range.EndRow + 1 || (hBorder != null && (hBorder.Pos & HBorderOwnerPosition.Bottom) == HBorderOwnerPosition.Bottom))) ) { int toCol = c - range.Col; ReoGridHBorder thBorder = ReoGridHBorder.Clone(hBorder); if (thBorder != null && thBorder.Span > cols - toCol) { thBorder.Span = cols - toCol; } data.HBorders[r - range.Row, toCol] = thBorder; } return(1); }); } if ((flag & PartialGridCopyFlag.VBorder) == PartialGridCopyFlag.VBorder) { data.VBorders = new VBorderArray(); vBorders.Iterate(range.Row, range.Col, rows, cols + 1, true, (r, c, vBorder) => { // only copy borders they belong to the cell (unless BorderOutsideOwner is specified) if (((exFlag & ExPartialGridCopyFlag.BorderOutsideOwner) == ExPartialGridCopyFlag.BorderOutsideOwner) || (vBorder != null && vBorder.Pos == VBorderOwnerPosition.None) || ( (c != range.Col || (vBorder != null && (vBorder.Pos & VBorderOwnerPosition.Left) == VBorderOwnerPosition.Left)) && (c != range.EndCol + 1 || (vBorder != null && (vBorder.Pos & VBorderOwnerPosition.Right) == VBorderOwnerPosition.Right))) ) { int toRow = r - range.Row; ReoGridVBorder tvBorder = ReoGridVBorder.Clone(vBorder); if (tvBorder != null && tvBorder.Span > rows - toRow) { tvBorder.Span = rows - toRow; } data.VBorders[toRow, c - range.Col] = tvBorder; } return(1); }); } return(data); }
public RSRangeObject(Worksheet sheet, RangePosition range) : base(sheet) { this.Range = range; this["style"] = new ExternalProperty( () => { if (Style == null) { Style = sheet.GetRangeStyles(this.Range); } return(Style); }, (v) => { sheet.SetRangeStyles(this.Range, RSUtility.GetRangeStyleObject(v)); } ); this["pos"] = new ExternalProperty( () => sheet.SelectionRange.StartPos, (v) => { range.StartPos = RSUtility.GetPosFromValue(sheet, v); sheet.SelectRange(range); }); this["range"] = new ExternalProperty( () => sheet.SelectionRange, (v) => { sheet.SelectRange(RSUtility.GetRangeFromValue(sheet, v)); }); this["row"] = new ExternalProperty(() => sheet.SelectionRange.Row, (v) => this.Range = new RangePosition(CellUtility.ConvertData <int>(v), this.Range.Col, this.Range.Rows, this.Range.Cols)); this["col"] = new ExternalProperty(() => sheet.SelectionRange.Col, (v) => this.Range = new RangePosition(this.Range.Row, CellUtility.ConvertData <int>(v), this.Range.Rows, this.Range.Cols)); this["rows"] = new ExternalProperty(() => sheet.SelectionRange.Row, (v) => this.Range = new RangePosition(this.Range.Row, this.Range.Col, CellUtility.ConvertData <int>(v), this.Range.Cols)); this["cols"] = new ExternalProperty(() => sheet.SelectionRange.Col, (v) => this.Range = new RangePosition(this.Range.Row, this.Range.Col, this.Range.Rows, CellUtility.ConvertData <int>(v))); this["toString"] = new NativeFunctionObject("toString", (ctx, owner, args) => this.Range.ToString()); this["toAddress"] = new NativeFunctionObject("toAddress", (ctx, owner, args) => this.Range.ToAddress()); this["toSpans"] = new NativeFunctionObject("toSpans", (ctx, owner, args) => this.Range.ToStringSpans()); this["merge"] = new NativeFunctionObject("merge", (ctx, owner, args) => { sheet.MergeRange(this.Range); return(null); }); this["unmerge"] = new NativeFunctionObject("unmerge", (ctx, owner, args) => { sheet.UnmergeRange(this.Range); return(null); }); }
public static FormulaValue VLookup(Cell cell, FormulaValue[] args) { FormulaValue argTarget = args[0]; FormulaValue argRange = args[1]; FormulaValue argReturnIndex = args[2]; FormulaValue argExactMatch = args.Length > 3 ? args[3] : FormulaValue.Nil; bool exactMatch = false; //int matchValueInt = 0; double matchValueDouble = 0; string matchValueText = null; bool matchNumber = true; if (argRange.type != FormulaValueType.Range || argReturnIndex.type != FormulaValueType.Number) { return(null); } if (argExactMatch.type != FormulaValueType.Nil) { if (argExactMatch.type != FormulaValueType.Boolean) { return(null); } exactMatch = (bool)argExactMatch.value; } var searchRange = (RangePosition)argRange.value; #region Match Value switch (argTarget.type) { case FormulaValueType.Number: matchValueDouble = (double)argTarget.value; break; case FormulaValueType.String: matchValueText = (string)argTarget.value; matchNumber = false; break; default: return(null); } #endregion // Match Value int selectColumnIndex = (int)Math.Ceiling((double)argReturnIndex.value); int matchedRowIndex = searchRange.EndRow; bool found = false; if (exactMatch) { for (int r = searchRange.Row; r <= searchRange.EndRow; r++) { var cel = cell.Worksheet.GetCell(r, searchRange.Col); if (cel != null) { if (matchNumber) { double cellNumVal = 0; if (CellUtility.TryGetNumberData(cel.Data, out cellNumVal)) { if (matchValueDouble == cellNumVal) { matchedRowIndex = r; found = true; break; } } } else { string cellText = cel.DisplayText; if (string.Compare(matchValueText, cellText, true) == 0) { matchedRowIndex = r; found = true; break; } } } } if (!found) { return(null); } } else { matchedRowIndex = Data.ArrayHelper.QuickFind(searchRange.Row, searchRange.EndRow, i => { var cel = cell.Worksheet.GetCell(i, searchRange.Col); if (cel == null) { return(0); } if (matchNumber) { double cellNumVal = 0; if (CellUtility.TryGetNumberData(cel.Data, out cellNumVal)) { if (matchValueDouble < cellNumVal) { return(-1); } else if (matchValueDouble > cellNumVal) { return(1); } } return(0); } else { string cellText = cel.DisplayText; return(string.Compare(matchValueText, cellText, true)); } }); } return(Evaluator.CreateFormulaValue(cell.Worksheet.GetCellData(matchedRowIndex, searchRange.Col + selectColumnIndex - 1))); }
/// <summary> /// Gets the cell IDs at the edges of the visibility area. /// </summary> /// <param name="cellularFloor">The cellularFloor.</param> /// <returns>HashSet<System.Int32>.</returns> public HashSet <int> GetIsovistEdge(CellularFloor cellularFloor) { return(CellUtility.GetEdgeOfField(cellularFloor, this.VisibleCells)); }
void _close_Click(object sender, RoutedEventArgs e) { HashSet <Index> allIndices = new HashSet <Index>(); foreach (SpatialAnalysis.Geometry.BarrierPolygon item in this._barriers) { allIndices.UnionWith(this._host.cellularFloor.GetIndicesInsideBarrier(item, 0.0000001)); } var visibleCells = new HashSet <int>(); foreach (Index item in allIndices) { if (this._host.cellularFloor.ContainsCell(item) && this._host.cellularFloor.Cells[item.I, item.J].VisualOverlapState == OverlapState.Outside) { visibleCells.Add(this._host.cellularFloor.Cells[item.I, item.J].ID); } } allIndices.Clear(); allIndices = null; foreach (Cell item in this._destinations) { if (item.VisualOverlapState == OverlapState.Outside) { visibleCells.Add(item.ID); } } if (visibleCells.Count == 0) { MessageBox.Show("Cannot Proceed without Setting Visibility Targets!"); return; } this._settings._panel1.Visibility = this._settings._panel2.Visibility = System.Windows.Visibility.Collapsed; this._settings._panel4.Visibility = System.Windows.Visibility.Visible; var cellsOnEdge = CellUtility.GetEdgeOfField(this._host.cellularFloor, visibleCells); List <Isovist> isovists = new List <Isovist>(cellsOnEdge.Count); this._settings.progressBar.Maximum = cellsOnEdge.Count; this._settings.progressBar.Minimum = 0; double depth = this._host.cellularFloor.Origin.DistanceTo(this._host.cellularFloor.TopRight) + 1; foreach (int item in cellsOnEdge) { isovists.Add(new Isovist(this._host.cellularFloor.FindCell(item))); } var timer = new System.Diagnostics.Stopwatch(); timer.Start(); Parallel.ForEach(isovists, (a) => { a.Compute(depth, BarrierType.Visual, this._host.cellularFloor, 0.0000001); Dispatcher.BeginInvoke(DispatcherPriority.Background, (SendOrPostCallback) delegate { double val = this._settings.progressBar.Value + 1; this._settings.progressBar.SetValue(ProgressBar.ValueProperty, val); } , null); Dispatcher.BeginInvoke(DispatcherPriority.Background, (SendOrPostCallback) delegate { double x = this._settings.progressBar.Value / this._settings.progressBar.Maximum; int percent = (int)(x * 100); int minutes = (int)Math.Floor(timer.Elapsed.TotalMinutes); int seconds = (int)(timer.Elapsed.Seconds); string message = string.Format("{0} minutes and {1} seconds\n%{2} completed", minutes.ToString(), seconds.ToString(), percent.ToString()); this._settings.IsovistPrgressReport.SetValue(TextBlock.TextProperty, message); } , null); Dispatcher.BeginInvoke(DispatcherPriority.ApplicationIdle, new Action(() => { })).Wait(TimeSpan.FromMilliseconds(1)); }); var t = timer.Elapsed.TotalMilliseconds; timer.Stop(); HashSet <int> visibleArea = new HashSet <int>(); foreach (Isovist item in isovists) { visibleArea.UnionWith(item.VisibleCells); } foreach (Cell item in this._destinations) { UV p1 = item; UV p2 = item + this._host.cellularFloor.CellSize * UV.UBase; UV p3 = item + this._host.cellularFloor.CellSize * (UV.UBase + UV.VBase); UV p4 = item + this._host.cellularFloor.CellSize * UV.VBase; var polygon = new SpatialAnalysis.Geometry.BarrierPolygon(new UV[4] { p1, p2, p3, p4 }); this._barriers.Add(polygon); } this._host.VisualEventSettings = new VisibilityTarget(visibleArea, isovists, this._barriers.ToArray()); #region visualization double _h = ((UIElement)this.Parent).RenderSize.Height; double _w = ((UIElement)this.Parent).RenderSize.Width; this._host.agentVisiblArea.Source = null; WriteableBitmap _view = BitmapFactory.New((int)_w, (int)_h); this._host.agentVisiblArea.Source = _view; this._host.agentVisiblArea.Visibility = System.Windows.Visibility.Visible; this._showVisibleArea.Header = "Hide Visible Area"; switch (this._settings._colorCode.IsChecked.Value) { case true: using (_view.GetBitmapContext()) { int max = int.MinValue; foreach (var item in this._host.VisualEventSettings.ReferencedVantageCells.Values) { if (max < item.Count) { max = item.Count; } } byte alpha = (byte)(255 * 0.4); Color yellow = Color.Add(Colors.Yellow, Color.Multiply(Colors.Red, 4.0f)); yellow.ScA = 0.7f; foreach (int cellID in visibleArea) { Cell item = this._host.cellularFloor.FindCell(cellID); Point p1 = this._host.Transform(item); Point p2 = this._host.Transform(item + new UV(this._host.cellularFloor.CellSize, this._host.cellularFloor.CellSize)); _view.FillRectangle((int)p1.X, (int)p1.Y, (int)p2.X, (int)p2.Y, GetColor(this._host.VisualEventSettings.ReferencedVantageCells[cellID].Count, max, alpha)); } foreach (int cellID in visibleCells) { Cell item = this._host.cellularFloor.FindCell(cellID); Point p1 = this._host.Transform(item); Point p2 = this._host.Transform(item + new UV(this._host.cellularFloor.CellSize, this._host.cellularFloor.CellSize)); _view.FillRectangle((int)p1.X, (int)p1.Y, (int)p2.X, (int)p2.Y, yellow); } } break; case false: using (_view.GetBitmapContext()) { Color green = Colors.GreenYellow; green.ScA = 0.4f; Color yellow = Color.Add(Colors.Yellow, Color.Multiply(Colors.Red, 4.0f)); yellow.ScA = 0.7f; foreach (int cellID in visibleArea) { Cell item = this._host.cellularFloor.FindCell(cellID); Point p1 = this._host.Transform(item); Point p2 = this._host.Transform(item + new UV(this._host.cellularFloor.CellSize, this._host.cellularFloor.CellSize)); _view.FillRectangle((int)p1.X, (int)p1.Y, (int)p2.X, (int)p2.Y, green); } foreach (int cellID in visibleCells) { Cell item = this._host.cellularFloor.FindCell(cellID); Point p1 = this._host.Transform(item); Point p2 = this._host.Transform(item + new UV(this._host.cellularFloor.CellSize, this._host.cellularFloor.CellSize)); _view.FillRectangle((int)p1.X, (int)p1.Y, (int)p2.X, (int)p2.Y, yellow); } } break; } #endregion //cleanup isovists.Clear(); isovists = null; this._settings.Close(); }
void _generateDataTermination_Click(object sender, RoutedEventArgs e) { #region Input validation if (string.IsNullOrEmpty(this._settings._dataName.Text)) { MessageBox.Show("Enter Data Field Name"); return; } if (this._host.cellularFloor.AllSpatialDataFields.ContainsKey(this._settings._dataName.Text)) { MessageBox.Show("The Data Field Name Exists. Try a Different Name..."); return; } double _in = 0, _out = 0, _on = 0; if (!double.TryParse(this._settings._valueOfVantageCells.Text, out _on)) { MessageBox.Show("Value of visibility vantage areas is invalid!"); return; } if (!double.TryParse(this._settings._valueOutside.Text, out _out)) { MessageBox.Show("Value of not visible areas is invalid!"); return; } if (!this._settings._interpolationMethod.IsChecked.Value && !double.TryParse(this._settings._constantValue.Text, out _in)) { MessageBox.Show("Value of visible areas is invalid!"); return; } if (this._settings._interpolationMethod.IsChecked.Value) { //test the interpolation function if (!this._settings.LoadFunction()) { return; } } #endregion #region get the vantage Cells HashSet <Index> allIndices = new HashSet <Index>(); foreach (SpatialAnalysis.Geometry.BarrierPolygon item in this._barriers) { allIndices.UnionWith(this._host.cellularFloor.GetIndicesInsideBarrier(item, OSMDocument.AbsoluteTolerance)); } var vantageCells = new HashSet <int>(); foreach (Index item in allIndices) { if (this._host.cellularFloor.ContainsCell(item) && this._host.cellularFloor.Cells[item.I, item.J].VisualOverlapState == OverlapState.Outside) { vantageCells.Add(this._host.cellularFloor.Cells[item.I, item.J].ID); } } allIndices.Clear(); allIndices = null; foreach (Cell item in this._destinations) { if (item.VisualOverlapState == OverlapState.Outside) { vantageCells.Add(item.ID); } } if (vantageCells.Count == 0) { MessageBox.Show("Cannot Proceed without Setting Visibility Targets!"); return; } #endregion this._settings._panel1.Visibility = this._settings._panel2.Visibility = System.Windows.Visibility.Collapsed; this._settings._panel4.Visibility = System.Windows.Visibility.Visible; var cellsOnEdge = CellUtility.GetEdgeOfField(this._host.cellularFloor, vantageCells); List <Isovist> isovists = new List <Isovist>(cellsOnEdge.Count); this._settings.progressBar.Maximum = cellsOnEdge.Count; this._settings.progressBar.Minimum = 0; double depth = this._host.cellularFloor.Origin.DistanceTo(this._host.cellularFloor.TopRight) + 1; foreach (int item in cellsOnEdge) { isovists.Add(new Isovist(this._host.cellularFloor.FindCell(item))); } var timer = new System.Diagnostics.Stopwatch(); timer.Start(); Parallel.ForEach(isovists, (a) => { a.Compute(depth, BarrierType.Visual, this._host.cellularFloor, 0.0000001); Dispatcher.BeginInvoke(DispatcherPriority.Background, (SendOrPostCallback) delegate { double val = this._settings.progressBar.Value + 1; this._settings.progressBar.SetValue(ProgressBar.ValueProperty, val); } , null); Dispatcher.BeginInvoke(DispatcherPriority.Background, (SendOrPostCallback) delegate { double x = this._settings.progressBar.Value / this._settings.progressBar.Maximum; int percent = (int)(x * 100); int minutes = (int)Math.Floor(timer.Elapsed.TotalMinutes); int seconds = (int)(timer.Elapsed.Seconds); string message = string.Format("{0} minutes and {1} seconds\n%{2} completed", minutes.ToString(), seconds.ToString(), percent.ToString()); this._settings.IsovistPrgressReport.SetValue(TextBlock.TextProperty, message); } , null); Dispatcher.BeginInvoke(DispatcherPriority.ApplicationIdle, new Action(() => { })).Wait(TimeSpan.FromMilliseconds(1)); }); var t = timer.Elapsed.TotalMilliseconds; timer.Stop(); HashSet <int> visibleArea = new HashSet <int>(); foreach (Isovist item in isovists) { visibleArea.UnionWith(item.VisibleCells); } if (this._settings._interpolationMethod.IsChecked.Value) { //HashSet<int> notVisibleCellIds = new HashSet<int>(); //foreach (var item in this._host.cellularFloor.Cells) //{ // if (item.FieldOverlapState != OverlapState.Outside) // { // if (!vantageCells.Contains(item.ID) && !visibleArea.Contains(item.ID)) // { // notVisibleCellIds.Add(item.ID); // } // } //} //var newData = this._host.FieldGenerator.GetSpatialDataField(vantageCells, _on, notVisibleCellIds, _out, this._settings._dataName.Text, this._settings.InterpolationFunction); //this._host.cellularFloor.AddSpatialDataField(newData); Dictionary <Cell, double> data = new Dictionary <Cell, double>(); foreach (var item in this._host.cellularFloor.Cells) { if (item.FieldOverlapState != OverlapState.Outside) { if (vantageCells.Contains(item.ID)) { data.Add(item, _on); } else { if (visibleArea.Contains(item.ID)) { UV visiblePoint = this._host.cellularFloor.FindCell(item.ID); double dist = double.PositiveInfinity; foreach (var id in cellsOnEdge) { var cell = this._host.cellularFloor.FindCell(id); dist = Math.Min(UV.GetLengthSquared(cell, visiblePoint), dist); } double value = _on + this._settings.InterpolationFunction(Math.Sqrt(dist)); data.Add(item, Math.Max(value, _out)); } else { data.Add(item, _out); } } } } Data.SpatialDataField newData = new Data.SpatialDataField(this._settings._dataName.Text, data); this._host.cellularFloor.AddSpatialDataField(newData); } else { Dictionary <Cell, double> data = new Dictionary <Cell, double>(); foreach (var item in this._host.cellularFloor.Cells) { if (item.FieldOverlapState != OverlapState.Outside) { if (vantageCells.Contains(item.ID)) { data.Add(item, _on); } else { if (visibleArea.Contains(item.ID)) { data.Add(item, _in); } else { data.Add(item, _out); } } } } Data.SpatialDataField newData = new Data.SpatialDataField(this._settings._dataName.Text, data); this._host.cellularFloor.AddSpatialDataField(newData); } this._settings.Close(); }
public RSSelectionObject(Worksheet sheet) : base(sheet) { this["moveUp"] = new NativeFunctionObject("moveUp", (srm, owner, args) => { sheet.MoveSelectionUp(); return(null); }); this["moveDown"] = new NativeFunctionObject("moveDown", (srm, owner, args) => { sheet.MoveSelectionDown(); return(null); }); this["moveLeft"] = new NativeFunctionObject("moveLeft", (srm, owner, args) => { sheet.MoveSelectionLeft(); return(null); }); this["moveRight"] = new NativeFunctionObject("moveRight", (srm, owner, args) => { sheet.MoveSelectionRight(); return(null); }); this["pos"] = new ExternalProperty( () => sheet.SelectionRange.StartPos, (v) => { RangePosition range = sheet.SelectionRange; range.StartPos = RSUtility.GetPosFromValue(sheet, v); sheet.SelectRange(range); }); this["range"] = new ExternalProperty( () => sheet.SelectionRange, (v) => { sheet.SelectRange(RSUtility.GetRangeFromValue(sheet, v)); }); this["row"] = new ExternalProperty(() => sheet.SelectionRange.Row, (v) => { RangePosition range = sheet.SelectionRange; range.Row = CellUtility.ConvertData <int>(v); sheet.SelectRange(range); }); this["col"] = new ExternalProperty(() => sheet.SelectionRange.Col, (v) => { RangePosition range = sheet.SelectionRange; range.Col = CellUtility.ConvertData <int>(v); sheet.SelectRange(range); }); this["toString"] = new NativeFunctionObject("toString", (ctx, owner, args) => sheet.SelectionRange.ToString()); this["toAddress"] = new NativeFunctionObject("toAddress", (ctx, owner, args) => sheet.SelectionRange.ToAddress()); this["toSpans"] = new NativeFunctionObject("toSpans", (ctx, owner, args) => sheet.SelectionRange.ToStringSpans()); this["merge"] = new NativeFunctionObject("merge", (ctx, owner, args) => { sheet.MergeRange(sheet.SelectionRange); return(null); }); this["unmerge"] = new NativeFunctionObject("unmerge", (ctx, owner, args) => { sheet.UnmergeRange(sheet.SelectionRange); return(null); }); }
public static FormulaValue SumIf(Cell cell, FormulaValue[] args) { if (cell == null || cell.Worksheet == null) { return(null); } if (args[1].type != FormulaValueType.String) { // todo: support not only string return(null); } double val = 0; double data; RangePosition evalRange, sumRange = RangePosition.Empty; if (args[0].type == FormulaValueType.Range) { evalRange = (RangePosition)args[0].value; } else { throw new FormulaTypeMismatchException(cell); } if (args.Length > 2) { if (args[2].type != FormulaValueType.Range) { throw new FormulaTypeMismatchException(cell); } sumRange = (RangePosition)(args[2].value); } string expStr = (string)args[1].value; STValueNode leftExp = new STValueNode(null); STNode compExp = Parser.ParseInterCompareExp(cell, expStr); int rows = cell.Worksheet.Rows; int cols = cell.Worksheet.Columns; cell.Worksheet.IterateCells(evalRange, (r, c, inCell) => { leftExp.Value = Evaluator.CreateFormulaValue(inCell); compExp[0] = leftExp; var result = (Evaluator.Evaluate(cell, compExp)); if (result.type == FormulaValueType.Boolean && ((bool)result.value)) { if (sumRange.IsEmpty) { if (CellUtility.TryGetNumberData(inCell.InnerData, out data)) { val += data; } } else { int tr = sumRange.Row + r - evalRange.Row; int tc = sumRange.Col + c - evalRange.Col; if (tr < rows && tc < cols) { var sumCell = cell.Worksheet.GetCell(tr, tc); if (sumCell != null && sumCell.InnerData != null && CellUtility.TryGetNumberData(sumCell.InnerData, out data)) { val += data; } } } } return(true); }); return(val); }
/// <summary> /// Get and convert data into specified type /// </summary> /// <typeparam name="T">Type try to convert</typeparam> /// <returns>Converted data in specified type</returns> public T GetData <T>() { return(CellUtility.ConvertData <T>(this.InnerData)); }
public static AdjacencyCluster AdjacencyCluster(IEnumerable <Space> spaces, IEnumerable <Panel> panels, out List <Topology> topologies, out List <Panel> redundantPanels, double minArea = Tolerance.MacroDistance, bool updatePanels = true, bool tryCellComplexByCells = true, Log log = null, double silverSpacing = Tolerance.MacroDistance, double tolerance = Tolerance.Distance) { Core.Modify.Add(log, "Method Name: {0}, Tolerance: {1}, Update Panels: {2}", "SAM.Analytical.Topologic.Create.AdjacencyCluster", tolerance, updatePanels); topologies = null; redundantPanels = null; AdjacencyCluster result = new AdjacencyCluster(); result.AddObjects(spaces); result.AddObjects(panels); List <Face> faces = new List <Face>(); int index = 1; foreach (Panel panel in result.GetObjects <Panel>()) { if (panel == null) { continue; } Face face = Convert.ToTopologic(panel); if (face == null) { continue; } faces.Add(face); Core.Modify.Add(log, "Face {0:D4} added. Panel [{1}]", index, panel.Guid); index++; } if (faces == null || faces.Count == 0) { return(null); } topologies = new List <Topology>(); List <Cell> cells = null; if (tryCellComplexByCells) { try { Core.Modify.Add(log, "Trying to make CellComplex By Cells"); Cluster cluster = Cluster.ByTopologies(faces as IList <Topology>); Core.Modify.Add(log, "Cluster.ByTopologies Done"); Topology topology = cluster.SelfMerge(); Core.Modify.Add(log, "Cluster SelfMerge Done"); if (topology.Cells != null && topology.Cells.Count != 0) { cells = topology.Cells?.ToList(); CellComplex cellComplex = null; try { cellComplex = CellComplex.ByCells(cells); } catch (Exception exception) { Core.Modify.Add(log, "Cells could not be taken from CellComplex"); Core.Modify.Add(log, "Exception Message: {0}", exception.Message); } if (cellComplex != null && cellComplex.Cells != null && cellComplex.Cells.Count != 0) { topologies.Add(cellComplex); cells = cellComplex.Cells?.ToList(); } else { topologies.Add(topology); Core.Modify.Add(log, "Cells taken from Cluster"); } } } catch (Exception exception) { Core.Modify.Add(log, "Cannot create CellComplex By Cells or Cells form Cluster SelfMerge"); Core.Modify.Add(log, "Exception Message: {0}", exception.Message); cells = null; } } if (cells == null) { try { Core.Modify.Add(log, "Trying to make CellComplex By Faces"); CellComplex cellComplex = CellComplex.ByFaces(faces, tolerance); topologies.Add(cellComplex); cells = cellComplex.Cells?.ToList(); Core.Modify.Add(log, "CellComplex By Faces Created"); } catch (Exception exception) { Core.Modify.Add(log, "Cannot create CellComplex By Faces"); Core.Modify.Add(log, "Exception Message: {0}", exception.Message); cells = null; } } if (cells == null || cells.Count == 0) { Core.Modify.Add(log, "No cells found"); return(null); } List <Geometry.Spatial.Shell> shells = cells.ToSAM(); Core.Modify.Add(log, "Single CellComplex converted to shells"); if (shells == null) { return(null); } //Matching spaces with shells Dictionary <Geometry.Spatial.Shell, List <Space> > dictionary_Spaces = new Dictionary <Geometry.Spatial.Shell, List <Space> >(); if (spaces != null) { foreach (Space space in spaces) { if (space == null || space.Location == null || !space.IsPlaced()) { continue; } List <Geometry.Spatial.Shell> shells_Temp = Analytical.Query.SpaceShells(shells, space.Location, silverSpacing, tolerance); if (shells_Temp == null || shells_Temp.Count == 0) { continue; } foreach (Geometry.Spatial.Shell shell in shells_Temp) { if (!dictionary_Spaces.TryGetValue(shell, out List <Space> spaces_Shell)) { spaces_Shell = new List <Space>(); dictionary_Spaces[shell] = spaces_Shell; } spaces_Shell.Add(space); } } } HashSet <Guid> guids_Updated = new HashSet <Guid>(); Dictionary <Panel, Face3D> dictionary_Panel_Face3D = new Dictionary <Panel, Face3D>(); result.GetObjects <Panel>().ForEach(x => dictionary_Panel_Face3D[x] = x.GetFace3D()); index = 1; List <Tuple <Panel, Point3D> > tuples_InternalPoint3D = new List <Tuple <Panel, Point3D> >(); for (int i = 0; i < shells.Count; i++) { Geometry.Spatial.Shell shell = shells[i]; if (shell == null) { return(null); } Core.Modify.Add(log, "Simplifying shell"); //shell.Simplify(tolerance); // Low tolerance cause of rounding issues shell.Simplify(); Core.Modify.Add(log, "Extracting faces from shell"); List <Face3D> face3Ds = shell?.Face3Ds; if (face3Ds == null) { Core.Modify.Add(log, "No face2Ds found in Shell"); continue; } dictionary_Spaces.TryGetValue(shell, out List <Space> spaces_Shell); if (spaces_Shell == null || spaces_Shell.Count == 0) { Core.Modify.Add(log, "Creating new Space"); Point3D location = shell.CalculatedInternalPoint3D(silverSpacing, tolerance); if (location == null) { continue; } Space space = new Space("Cell " + index, location); index++; if (!result.AddObject(space)) { continue; } spaces_Shell = new List <Space>() { space }; dictionary_Spaces[shell] = spaces_Shell; } if (spaces_Shell == null || spaces_Shell.Count == 0) { continue; } double volume = double.NaN; if (cells[i] != null) { Core.Modify.Add(log, "Calculating Volume"); volume = CellUtility.Volume(cells[i]); foreach (Space space_Shell in spaces_Shell) { space_Shell.SetValue(SpaceParameter.Volume, volume); } } Core.Modify.Add(log, "Upadting Panels"); foreach (Face3D face3D in face3Ds) { if (minArea != 0 && face3D.GetArea() <= minArea) { Core.Modify.Add(log, "Face3D is too small"); continue; } Core.Modify.Add(log, "Looking for existing Panel"); Tuple <Panel, Point3D> tuple_InternalPoint3D = tuples_InternalPoint3D.Find(x => face3D.Inside(x.Item2, tolerance)); if (tuple_InternalPoint3D != null) { Core.Modify.Add(log, "Existing Panel found: {0}", tuple_InternalPoint3D.Item1.Guid); foreach (Space space in spaces_Shell) { if (result.AddRelation(space, tuple_InternalPoint3D.Item1)) { Core.Modify.Add(log, "Space [{0}] and Panel [{1}] relation added", space.Guid, tuple_InternalPoint3D.Item1.Guid); } } continue; } Core.Modify.Add(log, "Looking for old Panel"); //Panel panel_Old = Query.SimilarPanel(face3D, dictionary_Panel_Face3D); //if (panel_Old == null) // continue; List <Panel> panels_Old = Query.SimilarPanels(face3D, dictionary_Panel_Face3D); if (panels_Old == null || panels_Old.Count == 0) { continue; } Panel panel_Old = panels_Old.First(); if (panels_Old.Count > 1) { if (redundantPanels == null) { redundantPanels = new List <Panel>(); } panels_Old.RemoveAt(0); redundantPanels.AddRange(panels_Old); } Core.Modify.Add(log, "Old Panel found: {0}", panel_Old.Guid); Panel panel_New = null; if (updatePanels) { if (guids_Updated.Contains(panel_Old.Guid)) { panel_New = Analytical.Create.Panel(Guid.NewGuid(), panel_Old, face3D); Core.Modify.Add(log, "Creating new Panel for Old Panel [{0}]. New Panel [{1}]", panel_Old.Guid, panel_New.Guid); } else { panel_New = Analytical.Create.Panel(panel_Old.Guid, panel_Old, face3D); guids_Updated.Add(panel_Old.Guid); Core.Modify.Add(log, "Updating Panel [{0}] with new geometry", panel_New.Guid); } result.AddObject(panel_New); } else { panel_New = Analytical.Create.Panel(panel_Old.Guid, panel_Old, face3D); Core.Modify.Add(log, "Creating temporary Panel for Panel [{0}]", panel_New.Guid); } if (panel_New == null) { continue; } tuples_InternalPoint3D.Add(new Tuple <Panel, Point3D>(panel_New, face3D.InternalPoint3D(tolerance))); foreach (Space space in spaces_Shell) { if (result.AddRelation(space, panel_New)) { Core.Modify.Add(log, "Space [{0}] and Panel [{1}] relation added", space.Guid, panel_New.Guid); } } Core.Modify.Add(log, "Adding face finished"); } } if (redundantPanels != null && redundantPanels.Count != 0) { Core.Modify.Add(log, "Solving Redundant Panels"); foreach (Panel panel in redundantPanels) { result.RemoveObject <Panel>(panel.Guid); } } List <Panel> panels_Shading = Analytical.Query.CutShading(result.GetPanels(), panels, tolerance); if (panels_Shading != null || panels_Shading.Count != 0) { foreach (Panel panel_Shading in panels_Shading) { result.AddObject(panel_Shading); } } Core.Modify.Add(log, "AdjacencyCluster verification"); Log log_AdjacencyCluster = Analytical.Create.Log(result); if (log != null) { log.AddRange(log_AdjacencyCluster); } Core.Modify.Add(log, "Process completed"); return(result); }