void grid_BeforePaste(object sender, RGBeforeRangeOperationEventArgs e)
{
if (chkPreventPasteEvent.Checked || chkCustomizePaste.Checked)
{
e.IsCancelled = true;
if (chkCustomizePaste.Checked)
{
string text = Clipboard.GetText();
object[,] data = RGUtility.ParseTabbedString(text);
// set a new range
var applyRange = new ReoGridRange(grid.SelectionRange.Row,
grid.SelectionRange.Col,
data.GetLength(0), data.GetLength(1));
grid.SetRangeData(applyRange, data);
grid.SetRangeStyle(applyRange, new ReoGridRangeStyle
{
Flag = PlainStyleFlag.FillAll,
BackColor = Color.Yellow,
});
}
}
}
/// <summary>
/// Returns the average of its arguments
/// </summary>
/// <param name="ctrl">instance of ReoGrid control</param>
/// <param name="range">range to count</param>
/// <returns></returns>
public static double AVERAGE(Worksheet ctrl, ReoGridRange range)
{
double val = 0;
int count = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
var data = cell.InnerData;
if (data != null)
{
double cellValue;
if (!RGUtility.ConvertCellData <double>(data, out cellValue))
{
throw new FormulaEvalutionException("Input value is not in number: " + cell.InternalPos.ToAddress());
}
count++;
}
return(true);
});
return(val / count);
}
private void btnSetEditableRange_Click(object sender, EventArgs ee)
{
var editableRange = new ReoGridRange(3, 1, 2, 3);
grid.SetRangeBorder(editableRange, ReoGridBorderPos.Outline, ReoGridBorderStyle.SolidBlack);
grid[2, 1] = "Edit only be allowed in this range:";
grid.BeforeCellEdit += (s, e) => e.IsCancelled = !editableRange.Contains(e.Cell.GetPos());
}
/// <summary>
/// Counts how many numbers are in the list of arguments
/// </summary>
/// <param name="ctrl">instance of ReoGrid control</param>
/// <param name="range">range to count</param>
/// <returns>number of items in specified range</returns>
public static double COUNT(Worksheet ctrl, ReoGridRange range)
{
int count = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
count++;
return(true);
});
return(count);
}
public void DuplicateBorder()
{
SetUp();
var range = new ReoGridRange(4, 4, 4, 4);
grid.SetRangeBorder(range, ReoGridBorderPos.All, ReoGridBorderStyle.SolidBlack);
var subgrid = grid.GetPartialGrid(range);
grid[0, 4] = subgrid; // top
grid[4, 0] = subgrid; // left
grid[8, 4] = subgrid; // bottom
grid[4, 8] = subgrid; // right
AssertTrue(grid._Debug_Validate_All());
// top
ReoGridRangeBorderInfo border = grid.GetRangeBorder(new ReoGridRange(0, 4, 4, 4));
AssertEquals(border.Top.Style, BorderLineStyle.Solid);
AssertEquals(border.Left.Style, BorderLineStyle.Solid);
AssertEquals(border.Right.Style, BorderLineStyle.Solid);
AssertEquals(border.Bottom.Style, BorderLineStyle.Solid);
AssertEquals(border.NonUniformPos, ReoGridBorderPos.None); // border at all positions are same
// left
border = grid.GetRangeBorder(new ReoGridRange(4, 0, 4, 4));
AssertEquals(border.Top.Style, BorderLineStyle.Solid);
AssertEquals(border.Left.Style, BorderLineStyle.Solid);
AssertEquals(border.Right.Style, BorderLineStyle.Solid);
AssertEquals(border.Bottom.Style, BorderLineStyle.Solid);
AssertEquals(border.NonUniformPos, ReoGridBorderPos.None); // border at all positions are same
// bottom
border = grid.GetRangeBorder(new ReoGridRange(8, 4, 4, 4));
AssertEquals(border.Top.Style, BorderLineStyle.Solid);
AssertEquals(border.Left.Style, BorderLineStyle.Solid);
AssertEquals(border.Right.Style, BorderLineStyle.Solid);
AssertEquals(border.Bottom.Style, BorderLineStyle.Solid);
AssertEquals(border.NonUniformPos, ReoGridBorderPos.None); // border at all positions are same
// right
border = grid.GetRangeBorder(new ReoGridRange(4, 8, 4, 4));
AssertEquals(border.Top.Style, BorderLineStyle.Solid);
AssertEquals(border.Left.Style, BorderLineStyle.Solid);
AssertEquals(border.Right.Style, BorderLineStyle.Solid);
AssertEquals(border.Bottom.Style, BorderLineStyle.Solid);
AssertEquals(border.NonUniformPos, ReoGridBorderPos.None); // border at all positions are same
}
public void BaseRange()
{
var r1 = new ReoGridRange(0, 0, 1, 1);
AssertEquals(r1.Row, 0);
AssertEquals(r1.Col, 0);
AssertEquals(r1.Row2, 0);
AssertEquals(r1.Col2, 0);
AssertEquals(r1.Rows, 1);
AssertEquals(r1.Cols, 1);
var r2 = new ReoGridRange(10, 20, 5, 5);
AssertEquals(r2.Row, 10);
AssertEquals(r2.Col, 20);
AssertEquals(r2.Row2, 14);
AssertEquals(r2.Col2, 24);
AssertEquals(r2.Rows, 5);
AssertEquals(r2.Cols, 5);
AssertTrue(r1 != r2);
AssertEquals(r2, new ReoGridRange(10, 20, 5, 5));
}
/// <summary>
/// Adds its arguments
/// </summary>
/// <param name="ctrl"></param>
/// <param name="range"></param>
/// <returns></returns>
public static double SUM(Worksheet ctrl, ReoGridRange range)
{
double val = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
var data = cell.InnerData;
if (data != null)
{
double cellValue;
if (RGUtility.ConvertCellData <double>(data, out cellValue))
{
val += cellValue;
}
}
return(true);
});
return(val);
}
/// <summary>
/// Constructor of ReusableActionGroup
/// </summary>
/// <param name="range">Range to be appiled this action group</param>
public RGReusableActionGroup(ReoGridRange range)
: base(range)
{
this.actions = new List<RGReusableAction>();
}
internal abstract RGReusableAction Clone(ReoGridRange range);
public void TestBorderSpan()
{
SetUp();
var range = new ReoGridRange(2, 2, 2, 5);
grid.MergeRange(range);
grid.SetRangeBorder(range, ReoGridBorderPos.Outline, ReoGridBorderStyle.SolidBlack);
grid.InsertCols(4, 2);
AssertTrue(grid._Debug_Validate_All());
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGStepRangeFontSizeAction(range, Enlarge);
}
/// <summary>
/// Create instance for SetRangeBorderAction with specified range and border styles.
/// </summary>
/// <param name="range">Range to be appiled this action</param>
/// <param name="pos">Position of range to set border</param>
/// <param name="style">Style of border</param>
public RGRemoveRangeBorderAction(ReoGridRange range, ReoGridBorderPos pos)
: base(range)
{
this.BorderPos = pos;
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGSetRowsHeightAction(range.Row, range.Rows, height);
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGMergeRangeAction(range);
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGRemoveColumnsAction(range.Col, range.Cols);
}
/// <summary>
/// Create instance for MergeRangeAction with specified range
/// </summary>
/// <param name="range">The range to be merged</param>
public RGMergeRangeAction(ReoGridRange range)
: base(range)
{
}
/// <summary>
/// Do this action
/// </summary>
public override void Do()
{
insertedRow = Row;
Grid.InsertRows(Row, Count);
Range = new ReoGridRange(Row, 0, Count, Grid.ColCount);
}
/// <summary>
/// Do this action
/// </summary>
public override void Do()
{
insertedCol = Col;
Grid.InsertCols(Col, Count);
Range = new ReoGridRange(0, Col, Grid.RowCount, Count);
}
/// <summary>
/// Constructor of ReusableActionGroup
/// </summary>
/// <param name="range">Range to be appiled this action group</param>
/// <param name="actions">Action list to be performed together</param>
public RGReusableActionGroup(ReoGridRange range, List<RGReusableAction> actions)
: base(range)
{
this.actions = actions;
}
public static double Count(ReoGridControl ctrl, ReoGridRange range)
{
int count = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
count++;
return true;
});
return count;
}
/// <summary>
/// Create cloned reusable action group from this action group
/// </summary>
/// <param name="range">Specified new range to apply this action group</param>
/// <returns>New reusable action group cloned from this action group</returns>
internal override RGReusableAction Clone(ReoGridRange range)
{
List<RGReusableAction> clonedActions = new List<RGReusableAction>();
foreach (RGReusableAction action in actions)
{
clonedActions.Add(action.Clone(range));
}
return new RGReusableActionGroup(range, clonedActions);
}
bool _Debug_Auto_Validate_All(ReoGridRange range)
{
return _Debug_Validate_All(range);
}
public object Evaluate(ReoGridCell cell, ICompiledFormula cformula)
{
var grid = this.Workbook;
if (grid == null)
{
return(null);
}
var formulaContext = (ReoScriptCompiledFormula)cformula;
//var cell = formulaContext.Cell;
var formula = formulaContext.Formula;
List <ReferenceRange> referencedRanges = formulaContext.ReferencedCellOrRanges as List <ReferenceRange>;
if (referencedRanges == null)
{
formulaContext.ReferencedCellOrRanges = referencedRanges = new List <ReferenceRange>();
}
else
{
referencedRanges.Clear();
}
// todo: improve: only create script context once
// when set data to a range
var ctx = grid.Srm.CreateContext();
// create an global variable getter
ctx.ExternalVariableGetter = (id) =>
{
#if FORMULA_CELL_INSTANCE_REF
if (id.StartsWith("$"))
{
var address = id.Substring(1);
if (ReoGridPos.IsValidAddress(address))
{
var pos = new ReoGridPos(address);
return(new RSCellObject(this, pos, cells[pos.Row, pos.Col]));
}
else
{
return(null);
}
}
else
#endif // FORMULA_CELL_INSTANCE_REF
if (ReoGridPos.IsValidAddress(id))
{
var pos = new ReoGridPos(id);
referencedRanges.Add(new ReferenceRange(grid, pos));
var cell = grid.GetCell(pos);
return(cell == null ? 0 : cell.InnerData);
}
else
{
NamedRange range = grid.GetNamedRange(id);
if (range != null)
{
referencedRanges.Add(range);
var referencedCell = grid.GetCell(range.StartPos);
return((referencedCell == null || referencedCell.InnerData == null) ? 0 : referencedCell.InnerData);
}
else
{
return(null);
}
}
};
try
{
// preprocess range syntax
formula = RGUtility.RangeReferenceRegex.Replace(formula, (m) =>
{
if (m.Groups["to_col"].Length > 0 && m.Groups["to_row"].Length > 0 &&
m.Groups["from_col"].Length > 0 && m.Groups["from_row"].Length > 0)
{
// range
int fromRow = -1;
if (!int.TryParse(m.Groups["from_row"].Value, out fromRow))
{
return("null");
}
fromRow--;
int toRow = -1;
if (!int.TryParse(m.Groups["to_row"].Value, out toRow))
{
return("null");
}
toRow--;
int fromCol = RGUtility.GetNumberOfChar(m.Groups["from_col"].Value);
int toCol = RGUtility.GetNumberOfChar(m.Groups["to_col"].Value);
if (fromRow < 0)
{
fromRow = 0;
}
if (fromCol < 0)
{
fromCol = 0;
}
if (toRow > grid.RowCount - 1)
{
toRow = grid.RowCount - 1;
}
if (toCol > grid.RowCount - 1)
{
toCol = grid.ColumnCount - 1;
}
ReoGridRange range = new ReoGridRange(fromRow, fromCol, toRow - fromRow + 1, toCol - fromCol + 1);
referencedRanges.Add(new ReferenceRange(grid, range));
return(string.Format("new Range({0},{1},{2},{3})", range.Row, range.Col, range.Rows, range.Cols));
}
else
{
return(m.Value);
}
});
return(grid.Srm.CalcExpression(formula, ctx));
}
catch (ReoScriptException ex)
{
Logger.Log("formula", string.Format("error to evaluate formula: ", ex.Message));
throw new FormulaEvalutionException(ex, "#ERR: " + ex.Message);
}
}
bool _Debug_Validate_All(bool showSuccessMsg, ReoGridRange range)
{
bool rs = _Debug_Validate_BorderSpan(showSuccessMsg);
if (rs) rs = _Debug_Validate_Merged_Cell(showSuccessMsg);
if (rs) rs = _Debug_Validate_Unmerged_Range(showSuccessMsg, range);
return rs;
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGRemoveRangeBorderAction(range, BorderPos);
}
/// <summary>
/// Create instance for this action with specified range and enlarge flag.
/// </summary>
/// <param name="range">Specified range to apply this action</param>
/// <param name="enlarge">True to set text larger, false to set smaller</param>
public RGStepRangeFontSizeAction(ReoGridRange range, bool enlarge)
: base(range)
{
this.Enlarge = enlarge;
}
public static double Avg(ReoGridControl ctrl, ReoGridRange range)
{
double val = 0;
int count = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
if (cell != null && ScriptRunningMachine.IsNumber(cell.Data))
{
val += ScriptRunningMachine.GetDoubleValue(cell.Data);
count++;
}
return true;
});
return val / count;
}
/// <summary>
/// Create instance for action to remove style from specified range.
/// </summary>
/// <param name="range">Styles from this specified range to be removed</param>
/// <param name="flag">Style flag indicates what type of style should be removed</param>
public RGRemoveRangeStyleAction(ReoGridRange range, PlainStyleFlag flag)
: base(range)
{
this.flag = flag;
}
bool _Debug_Validate_All(ReoGridRange range)
{
return _Debug_Validate_All(false, range);
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGRemoveRangeStyleAction(Range, flag);
}
bool _Debug_Validate_Unmerged_Range(bool showSuccessMsg, ReoGridRange range)
{
bool rs = grid._Debug_Validate_Unmerged_Range(range);
if (rs)
{
if (showSuccessMsg) MessageBox.Show("Unmerged range validation ok.");
}
else
{
ShowError("Unmerged range validation failed.");
if (!showDebugInfoToolStripMenuItem.Checked)
{
showDebugInfoToolStripMenuItem.PerformClick();
}
}
return rs;
}
internal override RGReusableAction Clone(ReoGridRange range)
{
return new RGRemoveRowsAction(range.Row, range.Rows);
}
void RandomlyMerged()
{
SetUp();
grid.Resize(20, 20);
grid.SetRowsHeight(0, 10, 10);
grid.SetColsWidth(0, 10, 10);
var rand = new Random();
for (int i = 0; i < 20; )
{
int row = rand.Next(16);
int col = rand.Next(16);
int rows = 2 + rand.Next(2);
int cols = 2 + rand.Next(2);
var range = new ReoGridRange(row, col, rows, cols);
if (grid.HasIntersectedMergingRange(range))
continue;
else
i++;
grid.MergeRange(range);
grid._Debug_Validate_All();
}
}
/// <summary>
/// Constructor of RGReusableAction
/// </summary>
/// <param name="range">Range to be applied this action</param>
public RGReusableAction(ReoGridRange range)
{
this.Range = range;
}
