openpyxl

1、创建一个工作薄

from openpyxl import Workbook,load_workbook

wb = Workbook()  # 该方法主要用于创建一个新的
wb = load_workbook('./name.xlsx')  # 该方法主要用于导入一个已经存在的工作薄

2、激活表格

sheet = wb.active

Workbook的一些属性方法：


sheet


cell

3、按行读取excel表格的方法
按行读取：
  1、方法一

#按行读取
for i in range(minrow,maxrow+1):
    for j in range(mincol,maxcol+1):
        cell=sheet.cell(i,j).value
        print(cell,end=" ")
    print()

方法二

for row in sheet.rows:
    for cell in row:
        print(cell.value,end='\t')
    print()  # 目的在于不换行

按列读取：
  1、方法一

#按列读取
for m in range(mincol,maxcol+1):
    for n in range(minrow,maxrow+1):
        cell=sheet.cell(n,m).value
        print(cell,end=" ")
    print()

4、openpyxl的写入
  1、单元格的输入

sheet['A1'] = 'name'  # 按照想应的位置输入
sheet.cell(1,2).value = '123456'  # cell后面接的是行和列

  2、一行一行的写
  如 user=['test’,'123456’]

sh1.append(user)

  现在有个数组要写入到excel表里
  login = [
  ['张飞’ , '123456’ ] ,
  ['赵云’ , '123456’ ] ,
  ['许褚’ , '123456’ ] ,
  ['典韦’ , '123456’ ] ,
  ['关羽’ , '123456’ ] ,
  ['黄忠’ , '123456’ ] ,
  ['徐晃’ , '123456’ ] ,
  ['马超’ , '123456’ ]
  ]

bk=openpyxl.Workbook()
sh1=bk.active
for i in range(len(login)):
    sh1.append(login[i])
bk.save('userinfo.xlsx')

2. Python 操作 Excel
2.1 Python 操作 Excel 之读取
2.1.1 打开本地工作簿，获取所有工作表名称

# 获取所有的工作表名称
import openpyxl

wb = openpyxl.load_workbook('excelTest.xlsx')

# 获取所有的工作表名称
print(wb.sheetnames)

# 获取当前激活的工作表
print(wb.active.title)

# 通过工作簿获取
for s in wb:
    print(s.title)

2.1.2 创建工作表

import openpyxl 
wb = openpyxl.load_workbook('excelTest.xlsx') 
#创建工作表 mySheet = wb.create_sheet('mySheet') 
print(wb.sheetnames) 
#遍历获取工作表的名称 
for sheet in wb: 
	print(sheet.title)

2.1.3 根据工作表名称获取工作表

import openpyxl 
wb = openpyxl.load_workbook('excelTest.xlsx') 
# 根据工作表名称获取工作表，DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
# 会产生弃用警告，wb['my_sheet']，可以避免
# my_sheet = wb.get_sheet_by_name('my_sheet')

#根据名称获取表单 
sheet3 = wb.get_sheet_by_name('Sheet2') 
#或者 
sheet4 = wb['mySheet']

2.1.4 获取单元格对象及单元格的值

import openpyxl 
wb = openpyxl.load_workbook('excelTest.xlsx') #获取当前激活的工作表 
ws = wb.active #返回的是工作表对象 
print(ws) #<Worksheet "Sheet1"> #获取 Cell 对象及单元格的值 
print(ws['A1']) #<Cell 'Sheet1'.A1> 
print(ws['A1'].value)

2.1.5 获取单元格的行、列及值

# 获取单元格对象的行列和值
import openpyxl

# 获取工作簿对象
wb = openpyxl.load_workbook('excelTest.xlsx')

# 获取sheet
ws = wb.active

# 根据单元格名称获取单元格对象
c = ws['C3']
print('row:{}, column:{},value:{}'.format(c.row, c.column, c.value))
print('coordinate:', c.coordinate)

print('ws.cell()获取单元格的值')
print(ws.cell(row=2, column=2).value)

