Пример #1
0
        def on_timer(timer_id, unused):
            # when Excel is starting up and PyXLL imports its modules there
            # is no Excel window, and so the COM object needed to register
            # the shortcut might not be available yet.
            try:
                xl_window = get_active_object()
            except RuntimeError:
                if time.time() - start_time > timeout:
                    _log.error("Timed out waiting for Excel COM interface to become available.")
                    timer.kill_timer(timer_id)
                    _timer_id = None
                return

            # Get the Excel application from its window
            xl_app = Dispatch(xl_window).Application

            while _shortcuts_to_add:
                accelerator, macroname = _shortcuts_to_add.pop()
                try:
                    xl_app.OnKey(accelerator, macroname)
                except:
                    _log.error("Failed to add shortcut %s -> %s" % 
                                    (accelerator, macroname), exc_info=True)
            _log.debug("Finished adding shortcuts")
            timer.kill_timer(timer_id)
            _timer_id = None
Пример #2
0
def start_subprocess(target_address):
    """
    Start a sub-process that will write back to a cell.
    :param target_address: address of cell to write to from the child process.
    """
    # Get the window handle of the Excel process so the sub-process can
    # find the right Excel Application instance.
    xl_app = win32com.client.Dispatch(get_active_object()).Application
    os.environ["PYXLL_EXCEL_HWND"] = str(xl_app.Hwnd)

    # Get the log file name for the subproces to log to.
    root = logging.getLogger()
    logfile = None
    for handler in root.handlers:
        if isinstance(handler, logging.FileHandler):
            logfile = handler.baseFilename
            break

    # Start the subprocess that will write back to the target cell.
    # It's a daemon process so that it doesn't stop the main Excel process
    # from terminating even if it's still running.
    process = Process(target=_subprocess_func, args=(target_address, logfile))
    process.daemon = True
    process.start()

    return "Child process %d started" % process.pid
Пример #3
0
def xl_app():
    """returns a Dispatch object for the current Excel instance"""
    # get the Excel application object from PyXLL and wrap it
    xl_window = get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application
    # it's helpful to make sure the gen_py wrapper has been created
    # as otherwise things like constants and event handlers won't work.
    win32com.client.gencache.EnsureDispatch(xl_app)
    return xl_app
