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