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();
}
}
private List <Excel.Range> GetChildren(ref Excel.Range poppedExcelCell, ref Stack <Excel.Range> stackExcelCells)
{
string sourceAddress = string.Format("{0}!{1}", poppedExcelCell.Worksheet.Name, poppedExcelCell.Address);
int arrowNumber = 1;
bool towardPrecedent = (_mode == "Precedents") ? true : false;
if (towardPrecedent)
{
poppedExcelCell.ShowPrecedents(false);
}
else
{
poppedExcelCell.ShowDependents(false);
}
List <Excel.Range> children = new List <Excel.Range>();
do
{
string targetAddress = null;
int linkNumber = 1;
do
{
try
{
Excel.Range target = poppedExcelCell.NavigateArrow(towardPrecedent, arrowNumber, linkNumber++);
targetAddress = string.Format("{0}!{1}", target.Worksheet.Name, target.Address);
if (sourceAddress == targetAddress)
{
break;
}
if (target.Count > _maxChildren)
{
children.Add(target);
stackExcelCells.Push(target);
}
else
{
var listTarget = target.Cast <Excel.Range>().ToList().Where(x => x.Value != null);
foreach (Excel.Range cell in listTarget)
{
if (cell.Value != null)
{
children.Add(cell);
stackExcelCells.Push(cell);
}
}
}
}
catch (COMException cex)
{
if (cex.Message == "NavigateArrow method of Range class failed")
{
break;
}
throw;
}
} while (true);
if (sourceAddress == targetAddress)
{
break;
}
arrowNumber++;
} while (true);
poppedExcelCell.Worksheet.ClearArrows();
return(children);
}
