kaalia0912/dsh-data-analysis-mode/xlsx-tools

# 本机有 Microsoft Excel（推荐）：用 Excel COM 重算（本技能自带脚本）
python scripts/recalc_excel.py output.xlsx
 
# 没有 Excel：用 LibreOffice 重算（soffice 需在 PATH；转换过程会计算公式并写回缓存值）
soffice --headless --convert-to "xlsx:Calc MS Excel 2007 XML" --outdir . output.xlsx

import openpyxl
wb = openpyxl.load_workbook("a.xlsx")            # 默认含公式文本
wb = openpyxl.load_workbook("a.xlsx", data_only=True)  # 只读缓存值（未重算则为 None）
ws = wb.active
ws["B2"] = "=SUM(B3:B99)"
wb.save("a.xlsx")
# 新建
wb2 = openpyxl.Workbook(); ws2 = wb2.active
ws2.title = "汇总"

import pandas as pd
df = pd.read_excel("a.xlsx", sheet_name="数据")          # 读指定 sheet
all_sheets = pd.read_excel("a.xlsx", sheet_name=None)    # 全部 sheet -> dict
df.to_excel("out.xlsx", index=False, sheet_name="结果")   # 写出
# 多 sheet 一次写
with pd.ExcelWriter("out.xlsx") as writer:
    df1.to_excel(writer, sheet_name="A", index=False)
    df2.to_excel(writer, sheet_name="B", index=False)

from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
hdr = ws["A1"]
hdr.font = Font(bold=True, color="FFFFFF")
hdr.fill = PatternFill("solid", fgColor="4472C4")
ws.merge_cells("A1:D1")
ws["A1"].alignment = Alignment(horizontal="center")
# 列宽
for col in range(1, 5):
    ws.column_dimensions[get_column_letter(col)].width = 14

from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=13)
cats = Reference(ws, min_col=1, min_row=2, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F2")