excel文件.xlsx操作 openpyxl 笔记

原创已于 2022-07-19 10:53:48 修改 · 1.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#python #开发语言 #后端

于 2022-02-23 15:53:32 首次发布

python openpyxl 专栏收录该内容

4 篇文章

订阅专栏

这篇博客详细介绍了如何使用openpyxl库对Excel文件进行读写操作，包括最大行列限制、创建和修改表单、读取与写入数据、单元格的赋值、样式设置、边框和填充等，还涵盖了行高列宽、合并单元格、对齐方式等高级功能。通过实例代码展示了各种操作方法，适合需要处理Excel数据的开发者参考。

1 .xls文件用xlrd和xlwt两个库，一个读一个写，最大行列是65536行，256列

2 .xlsx文件需用openpyxl库读写，最大行列是1048576行，16384列（采取的是xml存储），pandas读.xlsx需低版本xlrd支持(1.2.0) 或用openpyxl引擎：

data = pd.read_excel（'filepath.xlsx', engine='openpyxl'）

3 关于表单sheet

1）建立文件及表单

from openpyxl import Workbook

book = Workbook() #建立一个excel文件,默认包含一个sheet表单，默认名就是"sheet"
sht = book.active  #设sheet表单为活动表单，在程序中可起名为sht,(active有啥用待学习)

或者用book.create_sheet() 命令建立表单

2）表单操作

(1).新建sheet:

sht1=book.create_sheet() #新建立一个sheet页，括号为空时默认表单的标签名称为"sheet1"继续建立名称顺延sheet2等。在程序中用变量名sht1等表示引用表单，在excel文件中对应的表单标签名是"sheet1"，变量名和标签名可以统一也可以不统一随意，但注意这是两个概念

(2).sheet页改标签名：

sht.title = "表一"  #把默认的“sheet”表单标签的名称改名为“表一”
sht1.title = "表二" #把新建的“sheet1”表单标签的名称改名为“表二”

(3).读取文件表单

from openpyxl import load_workbook

wb = load_workbook(namefile_path)  #读取excel文件
print(wb.get_sheet_names()) #获得excel文件中所有sheet页标签的名称
sheet=wb['sheet1']  #获取excell文件中的标签名为sheet1的表单 
a=sheet[a1.value] #把sheet1页面a1格的值赋给a（一定要用.value取得值，否则只取得位置）

for row in sheet.rows:  #按行读取sheet页内容
for row in sheet.columns: #按列读取sheet页内容

（4）表单页在excel文件中的排序：

创建表单时可指定表单名称及排序

book.create_sheet（'sheet新'，2）  #创建文件中排第三张名称为“sheet新”的表单

表单顺序按0、1、2、3这样排，也可以为负值，表示倒数第几张 -3，-2，-1

（5）表单追加数据

可以把一个列表或元组数据追加成表单新的一行，例：

a=[1,2,3,4,5,6,7]
sheet.append(a)   #sheet表单追加一行数据a，列表的每个值占横向一个单元格

（6）表单最大行数、列数

cols = sheet.max_column  #最大列数
rows = sheet.max_row  #最大行数

（7）选择一行或一列：

row=sheet[2] #方括号里是行序号数字，没有引号

col=sheet['b'] #方括号里是列序号字母，带引号

或者：

row=sheet.rows[1]

col=sheet.columns[1]

然后用for循环进行单元格操作：

#选取sheet表单第三行，依次查看每个单元格的值
for r in sheet[3]:
    print(r.value)

（8）选择指定的区域：

# 按行获取单元格，min_col：
rows = sheet.iter_rows(min_col=1, max_col=2, min_row=2, max_row=20)
# 读取表格的1，2列，min_row：从第二行开始读到第20行，所有内容
# 打印读取内容
for row in rows:
    for cell in row:
        print(cell.value)
# 读取指定的行，先用列表推导式生成包含每一列中所有单元格的元组的列表，再对列表取索引
one_rows = [val for val in sheet.rows][0]   #获取第一行
# 打印读取内容
for cel in one_rows:
    print(cel.value)



# 按列获取单元格,min_col：
cols = sheet.iter_cols(min_col=1, max_col=2, min_row=2, max_row=20)
# 读取表格的1，2列，min_row：从第二行开始读到第20行，所有内容
# 打印读取内容
for col in cols:
    for cell in col:
        print(cell.value)
