private static string RangeToText(ExcelRange range)
{
IList <string> items = new List <string>();
RangeEnumerator enumerator = new RangeEnumerator(range);
while (enumerator.MoveNext())
{
ExcelRange cell = enumerator.Current;
if (cell != null && !string.IsNullOrWhiteSpace(cell.Text?.ToString()))
{
string text = cell.Text?.ToString();
items.Add(text);
}
}
if (items.Count > 0)
{
return(string.Join("\n", items));
}
else
{
return("");
}
}
/// <summary>
/// Given two iterators positioned such that ai.id().Contains(bi.id()),
/// visits all crossings between edges of A and B that intersect a.id().
/// Terminates early and returns false if visitor_ returns false.
/// Advances both iterators past ai.id().
/// </summary>
public bool VisitCrossings(RangeEnumerator ai, RangeEnumerator bi)
{
System.Diagnostics.Debug.Assert(ai.Id.Contains(bi.Id));
if (ai.Cell.NumEdges() == 0)
{
// Skip over the cells of B using binary search.
bi.SeekBeyond(ai);
}
else
{
// If ai.id() intersects many edges of B, then it is faster to use
// S2CrossingEdgeQuery to narrow down the candidates. But if it
// intersects only a few edges, it is faster to check all the crossings
// directly. We handle this by advancing "bi" and keeping track of how
// many edges we would need to test.
int b_edges = 0;
b_cells_.Clear();
do
{
int cell_edges = bi.Cell.NumEdges();
if (cell_edges > 0)
{
b_edges += cell_edges;
if (b_edges >= kEdgeQueryMinEdges)
{
// There are too many edges, so use an S2CrossingEdgeQuery.
if (!VisitSubcellCrossings(ai.Cell, ai.Id))
{
return(false);
}
bi.SeekBeyond(ai);
return(true);
}
b_cells_.Add(bi.Cell);
}
bi.MoveNext();
} while (bi.Id <= ai.RangeMax);
if (b_cells_.Any())
{
// Test all the edge crossings directly.
GetShapeEdges(a_index_, ai.Cell, a_shape_edges_);
GetShapeEdges(b_index_, b_cells_, b_shape_edges_);
if (!VisitEdgesEdgesCrossings(a_shape_edges_, b_shape_edges_))
{
return(false);
}
}
}
ai.MoveNext();
return(true);
}
public static void Execute(OfficeApps apps, Input input)
{
if (Flow.Interrupted)
{
return;
}
Log.Info("Executing Script");
Log.Debug("Target: " + input.Workbook.FullName);
Log.Debug("Template: " + input.Template.Name);
Log.Debug("Checking which cells are numerical:");
Log.PushIndent();
var fullEnumerator = new RangeEnumerator(input.Template.UsedRange);
bool[,] mask = new bool[fullEnumerator.Height, fullEnumerator.Width];
Progress.Init(Log.Debug);
Progress.Reset();
while (fullEnumerator.MoveNext())
{
Progress.Report(fullEnumerator.Progress);
ExcelRange cell = fullEnumerator.Current;
if (Flow.Interrupted)
{
break;
}
bool numeric = ExcelHelper.IsCellAnywhereNumeric(apps, cell.Row, cell.Column);
mask[fullEnumerator.RowIndex, fullEnumerator.ColumnIndex] = numeric;
}
Progress.Complete();
fullEnumerator.Dispose();
Log.PopIndent();
if (Flow.Interrupted)
{
return;
}
Log.Debug("Creating summary sheets:");
// Strange to double these I know, but it makes it easier to keep track of
// indents with interruptions this way.
Log.PushIndent();
Log.PushIndent();
foreach (string formula in input.Formulae)
{
string name = ExcelHelper.CreateUniqueWorksheetName(input.Workbook, formula);
Log.PopIndent();
Log.Debug($"Creating sheet \"{name}\":");
Log.PushIndent();
if (Flow.Interrupted)
{
break;
}
input.Template.Copy(After: input.Workbook.Sheets[apps.Excel.Sheets.Count]);
if (Flow.Interrupted)
{
break;
}
Worksheet newSheet = (Worksheet)input.Workbook.ActiveSheet;
newSheet.Name = name;
var maskedEnumerator = new RangeEnumerator(newSheet.UsedRange);
maskedEnumerator.ApplyMask(mask);
Progress.Reset();
while (maskedEnumerator.MoveNext())
{
Progress.Report(maskedEnumerator.Progress);
ExcelRange cell = maskedEnumerator.Current;
if (Flow.Interrupted)
{
break;
}
string range = $"'{input.SheetReference}'!{cell.Address}";
string formulaText = $"={formula}({range})";
cell.Value = formulaText;
}
Progress.Complete();
maskedEnumerator.Dispose();
if (Flow.Interrupted)
{
break;
}
}
Log.PopIndent();
Log.PopIndent();
if (Flow.Interrupted)
{
return;
}
Log.Debug($"Saving {input.Workbook.FullName}");
input.Workbook.Save();
Log.Success("Script complete");
}
public static void Execute(OfficeApps apps, Input input)
{
if (Flow.Interrupted)
{
return;
}
Log.Info("Executing Script");
input.Template.Copy(After: input.Workbook.Sheets[apps.Excel.Sheets.Count]);
if (Flow.Interrupted)
{
return;
}
Worksheet active = (Worksheet)apps.Excel.ActiveSheet;
active.Name = ExcelHelper.CreateUniqueWorksheetName(input.Workbook, "Summary");
if (Flow.Interrupted)
{
return;
}
ExcelHelper.TryParseWorksheetRange(out IEnumerable <Worksheet> worksheets, input.Workbook, input.SheetReference,
compareWords: true, verbrose: true);
if (Flow.Interrupted)
{
return;
}
RangeEnumerator enumerator = new RangeEnumerator(active.UsedRange);
Progress.Init(Log.Debug);
Progress.Reset();
while (enumerator.MoveNext())
{
if (Flow.Interrupted)
{
return;
}
ExcelRange cell = enumerator.Current;
string text = cell.Value?.ToString();
if (text == null)
{
continue;
}
string command = text.Trim().ToLower();
string referencePattern = "\\$\\s*([\\d\\w]+)\\s*\\$";
if (command == "$sheetname$")
{
int i = 0;
foreach (Worksheet sheet in worksheets)
{
if (Flow.Interrupted)
{
return;
}
// Note the self: .Cells[i,j] is 1-based, not 0-based!
ExcelRange recordCell = (ExcelRange)active.Cells[1 + enumerator.RowIndex, 1 + enumerator.ColumnIndex + i];
recordCell.Value = sheet.Name;
i++;
}
}
else
{
Match match = Regex.Match(command.ToUpper(), referencePattern);
if (match.Success)
{
string reference = match.Groups[1].Value;
try
{
int i = 0;
foreach (Worksheet sheet in worksheets)
{
if (Flow.Interrupted)
{
return;
}
ExcelRange sourceCell = (ExcelRange)sheet.Range[reference];
ExcelRange destinationCell = (ExcelRange)active.Cells[1 + enumerator.RowIndex, 1 + enumerator.ColumnIndex + i];
destinationCell.Value = sourceCell.Value?.ToString();
i++;
}
}
catch (Exception e)
{
Log.Warning($"Failed to apply reference {reference}");
Log.Debug("(Is it a valid reference?)");
if (AppHelper.DebugFlag)
{
Log.Debug("Note to self:", e);
}
}
}
}
Progress.Report(enumerator.Progress);
}
if (Flow.Interrupted)
{
return;
}
Progress.Complete();
Log.Debug($"Saving {input.Workbook.FullName}");
input.Workbook.Save();
Log.Success("Script complete");
}
/// <summary>
/// Like the above, but visits all pairs of crossing edges where one edge comes
/// from each S2ShapeIndex.
///
/// CAVEAT: Crossings may be visited more than once.
/// </summary>
public static bool VisitCrossingEdgePairs(S2ShapeIndex a_index, S2ShapeIndex b_index, CrossingType type, EdgePairVisitor visitor)
{
// We look for S2CellId ranges where the indexes of A and B overlap, and
// then test those edges for crossings.
// TODO(ericv): Use brute force if the total number of edges is small enough
// (using a larger threshold if the S2ShapeIndex is not constructed yet).
var ai = new RangeEnumerator(a_index);
var bi = new RangeEnumerator(b_index);
var ab = new IndexCrosser(a_index, b_index, type, visitor, false); // Tests A against B
var ba = new IndexCrosser(b_index, a_index, type, visitor, true); // Tests B against A
while (!ai.Done() || !bi.Done())
{
if (ai.RangeMax < bi.RangeMin)
{
// The A and B cells don't overlap, and A precedes B.
ai.SeekTo(bi);
}
else if (bi.RangeMax < ai.RangeMin)
{
// The A and B cells don't overlap, and B precedes A.
bi.SeekTo(ai);
}
else
{
// One cell contains the other. Determine which cell is larger.
var ab_relation = ai.Id.LowestOnBit() - bi.Id.LowestOnBit();
if (ab_relation > 0)
{
// A's index cell is larger.
if (!ab.VisitCrossings(ai, bi))
{
return(false);
}
}
else if (ab_relation < 0)
{
// B's index cell is larger.
if (!ba.VisitCrossings(bi, ai))
{
return(false);
}
}
else
{
// The A and B cells are the same.
if (ai.Cell.NumEdges() > 0 && bi.Cell.NumEdges() > 0)
{
if (!ab.VisitCellCellCrossings(ai.Cell, bi.Cell))
{
return(false);
}
}
ai.MoveNext();
bi.MoveNext();
}
}
}
return(true);
}