Python3 读取和写入excel

一、Excel

1、Excel文件三个对象

workbook： 工作簿，一个excel文件包含多个sheet。
sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。
cell： 单元格，存储数据对象

2、excel定义的图

excel定义的图分两级类别描述，第一级分别有九大类，如下所示

area: 面积图
bar: 转置直方图
column: 柱状图
line: 直线图
pie: 饼状图
doughnut: 环形图
scatter: 散点图
stock: 股票趋势图
radar: 雷达图

3、csv格式读写excel数据

现在我们已经在 Python 中拿到了想要的数据，对于这些数据我们可以先存放起来，比如把数据写入 csv 中。定义一个 writeDate 方法：

import csv #导入包

def writeData(data, name):
    with open(name, 'a', errors='ignore', newline='') as f:
            f_csv = csv.writer(f)
            f_csv.writerows(data)
    print('write_csv success')

writeData(result, 'D:/py_work/venv/Include/weather.csv') #数据写入到 csv文档中

二、python与excel

1、python处理excel主流代表有：

二、openpyxl基本用法

openpyxl专门处理Excel2007及以上版本产生的xlsx文件，可读可写excel表。

openpyxl定义了多种数据格式其中最重要的三种：
NULL空值：对应于python中的None，表示这个cell里面没有数据。
numberic： 数字型，统一按照浮点数来进行处理。对应于python中的float。
string： 字符串型，对应于python中的unicode。

openpyxl中有三个不同层次的类：
Workbook是对工作簿的抽象，
Worksheet是对表格的抽象，
Cell是对单元格的抽象，

1、Workbook：

一个Workbook对象代表一个Excel文档，因此在操作Excel之前，都应该先创建一个Workbook对象。
对于创建一个新的Excel文档，直接进行Workbook类的调用即可，对于一个已经存在的Excel文档，可以使用openpyxl模块的load_workbook函数进行读取。
一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)。

1.1Workbook属性：

●active：获取当前活跃的Worksheet
●worksheets：以列表的形式返回所有的Worksheet(表格)
●read_only：判断是否以read_only模式打开Excel文档
●encoding：获取文档的字符集编码
●properties：获取文档的元数据，如标题，创建者，创建日期等
●sheetnames：获取工作簿中的表（列表）

1.2Workbook方法（大部分方法都与sheet有关）：

●get_sheet_names：获取所有表格的名称(新版已经不建议使用，通过Workbook的sheetnames属性即可获取)
●get_sheet_by_name：通过表格名称获取Worksheet对象(新版也不建议使用，通过Worksheet[‘表名‘]获取)
●get_active_sheet：获取活跃的表格(新版建议通过active属性获取)
●remove_sheet：删除一个表格
●create_sheet：创建一个空的表格
●copy_worksheet：在Workbook内拷贝表格

 

2、Worksheet：

有了Worksheet对象以后，我们可以通过这个Worksheet对象获取表格的属性，得到单元格中的数据，修改表格中的内容。

2.1Worksheet属性：

●title：表格的标题
●row_dimensions[2].height = 40 # 第2行行高
●column_dimensions['C'].width = 30 # C列列宽
●max_row：表格的最大行
●min_row：表格的最小行
●max_column：表格的最大列
●min_column：表格的最小列
●rows：按行获取单元格(Cell对象) - 生成器
●columns：按列获取单元格(Cell对象) - 生成器
●freeze_panes：冻结窗格
●values：按行获取表格的内容(数据) - 生成器

2.2Worksheet方法:

●iter_rows：按行获取所有单元格，内置属性有(min_row,max_row,min_col,max_col)
●iter_columns：按列获取所有的单元格
●append：在表格末尾添加数据
●merged_cells：合并多个单元格
●unmerged_cells：移除合并的单元格

3、Cell：

3.1Cell属性:

●row：单元格所在的行
●column：单元格坐在的列
●value：单元格的值
●coordinate：单元格的坐标  # excel2[‘abc‘].cell(row=1,column=2).coordinate

3.2单元格样式

openpyxl的单元格样式由6种属性决定，每一种都是一个类，如下所示：

●font(字体类)：字号、字体颜色、下划线等
●fill(填充类)：颜色等
●border(边框类)：设置单元格边框
●alignment(位置类)：对齐方式
●number_format(格式类)：数据格式
●protection(保护类)：写保护

