pip install openpyxl

from openpyxl import Workbook
# 1. 創(chuàng)建一個新的工作簿（Workbook）對象
wb = Workbook()
# 默認(rèn)會創(chuàng)建一個名為‘Sheet'的工作表，我們通過 active 屬性獲取它
ws = wb.active
ws.title = "我的第一個Sheet" # 給工作表重命名
# 2. 操作單元格（Cell）
# 方法一：通過類似字典的鍵（單元格坐標(biāo)）來訪問
ws['A1'] = "你好，Excel！"
# 方法二：使用 .cell(row, column, value) 方法，更便于循環(huán)
ws.cell(row=2, column=1, value="這是第二行") # 相當(dāng)于 A2
# 3. 保存工作簿到文件
wb.save("hello_openpyxl.xlsx")
print("Excel文件已生成！")

from openpyxl import load_workbook
# 加載一個已存在的Excel文件
wb = load_workbook(filename='example.xlsx') # 假設(shè)此文件存在
# 獲取所有工作表的名稱
sheet_names = wb.sheetnames
print(f"所有工作表：{sheet_names}")
# 通過名稱獲取特定工作表
ws = wb['Sheet1'] # 或 wb[sheet_names[0]]
# 讀取單元格內(nèi)容
cell_value = ws['B5'].value
print(f"B5單元格的值是：{cell_value}")
# 讀取一系列單元格：使用切片或iter_rows
for row in ws['A1':'C3']: # 讀取A1到C3這個矩形區(qū)域
    for cell in row:
        print(cell.coordinate, cell.value) # 打印坐標(biāo)和值
    print("---行結(jié)束---")

# 假設(shè)我們要處理一個員工信息表
data_to_write = [
    ['工號', '姓名', '部門', '薪資'],
    ['001', '張三', '技術(shù)部', 15000],
    ['002', '李四', '市場部', 12000],
    ['003', '王五', '技術(shù)部', 18000],
]
# 從第1行開始，寫入數(shù)據(jù)
start_row = 1
for i, row_data in enumerate(data_to_write, start=start_row):
    for j, cell_value in enumerate(row_data, start=1): # 列從1開始
        ws.cell(row=i, column=j, value=cell_value)
# 在末尾追加一行數(shù)據(jù)（獲取最大行號）
max_row = ws.max_row
ws.cell(row=max_row+1, column=1, value='004')
ws.cell(row=max_row+1, column=2, value='趙六')
# ... 可以繼續(xù)寫入其他列
wb.save('updated_example.xlsx')

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.styles import numbers
# 1. 設(shè)置字體和填充（表頭樣式）
header_font = Font(name='微軟雅黑', size=12, bold=True, color='FFFFFF') # 白色加粗
header_fill = PatternFill(fill_type='solid', fgColor='366092') # 深藍(lán)色填充
for cell in ws[1]: # 遍歷第一行的所有單元格
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', vertical='center') # 居中
# 2. 設(shè)置數(shù)字格式（例如，將薪資列設(shè)置為千位分隔符和兩位小數(shù)）
salary_column = 4 # 假設(shè)薪資在第4列
for row in ws.iter_rows(min_row=2, min_col=salary_column, max_col=salary_column):
    for cell in row:
        cell.number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1 # '#,##0.00'
# 3. 設(shè)置邊框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
# 為A1到D5區(qū)域設(shè)置邊框
for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border
# 4. 調(diào)整列寬和行高
ws.column_dimensions['B'].width = 15 # 設(shè)置B列寬
ws.row_dimensions[1].height = 25 # 設(shè)置第1行高

# 在E2單元格插入求和公式，計(jì)算B2到D2的和
ws['E2'] = '=SUM(B2:D2)'
# 在F列計(jì)算薪資的稅率（假設(shè)稅率為10%）
for row in range(2, ws.max_row + 1):
    salary_cell = ws.cell(row=row, column=4) # 薪資列
    tax_cell = ws.cell(row=row, column=6) # 稅率結(jié)果列
    tax_cell.value = f'={salary_cell.coordinate} * 0.1' # 如 =D2*0.1

