public static SheetCellRange[] ValidateFormula(ICalcEvaluator evaluator, string formula, bool acceptConstantExpression = false)
{
if (string.IsNullOrEmpty(formula) || (evaluator == null))
{
return(null);
}
CalcExpression expression = FormulaUtility.Formula2Expression(evaluator, formula);
if (expression == null)
{
throw new ArgumentException("Formula is invalid.");
}
if ((expression is CalcConstantExpression) && acceptConstantExpression)
{
return(null);
}
SheetCellRange[] rangeArray = SheetCellRangeUtility.ExtractAllExternalReference(evaluator, formula);
if (rangeArray == null)
{
throw new ArgumentException("Formula is invalid.");
}
if (rangeArray.Length > 0)
{
Worksheet sheet = null;
foreach (SheetCellRange range in rangeArray)
{
if ((sheet != null) && (range.Sheet != sheet))
{
throw new ArgumentException("Formula is invalid.");
}
sheet = range.Sheet;
}
}
return(rangeArray);
}
public static CellRange ParseCellRange(Worksheet worksheet, string cellRange)
{
CalcExpression expression = FormulaUtility.Formula2Expression(worksheet, cellRange);
if (expression is CalcReferenceExpression)
{
return(GetRangeFromExpression(expression as CalcReferenceExpression));
}
if (expression is CalcNameExpression)
{
CalcNameExpression expression2 = expression as CalcNameExpression;
NameInfo customName = worksheet.GetCustomName(expression2.Name);
if ((customName != null) && (customName.Expression is CalcReferenceExpression))
{
return(GetRangeFromExpression(customName.Expression as CalcReferenceExpression));
}
}
else if (expression is CalcExternalNameExpression)
{
CalcExternalNameExpression expression3 = expression as CalcExternalNameExpression;
NameInfo info2 = worksheet.GetCustomName(expression3.Name);
if ((info2 != null) && (info2.Expression is CalcReferenceExpression))
{
return(GetRangeFromExpression(info2.Expression as CalcReferenceExpression));
}
}
return(null);
}
public static SheetCellRange[] ExtractAllExternalReference(ICalcEvaluator evaluator, CalcExpression expression)
{
if (expression != null)
{
List <CalcReferenceExpression> nodes = new List <CalcReferenceExpression>();
FormulaUtility.ExtractAllReferenceExpression(evaluator, expression, nodes);
if (nodes.Count > 0)
{
List <SheetCellRange> list2 = new List <SheetCellRange>();
foreach (CalcReferenceExpression expression2 in nodes)
{
if (expression2 is CalcExternalRangeExpression)
{
CalcExternalRangeExpression expression3 = expression2 as CalcExternalRangeExpression;
SheetCellRange range = new SheetCellRange(expression3.Source as Worksheet, expression3.StartRow, expression3.StartColumn, (expression3.EndRow - expression3.StartRow) + 1, (expression3.EndColumn - expression3.StartColumn) + 1);
list2.Add(range);
}
else if (expression2 is CalcExternalCellExpression)
{
CalcExternalCellExpression expression4 = expression2 as CalcExternalCellExpression;
SheetCellRange range2 = new SheetCellRange(expression4.Source as Worksheet, expression4.Row, expression4.Column, 1, 1);
list2.Add(range2);
}
else
{
return(null);
}
}
return(list2.ToArray());
}
}
return(null);
}
internal void UpdateTextReference()
{
SpreadChartBase chart = this.ChartBase;
if (chart != null)
{
this.TextReference = FormulaUtility.Formula2Expression(chart.Sheet, this.TextFormula);
}
}
public static string FormatCellRange(Worksheet worksheet, CellRange cellRange)
{
CalcReferenceExpression expressionFromCellRange = GetExpressionFromCellRange(worksheet, cellRange);
List <CalcExpression> expressions = new List <CalcExpression> {
expressionFromCellRange
};
return(FormulaUtility.BuildFormula(worksheet, expressions));
}
public static SheetCellRange Formula2SheetRange(ICalcEvaluator evaluator, string formula)
{
CalcExternalRangeExpression expression = FormulaUtility.Formula2Expression(evaluator, formula) as CalcExternalRangeExpression;
if (expression == null)
{
return(null);
}
return(new SheetCellRange(expression.Source as Worksheet, expression.StartRow, expression.StartColumn, (expression.EndRow - expression.StartRow) + 1, (expression.EndColumn - expression.StartColumn) + 1));
}
public static SheetCellRange[] ExtractAllExternalReference(ICalcEvaluator evaluator, string formula)
{
CalcExpression expression = FormulaUtility.Formula2Expression(evaluator, formula);
if (expression == null)
{
return(null);
}
return(ExtractAllExternalReference(evaluator, expression));
}
void UpdateDataProviders()
{
if (this._dataProviders != null)
{
foreach (ISeriesDataProvider provider in this._dataProviders)
{
if (provider != null)
{
if (provider is WorksheetSeriesDataProvider)
{
(provider as WorksheetSeriesDataProvider).Worksheet = null;
}
provider.DataChanged -= new EventHandler(this.DataProvider_DataChanged);
}
}
}
this._dataProviders = null;
if ((this.DataSeries != null) && (this.DataSeries.DataReference != null))
{
CalcReferenceExpression[] expressions = null;
FormulaUtility.ExtractAllReferenceExpression(this._dataSeries.Evaluator, this._dataSeries.DataReference, out expressions);
if ((expressions != null) && (expressions.Length > 0))
{
this._dataProviders = new List <ISeriesDataProvider>();
foreach (CalcReferenceExpression expression in expressions)
{
DataOrientation vertical = DataOrientation.Vertical;
if (this._dataSeries.DataOrientation.HasValue)
{
vertical = this._dataSeries.DataOrientation.Value;
}
else if (expression is CalcRangeExpression)
{
CalcRangeExpression expression2 = expression as CalcRangeExpression;
int num = (expression2.EndColumn - expression2.StartColumn) + 1;
int num2 = (expression2.EndRow - expression2.StartRow) + 1;
vertical = (num > num2) ? DataOrientation.Horizontal : DataOrientation.Vertical;
}
WorksheetSeriesDataProvider provider2 = this.CreateSeriesDataProvider(new DefaultDataSeries(expression, vertical, this._dataSeries.DisplayHiddenData, this._dataSeries.EmptyValueStyle, null));
this._dataProviders.Add(provider2);
if (expression is CalcExternalExpression)
{
provider2.Worksheet = (expression as CalcExternalExpression).Source as Worksheet;
}
provider2.DataChanged += new EventHandler(this.DataProvider_DataChanged);
}
}
}
}
internal override void OnRemoveRowRange(int row, int rowCount)
{
base.OnRemoveRowRange(row, rowCount);
this.XValueFormula = FormulaUtility.RemoveRowRange(base.Sheet, this.XValueFormula, row, rowCount);
}
internal override void OnRemoveColumnRange(int column, int columnCount)
{
base.OnRemoveColumnRange(column, columnCount);
this.XValueFormula = FormulaUtility.RemoveColumnRange(base.Sheet, this.XValueFormula, column, columnCount);
}
void UpdateXValuesReference(string xValueFormula)
{
this.XValuesReference = FormulaUtility.Formula2Expression(base.Sheet, xValueFormula);
}
internal override void OnAddColumnRange(int column, int columnCount)
{
base.OnAddRowRange(column, columnCount);
this.ZValueFormula = FormulaUtility.AddColumnRange(base.Sheet, this.ZValueFormula, column, columnCount);
}
void UpdateZValueReference(string zValueFormula)
{
this.ZValueReference = FormulaUtility.Formula2Expression(base.Sheet, zValueFormula);
}
internal override void OnAddRowRange(int row, int rowCount)
{
base.OnAddRowRange(row, rowCount);
this.SizeFormula = FormulaUtility.AddRowRange(base.Sheet, this.SizeFormula, row, rowCount);
}
void UpdateSizeReference(string sizeFormula)
{
this.SizeReference = FormulaUtility.Formula2Expression(base.Sheet, sizeFormula);
}