基本字体颜色
字体颜色有一些颜色常量，可以直接调用:
from openpyxl.styles import Font
from openpyxl.styles.colors import RED
font = Font(color=RED)
font = Font(color="00FFBB00")

4、openpyxl图表：
Area Charts: 面积图
Bar and Column Charts : 转置直方图
Bubble Charts
Line Charts: 直线图
Scatter Charts: 散点图
Pie Charts: 饼状图
Doughnut Charts: 环形图
Radar Charts: 雷达图
Stock Charts: 股票趋势图
Surface Charts
column: 柱状图

三、使用介绍

3.1、Workbook

re_ex=openpyxl.Workbook() #新建一个工作簿（workbook）
re_ex= openpyxl.Workbook(‘hello.xlxs‘)#新建一个工作簿并命名

re_ex=openpyxl.workbook.Workbook.active() #调用正在运行的工作簿（workbook）

re_ex= openpyxl.load_workbook(‘abc.xlsx‘) #返回一个Workbook对象，即打开一个已有的工作簿

re_ex.save('xxx.xlsx') #保存并关闭工作簿

from openpyxl import load_workbook #读取现有的工作簿
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)

3.2、worksheet

新建sheet（工作表）

ws1 = wb.create_sheet() #新建sheet
ws1 = wb.create_sheet(0) #新建sheet并指定sheet位置次序。系统自动命名，依次为Sheet, Sheet1, Sheet2

ws2 = wb.create_sheet(title="NewTitle") #新建sheet并设定sheet名称
ws2 = wb.create_sheet("NewTitle") #新建sheet并设定sheet名称
ws2 = wb.create_sheet('Data',index=1)#新建工作表并设定sheet名称，指定sheet位置次序

ws2.title = "NewTitle" # 修改sheet表名称，直接赋值即可

wb.remove(sheet) #删除某个工作表

通过sheet名

ws = wb["frequency"] #通过名字打开sheet
ws = wb.get_sheet_by_name('frequency') #通过名字打开sheet

获取名字（用index）

sheet_names = wb.get_sheet_names() #得到工作簿的所有工作表
sheet_names = wb.get_sheet_by_name(sheet_names[index]) # index为0为第一张表
sheet_names.title #获取sheet名

调用正在运行的工作表

ws =wb.active
ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表

3.3读写单元格
当一个工作表被创建时，其中是不包含单元格。只有当单元格被获取时才被创建。

读取单元格

d= ws['A4'] #根据单元格的索引获取单元格，如果不存在将在A4新建一个
d = ws.cell(row = 4, column = 2) #使用cell()方法通过行列获取单元格(行号列号从1开始)
d = ws.cell('A4') #使用cell()方法通过单元格索引获取单元格(行号列号从1开始)
d = ws.max_column #获得最大列
d = ws.max_row)#获得最大行

cell_range = ws['A1':'C2'] #使用切片获取多个单元格
d = get_cell_collection() #读所有单元格数据

print (rows[n]) #显示第n行数据 
print (columns[n]) #显示第n列数据

ws.rows #迭代读取所有行row 
ws.columns #迭代读取所有列column

获取行和列单元格的数据
sheet.rows为生成器,里面是每一行的数据，每一行又由一个tuple。
sheet.columns类似，不过里面是每个tuple是每一列的单元格。

#因为按行，所以返回A1,B1,C1这样的顺序

for row in sheet.rows:

         for cell in row:

                   print(cell.value)

#因为是按列，所以返回A1,A2,A3这样的顺序

for column in sheet.columns:

         for cell in column:

                   print(cell.value)

因为sheet.rows是生成器类型，不能使用索引，转换成list之后再使用索引，list(sheet.rows)[2]这样就获取到第二行的tuple对象。

获得任意区间的单元格
可以使用range函数，下面的写法，获得了以A1为左上角，B3为右下角矩形区域的所有单元格。注意range要从1开始的，因为在openpyxl中为了和Excel保持一致，以1表示第一个值。

for i in range(1,4):

         for j in range(1,3):

                   print(sheet.cell(row=i,column=j))

写入单元格
ws['A4'] = 4  #直接给单元格赋值
ws.cell(row = 4, column = 2).value = 'test' #通过cell函数给单元格赋值
ws.cell(row = 4, column = 2, value = 'test') #通过cell函数给单元格赋值

ws["A1"] = "=SUM(1, 1)"  #通过公式计算产生写入的值
ws["A1"] = "=SUM(B1:C1)" #通过公式计算产生写入的值
ws['B9']='=AVERAGE(B2:B8)' #通过公式计算产生写入的值
但是如果是读取的时候需要加上data_only=True这样读到B9返回的就是数字，如果不加这个参数，返回的将是公式本身'=AVERAGE(B2:B8)'

