一、openpyxl基本操作
1、导入表
import openpyxl wb=openpyxl.load_workbook(file_path,data_only="False",read_only="False")
如果需要去公式，data_only改成TRUE；如果需要只读，read_only改成TRUE；
2、新建表&创建sheet
from openpyxl.workbook import Workbook n_wb=Workbook() n_ws=n_wb.active #获取新建表的当前sheet n_ws=n_wb.create_sheet(sheetname,index) #插入一个新的sheet
3、获取文档的全部sheet名字
names=wb.sheetnames
4、根据索引或sheet名获取sheet
ws=wb['sheename'] #使用sheet名 ws=wb.sheetnames[0] #使用索引
5、读取单元格
ws.cell("A5") #直接输入单元格读取
ws.cell(row_index,col_index) #输入索引读取
cell.value #读取单元格的值
eg:
ws.cell("A5").value
还可以使用iter_rows,iter_columns;
iter_rows即逐行读取,iter_columns逐列读取：
for row in ws.iter_rows(min_row=1,max_row=5,min_col=1,max_col=10): for cell in row: print(cell.value)
6、根据坐标获取行列，根据列字母获取数字，根据数字获取列字母
from openpyxl.utils import coordinate_from_string,get_column_letter,column_index_from_string
print(coordinate_from_string('a11')) #返回单元格列与行的元组形式
('A', 11)
print(get_column_letter(12)) #根据列索引返回对应的字母
L
print(column_index_from_string("B")) #根据字母返回对应的列索引
2
7、保存
wb.save(file_path)
二、多sheet 相同内容合并
由于工作中遇到了多个sheet有相同内容需要合并到一个新表的情况，但是每个表内容所对应的坐标不同，同时有部分内容存在单元格内数值存在换行的情况，因此使用openpyxl对多sheet 进行合并。
import openpyxl
from openpyxl.utils import coordinate_from_string,column_index_from_string,get_column_letter
import os
from openpyxl.workbook import Workbook
class Info_day:
def __init__(self,filename,start_name,rowend_name,colend_name):
self.file_name=filename ##读取的文件名
self.start_name=start_name #每个表所需内容的起始参数名
self.rowend_name=rowend_name #每个表所需内容的结束行名
self.colend_name=colend_name #每个表所需内容的结束列名
def col_and_row(self,sn):
"""读取每个工作表起始与结束数据的行列"""
count_a1 = 0
count_a2 = 0
count_b2 = 0
list_index=[]
for row in sn.iter_rows(min_row=1,max_row= 20,max_col=20):
for cell in row:
if type(cell.value) is str and cell.value.replace("n","") == self.start_name and count_a1==0: #加==0是因为防止迭代范围内遇到与结束行列名字相同的cell，加type=str是因为所需要的数值内容是str(如果type为其他类型可以根据需要更改）
a1 = cell.coordinate
row_start_letter=cell.row #开始行index
column_start_letter = column_index_from_string(cell.column) #开始列index
count_a1 += 1
list_index.append(row_start_letter)
list_index.append(column_start_letter)
elif type(cell.value) is str and cell.value.replace("n","") == self.rowend_name and count_a2==0:
row_end_letter=cell.row #结束行index
count_a2 += 1
list_index.append(row_end_letter)
elif type(cell.value) is str and cell.value.replace("n","") == self.colend_name and count_b2 ==0:
column_end_letter = column_index_from_string(cell.column) #结束列index
count_b2 += 1
list_index.append(column_end_letter)
else:
if count_a1 == 1 and count_a2 == 1 and count_b2 ==1:
break
else:
continue
return list_index #返回一个列表
def handle_excel(self,n_file_path): ##n_file_path 为新Excel保存的位置
wb=openpyxl.load_workbook(self.file_name,data_only=True) ##加载excel表
if os.path.exists(n_file_path): ##如果文件存在，则删除
os.remove(n_file_path)
n_wb=Workbook() #新建一个文件
n_ws=n_wb.active
for snames in wb.sheetnames:
sb=wb[snames]
list_index = self.col_and_row(sb)
print(list_index)
rowindex_start=list_index[0]
rowindex_end = list_index[3]
colindex_start=list_index[1]
colindex_end = list_index[2]
#最后一列后增加一列每行数据来源的sheetname
letter_name=get_column_letter(colindex_end+1) #返回列的字母形式
start_name = letter_name + str(rowindex_start)
end_name = letter_name + str(rowindex_end)
print(snames,start_name,end_name)
for cell_range in sb[start_name:end_name]:
for cell in cell_range:
cell.value=snames
for row in sb.iter_rows(min_row=rowindex_start,max_row=rowindex_end,
min_col=colindex_start,max_col=colindex_end+1):
n_ws.append((cell.value for cell in row)) #这种方式可以直接将一行或列d的数据append到其他sheet中
n_wb.save(n_file_path)
