pip install spire.xls.free

from spire.xls import Workbook

# 創(chuàng)建一個新的工作簿
wb = Workbook()
sheet = wb.Worksheets[0]
sheet.Name = "銷售數(shù)據(jù)"

# 保存初始文件
wb.SaveToFile("SalesData.xlsx")
wb.Dispose()
print("Excel 文件已創(chuàng)建：SalesData.xlsx")

from spire.xls import Workbook

wb = Workbook()
sheet = wb.Worksheets[0]
sheet.Name = "銷售數(shù)據(jù)"

# 寫入表頭
headers = ["地區(qū)", "產(chǎn)品", "銷售額 (萬元)"]
for col, header in enumerate(headers, start=1):
    sheet.Range[1, col].Text = header

# 寫入示例數(shù)據(jù)
sales_data = [
    ["華東", "筆記本電腦", 120],
    ["華東", "平板電腦", 85],
    ["華北", "筆記本電腦", 95],
    ["華北", "平板電腦", 70],
    ["華南", "筆記本電腦", 110],
    ["華南", "平板電腦", 90],
]

for row, data in enumerate(sales_data, start=2):
    for col, value in enumerate(data, start=1):
        if isinstance(value, str):
            sheet.Range[row, col].Value = value
        else:
            sheet.Range[row, col].NumberValue = value

# 自動調(diào)整列寬
sheet.Range.AutoFitColumns()

wb.SaveToFile("SalesData.xlsx")
wb.Dispose()
print("業(yè)務(wù)數(shù)據(jù)已寫入 Excel 文件")

from spire.xls import Workbook, ExcelChartType, Color

wb = Workbook()
wb.LoadFromFile("SalesData.xlsx")
sheet = wb.Worksheets[0]

# 添加柱狀圖
chart = sheet.Charts.Add()
chart.DataRange = sheet.Range["A1:C7"]  # 包含表頭和數(shù)據(jù)
chart.SeriesDataFromRange = False        # 按列獲取系列數(shù)據(jù)

# 設(shè)置圖表位置
chart.LeftColumn = 1
chart.TopRow = 8
chart.RightColumn = 10
chart.BottomRow = 25

# 設(shè)置圖表類型
chart.ChartType = ExcelChartType.ColumnClustered
chart.ChartTitle = "各地區(qū)產(chǎn)品銷售額對比"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# 設(shè)置軸標(biāo)題
chart.PrimaryCategoryAxis.Title = "地區(qū)"
chart.PrimaryValueAxis.Title = "銷售額 (萬元)"

# 設(shè)置顏色與數(shù)據(jù)標(biāo)簽
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

wb.SaveToFile("SalesChart_Column.xlsx")
wb.Dispose()
print("柱狀圖創(chuàng)建完成：SalesChart_Column.xlsx")

from spire.xls import Workbook, ExcelChartType

wb = Workbook()
wb.LoadFromFile("SalesData.xlsx")
sheet = wb.Worksheets[0]

chart_line = sheet.Charts.Add()
chart_line.DataRange = sheet.Range["A1:C7"]
chart_line.SeriesDataFromRange = False

chart_line.LeftColumn = 1
chart_line.TopRow = 9
chart_line.RightColumn = 9
chart_line.BottomRow = 29
chart_line.ChartType = ExcelChartType.Line
chart_line.ChartTitle = "銷售趨勢分析"
chart_line.ChartTitleArea.IsBold = True
chart_line.ChartTitleArea.Size = 12
chart_line.PrimaryCategoryAxis.Title = "地區(qū)"
chart_line.PrimaryValueAxis.Title = "銷售額 (萬元)"

wb.SaveToFile("SalesChart_Line.xlsx")
wb.Dispose()
print("折線圖創(chuàng)建完成：SalesChart_Line.xlsx")

from spire.xls import Workbook, ExcelChartType

wb = Workbook()
wb.LoadFromFile("SalesData.xlsx")
sheet = wb.Worksheets[0]

chart_pie = sheet.Charts.Add()
chart_pie.DataRange = sheet.Range["B2:C7"]  # 產(chǎn)品與銷售額
chart_pie.SeriesDataFromRange = False
chart_pie.LeftColumn = 1
chart_pie.TopRow = 10
chart_pie.RightColumn = 8
chart_pie.BottomRow = 30
chart_pie.ChartType = ExcelChartType.Pie
chart_pie.ChartTitle = "產(chǎn)品銷售占比"
chart_pie.ChartTitleArea.IsBold = True
chart_pie.ChartTitleArea.Size = 12

# 顯示類別和百分比
chart_pie.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = True
chart_pie.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasPercentage = True

wb.SaveToFile("SalesChart_Pie.xlsx")
wb.Dispose()
print("餅圖創(chuàng)建完成：SalesChart_Pie.xlsx")

from spire.xls import Workbook, ExcelChartType

wb = Workbook()
wb.LoadFromFile("SalesData.xlsx")
sheet = wb.Worksheets[0]

# 增加利潤率列
profit_rates = [0.15, 0.12, 0.13, 0.10, 0.14, 0.11]
for i, rate in enumerate(profit_rates, start=2):
    sheet.Range[i, 4].NumberValue = rate
sheet.Range[1, 4].Text = "利潤率"

chart_bubble = sheet.Charts.Add(ExcelChartType.Bubble)
chart_bubble.DataRange = sheet.Range["B1:D7"]
chart_bubble.SeriesDataFromRange = False
chart_bubble.Series[0].Bubbles = sheet.Range["D2:D7"]  # 氣泡大小
chart_bubble.LeftColumn = 1
chart_bubble.TopRow = 10
chart_bubble.RightColumn = 11
chart_bubble.BottomRow = 29
chart_bubble.ChartTitle = "銷售額與利潤率氣泡圖"
chart_bubble.ChartTitleArea.IsBold = True
chart_bubble.ChartTitleArea.Size = 12

wb.SaveToFile("SalesChart_Bubble.xlsx")
wb.Dispose()
print("氣泡圖創(chuàng)建完成：SalesChart_Bubble.xlsx")