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 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);
}
}
internal void Load()
{
var shObj = _excel.ActiveSheet as Worksheet;
_ce.SourceCode = PythonSourceManager.GetSourceCode(shObj.Parent as Workbook, shObj.Name);
}
