(六十五)使用columns获取工作表的所有列
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
#获取收入表(mybook.active)的所有列
mycolumns=mybook.active.columns
mycolindex=2
#循环收入表(mycolumns)的B,C,D列(mycol)
for mycol in list(mycolumns)[1:4]:
mycolsum=0
#循环列(mycol)的5,6,7,8行的4个单元格
for mycell in mycol[4:8]:
mycolsum+=mycell.value
#将求和数据(mycolsum)写入合计单元格
mybook.active.cell(9,mycolindex).value=mycolsum
mycolindex+=1
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mycolumns=mybook.active.columns
表示收入表(mysheet.active)的所有列(mycolumns)
(六十六)使用iter_cols()指定数据范围
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet=mybook.active
#指定在收入表(mysheet)中按列操作单元格的数据范围(myrange)
myrange=mysheet.iter_cols(min_row=5,min_col=2,max_row=8,max_col=4)
mynewrow=['合计']
#循环收入表(myrange)的B,C,D列(mycol)
for mycol in myrange:
#按列对单元格数据求和
mycolsum=sum([mycell.value for mycell in mycol])
mynewrow.append(mycolsum)
#直接在收入表(mysheet)的末尾添加一行(分类收入)合计
mysheet.append(mynewrow)
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
myrange=mysheet.iter_cols(min_row=5,min_col=2,max_row=8,max_col=4)
表示在收入表(mysheet)中设置按列操作的数据范围,即指定B5到D8之间的所有列,该方法支持仅指定部分参数,其他参数按照默认值处理。
myrange=mysheet.iter_cols(min_row=5,min_col=2)
表示在收入表(mysheet)指定B5到最后一个单元格之间的所有单元格
myrange=mysheet.iter_cols(max_row=8,max_col=4)
表示在收入表(mysheet)指定第一个单元格到D8单元格之间的所有单元格
(六十七)使用行列起止编号指定数据范围
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet=mybook.worksheets[0]
myrowindex=0
#按行循环收入表(mysheet)的['B5':'D8']范围的行(myrow)
for myrow in mysheet['B5':'D8']:
myrowsum=0
myrowindex+=1
#循环行(myrow)的单元格(mycell)
for mycell in myrow:
myrowsum+=mycell.value
#在行(myrow)的最后一个单元格中写入合计
mysheet.cell(myrowindex+4,5).value=myrowsum
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mysheet['B5':'D8']
也可写成 mysheet['B5:D8']
(六十八)使用起止列号获取指定范围的列
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet = mybook.active
# 获取收入表(mysheet)的B,C,D列
myrange = mysheet['B':'D']
mycolindex = 2
# 按行循环收入表(mysheet)的B,C,D列
for mycol in myrange:
# 计算列(mycol)的5,6,7,8行的单元格数据合计
mycolsum = sum(mycell.value for mycell in mycol[4:8])
# 在列(col)的第9行的单元格中写入合计数据(mycolsum)
mysheet.cell(9, mycolindex).value = mycolsum
mycolindex += 1
mysheet['A9'] = '合计'
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
myrange = mysheet['B':'D']
表示收入表 (mysheet) 的B列到D列之间的所有列,B表示起始列号,D表示终止列号
也可写成 myrange = mysheet['B:D']
(六十九)根据列号获取该列的所有单元格
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet = mybook.active
# 对收入表(mysheet)的D列的5,6,7,8行数据求和
mycoldsum = sum([mycell.value for mycell in mysheet['D'][4:]])
# 对收入表(mysheet)的C列的5,6,7,8行数据求和
mycolcsum = sum([mycell.value for mycell in mysheet['C'][4:]])
# 对收入表(mysheet)的B列的5,6,7,8行数据求和
mycolbsum = sum([mycell.value for mycell in mysheet['B'][4:]])
# 在收入表(mysheet)的B,C,D列的第九行中写入合计数据
mysheet['D9'] = mycoldsum
mysheet['C9'] = mycolcsum
mysheet['B9'] = mycolbsum
mysheet['A9'] = '合计'
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mycoldsum = sum([mycell.value for mycell in mysheet['D'][4:]])
表示对收入表(mysheet)的 D 列的第4行之后的所有单元格数据求和
mysheet['D'] 表示收入表的 D 列
mysheet['D'][4:] 表示以切片方式获取 D 列的第4行之后(从第5行开始)的所有单元格
mysheet['D9'] = mycoldsum 表示在收入表(mysheet)的 D9 单元格中写入合计数据
(七十)根据起止列号隐藏指定范围的列
# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx", data_only=True)
mysheet = mybook.active
# 隐藏员工表(mysheet)的D,E,F列
mysheet.column_dimensions.group('D', 'F', hidden=True)
# 保存工作簿,保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")
mysheet.column_dimensions.group('D', 'F', hidden=True)
参数 'D' 表示隐藏的起始列号,'F' 表示隐藏的终止列号,hidden=True 表示执行隐藏操作
(七十一)冻结指定单元格之前的行和列
# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx", data_only=True)
mysheet = mybook.active
# 冻结员工表(mysheet)的D列和2行之前的所有行和列
mysheet.freeze_panes = 'D2'
# 保存工作簿,保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")
mysheet.freeze_panes = 'D2'
表示冻结员工表(mysheet)的 D 列之前(不包括 D 列)的所有列和第2行(不包括第2行)之上的所有行
(七十二)将数字列号转化为字母列号
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet = mybook.active
# 循环收入表(mysheet)的1-4列
for mycol in range(1, 5):
if mycol % 2 == 0:
#将数字列号转换为字母列号
mycolletter = openpyxl.utils.get_column_letter(mycol)
# 循环偶数列的5-8行
for myrow in range(5, 9):
# 清空偶数列的5-8行的所有单元格数据
mysheet[mycolletter + str(myrow)] = ''
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mycolletter = openpyxl.utils.get_column_letter(mycol)
表示将数字列号(mycol)转换为字母列号(mycolletter)
mysheet[mycolletter + str(myrow)]
表示收入表(mysheet)的某个单元格,如果是 mysheet[mycolletter + myrow] 则将出错,即字母和数字不能拼接(+),必须使用 str(myrow) 将数字 myrow 转换为字符串
如果 openpyxl.utils.cell.get_column_letter(29) 或 openpyxl.utils.get_column_letter(29) 则表示AC列
(七十三)将字母列号转换为数字列号
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet = mybook.active
mycol = openpyxl.utils.column_index_from_string('B')
# print(mycol)
# 删除收入表(mysheet)的第2列
mysheet.delete_cols(mycol, 1)
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mycol = openpyxl.utils.column_index_from_string('B')
表示将列号 ‘B’ 转换位列号 ‘2’ ,然后使用 mysheet.delete_cols(mycol, 1) 方法删除 ‘B’ 列,即使用 mysheet.delete_cols(2, 1)方法删除B列,如果直接使用 mysheet.delete_cols("B", 1)将会报错。
如果openpyxl.utils.cell.column_index_from_string('AC')或openpyxl.utils.column_index_from_string('AC')则表示 29列
(七十四)自定义工作表指定列的宽度
# 导入openpyxl模块
import openpyxl
# 根据“收入表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("收入表.xlsx", data_only=True)
mysheet = mybook.active
#设置收入表(mysheet)的C列的宽度为10
mysheet.column_dimensions['C'].width=10
# 保存工作簿,保存为"结果表-收入表.xlsx"文件
mybook.save("结果表-收入表.xlsx")
mysheet.column_dimensions['C'].width=10
表示设置收入表(mysheet)的C列的宽度为10
(七十五)根据字符串的最大长度设置列宽
# 导入openpyxl模块
import openpyxl
# 根据“高校汇总表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("高校汇总表.xlsx", data_only=True)
mysheet = mybook.active
mylist = [0]
# 从高校汇总表(mysheet)的第2行开始逐行循环(到最后一行)
for myrow in list(mysheet.rows)[1:]:
# 在列表(mylist)中添加高校列的单元格的字符串长度
mylist.append(len(myrow[1].value))
# 在列表(mylist)中获取最大的字符串长度,即最大列宽
mymaxwidth = max(mylist)
# 根据最大列宽设置高校汇总表(mysheet)的高校列(B列)的宽度
mysheet.column_dimensions['B'].width = mymaxwidth * 2
# 保存工作簿,保存为"结果表-高校汇总表.xlsx"文件
mybook.save("结果表-高校汇总表.xlsx")
len(myrow[1].value)
表示字符串(myrow[1].value)的长度
max(mylist)表示在列表中找最大值
(七十六)使用交错颜色设置列的背景
# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx", data_only=True)
mysheet = mybook.active
# 根据指定的范围(A1:F12)创建mytable
mytable = openpyxl.worksheet.table.Table(displayName='mytable', ref='A1:F12')
# 以交错列背景颜色的预置样式创建mystyle
mystyle = openpyxl.worksheet.table.TableStyleInfo(name='TableStyleMedium13', showColumnStripes=True)
# 在新建表格(mytable)中应用新建的样式(mystyle)
mytable.tableStyleInfo = mystyle
# 在员工表(mysheet)中应用新建表格(mytable)
mysheet.add_table(mytable)
# 保存工作簿,保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")
mystyle = openpyxl.worksheet.table.TableStyleInfo(name='TableStyleMedium13', showColumnStripes=True)
表示以交错列背景颜色的风格在工作表中应用预置的 'TableStyleMedium13' 样式,name 参数值表示预置的样式名称。
showColumnStripes=True 表示以交错列风格应用预置的表格样式