def get_sheet(sheet, file = "testfile.xlsx"):
'''
sheet = sheet name
file = default is 'main excel file', optional
returns excel worksheet
'''
return_obj = None
book = None
for i in range(10):
try:
if win.GetActiveObject("Excel.Application").Workbooks(i).Name == file:
return_obj = win.GetActiveObject("Excel.Application").Workbooks(i)
book = i
break
except:
continue
for i in range(10):
try:
if win.GetActiveObject("Excel.Application").Workbooks(book).Worksheets(i).Name == sheet:
return_obj = win.GetActiveObject("Excel.Application").Workbooks(book).Worksheets(i)
sheet = i
except:
continue
return return_obj
def __init__(self, file_name: str = None) -> None:
"""Initializes the `Win32Adobe` client.
## Parameters
----
file_name : str, optional
The name of the Thumbnail file to load, by default None
"""
# Grab the Adobe Application if it's open.
try:
self.adobe_app = win32.GetActiveObject("Illustrator.Application")
except:
self.adobe_app = win32.dynamic.Dispatch("Illustrator.Application")
# Define the Thumbnail folder.
self.thumbnail_folder = pathlib.Path("thumbnails").absolute()
# If we have a file name open it.
if file_name:
# Create the full path.
file_name = self.thumbnail_folder.joinpath(file_name)
# Open the file.
self.adobe_file = self.adobe_app.Open(file_name.as_posix())
self.active_document = self.adobe_app.ActiveDocument
def get_win32_app(name, visible=False):
""" 启动可支持pywin32自动化处理的应用
Args:
str name: 应用名称，不区分大小写，比如word, excel, powerpoint, onenote
不带'.'的情况下，会自动添加'.Application'的后缀
visible: 应用是否可见
Returns: app
"""
# 1 name
name = name.lower()
if '.' not in name:
name += '.application'
# 2 app
# 这里可能还有些问题，不同的应用，机制不太一样，后面再细化完善吧
try:
app = win32.GetActiveObject(f'{name}') # 不能关联到普通方式打开的应用。但代码打开的应用都能找得到。
except pythoncom.com_error:
app = win32.gencache.EnsureDispatch(f'{name}')
# 还有种常见的初始化方法，是 win32com.client.Dispatch和win32com.client.dynamic.Dispatch
# from win32com.client.dynamic import Disypatch
if visible is not None:
app.Visible = visible
return app
def __init__(self, visible=True):
try:
app = win32.GetActiveObject("Excel.Application")
logging.debug("Running Excel instance found, returning object")
except pwt.com_error: # pylint: disable=E1101
app = win32.gencache.EnsureDispatch("Excel.Application")
app.Visible = visible
logging.debug("No running Excel instances, returning new instance")
self.app = app
self.wbs = {} # type: Dict[str, bool]
def add_to_excel():
status = ["", "Just Received", "Picked", "Staged", "Shipped"]
activeExcel = win.GetActiveObject('Excel.Application')
excelWrkBk = activeExcel.Workbooks(1)
excelWrkSts = excelWrkBk.Worksheets(1)
today = date.today()
files_path = r'C:\Users\0235124\OneDrive - University of Waterloo\Desktop\signodeProjects\pdfParse\pyPDF2\OneDrive' + '\\' + str(
today)
os.chdir(files_path)
pdf_files_to_add = glob.glob(os.path.join(files_path, '*.pdf'))
############# Check for valid cellNo (cell.Value == None), init value in the next empty cell
init = 2
valid = True
pick_ticket_already_in_excel = []
while valid:
no = f'B{init}'
if excelWrkSts.Range(no).Value == None:
break
pick_ticket_already_in_excel.append(excelWrkSts.Range(no).Value)
init += 1
############ Now we have the init No
for file in pdf_files_to_add:
if os.path.basename(file[:-4]) in pick_ticket_already_in_excel:
continue
date_received = datetime.now() #1
friendly_name = '"' + os.path.basename(file[:-4]) + '"' #2
link_location = '"' + file + '"' #2
target_cells = [
f'A{init}', f'B{init}', f'C{init}', f'D{init}', f'E{init}'
]
############ Extract Info
loop(file)
excelWrkSts.Range(target_cells[0]).Value = date_received
excelWrkSts.Range(
target_cells[1]
).Formula = f'=HYPERLINK({link_location}, {friendly_name})'
excelWrkSts.Range(target_cells[2]).Value = ticket_dict['ship']
excelWrkSts.Range(target_cells[3]).Value = ticket_dict['via']
excelWrkSts.Range(target_cells[4]).Value = status[1]
init += 1
############ Return to original path
os.chdir(
'C:\\Users\\0235124\\OneDrive - University of Waterloo\\Desktop\\signodeProjects\\pdfParse\\pyPDF2\\OneDrive'
)
def prepare(self): # pylint: disable=missing-function-docstring
self.outlook = client.GetActiveObject('Outlook.Application')
self.mapi = self.outlook.GetNamespace("MAPI")
self.account = None
for account in self.mapi.Folders:
if (account.Name == self.conf('account')):
self.account = account
break
if (not self.account):
raise errors.AuthorizationError('Account {} does not exist'.format(
self.conf('account')))
def get_workbook(self):
wb_name = os.path.basename(self.wb_path)
try:
xl = client.GetActiveObject(EXCEL_CLS_NAME)
except:
# Excel is not running, so we need to handle it.
xl = client.Dispatch(EXCEL_CLS_NAME)
if wb_name in [wb.Name for wb in xl.Workbooks]:
return xl.Workbooks[wb_name]
else:
return xl.Workbooks.Open(self.wb_path)
def classic_submit(self):
try:
driver = comclt.GetActiveObject("SAS.Application")
except:
send_alert("No SAS program opening!")
logging.info("Submitting to sas classic ... ...")
driver = comclt.Dispatch("Wscript.Shell")
driver.AppActivate("SAS")
win32api.Sleep(500)
driver.SendKeys("{F6}")
win32api.Sleep(500)
driver.SendKeys("{F1}")
def open_office_app(which, visible=True):
"""
Get running Office app instance if possible, else return new instance.
App can be Word, Excel, Outlook, etc.
"""
try:
app = win32.GetActiveObject("{}.Application".format(which))
logging.debug("Running %s instance found, returning object", which)
except pwt.com_error: # pylint: disable=E1101
app = win32.gencache.EnsureDispatch("{}.Application".format(which))
app.Visible = visible
logging.debug("No running %s instances, returning new instance", which)
return app
def active_pres():
"""
@visible: Set PowerPoint application window to visible
Grabs the active PowerPoint application and creates a COM object
representing the active presentation.
"""
try:
p = win32Client.GetActiveObject("PowerPoint.Application")
except com_error:
p = win32ClientGen.EnsureDispatch("PowerPoint.Application")
return p.ActivePresentation
def __init__(self):
'''
Connect to Carl Zeiss ZEN blue Python API
'''
# setup logging
# Import the ZEN OAD Scripting into Python
self.Zen = microscopeConnection.GetActiveObject(
"Zeiss.Micro.Scripting.ZenWrapperLM")
# predefine internal settings
self.zLoad = None
self.zWork = None
self.image = None
# Save stored position. We will use this position to move the objective to this position before recalling this position.
# If the stored position is close to the find_surface position, Definite Focus works much faster.
self.DFObjective = None
self.DFStoredFocus = None
self.lastKnownFocusPosition = None
self.set_autofocus_not_ready()
log.info('Connected to ZEN')
def __Emailer(text, subject, recipients, auto=True):
import win32com.client as win32
try:
outlook = win32.GetActiveObject('Outlook.Application')
except:
outlook = win32.Dispatch('Outlook.Application')
mail = outlook.CreateItem(0)
if hasattr(recipients, 'strip'):
recipients = [recipients]
for recipient in recipients:
mail.Recipients.Add(recipient)
mail.Subject = subject
mail.HtmlBody = text
if auto:
mail.send
else:
mail.Display(True)
def get_app(cls,
app=None,
*,
visible=None,
display_alerts=0,
recursion_enchant=True):
"""
Args:
app: 可以自定义在外部使用Dispatch、DispatchEx等形式给入已初始化好的app
visible: 是否可见
display_alerts: 是否关闭警告
recursion_enchant: 是否递归执行enchant
"""
# 1 get app
name = 'WORD.APPLICATION'
if app is None:
try:
app = win32.GetActiveObject(name)
except pythoncom.com_error:
pass
if app is None:
try:
# 名称用大写，会比较兼容旧的word2013等版本
# 尽量静态调度，才能获得 from win32com.client import constants 的常量
app = win32.gencache.EnsureDispatch(name)
except TypeError:
# 实在不行，就用动态调度
app = win32.dynamic.Dispatch(name)
# 2 enchant
cls.enchant(app, recursion_enchant=recursion_enchant)
if visible is not None:
app.Visible = visible
if display_alerts is not None:
app.DisplayAlerts = display_alerts # 不警告
return app
from win32com import client
xl = client.GetActiveObject("Excel.Application")
xl.ActiveWorkbook.ActiveSheet.Range("A1:B2").Select()
# Define our Application Events
class ApplicationEvents:
# define an event inside of the application, Be aware OnSheetActivate Syntax!
def OnSheetActivate(self, *args):
print("汇报~你已经选中了这个Sheet~")
# Define our Workbook Events
class WorkbookEvents:
# define an event inside of the workbook, Be aware OnSheetSelectionChange Syntax!
def OnSheetSelectionChange(self, *args):
print(args[1].Address)
# get the instance which is activated right now
excel1 = win32.GetActiveObject("Excel.Application")
# assign our event to the Excel Object
excel1_events = win32.WithEvents(excel1, ApplicationEvents)
# Get our workbook (Remember to enter the correct current workbook name(例如：工作簿2))
excel1_workbook = excel1.Workbooks("工作簿2")
# assign our event to the workbook
excel1_workbook_events = win32.WithEvents(excel1_workbook, WorkbookEvents)
while True:
# display the message
pythoncom.PumpWaitingMessages()
import pprint
import win32com.client as win32
from win32com.client import constants as win_const
# Grab the Active Instance of Adobe.
try:
adobe_app = win32.GetActiveObject("Illustrator.Application")
except:
adobe_app = win32.gencache.EnsureDispatch("Illustrator.Application")
# Define the Document we will be working with.
try:
adobe_doc = adobe_app.ActiveDocument
except:
adobe_doc = adobe_app.Documents.Add(win_const.aiDocumentCMYKColor,
Width=300,
Height=300)
# Grab the Documents Collection.
adobe_docs = adobe_app.Documents
# Grab the Document Count.
print("The number of Documents are {doc_count}".format(
doc_count=adobe_docs.Count))
# Add a new Document.
adobe_doc_new = adobe_docs.Add(win_const.aiDocumentCMYKColor,
Width=300,
Height=300)
# Print the New Document Name.
# ts.set_token("0b32a25ad92f09bcf7bb160c7b203033781f9b23db7ba4d1917bb436")
# #初始化接口
# pro = ts.pro_api()
# #获取每日数据
# df_000001 = pro.daily(ts_code = "000001.SZ")
# df_000002 = pro.daily(ts_code = "000002.SZ")
# #实例化一个pandas的writer
# writer = pd.ExcelWriter(r"E:\pyworkspace\RawData.xlsx")
# df_000001.to_excel(writer,"sheet1")
# df_000002.to_excel(writer,"sheet2")
# writer.save()
import win32com.client as win32
import pandas
#获取表格内容
OurExcelApp = win32.GetActiveObject("Excel.Application")
# RangeSelect = OurExcelApp.Range("A1:L5001").Value
RangeSelect = OurExcelApp.Sheets("sheet2").Range("A1:L5001").Value
# RangeSelect = OurExcelApp.Workbooks("RawData.xlsx").Sheets("sheet2").Range("A1:L5001").Value
# print(RangeSelect)
# print(type(RangeSelect))
#导入pandas，生成DataFrame格式数据
#先生成Series格式，再转成dataframe
Raw_Series_Data = pd.Series(RangeSelect)
#标题
Column_Data = Raw_Series_Data[0]
#数据
Trade_Data = Raw_Series_Data[1:]
#使用pd.DataFrame
Ready_Data_Frame = pd.DataFrame(data=list(Trade_Data), columns=Column_Data)
import os
import win32com.client as win32
import pandas as pd
import pprint
from datetime import datetime
WORK_PATH = os.getcwd()
# reference active Excel file
# must make excel workbook activiate, otherwise
# Operation unavailable error will raise
xlApp = win32.GetActiveObject('Excel.Application')
wb = xlApp.Workbooks('pandas.xlsx')
wsData = wb.Worksheets('Data')
# pprint.pprint(wsData.Range("A1:N2").value[:2])
data = pd.Series(wsData.Range("A1:N5").value)
df = pd.DataFrame(list(data))
# new header
header_names = df.iloc[0]
df = df[1:]
df.reset_index(drop=True, inplace=True)
df.rename(columns=header_names, inplace=True)
wsPanda = wb.Worksheets('pandas')
wsPanda.Range(wsPanda.Cells(1, 1),
wsPanda.Cells(1, df.shape[1])).value = list(header_names)
wsPanda.Range(wsPanda.Cells(2, 1),
wsPanda.Cells(df.shape[0] + 1,
df.shape[1])).value = list(df.values)
import win32com.client as win32
# Grab the Active Instance of Word
WrdApp = win32.GetActiveObject("Word.Application")
# Grab the current document.
WrdDoc = WrdApp.ActiveDocument
# Reference the Table in it.
WrdTable = WrdDoc.Tables.Item(1)
# Grab all the columns
SaleColumn = WrdTable.Columns(1)
CostColumn = WrdTable.Columns(2)
ProfitColumn = WrdTable.Columns(3)
# Loop through each cell in the Sales Column.
for SaleCell in list(SaleColumn.Cells)[1:]:
# Grab the Text
SaleCellText = SaleCell.Range.Text
# Clear out the old text
SaleCell.Range.Text = ""
# Create a Formula String
formula_string = "={my_number}\#" "$#,##0.00;($#,##0.00)" "".format(
my_number=SaleCellText)
# Create the Range
SaleCell.Range.Select()
import win32com.client as win32
# Create/Assign an Application object from the currently active PowerPoint Instance
PPT = win32.GetActiveObject("PowerPoint.Application")
# Goto a specific slide number (slide view)
PPT.ActiveWindow.View.GotoSlide(1)
# Select a shape on a slide by it's number (the slide must be the current active slide)
PPT.ActivePresentation.Slides(1).Shapes(2).Select(-1)
# Select a shape it's Name (the slide must be the current active slide)
PPT.ActivePresentation.Slides(1).Shapes('Rectangle 1').Select(-1)
# Print out the Type number/constant value, of every Shape on the active slide numbered 1
for s in PPT.ActivePresentation.Slides(1).Shapes:
print(s.Type)
