private void AutoFillSerialCells(List <CellPosition> fromCells, List <CellPosition> toCells) { if (!fromCells.Any() || !toCells.Any()) { return; } var autoFillSequenceInput = fromCells .Select(cellPosition => this[cellPosition]) .ToList(); var autoFillSequence = new AutoFillSequence(autoFillSequenceInput); var autoFillExtrapolatedValues = autoFillSequence.Extrapolate(toCells.Count); 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 (!string.IsNullOrEmpty(fromCell?.InnerFormula)) { FormulaRefactor.Reuse(this, fromCellPosition, new RangePosition(toCellPosition)); } else { toCell.Data = autoFillExtrapolatedValues[toCellIndex]; } } }
private void AutoFillSerialCells(List <CellPosition> fromCells, List <CellPosition> toCells) { if (!fromCells.Any() || !toCells.Any()) { return; } var autoFillSequenceInput = fromCells .Select(cellPosition => this[cellPosition]) .ToList(); var autoFillSequence = new AutoFillSequence(autoFillSequenceInput); var autoFillExtrapolatedValues = autoFillSequence.Extrapolate(toCells.Count); DragCellData dragCellData = new DragCellData() { FromCells = new List <CellPosition>(fromCells), ToCells = new List <CellPosition>(toCells) }; 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 (!string.IsNullOrEmpty(fromCell?.InnerFormula)) { FormulaRefactor.Reuse(this, fromCellPosition, new RangePosition(toCellPosition)); } else { toCell.DataFormat = fromCell.DataFormat; toCell.DataFormatArgs = fromCell.DataFormatArgs; //toCell.Data = autoFillExtrapolatedValues[toCellIndex]; dragCellData.Data = autoFillExtrapolatedValues[toCellIndex]; if (toCell.SetDragData(dragCellData) == false) { break; } toCell.Style = fromCell.Style; //toCell.Style.HAlign = fromCell.Style.HAlign; //toCell.Style.VAlign = fromCell.Style.VAlign; //toCell.Style.TextColor = fromCell.Style.TextColor; //toCell.Style.FontName = fromCell.Style.FontName; //toCell.Style.FontSize = fromCell.Style.FontSize; //toCell.Style.Bold = fromCell.Style.Bold; //toCell.Style.Italic = fromCell.Style.Italic; //toCell.Style.Strikethrough = fromCell.Style.Strikethrough; //toCell.Style.Underline = fromCell.Style.Underline; //toCell.Style.TextWrap = fromCell.Style.TextWrap; //toCell.Style.Indent = fromCell.Style.Indent; //toCell.Style.Padding = fromCell.Style.Padding; //toCell.Style.RotationAngle = fromCell.Style.RotationAngle; } } }
private void GenerateFormula() { if (currentSyntaxTree != null) { FormulaFormatFlag flag = GetCurrentFormatSetting(); string formula = FormulaRefactor.Generate(txtGeneratedFormula.Text, currentSyntaxTree, flag); txtGeneratedFormula.Text = formula; } else { txtGeneratedFormula.Text = string.Empty; } }
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); } } } }
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)); }
/// <summary> /// Auto fill specified serial in range. /// </summary> /// <param name="fromRange">Range to read filling rules.</param> /// <param name="toRange">Range to be filled.</param> public void AutoFillSerial(RangePosition fromRange, RangePosition toRange) { fromRange = this.FixRange(fromRange); toRange = this.FixRange(toRange); #region Arguments Check if (fromRange.IntersectWith(toRange)) { throw new ArgumentException("fromRange and toRange cannot being intersected."); } if (toRange != CheckMergedRange(toRange)) { throw new ArgumentException("cannot change a part of merged range."); } #endregion // Arguments Check if (fromRange.Col == toRange.Col && fromRange.Cols == toRange.Cols) { #region Vertical Fill for (int c = toRange.Col; c <= toRange.EndCol; c++) { double diff = 1; #region Calc diff if (fromRange.Rows > 1) { for (int r = fromRange.Row; r < fromRange.EndRow; r++) { double val1 = 0; if (!this.TryGetNumberData(r, c, out val1)) { break; } double val2; if (this.TryGetNumberData(r + 1, c, out val2)) { if (r == fromRange.Row) { diff = (val2 - val1); } else { diff = (diff + (val2 - val1)) / 2; } } } } #endregion // Calc diff #region Up to Down for (int toRow = toRange.Row, index = 0; toRow < toRange.EndRow + 1; index++) { Cell toCell = this.cells[toRow, c]; if (toCell != null && toCell.Rowspan < 0) { toRow++; continue; } CellPosition fromPos = new CellPosition(fromRange.Row + (index % fromRange.Rows), c); Cell fromCell = this.cells[fromPos.Row, fromPos.Col]; if (fromCell == null || fromCell.Rowspan <= 0) { this[toRow, c] = null; toRow++; continue; } if (fromCell != null && !string.IsNullOrEmpty(fromCell.InnerFormula)) { #region Fill Formula FormulaRefactor.Reuse(this, fromPos, new RangePosition(toRow, c, 1, 1)); #endregion // Fill Formula } else { #region Fill Number double refValue = 0; if (CellUtility.TryGetNumberData(fromCell.Data, out refValue)) { this[toRow, c] = refValue + diff * (toRow - fromPos.Row); } #endregion // Fill Number } toRow += Math.Max(fromCell.Rowspan, toCell == null ? 1 : toCell.Rowspan); } #endregion // Up to Down } #endregion // Vertical Fill } else if (fromRange.Row == toRange.Row && fromRange.Rows == toRange.Rows) { #region Horizontal Fill for (int r = toRange.Row; r <= toRange.EndRow; r++) { double diff = 1; #region Calc diff if (fromRange.Cols > 1) { for (int c = fromRange.Col; r < fromRange.EndCol; c++) { double val1 = 0; if (!this.TryGetNumberData(r, c, out val1)) { break; } double val2; if (this.TryGetNumberData(r, c + 1, out val2)) { if (c == fromRange.Col) { diff = (val2 - val1); } else { diff = (diff + (val2 - val1)) / 2; } } } } #endregion // Calc diff #region Left to Right for (int toCol = toRange.Col, index = 0; toCol < toRange.EndCol + 1; index++) { Cell toCell = this.cells[r, toCol]; if (toCell != null && toCell.Colspan < 0) { toCol++; continue; } CellPosition fromPos = new CellPosition(r, fromRange.Col + (index % fromRange.Cols)); Cell fromCell = this.cells[fromPos.Row, fromPos.Col]; if (fromCell == null || fromCell.Colspan <= 0) { this[r, toCol] = null; toCol++; continue; } if (fromCell != null && !string.IsNullOrEmpty(fromCell.InnerFormula)) { #region Fill Formula FormulaRefactor.Reuse(this, fromPos, new RangePosition(r, toCol, 1, 1)); #endregion // Fill Formula } else { #region Fill Number double refValue = 0; if (CellUtility.TryGetNumberData(fromCell.Data, out refValue)) { this[r, toCol] = refValue + diff * (toCol - fromPos.Col); } #endregion // Fill Number } toCol += Math.Max(fromCell.Colspan, toCell == null ? 1 : toCell.Colspan); } #endregion // Left to Right } #endregion // Vertical Fill } else { throw new InvalidOperationException("The fromRange and toRange must be having same number of rows or same number of columns."); } }