openpyxl的使用
文章目录
预备知识
1.openpyxl只能处理xlsx格式的excel文档,若要处理更早格式需要用其他库,例如xlrd,xlwt等。
2. 使用到的excel对象:
workbook:工作簿
sheet:表
cell:表格单元
3. 安装openpyxl:
pip install openpyxl
文件操作
打开已有文件
import openpyxl
book = openpyxl.load_workbook("work1/基础信息.xlsx")
sheet = book.active
print(sheet.max_row) #打印表的行数
创建新的excel表以及创建新的sheet
import openpyxl
book = openpyxl.Workbook() #创建工作表
sheet = book.active
sheet = book.create_sheet('Mysheet', 0)
create_sheet(title=None, [index=None])
title表示sheet名字,index表示sheet位置,默认插入在最后,index为0插入在最前
更改sheet名字
sheet.title = 'sheetx'
更改excel名字或保存
book.save(filename='abc.xlsx')
相当于另存为,一般放最后
删除sheet
book.remove(sheet)
遍历工作表
同一个工作簿含有多个sheet的情况
for i in range(0, len(book.sheetnames)):
sheet = book[book.sheetnames[i]]
print(sheet.title)
sheet.sheetnames
可获取所有sheet的名字
for sheet in book:
print(sheet.title)
示例:
for sheet_name in book_check.sheetnames:
if '技术状况评定记录表' in sheet_name:
sheet_check = book_check[sheet_name]
print(sheet_check)
book.get_sheet_by_name()
和book.get_sheet_names()
方法已被丢弃
复制sheet到另一个excel
book_1 = openpyxl.load_workbook("test.xlsx")
sheet_1 = book_1.active
print(book_1.sheetnames)
book_new = openpyxl.Workbook() #创建新的excel
sheet_new = book_new.active
sheet_new = book_1.copy_worksheet(sheet_1)
print(sheet_new['C3'].value) #test
book_new.save('test_2.xlsx')
单元格操作
写入单元格(两种赋值方式)
sheet['B2'] = 1
sheet.cell(row=2, column=2).value = 2
或sheet.cell(row=2, column=2,value = 2)
- 在表格后面附加一组值:
sheet.append(row)
row可以是列表或元组
读取单元格
与写入类似
a=sheet['B2'].value
a=sheet.cell(row=2, column=2).value
获取最大行数或列数
print(sheet.max_column)
print(sheet.max_row)
查看sheet的范围:sheet.dimensions
获取单元格下标
cell对象的属性
- value 返回单元格中存储的值
- row 返回单元格的所在行数字
- column 返回单元格的所在列数字
- coordinate 返回单元格的坐标
for space in sheet_doc_3['A3:B3']:
for cell in space:
print(cell.value)
print("index", cell.coordinate) #返回的是行列例如'A3'
print("row", cell.row) #返回单元格所在行
print("col", cell.column)
下标转换
获取单元格所在的列的字母:get_column_letter()
将列的字母转换为数字:column_index_from_string
>>> import openpyxl
>>> from openpyxl.utils import get_column_letter,column_index_from_string
>>> get_column_letter(1)
'A'
>>> column_index_from_string('C')
3
>>>
插入行或列
sheet.insert_cols(4, 2)
第1个入参表示位置,第2个表示数量
sheet.insert_rows(4, 2)
设置单元格边框
import openpyxl
from openpyxl.styles import Border,Side
thin = Side(border_style="thin",color="000000")
non = Side(border_style=None,color="000000")
border_set = Border(left=thin, right=thin, top=thin, bottom=thin)
sheet["E4"].border = Border(left=thin, right=thin, top=non, bottom=thin)
获取合并单元格
for i in sheet_doc_3.merged_cell_ranges:
row_start, row_end, col_start, col_end = i.min_row, i.max_row, i.min_col, i.max_row
print(row_start, row_end, col_start, col_end)
获取合并单元格的行数
cell_merge_ass = sheet_check.cell(row=6,column=4)
for merged_range_ass in sheet_check.merged_cell_ranges:
if cell_merge_ass.coordinate in merged_range_ass:
print(merged_range_ass)
print(merged_range_ass.min_row, merged_range_ass.max_row)
取消合并 unmerge_cells()
打印表格中的所有合并单元格
print(sheet_ch.merged_cells.ranges)
print(sheet_ch.merged_cell_ranges)
print(sheet_ch.merged_cells)
判断指定单元格是否是合并单元格
if 'B9' in sheet_ch.merged_cells:
print('B9 是合并单元格')
遍历单元格
范围遍历
cells = sheet['A4':'D10']
for c1, c2, c3, c4 in cells:
print(c1.value, c2.value, c3.value, c4.value)
for i in range(1, sheet.max_row+1):
for j in range(1, sheet.max_column+1):
print(sheet.cell(row=i, column=j).value)
按行迭代
for row in sheet.iter_rows(min_row=5, min_col=4, max_row=sheet.max_row, max_col=5):
for cell in row:
print(cell.value)
按列迭代
for row in sheet.iter_cols(min_row=5, min_col=4, max_row=sheet.max_row, max_col=5):
for cell in row:
print(cell.value)
遍历某一行
例如第5行
for col in range(1, (sheet_doc.max_column)+1):
row_choose = 5
print(sheet_doc.cell(row=row_choose, column=col).value)
遍历目录下的所有excel文件
dir_file = '/home/x/work3'
file_num = 0
for root, dirs, files in os.walk(dir_file):
for file in files:
(filename, extension) = os.path.splitext(file) #将文件名拆分为文件名与后缀
if (extension == '.xlsx'): #判断该后缀是否为.xlsx文件
file_ass.append(file)
file_num= file_num+1 #记录文件数
print(file_num, '\n', file_ass)
for i in range(0, len(file_ass)):
print(i, file_ass[i])
带路径打印file
for root, dirs, files in os.walk(dir_file):
for dir in dirs:
print(os.path.join(root, dir)) #打印所有路径
for file in files:
(filename, extension) = os.path.splitext(file) #将文件名拆分为文件名与后缀
if (extension == '.xlsx'):
item_num += 1
print(item_num, os.path.join(root, file)) #打印带路径的file
处理excel单元格内容(即字符串)常用的方法和正则表达式
type(a) 为str
a.rstrip('桥')
去掉字符串开始是”桥“的字符a.lstrip()
去掉字符串结尾的某字符,strip()方法用于删除开始或结尾的字符a.replace('\n', '')
去掉字符串中的换行b = a.upper()
将字符串中的字母换成大写b= re.sub(u'[\u4e00-\u9fa5]', '', a)
去掉字符串中的中文b= re.sub(r'\d+', '', a)
去掉字符串中的数字b= re.sub('\\(.*?\\)', '', a)
去掉括号里的内容- 提取括号里的内容 【处理时注意区分中文()和英文(),以及’/‘和’/'的区别】
pattern = re.compile(r'[(](.*?)[)]')
temp = re.findall(pattern, a)
b= temp[0]
a in b
判断字符的包含关系- 判断字符串中是否存在数字:
bool(re.search(r'\d', a))
清空列表的方式
list_1=[1,2,3]
list_1.clear()
或 del list_1[-3:]
列表生成式 output_loc = [item for item in range(1,15+1)]
小结
参考链接:
极客教程openpyxl教程
官方openpyxl文档
用openpyxl遍历表格的行列
Python 获取指定路径下的指定后缀的文件名
openpyxl超详细笔记
最近用openpyxl帮老姐处理大批量excel文件,就此总结一波用到的东西。遇到诸多问题都因表格格式不规范导致,心力交瘁。经常遇到同样的表格格式多加一行这样的问题,以及sheet时不时多出一些不一样的。
在读取日期时读出来类似43770这样的格式,需要搬到另一个表格中,采取了直接搬过去再设置单元格格式才能正确显示出日期。
读取类似2/5这样的读出来变成了浮点型0.4,可能需要在读取之前先设置单元格格式再读取。
所读取单元格为空时,由于读取时统一进行了字符串处理,再赋值给另一个单元格则为'None'。