from openpyxl.chart import BarChart, Reference
# 假設(shè)數(shù)據(jù)在A1到D4區(qū)域，A列為類別，B-D列為數(shù)據(jù)系列
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=4) # B1:D4
categories = Reference(ws, min_col=1, min_row=2, max_row=4) # A2:A4
# 創(chuàng)建柱狀圖
chart = BarChart()
chart.title = "部門業(yè)績對比"
chart.x_axis.title = "部門"
chart.y_axis.title = "業(yè)績"
chart.add_data(data, titles_from_data=True) # 從數(shù)據(jù)第一行讀取系列標(biāo)題
chart.set_categories(categories)
# 將圖表插入到工作表的 F1 單元格位置
ws.add_chart(chart, "F1")

import os
from openpyxl import load_workbook

source_folder = './daily_reports/'
output_file = './merged_report.xlsx'

# 創(chuàng)建一個新的工作簿用于存放合并結(jié)果
merged_wb = Workbook()
merged_ws = merged_wb.active
merged_ws.title = '合并數(shù)據(jù)'
header_written = False # 標(biāo)記表頭是否已寫入

# 遍歷文件夾下所有.xlsx文件
for filename in os.listdir(source_folder):
    if filename.endswith('.xlsx'):
        filepath = os.path.join(source_folder, filename)
        print(f'正在處理：{filename}')
        
        wb = load_workbook(filepath, data_only=True)
        ws = wb.active
        
        # 如果是第一個文件，寫入表頭
        if not header_written:
            for row in ws.iter_rows(min_row=1, max_row=1, values_only=True):
                merged_ws.append(row) # append方法可以按行添加一個可迭代對象
            header_written = True
        
        # 從第二行開始，寫入數(shù)據(jù)
        for row in ws.iter_rows(min_row=2, values_only=True):
            merged_ws.append(row)

merged_wb.save(output_file)
print(f'所有文件合并完成，結(jié)果保存在：{output_file}')

# 1. 創(chuàng)建和刪除工作表
wb.create_sheet(title='月度匯總', index=0) # 在第一個位置創(chuàng)建
if '無用Sheet' in wb.sheetnames:
    useless_sheet = wb['無用Sheet']
    wb.remove(useless_sheet) # 刪除工作表

# 2. 復(fù)制工作表內(nèi)容
source = wb['原始數(shù)據(jù)']
target = wb.create_sheet('備份數(shù)據(jù)')
for row in source.iter_rows(values_only=True):
    target.append(row)

import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers
from openpyxl.chart import BarChart, Reference