append函数按行写入

append函数可以一次添加多行数据，从第一行空白行开始（下面都是空白行）写入。append函数只能按行写入

#添加一行
row=[1,2,3,4,5]
sheet.append(row)

#添加多行

import openpyxl
fe=openpyxl.Workbook("C:\Programs\22gg.xlsx")
fs=fe.create_sheet("tt")
rows=[
['Num','a','d'],
[2,40,30],
[3,40,25],
[4,50,30],
[5,30,10],
[6,25,5],
[7,50,10],
]

for row in rows:
    fs.append(row)
fe.save("C:\Programs\22gg.xlsx")

#-*-encoding:utf-*-
import openpyxl

ll=[]
fe=openpyxl.Workbook("C:\Programs\11gg.xlsx")
fs=fe.create_sheet("tt")

with open("C:\Programs\22gg.txt",'r',encoding="utf-8") as fwd:
    for line in fwd.readlines():
        yy=[]
        yy.append(line)#openpyxl中写入Excel的数据需是列表型的，所先先将每行放入列表，再将列表放入列表
        ll.append(yy)

for rr in ll:
    fs.append(rr)
fe.save("C:\Programs\11gg.xlsx")

如果我们想按列写入呢。如果把上面的列表嵌套看作矩阵。只要将矩阵转置就可以了。使用zip()函数可以实现，不过内部的列表变成了元组就是了。都是可迭代对象，不影响。

sheet.append(list(zip(*rows)))

合并单元格cell
合并单元格，合并后只可以往左上角写入数据，也就是区间中左边的坐标。如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃

wb = Workbook()
ws = wb.active

sheet.merge_cells('B1:G1')#合并一行中的几个单元格
sheet.merge_cells('A1:C3')#合并一个矩形区域中的单元格

# or
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

拆分单元格cell
拆分后，值回到A1位置。
sheet.unmerge_cells('A1:C3')

应用样式
可以直接应用到单元格:
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
wb = Workbook()
ws = wb.active
c = ws['A1']
c.font = Font(size=12)

可以对整行整列设置样式，前提是单元格已创建。
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")

复制样式样：
from openpyxl.styles import Font
ft1 = Font(name='Arial', size=14)
ft2 = ft1.copy(name="Tahoma") # 复制时指定字体为“Tahoma”，其他属性均复制自ft1

数据格式
数据格式属性number_format的值是字符串类型，不为对象，直接赋值即可。
openpyxl内置了一些数据格式查看openpyxl.styles.numbers，也支持excel自定义格式，以下两种方式效果相同：

# 使用openpyxl内置的格式
from openpyxl.styles import numbers
ws.cell['D2'].number_format = numbers.FORMAT_GENERAL
ws.cell(row=2, column=4).number_format = numbers.FORMAT_DATE_XLSX15

# 直接使用字符串

import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws.cell['D2].number_format = 'General'
ws['A1'] = datetime.datetime(2010, 7, 21)
ws.cell(row=2, column=4).number_format = 'd-mmm-yy'
ws['A1'].number_format #'yyyy-mm-dd h:mm:ss'

页面设置
from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1

3.8折叠列(大纲)
import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.column_dimensions.group('A','D', hidden=True)
wb.save('group.xlsx')

3.9获得列号的字母

from openpyxl.utils import get_column_letter

for  x  in  range( 1, len(record)+ 1 ): 

    col = get_column_letter(x)    # 默认x从1开始

    ws.cell( '%s%s' %(col, i)).value = x

通过列字母获取多个excel数据块

cell_range = "E3:{0}28".format(get_column_letter(bc_col))

ws["A1"] = "=SUM(%s)"%cell_range

3.10保存到文件

wb = Workbook()
wb.save('balances.xlsx')#save会在不提示的情况下用现在写的内容，覆盖掉原文件中的所有内容

3.11插入一个图片
from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'

# create an image
img = Image('logo.png')

# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('logo.xlsx')

 

四、图表

4.1创建一个图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series

wb = Workbook()
ws = wb.active

for i in range(10):
ws.append([i])

values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E15")
wb.save("SampleChart.xlsx")

4.2面积图(二维)

面积图类似于直线图之外,下面的区域画线。 不同的变异可通过设置分组€œstandarda€,一个€œstackeda€或€œpercentStackeda€; 一个€œstandarda€是默认的。

from openpyxl import Workbook
from openpyxl.chart import AreaChart,Reference,Series

wb = Workbook()
ws = wb.active

rows = [
    ['Number', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]

for row in rows:
    ws.append(row)

chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'

cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, "A10")

wb.save("area.xlsx")

4.3面积图(三维)

from openpyxl import Workbook
from openpyxl.chart import AreaChart3D,Reference,Series

wb = Workbook()
ws = wb.active

rows = [
    ['Number', 'Batch 1', 'Batch 2'],
    [2, 30, 40],
    [3, 25, 40],
    [4 ,30, 50],
    [5 ,10, 30],
    [6,  5, 25],
    [7 ,10, 50],
]

for row in rows:
    ws.append(row)

chart = AreaChart3D()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
chart.legend = None

cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, "A10")