# 读取指定列,先用列表推导式生成包含每一列中所有单元格的元组的列表，再对列表取索引
one_cols = [val for val in sheet.columns][0]    # 获取第1列
# 打印读取内容
for col in one_cols:
    print(col.value)

（9）删除行或列

sheet.delete_cols(1)    #删除第一列
sheet.delete_rows(2)    #删除第二行

3）单元格操作

（1）单元格赋值操作

sht[a1]="名称" #把sheet表单的a1单元格赋值为“名称”，可以直接这样赋值
c=sht[a1]

或根据行列序号赋值 cell命令：

d=sht.cell(row=1,column=2)

用cell时不能直接赋值，需要配合用value命令：

sht.cell(1，2).value=33   #给b1单元格赋值33

（2）单元格行高列宽设置

from openpyxl.styles import *

行高：参数没有引号

row2 = sheet.row_dimensions[2]
row2.height = 30

列宽：参数有引号

col = sheet.column_dimensions['a']
col.width = 16

（3）单元格合并

合并后的单元格若要赋值，其位置是原表最左上单元格的坐标

sheet.merge_cells("A1:f1")

（4）单元格字体样式设置.font

不需要的参数可以不写，如只设置字体大小就写size=xx

sheet.cell(1, 1).font = Font(name='微软雅黑',size=14,color=Color(index=0),b=0,i=0)
# size   sz  字体大小
# b bold  是否粗体
# i italic  是否斜体
# name family  字体样式

颜色表：
Color(index=0) # 根据索引进行填充
Color(rgb='00000000') # 根据rgb值进行填充

颜色rgb值和索引：
    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4
    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9
    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14
    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19
    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24
    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29
    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34
    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39
    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44
    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49
    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54
    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59
    '00993300', '00993366', '00333399', '00333333',             #60-63

常用颜色索引：
color=Color(index=0)        #黑色BLACK
color=Color(index=1)        #白色WHITE
color=Color(index=2)        #红色RED
color=Color(index=3)        #绿色GREEN
color=Color(index=4)        #蓝色BLUE
color=Color(index=5)        #黄色YELLOW
color=Color(index=8)        #深红DARKRED
color=Color(index=9)        #深绿DARKGREEN
color=Color(index=12)       #深蓝DARKBLUE
color=Color(index=19)       #深黄DARKYELLOW

(5)单元格对齐设置.alignment

horizontal 水平对齐 vertical垂直对齐 wraptext自动换行

sheet.cell(3, 1).alignment = Alignment(horizontal='center',vertical='center',wrapText=True)

# 参数可选项
horizontal = {'fill', 'distributed', 'centerContinuous', 'right',
'justify', 'center', 'left', 'general'}

vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}

（6）单元格边框设置 .border

ws.cell(3,3).border = Border(left=Side(style='thin',color=Color(index=0)),
right=Side(style='thin',color=Color(index=0)),
top=Side(style='thin',color=Color(index=0)),
bottom=Side(style='thin',color=Color(index=0)))

可以编个函数对指定表格区域加边框

def set_border(row_start,row_end,col_start,col_end):
    for i in range(row_start,row_end+1):#行
        for j in range(col_start,col_end+1): #列
            sheet.cell(i, j).border = Border(left=Side(style='thin'),
          right=Side(style='thin'),
          top=Side(style='thin'),
          bottom=Side(style='thin'))            
    return

（7）单元格背景色填充设置.fill

sheet.cell(4，5).fill = PatternFill(patternType='solid',start_color=Color(index=5))

PatternFill(patternType='solid',fgColor=Color(), bgColor=Color())
# fgColor或start_color 起始颜色（用这个就行，给单元格设背景色）
# bgColor或end_color   结束颜色
# 参数可选项patternType='solid'  纯色填充
patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 
               'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 
               'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 
               'lightHorizontal', 'darkTrellis', 'darkVertical'}

参数具体意义见另外做的实验：

python openpyxl中单元格颜色填充PatternFill(patternType=‘ ’)效果实测

（8）获取单元格索引信息（行号、列号）：

for col in sheet['e']:        #遍历列e的所有单元格
    print(col)            
    c=col.col_idx            #5，即e列的序号是第5列
    d=col.column             #5，同上（两者差别待学习）
    e=col.row                #1，即行号，从1开始
    f=col.column_letter      #e，即第e列的序号字母e
    h=col.coordinate         #e1，即第一个单元格e1格

4）保存文件：

book.save("savefilepath.xlsx")所有表单处理完后 保存book为xlsx文件