public static FormulaValue Average(Cell cell, FormulaValue[] args)
{
double val = 0, count = 0;
double data;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
ReferenceRange range = RangeFrom(cell, arg);
Worksheet worksheet = WorksheetFrom(cell, range);
worksheet.IterateCells(range.Position, (r, c, inCell) =>
{
if (CellUtility.TryGetNumberData(worksheet, inCell, out data))
{
val += data;
count++;
}
return(true);
});
break;
case FormulaValueType.Number:
val += (double)arg.value;
count++;
break;
case FormulaValueType.Nil:
continue;
}
}
return(count > 0 ? (val / count) : 0);
}
public static FormulaValue Sum(Cell cell, FormulaValue[] args)
{
double val = 0;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
if (cell == null || cell.Worksheet == null)
{
throw new FormulaTypeMismatchException(cell);
}
cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) =>
{
double data;
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
val += data;
}
return(true);
});
break;
case FormulaValueType.Number:
val += (double)arg.value;
break;
}
}
return(val);
}
private double GetCellsDifference(List <CellPosition> fromCells)
{
double diff = 1;
if (fromCells.Count > 1)
{
for (var i = 0; i < fromCells.Count - 1; i++)
{
var cell1 = Cells[fromCells[i]];
var cell2 = Cells[fromCells[i + 1]];
if (cell1 == null || cell2 == null)
{
continue;
}
double value1, value2;
if (CellUtility.TryGetNumberData(cell1, out value1) &&
CellUtility.TryGetNumberData(cell2, out value2))
{
if (i == 0)
{
diff = value2 - value1;
}
else
{
diff = (diff + (value2 - value1)) / 2;
}
}
}
}
return(diff);
}
public static FormulaValue Sum(Cell cell, FormulaValue[] args)
{
double val = 0;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
ReferenceRange range = RangeFrom(cell, arg);
Worksheet worksheet = WorksheetFrom(cell, range);
worksheet.IterateCells(range.Position, (r, c, inCell) =>
{
double data;
if (CellUtility.TryGetNumberData(worksheet, inCell, out data))
{
val += data;
}
return(true);
});
break;
case FormulaValueType.Number:
val += (double)arg.value;
break;
}
}
return(val);
}
// 統計を行う
private void btnCalcTotal_Click(object sender, System.EventArgs e)
{
var sheet = grid.CurrentWorksheet;
var range = new RangePosition("B2:E4");
double sumVal = 0, countVal = 0;
sheet.IterateCells(range, (r, c, inCell) =>
{
double data;
// 数値を取得
if (CellUtility.TryGetNumberData(inCell, out data))
{
sumVal += data;
countVal++;
}
// trueを返して反復を続ける
return(true);
});
// 計算結果を表示
sheet["F6:G6"] = new object[] { "合計(SUM)：", sumVal };
sheet["F7:G7"] = new object[] { "合計(COUNT)：", countVal };
}
public static FormulaValue Min(Cell cell, FormulaValue[] args)
{
double min = 0;
double data;
bool first = true;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
if (cell == null || cell.Worksheet == null)
{
throw new FormulaTypeMismatchException(cell);
}
cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) =>
{
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
if (first)
{
min = data;
first = false;
}
else if (min > data)
{
min = data;
}
}
return(true);
});
break;
case FormulaValueType.Number:
data = (double)arg.value;
if (first)
{
min = data;
first = false;
}
else if (min > data)
{
min = data;
}
break;
default:
return(null);
}
}
return(min);
}
/// <summary>
/// Try get number data from cell at specified position. If the data is string,
/// this method will try to convert the string into number value.
/// </summary>
/// <param name="row">Number of row of the cell to get data.</param>
/// <param name="col">Number of column of the cell to get data.</param>
/// <param name="val">Number data returned and converted from cell.</param>
/// <returns>True if data can be get and converted; Otherwise return false.</returns>
public bool TryGetNumberData(int row, int col, out double val)
{
var cell = this.cells[row, col];
if (cell == null)
{
val = 0;
return(false);
}
return(CellUtility.TryGetNumberData(cell.Data, out val));
}
public static FormulaValue Min(Cell cell, FormulaValue[] args)
{
double min = 0;
double data;
bool first = true;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
ReferenceRange range = RangeFrom(cell, arg);
Worksheet worksheet = WorksheetFrom(cell, range);
worksheet.IterateCells(range.Position, (r, c, inCell) =>
{
if (CellUtility.TryGetNumberData(worksheet, inCell, out data))
{
if (first)
{
min = data;
first = false;
}
else if (min > data)
{
min = data;
}
}
return(true);
});
break;
case FormulaValueType.Number:
data = (double)arg.value;
if (first)
{
min = data;
first = false;
}
else if (min > data)
{
min = data;
}
break;
default:
return(null);
}
}
return(min);
}
public static FormulaValue Count(Cell cell, FormulaValue[] args, bool includeEmpty = false)
{
double count = 0;
double data;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
if (cell == null || cell.Worksheet == null)
{
return(null);
}
cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) =>
{
if (includeEmpty)
{
if (inCell.InnerData != null)
{
count++;
}
}
else
{
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
count++;
}
}
return(true);
});
break;
case FormulaValueType.Number:
count++;
break;
case FormulaValueType.String:
if (includeEmpty && !string.IsNullOrEmpty((string)arg.value))
{
count++;
}
break;
}
}
return(count);
}
public static IAutoFillSectionEntry Create(object value)
{
if (value == null)
{
return new NullAutoFillSectionEntry();
}
if (CellUtility.TryGetNumberData(value, out var number))
{
return new NumericalAutoFillSectionEntry(number);
}
return new TextAutoFillSectionEntry(value.ToString());
}
public static FormulaValue Count(Cell cell, FormulaValue[] args, bool includeEmpty = false)
{
double count = 0;
double data;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
ReferenceRange range = RangeFrom(cell, arg);
Worksheet worksheet = WorksheetFrom(cell, range);
worksheet.IterateCells(range.Position, (r, c, inCell) =>
{
if (includeEmpty)
{
if (inCell.InnerData != null)
{
count++;
}
}
else
{
if (CellUtility.TryGetNumberData(worksheet, inCell, out data))
{
count++;
}
}
return(true);
});
break;
case FormulaValueType.Number:
count++;
break;
case FormulaValueType.String:
if (includeEmpty && !string.IsNullOrEmpty((string)arg.value))
{
count++;
}
break;
}
}
return(count);
}
public static FormulaValue Average(Cell cell, FormulaValue[] args)
{
double val = 0, count = 0;
double data;
foreach (var arg in args)
{
switch (arg.type)
{
case FormulaValueType.Range:
if (cell == null || cell.Worksheet == null)
{
return(null);
}
cell.Worksheet.IterateCells((RangePosition)arg.value, (r, c, inCell) =>
{
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
val += data;
count++;
}
return(true);
});
break;
case FormulaValueType.Number:
val += (double)arg.value;
count++;
break;
case FormulaValueType.Nil:
continue;
}
}
return(count > 0 ? (val / count) : 0);
}
private void AutoFillSerialCells(List <CellPosition> fromCells, List <CellPosition> toCells)
{
if (!fromCells.Any() || !toCells.Any())
{
return;
}
double diff = GetCellsDifference(fromCells);
for (var toCellIndex = 0; toCellIndex < toCells.Count; toCellIndex++)
{
var fromCellIndex = toCellIndex % fromCells.Count;
var fromCellPosition = fromCells[fromCellIndex];
var fromCell = Cells[fromCellPosition];
var toCellPosition = toCells[toCellIndex];
var toCell = Cells[toCellPosition];
if (fromCell == null)
{
continue;
}
if (!string.IsNullOrEmpty(fromCell.InnerFormula))
{
FormulaRefactor.Reuse(this, fromCellPosition, new RangePosition(toCellPosition));
}
else
{
double refValue = 0;
if (CellUtility.TryGetNumberData(fromCell.Data, out refValue))
{
toCell.Data = refValue + diff * (fromCells.Count + toCellIndex - fromCellIndex);
}
}
}
}
public void OnImportsSatisfied()
{
if (FormulaExtension.CustomFunctions.ContainsKey("qEuler"))
{
return;
}
FormulaExtension.CustomFunctions["qEuler"] = (cell, args) =>
{
if (args.Length < 2)
{
return(null);
}
var eulerangles = new float[3];
if (args[1] is RangePosition range)
{
var i = 0;
cell.Worksheet.IterateCells(range, (r, c, icell) =>
{
if (i >= 3)
{
return(false);
}
var isval = CellUtility.TryGetNumberData(icell, out var comp);
eulerangles[i] = isval ? (float)(comp * Math.PI * 2) : 0;
i++;
return(true);
});
}
else
{
if (args.Length < 4)
{
return(null);
}
for (int i = 0; i < 3; i++)
{
eulerangles[i] = (float)(double)args[i + 1];
}
}
var res = Quaternion.CreateFromYawPitchRoll(eulerangles[1], eulerangles[0], eulerangles[2]);
return(GetQuatComponent(args, res));
};
FormulaExtension.CustomFunctions["qEulerOrdered"] = (cell, args) =>
{
if (args.Length < 5)
{
return(null);
}
var eulerangles = new float[3];
if (args[4] is RangePosition range)
{
var i = 0;
cell.Worksheet.IterateCells(range, (r, c, icell) =>
{
if (i >= 3)
{
return(false);
}
var isval = CellUtility.TryGetNumberData(icell, out var comp);
eulerangles[i] = isval ? (float)(comp * Math.PI * 2) : 0;
i++;
return(true);
});
}
else
{
if (args.Length < 7)
{
return(null);
}
for (int i = 0; i < 3; i++)
{
eulerangles[i] = (float)(double)args[i + 4];
}
}
var res = Quaternion.Identity;
for (int i = 0; i < 3; i++)
{
var selcomp = (int)(double)args[i + 1] % 3;
switch (selcomp)
{
case 0: res = res * Quaternion.CreateFromYawPitchRoll(0, eulerangles[i], 0); break;
case 1: res = res * Quaternion.CreateFromYawPitchRoll(eulerangles[i], 0, 0); break;
case 2: res = res * Quaternion.CreateFromYawPitchRoll(0, 0, eulerangles[i]); break;
}
}
return(GetQuatComponent(args, res));
};
}
/// <summary>
/// Format cell
/// </summary>
/// <param name="cell">cell to be formatted</param>
/// <returns>Formatted text used to display as cell content</returns>
public string FormatCell(Cell cell)
{
object data = cell.InnerData;
bool isFormat = false;
double number;
DateTime value = baseStartDate;
string formattedText = null;
if (data is DateTime)
{
value = (DateTime)data;
isFormat = true;
}
else if (CellUtility.TryGetNumberData(data, out number))
{
try
{
var val = (double)number;
// Excel/Lotus 2/29/1900 bug
// original post: http://stackoverflow.com/questions/727466/how-do-i-convert-an-excel-serial-date-number-to-a-net-datetime
if (val > 59)
{
val -= 1;
}
value = BaseStartDate.AddDays(val - 1);
isFormat = true;
}
catch { }
}
else
{
string strdata = (data is string?(string)data : Convert.ToString(data));
double days = 0;
if (double.TryParse(strdata, out days))
{
try
{
value = value.AddDays(days);
isFormat = true;
}
catch { }
}
else
{
isFormat = (DateTime.TryParse(strdata, out value));
}
}
if (isFormat)
{
if (cell.InnerStyle.HAlign == ReoGridHorAlign.General)
{
cell.RenderHorAlign = ReoGridRenderHorAlign.Right;
}
CultureInfo culture = null;
string pattern = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
if (cell.DataFormatArgs != null && cell.DataFormatArgs is DateTimeFormatArgs)
{
DateTimeFormatArgs dargs = (DateTimeFormatArgs)cell.DataFormatArgs;
pattern = dargs.Format;
culture = (dargs.CultureName == null ||
string.Equals(dargs.CultureName, Thread.CurrentThread.CurrentCulture.Name))
? Thread.CurrentThread.CurrentCulture : new CultureInfo(dargs.CultureName);
}
else
{
culture = System.Threading.Thread.CurrentThread.CurrentCulture;
cell.DataFormatArgs = new DateTimeFormatArgs {
Format = pattern, CultureName = culture.Name
};
}
if (culture.Name.StartsWith("ja") && pattern.Contains("g"))
{
culture = new CultureInfo("ja-JP", true);
culture.DateTimeFormat.Calendar = new JapaneseCalendar();
}
try
{
switch (pattern)
{
case "d":
formattedText = value.Day.ToString();
break;
default:
formattedText = value.ToString(pattern, culture);
break;
}
}
catch
{
formattedText = Convert.ToString(value);
}
}
return(isFormat ? formattedText : null);
}
public static FormulaValue SumIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
if (args[1].type != FormulaValueType.String)
{
// todo: support not only string
return(null);
}
double val = 0;
double data;
RangePosition evalRange, sumRange = RangePosition.Empty;
if (args[0].type == FormulaValueType.Range)
{
evalRange = (RangePosition)args[0].value;
}
else
{
throw new FormulaTypeMismatchException(cell);
}
if (args.Length > 2)
{
if (args[2].type != FormulaValueType.Range)
{
throw new FormulaTypeMismatchException(cell);
}
sumRange = (RangePosition)(args[2].value);
}
string expStr = (string)args[1].value;
STValueNode leftExp = new STValueNode(null);
STNode compExp = Parser.ParseInterCompareExp(cell, expStr);
int rows = cell.Worksheet.Rows;
int cols = cell.Worksheet.Columns;
cell.Worksheet.IterateCells(evalRange, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = (Evaluator.Evaluate(cell, compExp));
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
if (sumRange.IsEmpty)
{
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
val += data;
}
}
else
{
int tr = sumRange.Row + r - evalRange.Row;
int tc = sumRange.Col + c - evalRange.Col;
if (tr < rows && tc < cols)
{
var sumCell = cell.Worksheet.GetCell(tr, tc);
if (sumCell != null && sumCell.InnerData != null &&
CellUtility.TryGetNumberData(sumCell.InnerData, out data))
{
val += data;
}
}
}
}
return(true);
});
return(val);
}
public static FormulaValue VLookup(Cell cell, FormulaValue[] args)
{
FormulaValue argTarget = args[0];
FormulaValue argRange = args[1];
FormulaValue argReturnIndex = args[2];
FormulaValue argExactMatch = args.Length > 3 ? args[3] : FormulaValue.Nil;
bool exactMatch = false;
//int matchValueInt = 0;
double matchValueDouble = 0;
string matchValueText = null;
bool matchNumber = true;
if (argRange.type != FormulaValueType.Range ||
argReturnIndex.type != FormulaValueType.Number)
{
return(null);
}
if (argExactMatch.type != FormulaValueType.Nil)
{
if (argExactMatch.type != FormulaValueType.Boolean)
{
return(null);
}
exactMatch = (bool)argExactMatch.value;
}
var searchRange = (RangePosition)argRange.value;
#region Match Value
switch (argTarget.type)
{
case FormulaValueType.Number:
matchValueDouble = (double)argTarget.value;
break;
case FormulaValueType.String:
matchValueText = (string)argTarget.value;
matchNumber = false;
break;
default:
return(null);
}
#endregion // Match Value
int selectColumnIndex = (int)Math.Ceiling((double)argReturnIndex.value);
int matchedRowIndex = searchRange.EndRow;
bool found = false;
if (exactMatch)
{
for (int r = searchRange.Row; r <= searchRange.EndRow; r++)
{
var cel = cell.Worksheet.GetCell(r, searchRange.Col);
if (cel != null)
{
if (matchNumber)
{
double cellNumVal = 0;
if (CellUtility.TryGetNumberData(cel.Data, out cellNumVal))
{
if (matchValueDouble == cellNumVal)
{
matchedRowIndex = r;
found = true;
break;
}
}
}
else
{
string cellText = cel.DisplayText;
if (string.Compare(matchValueText, cellText, true) == 0)
{
matchedRowIndex = r;
found = true;
break;
}
}
}
}
if (!found)
{
return(null);
}
}
else
{
matchedRowIndex = Data.ArrayHelper.QuickFind(searchRange.Row, searchRange.EndRow, i =>
{
var cel = cell.Worksheet.GetCell(i, searchRange.Col);
if (cel == null)
{
return(0);
}
if (matchNumber)
{
double cellNumVal = 0;
if (CellUtility.TryGetNumberData(cel.Data, out cellNumVal))
{
if (matchValueDouble < cellNumVal)
{
return(-1);
}
else if (matchValueDouble > cellNumVal)
{
return(1);
}
}
return(0);
}
else
{
string cellText = cel.DisplayText;
return(string.Compare(matchValueText, cellText, true));
}
});
}
return(Evaluator.CreateFormulaValue(cell.Worksheet.GetCellData(matchedRowIndex, searchRange.Col + selectColumnIndex - 1)));
}
public static FormulaValue AverageIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
if (args[1].type != FormulaValueType.String)
{
// todo: support not only string
return(null);
}
double val = 0, count = 0;
double data;
ReferenceRange evalRange = RangeFrom(cell, args[0]);
Worksheet evalWorksheet = WorksheetFrom(cell, evalRange);
ReferenceRange sumRange = null;
Worksheet sumWorksheet = null;
if (args.Length > 2)
{
sumRange = RangeFrom(cell, args[2]);
sumWorksheet = WorksheetFrom(cell, sumRange);
}
string expStr = (string)args[1].value;
STValueNode leftExp = new STValueNode(null);
STNode compExp = Parser.ParseInterCompareExp(cell, expStr);
int rows = cell.Worksheet.RowCount;
int cols = cell.Worksheet.ColumnCount;
evalWorksheet.IterateCells(evalRange.Position, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = Evaluator.Evaluate(cell, compExp);
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
if (sumRange == null)
{
if (CellUtility.TryGetNumberData(evalWorksheet, inCell, out data))
{
val += data;
count++;
}
}
else
{
int tr = sumRange.Row + r - evalRange.Row;
int tc = sumRange.Col + c - evalRange.Col;
if (tr < rows && tc < cols)
{
var sumCell = sumWorksheet.GetCell(tr, tc);
if (sumCell != null && sumCell.InnerData != null &&
CellUtility.TryGetNumberData(sumWorksheet, sumCell, out data))
{
val += data;
count++;
}
}
}
}
return(true);
});
return(count > 0 ? (FormulaValue)(val / count) : FormulaValue.Nil);
}
/// <summary>
/// Auto fill specified serial in range.
/// </summary>
/// <param name="fromRange">Range to read filling rules.</param>
/// <param name="toRange">Range to be filled.</param>
public void AutoFillSerial(RangePosition fromRange, RangePosition toRange)
{
fromRange = this.FixRange(fromRange);
toRange = this.FixRange(toRange);
#region Arguments Check
if (fromRange.IntersectWith(toRange))
{
throw new ArgumentException("fromRange and toRange cannot being intersected.");
}
if (toRange != CheckMergedRange(toRange))
{
throw new ArgumentException("cannot change a part of merged range.");
}
#endregion // Arguments Check
if (fromRange.Col == toRange.Col && fromRange.Cols == toRange.Cols)
{
#region Vertical Fill
for (int c = toRange.Col; c <= toRange.EndCol; c++)
{
double diff = 1;
#region Calc diff
if (fromRange.Rows > 1)
{
for (int r = fromRange.Row; r < fromRange.EndRow; r++)
{
double val1 = 0;
if (!this.TryGetNumberData(r, c, out val1))
{
break;
}
double val2;
if (this.TryGetNumberData(r + 1, c, out val2))
{
if (r == fromRange.Row)
{
diff = (val2 - val1);
}
else
{
diff = (diff + (val2 - val1)) / 2;
}
}
}
}
#endregion // Calc diff
#region Up to Down
for (int toRow = toRange.Row, index = 0; toRow < toRange.EndRow + 1; index++)
{
Cell toCell = this.cells[toRow, c];
if (toCell != null && toCell.Rowspan < 0)
{
toRow++;
continue;
}
CellPosition fromPos = new CellPosition(fromRange.Row + (index % fromRange.Rows), c);
Cell fromCell = this.cells[fromPos.Row, fromPos.Col];
if (fromCell == null || fromCell.Rowspan <= 0)
{
this[toRow, c] = null;
toRow++;
continue;
}
if (fromCell != null && !string.IsNullOrEmpty(fromCell.InnerFormula))
{
#region Fill Formula
FormulaRefactor.Reuse(this, fromPos, new RangePosition(toRow, c, 1, 1));
#endregion // Fill Formula
}
else
{
#region Fill Number
double refValue = 0;
if (CellUtility.TryGetNumberData(fromCell.Data, out refValue))
{
this[toRow, c] = refValue + diff * (toRow - fromPos.Row);
}
#endregion // Fill Number
}
toRow += Math.Max(fromCell.Rowspan, toCell == null ? 1 : toCell.Rowspan);
}
#endregion // Up to Down
}
#endregion // Vertical Fill
}
else if (fromRange.Row == toRange.Row && fromRange.Rows == toRange.Rows)
{
#region Horizontal Fill
for (int r = toRange.Row; r <= toRange.EndRow; r++)
{
double diff = 1;
#region Calc diff
if (fromRange.Cols > 1)
{
for (int c = fromRange.Col; r < fromRange.EndCol; c++)
{
double val1 = 0;
if (!this.TryGetNumberData(r, c, out val1))
{
break;
}
double val2;
if (this.TryGetNumberData(r, c + 1, out val2))
{
if (c == fromRange.Col)
{
diff = (val2 - val1);
}
else
{
diff = (diff + (val2 - val1)) / 2;
}
}
}
}
#endregion // Calc diff
#region Left to Right
for (int toCol = toRange.Col, index = 0; toCol < toRange.EndCol + 1; index++)
{
Cell toCell = this.cells[r, toCol];
if (toCell != null && toCell.Colspan < 0)
{
toCol++;
continue;
}
CellPosition fromPos = new CellPosition(r, fromRange.Col + (index % fromRange.Cols));
Cell fromCell = this.cells[fromPos.Row, fromPos.Col];
if (fromCell == null || fromCell.Colspan <= 0)
{
this[r, toCol] = null;
toCol++;
continue;
}
if (fromCell != null && !string.IsNullOrEmpty(fromCell.InnerFormula))
{
#region Fill Formula
FormulaRefactor.Reuse(this, fromPos, new RangePosition(r, toCol, 1, 1));
#endregion // Fill Formula
}
else
{
#region Fill Number
double refValue = 0;
if (CellUtility.TryGetNumberData(fromCell.Data, out refValue))
{
this[r, toCol] = refValue + diff * (toCol - fromPos.Col);
}
#endregion // Fill Number
}
toCol += Math.Max(fromCell.Colspan, toCell == null ? 1 : toCell.Colspan);
}
#endregion // Left to Right
}
#endregion // Vertical Fill
}
else
{
throw new InvalidOperationException("The fromRange and toRange must be having same number of rows or same number of columns.");
}
}
