赞
踩
读取Excel文件中的工作表(sheet)也是重要的知识点。在使用openpyxl库时,可以通过以下方式来读取工作表:
sheetnames
属性获取所有工作表的名称列表,然后通过工作簿对象的[sheet_name]
或者get_sheet_by_name(sheet_name)
方法来获取指定名称的工作表对象。- workbook = openpyxl.load_workbook('p1.xlsx')
- sheet_names = workbook.sheetnames
- sheet = workbook[sheet_names[0]] # 根据名称获取第一个工作表对象
- workbook = openpyxl.load_workbook('p1.xlsx')
- sheet = workbook.worksheets[0] # 获取第一个工作表对象
一旦获取了工作表对象,我们就可以使用工作表对象进行单元格数据的读取,遍历行和列等操作。
实例
- from openpyxl import load_workbook
-
- workbook = load_workbook("p1.xlsx")
-
- # 1、获取excel文件中所有shell名称
-
- print(workbook.sheetnames)
-
- # 2、选择sheet,基础sheet名称
- """
- sheet = workbook["人员"]
- cell = sheet.cell(2,3)
- print(cell.value)
- """
-
- # 3、选择sheet,基于索引位置
- """
- sheet = workbook.worksheets[0] # worksheets 索引函数
- cell = sheet.cell(2,3)
- print(cell.value)
- """
-
- # 4、循环所有的sheet,三种方式!
- """
- for name in workbook.sheetnames: # sheetnames 名称函数
- sheet = workbook[name]
- cell = sheet.cell(1,1)
- print(cell.value)
- """
-
- """
- for sheet in workbook.worksheets: # worksheets 索引函数
- cell = sheet.cell(1, 1)
- print(cell.value)
- """
-
- """
- for sheet in workbook:
- cell = sheet.cell(1,1)
- print(cell.value)
- """
- from openpyxl import load_workbook
-
- wb = load_workbook("p1.xlsx")
- sheet = wb.worksheets[0] # 获取索引位置为0的sheet --> 也就是第一个sheet
- """
- # 1、获取第N行第N列的单元格(位置从1开始)
- cell = sheet.cell(1,1)
- print(cell.value) # 内容
- print(cell.style) # 样式
- print(cell.font) # 字体
- print(cell.alignment) # 排列情况
- """
-
- """
- # 2、获取某个单元格
- v1 = sheet["A2"]
- print(v1.value)
- v2 = sheet["C2"]
- print(v2.value)
- """
-
- # 3、获取第N行所有的单元格
- """
- for cell in sheet[1]:
- print(cell.value)
- """
-
- # 4、获取所有行的数据
- """
- for row in sheet.rows:
- print(row[0].value,row[1].value,row[2].value)
- """
-
- # 4、获取所有列的数据
- """
- for clo in sheet.columns:
- print(clo[0].value,clo[1].value,clo[2].value)
- """
- import openpyxl
- from openpyxl import load_workbook
-
- # 打开excel工作表
- workbook = openpyxl.load_workbook("p1.xlsx")
-
- # 获取所有工作表的名称列表
- sheet_name = workbook.sheetnames
-
- # 选择第一个工作表进行操作
- sheet = workbook[sheet_name[0]]
-
- # 合并A1:A2
- sheet.merge_cells('A1:A2')
-
- # 设置合并后的值
- sheet['A1'] = 'OK!'
-
- # 保存修改
- workbook.save('p4.xlsx')
openpyxl 是一个用于处理 xlsx 格式 Excel 表格文件的第三方 python 库,其支持 Excel 表格绝大多数基本操作。
使用 pip 或通过专门 python IDE(如pyCharm)进行安装
其中pip安装方法,命令行输入: pip install openpyxl
第一步先是要导入 openpyxl 模块
import openpyxl
通过调用方法 load_workbook(filename) 进行文件读取,该方法中还有一个 read_only 参数用于设置文件打开方式,默认为可读可写,该方法最终将返回一个 workbook 的数据对象。
- # 文件必须是xlsx格式,如果是其他格式在执行前可利用win32辅助转化
- wb = openpyxl.load_workbook('p1.xlsx')
每一个 Excel 表格中都会有很多张 sheet 工作表,在对表格操作前需要先选定一张工作表
- # 获取所有工作表名(返回一个列表)
- sheets = wb.get_sheet_names()
-
- # 获取某一特定的工作表
- sheet = wb.get_sheet_by_name('Sheet2')
-
- # 获取工作表的表名
- sheet_name = sheet.title
-
- # 一般来说,表格大多数用到的是打开时显示的工作表,这时可以用active来获取当前工作表
- sheet = wb.active
-
对 Excel 表格的操作最终都落于对单元格的操作,获取单元格有两种获取方法:sheet[列行名]和 sheet.cell(row,column)
- # 通过sheet[列行名]获取
- a = sheet['A2']
-
- # 通过sheet.cell(row,column)获取
- b = sheet.cell(1, 2) # 即sheet['B1']
-
- # 获取单元格内容
- print(a.value)
-
- # 获取单元格所在列和行
- print(‘a is ’+str((a.column,a.row)))
-
需要注意的是,sheet.cell(row,column)中参数分别是行和列,且必须为整数,如果列为英文字母,可以利用 openpyxl.utils 中的 column_index_from_string (char)进行字母数字的转化。顺便一说,同理也可以利用 get_column_letter(number) 进行数字字母间的转化
- from openpyxl.utils import get_column_letter, column_index_from_string
-
- # 对列进行字母/数字转化
- c_num = column_index_from_string('B') # c_num = 2
- c_char = get_column_letter(5) # c_char = 'E‘
在处理 Excel 表格有时可能需要对表格进行遍历查找,openpyxl 中便提供了一个行和列的生成器 (sheet.rows和sheet.columns) ,这两个生成器里面是每一行(或列)的数据,每一行(或列)又由一个 tuple 包裹,借此可以很方便地完成对行和列的遍历
- # 对行进行遍历,输出A1,B1,C1
- for row in sheet.rows:
- for cell in row:
- print(cell.value)
-
- # 对列进行遍历,输出A1,A2,A3
- for column in sheet.columns:
- for cell in column:
- print(cell.value)
学习时还发现也可以通过 list(sheet.rows)index 对某一行或列进行遍历,而在此值得注意的是,由于sheet.rows(或sheet.columns)
是生成器类型,是不能直接调用的,需将其转化为一个 list 类型,然后再通过索引遍历
- # 对某一特定的行进行遍历
- for cell in list(sheet.rows)[0]:
- print(cell.value)
同时,也可以通过使用 sheet[行列值:行列值] 来对给定单元格范围进行遍历
- # 对某一单元格范围进行遍历
- for spaces in sheet['A1':'B2']:
- for cell in spaces:
- print(cell.value)
另外,有时候我们还可能需要确定表格的大小,即获取表格行和列的最大值,可以用 max_row 和 max_column 来获取
- # 获得最大列和最大行
- print(sheet.max_row)
- print(sheet.max_column)
在开头读取时已经介绍,默认的打开方式为可读可写,那么使用 load_workbook(filename) 读取 Excel 文档后也就可以直接写入了。另外,如果需要新建一个 Excel 文件,可以使用 Workbook()方法,同时它会自动提供一个 sheet 工作表。对于删除一个工作表,则可以使用 workbook 对象的 remove(sheet) 方法删除
- # 新建一个Excel文档
- wb = openpyxl.Workbook()
-
- # 删除某个工作表
- wb.remove(sheet)
获取工作表和之前一样,如果使用 load_workbook(filename) 读取,那么获取工作表后可以直接通过sheet[行列值]写入单元格。学习时,有资料介绍还可以传入Excel中的公式进行赋值,不过要注意,在读取文件时需要加上参数 data_only=True ,这样才能返回数字,否则将返回字符串,即公式本身。
- # 直接赋值
- sheet['A1'].value = 2
-
- # 公式赋值
- sheet['A6'].value = '=SUM(A1:A5)'
另外,也可使用 sheet.append(parameters) 一行或多行写入
- # 写入一行
- row = [1 ,2, 3, 4, 5]
- sheet.append(row)
-
- # 写入多行
- rows = [
- ['ID', 'Name', 'Department'],
- ['001', 'Lee','CS'],
- ['002', 'John','MA'],
- ['003', 'Amy','IS']
- ]
- sheet.append(rows)
写完文件后,使用 workbook.save(path+filename)进行保存,不过要注意文件扩展名一定要是 xlsx 格式
- # 保存文件至当前目录
- wb.save('new_file.xlsx')
单元格样式主要包括字体、边框、颜色以及对齐方式等,这些均位于 openpyxl.styles 库中
- # 导入字体、边框、颜色以及对齐方式相关库
- from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment
通过 sheet 单元格 font 属性设置字体风格
- # 设置字体风格为Times New Roman,大小为16,粗体、斜体,颜色蓝色
- sheet['A1'].font = Font(name='Times New Roman', size=16, bold=True, italic=True, color=colors.BLUE)
通过 sheet 单元格 alignment 属性设置文本对齐风格
- # 通过参数horizontal和vertical来设置文字在单元格里的对齐方式,此外设置值还可为left和right
- sheet['B1'].alignment = Alignment(horizontal='center',vertical='center')
通过 sheet 单元格 border 属性设置字体风格
- # 首先设置边框四个方向的线条种类
- left, right, top, bottom = [Side(style='thin', color='000000')] * 4
- # 再将各方向线条作为参数传入Border方法
- sheet['C1'].border = Border(left=left, right=right, top=top, bottom=bottom)
行和列的长度大小可以通过 row_dimensions[序号].height 和 column_dimensions[标号].width 来设置
- # 设置行高
- sheet.row_dimensions[1].height = 25
-
- # 设置列宽
- sheet.column_dimensions['D'].width = 15.5
对单元格的合并与拆分,主要是通过 sheet 的 merge_cells(args1:args2)和unmerge_cells(args1:args2) 两个方法来实现的;当然,除了对角矩形区域化合并,也可以对一行或一列进行合并,只需相应修改参数即可。不过,这里要注意的是,合并后单元格显示的文本内容是合并前最左上角单元格的内容,而其他单元格内容则会自动清除。
- # 合并单元格
- sheet.merge_cells('A1:B2')
-
- # 拆分单元格
- sheet.unmerge_cells('A1:B2')
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。