pip install Spire.XLS

pip install pandas openpyxl

from spire.xls import *
from spire.xls.common import *

# 加載數(shù)據(jù)文件
workbook = Workbook()
workbook.LoadFromFile("SalesData.xlsx")
sheet = workbook.Worksheets[0]

# 創(chuàng)建透視表緩存
data_range = sheet.Range["A1:E1000"]  # 假設(shè)數(shù)據(jù)有1000行
cache = workbook.PivotCaches.Add(data_range)

# 新建工作表存放透視表
pv_sheet = workbook.Worksheets.Add("銷(xiāo)售透視表")
pivot_table = pv_sheet.PivotTables.Add("SalesAnalysis", pv_sheet.Range["A3"], cache)

# 設(shè)置行列字段
pivot_table.PivotFields["區(qū)域"].Axis = AxisTypes.Row
pivot_table.PivotFields["產(chǎn)品"].Axis = AxisTypes.Column

# 添加值字段（求和）
sales_field = pivot_table.PivotFields["銷(xiāo)售額"]
pivot_table.DataFields.Add(sales_field, "總銷(xiāo)售額", SubtotalTypes.Sum)

# 應(yīng)用樣式
pivot_table.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9
workbook.SaveToFile("SalesPivot.xlsx")

import pandas as pd

# 讀取數(shù)據(jù)（假設(shè)數(shù)據(jù)已清洗）
df = pd.read_excel("SalesData.xlsx")

# 創(chuàng)建透視表：按季度和產(chǎn)品統(tǒng)計(jì)銷(xiāo)售額
pivot = pd.pivot_table(
    df,
    index=["季度"],  # 行字段
    columns=["產(chǎn)品"],  # 列字段
    values="銷(xiāo)售額",  # 計(jì)算字段
    aggfunc="sum",  # 聚合方式
    fill_value=0  # 空值填充
)

# 保存結(jié)果
pivot.to_excel("QuarterlySales.xlsx")

pivot = pd.pivot_table(
    df,
    index=["區(qū)域", "產(chǎn)品"],
    values=["銷(xiāo)售額", "利潤(rùn)", "銷(xiāo)售量"],
    aggfunc={
        "銷(xiāo)售額": "sum",
        "利潤(rùn)": "mean",
        "銷(xiāo)售量": "count"
    }
)

# 先篩選數(shù)據(jù)
filtered_df = df[df["銷(xiāo)售額"] > 10000]

# 創(chuàng)建透視表并排序
pivot = pd.pivot_table(
    filtered_df,
    index="產(chǎn)品",
    values="利潤(rùn)",
    aggfunc="sum"
).sort_values("利潤(rùn)", ascending=False)

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill

# 加載文件
wb = load_workbook("QuarterlySales.xlsx")
ws = wb.active

# 設(shè)置標(biāo)題樣式
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="4F81BD")
    cell.alignment = Alignment(horizontal="center")

# 設(shè)置數(shù)字格式
for row in ws.iter_rows(min_row=2):
    for cell in row:
        if isinstance(cell.value, (int, float)):
            cell.number_format = '#,##0'

wb.save("StyledPivot.xlsx")

chunk_size = 50000  # 每次讀取5萬(wàn)行
results = []

for chunk in pd.read_excel("LargeSalesData.xlsx", chunksize=chunk_size):
    # 對(duì)每個(gè)數(shù)據(jù)塊創(chuàng)建透視表
    pivot = pd.pivot_table(
        chunk,
        index="產(chǎn)品",
        values="銷(xiāo)售額",
        aggfunc="sum"
    )
    results.append(pivot)

# 合并結(jié)果
final_pivot = pd.concat(results).groupby(level=0).sum()
final_pivot.to_excel("LargeDataPivot.xlsx")

import dask.dataframe as dd

# 讀取數(shù)據(jù)（自動(dòng)分塊）
ddf = dd.read_excel("HugeData.xlsx")

# 創(chuàng)建透視表（延遲計(jì)算）
pivot = dd.pivot_table(
    ddf,
    index="產(chǎn)品",
    values="銷(xiāo)售額",
    aggfunc="sum"
)

# 計(jì)算并保存
pivot.compute().to_excel("DaskPivot.xlsx")

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2016, FileFormat.XlsxOpenXML)

from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler

class FileChangeHandler(FileSystemEventHandler):
    def on_modified(self, event):
        if event.src_path.endswith(".xlsx"):
            # 重新生成透視表
            update_pivot_table()

observer = Observer()
observer.schedule(FileChangeHandler(), path="./data")
observer.start()

pivot_table.RefreshData()  # 重新計(jì)算透視表數(shù)據(jù)

from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler

class FileChangeHandler(FileSystemEventHandler):
    def on_modified(self, event):
        if event.src_path.endswith(".xlsx"):
            # 重新生成透視表
            update_pivot_table()

observer = Observer()
observer.schedule(FileChangeHandler(), path="./data")
observer.start()

# 綜合處理方案
pivot = pd.pivot_table(
    df.fillna({
        "銷(xiāo)售額": 0,
        "利潤(rùn)": df["利潤(rùn)"].mean()  # 用均值填充利潤(rùn)空值
    }),
    index="產(chǎn)品",
    values="銷(xiāo)售額",
    aggfunc="sum"
)

pivot = pd.pivot_table(
    df,
    index=["門(mén)店名稱(chēng)", "品類(lèi)"],
    values="銷(xiāo)售額",
    aggfunc="sum",
    margins=True  # 顯示總計(jì)行
)
# 計(jì)算占比
pivot["占比"] = pivot["銷(xiāo)售額"] / pivot["銷(xiāo)售額"]["All"]

# 計(jì)算逾期率
df["逾期率"] = df["逾期金額"] / df["貸款金額"]

pivot = pd.pivot_table(
    df,
    index=["年齡組", "信用等級(jí)"],
    values="逾期率",
    aggfunc="mean"
)
# 條件格式標(biāo)記高風(fēng)險(xiǎn)群體
def highlight_risk(val):
    color = "red" if val > 0.05 else "green"
    return f"background-color: {color}"

styled_pivot = pivot.style.applymap(highlight_risk)
styled_pivot.to_excel("RiskAnalysis.xlsx")

from sklearn.feature_selection import mutual_info_classif

# 計(jì)算字段間的相關(guān)性
features = ["產(chǎn)品", "區(qū)域", "季度"]
target = "銷(xiāo)售額"
mi_scores = mutual_info_classif(df[features], df[target])

# 推薦高相關(guān)性字段
recommended_fields = [features[i] for i in mi_scores.argsort()[::-1][:2]]

# 示例指令："按產(chǎn)品分類(lèi)統(tǒng)計(jì)銷(xiāo)售額，并計(jì)算利潤(rùn)率"
def generate_pivot_from_query(query):
    if "產(chǎn)品" in query and "銷(xiāo)售額" in query:
        index = "產(chǎn)品"
        values = "銷(xiāo)售額"
    if "利潤(rùn)率" in query:
        aggfunc = {"銷(xiāo)售額": "sum", "利潤(rùn)": "mean"}
        # 計(jì)算利潤(rùn)率字段
        df["利潤(rùn)率"] = df["利潤(rùn)"] / df["銷(xiāo)售額"]
        values.append("利潤(rùn)率")
    
    return pd.pivot_table(df, index=index, values=values, aggfunc=aggfunc)