python自动化办公之使用openpyxl操作excel

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'。