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 IList <SpreadChartBase> GetChartShapeAffectedCellChanged(Worksheet worksheet, int row, int column, int rowCount, int columnCount)
{
List <SpreadChartBase> list = new List <SpreadChartBase>();
List <SpreadChartBase> list2 = new List <SpreadChartBase>();
foreach (SpreadChart chart in worksheet.Charts)
{
list.Add(chart);
}
CellRange range = new CellRange(row, column, rowCount, columnCount);
foreach (SpreadChartBase base2 in list)
{
SheetCellRange[] rangeArray = null;
if (base2 is SpreadChart)
{
rangeArray = SheetCellRangeUtility.ExtractAllExternalReference(worksheet, (base2 as SpreadChart).Formula);
}
if ((rangeArray != null) && (rangeArray != null))
{
foreach (SheetCellRange range2 in rangeArray)
{
if ((range2.Sheet == worksheet) && range.Intersects(range2.Row, range2.Column, range2.RowCount, range2.ColumnCount))
{
list2.Add(base2);
break;
}
}
}
}
return((IList <SpreadChartBase>)list2);
}
public static IList <SpreadChartBase> GetChartShapeAffectedBySheetTable(Worksheet worksheet, SheetTable table)
{
if ((worksheet == null) || (table == null))
{
return(null);
}
List <SpreadChartBase> list = new List <SpreadChartBase>();
List <SpreadChartBase> list2 = new List <SpreadChartBase>();
foreach (SpreadChart chart in worksheet.Charts)
{
list.Add(chart);
}
foreach (SpreadChartBase base2 in list)
{
SheetCellRange[] rangeArray = null;
if (base2 is SpreadChart)
{
rangeArray = SheetCellRangeUtility.ExtractAllExternalReference(worksheet, (base2 as SpreadChart).Formula);
}
if (rangeArray != null)
{
foreach (SheetCellRange range in rangeArray)
{
if ((range.Sheet == worksheet) && table.DataRange.Intersects(range.Row, range.Column, range.RowCount, range.ColumnCount))
{
list2.Add(base2);
break;
}
}
}
}
return((IList <SpreadChartBase>)list2);
}
public static SheetCellRange ExtractRange(ICalcEvaluator sheet, CalcExpression value)
{
if ((value != null) && !(value is CalcConstantExpression))
{
if ((value is CalcExternalCellExpression) || (value is CalcExternalRangeExpression))
{
CalcExternalCellExpression expression = value as CalcExternalCellExpression;
if (expression != null)
{
return(new SheetCellRange(expression.Source as Worksheet, expression.Row, expression.Column, 1, 1));
}
CalcExternalRangeExpression expression2 = value as CalcExternalRangeExpression;
if (expression2 != null)
{
return(new SheetCellRange(expression2.Source as Worksheet, expression2.StartRow, expression2.StartColumn, (expression2.EndRow - expression2.StartRow) + 1, (expression2.EndColumn - expression2.StartColumn) + 1));
}
}
else if (value is CalcExternalNameExpression)
{
SheetCellRange[] rangeArray = SheetCellRangeUtility.ExtractAllExternalReference(sheet, value);
if ((rangeArray != null) && (rangeArray.Length > 0))
{
return(rangeArray[0]);
}
}
}
return(null);
}
public static IList <SpreadChartBase> GetChartShapeAffectedByRowRangeGroup(Worksheet worksheet)
{
List <SpreadChartBase> list = new List <SpreadChartBase>();
List <SpreadChartBase> list2 = new List <SpreadChartBase>();
if (worksheet.RowRangeGroup != null)
{
foreach (SpreadChart chart in worksheet.Charts)
{
list.Add(chart);
}
foreach (SpreadChartBase base2 in list)
{
SheetCellRange[] rangeArray = null;
if (base2 is SpreadChart)
{
rangeArray = SheetCellRangeUtility.ExtractAllExternalReference(worksheet, (base2 as SpreadChart).Formula);
}
if (rangeArray != null)
{
bool flag = false;
foreach (SheetCellRange range in rangeArray)
{
if (range.Sheet == worksheet)
{
for (int i = range.Row; i < (range.Row + range.RowCount); i++)
{
if (worksheet.RowRangeGroup.GetLevel(i) >= 0)
{
list2.Add(base2);
flag = true;
break;
}
}
}
if (flag)
{
break;
}
}
}
}
}
return((IList <SpreadChartBase>)list2);
}
