openpyxl

python 专栏收录该内容
14 篇文章 0 订阅

基本使用

安装
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')
  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值