예제 #1
0
        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;
            }
예제 #2
0
        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);
        }
예제 #3
0
        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");
            }
        }
예제 #4
0
 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);
         }
     }
 }
예제 #5
0
        private void RunScript(OfficeApps apps)
        {
            if (Flow.Interrupted)
            {
                return;
            }

            if (userInput.TryParse(apps, readOnly: false, out var input))
            {
                Script.Execute(apps, input);
            }
        }
예제 #6
0
        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);
                }
예제 #7
0
        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");
        }
예제 #8
0
        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
                });
            }
        }
예제 #9
0
        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");
        }
예제 #10
0
        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");
            }
        }
예제 #11
0
 private void RunGuardedScript()
 {
     OfficeApps.RunExcelWithGuard(RunScript);
 }
예제 #12
0
 public static void CheckSheets(string workbookPath)
 {
     OfficeApps.RunExcelWithGuard((apps) => UnsafeCheckSheets(apps, workbookPath));
 }
예제 #13
0
        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");
        }
예제 #14
0
        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");
        }
예제 #15
0
        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");
        }
예제 #16
0
        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");
        }