openpyxl

基本使用

安装

pip install openpyxl

表单对象

import openpyxl

book = openpyxl.load_workbook('example.xlsx')

# book = openpyxl.Workbook() # 新建book实例

获取表单名字

# 方法一
print(book.sheetnames)

# 方法二
for sheet in book:
	print(sheet.title)

新建表

# 方式一：插入到最后(default)
sheet = book.create_sheet('sheet1')

# 方式二：插入到最开始的位置
sheet = book.create_sheet('sheet1',0)

获取某一表单

# 方法一
sheet = book['Sheet1']
# 方法二
sheet = book.get_sheet_by_name('Sheet1')

获取活跃表单

sheet = book.active

获取表单最大行列

sheet.max_row # 获取最大行数
sheet.max_column # 获取最大列数

单元格对象

sheet['A1'] # 获取单元格对象
sheet['A1'].value # 获取单元格值

sheet['A1'].row # 获取单元格行
sheet['A1'].column # 获取单元格列

sheet['A1'].coordinate # 获取对象对应的单元格 返回结果 A1

sheet.cell(row=1,column=2)  # 获取第一行第二列单元格

获取某行，某列

sheet['B']  # 获取B列，返回元组
sheet[2]    # 获取第2行，返回元组

行，列切片

row_range = sheet[2:6]
col_range = sheet['B:C']

# 访问单元格
for col in col_range:
	for cell in col:
		print(cell.value)

生成器遍历行列

# 遍历行
方法一
for row in sheet.iter_rows():   # 参数 min_row , max_row , min_col , max_col
	for cell in row:
		print(cell.value)
方法二
for row in sheet.rows:
	for cell in row:
		print(cell.value)
# 遍历列
方法一
for col in sheet.iter_cols():
	for cell in col:
		print(cell.value)
方法二
for col in sheet.columns:
	for cell in col:
		print(cell.value)
		

单元格切片

cell_range = sheet['A1:C3']

for row in cell_range:
	for cell in row:
		print(cell.coordinate,cell.value)

表单列与数字转换

import openpyxl
from openpyxl.utils import get_column_letter,column_index_from_string

print(get_column_letter(2)) # 第二列对应 B
print(get_column_letter(100)) # 第100列对应 CV

print(column_index_from_string('ABC'))  # ABC 是第731列

新建表单操作

import openpyxl

book = openpyxl.Workbook()
book.create_sheet(title='first book',index=0)  # index参数指定表单索引
sheet = book.active
print(sheet.title)
book.save('demo.xlsx')

删除表单

book.remove(sheet)
# del book[sheetname]

单元格写入

sheet['A1'] = 'hello'  # 一个单元格写入内容

sheet.append(range(10)) # 写入一行内容


'''写入示例1'''
data = [
	['number','batch1','batch2'],
	[1,10,10],
	[2,20,20],
	[3,30,30],
	[4,40,40]
]
for row in data:
	sheet.append(row)

'''写入示例2'''

for row in range(1,10):
	for col in range(1,20):
		sheet.cell(row=row,column=col,value=get_column_letter(col))

设置字体大小，样式，颜色

import openpyxl
from openpyxl import Font,color


font1 = Font(name='Times New Roman', size=20, bold=True, color=colors.BLUE)

book = openpyxl.Workbook()
sheet = book.active
sheet['B1'] = 'demo'
sheet['B1'].font = font1

book.save('Font.xlsx')

插入公式

import openpyxl

book = openpyxl.Worksheet()
sheet = book.active

sheet['A1'] = 10
sheet['B1'] = 20
sheet['C1'] = '=SUM(A1:B1)'

book.save('Formula.xlsx')

设置单元格行高，列宽

import openpyxl

book = openpyxl.Workbook()
sheet = book.active

sheet.row_dimensions[1].height = 70  # 设置第1行行高为 70
sheet.column_dimensions['B'].width = 70 # 设置B列列宽为 70

book.save('dimensions.xlsx')

合并单元格

import openpyxl

book = openpyxl.Worksheet()
sheet = book.active

sheet.merge_cell('A1:C3')
sheet['A1'] = '合并单元格'

sheet.save('merge.xlsx')

绘制图表

import openpyxl
from openpyxl.chart import PieChart,Reference

book = openpyxl.Workbook()
sheet = book.active
sheet.title = 'pieChart'

data = [
	['Pie','Sold'],
	['Apple',50],
	['Cherry',40],
	['Pumpkin',30]
]
for row in data:
	sheet.append(row)

pie = PieChart()

labels = Reference(sheet,min_col=1, min_row=2, max_row=5)
data= Reference(sheet,min_col=2, min_row=2, max_row=5)

pie.add_data(data)                     # 添加数据
pie.set_categories(labels=labels)     # 设置图表标签
pie.title = 'Pie sold by category'     # 设置图表标题

sheet.add_chart(pie,'D1')    # 将图表放置到指定位置

book.save('category.xlsx')