def generate_monthly_salary_report():
    """生成月度薪資報(bào)告主函數(shù)"""
    # 1. 加載原始數(shù)據(jù)
    raw_wb = load_workbook('raw_salary.xlsx', data_only=True)
    raw_ws = raw_wb.active
    
    # 數(shù)據(jù)結(jié)構(gòu)：按部門分組，存儲員工薪資列表
    dept_salary_dict = {}
    company_total = 0
    employee_count = 0
    
    # 2. 數(shù)據(jù)清洗與分組 (假設(shè)數(shù)據(jù)從第2行開始，A:姓名 B:部門 C:薪資)
    for row in raw_ws.iter_rows(min_row=2, values_only=True):
        name, dept, salary = row
        # 清洗：跳過測試部門和薪資為空的數(shù)據(jù)
        if dept == '測試部' or salary is None:
            continue
        # 分組
        if dept not in dept_salary_dict:
            dept_salary_dict[dept] = []
        dept_salary_dict[dept].append(salary)
        
        # 計(jì)算公司總和，用于后續(xù)求平均
        company_total += salary
        employee_count += 1
    
    if employee_count == 0:
        print("沒有有效數(shù)據(jù)！")
        return
    
    company_avg = company_total / employee_count
    
    # 3. 創(chuàng)建報(bào)告工作簿
    report_wb = Workbook()
    # 移除默認(rèn)sheet，創(chuàng)建匯總sheet
    default_sheet = report_wb.active
    report_wb.remove(default_sheet)
    summary_ws = report_wb.create_sheet('部門匯總')
    
    # 4. 寫入?yún)R總表頭并設(shè)置樣式
    headers = ['部門', '員工數(shù)', '總薪資', '平均薪資', '最高薪資', '超過公司平均']
    for col_idx, header in enumerate(headers, start=1):
        cell = summary_ws.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(fill_type='solid', fgColor='DDDDDD')
        cell.alignment = Alignment(horizontal='center')
    
    # 5. 計(jì)算并寫入各部門數(shù)據(jù)
    row_idx = 2
    highlight_fill = PatternFill(fill_type='solid', fgColor='FFFF00') # 黃色高亮
    
    for dept, salaries in dept_salary_dict.items():
        emp_count = len(salaries)
        dept_total = sum(salaries)
        dept_avg = dept_total / emp_count
        dept_max = max(salaries)
        is_above_avg = dept_avg > company_avg
        
        summary_ws.cell(row=row_idx, column=1, value=dept)
        summary_ws.cell(row=row_idx, column=2, value=emp_count)
        summary_ws.cell(row=row_idx, column=3, value=dept_total).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=4, value=dept_avg).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=5, value=dept_max).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=6, value='是' if is_above_avg else '否')
        
        # 如果超過公司平均，高亮該行平均薪資單元格
        if is_above_avg:
            summary_ws.cell(row=row_idx, column=4).fill = highlight_fill
        
        # 6. 為每個部門創(chuàng)建詳細(xì)數(shù)據(jù)工作表
        detail_ws = report_wb.create_sheet(title=dept[:31]) # 工作表名最多31字符
        detail_ws.append(['姓名', '薪資'])
        for salary in salaries:
            # 在真實(shí)場景中，這里應(yīng)該對應(yīng)姓名，本例簡化處理
            detail_ws.append([f'員工{row_idx}', salary])
        
        row_idx += 1
    
    # 7. 在匯總表末尾添加公司平均數(shù)據(jù)
    footer_row = row_idx + 1
    summary_ws.cell(row=footer_row, column=1, value='公司整體')
    summary_ws.cell(row=footer_row, column=2, value=employee_count)
    summary_ws.cell(row=footer_row, column=3, value=company_total).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
    summary_ws.cell(row=footer_row, column=4, value=company_avg).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
    summary_ws.cell(row=footer_row, column=4).font = Font(bold=True, color='FF0000')
    
    # 8. 為匯總表創(chuàng)建圖表（各部門平均薪資對比）
    chart = BarChart()
    chart.title = "各部門平均薪資對比"
    chart.x_axis.title = "部門"
    chart.y_axis.title = "平均薪資"
    
    # 數(shù)據(jù)范圍：A列部門名（從第2行到倒數(shù)第二行），D列平均薪資
    data = Reference(summary_ws, min_col=4, min_row=2, max_row=row_idx-1)
    categories = Reference(summary_ws, min_col=1, min_row=2, max_row=row_idx-1)
    chart.add_data(data, titles_from_data=False)
    chart.set_categories(categories)
    summary_ws.add_chart(chart, f"H2")
    
    # 9. 調(diào)整列寬
    for column in summary_ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2)
        summary_ws.column_dimensions[column_letter].width = adjusted_width
    
    # 10. 保存報(bào)告
    report_wb.save('月度薪資分析報(bào)告.xlsx')
    print(f"報(bào)告生成成功！已處理 {len(dept_salary_dict)} 個部門的數(shù)據(jù)。")

if __name__ == '__main__':
    generate_monthly_salary_report()