private static IEnumerable <ParseTreeNode> CellContainedInRanges(ParseTreeNode fqcellref, ParseTreeNode formula, Context CtxF)
{
var cell = new Location(fqcellref.ChildNodes[1].Print());
// Select all references and qualify them
var references = formula.GetReferenceNodes().Select(CtxF.Qualify).ToList();
// Check the different types of ranges
var ranges = formula.AllNodes().Where(reference => reference.MatchFunction(":"));
var rangesc = ranges.Where(range =>
{
var args = range.GetFunctionArguments().Select(ExcelFormulaParser.Print).ToList();
var start = new Location(args[0]);
var end = new Location(args[1]);
return(cell.Row >= start.Row && cell.Row <= end.Row &&
cell.Column >= start.Column && cell.Column <= end.Column);
});
var vranges = references.Where(reference =>
reference.ChildNodes[0].Is(GrammarNames.Prefix) &&
reference.ChildNodes[1].Is(GrammarNames.VerticalRange)
);
var vrangesc = vranges.Where(reference =>
{
var vrange = reference.ChildNodes[1];
var pieces = vrange.Print().Replace("$", "").Split(':');
return(cell.Column >= AuxConverter.ColToInt(pieces[0]) &&
cell.Column <= AuxConverter.ColToInt(pieces[1]));
});
var hranges = references.Where(reference =>
reference.ChildNodes[0].Is(GrammarNames.Prefix) &&
reference.ChildNodes[1].Is(GrammarNames.HorizontalRange)
);
var hrangesc = hranges.Where(reference =>
{
var hrange = reference.ChildNodes[1];
var pieces = hrange.Print().Replace("$", "").Split(':');
return(cell.Row >= (int.Parse(pieces[0]) - 1) && cell.Row <= (int.Parse(pieces[1]) - 1));
});
var combined = new[] { rangesc, vrangesc, hrangesc }.SelectMany(x => x);
return(combined);
}
public override void Refactor(ExcelRaw.Range applyto)
{
if (!CanRefactor(applyto))
{
throw new ArgumentException("Cannot refactor this range");
}
// Refactor + to SUM first so we can focus on that case
if (_opToAggregate.CanRefactor(applyto))
{
_opToAggregate.Refactor(applyto);
}
var node = Helper.Parse(applyto).SkipToRelevant(false);
var fname = node.GetFunction();
var fargs = node.GetFunctionArguments();
PrefixInfo prefix;
bool columnEqual;
List <int> summedColumns;
bool rowEqual;
List <int> summedRows;
if (!checkRowAndColumns(node, out prefix, out columnEqual, out summedColumns, out rowEqual, out summedRows))
{
throw new ArgumentException("All references cells must be in the same row or column");
}
summedColumns.Sort();
summedRows.Sort();
var summedColumnsSet = new HashSet <int>(summedColumns);
var summedRowsSet = new HashSet <int>(summedRows);
if (!columnEqual)
{
throw new NotImplementedException("Cant do this for rows yet");
}
ExcelRaw.Range summedRange = null;
ExcelRaw.Worksheet worksheet = null;
ExcelRaw.Range usedRange = null;
ExcelRaw.Range usedColumns = null;
ExcelRaw.Range usedRows = null;
try
{
summedRange = applyto.Precedents;
// Check if we have the correct range
var precendentRows = summedRange.Cast <ExcelRaw.Range>().Select(cell =>
{
var addr = cell.Address[false, false];
Marshal.ReleaseComObject(cell);
return(new Location(addr).Row1);
}).OrderBy(x => x);
Debug.Assert(summedRows.SequenceEqual(precendentRows), "Precedents given by Excel did now correspond to summed rows");
worksheet = applyto.Worksheet;
//cells = worksheet.Cells;
// Find last filled column in worksheet
usedRange = worksheet.UsedRange;
usedColumns = usedRange.Columns;
usedRows = usedRange.Rows;
var usedRowsCount = usedRows.Count;
// Determiner column: column we can use as the subject for the SUMIF predicate
var determiners = usedColumns.Cast <ExcelRaw.Range>().Select(column => {
ExcelRaw.Range columncells = null;
ExcelRaw.Range firstCell = null;
try
{
columncells = column.Cells;
firstCell = columncells[summedRows[0], 1];
object candidatevalue = firstCell.Value2;
// Check if [Column,Firstrow] contains a value
if (candidatevalue == null)
{
return(null);
}
// Check if all summed rows contain the same value
// Check if none of the other rows contain that value
if (Enumerable.Range(1, usedRowsCount).All(row =>
{
ExcelRaw.Range cell = columncells[row, 1];
object v = cell.Value2;
cell.ReleaseCom();
// Empty cell, candidate can't be empty so we can ignore this
if (v == null)
{
return(true);
}
bool isSummed = summedRowsSet.Contains(row);
// All summed rows must be candidatevalue
// All not-summed rows must not be candidatevalue
return((isSummed && candidatevalue.Equals(v)) || (!isSummed && !candidatevalue.Equals(v)));
}))
{
// We found a candidatecolumn!
return(Tuple.Create(candidatevalue, column.Column));
}
return(null);
}
finally
{
firstCell.ReleaseCom();
columncells.ReleaseCom();
column.ReleaseCom();
}
});
var determiner = determiners.FirstOrDefault(found => found != null);
// If we didn't find a candidate, do nothing
if (determiner == null)
{
return;
}
string determinerValue = (determiner.Item1 is double) ? determiner.Item1.ToString() : $"\"{determiner.Item1}\"";
var determinerColumn = AuxConverter.ConvertColumnToStr(determiner.Item2 - 1);
var summedColumn = AuxConverter.ConvertColumnToStr(summedColumns[0] - 1);
var formula = $"{fname}IF({determinerColumn}:{determinerColumn},{determinerValue},{summedColumn}:{summedColumn})";
try
{
applyto.Formula = $"={formula}";
}
catch (COMException)
{
throw new InvalidOperationException($"Refactoring created invalid formula <<{formula}>>");
}
}
finally
{
usedRange.ReleaseCom();
usedColumns.ReleaseCom();
usedRows.ReleaseCom();
worksheet.ReleaseCom();
summedRange.ReleaseCom();
}
}