print('循环遍历获取：')
for r in ws.rows:
    for c in r:
        print(c.value, end='\t\t')
    print()

2.1.6 读取整行、整列及部分几行

# 读取整行整列及部分行
import openpyxl

# 获取工作簿
wb = openpyxl.load_workbook('excelTest.xlsx')
# 获取sheet
ws = wb.active

print('获取一整行')
rows = ws[2]
print(rows)
# 遍历所有单元格
for r in rows:
    print(r.value, end='\t')

print('获取整列')
columns = ws['B']
print(columns)

for c in columns:
    print(c.value, end='\t')

print('获取部分行 对行进行切片')
row_range = ws[2:3]
print(row_range)

for r in row_range:
    for c in r:
        print(c.value, end='\t')
    print()

columns_range = ws['A':'C']
print(columns_range)
for col in columns_range:
    for c in col:
        print(c.value, end='\t\t')
    print()

2.1.7 使用 iter_rows()逐行读取

import openpyxl

# 获取工作簿
wb = openpyxl.load_workbook('excelTest.xlsx')

# 获取当前激活态的sheet
ws = wb.active

print('总共{}，总共{}列'.format(ws.max_row, ws.max_column))

print(ws.iter_rows())  # generator object 获取一个生成器对象
# 获取所有行所有列
for row in ws.iter_rows():
    for cell in row:
        print(cell.value, end='\t\t')
    print()

print('读取部分行 部分列')
for row in ws.iter_rows(min_row=2, max_row=3, min_col=2, max_col=2):
    for cell in row:
        print(cell.value, end='\t\t')
    print()
print()


print('iter.cols()')
print(ws.iter_cols())

for cell in ws.iter_cols():
    for c in cell:
        print(c.value, end='\t\t')
    print()
print()

print('ws.iter_rows()设置最小列、最大列、最小行和最大行读取'.center(20,'*'))
for cell in ws.iter_cols(min_row=2, max_col=2, min_col=2, max_row=3):
    for c in cell:
        print(c.value, end='\t\t')
    print()
print()

2.1.8 部分行部分列切片读取

import openpyxl 
wb = openpyxl.load_workbook('excelTest.xlsx') ws = 
wb.active print('共{}行， 共{}列'.format(ws.max_row,ws.max_column)) 
print('部分行部分列切片读取'.center(20,'*')) 
cell_range = ws['A1:C3'] 
for rowObject in cell_range: 
	for cellObject in rowObject:
	     print(cellObject.coordinate,cellObject.value,end='\t') 
	print()

2.1.9 列数字与字母的对应转换

# 列字母与数字的对象转化
from openpyxl.utils import get_column_letter, column_index_from_string

print('第2列对象的字母:', get_column_letter(2))
print('第12列对应的字母:', get_column_letter(12))

print('字母DR对应的数字:', column_index_from_string('DR'))
print('字母D对应的数字:', column_index_from_string('D'))

2.2 Python 操作 Excel 之写
2.2.1 创建、删除工作表

# 创建WorkSheet及删除
import openpyxl

# 创建工作簿
wb = openpyxl.Workbook()
ws = wb.active

print('默认工作表名称', ws.title)

# 设置名称
ws.title = 'mySheet'
print(ws.title)
# 获取工作簿的所有sheetNames
print(wb.sheetnames)  # 返回所有sheet名称 列表对象
print(wb.get_sheet_names())

print('创建工作表')
wb.create_sheet(index=1, title='One Sheet')
wb.create_sheet(index=2, title='Two Sheet')
wb.create_sheet(index=3, title='Three Sheet')
print(wb.sheetnames)
print('删除工作表')
wb.remove_sheet(wb['Two Sheet'])  # Deprecating Waring：弃用
wb.remove(wb['Two Sheet'])
print(wb.sheetnames)

2.2.2 使用 list 写入

# 写入数据到excel中
import openpyxl

wb = openpyxl.Workbook()
ws = wb.create_sheet(title='使用list写入')
print(wb.sheetnames)
print('使用list写入')
rows = [['now1', 'now2', 'now3', 'now3'],
        [12, 54, 23, 26],
        [11, 43, 55, 13],
        [54, 7672, 333, 433],
        [1, 2, 3, 4],
        ]
