/// <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);
}
void grid_BeforePaste(object sender, BeforeRangeOperationEventArgs 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 RangePosition(worksheet.SelectionRange.Row,
worksheet.SelectionRange.Col,
data.GetLength(0), data.GetLength(1));
worksheet.SetRangeData(applyRange, data);
worksheet.SetRangeStyles(applyRange, new WorksheetRangeStyle
{
Flag = PlainStyleFlag.BackAll,
BackColor = Color.Yellow,
});
}
}
}
public void ParseTabbedString()
{
worksheet[10, 5] = RGUtility.ParseTabbedString("A\tB\tC\t");
AssertEquals(worksheet[10, 5], "A");
AssertEquals(worksheet[10, 6], "B");
AssertEquals(worksheet[10, 7], "C");
worksheet[10, 10] = RGUtility.ParseTabbedString("A\t\tC\t");
AssertEquals(worksheet[10, 10], "A");
AssertEquals(worksheet[10, 11], "");
AssertEquals(worksheet[10, 12], "C");
worksheet[10, 15] = RGUtility.ParseTabbedString("A\nB\nC\n");
AssertEquals(worksheet[10, 15], "A");
AssertEquals(worksheet[11, 15], "B");
AssertEquals(worksheet[12, 15], "C");
worksheet[10, 20] = RGUtility.ParseTabbedString("1\t\t3\n\t\nA\t\tC");
AssertEquals(worksheet.GetCellText(10, 20), "1");
AssertEquals(worksheet.GetCellText(10, 22), "3");
AssertEquals(worksheet.GetCellText(11, 20), "");
AssertEquals(worksheet.GetCellText(11, 22), "");
AssertEquals(worksheet.GetCellText(12, 20), "A");
AssertEquals(worksheet.GetCellText(12, 22), "C");
}
/// <summary>
/// Rounds a number to the nearest integer or to the nearest multiple of significance
/// </summary>
/// <param name="ctrl">instance of ReoGrid control</param>
/// <param name="args">function arguments</param>
/// <returns>nearest integer or to the nearest multiple of significance</returns>
public static double CEILING(Worksheet ctrl, object[] args)
{
if (args.Length < 1)
{
return(double.NaN);
}
double input = RGUtility.ConvertCellData <double>(args[0]);
if (args.Length < 2)
{
return(Math.Ceiling(input));
}
double significance = RGUtility.ConvertCellData <double>(args[1]);
if ((input % significance) != 0)
{
return(((int)(input / significance) * significance) + significance);
}
else
{
return(Math.Round(input));
}
}
/// <summary>
/// Get the column filter by specified address code of column (A, TZ, etc.)
/// </summary>
/// <param name="columnCode">the alphabet of address code used to locate a column in spreadsheet</param>
/// <returns>instance of column filter, which contains the candidates list and selected items by user</returns>
public AutoColumnFilterBody this[string columnCode]
{
get
{
int index = RGUtility.GetNumberOfChar(columnCode);
return(this[index]);
}
}
/// <summary>
/// Create about dialog
/// </summary>
public AboutForm()
{
InitializeComponent();
lnkHP.Click += (s, e) => RGUtility.OpenFileOrLink(lnkHP.Text);
labVersion.Text = "version " + ProductVersion.ToString();
textBox2.Text = unvell.ReoGrid.Properties.Resources.EULA_EN;
}
/// <summary>
/// Manually fire the hyperlink click event.
/// </summary>
public void PerformClick()
{
if (AutoNavigate && !string.IsNullOrWhiteSpace(LinkURL))
{
try
{
RGUtility.OpenFileOrLink(LinkURL);
}
catch { }
}
Click?.Invoke(this, null);
}
void txtAddress_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
string id = addressBox.Text;
// avoid to directly use trim, it will create new string even nothing to be trimmed
if (id.StartsWith(" ") || id.EndsWith(" "))
{
id = id.Trim();
}
if (RangePosition.IsValidAddress(id))
{
this.worksheet.SelectionRange = new RangePosition(id);
workbook.Focus();
}
else if (RGUtility.IsValidName(id))
{
var refRange = this.worksheet.GetNamedRange(id);
if (refRange != null)
{
this.worksheet.SelectionRange = refRange;
workbook.Focus();
}
else
{
try
{
this.worksheet.DefineNamedRange(id, this.worksheet.SelectionRange);
workbook.Focus();
}
catch (NamedRangeAlreadyDefinedException)
{
// should be not reached
MessageBox.Show("Another range with same name does already exist.");
}
}
}
}
else if (e.KeyCode == Keys.Down)
{
PushDown();
}
else if (e.KeyCode == Keys.Escape)
{
workbook.Focus();
}
}
private void MakeButton()
{
var worksheet = reoGridControl.Worksheets[0];
// L16:M16範囲を結合
worksheet.Ranges["L17:M17"].Merge();
// セルボタンを作成
var button = new CellTypes.ButtonCell("Excelで開く");
// クリックした場合 Excel で「請求書」を表示する
button.Click += (s, e) => RGUtility.OpenFileOrLink(filename);
// セルボタンをワークシートに置く
worksheet["L17"] = button;
}
/// <summary>
/// Manually fire the hyperlink click event.
/// </summary>
public void PerformClick()
{
if (AutoNavigate && !string.IsNullOrWhiteSpace(LinkURL))
{
try
{
RGUtility.OpenFileOrLink(LinkURL);
}
catch (Exception ex)
{
//MessageBox.Show("Error to open link: " + ex.Message);
this.Cell?.Worksheet?.NotifyExceptionHappen(ex);
}
}
Click?.Invoke(this, null);
}
public InvoiceExcelDocDemo()
{
InitializeComponent();
var filename = "_Templates\\Excel\\simple-invoice.xlsx";
// load tepmlate from Excel file.
// https://reogrid.net/document/excel-file-format
//
reoGridControl.Load(filename);
var worksheet = reoGridControl.Worksheets[0];
worksheet.Ranges["K16:L16"].Merge();
var button = new CellTypes.ButtonCell("Open in Excel");
button.Click += (s, e) => RGUtility.OpenFileOrLink(filename);
worksheet["K16"] = button;
}
/// <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);
}
private void reportBugToolStripMenuItem_Click(object sender, EventArgs e)
{
RGUtility.OpenFileOrLink("http://forum.reogrid.net/");
}
public BuiltInTypesDemo()
{
InitializeComponent();
this.worksheet = grid.CurrentWorksheet;
// set default sheet style
worksheet.SetRangeStyles(RangePosition.EntireRange, new WorksheetRangeStyle
{
Flag = PlainStyleFlag.FontName | PlainStyleFlag.VerticalAlign,
FontName = "Arial",
VAlign = ReoGridVerAlign.Middle,
});
worksheet.SetSettings(WorksheetSettings.View_ShowGridLine |
WorksheetSettings.Edit_DragSelectionToMoveCells, false);
worksheet.SelectionMode = WorksheetSelectionMode.Cell;
worksheet.SelectionStyle = WorksheetSelectionStyle.FocusRect;
var middleStyle = new WorksheetRangeStyle
{
Flag = PlainStyleFlag.Padding | PlainStyleFlag.HorizontalAlign,
Padding = new PaddingValue(2),
HAlign = ReoGridHorAlign.Center,
};
var grayTextStyle = new WorksheetRangeStyle
{
Flag = PlainStyleFlag.TextColor,
TextColor = Color.DimGray
};
worksheet.MergeRange(1, 1, 1, 6);
worksheet.SetRangeStyles(1, 1, 1, 6, new WorksheetRangeStyle
{
Flag = PlainStyleFlag.TextColor | PlainStyleFlag.FontSize,
TextColor = Color.DarkGreen,
FontSize = 18,
});
worksheet[1, 1] = "Built-in Cell Bodies";
worksheet.SetColumnsWidth(1, 1, 100);
worksheet.SetColumnsWidth(2, 1, 30);
worksheet.SetColumnsWidth(3, 1, 100);
worksheet.SetColumnsWidth(6, 2, 65);
// button
worksheet.MergeRange(3, 2, 1, 2);
var btn = new ButtonCell("Hello");
worksheet[3, 1] = new object[] { "Button: ", btn };
btn.Click += (s, e) => ShowText("Button clicked.");
// link
worksheet.MergeRange(5, 2, 1, 3);
var link = new HyperlinkCell("http://www.google.com")
{
AutoNavigate = false
};
worksheet[5, 1] = new object[] { "Hyperlink", link };
link.Click += (s, e) =>
{
try
{
RGUtility.OpenFileOrLink(worksheet.GetCellText(5, 2));
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
};
// checkbox
var checkbox = new CheckBoxCell();
worksheet.SetRangeStyles(7, 2, 1, 1, middleStyle);
worksheet.SetRangeStyles(8, 2, 1, 1, grayTextStyle);
worksheet[7, 1] = new object[] { "Check box", checkbox, "Auto destroy after 5 minutes." };
worksheet[8, 2] = "(Keyboard is also supported to change the status of control)";
checkbox.CheckChanged += (s, e) => ShowText("Check box switch to " + checkbox.IsChecked.ToString());
// radio & radio group
worksheet[10, 1] = "Radio Button";
worksheet.SetRangeStyles(10, 2, 3, 1, middleStyle);
var radioGroup = new RadioButtonGroup();
worksheet[10, 2] = new object[, ] {
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "Apple" },
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "Orange" },
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "Banana" }
};
radioGroup.RadioButtons.ForEach(rb => rb.CheckChanged += (s, e) =>
ShowText("Radio button selected: " + worksheet[rb.Cell.Row, rb.Cell.Column + 1]));
worksheet[10, 2] = true;
worksheet[13, 2] = "(By adding radio buttons into same RadioGroup to make them toggle each other automatically)";
worksheet.SetRangeStyles(13, 2, 1, 1, grayTextStyle);
// dropdown
worksheet.MergeRange(15, 2, 1, 3);
var dropdown = new DropdownListCell("Apple", "Orange", "Banana", "Pear", "Pumpkin", "Cherry", "Coconut");
worksheet[15, 1] = new object[] { "Dropdown", dropdown };
worksheet.SetRangeBorders(15, 2, 1, 3, BorderPositions.Outside, RangeBorderStyle.GraySolid);
dropdown.SelectedItemChanged += (s, e) => ShowText("Dropdown list selected: " + dropdown.SelectedItem);
// image
worksheet.MergeRange(2, 6, 5, 2);
worksheet[2, 6] = new ImageCell(Resources.computer_laptop);
// information cell
worksheet.SetRangeBorders(19, 0, 1, 10, BorderPositions.Top, RangeBorderStyle.GraySolid);
}
private void reportBugToolStripMenuItem_Click(object sender, EventArgs e)
{
RGUtility.OpenFileOrLink("https://github.com/unvell/ReoGrid/issues");
}
private void downloadToolStripMenuItem_Click(object sender, EventArgs e)
{
RGUtility.OpenFileOrLink("https://reogrid.net/jp/download");
}
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);
}
}
private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
{
RGUtility.OpenFileOrLink("https://reogrid.net/about");
}
public BuiltInCellTypesDemo()
{
InitializeComponent();
this.worksheet = grid.CurrentWorksheet;
// ワークシート全体のスタイルを設定
worksheet.SetRangeStyles(RangePosition.EntireRange, new WorksheetRangeStyle
{
// フォント名と縦位置の揃え方を設定
Flag = PlainStyleFlag.FontName | PlainStyleFlag.VerticalAlign,
FontName = "Arial",
VAlign = ReoGridVerAlign.Middle,
});
// グリッドラインを非表示
worksheet.SetSettings(WorksheetSettings.View_ShowGridLine | WorksheetSettings.Edit_DragSelectionToMoveCells, false);
// 選択モードを単一セルのみに設定
worksheet.SelectionMode = WorksheetSelectionMode.Cell;
// 選択スタイルを Focus に設定
worksheet.SelectionStyle = WorksheetSelectionStyle.FocusRect;
var grayTextStyle = new WorksheetRangeStyle
{
Flag = PlainStyleFlag.TextColor,
TextColor = Color.DimGray
};
worksheet.MergeRange(1, 1, 1, 6); // B2:G2
worksheet.SetRangeStyles(1, 1, 1, 6, new WorksheetRangeStyle
{
Flag = PlainStyleFlag.TextColor | PlainStyleFlag.FontSize,
TextColor = Color.DarkGreen,
FontSize = 18,
});
worksheet[1, 1] = "内蔵セル型";
// 列幅を調整
worksheet.SetColumnsWidth(1, 1, 100);
worksheet.SetColumnsWidth(2, 1, 30);
worksheet.SetColumnsWidth(3, 1, 100);
worksheet.SetColumnsWidth(6, 2, 65);
// ボタン
worksheet.MergeRange(3, 2, 1, 2); // C4:D4
var btn = new ButtonCell("ボタン");
worksheet[3, 1] = new object[] { "ボタン: ", btn };
btn.Click += (s, e) => ShowText("ボタンがクリックされた。");
// リンク
worksheet.MergeRange(5, 2, 1, 3); // C6:E6
var link = new HyperlinkCell("https://www.google.com")
{
AutoNavigate = false
};
worksheet[5, 1] = new object[] { "ハイパーリンク", link };
link.Click += (s, e) => RGUtility.OpenFileOrLink(worksheet.GetCellText(5, 2));
// チェックボックス
var checkbox = new CheckBoxCell();
worksheet.SetRangeStyles(8, 2, 1, 1, grayTextStyle); // C9:C9
worksheet[7, 1] = new object[] { "チェックボックス", checkbox, "テキストは別のセルを利用します" };
worksheet[8, 2] = "(セル型の編集はキーボードでも変更できます)";
checkbox.CheckChanged += (s, e) => ShowText("チェックステータスが変更された： " + checkbox.IsChecked.ToString());
// ラジオボタン
worksheet[10, 1] = "Radio Button"; // B11
var radioGroup = new RadioButtonGroup(); // ラジオボタングループを作成
worksheet[10, 2] = new object[, ] { // C11
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "リンゴ" },
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "ミカン" },
{ new RadioButtonCell()
{
RadioGroup = radioGroup
}, "バナナ" }
};
radioGroup.RadioButtons.ForEach(rb => rb.CheckChanged += (s, e) =>
ShowText("ラジオボタンのステータスが変更された：" + worksheet[rb.Cell.Row, rb.Cell.Column + 1]));
worksheet[10, 2] = true;
worksheet[13, 2] = "(RadioGroup に複数のラジオボタンを追加するとお互いに切り替えることができます)";
worksheet.SetRangeStyles(13, 2, 1, 1, grayTextStyle); //C14
// ドロップダウンリスト
worksheet.MergeRange(15, 2, 1, 3); // C16:E16
var dropdown = new DropdownListCell("リンゴ", "ミカン", "バナナ", "ナシ", "カボチャ", "チェリー", "ココナッツ");
worksheet[15, 1] = new object[] { "ドロップダウン", dropdown };
worksheet.SetRangeBorders(15, 2, 1, 3, BorderPositions.Outside, RangeBorderStyle.GraySolid);
dropdown.SelectedItemChanged += (s, e) => ShowText("ドロップダウンの項目が選択された：" + dropdown.SelectedItem);
// イメージ
worksheet.MergeRange(2, 6, 5, 2); // G3:H7
worksheet[2, 6] = new ImageCell(Resources.computer_laptop);
// イベント情報
worksheet.SetRangeBorders("A20:J20", BorderPositions.Top, RangeBorderStyle.GraySolid);
}
private void projectHomepageToolStripMenuItem_Click(object sender, EventArgs e)
{
RGUtility.OpenFileOrLink("https://reogrid.net/");
}
internal static void SetupBuiltinFunctions(Worksheet grid)
{
#if EX_SCRIPT
var srm = grid.Srm;
srm["SUM"] = new NativeFunctionObject("SUM", (ctx, owner, args) => SUM(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["AVERAGE"] = new NativeFunctionObject("AVERAGE", (ctx, owner, args) => AVERAGE(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["COUNT"] = new NativeFunctionObject("COUNT", (ctx, owner, args) => COUNT(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["CEILING"] = new NativeFunctionObject("CEILING", (ctx, owner, args) => CEILING(grid, args));
#region ROW & COLUMN
srm["ROW"] = new NativeFunctionObject("ROW", (ctx, owner, args) =>
{
var cell = ctx["__cell__"] as ReoGridCell;
if (cell == null)
{
throw new FormulaEvalutionException("ROW function must be used in an evaluation of formula belonging to a cell.");
}
return(cell.InternalRow);
});
srm["COLUMN"] = new NativeFunctionObject("COLUMN", (ctx, owner, args) =>
{
var cell = ctx["__cell__"] as ReoGridCell;
if (cell == null)
{
throw new FormulaEvalutionException("COLUMN function must be used in an evaluation of formula belonging to a cell.");
}
return(cell.InternalCol);
});
#endregion // ROW & COLUMN
#region ADDRESS
srm["ADDRESS"] = new NativeFunctionObject("ADDRESS", (ctx, owner, args) =>
{
if (args.Length < 2)
{
throw new FormulaEvalutionException("ADDRESS function needs at least 2 arguments.");
}
int row = RGUtility.ConvertCellData <int>(args[0]);
int col = RGUtility.ConvertCellData <int>(args[1]);
int absNum = 1;
if (args.Length > 2)
{
absNum = RGUtility.ConvertCellData <int>(args[2]);
}
// Excel standard: false
bool a1style = true;
if (args.Length > 3)
{
a1style = RGUtility.ConvertCellData <bool>(args[3]);
}
return(RGUtility.ToAddress(row, col, absNum, a1style));
});
#endregion // ADDRESS
srm["ROUND"] = new NativeFunctionObject("ROUND", (ctx, owner, args) =>
{
if (args.Length < 1)
{
throw new FormulaEvalutionException("ROUND function needs at least 2 arguments.");
}
int digit = 0;
if (args.Length > 1)
{
digit = RGUtility.ConvertCellData <int>(args[1]);
}
double value = RGUtility.ConvertCellData <double>(args[0]);
return(Math.Round(value, digit));
});
srm["RANGE"] = new NativeFunctionObject("RANGE", (ctx, owner, args) =>
{
if (args.Length < 2)
{
throw new FormulaEvalutionException("RANGE function needs at least 2 arguments.");
}
int row = RGUtility.ConvertCellData <int>(args[0]);
int col = RGUtility.ConvertCellData <int>(args[1]);
int rows = 1, cols = 1;
if (args.Length > 2)
{
rows = RGUtility.ConvertCellData <int>(args[2]);
}
if (args.Length > 3)
{
cols = RGUtility.ConvertCellData <int>(args[3]);
}
return(grid.FixRange(new ReoGridRange(row, col, rows, cols)));
});
srm["INDIRECT"] = new NativeFunctionObject("INDIRECT", (ctx, owner, args) =>
{
if (args.Length == 0)
{
throw new FormulaEvalutionException("INDIRECT function needs at least 1 arguments.");
}
var arg1 = args[0];
if (arg1 is string || arg1 is StringBuilder)
{
return(grid.GetCellData(Convert.ToString(arg1)));
}
else if (arg1 is ReoGridPos || arg1 is RSCellObject || arg1 is ReoGridCell)
{
return(grid.GetCellData(RSUtility.GetPosFromValue(grid, arg1)));
}
else
{
return(0);
}
});
#endif // EX_SCRIPT
}