Пример #4
0
def resize_array_formula():
    """
    Recalculates and resizes a range to show all the results of a formula.
    """
    xl_window = get_active_object()
    xl = Dispatch(xl_window).Application

    selection = xl.Selection
    formula = selection.FormulaArray

    if not (formula and (formula.startswith("=") or formula.startswith("+"))):
        # nothing to do
        return

    # get the range of the entire formula
    current_range = _expand_range(xl, selection)

    # evaluate the formula to get the dimensions of the result
    # (this is an optimization to avoid converting the range into a python list)
    # result = xl.Evaluate(formula)
    result = xl._oleobj_.InvokeTypes(1, 0, 1, (12, 0), ((12, 1), ), formula)

    width, height = 0, len(result)
    if height > 0 and isinstance(result[0], (list, tuple)):
        width = len(result[0])
    width, height = max(width, 1), max(height, 1)

    new_range = xl.Range(current_range.Offset(1, 1),
                         current_range.Offset(height, width))

    # check if we're overwriting any existing data
    if new_range.Count > current_range.Count:
        current_non_blank = current_range.Count - xl.WorksheetFunction.CountBlank(
            current_range)
        new_non_blank = new_range.Count - xl.WorksheetFunction.CountBlank(
            new_range)
        if current_non_blank != new_non_blank:
            new_range.Select()
            result = win32api.MessageBox(
                None, "Content will be overwritten in %s" % new_range.Address,
                "Warning", win32con.MB_OKCANCEL | win32con.MB_ICONWARNING)
            if result == win32con.IDCANCEL:
                current_range.FormulaArray = formula
                return

    # clear the old range
    current_range.ClearContents()

    # set the formula on the new range
    try:
        new_range.FormulaArray = formula
    except Exception:
        result = win32api.MessageBox(
            None, "Error resizing range", "Error",
            win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
        _log.error("Error resizing range", exc_info=True)
        current_range.FormulaArray = formula
Пример #5
0
def resize_array_formula():
    """
    Recalculates and resizes a range to show all the results of a formula.
    """
    xl_window = get_active_object()
    xl = Dispatch(xl_window).Application

    selection = xl.Selection
    formula = selection.FormulaArray

    if not (formula and (formula.startswith("=") or formula.startswith("+"))):
        # nothing to do
        return 

    # get the range of the entire formula
    current_range = _expand_range(xl, selection)

    # evaluate the formula to get the dimensions of the result
    # (this is an optimization to avoid converting the range into a python list)
    # result = xl.Evaluate(formula)
    result = xl._oleobj_.InvokeTypes(1, 0, 1, (12, 0), ((12, 1),), formula)
    
    width, height = 0, len(result)
    if height > 0 and isinstance(result[0], (list, tuple)):
        width = len(result[0])
    width, height = max(width, 1), max(height, 1)

    new_range = xl.Range(current_range.Offset(1, 1),
                         current_range.Offset(height, width))

    # check if we're overwriting any existing data
    if new_range.Count > current_range.Count:
        current_non_blank = current_range.Count - xl.WorksheetFunction.CountBlank(current_range)
        new_non_blank = new_range.Count - xl.WorksheetFunction.CountBlank(new_range)
        if current_non_blank != new_non_blank:
            new_range.Select()
            result = win32api.MessageBox(None,
                                         "Content will be overwritten in %s" % new_range.Address,
                                         "Warning",
                                         win32con.MB_OKCANCEL | win32con.MB_ICONWARNING)
            if result == win32con.IDCANCEL:
                current_range.FormulaArray = formula
                return

    # clear the old range
    current_range.ClearContents()

    # set the formula on the new range
    try:
        new_range.FormulaArray = formula
    except Exception:
        result = win32api.MessageBox(None,
                                     "Error resizing range",
                                     "Error",
                                     win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
        _log.error("Error resizing range", exc_info=True)
        current_range.FormulaArray = formula
Пример #6
0
def recalc_selection():
    """
    Recalculates selection
    """
    xl_window = get_active_object()
    xl = Dispatch(xl_window).Application

    selection = xl.Selection
    selection.Dirty()
    selection.Calculate()
Пример #7
0
def recalc_selection():
    """
    Recalculates selection
    """
    xl_window = get_active_object()
    xl = Dispatch(xl_window).Application

    selection = xl.Selection
    selection.Dirty()
    selection.Calculate()
Пример #8
0
def _xl_app():
    """returns a Dispatch object for the current Excel instance"""
    # get the Excel application object from PyXLL and wrap it
    xl_window = pyxll.get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application

    # it's helpful to make sure the gen_py wrapper has been created
    # as otherwise things like constants and event handlers won't work.
    win32com.client.gencache.EnsureDispatch(xl_app)

    return xl_app
Пример #9
0
def py_rtd_test(seconds):
    xl = win32com.client.Dispatch(get_active_object()).Application
    return xl.WorksheetFunction.RTD(TimeServer._reg_progid_, "", "seconds", str(seconds))
Пример #10
0
def py_rtd_test(seconds):
    xl = win32com.client.Dispatch(get_active_object()).Application
    return xl.WorksheetFunction.RTD(TimeServer._reg_progid_, "", "seconds", str(seconds))
Пример #11
0
def xl_app():
    xl_window = get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application
    return xl_app
Пример #12
0
def xl_app():
    xl_window = get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application
    return xl_app
Пример #13
0
def Simulate():
    global num_of_sim 
    global op  
    global num_of_inp
    global iter_num
    global current_inp
    global inpcell
    global output_flag
    global usr_selection
    global inparraycell
    global formstr
    global arrayvals
    global simcomplete
    global inp
    
    xlcCalculateNow()
    xlcCalculateDocument()
    if not output_flag:
        xlcAlert("Select a output cell. If selected, click reload")
        return
#     
    checkMIArray=False
    win32com.client.gencache.Rebuild()
    xl_window = get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application
    xl_app.Calculation=xlCalculationManual
#     xl_app = win32com.client.GetActiveObject("Excel.Application")
#     win32com.client.gencache.EnsureDispatch(xl_app)
    
    num_of_sim = 0
    if ((inpcell is not None) and formstr == str(inpcell.formula) ): 
        checkMIArray=True
#         xlcAlert('inside looop:'+ str(inp).strip('[]'))
        rect = inpcell.rect
#         xlcAlert(inpcell.address)
#         xlcAlert(str(rect.first_row))
#         xlcAlert(str(rect.first_col))
        selection = xl_app.ActiveSheet.Cells(int(rect.first_row)+1,int(rect.first_col)+1)
#         selection.value = 100
#     inpcell.value = 100
#     xlcAlert("click OK")
    
    app1 = QApplication(sys.argv)
    form = Dialog.popup()
    app1.exec_()
    num_of_sim = int(Dialog.retval())
    usr_selection = str(Dialog.retsel())
    writeFG = Dialog.writeflag()
    if num_of_sim > 0:
        current_inp =1
#         xlcAlert(str(num_of_sim))
        start = time.time()
        op=None
        op = zeros((num_of_inp,num_of_sim))        
        xl_app.ScreenUpdating = False
        xl_app.DisplayStatusBar = False
        xl_app.EnableEvents = False
        for j in range(num_of_inp):
            current_inp = j
            if (checkMIArray):
                selection.Value = inp[current_inp]
            for i in range(num_of_sim):
                iter_num = i
                xlcCalculateDocument()
        end = time.time()
        xl_app.ScreenUpdating = True
        xl_app.DisplayStatusBar = True
        xl_app.EnableEvents = True
        current_inp = 0
        simcomplete= True
#         selection.value = inp[current_inp]
#         selection.Formula = inpcell.formula
        if checkMIArray:    
            fstr = '=MiInput(' + inparraycell.address + ')'
            selection.Formula = fstr
#         xlcAlert(str(checkMIArray))
        if not checkMIArray:
            inp = [0]
        UI.draw(op, inp,usr_selection)
        # Store data in a CSV format
        if checkMIArray:
            popupstr = ''
            for idx,inpvalue in enumerate(inp):
                tupleop = None
                tupleop = tuple(op[idx])
                datastr = "data" + str(idx)
                xl_app.ActiveWorkbook.Names.Add(datastr,tupleop,False)
                popupstr = popupstr + "Output variable for Input " + str(inpvalue) + "is: "+str(datastr) + "\n"
            popupstr = popupstr + "You can use all the excel statistical functions on these variables"
            xlcAlert(popupstr) 
        else:
            tupleop = tuple(op)
            xl_app.ActiveWorkbook.Names.Add("data",tupleop,False)
            xlcAlert("Your Output Variable is 'data'" + "\n" + "You can use all the excel statistical functions on this variable")
        if writeFG:
            config = get_config()
            config.read('pyxll.cfg')
            dir_path = config.get('LOG','path')
            xlcAlert("Data stored at "+str(dir_path))
            file_name = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
            if checkMIArray:
#                 xlcAlert(str(len(op[1])))
                for idx,inpvalue in enumerate(inp):
                    file_name1 = file_name +"-input "+str(inpvalue) +'.csv'
                    if os.path.exists(dir_path): 
                        myfile = open(os.path.join(dir_path, file_name1), 'wb')
                        wr = csv.writer(myfile, dialect='excel')
                        wr.writerow(op[idx])
            else:
                if os.path.exists(dir_path):
                    file_name = file_name + '.csv' 
                    myfile = open(os.path.join(dir_path, file_name), 'wb')
                    wr = csv.writer(myfile, dialect='excel')
                    wr.writerows(op)