print(rows)
# 按行写入
for row in rows:
    ws.append(row)

2.2.3 使用 range 方式写入

import openpyxl 
#向工作单元写内容 
wb = openpyxl.Workbook() 
ws2 = wb.create_sheet('range names')
ws = wb.create_sheet('RangeSheet')
for i in range(1, 41):
    ws.append(range(16))

2.2.4 使用 cell()方法写入

# 根据cell()写值
ws = wb.create_sheet('Cell Sheet')
from openpyxl.utils import get_column_letter  # 根据当前列获取该列名

for row in range(10, 21):
    for col in range(5, 16):
        ws.cell(row=row, column=col, value=get_column_letter(col))  # 根据当前列获取该列名，作为value

wb.save('写入数据.xlsx')

3. Python 操作 Excel 之修改样式
3. 1 修改字体样式

# 修改样式
import openpyxl
from openpyxl.styles import colors, Font

# 获取工作簿
wb = openpyxl.Workbook()
ws = wb.active

# 更改sheet的name
ws.title = '修改字体样式'

# 修改单元格字体样式
c3_font = Font(name='宋体', size=24, italic=True, )
ws['C3'].font = c3_font
ws['C3'] = '宋体 24 italic'  # italic：斜体的;斜体字;

a5_font = Font('微软雅黑', size=18, bold=True, color=colors.BLUE)
ws['A5'].font = a5_font
ws['A5'] = '微软雅黑 18 bold blue'
wb.save('style_excelTest.xlsx')

3. 2 设置单元格公式

import openpyxl from openpyxl.styles 
import Font 
from openpyxl.styles import colors 
wb = openpyxl.Workbook() ws = wb.active
# 设置单元格公式
ws = wb.create_sheet('设置单元格公式')
ws['B1'] = 100
ws['B2'] = 99
ws['B3'].font = a5_font
ws['B3'] = '=SUM(B1:B2)'

3.3 设置行高和列宽

import openpyxl from openpyxl.styles 
import Font 
from openpyxl.styles import colors 
wb = openpyxl.Workbook() 
ws = wb.active #设置行高和列宽 
ws = wb.create_sheet('dimesions') 
ws['A1'] = 'Tall row' 
ws.row_dimensions[1].height = 70 
ws['B2'] = 'Wide column' 
ws.column_dimensions['B'].width = 20 
wb.save('style_excelTest.xlsx')

3.4 合并单元格

import openpyxl from openpyxl.styles 
import Font 
from openpyxl.styles import colors 

wb = openpyxl.Workbook()
#合并单元格 
ws = wb.create_sheet('merged') 
ws.merge_cells('A1:D3') 
ws['A1'] = 'Twelve cells merged together' 
ws.merge_cells('C5:D5') 
ws['C5'] ='Two merged cells
wb.save('style_excelTest.xlsx')

3.5 拆分单元格

import openpyxl from openpyxl.styles 
import Font 
from openpyxl.styles import colors 

wb = openpyxl.Workbook() 
ws = wb.active #拆分单元格 
ws = wb.copy_worksheet(wb.get_sheet_by_name('merged')) 
ws.title = 'unmerged' 
ws.unmerge_cells('A1:D3') 
ws.unmerge_cells('C5:D5') 
wb.save('style_excelTest.xlsx')

4. Python 操作 Excel 之图表
4.1 饼图
饼图将数据绘制为一个圆的切片，每个切片代表整个百分比。切片按顺时针方向绘制， 圆的顶部为 0°。

# 绘制饼图
import openpyxl
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart  # Reference：图标所用信息
from openpyxl.chart import Series

# 准备数据
rows = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40]
]

# 将数据写入excel
# 创建工作簿
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Pie Charts'

for row in rows:
    ws.append(row)

