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: 柱状图

 

三、使用介绍

1、Workbook

import openpyxl 
WB=openpyxl.Workbook() #新建一个工作簿（workbook）
WB.save('xxx.xlsx') #保存并关闭工作簿

import openpyxl 
WB=openpyxl.Workbook() #新建一个工作簿（workbook）
sh=WB.active #激活
WB.save('xxx.xlsx') #保存并关闭工作簿


import openpyxl 
WB=openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）
WB.save('xxx.xlsx') #保存并关闭工作簿

import openpyxl 
wb = openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）
sh = wb['Sheet'] #通过sheet名来访问sheet
print(sh['D18'].value) #获取cell值

　　

2、worksheet

新建sheet

import openpyxl 
wb=openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）

ws1=wb.active#调用正在运行的工作表

ws2 = wb.create_sheet() #新建sheet
ws4 = wb.create_sheet("NewTitle") #新建sheet并设定sheet名称

ws3 = wb.create_sheet(index=0) #新建sheet并指定sheet位置次序。系统自动命名，依次为Sheet, Sheet1, Sheet2
ws5 = wb.create_sheet('Data',index=1)#新建工作表并设定sheet名称，指定sheet位置次序

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

ws7= wb["Sheet1"] #通过名字打开sheet

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

wb.save('xxx.xlsx')

获取名字

import openpyxl 
WB=openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）
sh1=WB.active

aa=WB.sheetnames[5] #按次序获取第6个sheet名
print(aa)

bb=WB.sheetnames #获取所有sheet名
print(bb)

cc=sh1.title #获取sheet名，默认取最后创建的那个sheet的名称
print(cc)

dd=['深圳市', '2020-09-02', '15:50:00', '08:00:00', '1970-01-01', '一达通未签约']
f=bb[3] #第四个sheet页的名称
sh2=WB[f] #按名称获取sheet
sh2.append(dd)#写数据
print(sh2.title)#获取当前sheet名

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

读取excel数据

#读取xlsx
from openpyxl import load_workbook
wb = load_workbook("C:\\Programs\\PythonTest3\\000.xlsx")   # 打开一个xlsx文件。load_workbook(filepath) 可以对已存在的 .xlsx 进行追加数据
sheet = wb.active
# sheet = wb["Sheet1"]  # 读取指定的Sheet页

e_list=[]

# for i in sheet.values:
#     print(list(i))

# for row in sheet.iter_rows(values_only=True):
#     print(row) #数据是按行显示的

for i in sheet.values:
    e_list.append(list(i))
print(e_list)

写入excel数据

#写入xlsx
from openpyxl import Workbook
wb = Workbook()  # 创建一个工作表
sheet = wb.active  # 找到活动的sheet页。空的excel表默认的sheet页就叫Sheet

sheet.title = "Sheet2"  # 为默认sheet页命名
sheet=wb.create_sheet('价格页')  # 创建sheet页，并指定为当前操作页。可以用create_sheet和remove_sheet进行添加和删除sheet页

sheet['C3'] = 'Hello world!'  # 往sheet页里面写内容
for i in range(10):
    sheet[f"A{i+1}"].value = i + 1

sheet["E1"].value = "=SUM(A:A)" # 写写公式
wb.save('000.xlsx')  # 保存



from openpyxl import Workbook
workbook = Workbook()  #覆盖写
worksheet = workbook.active

row=[1,2,3,4,5]
rows = [["AA", 2019],["FF", 2018],["KK", 2019],["CC", 2020],["BB", 2017],["GG", 2018],["DD", 2019],["EE", 2018]]

worksheet.append(row)#添加一行

for i in rows:      #添加多行
    worksheet.append(i) # 把每一行append到worksheet中

workbook.save('000.xlsx')



from openpyxl import load_workbook
wb=wb = load_workbook("C:\\Programs\\PythonTest3\\000.xlsx") #追加写
ws=wb.active

row=[1,2,3,4,5]
rows=[['Num','a','d'],[2,40,30],[3,40,25],[4,50,30],[5,30,10],[6,25,5],[7,50,10],]

ws.append(row) #添加一行

for row in rows: #添加多行
    ws.append(row)

wb.save("000.xlsx")

读写单元格

import openpyxl

wb=openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）
ws=wb.active

d1=ws.max_column #获得最大列
d2=ws.max_row #获得最大行

wb["sheet33"].max_row #获得最大列
wb["sheet33"].max_column #获得最大行

d3=ws['A4'] #根据单元格的索引获取单元格，如果不存在将在A4新建一个
d3=ws["B3"].value #取值
d4=ws.cell(row = 4, column = 2) #使用cell()方法通过行列获取单元格(行号列号从1开始)
d4=ws.cell(row=3, column=2).value #取值

cell_range1= ws['C']  #获取C列,cell_range1是tuple类型
cell_range2= ws[6] #获取第10行
cell_range3= ws['C:D']#获取C到D列
cell_range4= ws[3:7] #获取第3到7行
cell_range5= ws['A1':'C2'] #使用切片获取多个单元格
ws.rows #迭代读取所有行row ,生成器,里面是每一行的数据，每一行都是一个tuple。
ws.columns #迭代读取所有列column

