internal void btnViewDependencies_Click() { //todo: display a new pane which visualises the dependencies as a graph and allows the scripts to be run by interacting with that graph var sourceDict = PythonSourceManager.GetAllSourceCode(_excel.ActiveWorkbook).Where(i => i.Value.Trim().Length > 0); var sheetNames = _excel.ActiveWorkbook.Worksheets.Select(s => (s as Worksheet).Name); foreach (var pair in sourceDict) { var predecessors = sheetNames.Where(sheet => pair.Value.Contains(sheet)).Select(n => "'" + n + "'"); if (predecessors.Count(i => true) == 0) { MessageBox.Show("The worksheet '" + pair.Key + "' has no dependencies."); } else { var message = "The worksheet '" + pair.Key + "' uses data from the worksheet" + (predecessors.Count(i => true) == 1 ? " " : "s ") + String.Join(", ", predecessors) + "."; MessageBox.Show(ReplaceLastOccurrence(message, ", ", " and ")); } } if (sourceDict.Count(i => true) == 0) { MessageBox.Show("There is no Python code embedded in this workbook."); } // }
private void _excel_SheetActivateEvent(COMObject Sh) { var shObj = Sh as Worksheet; var retrieved = PythonSourceManager.GetSourceCode(shObj.Parent as Workbook, shObj.Name); _ce.SourceCode = retrieved; }
public static void GetAfter(Workbook wb, string sheetName) { //todo: consider tables names as well as sheet names CTPManager.Save(); var sourceDict = PythonSourceManager.GetAllSourceCode(wb).Where(i => i.Value.Trim().Length > 0); var sheetNames = wb.Worksheets.Select(s => (s as Worksheet).Name); var proposed = new HashSet <string> { sheetName }; var parsed = new Dictionary <string, List <string> > { }; var executed = new HashSet <string> { }; if (sourceDict.ToList().Count == 0) { MessageBox.Show("This workbook does not contain any embedded python code."); return; } while (proposed.Where(prop => !parsed.Keys.Contains(prop)).ToList().Count > 0) { var toParse = proposed.Where(prop => !parsed.Keys.Contains(prop)); var proposedSheet = toParse.First(); var source = sourceDict.Where(x => x.Key == proposedSheet).First().Value; var successors = sourceDict.Where(s => proposed.Where(p => s.Value.Contains(p)).ToList().Count > 0); var predecessors = proposed.Where(sh => source.Contains(sh) && sourceDict.Where(i => i.Key == sh).ToList().Count > 0).ToList(); foreach (var s in successors) { proposed.Add(s.Key); } parsed.Add(proposedSheet, predecessors); } executed.Add(sheetName); RefreshTable.Start(sheetName); while (parsed.Keys.Where(k => !executed.Contains(k)).ToList().Count > 0) { var notExecuted = parsed.Where(k => !executed.Contains(k.Key)); var readyToExecute = notExecuted.Where(i => i.Value.Where(p => !executed.Contains(p)).ToList().Count == 0); if (readyToExecute.ToList().Count == 0 && notExecuted.ToList().Count > 0) { MessageBox.Show("There is a circular reference within your scripts. The following scripts were not executed: \n" + string.Join(", ", notExecuted.Select(i => i.Key))); break; } else { var selected = readyToExecute.First().Key; RefreshTable.Start(selected); executed.Add(selected); } } }
public void Save(Worksheet targetSheet = null, string newSource = null) { if (targetSheet == null) { targetSheet = _excel.ActiveSheet as Worksheet; } if (newSource != null) { _ce.SourceCode = newSource; } if (_ce.SourceCode != "!") { PythonSourceManager.SetSourceCode(targetSheet.Parent as Workbook, targetSheet.Name, _ce.SourceCode); } }
public CodeEditorPane(CustomTaskPane ctp, CodeEditor ce) { _ctp = ctp; _ce = ce; _excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application); _ctp.DockPosition = MsoCTPDockPosition.msoCTPDockPositionBottom; _ctp.VisibleStateChange += _ctp_VisibleStateChange; _ce.Size = new System.Drawing.Size(_ctp.Width, _ctp.Height); _ctp.Visible = true; _excel.ActiveWorkbook.BeforeSaveEvent += ActiveWorkbook_BeforeSaveEvent; _excel.ActiveWorkbook.SheetActivateEvent += _excel_SheetActivateEvent; _excel.ActiveWorkbook.SheetDeactivateEvent += _excel_SheetDeactivateEvent; //Load existing source for current sheet var shObj = _excel.ActiveSheet as Worksheet; _ce.SourceCode = PythonSourceManager.GetSourceCode(shObj.Parent as Workbook, shObj.Name); }
public static void Start(string sheetName = "") { CTPManager.Save(); var _excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application); var activeSheet = sheetName == "" ? (Worksheet)_excel.ActiveSheet : (Worksheet)_excel.ActiveWorkbook.Worksheets[sheetName]; string sourceCode = PythonSourceManager.GetSourceCode(activeSheet.Parent as Workbook, activeSheet.Name); //remove non-printable characters sourceCode = Regex.Replace(sourceCode, @"[^ -~\n\t]+", " "); if (sourceCode.Length > 0) { var settings = new CustomXML.UserSettings(_excel.ActiveWorkbook); var workbookPath = "\"" + _excel.ActiveWorkbook.Path.Replace("\\", "\\\\") + "\""; var expandedSourceCode = "workbookPath=" + workbookPath + "\nimport os, pandas\nif(len(workbookPath)>1): os.chdir(workbookPath)\nresult = pandas.DataFrame()\n" + settings.prepend + "\n" + sourceCode + "\nif(result.shape[0] != 0): table[this] = result"; var tempCol = new TempFileCollection(Path.GetTempPath(), false); var filename = tempCol.AddExtension("py"); File.WriteAllText(filename, expandedSourceCode); //Run Python code _excel.StatusBar = $"Running Python for '{activeSheet.Name}' In Background ..."; Run(settings.path, Path.Combine(settings.args, filename), sourceCode, activeSheet); _excel.StatusBar = ""; // } else { MessageBox.Show("There is no table refresh code associated with this worksheet.", "Refresh Table Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
private static void Run(string pyPath, string pyArg, string sourceCode, Worksheet ws) { var _excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application); try { var psi = new ProcessStartInfo() { FileName = pyPath, WorkingDirectory = Path.GetDirectoryName(_excel.ActiveWorkbook.Path.Length > 0 ? _excel.ActiveWorkbook.Path : pyArg), Arguments = pyArg, RedirectStandardError = true, RedirectStandardOutput = true, WindowStyle = ProcessWindowStyle.Hidden, CreateNoWindow = true, UseShellExecute = false }; var process = new Process(); var output = ""; var error = ""; process.StartInfo = psi; process.EnableRaisingEvents = false; process.OutputDataReceived += (sender, eventArgs) => output += eventArgs.Data + "\n"; process.ErrorDataReceived += (sender, eventArgs) => error += eventArgs.Data + "\n"; process.Start(); process.BeginOutputReadLine(); process.BeginErrorReadLine(); var processExited = process.WaitForExit(30000); var lastError = error.Trim().Split('\n').Reverse().Take(1).ToArray()[0].ToString(); if (processExited == false) // we timed out... { process.Kill(); lastError = "Execution of the python script timed out after 30 seconds."; } //Script execution completed. var separator = new string[] { "\n\n#Refreshed = ", "\n \n#Refreshed = " }; var newSource = sourceCode.Split(separator, StringSplitOptions.None).FirstOrDefault(); newSource += "\n\n#Refreshed = " + DateTime.Now.ToString("yyyy-mm-dd @ hh:mm tt"); if (lastError.Trim().Length > 0) { newSource += "\n#Error = " + lastError; } if (error.Trim().Length > 0 || true) { newSource += "\n#Source = " + pyArg; } if (output.Trim().Length > 0) { newSource += "\n#Output = " + output.Trim().Replace("\n", "\n#Output = "); } if (error.Trim().Length > 0) { newSource += "\n\n#StackTrace\n#" + error.Replace("\n", "\n#"); } PythonSourceManager.SetSourceCode((Workbook)ws.Parent, ws.Name, newSource); if (ws.Name == ((ws.Parent as Workbook).ActiveSheet as Worksheet).Name) { CTPManager.Save(newSource); } } catch (Exception ex) { MessageBox.Show("Error executing Python script: " + ex.Message); } finally { _excel.Application.EnableEvents = true; _excel.Application.ScreenUpdating = true; } }
internal void Load() { var shObj = _excel.ActiveSheet as Worksheet; _ce.SourceCode = PythonSourceManager.GetSourceCode(shObj.Parent as Workbook, shObj.Name); }