# 绘制饼图
pie_chart = PieChart()
# 设置标题
pie_chart.title = 'Pie sold by category'
# 进行分类
category = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=2, max_row=5)  # 数据所在第2列

# 需要先添加数据再设置种类介绍
# 添加数据
pie_chart.add_data(data)
# 设置所分类别
pie_chart.set_categories(category)

# 在excel添加饼图
ws.add_chart(pie_chart, 'D1')  # 在D1位置绘制饼图
# 保存
wb.save('char_excel_text.xlsx')

4.2 条形图和柱形图
在条形图中，值被绘制为水平条或垂直列。可以通过 type 属性来设置。

绘制垂直的条形图则使用如下：

chart1.type = 'col

绘制成水平条形图示例如下：

chart1.type = 'bar

import openpyxl
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart  # Reference：图标所用信息
from openpyxl.chart import Series

# 绘制柱状图
# 创建工作表
ws = wb.create_sheet('Bar Chart')

# 准备数据
rows = [
    ('Number', 'Batch1', 'Batch2'),
    (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)

# 绘制柱状图
bar_chart = BarChart()
bar_chart.type = 'col'  # col垂直、水平柱状图 bar
bar_chart.title = 'Bar Chart'
bar_chart.style = 10  # 设置颜色，10的对比度最强，红色与蓝色
# 设置横轴纵轴标题
bar_chart.x_axis.title = 'Sample length(mm)'
bar_chart.y_axis.title = 'Test number'

# 设置分类
category = Reference(ws, min_col=1, min_row=2, max_row=7)
# 获取数据
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)

# 柱状图对象添加数据
bar_chart.add_data(data, titles_from_data=True)  # titles_from_data=True：根据来源设置数据标题
# 设置分类
bar_chart.set_categories(category)
# 工作页绘制柱状图，并指定位置
ws.add_chart(bar_chart, 'E1')

# 保存
wb.save('char_excel_text.xlsx')

4.3 气泡图

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

import openpyxl
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart  # Reference：图标所用信息
from openpyxl.chart import Series

# 绘制气泡图
ws = wb.create_sheet('Bubble Chart')

# 设置数据
rows = [
    ('Number of Products', 'Sales in USD', 'Marked share'),
    (14, 12200, 15),
    (20, 60000, 33),
    (18, 2440, 10),
    (22, 3200, 42),
    (),
    (12, 8200, 18),
    (15, 50000, 30),
    (19, 22400, 15),
    (25, 25000, 50),
]

# 添加数据
for row in rows:
    ws.append(row)

# 获取气泡图对象
bubble_chart = BubbleChart()
bubble_chart.style = 10  # 设置颜色

# 添加一组数据
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 = Series(values=yvalues, xvalues=xvalues, zvalues=size, title='2013')
bubble_chart.series.append(series)

# 添加一组数据
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')
bubble_chart.series.append(series)

# 添加气泡表
ws.add_chart(bubble_chart, 'E1')
# 保存
wb.save('char_excel_text.xlsx')

4.4 散点图
散点图或 xy 图类似于某些折线图。主要的区别是一个系列的值相对于另一个系列。当值无序时，这很有用。

import openpyxl
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart  # Reference：图标所用信息
from openpyxl.chart import Series

# 绘制散点图
ws = wb.create_sheet('Scatter Chart')

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

for row in rows:
    ws.append(row)

# 绘制散点图
scatter_chart = ScatterChart()
# 设置标题
scatter_chart.title = 'Scatter Chart'
# 设置颜色
scatter_chart.style = 13

# 设置x轴y轴标题
scatter_chart.x_axis.title = 'Size'
scatter_chart.y_axis.title = 'Percentage'

# 创建x轴的数据来源
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
# 创建yvalues
for i in range(2, 4):
    yvalues = Reference(ws, min_col=i, min_row=1, max_row=7)
    series = Series(yvalues, xvalues=xvalues, title_from_data=True)
    scatter_chart.series.append(series)

# 将散点图添加到ws工作表中
ws.add_chart(scatter_chart, 'E1')

# 保存工作簿
wb.save('charts.xlsx')