#输出cell_range1和cell_range2的数据
for row in cell_range1:
　　print(row.value)

#输出cell_range3、cell_range4和cell_range5的数据
for row in cell_range3:
　　for cell in row:
　　　　print(cell.value)

#按行返回所有数据
for row in ws.rows:
    for cell in row:
        print(cell.value)

#按列返回所有数据
for column in ws.columns:
    for cell in column:
        print(cell.value)

ws['A4'] = 4  #直接给单元格赋值
ws['A4'].value = 5  #给单元格赋值
ws.cell(row = 4, column = 2).value = 'test1' #通过cell函数给单元格赋值
ws.cell(row = 4, column = 2) value = 'test2' #通过cell函数给单元格赋值
ws["A1"] = "=SUM(1, 1)"  #通过公式计算产生写入的值
ws["A1"] = "=SUM(B1:C1)" #通过公式计算产生写入的值
ws['B9']='=AVERAGE(B2:B8)' #通过公式计算产生写入的值，读取时需加data_only=True这样读到B9返回的就是数字，否则返回的将是公式本身'=AVERAGE(B2:B8)'

wb.save('xxx.xlsx')

行列相关

import openpyxl
wb=openpyxl.load_workbook(filename = 'xxx.xlsx')

#插入行，列
wb["Sheet1"].insert_rows(4)     #在第四行之前插入空行
wb["Sheet1"].insert_cols(3)     #在第三列之前插入空列
wb["Sheet1"].append([1,2,3])    #默认在最大行之后一行，开始从第一列到第三列插入1,2,3这三个值

#删除行列
wb["Sheet1"].delete_rows(3)     #删除第三行
wb["Sheet1"].delete_cols(2)     #删除第二列
wb["Sheet1"].delete_rows(2,8)   #删除第二行到第九行 ，读者请注意：删除多行时是删除到8行之后的一行，列也是同样
wb["Sheet1"].delete_cols(2,3)   #删除第二列到第四列

wb.save('xxx.xlsx')

　

自适应设置各列宽度

import openpyxl

wb= openpyxl.Workbook()
ws=wb.active

column_widths = []
for row in sh.columns:
    for i, cell in enumerate(row):
        if len(column_widths) > i:
            if len(str(cell.value)) > column_widths[i]:
                column_widths[i] = len(str(cell.value))
        else:
            column_widths.append(len(str(cell.value)))

column_widths.reverse()#对最大宽度列表进行逆序

#设置各列宽度。enumerate()返回列表的下标和值
for i, column_width in enumerate(column_widths):
    sh.column_dimensions[chr(i+97)].width = column_width*2

wb.save('xxx.xlsx')

　　

单元格设置

import openpyxl
from openpyxl.styles import Font, Fill
import datetime
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font

wb=openpyxl.load_workbook(filename = 'xxx.xlsx')#调用已有的工作簿（workbook）
ws=wb.active

#合并单元格
ws.merge_cells('D6:E6')#合并一行中的几个单元格,合并后只可以往左上角写入数据，若这些单元格都有数据，则只会保留左上角的数据，其他则丢弃
ws.merge_cells(start_row=6,start_column=4,end_row=6,end_column=5)#合并一行中的几个单元格
ws.merge_cells('A1:C3')#合并一个矩形区域中的单元格
ws.merge_cells(start_row=1,start_column=1,end_row=3,end_column=3)#合并一个矩形区域中的单元格

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

#设置样式
c = ws['A1'].font = Font(size=14) #可以直接应用到单元格

ws.column_dimensions['B'].width=2 #设置B列宽度
ws.row_dimensions[2].height=2 #设置第2行高度

ws.column_dimensions['B'].hidde=1 #隐藏B列
ws.row_dimensions[2].hidde=1 #隐藏第2行

#单元格默认样式如下：
#字体
font = Font(name='Calibri',size=11,bold=False,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000')
#填充
fill = PatternFill(fill_type=None,start_color='FFFFFFFF',end_color='FF000000')
#边框
border = Border(left=Side(border_style=None,color='FF000000'),
             right=Side(border_style=None,color='FF000000'),
             top=Side(border_style=None,color='FF000000'),
             bottom=Side(border_style=None,color='FF000000'),
             diagonal=Side(border_style=None,color='FF000000'),
             diagonal_direction=0,
             outline=Side(border_style=None,color='FF000000'),
             vertical=Side(border_style=None,color='FF000000'),
             horizontal=Side(border_style=None,color='FF000000'))
#位置
alignment=Alignment(horizontal='general',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)

#单元格数据格式
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'

wb.save('xxx.xlsx')

 

插入一个图片

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

img = Image('logo.png')
ws.add_image(img, 'A1')

wb.save('logo.xlsx')

 把文本写入excel

import openpyxl

fe=openpyxl.Workbook()
fs=fe.create_sheet("tt")

with open("C:\\Programs\\PythonTest3\\info.txt",'r',encoding="utf-8") as fwd:
    for line in fwd.readlines():
        fs.append([line])#openpyxl中写入Excel的数据需是列表型的，所先先将每行放入列表，再将列表放入列表
fe.save("C:\\Programs\\PythonTest3\\000.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面积图(二维)

面积图类似于直线图

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")