/// <summary> /// Return a range position that is the minimum range contains two specified ranges. /// </summary> /// <param name="range1">The first range position.</param> /// <param name="range2">The secondary range position.</param> /// <returns>A range that contains the two specified ranges.</returns> public static RangePosition Union(RangePosition range1, RangePosition range2) { if (range1.IsEmpty && range2.IsEmpty) { return(RangePosition.Empty); } else if (range1.IsEmpty) { return(range2); } else if (range2.IsEmpty) { return(range1); } int row = Math.Min(range1.row, range2.row); int col = Math.Min(range1.col, range2.col); int endrow = Math.Max(range1.EndRow, range2.EndRow); int endcol = Math.Max(range1.EndCol, range2.EndCol); return(new RangePosition(row, col, endrow - row + 1, endcol - col + 1)); }
private bool CheckQuickSortRange(int columnIndex, int row, int endRow, int col, int endCol, SortOrder order, ref RangePosition affectRange) { for (int c = col; c <= endCol; c++) { var cell1 = this.cells[row, c]; for (int r = row + 1; r <= endRow; r++) { var cell2 = this.cells[r, c]; if (cell1 != null && cell2 != null && ((cell1.IsValidCell && !cell2.IsValidCell) || (!cell1.IsValidCell && cell2.IsValidCell))) { return(false); } } } return(true); }
internal void RaiseRangeDataChangedEvent(RangePosition range) { if (!this.suspendDataChangedEvent) { if (RangeDataChanged != null) { RangeDataChanged(this, new RangeEventArgs(range)); } #if EX_SCRIPT if (this.Srm != null && this.worksheetObj != null) { var ondatachange = Srm.DefaultContext.EvaluatePropertyGet(this.worksheetObj, "ondatachange"); if (ondatachange != null) { Srm.InvokeFunctionIfExisted(this.worksheetObj, "ondatachange", new RSRangeObject(this, range)); } } #endif } }
private bool RaiseBeforePasteEvent(RangePosition range) { if (BeforePaste != null) { var evtArg = new BeforeRangeOperationEventArgs(range); BeforePaste(this, evtArg); if (evtArg.IsCancelled) { return(false); } } #if EX_SCRIPT object scriptReturn = RaiseScriptEvent("onpaste", new RSRangeObject(this, range)); if (scriptReturn != null && !ScriptRunningMachine.GetBoolValue(scriptReturn)) { return(false); } #endif // EX_SCRIPT return(true); }
/// <summary> /// Load CSV data from stream into worksheet. /// </summary> /// <param name="s">Input stream to read CSV data.</param> /// <param name="encoding">Text encoding used to read and decode plain-text from stream.</param> /// <param name="targetRange">The range used to fill loaded CSV data.</param> /// <param name="autoSpread">decide whether or not to append rows or columns automatically to fill csv data</param> /// <param name="bufferLines">decide how many lines int the buffer to read and fill csv data</param> public object LoadCSV(Stream s, Encoding encoding, RangePosition targetRange, bool autoSpread, int bufferLines) { var arg = new CSVFormatArgument { AutoSpread = autoSpread, BufferLines = bufferLines, TargetRange = targetRange, }; this.controlAdapter?.ChangeCursor(CursorStyle.Busy); try { CSVFileFormatProvider csvProvider = new CSVFileFormatProvider(); Clear(); csvProvider.Load(this.workbook, s, encoding, arg); } finally { this.controlAdapter?.ChangeCursor(CursorStyle.PlatformDefault); } return(arg); }
/// <summary> /// Get cell display text by specified address /// </summary> /// <param name="address">address to locate a cell</param> /// <returns>display text in string returned from specified cell</returns> public string GetCellText(string address) { NamedRange range; if (CellPosition.IsValidAddress(address)) { return(GetCellText(new CellPosition(address))); } else if (RangePosition.IsValidAddress(address)) { return(GetCellText((new RangePosition(address)).StartPos)); } else if (NamedRange.IsValidName(address) && this.TryGetNamedRange(address, out range)) { return(GetCellText(range.StartPos)); } else { throw new InvalidAddressException(address); } }
/// <summary> /// Delete data format settings from specified range /// </summary> /// <param name="range">Range to be remove formats</param> public void DeleteRangeDataFormat(RangePosition range) { var fixedRange = this.FixRange(range); for (int r = fixedRange.Row; r <= fixedRange.EndRow; r++) { for (int c = fixedRange.Col; c <= fixedRange.EndCol;) { Cell cell = this.cells[r, c]; if (cell == null) { c++; } else { // clear data format flags cell.DataFormat = CellDataFormatFlag.General; cell.DataFormatArgs = null; if (cell.IsValidCell) { // reformat cell DataFormatterManager.Instance.FormatCell(cell, cell.Worksheet.Culture); // update cell render alignemnt (Number aligned to right might be restored to left) unvell.ReoGrid.Utility.StyleUtility.UpdateCellRenderAlign(this, cell); c += cell.Colspan > 1 ? cell.Colspan : 1; } else { c++; } } } } }
/// <summary> /// Auto fill specified serial in range. /// </summary> /// <param name="fromAddressOrName">Range to read filling rules.</param> /// <param name="toAddressOrName">Range to be filled.</param> public void AutoFillSerial(string fromAddressOrName, string toAddressOrName) { NamedRange fromNRange, toNRange; RangePosition fromRange, toRange; #region fromRange if (this.TryGetNamedRange(fromAddressOrName, out fromNRange)) { fromRange = fromNRange.Position; } else if (RangePosition.IsValidAddress(fromAddressOrName)) { fromRange = new RangePosition(fromAddressOrName); } else { throw new InvalidAddressException(fromAddressOrName); } #endregion // fromRange #region toRange if (this.TryGetNamedRange(toAddressOrName, out toNRange)) { toRange = toNRange.Position; } else if (RangePosition.IsValidAddress(toAddressOrName)) { toRange = new RangePosition(toAddressOrName); } else { throw new InvalidAddressException(toAddressOrName); } #endregion // toRange this.AutoFillSerial(fromRange, toRange); }
public void Load(IWorkbook workbook, List <T> obj, object arg) { bool autoSpread = true; string sheetName = String.Empty; int bufferItems = DEFAULT_READ_BUFFER_ITEMS > obj.Count ? obj.Count : DEFAULT_READ_BUFFER_ITEMS; RangePosition targetRange = RangePosition.EntireRange; GenericFormatArgument genericArg = arg as GenericFormatArgument; if (genericArg != null) { sheetName = genericArg.SheetName; targetRange = genericArg.TargetRange; } Worksheet sheet = null; if (workbook.Worksheets.Count == 0) { sheet = workbook.CreateWorksheet(sheetName); workbook.Worksheets.Add(sheet); } else { while (workbook.Worksheets.Count > 1) { workbook.Worksheets.RemoveAt(workbook.Worksheets.Count - 1); } sheet = workbook.Worksheets[0]; sheet.Reset(); } this.Read(obj, sheet, targetRange, bufferItems, autoSpread); // ApplyStyles(sheet, genericArg.Stylesheet); }
/// <summary> /// Copy content from specified range to another range. /// </summary> /// <param name="fromRangeAddress">Address to locate the range to read data.</param> /// <param name="toRangeAddress">Address to put copied data.</param> /// <exception cref="CellDataReadonlyException">Throw when current worksheet is read-only.</exception> /// <exception cref="RangeIntersectionException">Range to be moved or copied contains a part of another merged cell.</exception> public void CopyRange(string fromRangeAddress, string toRangeAddress) { RangePosition fromRange, toRange; if (RangePosition.IsValidAddress(fromRangeAddress)) { fromRange = new RangePosition(fromRangeAddress); } else if (!this.TryGetNamedRangePosition(fromRangeAddress, out fromRange)) { throw new InvalidAddressException(fromRangeAddress); } if (RangePosition.IsValidAddress(toRangeAddress)) { toRange = new RangePosition(toRangeAddress); } else if (!this.TryGetNamedRangePosition(toRangeAddress, out toRange)) { throw new InvalidAddressException(toRangeAddress); } this.CopyRange(fromRange, toRange); }
/// <summary> /// Export spreadsheet as CSV format from specified range. /// </summary> /// <param name="s">Stream to write CSV format as stream.</param> /// <param name="range">Range to be output from this worksheet.</param> /// <param name="encoding">Text encoding during output text in CSV format.</param> public void ExportAsCSV(Stream s, RangePosition range, Encoding encoding = null) { range = FixRange(range); int maxRow = Math.Min(range.EndRow, this.MaxContentRow); int maxCol = Math.Min(range.EndCol, this.MaxContentCol); if (encoding == null) { encoding = Encoding.Default; } using (var sw = new StreamWriter(s, encoding)) { StringBuilder sb = new StringBuilder(); for (int r = range.Row; r <= maxRow; r++) { if (sb.Length > 0) { sw.WriteLine(sb.ToString()); sb.Length = 0; } for (int c = range.Col; c <= maxCol;) { if (sb.Length > 0) { sb.Append(','); } var cell = this.GetCell(r, c); if (cell == null || !cell.IsValidCell) { c++; } else { var data = cell.Data; bool quota = false; //if (!quota) //{ // if (cell.DataFormat == CellDataFormatFlag.Text) // { // quota = true; // } //} if (data is string str) { if (!string.IsNullOrEmpty(str) && (cell.DataFormat == CellDataFormatFlag.Text || str.IndexOf(',') >= 0 || str.IndexOf('"') >= 0 || str.StartsWith(" ") || str.EndsWith(" "))) { quota = true; } } else { str = Convert.ToString(data); } if (quota) { sb.Append('"'); sb.Append(str.Replace("\"", "\"\"")); sb.Append('"'); } else { sb.Append(str); } c += cell.Colspan; } } } if (sb.Length > 0) { sw.WriteLine(sb.ToString()); sb.Length = 0; } } }
/// <summary> /// Remove all trace arrows from specified range /// </summary> /// <param name="range"></param> public void RemoveRangeAllTraceArrows(RangePosition range) { this.IterateCells(range, (r, c, cell) => RemoveCellAllTraceArrows(cell)); }
/// <summary> /// Load CSV data from stream into worksheet. /// </summary> /// <param name="s">Input stream to read CSV data.</param> /// <param name="encoding">Text encoding used to read and decode plain-text from stream.</param> /// <param name="targetRange">The range used to fill loaded CSV data.</param> public void LoadCSV(Stream s, Encoding encoding, RangePosition targetRange) { LoadCSV(s, encoding, targetRange, targetRange.IsEntire, 256); }
/// <summary> /// Copy data and put into Clipboard. /// </summary> public bool Copy() { if (IsEditing) { this.controlAdapter.EditControlCopy(); } else { this.controlAdapter.ChangeCursor(CursorStyle.Busy); try { if (BeforeCopy != null) { var evtArg = new BeforeRangeOperationEventArgs(selectionRange); BeforeCopy(this, evtArg); if (evtArg.IsCancelled) { return(false); } } #if EX_SCRIPT var scriptReturn = RaiseScriptEvent("oncopy"); if (scriptReturn != null && !ScriptRunningMachine.GetBoolValue(scriptReturn)) { return(false); } #endif // EX_SCRIPT // highlight current copy range currentCopingRange = selectionRange; #if WINFORM || WPF DataObject data = new DataObject(); data.SetData(ClipBoardDataFormatIdentify, GetPartialGrid(currentCopingRange, PartialGridCopyFlag.All, ExPartialGridCopyFlag.None, true)); string text = StringifyRange(currentCopingRange); if (!string.IsNullOrEmpty(text)) { data.SetText(text); } // set object data into clipboard Clipboard.SetDataObject(data); #endif // WINFORM || WPF if (AfterCopy != null) { AfterCopy(this, new RangeEventArgs(this.selectionRange)); } } catch (Exception ex) { this.NotifyExceptionHappen(ex); return(false); } finally { this.controlAdapter.ChangeCursor(CursorStyle.PlatformDefault); } } return(true); }
/// <summary> /// Copy data from Clipboard and put on grid. /// /// Currently ReoGrid supports the following types of source from the clipboard. /// - Data from another ReoGrid instance /// - Plain/Unicode Text from any Windows Applications /// - Tabbed Plain/Unicode Data from Excel or similar applications /// /// When data copied from another ReoGrid instance, and the destination range /// is bigger than the source, ReoGrid will try to repeat putting data to fill /// the destination range entirely. /// /// Todo: Copy border and cell style from Excel. /// </summary> public bool Paste() { if (IsEditing) { this.controlAdapter.EditControlPaste(); } else { // Paste method will always perform action to do paste // do nothing if in readonly mode if (this.HasSettings(WorksheetSettings.Edit_Readonly) // or selection is empty || this.selectionRange.IsEmpty) { return(false); } try { this.controlAdapter.ChangeCursor(CursorStyle.Busy); PartialGrid partialGrid = null; string clipboardText = null; #if WINFORM || WPF DataObject data = Clipboard.GetDataObject() as DataObject; if (data != null) { partialGrid = data.GetData(ClipBoardDataFormatIdentify) as PartialGrid; if (data.ContainsText()) { clipboardText = data.GetText(); } } #elif ANDROID #endif // WINFORM || WPF if (partialGrid != null) { #region Partial Grid Pasting int startRow = selectionRange.Row; int startCol = selectionRange.Col; int rows = partialGrid.Rows; int cols = partialGrid.Columns; int rowRepeat = 1; int colRepeat = 1; if (selectionRange.Rows % partialGrid.Rows == 0) { rows = selectionRange.Rows; rowRepeat = selectionRange.Rows / partialGrid.Rows; } if (selectionRange.Cols % partialGrid.Columns == 0) { cols = selectionRange.Cols; colRepeat = selectionRange.Cols / partialGrid.Columns; } var targetRange = new RangePosition(startRow, startCol, rows, cols); if (!RaiseBeforePasteEvent(targetRange)) { return(false); } if (targetRange.EndRow >= this.rows.Count || targetRange.EndCol >= this.cols.Count) { // TODO: paste range overflow // need to notify user-code to handle this return(false); } // check whether the range to be pasted contains readonly cell if (this.CheckRangeReadonly(targetRange)) { this.NotifyExceptionHappen(new OperationOnReadonlyCellException("specified range contains readonly cell")); return(false); } // check any intersected merge-range in partial grid // bool cancelPerformPaste = false; if (partialGrid.Cells != null) { try { #region Check repeated intersected ranges for (int rr = 0; rr < rowRepeat; rr++) { for (int cc = 0; cc < colRepeat; cc++) { partialGrid.Cells.Iterate((row, col, cell) => { if (cell.IsMergedCell) { for (int r = startRow; r < cell.MergeEndPos.Row - cell.InternalRow + startRow + 1; r++) { for (int c = startCol; c < cell.MergeEndPos.Col - cell.InternalCol + startCol + 1; c++) { int tr = r + rr * partialGrid.Rows; int tc = c + cc * partialGrid.Columns; var existedCell = cells[tr, tc]; if (existedCell != null) { if ( // cell is a part of merged cell (existedCell.Rowspan == 0 && existedCell.Colspan == 0) // cell is merged cell || existedCell.IsMergedCell) { throw new RangeIntersectionException(selectionRange); } // cell is readonly else if (existedCell.IsReadOnly) { throw new CellDataReadonlyException(cell.InternalPos); } } } } } return(Math.Min(cell.Colspan, (short)1)); }); } } #endregion // Check repeated intersected ranges } catch (Exception ex) { cancelPerformPaste = true; // raise event to notify user-code there is error happened during paste operation if (OnPasteError != null) { OnPasteError(this, new RangeOperationErrorEventArgs(selectionRange, ex)); } } } if (!cancelPerformPaste) { DoAction(new SetPartialGridAction(new RangePosition( startRow, startCol, rows, cols), partialGrid)); } #endregion // Partial Grid Pasting } else if (!string.IsNullOrEmpty(clipboardText)) { #region Plain Text Pasting var arrayData = RGUtility.ParseTabbedString(clipboardText); int rows = Math.Max(selectionRange.Rows, arrayData.GetLength(0)); int cols = Math.Max(selectionRange.Cols, arrayData.GetLength(1)); var targetRange = new RangePosition(selectionRange.Row, selectionRange.Col, rows, cols); if (!RaiseBeforePasteEvent(targetRange)) { return(false); } if (this.controlAdapter != null) { var actionSupportedControl = this.controlAdapter.ControlInstance as IActionControl; if (actionSupportedControl != null) { actionSupportedControl.DoAction(this, new SetRangeDataAction(targetRange, arrayData)); } } #endregion // Plain Text Pasting } } catch (Exception ex) { // raise event to notify user-code there is error happened during paste operation //if (OnPasteError != null) //{ // OnPasteError(this, new RangeOperationErrorEventArgs(selectionRange, ex)); //} this.NotifyExceptionHappen(ex); } finally { this.controlAdapter.ChangeCursor(CursorStyle.Selection); RequestInvalidate(); } if (AfterPaste != null) { AfterPaste(this, new RangeEventArgs(this.selectionRange)); } } return(true); }
/// <summary> /// Select specified range. /// </summary> /// <param name="range">Specified range to be selected</param> private RangePosition FixRangeSelection(RangePosition range) { if (range.IsEmpty) { return(RangePosition.Empty); } #if DEBUG Stopwatch stop = Stopwatch.StartNew(); #endif RangePosition fixedRange = FixRange(range); int minr = fixedRange.Row; int minc = fixedRange.Col; int maxr = fixedRange.EndRow; int maxc = fixedRange.EndCol; switch (this.selectionMode) { case WorksheetSelectionMode.Cell: maxr = minr = range.Row; maxc = minc = range.Col; break; case WorksheetSelectionMode.Row: minc = 0; maxc = this.cols.Count - 1; break; case WorksheetSelectionMode.Column: minr = 0; maxr = this.rows.Count - 1; break; } if ((this.selectionMode == WorksheetSelectionMode.Cell || this.selectionMode == WorksheetSelectionMode.Range) && ((fixedRange.Cols < this.cols.Count && fixedRange.Rows < this.rows.Count) || this.cols.Count == 1 || this.rows.Count == 1) ) { #region Check and select the whole merged region //#if DEBUG // if (!Toolkit.IsKeyDown(unvell.Common.Win32Lib.Win32.VKey.VK_CONTROL)) // { //#endif // // if there are any entire rows or columns selected (full == -1) // the selection bounds of merged range will not be checked. // any changes to the selection will also not be appiled to the range. // RangePosition checkedRange = CheckMergedRange(new RangePosition(minr, minc, maxr - minr + 1, maxc - minc + 1)); minr = checkedRange.Row; minc = checkedRange.Col; maxr = checkedRange.EndRow; maxc = checkedRange.EndCol; //#if DEBUG // } //#endif #endregion } int rows = maxr - minr + 1; int cols = maxc - minc + 1; #if DEBUG stop.Stop(); if (stop.ElapsedMilliseconds > 25) { Debug.WriteLine("select range takes " + stop.ElapsedMilliseconds + " ms."); } #endif return(new RangePosition(minr, minc, rows, cols)); }
/// <summary> /// Check whether or not that the specified range intersects with this range. /// </summary> /// <param name="range">The range to be checked.</param> /// <returns>True if specified range intersects with this range.</returns> public bool IntersectWith(RangePosition range) { return(this.Position.IntersectWith(range)); }
/// <summary> /// Load CSV file into worksheet. /// </summary> /// <param name="path">File contains CSV data.</param> /// <param name="targetRange">The range used to fill loaded CSV data.</param> public void LoadCSV(string path, RangePosition targetRange) { LoadCSV(path, Encoding.Default, targetRange); }
public HighlightRange Add(RangePosition range) { return(Worksheet.AddHighlightRange(range)); }
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 RangePosition SetPartialGrid(RangePosition toRange, PartialGrid data, PartialGridCopyFlag flag) { return(this.SetPartialGrid(toRange, data, flag, ExPartialGridCopyFlag.None)); }
/// <summary> /// Copy from a separated grid into current grid. /// </summary> /// <param name="data">Partial grid to be copied.</param> /// <param name="toRange">Range to be copied.</param> /// <returns>Range has been copied</returns> public RangePosition SetPartialGrid(RangePosition toRange, PartialGrid data) { return(this.SetPartialGrid(toRange, data, PartialGridCopyFlag.All, ExPartialGridCopyFlag.None)); }
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); }
/// <summary> /// Copy a part of worksheet from specified range. /// </summary> /// <param name="range">The range to be copied.</param> /// <returns>A part of worksheet that is copied from original worksheet.</returns> public PartialGrid GetPartialGrid(RangePosition range) { return(GetPartialGrid(range, PartialGridCopyFlag.All, ExPartialGridCopyFlag.BorderOutsideOwner)); }
/// <summary> /// Load CSV data from stream into worksheet. /// </summary> /// <param name="s">Input stream to read CSV data.</param> /// <param name="targetRange">The range used to fill loaded CSV data.</param> public void LoadCSV(Stream s, RangePosition targetRange) { LoadCSV(s, Encoding.Default, targetRange); }
/// <summary> /// Set data of cell at specified position on worksheet. /// </summary> /// <param name="row">Index of row of specified cell.</param> /// <param name="col">Index of column of specified cell.</param> /// <param name="data">Data of cell.</param> public void SetCellData(int row, int col, object data) { if (row < 0 || row > this.rows.Count - 1) { throw new ArgumentOutOfRangeException("row", "Number of row is out of the maximum rows, use either AppendRows or Resize to expend this worksheet."); } if (col < 0 || col > this.cols.Count - 1) { throw new ArgumentOutOfRangeException("col", "Number of column is out of maximum columns, use either AppendCols or Resize to expend this worksheet."); } if (data is Array) { var arr = (Array)data; if (arr.Rank == 1) { for (int c = col; c < Math.Min(col + arr.Length, this.cols.Count); c++) { SetCellData(row, c, arr.GetValue(c - col)); } } else if (arr.Rank == 2) { int rows = arr.GetLength(0); int cols = arr.GetLength(1); SetRangeData(new RangePosition(row, col, rows, cols), arr); } else { throw new ArgumentException("Array with more than 2 ranks is not supported."); } } else if (data is IEnumerable <object> ) { var elements = (IEnumerable <object>)data; int c = col; foreach (var ele in elements) { SetCellData(row, c, ele); c++; if (c >= this.cols.Count) { break; } } } else if (data is PartialGrid) { var subgrid = (PartialGrid)data; var range = new RangePosition(row, col, subgrid.Rows, subgrid.Columns); SetPartialGrid(range, subgrid); } else if (data is System.Data.DataTable) { var dt = (System.Data.DataTable)data; SetRangeData(new RangePosition(row, col, dt.Rows.Count, dt.Columns.Count), dt); } else { var cell = cells[row, col]; // both data and cell is null, then no need to update if ((data != null || cell != null) // if cell is not null, and it is valid (not merged by other cells), then need to update && (cell == null || cell.IsValidCell)) { SetSingleCellData(CreateAndGetCell(row, col), data); } } }
internal HighlightRange(Worksheet worksheet, RangePosition range) : this(worksheet, range.StartPos, range.EndPos) { }
/// <summary> /// Check whether specified string is an valid address to locate cell or range /// </summary> /// <param name="address">address for cell or range</param> public static bool IsValidAddress(string address) { return(CellPosition.IsValidAddress(address) || RangePosition.IsValidAddress(address)); }
private void ChangeSelectionRange(CellPosition start, CellPosition end) { var range = FixRangeSelection(new RangePosition(start, end)); // compare to current selection, only do this when selection was really changed. if (this.selectionRange != range) { if (this.BeforeSelectionRangeChange != null) { var arg = new BeforeSelectionChangeEventArgs(start, end); this.BeforeSelectionRangeChange(this, arg); if (arg.IsCancelled) { return; } if (start != arg.SelectionStart || end != arg.SelectionEnd) { start = arg.SelectionStart; end = arg.SelectionEnd; range = FixRangeSelection(new RangePosition(start, end)); } } this.selectionRange = range; this.selStart = start; this.selEnd = end; //if (!range.Contains(selStart)) selStart = range.StartPos; //if (!range.Contains(selEnd)) selEnd = range.EndPos; // focus pos validations: // 1. focus pos must be inside selection range // 2. focus pos cannot stop at invalid cell (any part of merged cell) if (this.focusPos.IsEmpty || !range.Contains(this.focusPos) || !IsValidCell(this.focusPos)) { var focusPos = selStart; // find first valid cell as focus pos for (int r = range.Row; r <= range.EndRow; r++) { for (int c = range.Col; c <= range.EndCol; c++) { var cell = this.cells[r, c]; if (cell != null && (cell.Colspan <= 0 || cell.Rowspan <= 0)) { continue; } focusPos.Row = r; focusPos.Col = c; goto quit_loop; } } quit_loop: if (focusPos.Col < this.cols.Count && focusPos.Row < this.rows.Count) { FocusPos = focusPos; } } // update focus return column this.focusReturnColumn = end.Col; if (this.operationStatus == OperationStatus.RangeSelect) { this.SelectionRangeChanging?.Invoke(this, new RangeEventArgs(this.selectionRange)); #if EX_SCRIPT // comment out this if you get performance problem when using script extension RaiseScriptEvent("onselectionchanging"); #endif } else { this.SelectionRangeChanged?.Invoke(this, new RangeEventArgs(this.selectionRange)); #if EX_SCRIPT RaiseScriptEvent("onselectionchange"); #endif } RequestInvalidate(); } }
/// <summary> /// Convert range address into cell address style if the range is a merged cell (A1:A1 => A1) /// </summary> /// <param name="sheet">Worksheet instance used to check whther or not the range is a merged cell</param> /// <param name="range">Range to be converted</param> /// <returns>Single cell address if convert is successful; otherwise return the range address</returns> public static string ToSingleAddressIfPossible(Worksheet sheet, RangePosition range) { return((sheet.IsMergedCell(range)) ? range.StartPos.ToAddress() : range.ToAddress()); }