public CellDataFormatFlag GetCellDataFormat(string addressOrName, out object dataFormatArgs)
{
RangePosition namedRange;
if (CellPosition.IsValidAddress(addressOrName))
{
return(this.GetCellDataFormat(new CellPosition(addressOrName), out dataFormatArgs));
}
else if (this.TryGetNamedRangePosition(addressOrName, out namedRange))
{
return(this.GetCellDataFormat(namedRange.StartPos, out dataFormatArgs));
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
/// <summary>
/// Get formula from cell specified by an address or registered name
/// </summary>
/// <param name="addressOrName">address or name used to locate a cell</param>
/// <returns>formula as string returned from cell</returns>
public string GetCellFormula(string addressOrName)
{
if (CellPosition.IsValidAddress(addressOrName))
{
return(GetCellFormula(new CellPosition(addressOrName)));
}
NamedRange namedRange;
if (this.registeredNamedRanges.TryGetValue(addressOrName, out namedRange))
{
return(GetCellFormula(namedRange.StartPos));
}
throw new InvalidReferenceException(
"Specified reference neither is an valid address nor name registered in spreadsheet.");
}
/// <summary>
/// Recalculate specified cell formula.
/// </summary>
/// <param name="pos">Address on worksheet to locate the cell.</param>
public void RecalcCell(string address)
{
RangePosition range;
if (CellPosition.IsValidAddress(address))
{
this.RecalcCell(new CellPosition(address));
}
else if (this.TryGetNamedRangePosition(address, out range))
{
this.RecalcCell(range.StartPos);
}
else
{
throw new InvalidAddressException(address);
}
}
/// <summary>
/// Set data of cell at specified position on worksheet.
/// </summary>
/// <param name="addressOrName">Address or name to locate the cell.</param>
/// <param name="data">Data to be set.</param>
public void SetCellData(string addressOrName, object data)
{
NamedRange range;
if (CellPosition.IsValidAddress(addressOrName))
{
SetCellData(new CellPosition(addressOrName), data);
}
else if (this.registeredNamedRanges.TryGetValue(addressOrName, out range))
{
SetCellData(range.StartPos, data);
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
/// <summary>
/// Set formula into cell, calculate the value of formula and update referenced cells.
/// </summary>
/// <param name="addressOrName">Address or name to locate range on worksheet</param>
/// <param name="formula">Formula to be set. Equal sign is not required.</param>
public void SetCellFormula(string addressOrName, string formula)
{
if (CellPosition.IsValidAddress(addressOrName))
{
this.SetCellFormula(new CellPosition(addressOrName), formula);
return;
}
if (this.registeredNamedRanges.TryGetValue(addressOrName, out var range))
{
this.SetCellFormula(range.StartPos, formula);
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
/// <summary>
/// Paste data from tabbed string into worksheet.
/// </summary>
/// <param name="startPos">Start position to fill data.</param>
/// <param name="str">Tabbed string to be pasted.</param>
/// <returns>Range position that indicates the actually filled range.</returns>
public RangePosition PasteFromString(CellPosition startPos, string str)
{
int rows = 0, cols = 0;
string[] lines = str.Split(new string[] { "\r\n" }, StringSplitOptions.None);
for (int r = 0; r < lines.Length; r++)
{
string line = lines[r];
if (line.EndsWith("\n"))
{
line = line.Substring(0, line.Length - 1);
}
//line = line.Trim();
if (line.Length > 0)
{
string[] tabs = line.Split('\t');
cols = Math.Max(cols, tabs.Length);
for (int c = 0; c < tabs.Length; c++)
{
int toRow = selectionRange.Row + r;
int toCol = selectionRange.Col + c;
if (!this.IsValidCell(toRow, toCol))
{
throw new RangeIntersectionException(new RangePosition(toRow, toCol, 1, 1));
}
string text = tabs[c];
if (text.StartsWith("\"") && text.EndsWith("\""))
{
text = text.Substring(1, text.Length - 2);
}
SetCellData(toRow, toCol, text);
}
rows++;
}
}
return(new RangePosition(startPos.Row, startPos.Col, rows, cols));
}
/// <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)
{
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 var range))
{
return(GetCellText(range.StartPos));
}
else
{
throw new InvalidAddressException(address);
}
}
/// <summary>
/// Check the boundary of cell position and return a safe position.
/// </summary>
/// <param name="pos">The cell position to check.</param>
/// <returns>Safe cell position.</returns>
public CellPosition FixPos(CellPosition pos)
{
if (pos.Row < 0)
{
pos.Row = 0;
}
if (pos.Col < 0)
{
pos.Col = 0;
}
if (pos.Row > this.rows.Count - 1)
{
pos.Row = this.rows.Count - 1;
}
if (pos.Col > this.cols.Count - 1)
{
pos.Col = this.cols.Count - 1;
}
return(pos);
}
private CellPosition FindNextMovableCellRight(CellPosition pos, RangePosition moveRange, bool autoReturn = true)
{
int col = pos.Col;
var endCol = this.selectionRange.Cols > 1 ? this.selectionRange.EndCol : this.cols.Count - 1;
if (col >= endCol)
{
var newpos = FindNextMovableCellDown(new CellPosition(pos.Row, moveRange.Col), moveRange, false);
if (pos == newpos)
{
return(pos);
}
pos = newpos;
}
int row = pos.Row;
// find next movable cell rightward
while (col < moveRange.EndCol)
{
col++;
var cell = this.cells[row, col];
if (cell != null && !cell.MergeEndPos.IsEmpty &&
col <= cell.MergeEndPos.Col &&
col > cell.MergeStartPos.Col)
{
continue;
}
if (this.cols[col].InnerWidth > 0)
{
break;
}
}
return(new CellPosition(pos.Row, col));
}
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>
/// Remove all trace arrows from specified position
/// </summary>
/// <param name="pos">Position to locate a cell</param>
public void RemoveCellAllTraceArrows(CellPosition pos)
{
RemoveCellTraceDependents(pos);
RemoveCellTracePrecedents(pos);
}
/// <summary>
/// Remove all trace dependent arrows from specified position
/// </summary>
/// <param name="pos">position to remove cell dependents</param>
/// <returns></returns>
public bool RemoveCellTraceDependents(CellPosition pos)
{
Cell cell = GetCell(pos);
return(cell == null ? false : RemoveCellTraceDependents(cell));
}
/// <summary>
/// Start trace dependents to specified position
/// </summary>
/// <param name="pos">target position to trace dependents</param>
/// <returns>true if trace arrow added</returns>
public bool TraceCellDependents(CellPosition pos)
{
Cell cell = GetCell(pos);
return(cell == null ? false : TraceCellPrecedents(cell));
}
/// <summary>
/// Get formula from cell specified by position
/// </summary>
/// <param name="pos">position to locate the cell to be get</param>
/// <returns>formula as string returned from specified cell</returns>
public string GetCellFormula(CellPosition pos)
{
var cell = GetCell(pos);
return(cell == null ? string.Empty : cell.InnerFormula);
}
/// <summary>
/// Get data from specified cell
/// </summary>
/// <param name="pos">Position of cell to get data</param>
/// <returns>Data of cell</returns>
public object GetCellData(CellPosition pos)
{
return(GetCellData(pos.Row, pos.Col));
}
/// <summary>
/// Delete formula from specified cell.
/// </summary>
/// <param name="pos">Position to locate the cell on worksheet.</param>
public void DeleteCellFormula(CellPosition pos)
{
DeleteCellFormula(pos.Row, pos.Col);
}
/// <summary>
/// Get a list of referenced ranges from formula of specified cell
/// </summary>
/// <param name="pos">position of cell to find its reference list</param>
/// <returns>a list of referenced cell</returns>
public List <ReferenceRange> GetCellFormulaReferenceRanges(CellPosition pos)
{
Cell cell = this.cells[pos.Row, pos.Col];
return(cell == null ? null : GetCellFormulaReferenceRanges(cell));
}
/// <summary>
/// Get data from specified cell
/// </summary>
/// <param name="pos">position to locate the cell</param>
/// <returns>data of cell</returns>
public T GetCellData <T>(CellPosition pos)
{
return(CellUtility.ConvertData <T>(this.GetCellData(pos)));
}
/// <summary>
/// Show cell formula precedent trace lines on worksheet
/// </summary>
/// <param name="address">address to trace the cell</param>
/// <returns>true if trace is successful</returns>
public bool TraceCellPrecedents(string address)
{
return(CellPosition.IsValidAddress(address) ? TraceCellPrecedents(new CellPosition(address)) : false);
}
/// <summary>
/// Get formatted cell text from spcified position
/// </summary>
/// <param name="pos">position to be get</param>
/// <returns>formatted cell's text</returns>
public string GetCellText(CellPosition pos)
{
return(GetCellText(pos.Row, pos.Col));
}
/// <summary>
/// Remove all trace dependent arrows from specified address
/// </summary>
/// <param name="address">address to remove cell dependents</param>
/// <returns></returns>
public bool RemoveCellTraceDependents(string address)
{
return(CellPosition.IsValidAddress(address) ? RemoveCellTraceDependents(new CellPosition(address)) : false);
}
/// <summary>
/// Set data of cell at specified position on worksheet.
/// </summary>
/// <param name="pos">Position of cell to set data.</param>
/// <param name="data">Data of cell to be set.</param>
public void SetCellData(CellPosition pos, object data)
{
SetCellData(pos.Row, pos.Col, data);
}
/// <summary>
/// Set formula into cell, calculate the value of formula and update referenced cells.
/// </summary>
/// <param name="pos">position of cell</param>
/// <param name="formula">Formula to be set. Equal sign is not required.</param>
public void SetCellFormula(CellPosition pos, string formula)
{
this.SetCellFormula(pos.Row, pos.Col, formula);
}
/// <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));
}
internal HighlightRange(Worksheet worksheet, CellPosition startPos, CellPosition endPos)
: base(worksheet, startPos, endPos)
{
this.HighlightColor = worksheet.GetNextAvailableHighlightRangeColor();
}
internal Rectangle GetRangeBounds(CellPosition startPos, CellPosition endPos)
{
return(GetRangePhysicsBounds(new RangePosition(startPos, endPos)));
}
internal void SelectRangeEndByMouse(Point location)
{
if (this.viewportController == null || this.viewportController.View == null)
{
return;
}
var viewport = this.viewportController.View.GetViewByPoint(location) as IRangeSelectableView;
if (viewport == null)
{
viewport = this.viewportController.FocusView as IRangeSelectableView;
}
if (viewport != null)
{
Point vp = viewport.PointToView(location);
var startpos = this.selStart;
var endpos = this.selEnd;
#region Each Operation Status
switch (this.operationStatus)
{
case OperationStatus.FullColumnSelect:
{
int col = -1;
this.FindColumnByPosition(vp.X, out col);
if (col > -1)
{
startpos = new CellPosition(0, startpos.Col);
endpos = new CellPosition(this.rows.Count, col);
}
}
break;
case OperationStatus.FullRowSelect:
{
int row = -1;
this.FindRowByPosition(vp.Y, out row);
if (row > -1)
{
startpos = new CellPosition(startpos.Row, 0);
endpos = new CellPosition(row, this.cols.Count);
}
}
break;
default:
endpos = CellsViewport.GetPosByPoint(viewport, vp);
break;
}
#endregion // Each Operation Status
this.ApplyRangeSelection(startpos, endpos, true);
}
}
internal Rectangle GetCellBounds(CellPosition pos)
{
return(GetCellBounds(pos.Row, pos.Col));
}
/// <summary>
/// Select speicifed range on spreadsheet
/// </summary>
/// <param name="pos1">Start position of specified range</param>
/// <param name="pos2">End position of specified range</param>
public void SelectRange(CellPosition pos1, CellPosition pos2)
{
SelectRange(new RangePosition(pos1, pos2));
}
internal void Clear()
{
// hidden edit textbox
EndEdit(EndEditReason.Cancel);
// clear editing flag
endEditProcessing = false;
// reset ActionManager
if (controlAdapter != null)
{
var actionSupportedControl = controlAdapter.ControlInstance as IActionControl;
if (actionSupportedControl != null)
{
actionSupportedControl.ClearActionHistoryForWorksheet(this);
}
}
#if OUTLINE
// clear row outlines
if (this.outlines != null)
{
ClearOutlines(RowOrColumn.Row | RowOrColumn.Column);
}
#endif // OUTLINE
// clear named ranges
registeredNamedRanges.Clear();
// clear highlight ranges
if (highlightRanges != null)
{
highlightRanges.Clear();
}
#if PRINT
// clear page breaks
if (this.pageBreakRows != null)
{
this.pageBreakRows.Clear();
}
if (this.pageBreakCols != null)
{
this.pageBreakCols.Clear();
}
if (this.userPageBreakRows != null)
{
this.userPageBreakRows.Clear();
}
if (this.userPageBreakCols != null)
{
this.userPageBreakCols.Clear();
}
this.printableRange = RangePosition.Empty;
this.printSettings = null;
#endif // PRINT
#if DRAWING
// drawing objects
if (this.drawingCanvas != null)
{
this.drawingCanvas.Children.Clear();
}
#endif // DRAWING
// reset default width and height
this.defaultColumnWidth = InitDefaultColumnWidth;
this.defaultRowHeight = InitDefaultRowHeight;
// clear root style
this.RootStyle = new WorksheetRangeStyle(DefaultStyle);
// clear focus highlight ranges
this.FocusHighlightRange = null;
// restore to default operation mode
this.operationStatus = OperationStatus.Default;
// restore settings
this.settings = WorksheetSettings.Default;
if (this.SettingsChanged != null)
{
this.SettingsChanged(this, null);
}
#if FORMULA
// clear formula referenced cells and ranges
formulaRanges.Clear();
// clear trace lines
if (this.traceDependentArrows != null)
{
this.traceDependentArrows.Clear();
}
#endif // FORMULA
#if EX_SCRIPT
if (Srm != null)
{
RaiseScriptEvent("unload");
}
#endif // EX_SCRIPT
// unfreeze rows and columns
CellPosition pos = this.FreezePos;
if (pos.Row > 0 || pos.Col > 0)
{
Unfreeze();
}
if (this.viewportController != null)
{
// reset viewport controller
viewportController.Reset();
}
// TODO: release objects inside cells and borders
cells = new CellArray();
hBorders = new HBorderArray();
vBorders = new VBorderArray();
// clear header & index
this.rows.Clear();
this.cols.Clear();
// reset max row and column indexes
this.maxRowHeader = -1;
this.maxColumnHeader = -1;
// reset highlight range color counter
this.rangeHighlightColorCounter = 0;
}
