pip install spire.xls.free

from spire.xls import *

from spire.xls import *

# 創(chuàng)建新工作簿
workbook = Workbook()

# 獲取第一個工作表
sheet = workbook.Worksheets[0]
sheet.Name = "銷售數據"  # 設置工作表名稱

# 添加表頭并設置樣式
headers = ["產品", "數量", "單價", "折扣 (%)", "小計", "折后總價"]
for col, header in enumerate(headers, start=1):
    cell = sheet.Range[1, col]                      
    cell.Text = header                               
    cell.Style.Font.IsBold = True                    
    cell.Style.HorizontalAlignment = HorizontalAlignType.Center  
    cell.Style.Color = Color.FromRgb(200, 200, 250)  

# 添加示例數據并設置對齊方式
data = [
    ("鍵盤", 10, 25, 5, "", ""),
    ("鼠標", 15, 12, 0, "", ""),
    ("顯示器", 8, 150, 10, "", "需檢查"),
    ("USB 數據線", 20, 5, 0, "", ""),
    ("筆記本包", 5, 40, 15, "", "")
]

for row, row_data in enumerate(data, start=2):
    for col, value in enumerate(row_data, start=1):
        cell = sheet.Range[row, col]
        if isinstance(value, (int, float)):
            cell.NumberValue = value                    
            if col > 1:                                
                cell.Style.HorizontalAlignment = HorizontalAlignType.Right
        else:
            cell.Text = value                            
            cell.Style.HorizontalAlignment = HorizontalAlignType.Left  

# 添加計算公式
for i in range(2, 7):
    sheet.Range[f"E{i}"].Formula = f"=B{i}*C{i}"          
    sheet.Range[f"F{i}"].Formula = f"=E{i}*(1-D{i}/100)" 

# 添加總計和平均值
sheet.Range["E7"].Formula = "=SUM(E2:E6)"
sheet.Range["F7"].Formula = "=SUM(F2:F6)"
sheet.Range["E8"].Formula = "=AVERAGE(E2:E6)"
sheet.Range["F8"].Formula = "=AVERAGE(F2:F6)"

# 高亮顯示總計和平均值
for cell_address in ["E7", "F7", "E8", "F8"]:
    cell = sheet.Range[cell_address]
    cell.Style.Font.IsBold = True
    cell.Style.Color = Color.FromRgb(220, 230, 241)

# 計算所有公式
sheet.CalculateAllValue()

# 設置所有行高
for row in range(1, sheet.LastRow + 1):
    sheet.Rows[row - 1].RowHeight = 15  

# 設置所有列寬
for col in range(1, sheet.LastColumn + 1):
    sheet.Columns[col - 1].ColumnWidth = 10 

# 保存工作簿
workbook.SaveToFile("公式.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

from spire.xls import *

# 創(chuàng)建工作簿對象
workbook = Workbook()

# 加載現有 Excel 文件
workbook.LoadFromFile("公式.xlsx")

# 獲取第一個工作表
sheet = workbook.Worksheets[0]

# 獲取工作表中已用的單元格范圍
usedRange = sheet.AllocatedRange

print("=== 讀取現有公式 ===")

# 遍歷已用范圍內的所有單元格
for cell in usedRange:
    if cell.HasFormula:
        print(f"單元格 {cell.RangeAddressLocal} 公式: {cell.Formula}")
        print(f"計算結果: {cell.FormulaValue}")

# 釋放工作簿資源
workbook.Dispose()

from spire.xls import *

# 加載現有工作簿
workbook = Workbook()
workbook.LoadFromFile("公式.xlsx")

# 訪問第一個工作表
sheet = workbook.Worksheets[0]

# 更新現有數據
sheet.Range["C2"].NumberValue = 27   # 修改鍵盤單價
sheet.Range["D4"].NumberValue = 12   # 修改顯示器折扣

# 修改現有公式
sheet.Range["F6"].Formula = "=E6*(1-D6/100)+5"  # 筆記本包增加額外費用

# 添加新公式
sheet.Range["G2"].Formula = "=C2*B2*0.1"  # 示例：小計的 10% 傭金

# 在工作表級別重新計算所有公式
sheet.CalculateAllValue()  

print("=== 更新公式 ===")

# 獲取并打印更新后的值
print(f"鍵盤折后總價 (F2): {sheet.Range['F2'].FormulaValue}")
print(f"顯示器折后總價 (F4): {sheet.Range['F4'].FormulaValue}")
print(f"筆記本包折后總價（含額外費用）(F6): {sheet.Range['F6'].FormulaValue}")
print(f"新增傭金 (G2): {sheet.Range['G2'].FormulaValue}")

# 保存更新后的工作簿
workbook.SaveToFile("更新公式.xlsx", ExcelVersion.Version2016)
workbook.Dispose()