wb.save("area3D.xlsx")

4.4柱状图表

垂直、水平和堆叠柱形图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]

for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"

ws.add_chart(chart2, "G10")


chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'

ws.add_chart(chart3, "A27")


chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'

ws.add_chart(chart4, "G27")

wb.save("bar.xlsx")

4.5柱状图表（三维）

from openpyxl import Workbook
from openpyxl.chart import Reference,Series,BarChart3D

wb = Workbook()
ws = wb.active

rows = [
    (None, 2013, 2014),
    ("Apples", 5, 4),
    ("Oranges", 6, 2),
    ("Pears", 8, 3)
]

for row in rows:
    ws.append(row)

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)

ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")

4.6泡沫图表（bubble chart）

泡沫图类似于散点图但使用第三个维度确定气泡的大小。 图表可以包括多个系列。

from openpyxl import Workbook
from openpyxl.chart import Series, Reference, BubbleChart

wb = Workbook()
ws = wb.active

rows = [
    ("Number of Products", "Sales in USD", "Market share"),
    (14, 12200, 15),
    (20, 60000, 33),
    (18, 24400, 10),
    (22, 32000, 42),
    (),
    (12, 8200, 18),
    (15, 50000, 30),
    (19, 22400, 15),
    (25, 25000, 50),
]

for row in rows:
    ws.append(row)

chart = BubbleChart()
chart.style = 18 # use a preset style

# add the first series of data
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
chart.series.append(series)

# add the second
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
chart.series.append(series)

# place the chart starting in cell E1
ws.add_chart(chart, "E1")
wb.save("bubble.xlsx")

4.7线图表

线图表允许数据绘制与固定轴。类似于条形图有三种线路图：标准、堆放、percentStacked。

from datetime import date
from openpyxl import Workbook
from openpyxl.chart import LineChart,Reference
from openpyxl.chart.axis import DateAxis
from copy import deepcopy

wb = Workbook()
ws = wb.active

rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015,9, 1), 40, 30, 25],
    [date(2015,9, 2), 40, 25, 30],
    [date(2015,9, 3), 50, 30, 45],
    [date(2015,9, 4), 30, 25, 40],
    [date(2015,9, 5), 25, 35, 30],
    [date(2015,9, 6), 20, 40, 35],
]

for row in rows:
    ws.append(row)

#Line Chart
c1 = LineChart()
c1.title = "Line Chart"
c1.style = 13
c1.y_axis.title = 'Size'
c1.x_axis.title = 'Test Number'
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)

# Style the lines
s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True

s2 = c1.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs

s2 = c1.series[2]
s2.smooth = True # Make the line smooth

ws.add_chart(c1, "A10")

#Stacked Line Line表
stacked = deepcopy(c1)
stacked.grouping = "stacked"
stacked.title = "Stacked Line Chart"
ws.add_chart(stacked, "A27")

#Percent Stacked Line表
percent_stacked = deepcopy(c1)
percent_stacked.grouping = "percentStacked"
percent_stacked.title = "Percent Stacked Line Chart"
ws.add_chart(percent_stacked, "A44")

# date axis表
c2 = LineChart()
c2.title = "Date axis Chart"
c2.style = 2 #线条的style,Max value is 48   2 10

#设置Y轴
c2.y_axis.title = "Size"
#c2.y_axis.crossAx = 100
c2.y_axis.scaling.min = 0  #y坐标的区间
c2.y_axis.scaling.max = 70 #y坐标的区间

