python 操作excel openpyxl

时间：2024-12-11 23:34:08

1 安装

pip install openpyxl

如果装不上,请指定安装源来安装

pip install -i https://pypi.douban.com/simple openpyxl

如果excel里面有图片(jpeg, png, bmp,…),需要安装图片处理模块

pip install pillow

2 excel的写

from openpyxl import Workbook

# 实例化对象

wb = Workbook()

# 创建工作簿,名字是上海,如果只写一个上海默认添加在最后一个工作簿中

wb1 = wb.create_sheet('上海')

# 创建工作簿,工作簿在第一个位置

wb2 = wb.create_sheet('广东',0)

# 获取当前工作中的工作簿

awb = wb.active

# 给工作不改名字

awb.title = "guangdong"

# excel写数据,可以写表格的函数

 # 向guangdong 的工作能力簿中写数据

 # 向单元格中写数据

awb['A4'] = '广东美食'

# 向第3行第4列的单元格中写数据

awb.cell(row=3,column=4,value="广东人吃福建人")

# 追加写入整行数据

awb.append(['虾饺','凤爪','烧鹅'])

# 可以循环的写入文件,从文件的第一行第一列开始写文件，每行写1,2,3,4

for i in range(10):

    awb.append([1,2,3,4])

# 写表格的函数:

awb['A16']="=SUM(A6:A15)"

# 保存文件

wb.save('城市.xlsx')

excel读数据

# 读文件

from  openpyxl import  load_workbook

# 实例化对象

wb = load_workbook('城市.xlsx',read_only=False,data_only=True)

# 获取所有工作簿的名字

# print(wb.sheetnames)

# 获取工作薄对象

wb1 = wb['guangdong']

# 如果单元格中有内容,但读出来是None,需要将文件打开手动保存一下,在读取,

# 在读取函数的单元格的时候,需要将data_only=True

a4= wb1["A4"].value

# None

a5 = wb1.cell(row=5,column=1).value

# 获取所有的行的数据,是一个生成器generator

row = wb1.rows

for r in row:

    # r是每行的数据的对象,是元组的形式

    print()

    for l in r:

        # l 是每个单元格的数据

        print(l.value,end='\t')

# 获取所有的列,是一个生成器

col = wb1.columns

for c in col:

    print()

    for i in c:

        print(i.value,end='\t')

# 共有多少行数据

print(wb1.max_row)

# 共有多少列数据

print(wb1.max_column)

在读表的时候需要注意的:

　　

获取行和列

sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。
sheet.columns类似，不过里面是每个tuple是每一列的单元格。读列的时候需要将read_only=False,或者不写,默认是False

在获取行或者列的时候,不能使用索引来取特定行的数据,因为sheel.rows是一个生成器,不过可以将生成器强转成列表，list(sheet.rows)[2] 这样就能取到第3行的数据了

在读有表格函数的表时:

读取的时候需要加上data_only=True这样读到B9返回的就是数字，如果不加这个参数，返回的将是公式本身'=AVERAGE(B2:B8)'

设置单元格风格--Style

先导入需要的类from openpyxl.styles import Font, colors, Alignment

分别可指定字体相关，颜色，和对齐方式。

字体:

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_itatic_24_font

对齐方式:

也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数

# 设置B1中的数据垂直居中和水平居中

sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

设置行高和列宽
有时候数据太长显示不完，就需要拉长拉高单元格。

# 第2行行高

sheet.row_dimensions[2].height = 40

# C列列宽

sheet.column_dimensions['C'].width = 30

合并和拆分单元格

所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。
相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

# 合并单元格， 往左上角写入数据即可

sheet.merge_cells('B1:G1') # 合并一行中的几个单元格

sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
以下是拆分单元格的代码。拆分后，值回到A1位置。

sheet.unmerge_cells('A1:C3')

通过excel表格的内容,向mysql数据库中导入数据:

import pymysql

from  openpyxl import load_workbook

db = pymysql.connect('127.0.0.1','root','','web')

cursor = db.cursor()

insert_sql = '''insert into student values(%s,%s)'''

wd = load_workbook('学生.xlsx')

s = wd['学生']

rows = s.max_row

columns = s.max_column

data=[]

for rx in  range(1,rows+1):

    for cx in range(1,columns+1):

        data.append(str(s.cell(row=rx, column=cx).value))

    cursor.execute(insert_sql,(data[0],data[1]))

    data=[]

db.commit()

在做表操作的时候可能遇到错误:

1 文件在打开的状态,操作文件报的错误

save_workbook

    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)

  File "C:\Python36\lib\zipfile.py", line 1113, in __init__

    self.fp = io.open(file, filemode)

PermissionError: [Errno 13] Permission denied: '城市.xlsx'

2 在读取列的数据时,需要将read_only=False 或者不写默认是False,

File "G:/python/exel/readfile.py", line 23, in <module>

    col = wb1.columns

AttributeError: 'ReadOnlyWorksheet' object has no attribute 'columns'

相关文章