public bool TryParse(OfficeApps apps, bool readOnly, out Input input)
{
input = new Input();
if (Flow.Interrupted)
{
return(false);
}
if (FilePath == null || TemplateName == null || Formulae == null || SheetReference == null)
{
Script.Log.Error("Unable to parse user input - one or more fields were null");
return(false);
}
if (FileHelper.TryOpenWorkbook(apps, FilePath, readOnly, out Workbook result))
{
input.Workbook = result;
}
// The FileHelper will have its own logs, no need to create new ones here
else
{
return(false);
}
if (ExcelHelper.TrySelectWorksheet(input.Workbook, out Worksheet worksheet, TemplateName, true))
{
input.Template = worksheet;
}
public static bool TryOpenDocument(OfficeApps apps, string filePath,
bool readOnly, out Document result)
{
var app = apps.Word;
result = null;
bool found = false;
if (Flow.Interrupted)
{
return(false);
}
try
{
result = app.Documents.Open(filePath, ReadOnly: readOnly);
Log.Core.Debug($"Opening {result.Name}");
found = true;
}
catch (Exception e)
{
Log.Core.Error($"Failed to read document at {filePath}", e);
}
return(found);
}
public static void Execute(OfficeApps apps, Input input)
{
if (Flow.Interrupted)
{
return;
}
apps.Excel.DisplayAlerts = false;
Log.Info("Executing Script");
Log.Debug("Target: " + input.Workbook.FullName);
Log.Debug("Template: " + input.Worksheet.Name);
foreach (var name in input.NewNames)
{
if (Flow.Interrupted)
{
break;
}
// Note: this can fail to create a unique name if interrupted
string newName = ExcelHelper.CreateUniqueWorksheetName(input.Workbook, name);
if (Flow.Interrupted)
{
break;
}
if (newName == name)
{
Log.Debug($"Creating {name}");
}
else
{
Log.Debug($"{name} exists already. Creating {newName}");
}
input.Worksheet.Copy(After: input.Workbook.Sheets[apps.Excel.Sheets.Count]);
if (Flow.Interrupted)
{
break;
}
// There are issues with hidden sheets and indexing, hence using the ActiveSheet as the the target for the name
// This relies on the fact that a copy is made active.
Worksheet newSheet = (Worksheet)apps.Excel.ActiveSheet;
newSheet.Name = newName;
}
if (!Flow.Interrupted)
{
Log.Debug($"Saving {input.Workbook.FullName}");
input.Workbook.Save();
Log.Success("Script complete");
}
}
private static IEnumerable <Workbook> OpenWorkbooks(OfficeApps apps, IEnumerable <string> paths)
{
foreach (var path in paths)
{
if (FileHelper.TryOpenWorkbook(apps, path, readOnly: true, out var workbook))
{
yield return(workbook);
}
}
}
private void RunScript(OfficeApps apps)
{
if (Flow.Interrupted)
{
return;
}
if (userInput.TryParse(apps, readOnly: false, out var input))
{
Script.Execute(apps, input);
}
}
public bool TryParse(OfficeApps apps, bool readOnly, out Input input)
{
input = new Input();
if (Flow.Interrupted)
{
return(false);
}
if (FilePath == null || TemplateNames == null || SourceNames == null)
{
Script.Log.Error("Unable to parse user input - one or more fields were null");
return(false);
}
IEnumerable <string> templateNames = StringHelper.Split(TemplateNames);
IEnumerable <string> sourceNames = StringHelper.Split(SourceNames);
if (!templateNames.Any())
{
Script.Log.Warning("No template names given");
return(false);
}
if (!sourceNames.Any())
{
Script.Log.Warning("No source names given");
return(false);
}
Workbook workbook;
if (!FileHelper.TryOpenWorkbook(apps, FilePath, readOnly, out workbook))
{
// The FileHelper will have its own logs, no need to create new ones here
return(false);
}
input.Workbook = workbook;
if (Flow.Interrupted)
{
return(false);
}
input.Templates = new List <Worksheet>();
foreach (string templateName in templateNames)
{
if (ExcelHelper.TrySelectWorksheet(workbook, out Worksheet currentSheet, templateName, compareWords: true, verbrose: true))
{
input.Templates.Add(currentSheet);
}
public static void Execute(OfficeApps apps, Input input)
{
if (Flow.Interrupted)
{
return;
}
Log.Info("Executing Script");
Log.Debug($"Saving {input.Workbook.FullName}");
input.Workbook.Save();
Log.Success("Script complete");
}
private static void UpdateSharedWorksheets(OfficeApps apps, IEnumerable <string> paths)
{
var workbooks = OpenWorkbooks(apps, paths).ToList();
if (workbooks.Count == 0)
{
sharedWorksheets = null;
}
sharedWorksheets = new List <SharedWorksheet>();
ExcelHelper.FindCommonWorksheetNames(out var commonNames, out var outliers, workbooks);
if (Flow.Interrupted)
{
return;
}
foreach (var common in commonNames)
{
if (Flow.Interrupted)
{
return;
}
sharedWorksheets.Add(new SharedWorksheet
{
IsCommon = true,
SharedName = common,
SourceWorksheet = ""
});
}
foreach (var outlier in outliers)
{
if (Flow.Interrupted)
{
return;
}
sharedWorksheets.Add(new SharedWorksheet
{
IsCommon = false,
SharedName = outlier.Name,
SourceWorksheet = outlier.Source
});
}
}
public static void CheckCommonWorksheets(IEnumerable <string> workbookPaths)
{
if (workbookPaths.Count() < 2)
{
sharedWorksheets = new List <SharedWorksheet>(0);
}
void Update(OfficeApps apps)
{
UpdateSharedWorksheets(apps, workbookPaths);
}
void safeUpdate() => OfficeApps.RunExcelWithGuard(Update);
WindowHelper.RunWithCancel("Find Common Worksheets", safeUpdate, "Cancelled checking common sheets");
}
private static void UnsafeCheckSheets(OfficeApps apps, string workbookPath)
{
worksheetNames = ExcelHelper.GetSheetNames(apps, workbookPath)
.ToList(); // It is important that the generator be evaluated now, and not
//later when the excel app is likely closed or doing something else.
if (Flow.Interrupted)
{
return;
}
bool success = worksheetNames.Any();
if (success)
{
Logs.Wpf.Info($"Found {worksheetNames.Count()} sheet(s)");
}
else
{
Logs.Wpf.Warning("No sheets found");
}
}
private void RunGuardedScript()
{
OfficeApps.RunExcelWithGuard(RunScript);
}
public static void CheckSheets(string workbookPath)
{
OfficeApps.RunExcelWithGuard((apps) => UnsafeCheckSheets(apps, workbookPath));
}
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");
Log.Debug("Template: " + input.Template.Name);
string outputName = Path.GetFileNameWithoutExtension(input.Template.Name);
string outputExtension;
WdSaveFormat outputFormat;
switch (input.OutputFormat)
{
case OutputFormat.Word:
outputExtension = "docx";
outputFormat = WdSaveFormat.wdFormatDocument;
break;
case OutputFormat.PDF:
outputExtension = "pdf";
outputFormat = WdSaveFormat.wdFormatPDF;
break;
default:
string name = Enum.GetName(typeof(OutputFormat), input.OutputFormat);
Log.Warning($"Unrecognized output format {name}");
Log.Debug("Defaulting to PDF");
outputExtension = "pdf";
outputFormat = WdSaveFormat.wdFormatPDF;
break;
}
if (input.Sources.Count == 0)
{
Log.Warning("No sources found. Aborting.");
return;
}
Log.Debug("Sources:");
Log.PushIndent();
foreach (Input.Source source in input.Sources)
{
Log.Debug($"{source.Alias} - {source.Workbook.Name} ({source.Workbook.Path})");
if (Flow.Interrupted)
{
return;
}
}
Log.PopIndent();
if (input.SheetNames.Count == 0)
{
Log.Warning("No sheet names found. Aborting");
return;
}
Log.Debug("Sheet Names:");
Log.PushIndent();
foreach (string name in input.SheetNames)
{
Log.Debug(name);
if (Flow.Interrupted)
{
return;
}
}
Log.PopIndent();
if (Flow.Interrupted)
{
return;
}
FileHelper.SaveTemporarily(input.Template);
Log.Info($"Reading template");
List <ICommand> commands = CollectCommands(input.Template);
if (!commands.Any())
{
Log.Warning($"No commands found. Aborting.");
return;
}
else
{
Log.Debug($"{commands.Count()} found");
}
CommandContext context = new CommandContext();
foreach (Input.Source source in input.Sources)
{
context.BooksByAlias.Add(source.Alias, source.Workbook);
context.BooksByName.Add(source.Workbook.Name, source.Workbook);
}
Log.Debug("Checking commands");
List <ICommand> checkedCommands = commands
.Where(x => x.Check(context))
.ToList();
if (!checkedCommands.Any())
{
Log.Warning($"No commands passed check. Aborting.");
return;
}
Log.Debug($"Applying template to:");
Log.PushIndent();
if (Flow.Interrupted)
{
return;
}
foreach (string sheetName in input.SheetNames)
{
context.Name = sheetName;
Log.Debug(sheetName);
foreach (var command in checkedCommands)
{
command.Apply(context);
if (Flow.Interrupted)
{
break;
}
}
if (Flow.Interrupted)
{
break;
}
string parent = Path.GetFullPath("Output");
Directory.CreateDirectory(parent);
string fileName = PathHelper.CreateValidFilename($"{outputName} - {sheetName}.{outputExtension}");
string filePath = Path.Combine(parent, PathHelper.GetUniqueFileName(fileName));
input.Template.SaveAs2(filePath, outputFormat);
}
Log.PopIndent();
if (Flow.Interrupted)
{
return;
}
// This is saving to the temporary file, not overwriting anything.
input.Template.Save();
Log.Success("Script complete");
}
public static void Execute(OfficeApps apps, Input input)
{
if (Flow.Interrupted)
{
return;
}
Log.Info("Executing Script");
Log.Debug("Target workbook: " + input.Workbook.FullName);
Log.Debug("Number of templates: " + input.Templates.Count);
Log.Debug("Number of sources: " + input.Sources.Count);
foreach (Worksheet template in input.Templates)
{
if (template.Columns.Count < 2)
{
Log.Warning($"Skipping template {template.Name}, since it does not seem to have a" +
$" second column");
continue;
}
if (Flow.Interrupted)
{
break;
}
Log.Debug("Reading template: " + template.Name);
Log.PushIndent();
ExcelRange bottomRight = (ExcelRange)template.UsedRange.Cells[template.UsedRange.Cells.Count];
int endRow = bottomRight.Row;
int rowCount = endRow;
string[] references = new string[rowCount];
int referenceCount = 0;
for (int i = 1; i < rowCount; i++)
{
if (Flow.Interrupted)
{
break;
}
ExcelRange cell = (ExcelRange)template.Cells[1 + i, 2];
string reference = cell?.Text?.ToString();
reference = reference?.Trim();
const string pattern = @"[A-Za-z]+\d+";
if (string.IsNullOrWhiteSpace(reference))
{
Log.Debug($"Skipping empty row {1+i}");
continue;
}
if (!Regex.IsMatch(reference, pattern))
{
Log.Warning($"Skipping row {1+i}: cannot parse reference \"{reference}\"");
continue;
}
Log.Debug($"Row {1+i}: {reference}");
references[i] = reference;
referenceCount++;
}
Log.PopIndent();
if (Flow.Interrupted)
{
break;
}
if (referenceCount == 0)
{
continue;
}
Log.Debug("Populating template: " + template.Name);
Log.PushIndent();
for (int i = 0; i < input.Sources.Count; i++)
{
string name = input.Sources[i].Name;
Log.Debug(name);
ExcelRange headerCell = (ExcelRange)template.Cells[1, i + 3];
headerCell.Value = name;
for (int j = 0; j < references.Length; j++)
{
if (Flow.Interrupted)
{
break;
}
if (references[j] == null)
{
continue;
}
string formula = $"='{name}'!{references[j]}";
// The i + 3 is important - the columns of data should start
// on the third sheet column
ExcelRange targetCell = (ExcelRange)template.Cells[j + 1, i + 3];
targetCell.Value = formula;
if (Flow.Interrupted)
{
break;
}
// Use the formula to retrieve the value, then replace
// the formula with that value.
// This is a little less fiddly than retrieving the value
// directly from the other sheet. (It also requires less interop calls I think)
targetCell.Value = targetCell.Value2;
}
if (Flow.Interrupted)
{
break;
}
}
Log.PopIndent();
if (Flow.Interrupted)
{
break;
}
}
if (Flow.Interrupted)
{
return;
}
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");
}
