from openpyxl.styles import PatternFill
workbook = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\user_info_format1.xlsx') # 加载已经存在的excel
name_list = workbook.sheetnames #获取所有的sheet表名
worksheet = workbook[name_list[0]]
fill = PatternFill("solid", fgColor="FF0000") #设置填充样式
m_list = worksheet.merged_cells # 合并单元格的位置信息,可迭代对象(单个是一个'openpyxl.worksheet.cell_range.CellRange'对象),print后就是excel坐标信息
cr = []
for m_area in m_list:
# 合并单元格的起始行坐标、终止行坐标。。。。,
r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
# 纵向合并单元格的位置信息提取出
if r2 - r1 >0 or c2 - c1 >0:
cr.append((r1, r2, c1, c2))
print('符合条件%s' % str(m_area))
#print(cr)
# 这里注意需要把合并单元格的信息提取出再拆分
for r in cr:
worksheet.unmerge_cells(start_row=r[0], end_row=r[1],
start_column=r[2], end_column=r[3])
if r[3]-r[2]==0: #单独列合并
for row in (range(r[0], r[1]+1)):
worksheet.cell(row=row, column=r[3], value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row, column=r[3]).fill = fill #将对应拆分单元格填充颜色
elif r[1]-r[0]==0 : #单独行合并
for column in (range(r[2], r[3]+1)):
worksheet.cell(row=r[1], column=column, value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row=r[0], column=column).fill = fill #将对应拆分单元格填充颜色
else : #多行、列合并
for row in (range(r[0], r[1]+1)):
for column in (range(r[2], r[3]+1)):
worksheet.cell(row, column, value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row, column).fill=fill #将对应拆分单元格填充颜色
workbook.save(r'C:\Users\Administrator\Desktop\data25.xlsx') #另存为新表
《用Python处理Excel表格》下篇来啦!
身为工作党或学生党的你,平日里肯定少不了与Excel表格打交道的机会。当你用Excel处理较多数据时,还在使用最原始的人工操作吗?现在教你如何用Python处理Excel,从此处理表格再也不加班,时间缩短数十倍!
上篇我们进行了一些事前准备,目的是用Python提取Excel表中的数据。而这一篇便是在获取数据的基础上,对Excel表格的实操处理。
第9行代码用来指定创建的excel的活动表的名字:
·不写第9行,默认创建sheet
·写了第9行,创建指定名字的sheet表
第9行代码,通过给单元格重新赋值,来修改单元格的值
第9行代码的另一种写法sheet['B1'].value = 'age'
第10行代码,保存时如果使用原来的(第7行)名字,就直接保存;如果使用了别的名字,就会另存为一个新文件
插入有效数据
使用append()方法,在原来数据的后面,按行插入数据
·insert_rows(idx=数字编号, amount=要插入的行数),插入的行数是在idx行数的下方插入
·insert_cols(idx=数字编号, amount=要插入的列数),插入的位置是在idx列数的左侧插入
·delete_rows(idx=数字编号, amount=要删除的行数)
·delete_cols(idx=数字编号, amount=要删除的列数)
move_range(“数据区域”,rows=,cols=):正整数为向下或向右、负整数为向左或向上
举个例子:
openpyxl.styles.Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
其中,字体颜色中的color是RGB的16进制表示
再者,可以使用for循环,修改多行多列的数据,在这里介绍了获取的方法
Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
水平对齐:‘distributed’,‘justify’,‘center’,‘left’, ‘centerContinuous’,'right,‘general’
垂直对齐:‘bottom’,‘distributed’,‘justify’,‘center’,‘top’
当然,你仍旧可以调用for循环来实现对多行多列的操作
设置行列的宽高:
·row_dimensions[行编号].height = 行高
·column_dimensions[列编号].width = 列宽
合并单元格有下面两种方法,需要注意的是,如果要合并的格子中有数据,即便python没有报错,Excel打开的时候也会报错。
merge_cells(待合并的格子编号)
merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
拆分单元格的方法同上
unmerge_cells(待合并的格子编号)
unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
create_sheet(“新的sheet名”):创建一个新的sheet表
第11行,使用title修改sheet表的名字
remove(“sheet名”):删除某个sheet表
要删除某sheet表,需要激活这个sheet表,即:将其作为活动表(关于活动表的定义请看前面文章开头写的有)下面8~11行代码展示了原始活动表与手动更换活动表,第13行代码删掉活动表
背景知识
numpy与pandas
NumPy是 Python 语言的一个扩展程序库,支持大量的维度数组与矩阵运算,此外也针对数组运算提供大量的数学函数库;pandas 是基于NumPy 的一种工具,该工具是为解决数据分析任务而创建的,我们需要利用Pandas进行Excel的合并
1.下面的代码生成了一个5行3列的包含15个字符的嵌套列表
(注意,第4行代码:15是等于35的,如果是15对应43,或者16对应5*3都会报错)
(注意,第5行代码,虽然5行3列是15个数据,但是可以指定数据从1开头,到16结束)
2.添加表头
使用pandas库的DataFrame来添加表头。关于打印的结果,把最左侧的一列去掉之后会发现结果很和谐,这是因为最左侧的一列代表行号。此时xx变量的类型是
xlsxwriter模块一般是和xlrd模块搭配使用的,
xlsxwriter:负责写入数据,
xlrd:负责读取数据。
1.创建一个工作簿
2.创建sheet表
3.写入数据
代码如下:# -*- coding: utf-8 -*-import xlrdimport uuidclass Student():
def __init__(self, id, **kw):
self.id = id
for k, v in kw.items():
setattr(self, k, v)
def __str__(self):
return '%s(id=%s,column1=%s,column2=%s,column3=%s,column4=%s)' \ % (
self.__class__.__name__, self.id, self.column1, self.column2, self.column3,
self.column4)def read_excel():
# 打开文件
workbook = xlrd.open_workbook(r'py.xlsx')
# 获取所有sheet
print('打印所有sheet:', workbook.sheet_names())
sheet2 = workbook.sheet_by_index(0) # sheet索引从0开始
rows_num = sheet2.nrows
cols_num = sheet2.ncolsfor r in range(rows_num):
# 一行数据的实体类
entity_dict = {}
for c in range(cols_num):
cell_value = sheet2.row_values(r)[c]
# print('第%d行第%d列的值:[%s]' % (r, c, sheet2.row_values(r)[c]))
if (cell_value is None or cell_value == ''):
cell_value = (get_merged_cells_value(sheet2, r, c))
# 构建Entity
the_key = 'column' + str(c + 1)
# 动态设置各属性值
entity_dict[the_key] = cell_value
entity_dict['id'] = getUUID()
stu = Student(**entity_dict)
print(stu)def get_merged_cells(sheet):
"""
获取所有的合并单元格,格式如下:
[(4, 5, 2, 4), (5, 6, 2, 4), (1, 4, 3, 4)]
(4, 5, 2, 4) 的含义为:行 从下标4开始,到下标5(不包含) 列 从下标2开始,到下标4(不包含),为合并单元格
:param sheet:
:return:
"""
return sheet.merged_cellsdef get_merged_cells_value(sheet, row_index, col_index):
"""
先判断给定的单元格,是否属于合并单元格;
如果是合并单元格,就返回合并单元格的内容
:return:
"""
merged = get_merged_cells(sheet)
for (rlow, rhigh, clow, chigh) in merged:
if (row_index >= rlow and row_index <rhigh):
if (col_index >= clow and col_index <chigh):
cell_value = sheet.cell_value(rlow, clow)
# print('该单元格[%d,%d]属于合并单元格,值为[%s]' % (row_index, col_index, cell_value))
return cell_valuebreak
return Nonedef getUUID():
return uuid.uuid1().hexif __name__ == "__main__":
read_excel()