openpyxl读取excel ⼀、openpyxl 简介
1.1 简介
openpyxl 是⼀个 Python 库，⽤于读取/写⼊Excel 2010 xlsx / xlsm / xltx / xltm⽂件。
pip install openpyxl
1.2 ⼊门⽰例
import datetime
from openpyxl import Workbook
wb = Workbook()
# 获取活动⼯作表
ws = wb.active
# 数据可以直接分配给单元
ws["A1"] = 42
# ⾏也可以追加
ws.append([1,2,3])
# Python类型将⾃动转换
# 导⼊⽇期时间
ws['A2'] = w()
# 保存⽂件，会覆盖原有⽂件
wb.save("sample.xlsx")
⼆、⼯作表基础操作
2.1 创建⽂件
from openpyxl import Workbook
# 创建⼯作簿对象
wb = Workbook()
# 获取当前活动的⼯作表，默认获取第⼀个⼯作表
ws = wb.active
print(ws)
# 创建新的⼯作表
# 默认在末尾插⼊
ws1 = wb.create_sheet(title="Mysheet1")
# 在最前⾯插⼊
ws2 = wb.create_sheet(title="Mysheet", index=0)
# index=-1 在倒数第⼆的位置插⼊⼯作表
ws3 = wb.create_sheet(title="Mysheet2", index=-1)
wb.save("sample.xlsx")
2.2 打开⽂件
from openpyxl import load_workbook
# 打开现有⼯作簿
wb = load_workbook("sample.xlsx")
print(wb.sheetnames)
2.3 修改⼯作表属性
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet(title="Mysheet1")
# 修改⼯作表属性
# 修改⼯作表名称
ws1.title = "UpdateSheet"
# 修改⼯作表颜⾊
ws1.sheet_properties.tabColor = "1072BA"
wb.save("sample.xlsx")
2.4 获取⼯作表
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet(title="Mysheet1")
# 查看所有⼯作表
print(wb.sheetnames)
# 遍历⼯作表
for sheet in wb:
print(sheet.title)
wb.save("sample.xlsx")
2.5 复制⼯作表
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 复制⼯作表，仅能复制单元格（包括值、样式、超链接和注释）和某些⼯作表属性（如尺⼨、格式和属性）；只读模式打开时不能复制target = wb.copy_worksheet(ws)
wb.save("sample.xlsx")
2.6 单元格读写
存在 sample.xlsx ⽂件如下：
import openpyxl
wb = openpyxl.load_workbook("sample.xlsx")
ws = wb.get_sheet_by_name("Sheet")
# 获取单元格 A4 的值
print(ws["A4"])
python怎么读入excel
# 给单元格 A4 赋值
ws["A4"] = "⿇⼦"
# cell ⽅式赋值
# 切⽚的⽅式访问多个单元格
colB = ws['B']  # B列
colBtoD = ws['B':'C']  # B-D 列
row2 = ws[2]    # 第2⾏
row2to4 = ws[2:4]    # 第2-4⾏
wb.save("sample.xlsx")
2.7 单元格遍历
import openpyxl
wb = openpyxl.load_workbook("sample.xlsx")
ws = wb.get_sheet_by_name("Sheet")
# 按⾏遍历
for row ws:
for cell in row:
print(cell.value, end="\t\t")
print()
print("-------------------------")
for col lumns:
for cell in col:
print(cell.value, end="\t\t")
print()
wb.close()
2.8 合并单元格
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active
# 合并第2⾏的 A-C 列
<_cells('A2:C2')
# 取消合并
# ws.unmerge_cells('A2:C2')
# 或者使⽤以下⽅法：
# ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=3) # ws.unmerge_cells(start_row=2, start_column=1, end_row=3, end_column=3) wb.save("sample.xlsx")
2.9 插⼊图⽚
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook("sample.xlsx")
ws = wb.active
img = Image('logo.png')
ws.add_image(img, 'A1')
wb.save("sample.xlsx")
2.10 插⼊删除⾏、列
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active
# 第2⾏位置插⼊2⾏
ws.insert_rows(2, 2)
# 在第2列插⼊2列
ws.insert_cols(2, 2)
# 删除
ws.delete_cols(2, 2)
ws.delete_rows(2, 2)
wb.save("sample.xlsx")

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。