#设置X轴
c2.x_axis.title = "Date"
#c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_format = 'd-mmm'  #规定日期格式
c2.x_axis.majorTimeUnit = "days"   #规定日期间隔
dates2 = Reference(ws, min_col=1, min_row=2, max_col=1, max_row=7) #引用由行列所描述的区域内的数据
c2.set_categories(dates2) #设置X轴坐标类别

#向图表中加入数据
dates = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7) #引用由行列所描述的区域内的数据
c2.add_data(dates, titles_from_data=True)

#设置图表在表格中的起始位置
ws.add_chart(c2, "A61")

#保存表格
wb.save("line.xlsx")

4.8线图表（三维）

from datetime import date
from openpyxl import Workbook
from openpyxl.chart import LineChart3D, Reference
from openpyxl.chart.axis import DateAxis

wb = Workbook()
ws = wb.active

rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015,9, 1), 40, 30, 25],
    [date(2015,9, 2), 40, 25, 30],
    [date(2015,9, 3), 50, 30, 45],
    [date(2015,9, 4), 30, 25, 40],
    [date(2015,9, 5), 25, 35, 30],
    [date(2015,9, 6), 20, 40, 35],
]

for row in rows:
    ws.append(row)

c1 = LineChart3D()
c1.title = "3D Line Chart"
c1.legend = None
c1.style = 15
c1.y_axis.title = 'Size'
c1.x_axis.title = 'Test Number'

data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)

ws.add_chart(c1, "A10")

wb.save("line3D.xlsx")

4.9散点图

分散、xy图类似于一些线形图。 的主要区别 是一个系列策划反对另一个值。 这是有用的, 值是无序的。

from openpyxl import Workbook
from openpyxl.chart import ScatterChart,Reference,Series

wb = Workbook()
ws = wb.active

