import win32com.client as win32

# 啟動(dòng)Excel應(yīng)用
excel = win32.Dispatch('Excel.Application')
excel.Visible = True  # 可視化操作

# 創(chuàng)建工作簿
wb = excel.Workbooks.Add()
ws = wb.Worksheets(1)

# 單元格操作
ws.Cells(1, 1).Value = "Hello Excel"  # A1單元格
ws.Range("A2").Value = 123.45
ws.Range("A3:B3").Value = ["Python", "自動(dòng)化"]

# 保存文件
wb.SaveAs(r'C:\test\demo.xlsx')
wb.Close()
excel.Quit()

# 格式設(shè)置
ws.Range("A1:A3").Font.Bold = True
ws.Range("A1:A3").Font.Color = 0xFF0000  # 紅色
ws.Columns(1).ColumnWidth = 20

# 公式應(yīng)用
ws.Range("B4").Formula = "=SUM(A2:A3)"

# 圖表創(chuàng)建
chart = ws.Shapes.AddChart2(240, win32.constants.xlColumnClustered).Chart
chart.SetSourceData(ws.Range("A1:B3"))

# 數(shù)據(jù)篩選
ws.Range("A1:B10").AutoFilter(Field:=1, Criteria1:=">100")

# 宏調(diào)用
excel.Application.Run("MacroName")

word = win32.Dispatch('Word.Application')
word.Visible = True

# 新建文檔
doc = word.Documents.Add()

# 內(nèi)容操作
doc.Content.Text = "Python自動(dòng)化Word文檔\n"
paragraph = doc.Content.Paragraphs.Add()
paragraph.Range.Text = "第一段落內(nèi)容"
paragraph.Range.Font.Bold = True
paragraph.Format.Alignment = 1  # 1=居中

# 表格操作
table = doc.Tables.Add(doc.Range(0,0), 3, 4)  # 3行4列
table.Cell(1,1).Range.Text = "單元格1"

# 保存
doc.SaveAs(r'C:\test\demo.docx')
doc.Close()
word.Quit()

# 樣式應(yīng)用
styles = doc.Styles
new_style = styles.Add("MyStyle", 1)  # 1=段落樣式
new_style.Font.Name = "宋體"
new_style.Font.Size = 14

# 頁(yè)眉頁(yè)腳
section = doc.Sections(1)
header = section.Footers(win32.constants.wdHeaderFooterPrimary)
header.Range.Text = "公司機(jī)密"

# 郵件合并
doc.MailMerge.MainDocumentType = 0  # 0=普通文檔
doc.MailMerge.OpenDataSource(Name:=r"C:\data\contacts.xlsx")

# 批量替換
word.Selection.Find.Execute("舊文本", False, False, False, False, False, True, 1, True, "新文本", 2)

ppt = win32.Dispatch('PowerPoint.Application')
ppt.Visible = True

# 新建演示文稿
pres = ppt.Presentations.Add()

# 添加幻燈片
slide1 = pres.Slides.Add(1, 1)  # 1=標(biāo)題幻燈片版式
slide1.Shapes(1).TextFrame.TextRange.Text = "主標(biāo)題"
slide1.Shapes(2).TextFrame.TextRange.Text = "副標(biāo)題"

# 添加內(nèi)容幻燈片
slide2 = pres.Slides.Add(2, 2)  # 2=標(biāo)題和內(nèi)容版式
slide2.Shapes(1).TextFrame.TextRange.Text = "項(xiàng)目列表"
slide2.Shapes(2).TextFrame.TextRange.Text = "- 第一項(xiàng)\n- 第二項(xiàng)"

# 添加圖表
slide3 = pres.Slides.Add(3, 5)  # 5=標(biāo)題和圖表版式
chart = slide3.Shapes(2).Chart
chart.ChartData.Workbook.Worksheets(1).Range("A1:B3").Value = [["產(chǎn)品", "銷量"], ["A", 120], ["B", 150]]

