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);
}