rows = [
    ['Size', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    ws.append(row)

chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'

xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
    values = Reference(ws, min_col=i, min_row=1, max_row=7)
    series = Series(values, xvalues, title_from_data=True)
    chart.series.append(series)

ws.add_chart(chart, "A10")

wb.save("scatter.xlsx")

 

4.10饼图

饼图绘制数据作为一个圆片每片代表 整体的百分比。 片绘制在顺时针方向为0° 在顶部的圆。 饼图只能把一个系列的 数据。 图表的标题将默认的标题。

from openpyxl import Workbook
from openpyxl.chart import PieChart,ProjectedPieChart,Reference
from openpyxl.chart.series import DataPoint

data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"

# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

ws.add_chart(pie, "D1")


ws = wb.create_sheet(title="Projection")

data = [
    ['Page', 'Views'],
    ['Search', 95],
    ['Products', 4],
    ['Offers', 0.5],
    ['Sales', 0.5],
]

for row in data:
    ws.append(row)

projected_pie = ProjectedPieChart()
projected_pie.type = "pie"
projected_pie.splitType = "val" # split by value
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)

ws.add_chart(projected_pie, "A10")

from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar"
projected_bar.splitType = 'pos' # split by position

ws.add_chart(projected_bar, "A27")

wb.save("pie.xlsx")

 

4.11饼图（三维）

from openpyxl import Workbook
from openpyxl.chart import PieChart3D,Reference

data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie = PieChart3D()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"

ws.add_chart(pie, "D1")

wb.save("pie3D.xlsx")

4.12散列饼图

油炸圈饼图表类似于饼图,除了他们使用一枚戒指 一个圆。 他们还可以情节几个系列的数据如同心圆。

from openpyxl import Workbook
from openpyxl.chart import DoughnutChart,Reference,Series
from openpyxl.chart.series import DataPoint

data = [
    ['Pie', 2014, 2015],
    ['Plain', 40, 50],
    ['Jam', 2, 10],
    ['Lime', 20, 30],
    ['Chocolate', 30, 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

chart = DoughnutChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = "Doughnuts sold by category"
chart.style = 26

# Cut the first slice out of the doughnut
slices = [DataPoint(idx=i) for i in range(4)]
plain, jam, lime, chocolate = slices
chart.series[0].data_points = slices
plain.graphicalProperties.solidFill = "FAE1D0"
jam.graphicalProperties.solidFill = "BB2244"
lime.graphicalProperties.solidFill = "22DD22"
chocolate.graphicalProperties.solidFill = "61210B"
chocolate.explosion = 10

ws.add_chart(chart, "E1")

from copy import deepcopy

chart2 = deepcopy(chart)
chart2.title = None
data = Reference(ws, min_col=3, min_row=1, max_row=5)
series2 = Series(data, title_from_data=True)
series2.data_points = slices
chart2.series.append(series2)

ws.add_chart(chart2, "E17")

wb.save("doughnut.xlsx")

 

4.13雷达图表

数据按行或列在一个表可以绘制雷达图表。 雷达图表比较多个数据的聚合值 系列。 它实际上是一个圆轴的投影面积图。

from openpyxl import Workbook
from openpyxl.chart import RadarChart,Reference

wb = Workbook()
ws = wb.active

rows = [
    ['Month', "Bulbs", "Seeds", "Flowers", "Trees & shrubs"],
    ['Jan', 0, 2500, 500, 0,],
    ['Feb', 0, 5500, 750, 1500],
    ['Mar', 0, 9000, 1500, 2500],
    ['Apr', 0, 6500, 2000, 4000],
    ['May', 0, 3500, 5500, 3500],
    ['Jun', 0, 0, 7500, 1500],
    ['Jul', 0, 0, 8500, 800],
    ['Aug', 1500, 0, 7000, 550],
    ['Sep', 5000, 0, 3500, 2500],
    ['Oct', 8500, 0, 2500, 6000],
    ['Nov', 3500, 0, 500, 5500],
    ['Dec', 500, 0, 100, 3000 ],
]

for row in rows:
    ws.append(row)

chart = RadarChart()
chart.type = "filled"
labels = Reference(ws, min_col=1, min_row=2, max_row=13)
data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True

ws.add_chart(chart, "A17")

wb.save("radar.xlsx")

 

4.14股票图表

from datetime import date
from openpyxl import Workbook
from openpyxl.chart import BarChart,StockChart,Reference,Series
from openpyxl.chart.axis import DateAxis, ChartLines
from openpyxl.chart.updown_bars import UpDownBars

wb = Workbook()
ws = wb.active

rows = [
   ['Date',      'Volume','Open', 'High', 'Low', 'Close'],
   ['2015-01-01', 20000,    26.2, 27.20, 23.49, 25.45,  ],
   ['2015-01-02', 10000,    25.45, 25.03, 19.55, 23.05, ],
   ['2015-01-03', 15000,    23.05, 24.46, 20.03, 22.42, ],
   ['2015-01-04', 2000,     22.42, 23.97, 20.07, 21.90, ],
   ['2015-01-05', 12000,    21.9, 23.65, 19.50, 21.51,  ],
]

for row in rows:
    ws.append(row)

# High-low-close
c1 = StockChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=6)
data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6)
c1.add_data(data, titles_from_data=True)
c1.set_categories(labels)
for s in c1.series:
    s.graphicalProperties.line.noFill = True
# marker for close
s.marker.symbol = "dot"
s.marker.size = 5
c1.title = "High-low-close"
c1.hiLowLines = ChartLines()

# Excel is broken and needs a cache of values in order to display hiLoLines :-/
from openpyxl.chart.data_source import NumData, NumVal
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
c1.series[-1].val.numRef.numCache = cache

ws.add_chart(c1, "A10")

# Open-high-low-close
c2 = StockChart()
data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
c2.add_data(data, titles_from_data=True)
c2.set_categories(labels)
for s in c2.series:
    s.graphicalProperties.line.noFill = True
c2.hiLowLines = ChartLines()
c2.upDownBars = UpDownBars()
c2.title = "Open-high-low-close"

# add dummy cache
c2.series[-1].val.numRef.numCache = cache

ws.add_chart(c2, "G10")

# Create bar chart for volume

bar = BarChart()
data =  Reference(ws, min_col=2, min_row=1, max_row=6)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)

from copy import deepcopy

# Volume-high-low-close
b1 = deepcopy(bar)
c3 = deepcopy(c1)
c3.y_axis.majorGridlines = None
c3.y_axis.title = "Price"
b1.y_axis.axId = 20
b1.z_axis = c3.y_axis
b1.y_axis.crosses = "max"
b1 += c3

c3.title = "High low close volume"

ws.add_chart(b1, "A27")

## Volume-open-high-low-close
b2 = deepcopy(bar)
c4 = deepcopy(c2)
c4.y_axis.majorGridlines = None
c4.y_axis.title = "Price"
b2.y_axis.axId = 20
b2.z_axis = c4.y_axis
b2.y_axis.crosses = "max"
b2 += c4

ws.add_chart(b2, "G27")

wb.save("stock.xlsx")