# 保存
pres.SaveAs(r'C:\test\demo.pptx')
pres.Close()
ppt.Quit()

outlook = win32.Dispatch('Outlook.Application')
mail = outlook.CreateItem(0)  # 0=郵件項(xiàng)

mail.Subject = 'Python自動(dòng)化郵件'
mail.Body = '這是一封通過(guò)Python自動(dòng)發(fā)送的郵件'
mail.To = 'recipient@example.com'

# 添加附件
attachment = r'C:\test\report.xlsx'
mail.Attachments.Add(attachment)

# 發(fā)送
mail.Send()

# 或者顯示郵件窗口人工發(fā)送
# mail.Display(True)

outlook = win32.Dispatch('Outlook.Application')
namespace = outlook.GetNamespace("MAPI")
inbox = namespace.GetDefaultFolder(6)  # 6=收件箱

messages = inbox.Items
for message in messages:
    if message.UnRead:  # 未讀郵件
        print(f"主題: {message.Subject}")
        print(f"發(fā)件人: {message.SenderName}")
        print(f"正文: {message.Body[:100]}...")
        
        # 標(biāo)記為已讀
        message.UnRead = False
        message.Save()

try:
    excel = win32.Dispatch('Excel.Application')
    # 其他操作...
except Exception as e:
    print(f"Office自動(dòng)化錯(cuò)誤: {str(e)}")
finally:
    # 確保釋放資源
    if 'excel' in locals():
        excel.Quit()

import os

def kill_process(process_name):
    os.system(f'taskkill /f /im {process_name}')

# 強(qiáng)制結(jié)束Excel進(jìn)程
kill_process('EXCEL.EXE')

# 禁用屏幕刷新和警告提示
excel.ScreenUpdating = False
excel.DisplayAlerts = False

# 執(zhí)行批量操作...

# 操作完成后恢復(fù)
excel.ScreenUpdating = True
excel.DisplayAlerts = True

# 指定特定版本
excel = win32.Dispatch('Excel.Application.16') # Office 2016

path = r'C:\測(cè)試\文檔.xlsx'.encode('utf-8').decode('latin1') 
wb = excel.Workbooks.Open(path)

def generate_reports(template_path, output_dir, data_list):
    word = win32.Dispatch('Word.Application')
    
    for data in data_list:
        doc = word.Documents.Open(template_path)
        
        # 替換模板標(biāo)記
        for field, value in data.items():
            word.Selection.Find.Execute(field, False, False, False, False, False, True, 1, True, str(value), 2)
        
        # 保存為單獨(dú)文件
        output_path = f"{output_dir}/report_{data['id']}.docx"
        doc.SaveAs(output_path)
        doc.Close()
    
    word.Quit()

def create_dashboard(data_files, output_file):
    excel = win32.Dispatch('Excel.Application')
    wb = excel.Workbooks.Add()
    
    # 匯總數(shù)據(jù)
    for i, file in enumerate(data_files, 1):
        ws = wb.Worksheets.Add()
        ws.Name = f"Source_{i}"
        
        # 從CSV導(dǎo)入數(shù)據(jù)
        ws.QueryTables.Add(
            Connection=f"TEXT;{file}",
            Destination=ws.Range("A1")
        ).Refresh()
    
    # 創(chuàng)建匯總表
    summary_ws = wb.Worksheets.Add()
    summary_ws.Name = "Summary"
    
    # 添加數(shù)據(jù)透視表
    pivot_cache = wb.PivotCaches().Create(1, wb.Sheets("Source_1").UsedRange)
    pivot_table = pivot_cache.CreatePivotTable(summary_ws.Range("A3"), "DataSummary")
    
    # 配置透視表字段
    pivot_table.PivotFields("Category").Orientation = 1  # 行標(biāo)簽
    pivot_table.PivotFields("Value").Orientation = 4    # 數(shù)值
    
    wb.SaveAs(output_file)
    wb.Close()
    excel.Quit()